A login account is a member of role A, which is itself a member of role B, and B holds SELECT on a table. How does the engine decide whether that login may read the table, and could a later grant ever take access away?

## Roles as a graph A role in a relational engine is both a subject (something that can hold privileges) and a group (something other roles can be members of). Membership edges form a directed graph, usually acyclic because engines refuse to create membership cycles. When a session runs a statement, the engine needs the **effective privilege set** for the current authorization identifier. It computes it as the union of: 1. privileges granted directly to the account; 2. privileges granted to every role reachable from the account through membership edges, transitively -- so membership in A that is a member of B pulls in B's privileges; 3. privileges granted to PUBLIC, which every role holds implicitly; 4. implicit rights the account has as the **owner** of an object, which are not stored as grants at all. If the required privilege appears in that set, the statement proceeds. ## Inheritance versus assumption There are two flavours of membership and they behave differently: - **Inheriting membership**: the member automatically uses the role's privileges in every statement. Convenient, and the usual choice for grouping application permissions. - **Non-inheriting membership**: the member may *become* the role via a role-switch statement, but does not use its privileges otherwise. This is the model behind 'you have admin rights but must explicitly step into them', and it is valuable for privileged roles because it makes elevation deliberate and auditable. An interviewer probing this will ask why you would ever want the second. The answer is blast-radius control: a compromised or careless session running as the login role cannot accidentally exercise the powerful role's privileges without an explicit act. ## Additive semantics: no DENY Core SQL privileges are monotone: every grant adds, nothing subtracts. That has several consequences worth stating: - You cannot carve an exception into a broad grant. If a role can read a table, you cannot deny it one column by adding something; you must revoke the broad grant and re-grant narrowly. - Adding a role membership for convenience is irreversible in effect until someone revokes it -- there is no override to fence it. - Some engines add an explicit DENY that overrides grants, which changes the algebra (precedence rules, deny-wins) and is a vendor-specific extension rather than the portable model. Row-level policies are also a separate mechanism layered on top rather than a negative grant. ## Practical consequences **Review is a reachability problem.** Answering 'which accounts can read the customers table' means walking the membership graph backwards from every role holding the privilege, and remembering PUBLIC and ownership. Tools and hand-written queries that list direct grants systematically under-report. Any organisation with nested roles needs a canonical query or report that computes the closure. **Chains drift.** Nesting roles is how privilege creep happens: a role created for one purpose is made a member of a broader role 'temporarily', and everything downstream silently gains the broader set. Depth is the enemy; two levels (a group role per data domain, service accounts as members) keeps the closure readable. **Timing.** Privilege changes generally take effect for statements executed after the change, but a session already inside a transaction, or an execution plan already prepared, may not observe the change immediately. If you are cutting off access urgently, revoke *and* terminate the sessions. ## Answering well Say the algorithm (transitive union, plus PUBLIC, plus ownership), name the inheritance-versus-assumption distinction, state that the model is grant-only so a new grant cannot revoke, and land on the operational consequence: access reviews must compute the closure, and revocation must target the right edge in the graph.