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.

Left: a grant graph. admin grants SELECT with grant option to alice and to dave, alice to bob with grant option, bob and dave both grant plain SELECT to carol. Right: MariaDB's mysql.tables_priv holds only four rows, alice, dave and bob with Select,Grant and carol with Select and Grantor dave.
Five GRANT statements, but MariaDB keeps one row per account and table: carol has one row, whoever granted it.

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 happensMariaDB
who may REVOKEany account that holds the privilege and its GRANT OPTION, whoever granted it
what REVOKE deletesthe grantee's row (or the named privileges in it)
grants the grantee passed onstay: bob keeps SELECT and his grant option after alice loses hers
two grantors, same accountone row, so one REVOKE removes the privilege completely
nothing to revokeERROR 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.

The same grant graph after admin revokes SELECT from alice. Left, MariaDB: alice loses it; bob, carol and dave keep it, and the arrow from alice to bob stays. Right, SQL standard CASCADE: the admin to alice descriptor is removed, alice to bob is abandoned in round 1, bob to carol in round 2; bob loses SELECT, carol keeps it through dave.
Same REVOKE, two results: MariaDB takes it from alice only; the standard CASCADE also takes it from bob, but not from carol.

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.

The orders table with four rows; the view v_eu, defined by alice with SQL SECURITY DEFINER, shows only the EU rows 1 and 3 and only the columns id and amount. dave, who has SELECT only on v_eu, gets 2 rows: (1, 120) and (3, 45).
dave reads 2 of 4 rows and 2 of 4 columns through the view, and nothing through the table.

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 FOR on it and on the accounts it may have granted to. MariaDB will not clean those up for you.
  • Give WITH GRANT OPTION rarely. 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.

On the mind map

Open the Web & Security mind mapWeb & SecurityCrypto building blocks: keys, hashes, signaturesCrypto building blocksStoring passwords: slow salted hash, never plaintextStoring passwordsBrowser rules: which request carries whatBrowser rulesRequest attacks: trick a browser or a serverRequest attacksLogin & identity: prove who you areLogin & identityOWASP Top 10 2025: common flaws, then the fixOWASP Top 10 2025OWASP: supply chain & ops: dependencies, logs and error pathsOWASP: supply chain & opsOAuth 2.0 with PKCEOAuth 2.0 with PKCEapp gets a scoped token, not the passwordJWT vs session cookieJWT vs session cookieJWT: signed, stateless, hard to revokeKerberosKerberosTGT from the KDC, then a ticket per serviceMutual TLSMutual TLSboth sides show and prove a certificateEmail SPF, DKIM, DMARCEmail SPF, DKIM, DMARCDMARC: SPF or DKIM must align with From:TOTP two-factor codesTOTP two-factor codesHMAC(secret, time/30s) → 6 digitsRBAC vs ABACRBAC vs ABACRoles grant rights; attributes decide liveBell-LaPadula and BibaBell-LaPadula and BibaNo read up, no write down; Biba flips itGRANT and REVOKEMariaDB REVOKE never cascadesOpen the Web & Security mind mapWeb & Securityprove who you areLogin & identityOAuth 2.0 with PKCEOAuth 2.0 with PKCEapp gets a scoped token, not the passwordJWT vs session cookieJWT vs session cookieJWT: signed, stateless, hard to revokeKerberosKerberosTGT from the KDC, then a ticket per serviceMutual TLSMutual TLSboth sides show and prove a certificateEmail SPF, DKIM, DMARCEmail SPF, DKIM, DMARCDMARC: SPF or DKIM must align with From:TOTP two-factor codesTOTP two-factor codesHMAC(secret, time/30s) → 6 digitsRBAC vs ABACRBAC vs ABACRoles grant rights; attributes decide liveBell-LaPadula and BibaBell-LaPadula and BibaNo read up, no write down; Biba flips itGRANT and REVOKEMariaDB REVOKE never cascades
This page on the Web & Security mind map · 37 pages in 7 branches · open it to see the whole map and test yourself in recall mode.