SQL DCL Commands (GRANT, REVOKE)

---

1. AIM

To study and implement SQL Data Control Language (DCL) commands — GRANT and REVOKE — for managing database user permissions in MySQL.

---

2. THEORY

DCL commands are used to control access to data in the database.

✔ Major DCL Commands

Command	Purpose
GRANT	Gives a user specific privileges
REVOKE	Removes previously granted privileges

These commands allow the database administrator to manage security, access control, and permissions.

---

3. COMMON PRIVILEGES IN MYSQL

Privilege	Meaning
SELECT	Allows reading data
INSERT	Allows inserting records
UPDATE	Allows updating records
DELETE	Allows deleting records
ALL PRIVILEGES	Gives full permissions
CREATE	Create tables
DROP	Drop tables
ALTER	Modify tables

---

4. PREPARATION FOR LAB

Create a Sample Database and Table

CREATE DATABASE LabSecurity;
USE LabSecurity;

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    salary INT
);

INSERT INTO employees VALUES
(1, 'Alice', 50000),
(2, 'Bob', 60000),
(3, 'Charlie', 70000);

---

5. DCL COMMANDS WITH EXAMPLES

---

Experiment 1: Creating a New User

CREATE USER 'student1'@'localhost' IDENTIFIED BY 'password123';

✔ A user student1 is created on the local machine.

---

Experiment 2: GRANT Privileges

Example 1 — Grant SELECT privilege

GRANT SELECT ON LabSecurity.employees TO 'student1'@'localhost';

Student1 can now view the table but cannot insert/update/delete.

---

Example 2 — Grant multiple privileges

GRANT SELECT, INSERT, UPDATE
ON LabSecurity.employees
TO 'student1'@'localhost';

---

Example 3 — Grant ALL PRIVILEGES

GRANT ALL PRIVILEGES ON LabSecurity.* TO 'student1'@'localhost';

✔ Student1 can now perform any operation inside the database.

---

Apply privileges using FLUSH

FLUSH PRIVILEGES;

---

Experiment 3: REVOKE Privileges

Example 1 — Revoke a specific privilege

REVOKE INSERT ON LabSecurity.employees FROM 'student1'@'localhost';

Student1 can no longer insert rows.

---

Example 2 — Revoke all privileges

REVOKE ALL PRIVILEGES, GRANT OPTION
FROM 'student1'@'localhost';

This removes all permissions granted earlier.

---

Experiment 4: Deleting a User

(Optional for lab)

DROP USER 'student1'@'localhost';

---

6. LAB PRACTICE EXERCISES

1. Create a user labuser and give only SELECT privileges on employees table.
2. Revoke SELECT privilege and check access.
3. Give INSERT and UPDATE privileges to a new user and test them.
4. Grant ALL PRIVILEGES on the database to a user and later revoke only DELETE permission.
5. Create two users and assign different privileges to compare their permissions.
6. Try accessing restricted operations and observe the MySQL error messages.

---

7. RESULT

Students successfully learned how to manage user permissions using GRANT and REVOKE commands.
They understood how MySQL controls security and access at database and table levels.