SQL Command DCL

 

 DCL (Data Control Language)

The data in the database is an important asset of any organization. It shouldn’t be provided to all the users instead each and every user should be provided access to the data according to some privileges and rights. All this should be done to secure the data from illegal access. DBMS (Database Management System) should provide the mechanism to ensure that only the authorized user can access the data. Each user is given appropriate access rights to specific data objects (tables and views). In SQL these privileges and access rights can be assigned by the commands.

What is DCL?

DCL is a Data Control Language that contains commands related to assigning user rights and privileges. These commands are Grant and Revoke commands.

Grant command:

Grant command is used to provide access rights on database objects to specific users. Normally Grant statement is used by the owner of the table to provide access to the other users. The user who creates the table using CREATE TABLE statement then he/she become the owner of the table and gets full privileges for the table. Other users at the beginning have no privileges on the newly created table The owner can deliberately assign some privileges to the other users using the GRANT command. But in the case of creating VIEW, the user doesn’t receive full privileges.

Granting Privileges To Other Users:

Syntax:

GRANT privileges List | ALL PRIVILEGES

ON ObjectName

To AuthorizationIdList | PUBLIC

WITH GRANT OPTION

Privilege List consist of one of the following list separated by a comma:

SELECT, DELETE, INSERT, UPDATE, REFERENCES, USAGE.

For convenience, the GRANT statement allows the keyword ALL PRIVILEGES  instead of using the upper six privileges. It also provides the keyword PUBLIC to allow access to all the users instead of the specific user who is using DBMS.

Object Name: It is the name of Base table (main database) View (virtual table derived from base table) or domain.

WITH GRANT OPTION: It is the clause that gives the right or privilege to the user in the Authorized List to pass the given privileges to the other users. If this keyword is not specified, the owner of the object (table, view) maintains very tight control over who has permission to use that object.

Example: (GRANT All Privileges)

Let suppose there is an Employee table and we want to give the authorization identifier to the Manager.

GRANT ALL PRIVILEGES

ON Employee

TO Manager WITH GRANT OPTION;

Now the user who is Manager can retrieve the rows from Employee table and can perform insert, update, delete, etc operations as all privileges are provided. Due to WITH GRANT OPTION he/she has the right to further assign these privileges to others.

Example: (Grant Specific Privileges)

If we want to give privileges to the Clerk and Director to Select and Update the column salary in the Employee table.

GRANT SELECT UPDATE (salary)

ON Employee

TO Clerk, Director;

As we have omitted WITH GRANT OPTION so both users Clerk and Director cannot pass the given privileges to the other users.

Example: ( Grant Specific Privileges To Public)

If we want to give privileges to all the users to access or retrieve the data of the Department table.

GRANT SELECT

ON Department

TO PUBLIC

Now due to the PUBLIC keyword all the users (now  OR  in future) can access the data from the Department table.

REVOKE COMMAND:

REVOKE command is used to take back privileges that are assigned through the GRANT statement. This command is used to take away all or some of the privileges granted to a user.

Its syntax is as follows:

REVOKE [GRANT OPTION FOR] {Privilege list | ALL PRIVILRGES}

ON Object name

FROM {AthorizationIdList | PUBLIC} [RESTRICT | CASCADE]

REVOKE: it is the keyword for taking away the privileges.

GRANT OPTION FOR: It is the clause to take back the privileges that are assigned through WITH GRANT OPTION in the GRANT statement. This means that now the user cannot assign the privileges to the other users.

PRIVILEGE LIST: it is the list of privileges to be taken back like SELECT, UPDATE, etc.

 AthorizationIdList | PUBLIC: AthorizationIdList is the list of the specific user however public denotes all the users.

RESTRICT | CASCADE: RESTRICT is used to tack back the privilege from only the user to whom it is assigned but the cascade will take away privileges from the user himself as well as from the other users to whom he/she assigns those privileges. For example, there is a user_A who assign the INSERT privilege to userB and userB further assign it to user_C Now if INSERT privilege is revoked from the user_A with CASCADE it will automatically be taken away from user_B and user_C.

 Example: Revoke Specific Privilege From Public:

if we want to revoke the privilege of accessing data of Staff table from all users.

REVOKE SELECT

ON Staff

FROM PUBLIC;

Example: Revoke All Privileges From Specific User:

Revoke all privileges you have given to Director on Employee table.

REVOKE ALL PRIVILEGES

ON Employee

FROM Director

In short, DCL is a data control language that contains some commands to assign and taking away some user’s data access rights and privileges to maintain the security and integrity of data. By applying these commands data in the database remain secure and reliable.