SQL REVOKE: Safely Taking Back Granted Privileges

REVOKE is the mirror image of GRANT: it takes back privileges you previously handed to a user or role. In practice, revoking is trickier than granting, because the same access can arrive through several paths at once.

Basic syntax

To take back a specific privilege on a table, name the privilege, the object, and the grantee:

REVOKE INSERT ON orders FROM analyst;

You can revoke several privileges at once and target specific columns:

REVOKE SELECT, UPDATE ON users FROM analyst;
REVOKE UPDATE (salary) ON employees FROM analyst;
REVOKE ALL PRIVILEGES ON orders FROM analyst;

The key principle: REVOKE only removes what was granted directly to that grantee. If the access came from somewhere else, the statement still succeeds without error, but the effective access stays put.

CASCADE vs RESTRICT

If a privilege was granted WITH GRANT OPTION, the recipient could pass it along. When you revoke, PostgreSQL has to decide what happens to those downstream grants.

• RESTRICT (the default) refuses the revoke if other grants depend on this one.
• CASCADE removes the privilege and every grant that descends from it.

GRANT SELECT ON orders TO manager WITH GRANT OPTION;
-- manager passed it on:
GRANT SELECT ON orders TO analyst;

-- This fails because manager re-granted the right:
REVOKE SELECT ON orders FROM manager RESTRICT;

-- This removes manager's grant AND analyst's:
REVOKE SELECT ON orders FROM manager CASCADE;

You can also revoke just the ability to re-grant, while leaving the access itself intact:

REVOKE GRANT OPTION FOR SELECT ON orders FROM manager CASCADE;

Gotcha: CASCADE can wipe out access for accounts you never thought about. Before revoking with CASCADE, inspect the grant chain in information_schema.role_table_grants, or you may break someone's dashboard or ETL job.

Why PUBLIC may still have access

The most common surprise: you revoke a privilege from a user, and they keep reading the table anyway. The cause is the PUBLIC pseudo-role, which every role in the cluster implicitly belongs to. If the privilege was granted to PUBLIC, revoking it from one user changes nothing.

-- Someone granted broad access earlier:
GRANT SELECT ON users TO PUBLIC;

-- This looks right but analyst still reads users:
REVOKE SELECT ON users FROM analyst;

-- You must revoke from PUBLIC itself:
REVOKE SELECT ON users FROM PUBLIC;

Watch out for owner privileges too: the table owner and superusers keep full access regardless of REVOKE. You cannot strip their access with a plain revoke.

Role vs user

Handing out access through group roles is convenient, but it complicates revoking. If the user analyst is a member of the analytics_team role, revoking a privilege from analyst personally does nothing to what they inherit through the role.

GRANT SELECT ON orders TO analytics_team;
GRANT analytics_team TO analyst;

-- No effect: analyst gets SELECT via the role, not directly:
REVOKE SELECT ON orders FROM analyst;

-- Option A: revoke from the role (affects everyone in it):
REVOKE SELECT ON orders FROM analytics_team;

-- Option B: remove this user from the role:
REVOKE analytics_team FROM analyst;

MySQL follows a similar model, but it does not support REVOKE ... CASCADE/RESTRICT, and roles only arrived in 8.0; before that, privileges were assigned directly to accounts. ClickHouse supports REVOKE and roles, but its privileges and syntax differ noticeably from standard SQL.

Tightening permissions safely

• Check the effective access first: read information_schema.role_table_grants and walk the role memberships before you revoke anything.
• Do not forget PUBLIC — for sensitive tables, explicitly run REVOKE ... FROM PUBLIC.
• Prefer RESTRICT (or simply omit CASCADE) so the statement fails loudly when dependent grants exist and you see the problem in advance.
• Revoke at the same level you granted: revoke a role from a role, a direct grant from a user.
• Wrap a series of REVOKE statements in a transaction and verify with a SELECT as the target user before you commit.