What does adding WITH GRANT OPTION to a privilege change, and what happens to the privileges that grantee handed out when you later revoke theirs?

## Grants are edges, not flags It is tempting to picture privileges as a checkbox per (role, object, action). The engine actually stores something richer: each privilege records **who granted it**. So the state is a graph whose nodes are roles and whose edges are 'grantor G gave privilege P on object O to grantee E, with or without the right to pass it on'. WITH GRANT OPTION is what makes the graph deeper than one level: it authorises the grantee to create new edges for the same privilege. Without it, the grantee can use the privilege but not spread it. ## Why the graph shape matters on REVOKE Suppose the owner grants SELECT to B with grant option, and B grants SELECT to C. C's privilege exists because B was entitled to give it. Remove B's privilege and C's edge has no valid basis -- an abandoned privilege. SQL gives two behaviours: - **RESTRICT** (the standard default): the REVOKE fails because dependent privileges exist. This is the safe default: it forces you to look at what you are about to break. - **CASCADE**: the engine removes the dependent privileges too, transitively. The revoke succeeds, and roles you never named lose access. The practical implication is that CASCADE on a widely re-granted privilege is a blast-radius event, and you should enumerate the dependants before running it. In an incident this is the right tool; in routine maintenance, RESTRICT plus a deliberate cleanup order is safer. ## Revoking only the grant option Sometimes the goal is 'you may keep reading, but stop giving this to other people'. The standard supports revoking the *grant option* alone: the grantee keeps the privilege and loses the right to propagate it. Note the wrinkle -- privileges the grantee already handed out are still dependants, so if any exist the same RESTRICT/CASCADE choice applies. ## Only your own edges A REVOKE removes edges whose grantor is you (or a role you are acting as). This produces one of the most common real-world surprises: an admin revokes a privilege, the target still has access, and nothing looks wrong. The reasons are almost always one of: - **Another grantor.** Someone else granted the same privilege independently. Two edges existed; you removed one. - **A role path.** The account inherits the privilege through a role it is a member of, so the direct revoke is irrelevant. - **A PUBLIC grant.** The privilege is held by everyone, and an individual revoke does not subtract from a group grant. - **Ownership.** Owners have implicit rights that are not modelled as grants at all, so revoking does nothing to them. A correct answer to 'I revoked it and they still have access' therefore starts by querying *effective* privileges and their sources, not by repeating the revoke. ## Lifecycle hazards - **Dropping a grantor role.** Every privilege that role granted is a dangling edge; engines generally refuse to drop a role that still owns objects or has granted privileges until those are reassigned or dropped. This is why role deletion in a mature system is a multi-step operation. - **Auditability.** Because the graph can be several edges deep, 'who can read this table' is a transitive-closure question. Any access review that reads only direct grants will under-report. - **Design guidance.** Grant option is rarely appropriate for application accounts. Keep propagation rights with owners and a small set of administrative roles, and give services flat, non-propagating grants; this keeps the graph one level deep, which makes both revocation and review tractable. ## Interview framing Strong answers say three things: grants have grantors, so revocation has dependants; RESTRICT versus CASCADE is a choice about whether to fail loudly or delete transitively; and revocation is scoped to your own edges, which is why access can survive a revoke.