Managing Role

Creating a Role:

create role clerk;

 

Assign Privilege to role:

grant create session,create table to clerk;


Assign More Priviledge to the role:

SQL>Create table test(id number); 

SQL>grant select,insert,update on test to clerk;

 

Add Another Layer To The Heirarchy:

SQL>CREATE ROLE manager;

SQL> GRANT clerk TO manager;

SQL> GRANT DELETE ON test TO manager;

 

Assigning Role to user:

GRANT clerk TO liton;

GRANT manager TO arif;

 

Granting System Priviledge:

GRANT CREATE SESSION to manager WITH ADMIN OPTION;

 

Revoke Role From a User:

REVOKE manager FROM arif;


Drop a Role:

DROP ROLE manager;

 

Obtaining Role Information:

  • DBA__ROLES
  • DBA_ROLES_PRIVS
  • ROLE_ROL_PRIVS
  • DBA_SYS_PRIVS
  • ROLE_SYS_PRIVS
  • ROLE_TAB_PRIVS
  • SESSION_ROLES
Comments