DCL (Data Control Language) in SQL

Introduction

After learning how to define database structure (DDL) and manipulate data (DML), the next important concept is controlling access to the database.

This is where Data Control Language (DCL) comes in.

DCL includes SQL commands used to manage permissions and security in a database. It controls who can access the data and what actions they are allowed to perform.

What is DCL

Data Control Language (DCL) is a set of SQL commands used to grant or restrict access to database users.

In real-world applications, not every user should have full access to the database. Some users may only view data, while others may modify it.

DCL helps manage these permissions.

Why DCL is Important

DCL plays a key role in database security.

It helps to:

  • Protect sensitive data

  • Control user access

  • Prevent unauthorized changes

  • Manage roles and permissions

For example:

  • A student may only view data

  • A teacher may update marks

  • An admin may have full access

Common DCL Commands

The two main DCL commands are:

  • GRANT

  • REVOKE

GRANT Command

The GRANT command is used to give specific permissions to a user.

Example:

GRANT SELECT, INSERT ON Students TO user1;

This allows user1 to:

  • View data (SELECT)

  • Add new records (INSERT)

in the Students table.

REVOKE Command

The REVOKE command is used to remove previously granted permissions.

Example:

REVOKE INSERT ON Students FROM user1;

This removes the INSERT permission from user1.

Types of Permissions in MySQL

Some common permissions that can be granted include:

  • SELECT → Read data

  • INSERT → Add data

  • UPDATE → Modify data

  • DELETE → Remove data

  • ALL PRIVILEGES → Full access

Example:

GRANT ALL PRIVILEGES ON Students TO user1;

How DCL Works

When a user tries to operate:

  1. MySQL checks the user’s permissions

  2. If permission is granted → operation is allowed

  3. If not → access is denied

This ensures that only authorized users can perform specific actions.

Example Scenario

In a company database:

  • Admin → full access

  • Employee → can view and update data

  • Guest → read-only access

DCL commands help define these roles and permissions.

Key Points to Remember

  • DCL stands for Data Control Language

  • It is used to manage user permissions

  • Main commands are GRANT and REVOKE

  • Helps maintain database security and controlled access

  • Ensures only authorised users can perform operations