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;