最近一朋友碰到一奇怪问题，赋予一用户dba角色，然后没有dba权限，后来发现在abc用户下dba不是default role 很奇怪！
数据库版本
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

有一用户abc，拥有角色connect、resource

SQL> select * from session_roles;


ROLE
------------------------------
CONNECT
RESOURCE

有需求要给abc用户赋予dba角色
SQL> show user  
USER is "ABC"
SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> grant dba to abc;


Grant succeeded.

登陆abc发现当前会话没有dba角色
SQL> conn abc/abc
Connected.
SQL> select * from session_roles;


ROLE
------------------------------
CONNECT
RESOURCE

查询该用户是拥有dba角色，担不是default role
SQL> select * from user_role_privs;


USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
ABC                            CONNECT                        NO  YES NO
ABC                            DBA                            NO  NO NO
ABC                            RESOURCE                       NO  YES NO

很奇怪，用户abc自创建后没有指定过default role，为什么赋予dba角色时，默认不是default role。
解决：
1、在当前用户会话下，临时解决
SQL> set role connect,resource,dba;


Role set.

查询session_roles
SQL> select * from session_roles;


ROLE
------------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_SELECT_ROLE
EXECUTE_CATALOG_ROLE
HS_ADMIN_EXECUTE_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
DATAPUMP_EXP_FULL_DATABASE


ROLE
------------------------------
DATAPUMP_IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDB_SET_INVOKER
OLAP_XS_ADMIN
OLAP_DBA


21 rows selected.
但是查询user_role_privs
SQL> select * from user_role_privs;


USERNAME                       GRANTED_ROLE                   ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
ABC                            CONNECT                        NO  YES NO
ABC                            DBA                            NO  NO NO
ABC                            RESOURCE                       NO  YES NO

2、永久解决
SQL> conn / as sysdba
Connected.
SQL> alter user abc default role all;


User altered.