# 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" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/security/user-account-management/account-locking.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.