DROP USER (and RENAME USER) in MariaDB
Last updated: 2026-07-20
This skill covers the MariaDB-specific behavior of DROP USER and, briefly, the related RENAME USER statement. It assumes the agent already knows the standard-SQL notion of dropping/renaming a database principal. The traps here are almost all about what DROP USER doesn't do — it does not touch active sessions, owned objects, or privileges the dropped user granted to others.
Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Only newer-than-10.6 features carry a *(since X.Y)* annotation.
What LLMs Often Miss
| If the agent writes / suggests… |
…prefer the MariaDB form |
Assuming DROP USER kills the user's open connections |
It does not. A dropped account with an active session keeps working, with all its privileges, until that connection disconnects — MariaDB only blocks new connections and emits a note-level warning: Dropped users '...' have active connections. Use KILL CONNECTION if they should not be used anymore. To actually cut it off, run KILL CONNECTION <id> for each of the account's live sessions |
Writing a loop issuing one DROP USER per account |
DROP USER takes a comma-separated list in a single statement: DROP USER 'u1'@'%', 'u2'@'%', 'u3'@'%'; — one statement, one binlog event, one round trip |
DROP USER 'bob'@'%'; in a script where the account might not exist |
Add IF EXISTS — without it, a missing account raises ERROR 1396 (HY000): Operation DROP USER failed for '...'. With IF EXISTS, a missing account produces a note, not an error, so idempotent teardown scripts don't fail |
| Expecting a dropped user's tables/views/procedures to disappear too |
DROP USER never cascades onto objects the user owns — those objects are untouched and now ownerless. It only removes rows from the grant tables (mysql.user, mysql.db, mysql.tables_priv, etc.) |
| Expecting revocation of privileges the dropped user had granted to other users |
Not revoked. If alice ran GRANT SELECT ON t TO bob, dropping alice leaves bob's SELECT on t intact — there is no cascade on grants made by the account |
Dropping a user that's the DEFINER of a view, stored routine, or trigger |
The object is left behind with a now-nonexistent definer. It keeps working for SELECT/schema purposes in most cases, but invoking it (routine call, SQL SECURITY DEFINER context, etc.) fails with The user specified as a definer ('user'@'host') does not exist. Re-point the DEFINER (e.g. ALTER VIEW ... DEFINER = ...) before dropping the account, not after |
| Renaming a user and assuming privileges need to be re-granted |
RENAME USER old TO new preserves the account's existing privileges — they follow the renamed account; no re-GRANT needed |
Checking for active connections before RENAME USER |
Not necessary and not done by the server — RENAME USER, unlike DROP USER, does not check for or warn about active sessions of the account being renamed |
DROP USER — Syntax and Behavior
DROP USER [IF EXISTS] user_name [, user_name] ...
- A connected account is still deleted from the grant tables, but a note-level warning is issued and the live session keeps its privileges until it disconnects on its own. Run
KILL CONNECTION <id> against each of the account's sessions if you need to end them immediately.
- In
sql_mode=ORACLE, dropping a currently-connected user fails outright with Operation DROP USER failed for '...', instead of the warn-and-proceed behavior used in the default mode.
- Each
user_name uses the same 'user'@'host' form as CREATE USER; a bare user name implies @'%'.
- Multiple accounts in one statement are processed together: if some fail (don't exist, and
IF EXISTS wasn't given) while others succeed, the successful drops still commit, and a single ERROR 1396 lists all the failed names together.
- Privilege required: the global
CREATE USER privilege, or the DELETE privilege on the mysql database.
-- Idempotent teardown:
DROP USER IF EXISTS 'svc_batch'@'%', 'svc_report'@'%';
RENAME USER
RENAME USER old_user TO new_user [, old_user TO new_user] ...
- Renames one or more accounts in place; privileges are attached to the same underlying account record, so they carry over to the new name automatically.
- If any
old_user doesn't exist, or any new_user already exists, ERROR 1396 (HY000) results — same partial-success semantics as DROP USER: pairs that don't error still get renamed.
- Also usable to change just the host part:
RENAME USER 'foo'@'1.2.3.4' TO 'foo'@'10.20.30.40';
- Privilege required: the global
CREATE USER privilege, or the UPDATE privilege on the mysql database.
- For changing other account attributes (password,
REQUIRE, resource limits) without changing the name, use ALTER USER, not RENAME USER.
See Also
mariadb-create-user — account creation, IDENTIFIED BY/auth plugins, resource limitsmariadb-grant — privilege levels, GRANT ... TO, CREATE USER vs. DELETE/UPDATE on mysql- Canonical references on
mariadb.com/docs:
This page is: Copyright © 2026 MariaDB. All rights reserved.
1---2name: mariadb-drop-user3description: Explains MariaDB-specific behavior of DROP USER and RENAME USER, including multiple accounts per statement, IF EXISTS handling, active-connection warnings, and effects on privileges and definers. Use when writing or reviewing account-lifecycle SQL for MariaDB.4---56# DROP USER (and RENAME USER) in MariaDB78*Last updated: 2026-07-20*910This skill covers the MariaDB-specific behavior of `DROP USER` and, briefly, the related `RENAME USER` statement. It assumes the agent already knows the standard-SQL notion of dropping/renaming a database principal. The traps here are almost all about what `DROP USER` *doesn't* do — it does not touch active sessions, owned objects, or privileges the dropped user granted to others.1112> **Default context:** Assume MariaDB **11.8 LTS** (GA May 2025) unless the user states another version. Only newer-than-10.6 features carry a `*(since X.Y)*` annotation.1314## What LLMs Often Miss1516| If the agent writes / suggests… | …prefer the MariaDB form |17|---|---|18| Assuming `DROP USER` kills the user's open connections | It does **not**. A dropped account with an active session keeps working, with all its privileges, until that connection disconnects — MariaDB only blocks *new* connections and emits a note-level warning: `Dropped users '...' have active connections. Use KILL CONNECTION if they should not be used anymore.` To actually cut it off, run `KILL CONNECTION <id>` for each of the account's live sessions |19| Writing a loop issuing one `DROP USER` per account | `DROP USER` takes a comma-separated list in a single statement: `DROP USER 'u1'@'%', 'u2'@'%', 'u3'@'%';` — one statement, one binlog event, one round trip |20| `DROP USER 'bob'@'%';` in a script where the account might not exist | Add `IF EXISTS` — without it, a missing account raises `ERROR 1396 (HY000): Operation DROP USER failed for '...'`. With `IF EXISTS`, a missing account produces a **note**, not an error, so idempotent teardown scripts don't fail |21| Expecting a dropped user's tables/views/procedures to disappear too | `DROP USER` never cascades onto objects the user owns — those objects are untouched and now ownerless. It only removes rows from the grant tables (`mysql.user`, `mysql.db`, `mysql.tables_priv`, etc.) |22| Expecting revocation of privileges the dropped user had granted to *other* users | Not revoked. If `alice` ran `GRANT SELECT ON t TO bob`, dropping `alice` leaves `bob`'s `SELECT` on `t` intact — there is no cascade on grants made *by* the account |23| Dropping a user that's the `DEFINER` of a view, stored routine, or trigger | The object is left behind with a now-nonexistent definer. It keeps working for `SELECT`/schema purposes in most cases, but **invoking** it (routine call, `SQL SECURITY DEFINER` context, etc.) fails with `The user specified as a definer ('user'@'host') does not exist`. Re-point the `DEFINER` (e.g. `ALTER VIEW ... DEFINER = ...`) *before* dropping the account, not after |24| Renaming a user and assuming privileges need to be re-granted | `RENAME USER old TO new` preserves the account's existing privileges — they follow the renamed account; no re-`GRANT` needed |25| Checking for active connections before `RENAME USER` | Not necessary and not done by the server — `RENAME USER`, unlike `DROP USER`, does not check for or warn about active sessions of the account being renamed |2627## `DROP USER` — Syntax and Behavior2829```sql30DROP USER [IF EXISTS] user_name [, user_name] ...31```3233- A connected account is still deleted from the grant tables, but a note-level warning is issued and the live session keeps its privileges until it disconnects on its own. Run `KILL CONNECTION <id>` against each of the account's sessions if you need to end them immediately.34- In `sql_mode=ORACLE`, dropping a currently-connected user fails outright with `Operation DROP USER failed for '...'`, instead of the warn-and-proceed behavior used in the default mode.35- Each `user_name` uses the same `'user'@'host'` form as `CREATE USER`; a bare user name implies `@'%'`.36- Multiple accounts in one statement are processed together: if some fail (don't exist, and `IF EXISTS` wasn't given) while others succeed, the successful drops still commit, and a single `ERROR 1396` lists all the failed names together.37- Privilege required: the global `CREATE USER` privilege, **or** the `DELETE` privilege on the `mysql` database.3839```sql40-- Idempotent teardown:41DROP USER IF EXISTS 'svc_batch'@'%', 'svc_report'@'%';42```4344## `RENAME USER`4546```sql47RENAME USER old_user TO new_user [, old_user TO new_user] ...48```4950- Renames one or more accounts in place; privileges are attached to the same underlying account record, so they carry over to the new name automatically.51- If any `old_user` doesn't exist, or any `new_user` already exists, `ERROR 1396 (HY000)` results — same partial-success semantics as `DROP USER`: pairs that don't error still get renamed.52- Also usable to change just the host part: `RENAME USER 'foo'@'1.2.3.4' TO 'foo'@'10.20.30.40';`53- Privilege required: the global `CREATE USER` privilege, **or** the `UPDATE` privilege on the `mysql` database.54- For changing other account attributes (password, `REQUIRE`, resource limits) without changing the name, use `ALTER USER`, not `RENAME USER`.5556## See Also5758- **`mariadb-create-user`** — account creation, `IDENTIFIED BY`/auth plugins, resource limits59- **`mariadb-grant`** — privilege levels, `GRANT ... TO`, `CREATE USER` vs. `DELETE`/`UPDATE` on `mysql`60- Canonical references on `mariadb.com/docs`:61 - <https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/drop-user>62 - <https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/rename-user>63 - <https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/create-user>64 - <https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant>6566<sub>_This page is: Copyright © 2026 MariaDB. All rights reserved._</sub>
mariadb-corporation