GotoDBA Database Security,Did You Know,Infrastructure Did You Know #36 – List All Active Roles

Did You Know #36 – List All Active Roles

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Post