Granting and Revoking Permissions in SQL – Managing User Access Control

Overview

In SQL, permissions (also known as privileges) define what operations a user or role is allowed to perform on a database object like a table, view, or schema. To manage access securely and efficiently, SQL provides two primary commands:

• GRANT – To assign permissions to users or roles
• REVOKE – To remove previously granted permissions

These commands are essential for enforcing role-based access control, ensuring that users can only access what they are authorized to.

Granting Permissions

The GRANT command is used to give specific privileges to a user or role on database objects such as tables, views, or the entire database.

Syntax:

GRANT privilege_type [, ...] 
ON object_type 
TO user_or_role;

Common Privilege Types:

• SELECT – Allows reading data from a table or view
• INSERT – Allows inserting new rows into a table
• UPDATE – Allows modifying existing rows
• DELETE – Allows deleting rows
• ALL PRIVILEGES – Grants all available privileges

Example (MySQL / PostgreSQL):

GRANT SELECT, INSERT ON employees TO 'john'@'localhost';

This command allows the user john to select and insert data in the employees table.

Revoking Permissions

The REVOKE command is used to remove privileges that were previously granted to a user or role.

Syntax:

REVOKE privilege_type [, ...] 
ON object_type 
FROM user_or_role;

Example:

REVOKE INSERT ON employees FROM 'john'@'localhost';

This removes the INSERT privilege for the user john on the employees table.

Granting Roles (Optional, If Roles Are Used)

In databases that support roles (such as PostgreSQL and MySQL 8+), you can grant a role to a user instead of individual permissions.

GRANT analyst_role TO 'john'@'localhost';

And revoke it as:

REVOKE analyst_role FROM 'john'@'localhost';

Important Notes

• Privileges must be granted by users with sufficient authority (e.g., root or a user with GRANT OPTION).
• Permissions can be granted at different levels: database, table, column, or even routine level.
• You can use WITH GRANT OPTION to allow a user to grant the same privileges to others.

Example:

GRANT SELECT ON employees TO 'jane'@'localhost' WITH GRANT OPTION;

Best Practices

• Always follow the principle of least privilege: only grant the minimum necessary permissions.
• Use roles for easier privilege management across multiple users.
• Regularly audit permissions and revoke unused or outdated access.
• Avoid using ALL PRIVILEGES unless absolutely necessary.