0
19kviews
Explain authorization in sql.
1 Answer
2
773views
  • 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.

    iii. Syntax:

    GRANT <privilege list>
    ON<relation name or view name>
    TO<user/role list>
    

    iv. Example:

    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.

    iii. Syntax:

    REVOKE <privilege list>
    ON<relation name or view name>
    FROM <user/role list>[restrict/cascade]
    

    iv. Example:

    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.
    
Please log in to add an answer.