升级10205遭遇bug222316 之 ora-1031 VS dba_role_privs.default_role =N

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 22231610205 bug之6980350， dbms_stats巨慢无比dbms_stats 11g新功能 set_param的改进相关资讯 Oracle数据库基础教程

在Oracle数据库中插入含有&符号的（03/06/2013 09:20:14）
Oracle 执行计划更改导致数据加工（02/13/2013 14:45:04）
判断Oracle Sequence是否存在（02/13/2013 14:32:26）

Oracle数据库中无法对数据表进行（02/26/2013 14:24:58）
Oracle 在同一台主机上建立用户管（02/13/2013 14:40:58）
Oracle em 无法启动,报not found错（02/13/2013 14:29:48）

本文评论查看全部评论（0）