DCL (Data Control Language) Commands in SQL

Data Control Language (DCL) consists of SQL commands that are used to control access to the database. These commands are primarily used to grant and revoke user permissions to ensure data security and integrity.

Main DCL Commands:

1. GRANT – Provides specific privileges to users.
2. REVOKE – Removes privileges from users.

---

1. GRANT Command

The GRANT command is used to provide privileges (permissions) to users or roles in a database. These privileges allow users to perform actions such as SELECT, INSERT, UPDATE, DELETE, and more on database objects like tables, views, and procedures.

Syntax:

GRANT privilege_name ON object_name TO user_name;

Example:

GRANT SELECT, INSERT ON Students TO user1;

This allows user1 to perform SELECT (read data) and INSERT (add new data) operations on the Students table.

Granting All Privileges:

GRANT ALL PRIVILEGES ON Students TO user1;

This grants all permissions on the Students table to user1.

Granting Privileges to Multiple Users:

GRANT SELECT, UPDATE ON Students TO user1, user2;

This allows both user1 and user2 to read (SELECT) and modify (UPDATE) records in the Students table.

Granting Privileges with GRANT OPTION:

GRANT SELECT ON Students TO user1 WITH GRANT OPTION;

The WITH GRANT OPTION allows user1 to further grant the SELECT privilege to other users.

---

2. REVOKE Command

The REVOKE command is used to remove previously granted privileges from users or roles.

Syntax:

REVOKE privilege_name ON object_name FROM user_name;

Example:

REVOKE SELECT, INSERT ON Students FROM user1;

This removes the SELECT and INSERT privileges from user1 on the Students table.

Revoking All Privileges:

REVOKE ALL PRIVILEGES ON Students FROM user1;

This removes all permissions from user1 on the Students table.

Revoking Privileges from Multiple Users:

REVOKE UPDATE ON Students FROM user1, user2;

This removes the UPDATE privilege from both user1 and user2 on the Students table.

⚠ Note:

• The REVOKE command only affects the privileges granted explicitly using GRANT.
• If a user received privileges through a role, revoking privileges from the role does not remove them from the user unless the role is also revoked.

---

Summary of DCL Commands:

Command	Purpose
GRANT	Grants specific privileges to users.
REVOKE	Removes previously granted privileges from users.

These commands help in managing database security by restricting and controlling user access.