Authorization

There are several forms of authorizations on parts of the database. Authorizations on data include :

•  Authorization to  read data
•  Authorization to  insert new data
•  Authorization to  update data
•  Authorization to  delete data

Granting of Privilege :

The SQL DDL has two statements :    

1. Grant
2. Revoke

There are four types of privileges here :                      

1. Select
2. Insert
3. Update
4. Delete

The ‘grant’ statement is used to confer authorization.

Syntax :                

grant <privilege list>
on  <relation name >
to  < user list > ;

Example-1 :

grant   select  on  STUD  to   abc,  pqr ;

This statement allows abc, pqr to run queries on STUD relation.

Example-2 :

grant update (SName) on STUD to def, mnp ;

This statement allows def, mnp to update values on the SName attribute of STUD relation.

Example-3 :

Grant insert on STUD to ghi, jkl ;           

This statement allows ghi, jkl to insert tuples in STUD relation.

Example-4 :

Grant delete on STUD to stu, xyz ;        

This statement allows stu, xyz to delete  tuples from  STUD relations.

Revoking of Privilege :

To revoke an authorization, one uses ‘revoke’ statement.

Syntax :              

revoke <privilege list>
on  <relation name >
from  < user list > ;

Example-5 :

Revoke   select  on  STUD  from pqr ;   

This statement is used to cancel the authorization ‘select’ from pqr.

Example-6 :

Revoke   insert  on  STUD  from ghi ;    

This statement is used to cancel the authorization ‘insert’ from ghi.

Roles :

Roles are like designations.

For example, dean, professor, lecturer, instructor,

Example-7 :

create role professor ;
grant professor to  xxx ;
create role dean ;
grant dean to yyy ;

Note : Any authorization that can be granted to a user can be granted to a role.All privileges can be granted to a user/role.

Authorization on Views :

Similar to a relation, the authorization on various privileges can be given on views. The user may be authorized to use particular privilege on some part of the data, which can be known as ‘view’. A user who creates a view need not be given ‘update’ authorization on views, without giving ‘update’ authorization on the relation, from which the view is created.

Transfer of Privileges :

A user can be allowed to be given some authorization and allow them to grant their privilege to others.

grant  select on STUD to zzz with grant option ;