How to list current active roles for a user in a session?

Using SESSION_ROLES it will retrieve the current roles that are active for a user in a session.

Example:
SQL> connect test/…
SQL> select * from session_roles;

ROLE
——————————
DELETE_CATALOG_ROLE

Using “SET ROLE” one can change the roles active in the current session.
Example:
SQL> select * from session_roles;
ROLE
——————————
TEST11

Enable specific set of roles in the current session
SQL> set role test11, delete_catalog_role;
Role set.

SQL> select * from session_roles;
ROLE
——————————
DELETE_CATALOG_ROLE
TEST11

Enable all roles in the current session
SQL> set role all;
Role set.

Advertisements

DEFAULT role explained

It seems at times when roles are assigned they are not assigned as DEFAULT role to a user.

The reason for seems to be the following: In the table sys.user$ there is a column called “DEFROLE” which indicates if the role will be a default role or not. The possible values that seem to be are as follows:
0 – None (So when a role is assigned to a user the role is not a default) and none of the current roles are default. The value is set to 0 by “ALTER USER test DEFAULT ROLE NONE”.
1 – Roles assigned to users and the ones that will be assigned will be default roles. The value is set to 1 by “ALTER USER test DEFAULT ROLE ALL”.
2 – Specific roles are default roles, the list of roles that are default to a user is stored in the table “DEFROLE$” and also can be seen when querying “DBA_ROLE_PRIVS” column “DEFAULT_ROLE”. The value is set to 2 by “ALTER USER test DEFAULT ROLE <role name>” only if the value was 0, if the value is 1 it can't be changed to 2 unless to 0 (NONE) first.

So once a value of “DEFROLE” is 1, it can only be changed if the default role is set to “NONE” so any roles assigned or will be assigned are DEFAULT roles.

Example: When the value of DEFROLE = 0 (NONE) for a user
SQL> create user test identified by abcd1234;
User created.

SQL> alter user test default role none;
User altered.

SQL> grant dba to test;
Grant succeeded.

SQL> grant imp_full_database to test;
Grant succeeded.

— Shows none of the roles assigned are default roles
SQL> select * FROM dba_role_privs where grantee = 'TEST';
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST DBA NO NO
TEST IMP_FULL_DATABASE NO NO

Example: When the value of DEFROLE = 1 (ALL) for a user
SQL> create user test identified by abcd1234;
User created.

SQL> alter user test default role none;
User altered.

SQL> grant imp_full_database to test;
Grant succeeded.

SQL> grant dba to test;
Grant succeeded.

SQL> select * FROM dba_role_privs where grantee = 'TEST';
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST DBA NO NO
TEST IMP_FULL_DATABASE NO NO

SQL> alter user test default role all;
User altered.

SQL> select * FROM dba_role_privs where grantee = 'TEST';
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST DBA NO YES
TEST IMP_FULL_DATABASE NO YES

SQL> grant exp_full_database to test;
Grant succeeded.

SQL> select * FROM dba_role_privs where grantee = 'TEST';
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST DBA NO YES
TEST EXP_FULL_DATABASE NO YES
TEST IMP_FULL_DATABASE NO YES

Example: When the value of DEFROLE = 2 for a user
SQL> create user test identified by abcd1234;
User created.

SQL> alter user test default role none;
User altered.

SQL> select user#, defrole from sys.user$ where name = 'TEST';
USER# DEFROLE
———- ———-
35 0

SQL> grant dba to test;
Grant succeeded.

SQL> alter user test default role dba;
User altered.

SQL> grant imp_full_database to test;
Grant succeeded.

SQL> select user#, defrole from sys.user$ where name = 'TEST';
USER# DEFROLE
———- ———-
35 2

— Shows the DBA role is default and INP_FULL_DATABASE is not the default role
SQL> select * FROM dba_role_privs where grantee = 'TEST';
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST DBA NO YES
TEST IMP_FULL_DATABASE NO NO

How to find the date when a role was created?

The view DBA_ROLES doesn’t have the created date column to display when the role was created, but the information is stored in sys.user$ the underlying table. So using the following query one can find the date the role was created.

SQL> select name, to_char(ctime, ‘DD-MON-YYYY HH24:MI:SS’) from sys.user$ where name = ‘RESOURCE’;

NAME TO_CHAR(CTIME,’DD-MON-YYYYHH24:MI:SS’)
——– —————————————–
RESOURCE 22-JUL-2005 00:42:50