Friday, 6 October 2017

Grant and Revoke of Privileges

Data Control Language

Data Control Language (DCL):-Only "grant" and "revoke" commands are coming under this category.By DCL commands we can grant access and revoke access on objects.

Privileges:-Privileges are permission to access the system/schema's objects
 

Types of Privileges:- There are 2 types of privilege
1) System Privileges
2) Object Privileges


System Privileges:-System Privileges are the privilege given on system's objects.
                    System privilege are not dependent on existence of Granter.
 

Example:-If user A gives "create session" privilege to user B with "admin option" and B user gives same privilege (create session) to user C. Then "create session" privilege will remain to C user even B user got dropped.                     

Object Privileges:-Object Privileges are the privilege given on Schema's Objects.
                    Objects privilege are dependent on existence of Granter.


Example:-If user A gives "select on tab1" privilege to user B with "grant option" and B user gives same privilege  (select on tab1) to user C. Then privilege "select on tab1" will revoked from C user if B user get dropped.

       
Grant:- Grant command is used to give the privilege.
Object Priv:-
grant select on emp to hr;
grant select,delete,update on emp to hr;
grant select on emp to hr with grant option;
grant all on emp to hr;
grant all on emp to public;
System Priv:-
grant create session to hr ;
grant developer to hr ;
grant developer to hr with admin option;


Procedure for Granting "select,update,delete,insert" on all tables and views to user "xyz".

SQL>BEGIN
    FOR i IN (SELECT * FROM user_tables)
    LOOP 
        EXECUTE IMMEDIATE
 '
GRANT SELECT, UPDATE, INSERT, DELETE ON ' || i.table_name || ' TO  xyz';  
    END LOOP;
END;
/


Procedure for Granting "select,update,delete,insert" on all tables and views   and "execute" on procedure,function and package to user "xyz" on schema "ABC".

SQL>BEGIN
  FOR i IN (SELECT object_name, object_type FROM all_objects WHERE owner
='ABC' AND object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','PACKAGE')) LOOP
    
IF i.object_type IN ('TABLE','VIEW') THEN
      
EXECUTE IMMEDIATE 'GRANT SELECT, UPDATE, INSERT, DELETE ON ABC.'||i.object_name||' TO xyz';
   
 ELSIF i.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') THEN
      
EXECUTE IMMEDIATE 'GRANT EXECUTE ON ABC.'||i.object_name||' TO xyz';
   
 END IF;
  
END LOOP;
END;
/



Revoke:-Revoke command is used to revert back privilege
Object Priv:-
revoke select on emp from hr;
revoke select,delete,update on emp from hr;
revoke all on emp from hr;
revoke all on emp from public;
System Priv:-
revoke create session from hr ;
revoke developer from hr ;


Related Topics :-
Table Creation with constraint and virtual column 
Insert, Update and Delete Operation in Oracle SQL 
Different ways to Insert record into Table 
Order by Clause in Oracle SQL 
Null concept in Oracle SQL 
Operators in Oracle SQL 
Subquery in Oracle SQL 

No comments:

Post a Comment

Cloning database with RMAN

Below are the steps to clone production database(primedb) to UAT database(clonedb)   STEP1 :- Check the both servers are able to connect ...