GRANT & REVOKE

In database systems, controlling access to data is crucial for security and proper management. The GRANT and REVOKE commands allow administrators to give or remove permissions to users or roles.

Learning Objectives:

• Understand what GRANT and REVOKE commands do.
• Learn how to assign permissions to users.
• Learn how to remove permissions safely.
• Apply these commands in practical database scenarios.

---

Module 2: GRANT Command

The GRANT command is used to give privileges to users or roles. Privileges define what actions a user can perform on database objects such as tables, views, or procedures.

Syntax:

GRANT privilege_list
ON object_name
TO user_name
[WITH GRANT OPTION];

Explanation:

• privilege_list – The type of permission (SELECT, INSERT, UPDATE, DELETE, etc.)
• object_name – The database object on which the permission is granted
• user_name – The user or role receiving the permission
• WITH GRANT OPTION – Allows the user to grant the same permission to others

Example:

GRANT SELECT, INSERT
ON Employees
TO John;

This allows John to view and add data to the Employees table.

---

Module 3: REVOKE Command

The REVOKE command removes previously granted permissions from a user or role.

Syntax:

REVOKE privilege_list
ON object_name
FROM user_name;

Explanation:

• privilege_list – The permissions to remove
• object_name – The database object from which permissions are revoked
• user_name – The user or role losing the permission

Example:

REVOKE INSERT
ON Employees
FROM John;

This removes John’s ability to add data to the Employees table but does not affect his ability to view data.

---

Module 4: Best Practices

• Always follow the principle of least privilege – only grant the permissions necessary.
• Use roles to manage permissions more efficiently.
• Regularly review and revoke unnecessary permissions to maintain security.
• Document all GRANT and REVOKE actions for audit purposes.

---

Module 5: Summary

• GRANT – Assigns permissions to users or roles.
• REVOKE – Removes permissions from users or roles.
• Proper use of these commands ensures database security and controlled access.

---