When granting roles to users we need to remember two things:
- Any child role of the granted role is granted to the user as well (and this is hierarchical)
- You can turn on and off roles in your session using the SET ROLE command
So now comes the question, how can I see ALL roles that are active in my session (only active ones and including all roles granted to me hierarchically)?
First, let’s create a user and some roles:
SQL> create user gotodba identified by gotodba;
User GOTODBA created.
SQL> create role role1;
Role ROLE1 created.
SQL> create role role2;
Role ROLE2 created.
SQL> grant role2 to role1;
Grant succeeded.
SQL> grant role1,role3,connect to gotodba;
Grant succeeded.
SQL> alter user gotodba default role all except role3;
User GOTODBA altered.
Let’s see all the granted roles:
SQL> select granted_role,default_role
2* from dba_role_privs where grantee='GOTODBA';
GRANTED_ROLE DEF
------------------------------ ---
ROLE1 YES
CONNECT YES
ROLE3 NO
In order to see only active roles for a session, we’ll connect with the user:
SQL> conn gotodba/gotodba
Connected.
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
ROLE1
ROLE2
As you can see, ROLE3 is not there (because it’s disabled), but ROLE2 is (because it’s inherited from ROLE1.
