Database Protection | DISCRETIONARY ACCESS CONTROL | access control

DISCRETIONARY ACCESS CONTROL

The typically method of enforcing discretionary access control in a database system is based on the granting and revoking privileges.

1. GRANTING PRIVILEGES
The grant command is used to provide system level privileges .System level privileges are those privileges that you need to actually do something on the system.
EXAMPLE- Your grant system privileges to a user so that they can

• Connect to the database
• Create objects(create table,index)
• Perform DBA activities like backup and database 
• Alter session related parameters

2 .REVOKING PRIVILEGES- 

The revoke command is used to revoke system level privileges that were previously granted with the grant command. Simple enter the privilege you wish to revoke in the body of the revoke command. 

EXAMPLE OF GRANT AND REVOKE PRIVILEGES-

• 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.