What are DCL Commands?

DCL stands for Data Control Language. It is a category of SQL commands used to control access, permissions, and privileges in a database system.

In simple words, DCL commands decide who can access the database and what actions they are allowed to perform. These commands are mostly used by Database Administrators (DBAs) or users who have permission to manage database security.

 Why Do We Need DCL?

In real-world applications, many users may work with the same database. However, every user should not get full access. For example, a student may only view marks, a teacher may update marks, and an administrator may manage the entire database.

DCL helps us create this type of controlled environment by giving limited and required permissions to each user.

 Real-Life Analogy

 Prerequisites Before Using DCL Commands

Before using DCL commands in MySQL, you should understand a few basic requirements. These are important because DCL commands deal with user permissions and database security.

 Main DCL Commands in MySQL

The two main DCL commands are GRANT and REVOKE. These commands are used to give and remove permissions from database users.

 Related User Management Commands

In MySQL, DCL is usually used together with user management commands. These commands help create, modify, and view users.

 Creating a User in MySQL

Before giving permission to a user, we first need to create that user. In MySQL, a user is usually written in the form: 'username'@'host'.

Here, username is the name of the user and host tells from where the user can connect. For local computer access, we commonly use localhost.

 Syntax

CREATE USER 'user_name'@'host' IDENTIFIED BY 'password';

 Example

CREATE USER 'john'@'localhost' IDENTIFIED BY 'john123';

 GRANT Command in MySQL

The GRANT command is used to give privileges to a user. A privilege means permission to perform a specific operation. For example, you can allow a user to only read data, or you can allow the user to insert and update data.

 Basic Syntax

GRANT privilege_name
ON database_name.table_name
TO 'user_name'@'host';

 Syntax Breakdown

 Example 1: Grant SELECT Permission

Suppose we have a database named company and a table named employees. We want to allow the user john to only read data from this table.

GRANT SELECT
ON company.employees
TO 'john'@'localhost';

 User Can Run

SELECT * FROM company.employees;

 User Cannot Run

INSERT INTO company.employees VALUES (1, 'Rahul', 'HR');

 Example 2: Grant Multiple Privileges

We can also give multiple permissions at the same time by separating them with commas.

GRANT SELECT, INSERT, UPDATE
ON company.employees
TO 'john'@'localhost';

 Example 3: Grant All Privileges

If you want to give full access on a database or table, you can use ALL PRIVILEGES.

GRANT ALL PRIVILEGES
ON company.*
TO 'john'@'localhost';

 Example 4: GRANT with WITH GRANT OPTION

The WITH GRANT OPTION allows a user to give the same permission to other users. This is a powerful option and should be used carefully.

GRANT SELECT
ON company.employees
TO 'john'@'localhost'
WITH GRANT OPTION;

 REVOKE Command in MySQL

The REVOKE command is used to remove permissions from a user. If a user no longer needs access, or if permission was given by mistake, we can remove it using REVOKE.

 Basic Syntax

REVOKE privilege_name
ON database_name.table_name
FROM 'user_name'@'host';

 Syntax Breakdown

 Example 1: Revoke INSERT Permission

Suppose user john has INSERT permission on the employees table. Now we want to remove only the INSERT permission.

REVOKE INSERT
ON company.employees
FROM 'john'@'localhost';

 Example 2: Revoke Multiple Privileges

REVOKE SELECT, UPDATE
ON company.employees
FROM 'john'@'localhost';

 Example 3: Revoke All Privileges

REVOKE ALL PRIVILEGES
ON company.*
FROM 'john'@'localhost';

 Common Privileges in MySQL

MySQL provides different types of privileges. Each privilege allows the user to perform a specific task.

 Privilege Levels in MySQL

In MySQL, permissions can be given at different levels. This helps the administrator control access more precisely.

 Database-Level Permission Example

GRANT SELECT
ON company.*
TO 'john'@'localhost';

 Table-Level Permission Example

GRANT SELECT, INSERT
ON company.employees
TO 'john'@'localhost';

 Column-Level Permission Example

Column-level permission is useful when you want to allow access to only selected columns.

GRANT SELECT (name, department)
ON company.employees
TO 'john'@'localhost';

 Checking User Permissions with SHOW GRANTS

The SHOW GRANTS command is used to see what permissions are currently assigned to a user.

 Syntax

SHOW GRANTS FOR 'user_name'@'host';

 Example

SHOW GRANTS FOR 'john'@'localhost';

 What is FLUSH PRIVILEGES?

FLUSH PRIVILEGES reloads the privilege tables in MySQL. In modern MySQL usage, when you use commands like GRANT, REVOKE, or CREATE USER, MySQL usually applies changes automatically.

However, if permissions are changed manually inside MySQL system tables, then FLUSH PRIVILEGES may be required.

FLUSH PRIVILEGES;

 Complete Practical Example

Let us understand DCL commands using a complete example.

 Step 1: Create Database

CREATE DATABASE company;

 Step 2: Use Database

USE company;

 Step 3: Create Table

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

 Step 4: Insert Sample Data

INSERT INTO employees VALUES
(1, 'Amit Sharma', 'HR', 45000.00),
(2, 'Neha Verma', 'IT', 60000.00),
(3, 'Rahul Khan', 'Finance', 55000.00);

 Step 5: Create User

CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'report123';

 Step 6: Give SELECT Permission

GRANT SELECT
ON company.employees
TO 'report_user'@'localhost';

 Step 7: Check Permission

SHOW GRANTS FOR 'report_user'@'localhost';

 Step 8: Remove SELECT Permission

REVOKE SELECT
ON company.employees
FROM 'report_user'@'localhost';

 Difference Between GRANT and REVOKE

 Best Practices for Using DCL

Since DCL commands are related to database security, they should be used carefully.

 Good and Bad Permission Design

 Common Errors in DCL Commands

Beginners often face errors while using GRANT and REVOKE commands. Below are some common errors and their meanings.

 Interview Questions on DCL Commands

 Important Exam Points

 Summary

DCL commands are one of the most important parts of SQL security. They help database administrators control access to data and protect the database from unauthorized users.

In MySQL, GRANT is used to give permissions, while REVOKE is used to remove permissions. These commands are commonly used with user management commands such as CREATE USER, ALTER USER, DROP USER, and SHOW GRANTS.