REVOKE

The SQL REVOKE command is used to remove or withdraw permissions from users or roles, which were previously granted using the GRANT command. It is an essential tool for managing database security and ensuring that users have only the necessary privileges.

In this guide, we will explore the various ways to use the REVOKE command and explain the internal processes that occur when it is executed.

What is the REVOKE Command in SQL?

The REVOKE command is part of SQL’s Data Control Language (DCL) and is used to remove access privileges from a user or role. These privileges may include SELECT, INSERT, UPDATE, or DELETE permissions, as well as the ability to execute stored procedures or grant permissions to others.

Different Methods of Using the REVOKE Command

1. Revoking Specific Permissions

You can revoke specific permissions on an object from a user.

Syntax:

REVOKE privilege ON object FROM user;

Example:

REVOKE SELECT ON employees FROM john_doe;

What Happens Internally:

1. Privilege Validation: The database checks whether the specified privilege exists for the user.
2. Access Control Update: The system modifies the internal permissions catalog to remove the SELECT privilege for john_doe on the employees table.
3. Confirmation: The privilege is revoked, and the user no longer has access to the specified operation on the object.

2. Revoking Multiple Permissions

You can revoke multiple privileges at once for a user.

Syntax:

REVOKE privilege1, privilege2, ... ON object FROM user;

Example:

REVOKE SELECT, INSERT, UPDATE ON employees FROM john_doe;

What Happens Internally:

1. Privilege Validation: The database checks if all specified privileges are granted to the user.
2. Remove Permissions: The system updates the permissions catalog to revoke the privileges.
3. Revoke Confirmation: The user no longer has access to the listed privileges on the employees table.

3. Revoking Permissions with WITH GRANT OPTION

You can revoke privileges along with the WITH GRANT OPTION if the user was allowed to grant those privileges to others.

Syntax:

REVOKE privilege ON object FROM user CASCADE;

Example:

REVOKE SELECT ON employees FROM john_doe CASCADE;

What Happens Internally:

1. Cascade Revocation: If the user john_doe granted the SELECT privilege to others, those privileges are also revoked automatically with the CASCADE keyword.
2. Update Permissions: The system removes the privilege and any associated permissions granted by the user.
3. Internal Adjustment: The permissions catalog is updated, and all dependent permissions are revoked.

4. Revoking Permissions from a Role

Instead of revoking permissions from individual users, you can revoke permissions from a role. Users who are members of the role will lose the associated privileges.

Syntax:

REVOKE privilege ON object FROM role;

Example:

REVOKE SELECT ON employees FROM hr_role;

What Happens Internally:

1. Role-Based Permission Removal: The database removes the specified privilege from the role hr_role.
2. User Inheritance Impact: Any user assigned to hr_role will automatically lose the SELECT privilege on the employees table.

5. Revoking All Privileges (Global Revoke)

To revoke all privileges from a user or role on an object, you can use the ALL keyword.

Syntax:

REVOKE ALL PRIVILEGES ON object FROM user;

Example:

REVOKE ALL PRIVILEGES ON employees FROM john_doe;

What Happens Internally:

1. Remove All Privileges: The system removes all privileges (e.g., SELECT, INSERT, UPDATE, etc.) for john_doe on the employees table.
2. Update Permissions Catalog: The internal permissions catalog is updated to reflect the removal of all access for the specified user.

Internal Mechanics of the REVOKE Command

1. Parsing and Validation

• The SQL engine parses the REVOKE command to ensure the syntax is correct.
• It checks whether the user or role has the specified privileges before proceeding with the revocation.

2. Searching the System Catalog

• The database checks the system catalog (e.g., mysql.db, pg_catalog.pg_roles) to find the user’s current privileges on the specified object.

3. Removing Permissions

• The database modifies the internal permissions table to remove the specified privilege(s) from the user or role.
• If the CASCADE option is used, it further checks if the user has granted those privileges to others and removes them as well.

4. Updating Access Control Lists (ACLs)

• The system updates its Access Control List (ACL) for the affected user or role to reflect the change in permissions.
• In the case of a role-based permission, any users assigned to the role automatically lose the revoked privileges.

5. Logging the Changes

• The operation is logged to ensure that changes can be tracked, and the database can recover the prior state if necessary.

Best Practices for Using the REVOKE Command

1. Be Specific with Privileges:
   • When revoking privileges, specify the exact privileges to avoid accidentally removing more permissions than intended.
2. Use the CASCADE Option with Caution:
   • The CASCADE option revokes privileges granted to other users by the target user. Use this cautiously to avoid unintended consequences.
3. Regularly Review and Revoke Unused Privileges:
   • Periodically audit user permissions and revoke those that are no longer necessary for their tasks.
4. Use Roles for Easier Management:
   • Assign permissions to roles rather than individual users to simplify privilege management. Revoking permissions from a role automatically impacts all users assigned to that role.