The lock_option and password_option clauses can occur in either order.
Prior to and , 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 statement, or modified after creation with the statement. For example:
or
The server will return an ER_ACCOUNT_HAS_BEEN_LOCKED error when locked users attempt to connect:
The statement is used to unlock a user:
The statement will show whether the account is locked:
as well as querying the :
This query against the mysql.global_priv table will return all accounts which have "account_locked": true within the Priv json column:
Example Output:
video tutorial
This page is licensed: CC BY-SA / Gnu FDL
CREATE USER [...]
[lock_option] [password_option]
ALTER USER [...]
[lock_option] [password_option] CREATE USER 'lorin'@'localhost' ACCOUNT LOCK;ALTER USER 'marijn'@'localhost' ACCOUNT LOCK;mariadb -ulorin
ERROR 4151 (HY000): Access denied, this account is lockedALTER USER 'lorin'@'localhost' ACCOUNT UNLOCK;SHOW CREATE USER 'marijn'@'localhost';
+-----------------------------------------------+
| CREATE USER for marijn@localhost |
+-----------------------------------------------+
| CREATE USER 'marijn'@'localhost' ACCOUNT LOCK |
+-----------------------------------------------+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
} |
+--------------------------------------------------------------------------------------+SELECT CONCAT(user, '@', host) AS 'Locked Accounts' FROM mysql.global_priv WHERE Priv like '%account_locked":true%';+------------------------+
| Locked Accounts |
+------------------------+
| mariadb.sys@localhost |
| bart.simpson@localhost |
+------------------------+
2 rows in set (0.000 sec)