REVOKE in MariaDB
Last updated: 2026-07-20
This skill covers the delta between standard SQL REVOKE and MariaDB's. It assumes the agent already knows the standard form (REVOKE priv ON obj FROM grantee). MariaDB's privilege model is symmetric with GRANT (see mariadb-grant for the full priv_type/priv_level catalog) but REVOKE has its own grammar quirks, error semantics, and a role-revocation path that has no standard-SQL equivalent.
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 |
REVOKE ALL PRIVILEGES, GRANT OPTION FROM ON *.* user or otherwise adding ON *.* to the all-privileges form |
The all-privileges form takes no ON clause at all — REVOKE ALL PRIVILEGES, GRANT OPTION FROM user;. Adding ON *.* is a syntax error; the grammar simply has no ON slot in that production |
REVOKE IF EXISTS priv ON obj FROM user |
No IF EXISTS clause exists for REVOKE — the grammar (revoke: rule) has no IF EXISTS token. Revoking a privilege/grant that doesn't exist at that exact level always errors (ER_NONEXISTING_GRANT, SQLSTATE 42000); there is no built-in idempotent form. Wrap in a stored routine with a handler, or check SHOW GRANTS first, if idempotency is needed |
Assuming REVOKE ALL ON *.* FROM user removes every privilege |
It removes everything except USAGE — the user keeps the ability to connect. To fully lock the account out, DROP USER it instead. The second form (REVOKE ALL PRIVILEGES, GRANT OPTION FROM user) is the same story — USAGE always survives |
Revoking a table/column privilege at the wrong level (e.g. revoking a column-level grant with a table-level REVOKE) |
The revoke's ON level must match exactly the level the privilege was granted at (global / db / table / column / routine). Mismatched levels raise ER_NONEXISTING_GRANT, not a silent no-op |
REVOKE role FROM user when checking whether it "cascades" |
It does — MariaDB's role system computes effective privileges by propagation through the role graph. Revoking a privilege from a role (or revoking a role from another role) immediately changes the effective privileges of every user/role that inherits it, with no re-grant needed |
Forgetting REVOKE role needs the grantor to already hold GRANT OPTION-equivalent authority |
Revoking privileges (form 1) requires GRANT OPTION on what's being revoked, same as GRANT; revoking the special ALL form requires the global CREATE USER privilege or UPDATE on the mysql database — a different requirement than form 1 |
REVOKE PROXY FROM user (omitting ON) |
PROXY revocation always needs the target: REVOKE PROXY ON proxied_user FROM user_who_can_proxy — mirrors GRANT PROXY ON ... TO ... in reverse |
The Five Forms
-- 1. Revoke specific privileges at a given level
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] priv_level
FROM account_or_role [, account_or_role] ...
-- 2. Revoke everything (no ON clause — this is the special form)
REVOKE ALL [PRIVILEGES], GRANT OPTION
FROM account_or_role [, account_or_role] ...
-- 3. Revoke proxy privilege
REVOKE PROXY ON user_or_role
FROM account_or_role [, account_or_role] ...
-- 4. Revoke a role
REVOKE role [, role] ...
FROM account_or_role [, account_or_role] ...
-- 5. Revoke the ADMIN OPTION for a role (keeps the role itself)
REVOKE ADMIN OPTION FOR role [, role] ...
FROM account_or_role [, account_or_role] ...
priv_type/priv_level/object_type are identical to GRANT — see mariadb-grant for the full catalog (this includes MariaDB-only privileges like BINLOG ADMIN, CONNECTION ADMIN, READ ONLY ADMIN, SET USER, SLAVE MONITOR). account_or_role accepts username, role, PUBLIC, CURRENT_USER[()], or CURRENT_ROLE[()].
Privilege Requirements — Two Different Bars
- Form 1 (targeted privilege revoke): the executing user needs the
GRANT OPTION privilege and must actually hold the privileges being revoked.
- Form 2 (
REVOKE ALL PRIVILEGES, GRANT OPTION FROM ...): needs the global CREATE USER privilege, or the UPDATE privilege on the mysql database — a distinct, broader requirement than form 1.
Errors and Mismatches
- Revoking a privilege the grantee doesn't hold at the specified level raises
ER_NONEXISTING_GRANT (SQLSTATE 42000) — not a silent success. This applies to global/db/table/column/routine levels and to proxy revocation alike.
- There is no
IF EXISTS variant of REVOKE — confirmed against the grammar (sql/sql_yacc.yy, the revoke: production). If a script needs to revoke-if-present, catch the error explicitly (e.g. in a stored procedure with a DECLARE ... HANDLER) rather than assuming an IF EXISTS keyword is accepted.
REVOKE ALL PRIVILEGES, GRANT OPTION FROM ... never takes an ON clause; ON *.* there is a parse error because the production has no ON token, not a semantic restriction that can be worked around with different syntax.
Roles
REVOKE journalist FROM hulda;
REVOKE ADMIN OPTION FOR journalist FROM hulda;
- Revoking a role from a user or another role removes the membership edge; privilege propagation recomputes effective privileges for every downstream grantee immediately — no separate refresh step.
- Revoking a privilege from a role (via form 1,
ON ... FROM role_name) similarly propagates: every user or role that has that role in its chain loses the privilege, without touching the role's other members' unrelated privileges.
- If the role being revoked was previously set as a session's default role (
SET DEFAULT ROLE), REVOKE does not clear that default-role record in mysql.user. Re-granting the same role later makes it the default again automatically. Use SET DEFAULT ROLE NONE to actually clear it.
Proxy Revocation
REVOKE PROXY ON 'dba_user'@'localhost' FROM 'app_user'@'localhost';
Removes app_user's ability to connect and execute as dba_user. Always requires the ON target — there's no bare "revoke all proxy" shorthand.
See Also
mariadb-grant — the mirrored GRANT privilege model, full priv_type/priv_level catalog, WITH GRANT OPTIONmariadb-create-user — account creation/locking; DROP USER for fully removing an account (the only way to strip the residual USAGE privilege)- 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-revoke3description: "MariaDB-specific syntax and behavior for REVOKE — the mirrored five-form grammar (privilege ON object, the special ALL PRIVILEGES/GRANT OPTION form with no ON clause, PROXY, role revocation, ADMIN OPTION FOR role), the USAGE-privilege gotcha, ER_NONEXISTING_GRANT on mismatched revoke level, and the absence of an IF EXISTS clause. Use when writing, generating, or reviewing REVOKE statements (privileges, roles, or proxy) against MariaDB."4---56# REVOKE in MariaDB78*Last updated: 2026-07-20*910This skill covers the **delta between standard SQL `REVOKE` and MariaDB's**. It assumes the agent already knows the standard form (`REVOKE priv ON obj FROM grantee`). MariaDB's privilege model is symmetric with `GRANT` (see **`mariadb-grant`** for the full `priv_type`/`priv_level` catalog) but `REVOKE` has its own grammar quirks, error semantics, and a role-revocation path that has no standard-SQL equivalent.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| `REVOKE ALL PRIVILEGES, GRANT OPTION FROM ON *.* user` or otherwise adding `ON *.*` to the all-privileges form | The all-privileges form **takes no `ON` clause at all** — `REVOKE ALL PRIVILEGES, GRANT OPTION FROM user;`. Adding `ON *.*` is a syntax error; the grammar simply has no `ON` slot in that production |19| `REVOKE IF EXISTS priv ON obj FROM user` | **No `IF EXISTS` clause exists for `REVOKE`** — the grammar (`revoke:` rule) has no `IF EXISTS` token. Revoking a privilege/grant that doesn't exist at that exact level always errors (`ER_NONEXISTING_GRANT`, SQLSTATE 42000); there is no built-in idempotent form. Wrap in a stored routine with a handler, or check `SHOW GRANTS` first, if idempotency is needed |20| Assuming `REVOKE ALL ON *.* FROM user` removes every privilege | It removes everything **except `USAGE`** — the user keeps the ability to connect. To fully lock the account out, `DROP USER` it instead. The second form (`REVOKE ALL PRIVILEGES, GRANT OPTION FROM user`) is the same story — `USAGE` always survives |21| Revoking a table/column privilege at the wrong level (e.g. revoking a column-level grant with a table-level `REVOKE`) | The revoke's `ON` level must **match exactly** the level the privilege was granted at (global / db / table / column / routine). Mismatched levels raise `ER_NONEXISTING_GRANT`, not a silent no-op |22| `REVOKE role FROM user` when checking whether it "cascades" | It does — MariaDB's role system computes effective privileges by propagation through the role graph. Revoking a privilege *from a role* (or revoking a role *from another role*) immediately changes the effective privileges of every user/role that inherits it, with no re-grant needed |23| Forgetting `REVOKE role` needs the grantor to already hold `GRANT OPTION`-equivalent authority | Revoking privileges (form 1) requires `GRANT OPTION` on what's being revoked, same as `GRANT`; revoking the special ALL form requires the global `CREATE USER` privilege or `UPDATE` on the `mysql` database — a *different* requirement than form 1 |24| `REVOKE PROXY FROM user` (omitting `ON`) | `PROXY` revocation always needs the target: `REVOKE PROXY ON proxied_user FROM user_who_can_proxy` — mirrors `GRANT PROXY ON ... TO ...` in reverse |2526## The Five Forms2728```sql29-- 1. Revoke specific privileges at a given level30REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...31 ON [object_type] priv_level32 FROM account_or_role [, account_or_role] ...3334-- 2. Revoke everything (no ON clause — this is the special form)35REVOKE ALL [PRIVILEGES], GRANT OPTION36 FROM account_or_role [, account_or_role] ...3738-- 3. Revoke proxy privilege39REVOKE PROXY ON user_or_role40 FROM account_or_role [, account_or_role] ...4142-- 4. Revoke a role43REVOKE role [, role] ...44 FROM account_or_role [, account_or_role] ...4546-- 5. Revoke the ADMIN OPTION for a role (keeps the role itself)47REVOKE ADMIN OPTION FOR role [, role] ...48 FROM account_or_role [, account_or_role] ...49```5051`priv_type`/`priv_level`/`object_type` are identical to `GRANT` — see **`mariadb-grant`** for the full catalog (this includes MariaDB-only privileges like `BINLOG ADMIN`, `CONNECTION ADMIN`, `READ ONLY ADMIN`, `SET USER`, `SLAVE MONITOR`). `account_or_role` accepts `username`, `role`, `PUBLIC`, `CURRENT_USER[()]`, or `CURRENT_ROLE[()]`.5253## Privilege Requirements — Two Different Bars5455- **Form 1** (targeted privilege revoke): the executing user needs the `GRANT OPTION` privilege **and** must actually hold the privileges being revoked.56- **Form 2** (`REVOKE ALL PRIVILEGES, GRANT OPTION FROM ...`): needs the global `CREATE USER` privilege, *or* the `UPDATE` privilege on the `mysql` database — a distinct, broader requirement than form 1.5758## Errors and Mismatches5960- Revoking a privilege the grantee doesn't hold **at the specified level** raises `ER_NONEXISTING_GRANT` (SQLSTATE `42000`) — not a silent success. This applies to global/db/table/column/routine levels and to proxy revocation alike.61- There is **no `IF EXISTS` variant** of `REVOKE` — confirmed against the grammar (`sql/sql_yacc.yy`, the `revoke:` production). If a script needs to revoke-if-present, catch the error explicitly (e.g. in a stored procedure with a `DECLARE ... HANDLER`) rather than assuming an `IF EXISTS` keyword is accepted.62- `REVOKE ALL PRIVILEGES, GRANT OPTION FROM ...` never takes an `ON` clause; `ON *.*` there is a parse error because the production has no `ON` token, not a semantic restriction that can be worked around with different syntax.6364## Roles6566```sql67REVOKE journalist FROM hulda;68REVOKE ADMIN OPTION FOR journalist FROM hulda;69```7071- Revoking a role from a user or another role removes the membership edge; privilege propagation recomputes effective privileges for every downstream grantee immediately — no separate refresh step.72- Revoking a *privilege from a role* (via form 1, `ON ... FROM role_name`) similarly propagates: every user or role that has that role in its chain loses the privilege, without touching the role's other members' unrelated privileges.73- If the role being revoked was previously set as a session's **default role** (`SET DEFAULT ROLE`), `REVOKE` does **not** clear that default-role record in `mysql.user`. Re-granting the same role later makes it the default again automatically. Use `SET DEFAULT ROLE NONE` to actually clear it.7475## Proxy Revocation7677```sql78REVOKE PROXY ON 'dba_user'@'localhost' FROM 'app_user'@'localhost';79```8081Removes `app_user`'s ability to connect and execute as `dba_user`. Always requires the `ON` target — there's no bare "revoke all proxy" shorthand.8283## See Also8485- **`mariadb-grant`** — the mirrored `GRANT` privilege model, full `priv_type`/`priv_level` catalog, `WITH GRANT OPTION`86- **`mariadb-create-user`** — account creation/locking; `DROP USER` for fully removing an account (the only way to strip the residual `USAGE` privilege)87- Canonical references on `mariadb.com/docs` (consult only for edge cases not covered here):88 - <https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/revoke>89 - <https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant>9091<sub>_This page is: Copyright © 2026 MariaDB. All rights reserved._</sub>
mariadb-corporation