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 |
