MySQL – Account Unlock

A user can unlock an account by releasing all locks or any particular lock associated with the account. This article explains how to unlock user accounts on a MySQL server.

The CREATE USER… UNLOCK statement creates a new user account that is locked.

To release a lock from an existing user account, we must use the ALTER USER… ACCOUNT UNLOCK statement as follows:

ALTER USER [IF EXISTS] user_account_name ACCOUNT UNLOCK; 

We must first specify the user account name that we wish to release a lock after the ALTER USER keyword. The next step is to provide the ACCOUNT UNLOCK clause next to the user name. Note that the IF EXISTS option can also be used to unlock an account only if it has existed on the server previously.

 By using the following statement, we can unlock multiple user accounts at the same time in MySQL:

ALTER USER [IF EXISTS]   
    user_account_name1, user_account_name2, ...  
ACCOUNT UNLOCK;  

Using this syntax, we need to provide a list of comma-separated user names in order to unlock multiple accounts within a single query. The account unlocking state remains unchanged if the ACCOUNT UNLOCK clause is not specified.

A record of the account locking state is stored in the account_locked column of the mysql.user system table. The SHOW CREATE USER statement can be used to determine whether the account has been unlocked or locked. The value of this column is Y if the account is locked. The account is unlocked if it contains N.

If Upon attempting to connect to the account without unlocking, MySQL issues the following error message:

Access denied for user 'user_name'@'host_name'.  

An account is locked.