MySQL Lock User Account

When we create a user in the MySQL database, we can create an account lock to safeguard the database from any external modifications. We can also alter the existing account and patch it up with an account lock. We can lock the user using the create user followed by the account lock statement in the end.

New User Account Lock

Let us create an account name ash@localhost in a locked state.

mysql> CREATE USER IF NOT EXISTS ash@localhost   
IDENTIFIED BY '12345'  
ACCOUNT LOCK; 

Then when we try to check the account status using this query,

mysql> SELECT user, host, account_locked  
FROM MySQL.user  
WHERE user = 'ash' AND host = 'localhost'; 

This will show the account name and account locked as 'y' meaning yes.

Now, if we try to access the user using,

MySQL -u ash -p  
Enter password: ********* 

We get this access denied for the user as the account is locked.

Locking For Existing Users

Using the Alter statement, we can also lock the existing user likewise.

mysql> ALTER USER tony@localhost ACCOUNT LOCK;  

We can also check like the same in the previous steps about its locked status, and it will yield the same.