Lock User Accounts using ALTER USER statement in MySQL

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.

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            | %         | N              |
| 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              |
+------------------+-----------+----------------+

Lock an Existing User

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

The following SQL statement will lock the user account 'sqliz'@'%'. Please refer to the “Create MySQL User Tutorial: to create the 'sqliz'@'%' first.

Please use the root account or other administrator account to execute the following statement:

ALTER USER 'sqliz'@'%' ACCOUNT LOCK;

To query the information about 'sqliz'@'%', run the following SQL statement:

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

+-------+------+----------------+
| user  | host | account_locked |
+-------+------+----------------+
| sqliz | %    | Y              |
+-------+------+----------------+

Here, the Y in the account_locked column indicates that user account 'sqliz'@'%' has been locked.

Attempt to log in to MySQL server verify that the user sqliz is really locked out.

mysql -u sqliz -p

Enter your password sqliz and press Enter:

Enter password: ********

Since the user has been locked, the MySQL server will give an error message: “ERROR 3118 (HY000): Access denied for user ‘sqliz’@‘192.168.0.204’. Account is locked.”

Create a locked user

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

Suppose, you need to create a new user sqliz2, execute the following statement:

CREATE user 'sqliz2'@'%'
IDENTIFIED by 'SqLiZ9879123!'
ACCOUNT LOCK;

Here, the user named sqliz2 and allowed to connect from any host, was created, and this user is locked.

To query the information about the use 'sqliz2'@'%', run the following SQL statement:

SELECT user, host, account_locked
FROM mysql.user
WHERE user = 'sqliz2' AND host = '%';

+--------+------+----------------+
| user   | host | account_locked |
+--------+------+----------------+
| sqliz2 | %    | 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.