Default Role If you've never altered a user to assign one or more roles as his default roles, all the roles granted to him are "default" and so enabled. If you assign one or more roles as his default roles, those not assigned become non-default and so disabled; he has to explicitly SET ROLE to enable them. You can see the "status" of the roles in DBA_ROLE_PRIVS DEFAULT_ROLE column. What's not documented is that if he's assigned certain default roles, SYS.USER$.DEFROLE changes from 1 to 2, meaning "roles in defrole$" according to $ORACLE_HOME/rdbms/admin/dcore.bsq defrole number not null, /* default role indicator: */ /* 0 = no roles, 1 = all roles granted, 2 = roles in defrole$ */ At this time, SYS.DEFROLE$ table contains the numbers for his default roles. You match the numbers with SYS.USER$.USER# to get the role names. (To rollback this action, ALTER USER username DEFAULT ROLE ALL.) If these roles are revoked, these roles are deleted from SYS.DEFROLE$. Surprisingly, column SYS.USER$.DEFROLE remains 2! This means the comment "roles in defrole$" is not strictly correct. As a result, newly granted roles will all be disabled, including the same roles granted earlier and made default (SYS.DEFROLE$ is empty so Oracle has no memory of the previous default roles; it just assumes the user "has" default roles and considers all as non-default). Again, ALTER USER username DEFAULT ROLE ALL can correct it. SQL> create user test identified by test123; User created. SQL> grant select_catalog_role, imp_full_database to test; Grant succeeded. SQL> select granted_role, default_role from dba_role_privs where grantee = 'TEST'; GRANTED_ROLE DEF ------------------------------ --- SELECT_CATALOG_ROLE YES IMP_FULL_DATABASE YES SQL> select user#, defrole from sys.user$ where name = 'TEST'; USER# DEFROLE ---------- ---------- 477 1 <-- 1 means all roles, including future ones, are default (enabled) SQL> alter user test default role select_catalog_role; User altered. SQL> select granted_role, default_role from dba_role_privs where grantee = 'TEST'; GRANTED_ROLE DEF ------------------------------ --- SELECT_CATALOG_ROLE YES IMP_FULL_DATABASE NO <-- no longer default; user must SET ROLE ALL or SET ROLE IMP_FULL_DATABASE in his session to enable it SQL> select user#, defrole from sys.user$ where name = 'TEST'; USER# DEFROLE ---------- ---------- 477 2 <-- 2 means the default role(s) in defrole$ SQL> select * from sys.defrole$ where user# = 477; USER# ROLE# ---------- ---------- 477 6 SQL> select name from sys.user$ where user# = 6; NAME ------------------------------ SELECT_CATALOG_ROLE <-- role# 6 SQL> revoke select_catalog_role from test; Revoke succeeded. SQL> select * from sys.defrole$ where user# = 477; no rows selected SQL> select defrole from sys.user$ where name = 'TEST'; DEFROLE ---------- 2 <-- not reset to 1! SQL> select granted_role, default_role from dba_role_privs where grantee = 'TEST'; GRANTED_ROLE DEF ------------------------------ --- IMP_FULL_DATABASE NO SQL> grant exp_full_database to test; Grant succeeded. SQL> select granted_role, default_role from dba_role_privs where grantee = 'TEST'; GRANTED_ROLE DEF ------------------------------ --- EXP_FULL_DATABASE NO IMP_FULL_DATABASE NO <-- so new role won't be default (auto enabled) SQL> alter user test default role all; User altered. SQL> select granted_role, default_role from dba_role_privs where grantee = 'TEST'; GRANTED_ROLE DEF ------------------------------ --- EXP_FULL_DATABASE YES IMP_FULL_DATABASE YES