DCL: GRANT, REVOKE

1. What is DCL?

  • DCL (Data Control Language): Commands used to manage permissions and access control for database users.
  • Key Commands:
    • GRANT
    • REVOKE

2. GRANT Command

  • Purpose: Provides permissions to users to perform specific operations on database objects.
  • Syntax: GRANT privilege1, privilege2, ... ON object_name TO user1, user2, ... [WITH GRANT OPTION];
  • Examples:
    • Grant SELECT permission on the Employees table to a user: GRANT SELECT ON Employees TO User1;
    • Grant multiple permissions: GRANT SELECT, INSERT ON Employees TO User2;
    • Grant permission with the ability to further delegate: GRANT SELECT ON Employees TO User3 WITH GRANT OPTION;
  • Key Points:
    • Privileges include: SELECT, INSERT, UPDATE, DELETE, EXECUTE, etc.
    • WITH GRANT OPTION: Allows the recipient to grant the same permissions to other users.

3. REVOKE Command

  • Purpose: Removes permissions from users.
  • Syntax: REVOKE privilege1, privilege2, ... ON object_name FROM user1, user2, ...;
  • Examples:
    • Revoke SELECT permission from a user: REVOKE SELECT ON Employees FROM User1;
    • Revoke all permissions on a table: REVOKE ALL PRIVILEGES ON Employees FROM User2;
  • Key Points:
    • Removes previously granted permissions.
    • Does not affect the data or structure of the database objects.

Comparison Table

CommandPurposeEffect
GRANTProvides permissions to usersAllows users to perform specific operations.
REVOKERemoves permissions from usersDenies previously granted operations.

Common Privileges

PrivilegeDescription
SELECTAllows reading data from a table/view.
INSERTAllows inserting new rows into a table.
UPDATEAllows modifying existing data in a table.
DELETEAllows removing rows from a table.
EXECUTEAllows running stored procedures or functions.

Exam Tips

  1. Memorize the syntax for GRANT and REVOKE commands.
  2. Understand the effect of WITH GRANT OPTION in GRANT.
  3. Know the privileges (SELECT, INSERT, UPDATE, etc.) and when they are used.
  4. Remember:
    • GRANT adds permissions.
    • REVOKE removes permissions.