MySQL Unlock Account

Unlock is a mechanism that allows the user to release all locks or any specific lock associated with the account. In this article, we will learn how to unlock the user accounts in the MySQL server.

When the CREATE USER… UNLOCK statement creates a new user account, the new user stored as a locked state.

If we want to release a lock from the existing user account, we need to use the ALTER USER… ACCOUNT UNLOCK statement as follows:

ALTER USER [IF EXISTS] user_account_name ACCOUNT UNLOCK; 

In this syntax, we have to first specify the user account name that we want to release a lock after the ALTER USER keyword. Next, we need to provide the ACCOUNT UNLOCK clause next to the user name. It is to note the IF EXISTS option can also be used to unlock the account only if it has existed in the server.

MySQL also allows us to unlock multiple user accounts at the same time by using the below statement:

ALTER USER [IF EXISTS]   

    user_account_name1, user_account_name2, ...  

ACCOUNT UNLOCK;

In this syntax, we need to provide a list of comma-separated user name for unlocking multiple accounts within a single query. If we do not specify the ACCOUNT UNLOCK clause with the statement, the account unlocking state remains unchanged.

MySQL uses the account_locked column of the mysql.user system table to store the account locking state. We can use the SHOW CREATE USER statement to validate whether the account is unlocked or locked. If this column value is Y, it means the account is locked. If it contains N, it means the account is unlocked.

If we will try to connect to the account without unlocking, MySQL issues an error that writes the below message to the error log:

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

An account is locked.

MySQL User Account Unlocking Examples

Let us understand how to unlock the user accounts through examples. First, we will create a new user account named javatpoint@localhost in the locked state using the below statement:

mysql> CREATE USER IF NOT EXISTS javatpoint@localhost   

IDENTIFIED BY 'jtp123456'  

ACCOUNT LOCK;

Next, we will execute the below statement to show the user account and its status:

mysql> SELECT user, host, account_locked  

FROM mysql.user  

WHERE user = 'javatpoint' AND host = 'localhost';

We should get the below output:

In this output, we can see that the account_locked column in the mysql.user system table indicates Y. It means the username javatpoint is locked on the server.

If we try to connect with this account without unlocking in the MySQL Server, it returns the following error:

mysql -u javatpoint -p  

Enter password: *********

Here is the error message:

Thus, we can use the ALTER USER statement to unlock the account before accessing it as follows:

mysql> ALTER USER 'javatpoint'@'localhost' ACCOUNT UNLOCK;  

In the below output, we can see that the account_locked column status is N. It means the user account javatpoint does not have any lock.

In this article, we have learned how we can use the ALTER TABLE ACCOUNT UNLOCK statement to release a lock from an existing user account.