Data Control Language (DCL) SQL commands with examples!

In a big organization there are thousands of people working on same database but not every one needs access to everything. For eg: A sales representive should be given to only write his personal details and no one else, or considering bank account holder, he/ she should be granted the read rights to his/her own data only. For such scenarios, Data Control Language Statements are used. They are generally to grant privileges on tables, views, sequences, synonyms, procedures to other users or roles.

What is DCL?

Data Control Language (DCL) is subset of SQL commands available to interact with a database.

What does it do?

It grants or revokes privileges to user to access parts of database.

Advertisement
Why is DCL needed?

The aim is to give only required rights to users so as to avoid any haphazard deletion or misuse of information from the database.

Types of DCL statements & uses

1. Grant (to grant privileges on objects such as tables, view, procedure to other users or roles), 2. Revoke(to revoke privileges already granted to other users) are two popular DCL commands.

Privileges & Types

Privileges as the name suggests are permissions granted to user. Privileges are of two types : ▪ System PrivilegesObject privileges

▪ System Privileges

They are normally granted by a DBA i.e. Database Administrator to other users and they include permissions to create TABLE, SESSION or USERs.

▪ Object privileges

It means privileges granted on objects such as tables, views, synonyms, procedure by object owners.

Object privileges types

ALTER : Allows the grantee USER to change the table definition with the ALTER TABLE command.

DELETE : Allows the USER to remove the records from the table with the DELETE command.

INDEX:    Allows the grantee to create an index on the table with the CREATE INDEX command.

INSERT :  Allows the USER to add records to the table with the INSERT command.

SELECT :  Allows the USER to query the table with the SELECT command.

UPDATE : Allows the USER to modify the records in the tables with the UPDATE command.

Basic DCL Syntax & Types

GRANT: Use to grant privileges to other users or roles

REVOKE: Use to take back privileges granted to other users and roles

GRANT DCL QUERY

GRANT <object privileges>

ON <object_name>

TO <User_Name>

[WITH GRANT OPTION] 

Example

To allow all privileges:

GRANT ALL ON Table_Student TO user_xyz;

Now, if we further want to allow user_xyz to grant privileges we would additionally add WITH GRANT OPTION at ending of above example.

To allow only read privileges:

GRANT SELECT ON Table_Student TO user_xyz;

REVOKE DCL QUERY

REVOKE <Object_Privileges>

ON <Object_Name>      

FROM <User_Name>

Example

 REVOKE  UPDATE                  ON Table_Student                 FROM user_xyz;