Data Control Language(DCL)

DCL(Data Control Language) work is to deal with sql commands that are used to permit a user to access,modify and work on a database. it is used to access stored data. It gives access,revoke access and change the permission to the owner of the database as per the requirement.

• It helps in how sql can use a user authority to modify or retrieve data and protect against unauthorised access.

• It complements data manipulation language(DML) and data definition language(DDL).

• It is the simplest among the three command

• It is basically used for enforcing data security.

DCL Commands

The DCL commands present in SQL are Grant and Revoke.

Grant Command

It is used to give access to security privileges to a specific database user. It is far in most cases used to limit user access to insert, delete, select, update, execute, alter or to provide privileges to user's data.

Syntax

GRANT privileges ON object_name TO user;

• Privileges refers to INSERT, DELETE, SELECT, UPDATE, EXECUTE, ALTER, ALL

• object_name refers to table name.

• user refers to name of user to whom we grant privileges.

Example

In this example, we are going to see how the grant command works on a table.

Algorithm

• Step 1 − Use grant command

• Step 2 − Provide privileges

• Step 3 − Provide table name and user name.

• Step 4 − Use select, insert statements to get the output

Input

product_details

id	name	age	marks
1	monu	23	90
2	somu	21	98
3	sonu	22	99

Code

GRANT SELECT, INSERT#operation to be performed
ON product_details#table on which operation is done
TO monu;#user name is monu
Select * from product_details;#data selected
Insert into product_deails values(4, ‘aman’,24,100);

Output

id	name	age	marks
1	monu	23	90
2	somu	21	98
3	sonu	22	99
4	aman	24	100

Revoke command

It is used to revoke the access from the user that is being granted by the grand command. It is used to remove the privileges on user accounts for access to a database object.

Syntax

REVOKE privileges ON table_name FROM user; 

• privileges refers to Insert,Delete,Select,Update,Execute,Alter,All

• object_name refers to table name

• user refers to name of user to whom we are removing privilege

Example

In this example,we are going to see how the revoke command works on a table.

Algorithm

• Step 1 − Use revoke command

• Step 2 − Provide privileges

• Step 3 − Provide table name and user name.

Input

product_details

id	name	age	marks
1	monu	23	90
2	somu	21	98
3	sonu	22	99

Code

REVOKE SELECT, INSERT#operation to be performed
ON product_details#table on which operation is done
FROM monu;user name is monu
Select * from product_details;#data selected

Output

ERROR

Conclusion

In this article, we have learned about dcl command which is used to access the stored data. the two main components are grant and revoke. Grant is used to grant a privilege to the user whereas revoke is used to remove the privilege from the user. Note that the authorised member can pass the authorization to another user and can be revoked anytime by the user.