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