CREATE USER [...]
[lock_option] [password_option]
ALTER USER [...]
[lock_option] [password_option]
The lock_option and password_option clauses can occur in either order.
Prior to MariaDB 10.4.7 and MariaDB 10.5.8, the lock_option must be placed before the password_option.
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).
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
The ALTER USER statement is also used to unlock a user:
ALTER USER 'lorin'@'localhost' ACCOUNT UNLOCK;
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
} |
+--------------------------------------------------------------------------------------+
Account Locking and Password Expiry video tutorial
This page is licensed: CC BY-SA / Gnu FDL