DCL in SQL: GRANT and REVOKE — A Practice-Driven DBMS Tutorial

The Story That Explains DCL

DCL (Data Control Language) manages privileges: the permissions that let a user perform actions on database objects. It has just two statements, GRANT and REVOKE, but they carry real weight — they are the front line of database security. This tutorial covers privileges, GRANT, roles, PUBLIC, WITH GRANT OPTION, REVOKE, and the all-important CASCADE behaviour, each with examples and animated diagrams.

Where DCL Sits Among SQL Commands

SQL statements fall into four families. DCL is one of them — the one concerned purely with permissions.

Two Kinds of Privileges

GRANT — Handing Out a Privilege

The GRANT statement gives one or more privileges on an object to one or more users or roles.

-- Syntax
GRANT privilege_list
ON    object
TO    grantee_list
[WITH GRANT OPTION];

-- Let an analyst read the employees table
GRANT SELECT
ON    employees
TO    analyst_amy;

-- Multiple privileges, multiple users, in one statement
GRANT SELECT, INSERT, UPDATE
ON    employees
TO    manager_mia, hr_clerk;

-- Column-level: allow updating only the salary column
GRANT UPDATE (salary)
ON    employees
TO    payroll_pat;

WITH GRANT OPTION — Delegating the Power to Grant

Adding WITH GRANT OPTION lets the grantee pass the same privilege on to others. It is powerful and risky: you are no longer the only gatekeeper.

-- Owner grants to amy AND lets her re-grant
GRANT SELECT ON employees TO analyst_amy WITH GRANT OPTION;

-- Now amy can legally do this herself
GRANT SELECT ON employees TO intern_raj;

Roles — Bundling Privileges

Granting the same five privileges to fifty users individually is a maintenance nightmare. A role is a named bundle of privileges; grant privileges to the role once, then grant the role to users. Change the role and everyone updates at once.

-- 1. Create a role and load it with privileges
CREATE ROLE hr_staff;
GRANT SELECT, INSERT, UPDATE ON employees TO hr_staff;

-- 2. Hand the whole bundle to users
GRANT hr_staff TO manager_mia, hr_clerk;

-- 3. Later, add one privilege to the role — every member gains it instantly
GRANT DELETE ON employees TO hr_staff;

PUBLIC — Granting to Everyone

The special grantee PUBLIC represents every current and future user. A privilege granted to PUBLIC is held by all.

-- Make a lookup table readable by absolutely everyone
GRANT SELECT ON country_codes TO PUBLIC;

REVOKE — Taking a Privilege Back

The mirror image of GRANT. The structure is the same, with FROM in place of TO.

-- Syntax
REVOKE privilege_list
ON     object
FROM   grantee_list
[CASCADE | RESTRICT];

-- Remove the analyst's read access
REVOKE SELECT ON employees FROM analyst_amy;

-- Remove one privilege from a role (every member loses it)
REVOKE INSERT ON employees FROM hr_staff;

-- Take back only the ability to re-grant, keeping the privilege itself
REVOKE GRANT OPTION FOR SELECT ON employees FROM analyst_amy;

CASCADE vs RESTRICT — The Cascading Revoke

Recall the delegation chain: owner → amy (with grant option) → raj. When you revoke amy's privilege, what happens to raj — who only has access because amy granted it? That is decided by CASCADE or RESTRICT.

-- Cascade: amy AND everyone she granted to lose SELECT
REVOKE SELECT ON employees FROM analyst_amy CASCADE;

-- Restrict: fails with an error because raj still depends on amy's grant
REVOKE SELECT ON employees FROM analyst_amy RESTRICT;

Common Mistakes

Practice Problems

Use the building/keys scenario — owner hr_admin, table employees, users analyst_amy, intern_raj, manager_mia.

-- Q3 solution: role-based read-only access
CREATE ROLE read_only;
GRANT SELECT ON employees TO read_only;
GRANT read_only TO intern_raj;

Golden Rules