Introduction
In relational databases, retaining information security and integrity is paramount. SQL’s Data Control Language (DCL) empowers you with the essential tools to control user privileges, ensuring only specific people can access and control database items. Two crucial DCL commands, GRANT and REVOKE, form the bedrock of this permission management system.

Overview
- Data Control Language (DCL) in SQL helps manage database access through commands like GRANT and REVOKE.
- The GRANT command provides specific privileges to users, such as SELECT, INSERT, UPDATE, and DELETE.
- The REVOKE command removes previously granted permissions, maintaining data security and integrity.
- Role-based permission management simplifies access control, assigning predefined roles to different users.
- Effective use of GRANT and REVOKE commands ensures secure and controlled access to relational databases.
DCL: The Gatekeeper of Data Access
DCL, or Data Control Language, is mostly about who can access different parts of a database – tables, views, stored procedures, and functions. What is DCL? It’s not the same as Data Definition Language (DDL), which is all about making and changing the database structure, or Data Manipulation Language (DML), which you use to get things out, put things in, change them, and remove them.
GRANT: Bestowing Permissions
The GRANT command is the key that unlocks users’ database object access. Its syntax allows you to grant specific privileges on a database object to one or more users (or roles, which we’ll discuss later). Here’s the basic structure:
GRANT <privilege_type> ON <object_name> TO <user_name(s)>;
Privilege Types
- SELECT: Grants the ability to retrieve data from the object.
- INSERT: Allows insertion of new data into the object.
- UPDATE: Empowers users to modify existing data in the object.
- DELETE: Permits deletion of data from the object.
- ALTER: Enables users to alter the structure of the object.
- REFERENCES: Grants permission to reference another object in a relationship.
- EXECUTE: Allows users to execute stored procedures or functions.
There are more (specific to different database systems).
Granting SELECT on a Table
Let’s consider a sample table named customers storing customer information. To grant the user sales_rep the ability to view customer data, we’d execute:
GRANT SELECT ON customers TO sales_rep;
Now, sales_rep can use SELECT statements to query the customer’s table.
Also read: How to Use DDL Commands in SQL
REVOKE: Taking Away Permissions
The REVOKE command serves as the opposite of GRANT. It’s used to rescind previously granted privileges from users. The syntax is similar:QL
REVOKE <privilege_type> ON <object_name> FROM <user_name(s)>;
Revoking SELECT on a Table
Continuing with our customer’s table, suppose we no longer want sales_rep to access customer data. We’d use:
REVOKE SELECT ON customers FROM sales_rep;
By executing this statement, the SELECT privilege would be revoked from sales_rep, preventing them from querying the customer’s table.
Additional Considerations
- Detail Level: You can give or remove permissions at different levels, from whole tables to particular columns inside a table.
- Linked Removal: If you remove permissions from a user who has passed them on to others, those others will also lose their permissions.
- Groups: Groups are a set of permissions that can be given to users. Giving a group of permissions with just one command makes managing permissions easier.
Next, we will go for a simple example – We have a database for a bookstore. We need to manage user permissions for different roles
Also read: SQL: A Full Fledged Guide from Basics to Advance Level
Managing User Permissions for Different Roles in Bookstore
Here are the different roles:
- Manager: Has full access (SELECT, INSERT, UPDATE, DELETE) to all tables (
books
,authors
,customers
,orders
). - Sales Staff: Can view books and customer information (SELECT) but cannot modify or delete data.
- Inventory Staff: Can add new books (INSERT) and update existing book information (UPDATE) but cannot access customer or order information.
Database Setup
CREATE TABLE authors (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL
);
CREATE TABLE customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL
);
CREATE TABLE books (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
author_id INT NOT NULL,
FOREIGN KEY (author_id) REFERENCES authors(id)
);
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
book_id INT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id),
FOREIGN KEY (book_id) REFERENCES books(id)
);
Creating Roles
For PostgreSQL
CREATE ROLE role_manager;
CREATE ROLE role_sales_staff;
CREATE ROLE role_inventory_staff;
For MySQL
CREATE ROLE 'role_manager';
CREATE ROLE 'role_sales_staff';
CREATE ROLE 'role_inventory_staff';
Granting Privileges to Roles:
For PostgreSQL
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES TO role_manager;
GRANT SELECT ON books, customers TO role_sales_staff;
GRANT INSERT, UPDATE ON books TO role_inventory_staff;
For MYSQL
GRANT SELECT ON database_name.books TO 'role_sales_staff';
GRANT SELECT ON database_name.customers TO 'role_sales_staff';
GRANT INSERT, UPDATE ON database_name.books TO 'role_inventory_staff';
Output

Create Users
For MySQL
CREATE USER 'user1'@'%' IDENTIFIED BY 'password1';
CREATE USER 'user2'@'%' IDENTIFIED BY 'password2';
CREATE USER 'user3'@'%' IDENTIFIED BY 'password3';
Output

Assigning Users to Roles:
For PostgreSQL
GRANT role_manager TO user1;
GRANT role_sales_staff TO user2;
GRANT role_inventory_staff TO user3;
For MySQL
GRANT 'role_manager' TO 'user1'@'%';
GRANT 'role_sales_staff' TO 'user2'@'%';
GRANT 'role_inventory_staff' TO 'user3'@'%';
Output

Explanation:
user1
(manager) has full access to all tables through therole_manager
role.user
(sales staff) can only view the information inbooks
andcustomer
tables due torole_sales_staff
permissions.user3
(inventory staff) can add new books and update existing ones but cannot access customer or order information.
Following these steps, you can manage user access in your database using GRANT
, REVOKE
, and roles, ensuring appropriate data security and control.
Also read: SQL For Data Science: A Beginner Guide!
Conclusion
GRANT and REVOKE are fundamental instructions in SQL for strong database safety. By successfully handling user privileges, you can protect sensitive data, ensure suitable get admission to manipulate, and hold the integrity of your database. As your database evolves, knowledge the way to furnish and revoke permissions will remain a cornerstone of reliable information control.
Frequently Asked Questions
Ans. GRANT and REVOKE are SQL commands used to manage user permissions in a database. The GRANT command is used to give specific privileges to users, while the REVOKE command takes away those privileges given to users.
Ans. The GRANT command in SQL is used to assign privileges to users. For example, GRANT SELECT, INSERT ON database_name.table_name TO ‘user’@’host’; this command allows users to select and insert data into the specified tab.
Ans. You use the REVOKE command to revoke a grant in MySQL. For example: REVOKE SELECT, INSERT ON database_name.table_name FROM ‘user’@’host’; this command removes the SELECT and INSERT privileges from the specified user on the given table.