Lock MySQL User Accounts with ACCOUNT LOCK

In some specific cases, you may want to lock a user account, such as:

• Create a locked user and wait for authorization to complete before unlocking
• This user account is no longer in use
• This user account has been compromised

To lock an existing user, use the ALTER USER .. ACCOUNT LOCK statement.

To create a locked user directly, use the CREATE USER .. ACCOUNT LOCK statement.

An explicit account lock blocks new direct login attempts but does not remove the account or its grants. It also does not prevent proxy use or stored programs and views that name the account as DEFINER. Review active threads separately with SHOW PROCESSLIST. See the MySQL account-locking rules.

Query the lock status of a user

account_locked in mysql.user reports an explicit ACCOUNT LOCK: Y means locked, and N means not explicitly locked. Do not confuse that with a temporary block after repeated incorrect passwords, which uses FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME and returns Error 3955. Error 3118 is the explicit locked-account response. See MySQL password management and the MySQL 8.4 error reference.

Use the exact user and host because MySQL treats them as separate account identifiers:

SELECT User, Host, account_locked
FROM mysql.user
WHERE User = 'app_user' AND Host = 'localhost';

+----------+-----------+----------------+
| User     | Host      | account_locked |
+----------+-----------+----------------+
| app_user | localhost | N              |
+----------+-----------+----------------+

Lock an Existing User

To lock an existing user, you should use the ALTER USER .. ACCOUNT LOCK statement.

The following statement locks the example account 'app_user'@'localhost'. Create it first if it does not exist; see the MySQL CREATE USER tutorial.

Run the statement as an account authorized to modify MySQL accounts:

ALTER USER 'app_user'@'localhost' ACCOUNT LOCK;

Check the same account again:

SELECT User, Host, account_locked
FROM mysql.user
WHERE User = 'app_user' AND Host = 'localhost';

+----------+-----------+----------------+
| User     | Host      | account_locked |
+----------+-----------+----------------+
| app_user | localhost | Y              |
+----------+-----------+----------------+

Here, Y indicates that 'app_user'@'localhost' is explicitly locked.

Attempt to connect to verify that MySQL rejects new logins for the locked account:

mysql --user=app_user --host=localhost --password

Enter the account password when prompted:

Enter password: ********

An explicit account lock returns Error 3118 (HY000), Account is locked. A temporary failed-login block instead returns Error 3955. See the MySQL error reference.

Create a locked user

To create a locked user directly, use the CREATE USER .. ACCOUNT LOCK statement.

To create an account that starts locked, use a randomly generated password instead of placing a reusable password in an example:

CREATE USER 'app_user2'@'localhost'
  IDENTIFIED BY RANDOM PASSWORD
  ACCOUNT LOCK;

MySQL returns the generated password in the result. Store it securely if you intend to unlock and use this account later; do not put a generated password in a script or public documentation.

IDENTIFIED BY RANDOM PASSWORD is available in MySQL 8.0.18 and later. On older releases, substitute a unique password from a password manager and keep it out of source code.

Check the new account’s explicit lock state:

SELECT User, Host, account_locked
FROM mysql.user
WHERE User = 'app_user2' AND Host = 'localhost';

+-----------+-----------+----------------+
| User      | Host      | account_locked |
+-----------+-----------+----------------+
| app_user2 | localhost | Y              |
+-----------+-----------+----------------+

Number of connections for a locked user

MySQL maintains a variable Locked_connects that holds the number of times a locked user has attempted to connect to the server. When a locked account attempts to log in, the value of the Locked_connects variable will be incremented by 1.

You can view the number of login attempts by locked users on the current MySQL database server using the following statement:

SHOW GLOBAL STATUS LIKE 'Locked_connects';

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Locked_connects | 3     |
+-----------------+-------+

Note that your results may be different.

Conclusion

In MySQL you can lock a user in two ways:

• To lock an existing user, use the ALTER USER .. ACCOUNT LOCK statement.
• To create a locked user directly, use the CREATE USER .. ACCOUNT LOCK statement.

For a locked user, you can unlock the user.