Grant and Revoke of Privileges

Grant and Revoke of Privileges

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 ;