SQL REVOKE

What is SQL REVOKE?

SQL REVOKE withdraws previously granted privileges or roles from users or roles.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL REVOKE:

SQL REVOKE Full Explanation

SQL REVOKE is a Data Control Language (DCL) command that removes permissions granted with the GRANT statement. Once executed, the targeted grantee (user, role, or public) immediately loses the specified privileges, preventing future operations that rely on those rights. REVOKE can operate at multiple scopes - entire databases, schemas, tables, columns, sequences, views, functions, procedures, or even server-level capabilities depending on the dialect. Most implementations support cascading effects: if a grantee had handed off the same privilege to others WITH GRANT OPTION, those dependent grants are also removed unless the syntax explicitly chooses RESTRICT. Because REVOKE changes security metadata, it commits implicitly in many systems and cannot be rolled back inside a transaction in some databases (notably MySQL). Administrators should audit dependent objects and active sessions after revocation, since long-running transactions may still hold locks or open cursors created before the privilege was revoked.

SQL REVOKE Syntax

REVOKE privilege_list
       ON object_name
       FROM grantee_list
       [CASCADE | RESTRICT];

-- revoke a role (where supported)
REVOKE role_name FROM grantee_list;

SQL REVOKE Parameters

privilege_list (list) - One or more privileges such as SELECT, INSERT, UPDATE, DELETE, REFERENCES, EXECUTE, USAGE, ALL.
object_name (identifier) - Database object the privilege applies to (table, view, sequence, function, etc.).
grantee_list (list) - User names, roles, or PUBLIC keyword.
CASCADE (keyword) - Automatically revoke any dependent privileges granted by the grantee. Default in many systems.
RESTRICT (keyword) - Fail if dependent grants exist, preventing accidental privilege chain breakage.

Example Queries Using SQL REVOKE

-- Remove SELECT on a single table from a specific user
REVOKE SELECT ON sales.orders FROM analyst1;

-- Remove INSERT and UPDATE across an entire schema
REVOKE INSERT, UPDATE ON SCHEMA reporting FROM role_data_entry;

-- Revoke a role assignment in PostgreSQL
REVOKE reporting_reader FROM analyst_team;

-- Revoke all privileges the user gave others (cascade is implicit in MySQL)
REVOKE ALL PRIVILEGES ON *.* FROM 'temp_user'@'localhost';

Expected Output Using SQL REVOKE

The specified user or role immediately loses the listed privileges
Subsequent attempts to execute the revoked operation will raise a permission error such as "ERROR: permission denied for table orders"

Use Cases with SQL REVOKE

Remove temporary access granted to contractors after project completion
Lock down sensitive tables before a data migration
Enforce least-privilege by stripping unused rights discovered during audits
Roll back mistakenly granted WITH GRANT OPTION privileges
Revoke a role when an employee changes departments

Common Mistakes with SQL REVOKE

Forgetting that REVOKE may implicitly commit, causing unintended transaction boundaries
Using RESTRICT when dependent grants exist, which triggers an error instead of completing
Assuming active sessions instantly lose access; some engines only enforce on next statement
Neglecting to revoke column-level privileges when object-level rights are removed
Thinking REVOKE can undo GRANT OPTION hand-offs without CASCADE where required

First Introduced In

SQL-92 Core Standard

Frequently Asked Questions

What happens to existing sessions after a REVOKE?

The new privilege rules take effect on the next statement each session executes. A connection that already fetched data keeps that data but cannot issue new commands requiring the revoked right.

How do CASCADE and RESTRICT differ?

CASCADE removes dependent privileges granted by the affected user, ensuring no orphaned permissions remain. RESTRICT prevents the REVOKE if such dependencies exist, forcing you to clean them up manually first.

Can I revoke specific column privileges?

Yes in databases like PostgreSQL and Oracle. Use syntax such as `REVOKE SELECT(col1, col2) ON table_name FROM user1;`.

Does REVOKE affect default privileges?

No. ALTER DEFAULT PRIVILEGES defines future grants. You must issue a separate REVOKE DEFAULT PRIVILEGES command (or its dialect equivalent) to change them.