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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s