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