Lock / Unlock user account in MySQL

Lock / Unlock User Account in MySQL

Check the Status of User Account is locked/ unlocked

SELECT user, host, account_locked
 FROM
 mysql.user
WHERE user = 'hr' AND 
host='localhost';

Note: Column ACCOUNT_LOCKED value “Y” means locked, “N” means unlocked

Lock the User Account in MySQL

-- Create user with Locked account status
--Syntax
CREATE USER account_name
IDENTIFIED BY 'password'
ACCOUNT LOCK;

--Locked the Existing user in mysql
ALTER USER account_name
IDENTIFIED BY 'password'
ACCOUNT LOCK;

Example:
-- lock existing user
ALTER USER hr@localhost
 ACCOUNT LOCK;

--Create user command with lock
CREATE USER hr@localhost IDENTIFIED BY 'Password!1' ACCOUNT LOCK;

Unlock the User Account in MySQL

Syntax:
--for single user
ALTER USER [IF EXISTS] account_name
 ACCOUNT UNLOCK;
--For multiple user
ALTER USER [IF EXISTS] account_name1 
[, account_name2, ...]
ACCOUNT UNLOCK;

Example:
ALTER USER 'hr'@'localhost'
 ACCOUNT UNLOCK;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.