The idea: whoever holds a privilege may pass it on
In discretionary access control (DAC) the holder of a privilege decides who else gets it. SQL does this with two statements. GRANT gives a privilege, such as SELECT on a table, to an account. REVOKE takes it back. If the GRANT says WITH GRANT OPTION, the receiver may pass the privilege on in turn. Drawing who granted to whom gives a grant graph.
The hard question is what REVOKE should do to the grants that hang below the account you revoke. The SQL standard and most textbooks answer "cascade". MariaDB does not. This page shows both, and labels the textbook model clearly.
GRANT and WITH GRANT OPTION
GRANT SELECT ON shop.orders TO alice WITH GRANT OPTION; -- run by admin
GRANT SELECT ON shop.orders TO bob WITH GRANT OPTION; -- run by alice
GRANT SELECT ON shop.orders TO carol; -- run by bob
To run GRANT you must hold the privilege and the GRANT OPTION for it. carol got plain SELECT, so her own GRANT fails with ERROR 1142 (42000): GRANT command denied. She can still read the table (Demo: GRANT chain with GRANT OPTION). On the canvas, an arrow is a grant and +GO marks the grant option.
REVOKE in MariaDB: one row, no cascade
MariaDB keeps table privileges in mysql.tables_priv. The key is (Host, Db, User, Table_name): one row per account and table. Grantor is an ordinary column that holds the account of the latest GRANT. There is no table of "who gave what to whom", so there is no graph to follow.
| what happens | MariaDB |
|---|---|
| who may REVOKE | any account that holds the privilege and its GRANT OPTION, whoever granted it |
| what REVOKE deletes | the grantee's row (or the named privileges in it) |
| grants the grantee passed on | stay: bob keeps SELECT and his grant option after alice loses hers |
| two grantors, same account | one row, so one REVOKE removes the privilege completely |
| nothing to revoke | ERROR 1147: There is no such grant defined ... |
So REVOKE SELECT, GRANT OPTION ON shop.orders FROM alice removes alice's row and nothing else (Demo: MariaDB REVOKE does not cascade). And alice can revoke carol's SELECT even though dave was the last to grant it, because the row does not remember alice's grant anyway (Demo: two grantors, one row). MariaDB's REVOKE has no CASCADE or RESTRICT keyword. The manual does not describe any cascade; the behaviour follows from how grants are stored: one row per grantee, with no link back to the grants that account passed on.
The SQL standard model: REVOKE ... CASCADE
The SQL standard keeps one privilege descriptor per (grantor, grantee, privilege). A REVOKE removes only the descriptors the revoker itself granted. Then every descriptor whose grantor no longer holds a grantable privilege is abandoned. CASCADE deletes the abandoned ones, round after round, until none is left. RESTRICT refuses the REVOKE if anything would be abandoned. An account keeps the privilege while some path of grants still leads to it from the owner.
Demo: SQL standard REVOKE CASCADE, textbook runs the same five grants in this model. After admin revokes alice, two rounds remove alice → bob and bob → carol. carol keeps SELECT through dave. Use the Options menu to try your own statements in either model.
Roles
CREATE ROLE analyst;
GRANT SELECT ON shop.orders TO analyst;
GRANT analyst TO carol, dave;
SET ROLE analyst; -- run by carol; SET DEFAULT ROLE does it at login
A role is a named bundle of privileges. Grant the privileges to the role once, then grant the role to accounts. In MariaDB a role's privileges count only after SET ROLE: before it carol gets ERROR 1142, after it her SELECT works. REVOKE SELECT ON shop.orders FROM analyst takes the privilege from every member at once (Demo: a role, SET ROLE, revoke from the role). How roles compare with attribute rules is on RBAC vs ABAC.
A view as a security window
A view can hide rows and columns. By default a MariaDB view runs with SQL SECURITY DEFINER: the server checks the base tables against the view's definer, not against the caller. So dave needs SELECT only on the view, and he sees only what the view selects.
This is also the one place where a REVOKE in MariaDB reaches past its target. Revoke alice's SELECT on orders and dave's grant on the view is still there, but the view now fails for him with ERROR 1356 ... definer/invoker of view lack rights to use them (Demo: a view as a window, then revoke its definer). How the optimizer expands a view into the query is on subquery and view merging.
In practice
- Before you remove an account's access, run
SHOW GRANTS FORon it and on the accounts it may have granted to. MariaDB will not clean those up for you. - Give
WITH GRANT OPTIONrarely. Prefer roles managed by a few admins. - To expose part of a table, grant SELECT on a view, not on the table.
DAC lets owners decide. When the system itself must stop information from flowing down, by labels the owner cannot change, that is mandatory access control: see Bell-LaPadula and Biba. When an app forgets these checks altogether, see OWASP broken access control.
See also MariaDB triggers and stored procedures for the code that the TRIGGER, CREATE ROUTINE and EXECUTE privileges control.
What the page leaves out
Only SELECT on one table is shown. Global (mysql.global_priv), database (mysql.db) and column privileges work the same way at other levels; a privilege still covered by a broader grant stays after the narrower one is revoked. Accounts are all 'name'@'%'; host patterns, CREATE USER, passwords and WITH ADMIN OPTION for roles are not drawn. Changes to global privileges reach only new connections. The SQL standard's RESTRICT and its rules for owner-defined views and dependent objects are not available in MariaDB and are not animated.