Change Schema Name & ORA-01031: insufficient privileges (on SYS.DMBS_SESSION), ORA-28100, etc...

虽然平时很少会遇到改动一个schema名字的问题，但是并不是不会遇到, 今天就很不幸给碰到了 :-)

 

话说之前用DBCA做了一个数据库A的模板（包含数据文件），然后在另外一台机器上clone出了一个数据库叫B。因为是Clone嘛，自然里面的Schema都是一样的。但是为了方便区分，希望将其中的一个schema改名字，也就是将schema QA 改成 CI. 

 

之前没有做过类似的操作，因此不敢贸贸然行动，google了一下，果然还是有前人的实验案例的，见这里。 于是乎，依葫芦画瓢照做一番，如下所示...

 

 

SQL> show user
USER is "SYS"
SQL> select user#, name, password from user$ where name='LO_XXX_QA';

     USER# NAME                           PASSWORD
---------- ------------------------------ ------------------------------
        64 LO_XXX_QA                      3D2958B93CD8FFE3

SQL> update user$ set name='LO_XXX_CI' where user#=64;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> alter user lo_xxx_ci identified by a;

User altered.

SQL> conn lo_xxx_ci/a@10.80.14.106/lo
Connected.

 

 

 

 

看来更改schema的名字没有想象中的那么困难，如果故事到这里就结束了，那我没有必要费事写这篇部落格了 :-)  

 

等我连上这个数据库之后，进行一些操作的时候，遇到了如下这个问题...

 

SQL> @db_update_2010-08-05_001.sql
BEGIN
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 90
ORA-06512: at "LO_XXX_CI.PACK_CONTEXT", line 1065
ORA-06512: at "LO_XXX_CI.PACK_DDL", line 56

 

 

表面上来看，是因为当前的schema user没有对dbms_session的执行权限，这就有点奇怪了，因为clone的模板数据库里面的对应schema是拥有这个权限的。先不管，grant看看如何，

 

SQL> grant execute on dbms_session to lo_xxx_ci;

 

但是完了之后，重复上面的操作还是报同样的错误！ 看来问题就不会想表面上看起来这么直观简单了，需要进一步的dig一番了。 网上关于这个问题的讨论也有一些，参见这里， 还有ASKTOM. Tom系列里给出如下一个范例，让我找到了解决问题的方向， 

 

 

 

 

 造成ORA-01031的错误根源在于源schema中用到了context， 虽然改变了schema的名字，但是并没有改变context用到的package的owner。 可以通过查询dba_context看到，如下。。。

 

SQL> select * from dba_context;

NAMESPACE                      SCHEMA                         PACKAGE                        TYPE
------------------------------ ------------------------------ ------------------------------ -------------------
REGISTRY$CTX                   SYS                            DBMS_REGISTRY_SYS              ACCESSED LOCALLY
LT_CTX                         WMSYS                          LT_CTX_PKG                     ACCESSED LOCALLY
DR$APPCTX                      CTXSYS                         DRIXMD                         ACCESSED LOCALLY
EM_GLOBAL_CONTEXT              SYSMAN                         SETEMUSERCONTEXT               ACCESSED GLOBALLY
EM_USER_CONTEXT                SYSMAN                         SETEMUSERCONTEXT               ACCESSED LOCALLY
LO_XXX_QA                      LO_XXX_QA                      PACK_CONTEXT                   ACCESSED LOCALLY

6 rows selected.

 

 

 这里我可以通过简单地重新创建一个context来解决这个问题，如下...

 

SQL> create or replace context LO_XXX_CI USING PACK_CONTEXT;

SQL> select * from dba_context;

NAMESPACE                      SCHEMA                         PACKAGE                        TYPE
------------------------------ ------------------------------ ------------------------------ ---------------------
REGISTRY$CTX                   SYS                            DBMS_REGISTRY_SYS              ACCESSED LOCALLY
LT_CTX                         WMSYS                          LT_CTX_PKG                     ACCESSED LOCALLY
DR$APPCTX                      CTXSYS                         DRIXMD                         ACCESSED LOCALLY
EM_GLOBAL_CONTEXT              SYSMAN                         SETEMUSERCONTEXT               ACCESSED GLOBALLY
EM_USER_CONTEXT                SYSMAN                         SETEMUSERCONTEXT               ACCESSED LOCALLY
LO_XXX_QA                      LO_XXX_QA                      PACK_CONTEXT                   ACCESSED LOCALLY
LO_XXX_CI                      LO_XXX_CI                      PACK_CONTEXT                   ACCESSED LOCALLY

7 rows selected.

SQL>

 

 

现在回过头来重复之前的操作，会发现ORA-01031这个问题不见了。 

 

本以为大功告成，结果在进行其他操作的时候又抛出了如下的错误。。。

 

 ORA-28100: policy function schema LO_XXX_QA is invalid  

 

不过这个问题很容易想到出在什么地方，因为这个schema用到了VPD，创建了大量的policy，应该就是在改动schema名字之后，造成了policy function的owner的信息还停留在之前的schema，可以检测一下，如果查询user_policies视图会发现没有一条数据，因为当前的schema已经改成了lo_xxx_ci，可以查询dba_policies，通过限制object_owner='LO_XXX_QA'可以查到当前schema "应该“存在的所有Policy。 

 

解决问题的方法，应该是先把这些Policy删除，然后再重新创建这些policy，可以通过如下的代码来做...

 

BEGIN

for rec in (
SELECT OBJECT_OWNER, 
            OBJECT_NAME, 
            POLICY_NAME, 
            PF_OWNER AS FUNCTION_SCHEMA, 
            PACKAGE || '.' || FUNCTION AS POLICY_FUNCTION,
            SUBSTR(
             DECODE(SEL, 'YES', ',SELECT', '') ||
             DECODE(INS, 'YES', ',INSERT', '') ||
             DECODE(UPD, 'YES', ',UPDATE', '') ||
             DECODE(DEL, 'YES', ',DELETE', ''), 
             2) AS STATEMENT_TYPES,
             CHK_OPTION  AS UPDATE_CHECK,
             POLICY_TYPE
FROM 
     DBA_POLICIES  
WHERE OBJECT_OWNER='LO_XXX_QA'
)

LOOP
   
   dbms_rls.drop_policy(rec.object_owner, rec.OBJECT_NAME, rec.policy_name);
   
   dbms_rls.add_policy(
                        object_schema    => user,             -- update object owner
                        object_name    => rec.OBJECT_NAME,
                        POLICY_NAME    => REC.POLICY_NAME,
                        FUNCTION_SCHEMA    => user,             -- update policy function owner
                        policy_function    => rec.POLICY_FUNCTION,
                        STATEMENT_TYPES    => REC.STATEMENT_TYPES,
                        update_check    => case rec.UPDATE_CHECK when 'YES' then true else false end ,
                        policy_type    => dbms_rls.shared_context_sensitive
                    );

end loop;

END;

 

 

 

经过这么一番操作，就把这些policy”还原“回来了，这下应该算是”大功告成“了。

 

 

小结一下：

从这次事件可以看出，Oracle之所以没有提供类似改密码alter user blah blah blah 这么简单的操作来更改schema的用户名，就是在”提醒“我们不要轻易这么去尝试，因为会带来”一系列“的负面影响。虽然可以更改schema的用户名，但是需要更改oracle的数据字典表($表），显然这不是推荐的做法，只能用于非常情况。

 

也许ORA-01031和 ORA-28100只是更改schema名字带来的副作用的”冰山一角“，但是这个至少能提醒下次再做类似更改操作的时候多考虑一下，未雨绸缪呀!