Account Locking

Explains how to lock and unlock user accounts using `CREATE USER` and `ALTER USER` statements to prevent login access without deleting the account.

Syntax

 CREATE USER [...]
 [lock_option] [password_option] 
 
 ALTER USER [...]
 [lock_option] [password_option] 

Current

The lock_option and password_option clauses can occur in either order.

<10.4.7, < 10.5.8

Description

Account locking permits privileged administrators to lock/unlock user accounts. No new client connections will be permitted if an account is locked (existing connections are not affected).

Locking Accounts

User accounts can be locked at creation, with the CREATE USER statement, or modified after creation with the ALTER USER statement. For example:

CREATE USER 'lorin'@'localhost' ACCOUNT LOCK;

or

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

The server will return an ER_ACCOUNT_HAS_BEEN_LOCKED error when locked users attempt to connect:

mariadb -ulorin
  ERROR 4151 (HY000): Access denied, this account is locked

Unlocking Accounts

The ALTER USER statement is used to unlock a user:

ALTER USER 'lorin'@'localhost' ACCOUNT UNLOCK;

Show Whether a Specific Account is Locked

The SHOW CREATE USER statement will show whether the account is locked:

SHOW CREATE USER 'marijn'@'localhost';
+-----------------------------------------------+
| CREATE USER for marijn@localhost              |
+-----------------------------------------------+
| CREATE USER 'marijn'@'localhost' ACCOUNT LOCK |
+-----------------------------------------------+

as well as querying the mysql.global_priv table:

SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) 
  FROM mysql.global_priv 
  WHERE user='marijn';
+--------------------------------------------------------------------------------------+
| CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv))                                 |
+--------------------------------------------------------------------------------------+
| marijn@localhost => {
    "access": 0,
    "plugin": "mysql_native_password",
    "authentication_string": "",
    "account_locked": true,
    "password_last_changed": 1558017158
} |
+--------------------------------------------------------------------------------------+

Find Locked Accounts

This query against the mysql.global_priv table will return all accounts which have "account_locked": true within the Priv json column:

SELECT CONCAT(user, '@', host) AS 'Locked Accounts' FROM mysql.global_priv WHERE Priv like '%account_locked":true%';

Example Output:

+------------------------+
| Locked Accounts        |
+------------------------+
| mariadb.sys@localhost  |
| bart.simpson@localhost |
+------------------------+
2 rows in set (0.000 sec)

See Also

• Account Locking and Password Expiry video tutorial

_{This page is licensed: CC BY-SA / Gnu FDL}