Sunday, December 16, 2012

0 Oracle Role

Role is grouping of the privilege. It is used to simplify the privilege management.

Relate tables

  • DBA_ROLES: List all the Roles
  • DBA_ROLE_PRIVS: List Ownership of the roles and default roles
  • ROLE_SYS_PRIVS: List role’s system privilege
  • ROLE_TAB_PRIVS: List role’s object privilege

Create role

create role TestRoleA identified by oracleA;
grant create procedure, create any table to TestRoleA;
grant select on PO3.t1 to TestROleA;
create role TestRoleB identified by oracleB;
grant create session to TestRoleB;
create role TestRoleC;
grant insert any table, select any table to TestRoleC;


select * from DBA_ROLES where ROLE like 'TEST%';
select * from ROLE_SYS_PRIVS where ROLE like 'TEST%';
select * from ROLE_TAB_PRIVS where ROLE like 'TEST%';


Grant Role

grant TestRoleA to PO;



Default Role

Default Role is the role user automatically entitle to when user establish the session. One user can have multiple default roles. However, only the role without the identified would be effective upon the login.

 select * from DBA_ROLE_PRIVS where GRANTEE='PO';


In below example, the TESTTOLEA and TESTROLEB is protected by password, therefore when PO user establish the session, only TESTROLEC and TESTROLED would be effective.



Role is being effective after the session is establish. If the user being grant to the new role while its already connect to the server. The new role only will be effective for the new session not the current session.

Set role : Change the current role

Use set role command can change the current user role.

set role TestRoleA;
set role TestRoleA identified by oracleA;
select * from session_roles;



The set role command would reset all the role. In this example, the PO user already have TESTROLEC and TESTROLED, since we only enable the TESTROLEA, PO will only get the TESTROLEA privilege. 

To enable multiple role, we have include all the role in the set command.

Below example, enable TestRoleA,TestRoleB,TestRoleC and TestRoleD. TestRoleA and TestRoleB has the different password.

set role TestRoleA identified by oracleA, TestRoleC,TestRoleD,TestRoleB identified by oracleB;


Set role NONE and Set role ALL

set role none, disable all the role.


set role all enable all the role, however it can not be used to the role with the password so we have to use except.



Post a Comment


SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates