Commands used to control user permissions and access in a database.
Main Commands:
- GRANT → Give permissions
- REVOKE → Take back permissions
1. GRANT (Give Permission)
Syntax:
GRANT privilege1, privilege2 ON object_name TO user1, user2 [WITH GRANT OPTION];
Examples:
- Give SELECT →
GRANT SELECT ON Employees TO User1; - Multiple privileges →
GRANT SELECT, INSERT ON Employees TO User2; - Allow user to further grant →
GRANT SELECT ON Employees TO User3 WITH GRANT OPTION;
Key Points:
- Privileges → SELECT, INSERT, UPDATE, DELETE, EXECUTE, etc.
WITH GRANT OPTION→ lets the user pass on permissions.
2. REVOKE (Remove Permission)
Syntax:
REVOKE privilege1, privilege2 ON object_name FROM user1, user2;
Examples:
- Remove SELECT →
REVOKE SELECT ON Employees FROM User1; - Remove all privileges →
REVOKE ALL PRIVILEGES ON Employees FROM User2;
Key Points:
- Cancels permissions given earlier.
- Does not delete data or structure.
Comparison Table of DCL Commands
| Command | Purpose | Effect |
|---|---|---|
| GRANT | Give permissions | Allows users to perform operations |
| REVOKE | Remove permissions | Stops users from performing operations |
Common Privileges
| Privilege | Description |
|---|---|
| SELECT | Read data from table/view |
| INSERT | Add new rows |
| UPDATE | Modify existing rows |
| DELETE | Remove rows |
| EXECUTE | Run stored procedures/functions |