Relational Query Languages | Data control language (DCL)

DCL(Data Control Language)

The Data Control Language (DCL) authorizes users to access and manipulate data.        

COMMAND USED- GRANT, REVOKE, COMMENT

GRANT authorizes one or more users to perform an operation or a set of operations on an object.

REVOKE eliminates a grant, which may be the default grant.

COMMENT Adding the element into data dictionary

1.GRANT COMMAND- It authorizes one or more users to perform an operation or a set of operations on an object.

2.REVOKE COMMAND-It eliminates a grant, which may be the default grant.

EXAMPLE OF GRANT AND REVOKE COMMAND-

• Suppose that DBA(Data base administer) creates four accounts A1,A2,A3 and A4 and wants only A1 to be able to create base relations, then the DBA must issue the following GRANT command in SQL.               

          GRANT CREATE TABLE TO A1;

• Suppose that A1 creates two base relations EMPLOYEE AND DEPARTMENT.Hence A1 is then the owner of these two relations. 

EMPLOYEE RELATION  

NAME

SSN

BDATE

ADDRESS

SALARY

DNO

          

DEPARTMENT RELATION

DNUMBER

DNAME

MGR_SSN

• Next suppose that A1 wants to grant to account A2 ,the privilege to insert and delete tuples in both of these relations.However A1 does not want A2 to be able to propagate these privileges to additional accounts then A1 can issue the following command.

GRANT INSERT ,DELETE ON EMPLOYEE,DEPARTMENT TO A2;

          Here A2 has not given the Grant Option.

• Now suppose that A1 wants to allow account A3 to retrieve information from either of the two tables. A1 can issue the following command.

GRANT SELECT ON EMPLOYEE ,DEPARTMENT TO A3 WITH GRANT OPTION;

          The clause with GRANT OPTION means that A3 can now propagate the privileges to other              accounts by using GRANT.

          E.G.- A3 can grant the SELECT privilege on the EMPLOYEE relation to A4 by issuing the following command. 

GRANT SELECT ON EMPLOYEE TO A4;

• Now suppose that A1 decides to revoke the SELECT privilege on the EMPLOYEE relation from A3 .A1 then can issue this command.

REVOKE SELECT ON EMPLOYEE FROM A3;

         The DBMS must now automatically revoke the SELECT privilege on EMPLOYEE from A4 too because A3 granted that privilege to A4 and A3 does not have the privilege any more.

3. COMMENT COMMAND-Adding the element into data dictionary.A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement in two ways:

• Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. This text can span multiple lines. End the comment with an asterisk and a slash (*/). The opening and terminating characters need not be separated from the text by a space or a line break.
• Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.

EXAMPLE-   SELECT last_name, salary /* Select the names of the employee and their salary */

FROM EMPLOYEE /*The EMPLOYEE relation is used from where data is retrieved*/;