Unlock Locked User Accounts using ALTER USER statement in MySQL

To unlock one or more locked users, use the ALTER USER .. ACCOUNT UNLOCK statement.

Basic syntax of Unlocking users

The following is the syntax for unlocking a user account:

ALTER USER [IF EXISTS] user@host [, user@host, ...]
ACCOUNT UNLOCK;

here:

• The user@host includes the username and hostname. Use commas to separate multiple accounts.
• The UNLOCK keywords are used to unlock users. Use LOCK if you want to lock a user.

Query the lock status of a user

You can get a user’s lock status from the user table in the mysql database. The account_locked column in the mysql.user table holds the status of whether the account is locked: Y if the user is locked, and N if the user is not locked.

Please use the following SQL statement to query the lock status of all users in the MySQL database server:

SELECT user, host, account_locked
FROM mysql.user;

+------------------+-----------+----------------+
| user             | host      | account_locked |
+------------------+-----------+----------------+
| root             | %         | N              |
| sqliz            | %         | Y              |
| sqliz2           | %         | Y              |
| test_role1       | %         | Y              |
| test_role2       | %         | Y              |
| test_user1_new   | %         | N              |
| test_user2_new   | %         | N              |
| test_user3_new   | %         | N              |
| testuser         | %         | N              |
| mysql.infoschema | localhost | Y              |
| mysql.session    | localhost | Y              |
| mysql.sys        | localhost | Y              |
| root             | localhost | N              |
+------------------+-----------+----------------+

The users sqliz and sqliz2 were locked in the MySQL Lock User tutorial.

Unlock user accounts

To unlock users sqliz and sqliz2, run the following statement:

ALTER USER 'sqliz'@'%', 'sqliz2'@'%' ACCOUNT UNLOCK;

Here, we unlock 'sqliz'@'%' and 'sqliz2'@'%'.

To query the lockout status of these two user accounts, run the following SQL statement:

SELECT user, host, account_locked
FROM mysql.user
WHERE user LIKE 'sqliz%';

+--------+------+----------------+
| user   | host | account_locked |
+--------+------+----------------+
| sqliz  | %    | N              |
| sqliz2 | %    | N              |
+--------+------+----------------+

The N in the account_locked column indicates that the two users are no longer locked.

Conclusion

In MySQL, you can unlock one or more locked users with the ALTER USER .. ACCOUNT UNLOCK statement.