OS: HP
DB: 10204升级到10205
症状：
原本正常的操作升级后一直抱ora-1031错误，如insert into a as select * b;
对表b 的select权限赋予了角色r，然后r被grant给当前用户u，但是却报错没有操作权限，若直接grant select on b to u插入操作可执行成功；
又是开了SR，又是经过好几天的信息更新，中间MOS居然还提供了方案“You need to drop the invalid sys objects as mentioned in the doc :- Invalid x_$ Objects After Upgrade (Doc ID 361757.1) .””，看来SR的信息更新很重要，一定要准确和完整，否则坑人又坑己 。
最后终于找到了如下解决方案：
 
Problem Description:
====================
Database Administrator grants privledges to a role, then grants this role to a
user.  When THE user tries to execute those privileges through this role, it
fails. However, user can execute privileges directly.
 
 
Problem Explanation:
====================
 
In SYS.DBA_ROLE_PRIVS this role shows up but DEFAULT_ROLE column is set to 'N'
therefore role cannot be executed as it is not a default.
 
SYS.DBA_ROLE_PRIVS shows roles granted to users and roles:
 
SVRMGR> desc dba_role_privs;
 
Column Name                    Null?    Type
------------------------------ -------- ----
GRANTEE  VARCHAR2(30)
GRANTED_ROLE                                  NOT NULL VARCHAR2(30)
ADMIN_OPTION                   VARCHAR2(3)
DEFAULT_ROLE                   VARCHAR2(3)
 
SVRMGR> select * from dba_role_privs
     2> where GRANTEE='AL';
 
GRANTEE                        GRANTED_ROLE                 ADM DEF
------------------------------ ------------------------------ --- ---
AL                              ABC                            NO  NO
AL                                                  ABCD                                                NO  NO
AL                             CONNECT                                NO  YES
AL                             RESOURCE                              NO  NO
4 rows selected.
 
Therefore the only role user AL can execute is Connect and neither role ABC nor
ABCD cannot be executed.
 
 
Search Words:
=============
 
ORA-00942: table or view does not exist
ORA-01031: insufficient privileges
 
 
Solution Description:
=====================
Issue the following statement to make all the roles
granted to user as default roles :
 
ALTER USER_NAME DEFAULT ROLE ALL;
 
 
Solution Explanation:
=====================
If one of the roles was altered as default, that will be the only role showing
as default.  To be able to execute a role it needs to appear as default in
SYS.DBA_ROLE_PRIVS:
 
SVRMGR> connect system
Password:
Connected.
SVRMGR> alter user al default role all;
Statement processed.
SVRMGR> select * from dba_role_privs
     2> where GRANTEE='AL';
GRANTEE                                                GRANTED_ROLE                          ADM DEF
------------------------------ ------------------------------ --- ---
AL                            ABC                             NO  YES
AL                             ABCD                                                             NO  YES
AL                             CONNECT                        NO  YES
AL                             RESOURCE                       NO  YES
4 rows selected.
 
All roles show as default therefore all privileges that were granted to a role
can now be executed not only directlty.
 
Solution References:
====================
BUG 222316