CREATE USER in MariaDB
Last updated: 2026-07-20
This skill covers the delta between generic "create a database user" SQL and MariaDB's CREATE USER. It assumes the agent already knows the basic shape (CREATE USER name IDENTIFIED BY 'password'). Everything below documents MariaDB-specific authentication chaining, default-plugin behavior, TLS/resource/expiry/lock options, and the most common LLM traps.
Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Anything available in current LTS branches (10.6, 10.11, 11.4, 11.8) is shown without a "since" annotation; only newer-than-10.6 features carry one.
What LLMs Often Miss
| If the agent writes / suggests… |
…prefer the MariaDB form |
| Assuming the default auth plugin is a SHA-2/caching variant (a habit from other databases) |
MariaDB's compiled-in default authentication plugin is mysql_native_password (default_auth_plugin_name in server source). Fresh installs additionally give the local root account unix_socket as an OR-ed fallback, so root can log in passwordless via the socket — don't assume root always needs a password |
CREATE USER 'app'@'%' IDENTIFIED BY 'pw1', 'app'@'%' IDENTIFIED VIA pam as two separate fallback mechanisms |
Chain alternatives on one account with OR: IDENTIFIED VIA ed25519 USING PASSWORD('pw1') OR unix_socket OR pam. Mechanisms are tried in the order written; first success wins |
IDENTIFIED BY PASSWORD 'mySecretPass' thinking BY PASSWORD accepts plain text |
IDENTIFIED BY PASSWORD '...' expects an already-hashed value (the output of PASSWORD()), not plain text. Plain text goes in plain IDENTIFIED BY '...' |
Not checking whether the account already exists before CREATE USER |
Either CREATE USER IF NOT EXISTS (emits a warning, Note 1973, not an error, and skips creation) or CREATE OR REPLACE USER (drops and recreates unconditionally) |
CREATE USER 'app' assuming it matches only local connections |
No host part defaults to 'app'@'%' — matches from any host. If you want local-only, write 'app'@'localhost' explicitly |
Relying on GRANT ... TO newuser to silently create the account |
NO_AUTO_CREATE_USER is set in sql_mode by default, so GRANT to a non-existent user errors instead of creating one. Always issue an explicit CREATE USER first |
REQUIRE X509 AND SSL or stacking SSL/X509 with other options |
SSL and X509 cannot combine with other TLS options (each implies the weaker one already). Only CIPHER/ISSUER/SUBJECT can be combined with each other via AND |
Assuming ACCOUNT LOCK kills the user's current session |
It only blocks new connection attempts — existing connections are unaffected until they disconnect |
Assuming IDENTIFIED VIA <plugin> USING 'x' works for every plugin |
USING/AS with a literal string is plugin-specific (e.g. pam takes a service name); to feed a plain-text password through PASSWORD() to a plugin, the plugin must implement that hook — ed25519 does, most others don't |
Syntax at a Glance
CREATE [OR REPLACE] USER [IF NOT EXISTS]
user_spec [, user_spec ...]
[REQUIRE {NONE | tls_option [[AND] tls_option ...]}]
[WITH resource_option [resource_option ...]]
[[ACCOUNT {LOCK|UNLOCK}] [PASSWORD EXPIRE [DEFAULT|NEVER|INTERVAL N DAY]]]
-- lock_option and password_option may appear in either order
user_spec:
'user'[@'host'] [ IDENTIFIED BY 'password'
| IDENTIFIED BY PASSWORD 'hash'
| IDENTIFIED {VIA|WITH} auth_rule [OR auth_rule ...] ]
auth_rule:
plugin_name [ {USING|AS} 'auth_string' | {USING|AS} PASSWORD('plaintext') ]
Authentication Options
IDENTIFIED BY 'password' — plain-text password, hashed on the server via PASSWORD() before storage. Supported only for the mysql_native_password and mysql_old_password plugins.
IDENTIFIED BY PASSWORD 'hash' — the value must already be a PASSWORD()-format hash; same two-plugin restriction.
IDENTIFIED {VIA|WITH} plugin [USING|AS ...] [OR plugin ...] — VIA and WITH are synonyms. Chain multiple plugins with OR; each is a full alternative authentication path, tried in declared order until one succeeds. Common pattern:
CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket;
No auth clause at all — the account has no password requirement; an empty password does not act as a wildcard, the client must connect with no password supplied.
The plugin must already be active (check SHOW PLUGINS); install with INSTALL PLUGIN/INSTALL SONAME first if it isn't loaded.
TLS — REQUIRE
| Option |
Effect |
REQUIRE NONE |
TLS not required (default) |
REQUIRE SSL |
TLS required, no certificate check. Cannot combine with other TLS options |
REQUIRE X509 |
TLS + valid X.509 certificate required (implies SSL). Cannot combine with other TLS options |
REQUIRE ISSUER 'x' / SUBJECT 'x' / CIPHER 'x' |
Each implies X509/SSL as needed; these three can be combined with each other via AND |
Resource Limits — WITH
MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, MAX_CONNECTIONS_PER_HOUR, MAX_USER_CONNECTIONS, MAX_STATEMENT_TIME (seconds). 0 means unlimited for that resource. Limits are tracked per account ('user'@'host'), not per session; reset with FLUSH USER_RESOURCES / FLUSH PRIVILEGES.
Password Expiry / Account Locking
PASSWORD EXPIRE -- expire immediately
PASSWORD EXPIRE DEFAULT -- fall back to default_password_lifetime
PASSWORD EXPIRE NEVER -- override global default, never expire
PASSWORD EXPIRE INTERVAL N DAY -- expire N days from now
ACCOUNT LOCK | ACCOUNT UNLOCK
lock_option and password_option may be given in either order (both orderings are valid grammar productions). ACCOUNT LOCK prevents new connections only.
OR REPLACE / IF NOT EXISTS / Errors
- Without either modifier, creating a user (or any of its permission rows) that already exists returns
ERROR 1396 (HY000); the statement still creates whichever listed accounts don't error.
IF NOT EXISTS downgrades the "exists" case to a warning (Note 1973) instead of an error, and leaves the existing account untouched.OR REPLACE is shorthand for DROP USER IF EXISTS name; CREATE USER name ... — unconditionally recreates.
Account Names
- Format:
'user_name'@'host_name'. Omitted host defaults to '%' (any host), not localhost.
- Host name supports
%/_ wildcards (LIKE-style matching) and CIDR-like base_ip/netmask forms; netmasks don't apply to IPv6.
- An empty user name (
''@'host') is an anonymous, catch-all account for that host pattern.
- When multiple accounts could match a connecting client, MariaDB picks the most specific: exact host beats wildcard host, narrower wildcards beat broader ones, and a non-empty user name beats an anonymous one. Only the matched account's own grants apply — not the union of everything that could have matched.
See Also
mariadb-grant — privilege assignment; also the implicit-user-creation interaction with NO_AUTO_CREATE_USERmariadb-alter-user — modifying an existing account's auth/TLS/resource/expiry/lock settings in placemariadb-drop-user — removing accounts; same ERROR 1396 / IF EXISTS semantics- Canonical references on
mariadb.com/docs (consult only for edge cases not covered here):
This page is: Copyright © 2026 MariaDB. All rights reserved.
1---2name: mariadb-create-user3description: "MariaDB-specific syntax and behavior for CREATE USER — multiple authentication methods chained with OR, IDENTIFIED BY / IDENTIFIED BY PASSWORD / IDENTIFIED VIA|WITH plugin USING|AS 'string'|PASSWORD(...), the default authentication plugin, REQUIRE TLS options, WITH resource-limit options, PASSWORD EXPIRE variants, ACCOUNT LOCK/UNLOCK, OR REPLACE, IF NOT EXISTS, and account-name/host defaulting. Covers CREATE USER only (not GRANT's implicit user-creation path). Use when writing, generating, or reviewing CREATE USER statements that target MariaDB."4---56# CREATE USER in MariaDB78*Last updated: 2026-07-20*910This skill covers the **delta between generic "create a database user" SQL and MariaDB's `CREATE USER`**. It assumes the agent already knows the basic shape (`CREATE USER name IDENTIFIED BY 'password'`). Everything below documents MariaDB-specific authentication chaining, default-plugin behavior, TLS/resource/expiry/lock options, and the most common LLM traps.1112> **Default context:** Assume MariaDB **11.8 LTS** (GA May 2025) unless the user states another version. Anything available in current LTS branches (10.6, 10.11, 11.4, 11.8) is shown without a "since" annotation; only newer-than-10.6 features carry one.1314## What LLMs Often Miss1516| If the agent writes / suggests… | …prefer the MariaDB form |17|---|---|18| Assuming the default auth plugin is a SHA-2/caching variant (a habit from other databases) | MariaDB's compiled-in default authentication plugin is **`mysql_native_password`** (`default_auth_plugin_name` in server source). Fresh installs additionally give the local root account `unix_socket` as an **`OR`**-ed fallback, so root can log in passwordless via the socket — don't assume root always needs a password |19| `CREATE USER 'app'@'%' IDENTIFIED BY 'pw1', 'app'@'%' IDENTIFIED VIA pam` as two separate fallback mechanisms | Chain alternatives on **one** account with `OR`: `IDENTIFIED VIA ed25519 USING PASSWORD('pw1') OR unix_socket OR pam`. Mechanisms are tried in the order written; first success wins |20| `IDENTIFIED BY PASSWORD 'mySecretPass'` thinking `BY PASSWORD` accepts plain text | `IDENTIFIED BY PASSWORD '...'` expects an **already-hashed** value (the output of `PASSWORD()`), not plain text. Plain text goes in plain `IDENTIFIED BY '...'` |21| Not checking whether the account already exists before `CREATE USER` | Either `CREATE USER IF NOT EXISTS` (emits a **warning**, `Note 1973`, not an error, and skips creation) or `CREATE OR REPLACE USER` (drops and recreates unconditionally) |22| `CREATE USER 'app'` assuming it matches only local connections | No host part defaults to **`'app'@'%'`** — matches from *any* host. If you want local-only, write `'app'@'localhost'` explicitly |23| Relying on `GRANT ... TO newuser` to silently create the account | `NO_AUTO_CREATE_USER` is set in `sql_mode` **by default**, so `GRANT` to a non-existent user errors instead of creating one. Always issue an explicit `CREATE USER` first |24| `REQUIRE X509 AND SSL` or stacking `SSL`/`X509` with other options | `SSL` and `X509` **cannot combine with other TLS options** (each implies the weaker one already). Only `CIPHER`/`ISSUER`/`SUBJECT` can be combined with each other via `AND` |25| Assuming `ACCOUNT LOCK` kills the user's current session | It only blocks **new** connection attempts — existing connections are unaffected until they disconnect |26| Assuming `IDENTIFIED VIA <plugin> USING 'x'` works for every plugin | `USING`/`AS` with a literal string is plugin-specific (e.g. `pam` takes a service name); to feed a plain-text password through `PASSWORD()` to a plugin, the plugin must implement that hook — `ed25519` does, most others don't |2728## Syntax at a Glance2930```sql31CREATE [OR REPLACE] USER [IF NOT EXISTS]32 user_spec [, user_spec ...]33 [REQUIRE {NONE | tls_option [[AND] tls_option ...]}]34 [WITH resource_option [resource_option ...]]35 [[ACCOUNT {LOCK|UNLOCK}] [PASSWORD EXPIRE [DEFAULT|NEVER|INTERVAL N DAY]]]36 -- lock_option and password_option may appear in either order3738user_spec:39 'user'[@'host'] [ IDENTIFIED BY 'password'40 | IDENTIFIED BY PASSWORD 'hash'41 | IDENTIFIED {VIA|WITH} auth_rule [OR auth_rule ...] ]4243auth_rule:44 plugin_name [ {USING|AS} 'auth_string' | {USING|AS} PASSWORD('plaintext') ]45```4647## Authentication Options4849- **`IDENTIFIED BY 'password'`** — plain-text password, hashed on the server via `PASSWORD()` before storage. Supported only for the `mysql_native_password` and `mysql_old_password` plugins.50- **`IDENTIFIED BY PASSWORD 'hash'`** — the value must already be a `PASSWORD()`-format hash; same two-plugin restriction.51- **`IDENTIFIED {VIA|WITH} plugin [USING|AS ...] [OR plugin ...]`** — `VIA` and `WITH` are synonyms. Chain multiple plugins with `OR`; each is a full alternative authentication path, tried in declared order until one succeeds. Common pattern:5253 ```sql54 CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket;55 ```5657- **No auth clause at all** — the account has no password requirement; an empty password does **not** act as a wildcard, the client must connect with no password supplied.58- The plugin must already be active (check `SHOW PLUGINS`); install with `INSTALL PLUGIN`/`INSTALL SONAME` first if it isn't loaded.5960## TLS — `REQUIRE`6162| Option | Effect |63|---|---|64| `REQUIRE NONE` | TLS not required (default) |65| `REQUIRE SSL` | TLS required, no certificate check. Cannot combine with other TLS options |66| `REQUIRE X509` | TLS + valid X.509 certificate required (implies `SSL`). Cannot combine with other TLS options |67| `REQUIRE ISSUER 'x'` / `SUBJECT 'x'` / `CIPHER 'x'` | Each implies `X509`/`SSL` as needed; these three **can** be combined with each other via `AND` |6869## Resource Limits — `WITH`7071`MAX_QUERIES_PER_HOUR`, `MAX_UPDATES_PER_HOUR`, `MAX_CONNECTIONS_PER_HOUR`, `MAX_USER_CONNECTIONS`, `MAX_STATEMENT_TIME` (seconds). `0` means unlimited for that resource. Limits are tracked per **account** (`'user'@'host'`), not per session; reset with `FLUSH USER_RESOURCES` / `FLUSH PRIVILEGES`.7273## Password Expiry / Account Locking7475```sql76PASSWORD EXPIRE -- expire immediately77PASSWORD EXPIRE DEFAULT -- fall back to default_password_lifetime78PASSWORD EXPIRE NEVER -- override global default, never expire79PASSWORD EXPIRE INTERVAL N DAY -- expire N days from now80ACCOUNT LOCK | ACCOUNT UNLOCK81```8283`lock_option` and `password_option` may be given in either order (both orderings are valid grammar productions). `ACCOUNT LOCK` prevents new connections only.8485## OR REPLACE / IF NOT EXISTS / Errors8687- Without either modifier, creating a user (or any of its permission rows) that already exists returns `ERROR 1396 (HY000)`; the statement still creates whichever listed accounts *don't* error.88- `IF NOT EXISTS` downgrades the "exists" case to a warning (`Note 1973`) instead of an error, and leaves the existing account untouched.89- `OR REPLACE` is shorthand for `DROP USER IF EXISTS name; CREATE USER name ...` — unconditionally recreates.9091## Account Names9293- Format: `'user_name'@'host_name'`. Omitted host defaults to `'%'` (any host), **not** `localhost`.94- Host name supports `%`/`_` wildcards (LIKE-style matching) and CIDR-like `base_ip/netmask` forms; netmasks don't apply to IPv6.95- An empty user name (`''@'host'`) is an anonymous, catch-all account for that host pattern.96- When multiple accounts could match a connecting client, MariaDB picks the most specific: exact host beats wildcard host, narrower wildcards beat broader ones, and a non-empty user name beats an anonymous one. Only the matched account's own grants apply — not the union of everything that could have matched.9798## See Also99100- **`mariadb-grant`** — privilege assignment; also the implicit-user-creation interaction with `NO_AUTO_CREATE_USER`101- **`mariadb-alter-user`** — modifying an existing account's auth/TLS/resource/expiry/lock settings in place102- **`mariadb-drop-user`** — removing accounts; same `ERROR 1396` / `IF EXISTS` semantics103- Canonical references on `mariadb.com/docs` (consult only for edge cases not covered here):104 - <https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/create-user>105 - <https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/set-password>106 - <https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-user>107 - <https://mariadb.com/docs/server/reference/plugins/authentication-plugins>108109<sub>_This page is: Copyright © 2026 MariaDB. All rights reserved._</sub>
mariadb-corporation