- Authorization is finding out if the person, once identified, is permitted to have the resource.
- Authorization explains that what you can do and is handled through the DBMS unless external security procedures are available.
- Database management system allows DBA to give different access rights to the users as per their requirements.
Basic Authorization we can use any one form or combination of the following basic forms of authorizations
i. Resource authorization:-Authorization to access any system resource. e.g. sharing of database, printer etc.
ii. Alternation Authorization:- Authorization to add attributes or delete attributes from relations
iii. Drop Authorization:-Authorization to drop a relation.
Granting of privileges:
i. A system privilege is the right to perform a particular action,or to perform an action on any schema objects of a particular type.
ii. An authorized user may pass on this authorization to other users.This process is called as ganting of privileges.
GRANT <privilege list> ON<relation name or view name> TO<user/role list>
The following grant statement grants user U1,U2 and U3 the select privilege on Emp_Salary relation:
GRANT select ON Emp_Salary TO U1,U2 and U3.
Revoking of privileges:
i. We can reject the privileges given to particular user with help of revoke statement.
ii. To revoke an authorization, we use the revoke statement.
REVOKE <privilege list> ON<relation name or view name> FROM <user/role list>[restrict/cascade]
The revocation of privileges from user or role may cause other user or roles also have to loose that privileges.This behavior is called cascading of the revoke.
Revoke select ON Emp_Salary FROM U1,U2,U3.
Some other types of Privileges:
i. Reference privileges:
SQL permits a user to declare foreign keys while creating relations.
Example: Allow user U1 to create relation that references key ‘Eid’ of Emp_Salary relation.
GRANT REFERENCES(Eid) ON Emp_Salary TO U1
ii. Execute privileges:
This privileges authorizes a user to execute a function or procedure.
Thus,only user who has execute privilege on a function Create_Acc() can call function.
GRANT EXECUTE ON Create_Acc TO U1.