# Mariadb Grant

> Documents MariaDB-specific GRANT behavior, including privilege scope levels, implicit account creation, MariaDB-only privileges, roles, proxy grants, resource limits, and TLS options, to help write accurate GRANT statements.

- Skill: `mariadb-corporation/mariadb-grant` (Agent Skill)
- Install (CLI): `npx skillmds add mariadb-corporation/mariadb-grant`
- Raw SKILL.md: https://api.skillmd.com/api/skills/mariadb-corporation/mariadb-grant/raw
- Safety review: PASS (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools, Data & Analytics, SQL & Databases
- Tags: Database Administration, Grant, Mariadb, Privileges, Roles, Sql
- Author: mariadb-corporation (https://skillmd.com/u/mariadb-corporation)
- Updated: 2026-08-22
- Page: https://skillmd.com/skills/mariadb-corporation/mariadb-grant

---


# GRANT in MariaDB

*Last updated: 2026-07-20*

This skill covers the **delta between the privilege grammar an LLM tends to reach for and MariaDB's `GRANT`**. It assumes the agent already knows the basic shape (`GRANT priv ON object TO user`). Everything below documents MariaDB's privilege catalog quirks, implicit-user-creation rules, roles, proxy grants, and the traps most likely to trip up a coding agent.

> **Default context:** Assume MariaDB **11.8 LTS** unless the user states another version. Privileges available in all current LTS branches (10.6, 10.11, 11.4, 11.8) are shown without a "since" annotation; only newer-than-10.6 items carry one. Never mention versions below 10.6.

## What LLMs Often Miss

| If the agent writes / assumes… | …the actual MariaDB behavior |
|---|---|
| Assuming `GRANT ... TO newuser` cannot create an account | In MariaDB, `GRANT ... TO newuser IDENTIFIED BY 'pw'` **still implicitly creates the account** if it doesn't exist — as long as authentication info is supplied. Verified in `sql/sql_acl.cc` (`replace_user_table`, ~line 5639) |
| Assuming `GRANT` freely auto-creates passwordless users | The `NO_AUTO_CREATE_USER` `sql_mode` is **enabled by default** in current MariaDB (`sql/sys_vars.cc`: default `sql_mode` includes `MODE_NO_AUTO_CREATE_USER`). With it on (the default), `GRANT ... TO newuser` with **no** `IDENTIFIED BY`/`VIA` clause errors (`ER_PASSWORD_NO_MATCH`) instead of silently creating a passwordless account. `IDENTIFIED BY ''` (empty string) does **not** count as auth for this check — `LEX_USER::has_auth()` requires a non-empty `pwtext`/`auth_str`/plugin — so it still errors under the default mode |
| `GRANT REPLICATION CLIENT ON *.* TO ...` for binlog-status monitoring | Still works (kept as a compatibility alias), but the current name is **`BINLOG MONITOR`** — `REPLICATION CLIENT` was renamed in 10.5.2. Confirmed in `sql/sql_yacc.yy`: `REPLICATION CLIENT_SYM { $$= BINLOG_MONITOR_ACL; /*Compatibility*/ }` |
| Granting `SUPER` for anything beyond legacy compatibility | `SUPER` was split (MDEV-21743, 10.5.2+) into narrower privileges: `SET USER`, `FEDERATED ADMIN`, `CONNECTION ADMIN`, `REPLICATION SLAVE ADMIN`, `BINLOG ADMIN`, `BINLOG REPLAY`, `SLAVE MONITOR` (`REPLICA` is a lexer synonym for the `SLAVE` token, so `REPLICA MONITOR` also parses), `BINLOG MONITOR`, `REPLICATION MASTER ADMIN`. `READ_ONLY ADMIN` was later **removed from `SUPER`** entirely (so replicas can be locked down even for `SUPER` users). Prefer the narrow privilege over blanket `SUPER` |
| A user's assigned roles are "active" as soon as they're granted | Granting a role (`GRANT rolename TO user`) does **not** confer its privileges. The user must activate it with `SET ROLE rolename` (or have it as a default role — see `SET DEFAULT ROLE`) before the privileges apply |
| `SET ROLE r1, r2` to activate several roles at once | MariaDB has **only one active role per session** — `SET ROLE` replaces the current role, it does not add to a set. (A role can itself have other roles granted to it, which is how a session reaches a whole privilege tree through one active role.) |
| A role name and a username never collide | They can, and MariaDB resolves the conflict **in favor of the role**: `CREATE USER alice; CREATE ROLE alice; GRANT SELECT ON db.* TO alice;` grants `SELECT` to the *role* `alice`, not the user |
| `DELETE`-only privilege list for a table | MariaDB also has **`DELETE HISTORY`** — required to run `DELETE HISTORY` against a `SYSTEM VERSIONING` table's historical rows; it's distinct from plain `DELETE` |
| `GRANT ... TO user@host` is the only grantee form | MariaDB also accepts `TO PUBLIC` *(since 10.11)* to grant to every account, present and future — `SHOW GRANTS` shows a user's `PUBLIC`-inherited grants; `SHOW GRANTS FOR PUBLIC` isolates just the `PUBLIC` grants |
| Overlapping wildcard `db_name.*` grants resolve by grant order or wildcard-prefix length | MariaDB picks the **most specific pattern** (the one matching the fewest databases) regardless of grant order or literal prefix length |

## Privilege Levels

`priv_level` in the grammar selects scope; some privileges only make sense at certain scopes:

| Scope | `priv_level` form | Notes |
|---|---|---|
| Global | `*.*` | Server administration + all DB/table/routine privileges everywhere, including objects created later. Stored in `mysql.global_priv`. Takes effect only for *new* connections |
| Database | `db_name.*` (or bare `*` for current DB) | All table/routine privileges within that DB, including future objects. Stored in `mysql.db` |
| Table | `db_name.tbl_name` (or bare `tbl_name`) | `TABLE` keyword optional |
| Column | table-level `priv_level` + `priv_type (col1, col2, …)` | Only `SELECT`, `INSERT`, `UPDATE`, `REFERENCES` support column lists. `GRANT OPTION` cannot be column-scoped — `WITH GRANT OPTION` on a column grant promotes to table-level `GRANT OPTION` |
| Routine | `FUNCTION db.name` / `PROCEDURE db.name` | Also `PACKAGE` / `PACKAGE BODY` object types |

`ALL` / `ALL PRIVILEGES` grants everything **at the given level only** (table-level `ALL` doesn't touch DB or global scope) and never implies `GRANT OPTION`. `USAGE` grants nothing — it's the vehicle for changing resource limits or TLS options on an existing account without touching privileges.

## Roles

```sql
GRANT role TO user_or_role [, user_or_role ...] [WITH ADMIN OPTION];
GRANT role1 TO role2;   -- roles can be granted to other roles
```

- Requires the grantor to already have admin rights over the role (see `CREATE ROLE ... WITH ADMIN`, which defaults to `WITH ADMIN CURRENT_USER`).
- `WITH ADMIN OPTION` lets the grantee re-grant the role onward.
- Privileges attach to the role via ordinary `GRANT priv ON obj TO rolename`, then reach the user only when active (`SET ROLE rolename`).
- Role-vs-username conflicts resolve to the role (see table above).

## `GRANT PROXY`

```sql
GRANT PROXY ON user_or_role TO account_or_role [, ...] [WITH GRANT OPTION];
```

Lets one account impersonate another (`CURRENT_USER()` becomes the proxy target); requires an auth plugin that supports it (the default `mysql_native_password` does not — `pam` does). To grant `PROXY` for a *specific* target account, the granter needs `PROXY` on that exact account **with `GRANT OPTION`**. To grant `PROXY` for *any* account, the granter needs `PROXY ON ''@'%' WITH GRANT OPTION` (the anonymous-account form) — this is what default `root` accounts hold.

## Resource Limits & TLS

```sql
GRANT USAGE ON *.* TO 'u'@'h' WITH
    MAX_QUERIES_PER_HOUR n MAX_UPDATES_PER_HOUR n
    MAX_CONNECTIONS_PER_HOUR n MAX_USER_CONNECTIONS n
    MAX_STATEMENT_TIME t;

GRANT USAGE ON *.* TO 'u'@'h' REQUIRE SSL;                 -- or X509
GRANT USAGE ON *.* TO 'u'@'h' REQUIRE SUBJECT '...' AND ISSUER '...' AND CIPHER '...';
```

`0` on any resource limit means unlimited. Limits are tracked per `'user'@'host'` account, not per session. `CONNECTION ADMIN` (and legacy `SUPER`) accounts bypass `max_user_connections`/`max_password_errors` and get one extra slot past `max_connections`. `REQUIRE SSL`/`X509` cannot combine with other TLS options; `ISSUER`/`SUBJECT`/`CIPHER` imply and can combine with `X509`.

## See Also

- **`mariadb-revoke`** — the inverse statement; same privilege catalog and scope rules apply
- **`mariadb-create-user`** — account-name syntax, authentication plugins, `IDENTIFIED VIA`
- Canonical references on `mariadb.com/docs` (consult for the full ~50-entry privilege table and edge cases not covered here):
  - <https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant>
  - <https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/create-role>
  - <https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/set-role>
  - <https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/revoke>

<sub>_This page is: Copyright © 2026 MariaDB. All rights reserved._</sub>