5.4 DCL — GRANT, REVOKE

Level: 5 | Version: v1.1 | Author: Meptrasoft

Overview

DCL (Data Control Language) is commonly used for managing database permissions and access.

The two main commands are:

GRANT → Give access
REVOKE → Take back access

GRANT

GRANT is used to give a user or role permission to perform specific operations on a database object.

Example:

GRANT SELECT, INSERT
ON employees
TO hr_user;

This gives hr_user permission to:

• Read data using SELECT
• Add data using INSERT

The exact syntax and available privileges can vary between database systems.

GRANT → Give permission

REVOKE

REVOKE is used to remove a previously granted permission.

Example:

REVOKE INSERT
ON employees
FROM hr_user;

After this, hr_user no longer has the INSERT permission from that grant, but may still have SELECT permission.

Before:
SELECT ✓
INSERT ✓

After REVOKE:
SELECT ✓
INSERT ✗

REVOKE → Remove permission

Common Permissions

Database systems can provide different privileges depending on the product.

Common examples include:

SELECT  → Read data
INSERT  → Add data
UPDATE  → Change data
DELETE  → Remove data

For example:

GRANT SELECT ON employees TO hr_user;

allows the role/user to read from employees, subject to the database's permission model.

Users and Roles

Permissions are commonly assigned to users or roles.

A role is a named collection of permissions that can be assigned to users.

For example:

HR Role
   ↓
SELECT
INSERT
UPDATE
   ↓
HR Users

Using roles can make permission management easier when many users need similar access.

GRANT vs REVOKE

Easy Memory Trick

GRANT  → GIVE
REVOKE → REMOVE

Why DCL Matters

Not every user should have unrestricted access to every database object.

For example:

Student
→ View permitted student information

Teacher
→ View and update authorized academic data

Admin
→ Manage broader database access

Using permissions and roles helps follow the principle of least privilege:

Give users only the access they need to perform their job.

Common Beginner Mistakes

Placement Quick Points

DCL
→ ACCESS CONTROL

GRANT
→ GIVE PERMISSION

REVOKE
→ REMOVE PERMISSION

SELECT
→ READ

INSERT
→ ADD

UPDATE
→ MODIFY

DELETE
→ REMOVE

• DCL is commonly used to manage database access and permissions.
• GRANT gives privileges to users or roles.
• REVOKE removes previously granted privileges.
• Permissions can be assigned to users or roles.
• Common privileges include SELECT, INSERT, UPDATE, and DELETE.
• Permission syntax and behavior can vary between database systems.
• Least privilege helps reduce unnecessary access.

Interview Questions

What is DCL?

DCL stands for Data Control Language and is commonly used to manage database permissions and access.

What is GRANT?

GRANT gives a user or role permission to perform specified operations on database objects.

What is REVOKE?

REVOKE removes previously granted permissions.

What is the difference between GRANT and REVOKE?

TEXT

GRANT  → Give permission
REVOKE → Remove permission

What is a database role?

A role is a named collection of permissions that can be assigned to users, making access management easier.

Why are database permissions important?

They help prevent unauthorized users from viewing or modifying data and allow access to be controlled according to user responsibilities.

Practice & Hands-On Exercises

DCL commands require appropriate database users/roles and permissions, so they may not be executable in a shared learning environment.

1. Explain what this command does:

GRANT SELECT, INSERT
ON employees
TO hr_user;

2. Explain what this command does:

REVOKE INSERT
ON employees
FROM hr_user;

3. After the REVOKE, which permission does hr_user still have?
4. Explain why roles are useful when many users need the same permissions.
5. Give one example of a user who should have read-only access.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

DCL
 ↓
ACCESS CONTROL

GRANT
 ↓
GIVE PERMISSION

REVOKE
 ↓
REMOVE PERMISSION

DCL controls access to database objects: GRANT gives permissions, while REVOKE removes permissions.

End of DCL — GRANT, REVOKE