In SQL, which REVOKE keyword makes it fail when team_lead has passed SELECT on orders to others, and what are both forms?
answer
- two keywords at the end of the statement
- one refuses, one goes further
- what PostgreSQL assumes when neither appears
- not every engine honours both
basics
~10 sRESTRICT makes it fail: REVOKE SELECT ON orders FROM team_lead RESTRICT;. CASCADE also removes what team_lead passed on: REVOKE SELECT ON orders FROM team_lead CASCADE;. PostgreSQL assumes RESTRICT when neither is written.
solid answer
~50 s`RESTRICT` makes it fail. Both keywords go last: `REVOKE SELECT ON orders FROM team_lead RESTRICT;` refuses while privileges passed on by `team_lead` exist, and `REVOKE SELECT ON orders FROM team_lead CASCADE;` removes those as well. PostgreSQL's reference says the SQL standard requires one of the two, while PostgreSQL assumes `RESTRICT`, so a bare revoke behaves the same way there. `RESTRICT` fails only when dependent privileges exist; without any, it simply revokes. `CASCADE` here removes privileges, not tables or rows, unlike `CASCADE` on `DROP TABLE`. Engines do not all honour the pair. SQL Server has `CASCADE` and no `RESTRICT`, MySQL's `REVOKE` has neither, and Oracle's `CASCADE CONSTRAINTS` is a different clause about foreign keys. So the safe habit where `RESTRICT` exists is to try it first: its failure tells you dependants exist before you decide to go further.
code
sql · 5 linesREVOKE SELECT ON orders FROM team_lead RESTRICT;
REVOKE SELECT ON orders FROM team_lead CASCADE;
REVOKE GRANT OPTION FOR SELECT ON orders FROM team_lead CASCADE;go deeper
Recall that the keywords go last, and which one refuses. Write the same revoke twice, once with RESTRICT and once with CASCADE, and say what each does when nothing was passed on.
State what PostgreSQL does with neither keyword and what the standard requires. Explain that RESTRICT fails only when dependent privileges exist, and that CASCADE here touches privileges, not objects.
Show the habit of trying the refusing form first and reading its failure before going further. Know which engines lack the keywords, so that a script moved between them does not change meaning unnoticed.
Set the standard for revoke scripts: whether CASCADE needs a second reviewer, and how offboarding handles failures. Weigh the extra friction against the risk of quietly removing access that someone else depends on.
A role that holds a privilege `WITH GRANT OPTION` can pass it on, and the privileges it hands out are called **dependent privileges**. The `REVOKE` statement has two keywords for the case where those exist. This answer covers those keywords as syntax: where they go, which engines accept them and what each one does to the statement's outcome. How far a revoke reaches and why it works that way is a matter of engine semantics, covered with access control. ## The two forms Both keywords go at the very end of the statement: ```sql REVOKE SELECT ON orders FROM team_lead RESTRICT; REVOKE SELECT ON orders FROM team_lead CASCADE; ``` - **`RESTRICT`** refuses to run if dependent privileges exist. The statement fails, and nothing changes. - **`CASCADE`** runs anyway and removes the dependent privileges too. In PostgreSQL the failure reads `dependent privileges exist`, with a hint to use `CASCADE`. `RESTRICT` is not a blanket refusal: if `team_lead` holds the privilege but has passed it to nobody, the same statement runs and revokes it. There is one keyword per statement, not one per privilege: `REVOKE SELECT, INSERT ON orders FROM team_lead, bob CASCADE;`. PostgreSQL's synopsis gives the same final slot to its other revoke forms, so `REVOKE SELECT (email) ON customers FROM team_lead CASCADE;` and `REVOKE reporting FROM team_lead CASCADE;` are both valid. ## What you get when you write neither PostgreSQL's `REVOKE` reference has the exact wording: the keyword `RESTRICT` or `CASCADE` is required according to the standard, but PostgreSQL assumes `RESTRICT` by default. Its description says the same thing from the other side: dependent privileges are revoked if `CASCADE` is specified, and if it is not, the revoke action will fail. So these two statements behave the same in PostgreSQL: ```sql REVOKE SELECT ON orders FROM team_lead; REVOKE SELECT ON orders FROM team_lead RESTRICT; ``` Writing `RESTRICT` out still earns its place. It says the failure is intended, and it matches what the standard asks for. ## Engines do not agree on these keywords | Engine | `CASCADE` | `RESTRICT` | Written with neither | |---|---|---|---| | PostgreSQL | Yes | Yes | Behaves as `RESTRICT` | | SQL Server | Yes | No | Refused when the grantee holds the privilege with grant option | | Oracle | No plain form | No | An object privilege revoke goes ahead and carries through | | MySQL | No | No | The revoke goes ahead | Three consequences follow for anyone writing a script: 1. A `REVOKE ... RESTRICT` that works on PostgreSQL is rejected by SQL Server, Oracle and MySQL. 2. A script without either keyword can fail on one engine and succeed on another with very different results, so read the table as a warning against assuming. 3. SQLite has no `REVOKE` at all. ## Not the same word as elsewhere `CASCADE` appears in other statements and means different things, which is where confusion comes from: - `DROP TABLE orders CASCADE;` in PostgreSQL removes dependent **objects**, such as views on the table. - `REVOKE ... CASCADE` in PostgreSQL removes dependent **privileges**. No table, view or row is dropped by it. - Oracle's `REVOKE ... CASCADE CONSTRAINTS` removes foreign key constraints that the grantee defined using the `REFERENCES` privilege. That is unrelated to passing a privilege on. ## Combining with GRANT OPTION FOR `CASCADE` and `RESTRICT` also apply when you revoke only the option. PostgreSQL's reference says that the rule covers "the privilege or the grant option": ```sql REVOKE GRANT OPTION FOR SELECT ON orders FROM team_lead CASCADE; ``` `team_lead` keeps `SELECT` and loses the right to pass it on, and the privileges they handed out are removed. Written without `CASCADE`, that statement fails in PostgreSQL if any such privileges exist. It is the form to use when someone should keep reading but stop being a source of access. ## A working habit Where `RESTRICT` exists, try it first: - Run the `RESTRICT` form, or a bare revoke on PostgreSQL. - If it succeeds, you are done, and nothing else was touched. - If it fails, look at who holds the dependent privileges before deciding whether `CASCADE` is what you mean. The failure is cheap information. `CASCADE` written by reflex skips it.
- In PostgreSQL, why write RESTRICT if a bare REVOKE already behaves that way?PostgreSQL's reference says the standard requires one of the two keywords and PostgreSQL merely assumes `RESTRICT`. Writing it records that the failure is intended, and tells a reviewer that dependent privileges were considered. It changes nothing about the outcome on PostgreSQL.
- In PostgreSQL, how do you stop team_lead passing SELECT on, keep their own SELECT, and also clear what they handed out?Combine the option-only form with the keyword: `REVOKE GRANT OPTION FOR SELECT ON orders FROM team_lead CASCADE;`. The first clause keeps their privilege, and `CASCADE` removes the dependent privileges. Without `CASCADE` it fails if any exist.
- A migration has REVOKE SELECT ON orders FROM team_lead RESTRICT and must also run on SQL Server. What changes?Drop `RESTRICT`, because SQL Server's `REVOKE` does not accept that keyword. Written bare, SQL Server refuses if `team_lead` holds the privilege with grant option, and asks for `CASCADE`. That refusal is close to PostgreSQL's without being the same, so the two engines need separate statements.
- Assumes a bare REVOKE in PostgreSQL silently removes what the grantee passed on.
- Thinks RESTRICT always makes REVOKE fail, even when no dependent privileges exist.
- Reads CASCADE on REVOKE as dropping tables or views, as it can on DROP.
- Expects REVOKE ... RESTRICT to parse on every engine that supports REVOKE.
- Places CASCADE straight after REVOKE, instead of at the end of the statement.
- Reads Oracle's CASCADE CONSTRAINTS as the same clause as PostgreSQL's CASCADE.