What are the DCL Commands in SQL?

What are DCL commands in SQL?

Data Control Language (DCL) forms an essential part of Structured Query Language. They help manage access and permissions to a particular database. A database administrator is a person who looks into the design and maintenance of an organization's database system. He ensures the security of the database. 

• DCL allows the administrator to control access to the data in the database. 
   
• DCL commands permit to set the roles and permissions for different users. 
   
• DCL commands can restrict or deny access to certain parts of the database. 
   
• DCL ensures authorized access to sensitive data and maintains data security & integrity. 

Types of SQL Commands

The main types of SQL Commands are:

DMLCommands

Also known as Data manipulation language Commands, these commands are used to manipulate and interact with the data within a database. The most commonly used DML commands include:

• SELECT: This command retrieves data from one or more database tables.
   
• DELETE: Remove records from a database table.
   
• INSERT:  Add new records into a database table.
   
• UPDATE: Modifies existing records in a database table.

DDL Commands

Data Definition Language commands are used to define and manage the structure of the database objects. They allow you to create, modify, and delete various database objects. The most commonly used DDL commands include:

• CREATE: Creates a new database object, such as a table, view, or index.
   
• ALTER: Modifies the structure of an existing database object.
   
• TRUNCATE: Remove all the records from a table but keep the structure intact.
   
• DROP: Deletes a database object, such as tables, views, and indexes.

DCL Commands

Data Control Language commands control access to the database and manage user permissions. DCL commands include:

• GRANT: It is used to give access privileges to a user.
   
• REVOKE: It is used to revoke or withdraw the access privileges given.

TCL Commands

Transaction Control Language Commands are used to manage transactions within a database. TCL commands include:

• COMMIT: To commit a transaction.
   
• ROLLBACK: To roll back or cancel a transaction if any error occurs.
   
• SAVEPOINT: To set a save point.

Also read, Natural Join in SQL

How is Data Control done in SQL?

Data control, as the name suggests, means controlling the data stored in the database. It refers to managing access and permissions to a database. Data control is done with the help of (Data Control Language) DCL Commands in SQL.

• DCL commands in SQL provide a way to authorize access to the data. They limit the users to access the data and manipulate it.
   
• Some Data Control Language (DCL) commands are GRANT, REVOKE, and DENY.
   
• GRANT command grants the privileges, and the REVOKE command takes away the authority or the privileges.
   
• This ensures the security and integrity of the database management system.

Various DCL Commands in SQL?

DCL (Data Control Language) commands in SQL are necessary for managing permissions and privileges that control access to database objects. They play a crucial role in ensuring data security and integrity. Here are various essential DCL (Data Control Language) commands in SQL that are widely used for managing permissions and privileges related to database objects, ensuring secure and controlled access:

Also read about, gzip command in linux

1. GRANT Command

This DCL command grants specific privileges to users on a table, view, or stored procedure.

Syntax 

GRANT [privilege_name] ON [object_name] TO [user_name]

or

GRANT privilege_name ON object_name TO user_name WITH GRANT OPTION

 

where 

privilege_name 

It refers to the name of the privilege to be granted.

object_name

It refers to the name of the table, view, or object on which the privilege has to be granted.
user_name

It refers to the user or role to which the privilege is granted.

WITH GRANT OPTION 

It means that the user who has been granted some privileges can further grant those privileges to other users.

Example 

Suppose we have a table named STUDENT created in the following way:

CREATE TABLE STUDENT(RNO NUMBER(10),NAME CHAR(20),AGE NUMBER(2),CITY CHAR(10),MARKS NUMBER(3));
INSERT INTO STUDENT VALUES(1, 'Sasha',17,'Faridabad',80);
INSERT INTO STUDENT VALUES(2,'John',18,'Agra',91);
INSERT INTO STUDENT VALUES(3,'Sara',17,'Hisar',86);
INSERT INTO STUDENT VALUES(4,'Rohan',16,'Faridabad',79);
INSERT INTO STUDENT VALUES(5,'Virat',18,'Delhi',80);
SELECT * FROM STUDENT;

 

RNO	Name	Age	City	Marks
1	Sasha	17	Faridabad	80
2	John	18	Agra	91
3	Sara	17	Hisar	86
4	Rohan	16	Faridabad	79
5	Virat	18	Delhi	80

2.REVOKE Command

This command revokes the previously granted privileges through the GRANT command. from a user. It reverts to the point when there is no access.

Syntax

REVOKE [privilege_name] ON [object_name] FROM [user_name]

 

where

privilege_name

It refers to the privilege that was granted.

object_name

It refers to the specific object whose access was granted.

user_name 

It refers to the name of the user from which the privilege is being revoked.

Example

Suppose we have a table named STUDENT created in the following way:

CREATE TABLE STUDENT(RNO NUMBER(10),NAME CHAR(20),AGE NUMBER(2),CITY CHAR(10),MARKS NUMBER(3));
INSERT INTO STUDENT VALUES(1, 'Sasha',17,'Faridabad',80);
INSERT INTO STUDENT VALUES(2,'John',18,'Agra',91);
INSERT INTO STUDENT VALUES(3,'Sara',17,'Hisar',86);
INSERT INTO STUDENT VALUES(4,'Rohan',16,'Faridabad',79);
INSERT INTO STUDENT VALUES(5,'Virat',18,'Delhi',80);
SELECT * FROM STUDENT;

 

 

RNO	Name	Age	City	Marks
1	Sasha	17	Faridabad	80
2	John	18	Agra	91
3	Sara	17	Hisar	86
4	Rohan	16	Faridabad	79
5	Virat	18	Delhi	80

Benefits of Implementing DCL Commands

• Enhanced security: DCL commands enable administrators to restrict access to sensitive system resources.
• Automation: DCL commands facilitate automated execution of tasks, streamlining administrative processes.
• Customization: DCL commands allow customization of user permissions and system configurations according to specific requirements.

Disadvantages of Implementing DCL Commands

• Complexity: Implementing DCL commands may require a steep learning curve for administrators unfamiliar with command-line interfaces.
• Risk of errors: Manual entry of commands increases the likelihood of human error, potentially leading to system misconfigurations or data loss.
• Limited user interface: DCL commands typically lack graphical user interfaces, making them less intuitive for novice users compared to graphical management tools.

Frequently Asked Questions

What is DCL and its commands?

DCL is a set of SQL commands that control database access and permissions. DCL commands include GRANT and REVOKE. GRANT is used to give access privileges to a user, whereas REVOKE is used to revoke or withdraw the access privileges given.

What is DCL and TCL in SQL? 

DCL and TCL are two integral components of SQL. In SQL, DCL commands control database access and manage user permissions. Whereas TCL commands are used to manage transactions within a database, ensuring their consistency and integrity.

What type of command is DCL? 

DCL falls into the SQL commands category that handles the important task of access control and permissions management. It includes commands like GRANT and REVOKE. which allows giving specific privileges to users, and also lets take them away.

What are the two DCL commands? 

The two DCL commands are GRANT and REVOKE. The GRANT gives users access privileges and permissions to users or roles. While the REVOKE command is used to revoke or withdraw the access privileges given. These commands are crucial in managing who can do what in a database.

Conclusion

In this blog, we discussed what are the DCL commands in SQL. We covered topics such as introduction, DML commands, DDL commands, DCL commands, TCL commands, and various DCL commands in SQL. 

If you found this blog interesting and insightful, refer to similar blogs:

• SQL Introduction
• Coalesce in SQL
• DUAL in SQL