Explain Data Control Language (DCL) with Examples in DBMS: A Data Control Language (DCL) can be defined as a computer language that is used for controlling privilege in the database. The privileges are required for performing all the database operations, such as creating sequences, views or tables. It is a part of the Structured Query Language.

Explain Data Control Language (DCL) with Examples in DBMS.

Types of Privileges

There are two main types of privileges in the database:

  • System Privileges

 System privileges are used for performing a particular type of action on objects, such as cache groups, synonyms, materialized views, tables, views, sequences, indexes, replication schemes, and PL/SQL procedures & functions. This type of privileges can only be granted or revoked by the instance administrator or a user. 

 

  • Object Privileges

Object privileges can be defined as the right for performing a special type of action on objects like materialized views, sequences, replication schemes, cache groups, synonyms, tables, views, etc. This type of privilege cannot be revoked and the owner of the object can grant object privileges.

Data Control Languages (DCL) Commands

There are two types of commands in the data control languages:

  1. Grant Command

Grant Command is used for offering access or privileges to the users on the objects of the database. Through this command, the users get access to the privileges in the database.

The General Syntax for the Grant Command is mentioned below:

GRANT privilege_name

ON object_name

TO {user_name I PUBLIC I role_name}

[WITH GRANT OPTION];

For Example

GRANT ALL ON workers

TO MNO;

[WITH GRANT OPTION]

In the given example, the permission to view and modify the details in the ‘workers table’ has been given to the user MNO.

  1. Revoke Command

The main purpose of the revoke command is canceling the previously denied or granted permissions. Through the revoke command, the access to the given privileges can be withdrawn. In simple words, the permission can be taken back from the user with this command.

The general syntax for the revoke command is mentioned below:

REVOKE<privilege list>

ON <relation name or view name>

From <user name>

For Example

REVOKE UPDATE

ON worker

FROM MNO;

Differences between the Grant and Revoke Command

             Grant Command      Revoke Command

A user is allowed to perform some particular activities on the database by using Grant Command.

 A user is disallowed to performing some particular activities by using the revoke command.
The access to privileges for database objects is granted to the other users. The access to privileges for database objects that is granted previously to the users can be revoked.

So it was all about Data Control Language (DCL) command with examples in DBMS. If you liked then please share it with your friends.

Explain Data Control Language (DCL) with Examples in DBMSSumit ThakurUncategorizedExplain Data Control Language (DCL) with Examples in DBMS: A Data Control Language (DCL) can be defined as a computer language that is used for controlling privilege in the database. The privileges are required for performing all the database operations, such as creating sequences, views or tables. It is...Let's Define DBMS