# Account Locking

## Syntax

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

{% tabs %}
{% tab title="Current" %}
The *lock\_option* and *password\_option* clauses can occur in either order.
{% endtab %}

{% tab title="<10.4.7, < 10.5.8" %}
Prior to [MariaDB 10.4.7](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.4/10.4.7) and [MariaDB 10.5.8](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/old-releases/10.5/10.5.8), the *lock\_option* must be placed before the *password\_option*.
{% endtab %}
{% endtabs %}

## 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](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/create-user) statement, or modified after creation with the [ALTER USER](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/alter-user) statement. For example:

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

or

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

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

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

### Unlocking Accounts

The [ALTER USER](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/alter-user) statement is used to unlock a user:

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

### Show Whether a Specific Account is Locked

The [SHOW CREATE USER](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-user) statement will show whether the account is locked:

```sql
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](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-global_priv-table):

```sql
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:

```sql
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](https://www.youtube.com/watch?v=AWM_fWZ3XIw) video tutorial

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}