The REVOKE Statement
Introduction
The REVOKE statement is crucial for managing user privileges in SQL. It allows administrators to remove permissions that were previously granted. This ensures that users do not retain access that is no longer needed or should be restricted.
Syntax of the REVOKE Statement
The syntax for REVOKE varies depending on whether you are removing system privileges or object privileges.
Object Privileges
To revoke object privileges (such as access to a table or view), the general syntax is:
REVOKE privilege_type ON object_name FROM user_or_role;
Example:
To revoke the SELECT privilege on a table named employees from a user user1:
REVOKE SELECT ON employees FROM user1;
System Privileges
To revoke a system privilege (such as the ability to create users), the general syntax is:
REVOKE system_privilege FROM user_or_role;
Example:
To revoke the CREATE USER privilege from user1:
REVOKE CREATE USER FROM user1;
Details on Object Privileges
Object privileges include permissions such as SELECT, INSERT, UPDATE, DELETE, etc.
Privileges on a Table
Revoke all privileges on a specific table:
REVOKE ALL ON table_name FROM user_or_role;
Example:
To revoke all privileges on the employees table from user1:
REVOKE ALL ON employees FROM user1;
Privileges on a View
Revoke the SELECT privilege on a view:
REVOKE SELECT ON view_name FROM user_or_role;
Example:
To revoke the SELECT privilege on the view employee_view from user1:
REVOKE SELECT ON employee_view FROM user1;
Revocation and Cascade
Some databases support the CASCADE option when revoking privileges. This means that if a user has granted privileges to others, those privileges will also be revoked.
Example:
Revoking an object privilege with the CASCADE option in Oracle:
REVOKE privilege_type ON object_name FROM user_or_role CASCADE;
However, the CASCADE option might not be available in all database systems, so consult your database’s documentation for specific capabilities.
Implicit Privileges
When a privilege is revoked, it might not always affect indirectly granted privileges via roles or groups. Roles or groups with certain privileges might still allow an action even after direct revocation of those privileges.
Advanced Examples
Revoking Accumulated Privileges
If a user received privileges through a role, revoking those privileges might require revoking the role itself.
Example: To revoke a role that includes multiple privileges:
REVOKE role_name FROM user_or_role;
Revoking Privileges on Multiple Objects
To revoke privileges on multiple objects, you often need to issue a separate REVOKE statement for each object unless the database supports batch revocation.
Example: Revoke SELECT on multiple tables:
REVOKE SELECT ON table1, table2, table3 FROM user1;
Practical Considerations
- Audit and Verification: Before revoking privileges, verify what privileges are currently granted to avoid unintended disruptions.
- Selective Revocation: Use REVOKE carefully to ensure you are not removing necessary privileges for critical operations.