What is DCL in SQL? - Scaler Topics

We know that SQL allows a user to define the structure and organization of data to be stored and the relationships among stored data items and to retrieve the stored data from the database. It also allows the user to manipulate and update the existing data from the database of an application program. Hence, in this article, we will learn about how SQL can be used to condemn a user's authority to modify, add or retrieve data and protect all the data against unauthorized access. This is carried out by using Data Control Language (DCL) in SQL.

Data Control Language(DCL) deals with the commands used in SQL that permit a user to access, modify or work on the different privileges in order to control the database. It allows the database owner to give access, revoke access, and change the given permissions as and when required. DCL is basically used for enforcing data security.

Note: Any modification, like creating or deleting relations, adding or dropping attributes of relations, etc., in the database schema can be carried out only by the schema owner or the one authorized to do so.

What are the various DCL Commands Present in SQL?

The DCL commands present in SQL are

GRANT: GRANT is a DCL command used to grant(give access to) security privileges to specific database users. It is mostly used to restrict user access to INSERT, DELETE, SELECT, UPDATE, EXECUTE, ALTER or to provide privileges to user's data.
REVOKE: REVOKE is a DCL command that is used to revoke the permissions/access that was granted via the GRANT command. It is mostly used to revert back to the time when no access was specified, i.e., withdrawing the permission that was authorized to carry out specific tasks.

What is the syntax for writing DCL commands in SQL?

Syntax for writing GRANT command:

Syntax for writing REVOKE command:

Explanation:

<privileges>: Privileges here refers to the INSERT,DELETE,SELECT,UPDATE, EXECUTE, ALTER, ALL, reference privilege(reference privilege permits a user/role to declare foreign keys while creating relations) and all options provided by SQL.
<object_name>: Object could be anything amongst table, view or functions.
<user/roles>: Roles are the users to whom the privileges are granted or revoked.

Example of DCL in SQL :

Examples using GRANT command

Examples using REVOKE command The following examples show the usage of the revoke command in SQL.

Learn More

Difference between DDL and DML

Conclusion

We learned the role of providing access to database maintainers and users to ensure security and authorization.
DCL commands allow a user to restrict the user/roles from accessing the database.
It ensures security in the database when the data is exposed to multiple users working on the database.
One must remember that a user having access to authorization can pass on the authorization to another user and that authorization can be revoked at any time by the user.

Having learned the commands used in DCL, it is your turn now to play around with the commands creating simple tables using SQL.