ORACLE重编译同义词(synonym)遇到ORA-01031总结

我们在编译INVALID的同义词(synonym)的时候，可能在某些版本会遇到一些特殊权限问题，具体来说是遇到ORA-01031错误。下面构造这样一个例子：

 

 

如下所示，scott用户下面创建了一个公共同义词emp（下面测试环境为Oracle 10.2.0.5）

 

SQL> show user;
USER is "SCOTT"
SQL> create public synonym emp for scott.emp;

Synonym created.

SQL>

 

   假设我们需要重新编译同义词，在system用户下去编译同义词时，就会遇到ORA-01031

 

SQL> show user;
USER is "SYSTEM"
SQL> alter public synonym emp compile;
alter public synonym emp compile
*
ERROR at line 1:
ORA-01031: insufficient privileges

 

 另外一种情况也会遇到这个错误，UserA 去编译UserB下的同义词的话，也会遇到这个错误。按照我的理解，如果用户拥有CREATE ANY SYNONYM和DROP ANY SYNONYM权限的话，应该是可以编译其它SCHEMA下的同义词。而且SYSTEM用户被授予了DBA角色，DBA角色拥有CREATE ANY SYNONYM 或DROP ANY SYNONYM的权限。

 

SQL>  SELECT * FROM DBA_SYS_PRIVS WHERE PRIVILEGE IN('CREATE ANY SYNONYM','DROP ANY SYNONYM') AND GRANTEE='DBA';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
DBA                            DROP ANY SYNONYM                         YES
DBA                            CREATE ANY SYNONYM                       YES


SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='SYSTEM';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
SYSTEM                         AQ_ADMINISTRATOR_ROLE          YES YES
SYSTEM                         DBA                            YES YES
SYSTEM                         MGMT_USER                      NO  YES

 

但是metalink上的文档Doc ID 1299001.1提示：同义词的编译时，要么是sys用户或者同义词的OWNER才能编译，也就是说，你要编译同义词emp，必须是scott用户或sys用户。

 

 

SQL> show user
USER is "SYS"
SQL> alter public synonym emp compile;

Synonym altered

 

 

所以在Oracle 10g下要编译其它schema下的同义词，必须是sys用户。Ora-01031 When Compiling A Synonym (Doc ID 1299001.1)的介绍如下：

 

 

CAUSE

This is the expected behaviour. The command "alter synonym <name> compile" is not documented and as such it is not supported. Although it works in some circumstances it is not meant to be used by the users directly.

SOLUTION

This is the expected behaviour. If all the synonyms have to compiled then run the recompilation script as SYS.

 

文档Ora-01031 When Executing Alter Synonym Compile For Other Schema (Doc ID 1435898.1)中也有相关简单阐述

 

  在Oracle 11.2.0.4.0中测试也是如此。但是后续官方文档中将这个现象视为一个Bug，官方文档4189542 - Only SYS can "alter synonym... compile" for another users's synonym (Doc ID 4189542.8)中有简单介绍，并且在后面的一些版本中修复了这个Bug，Doc ID 4189542.8的具体信息如下如下：

 

Only SYS can "alter synonym... compile" for another users's synonym.

Other users get ORA-1031 attempting to compile another users synonym.

With this fix a user with both CREATE ANY SYNONYM and DROP ANY SYNONYM

privilege is allowed to issue ALTER SYNONYM .. COMPILE for another users synonym.

 

 

 

 相同的现象在不同文档中有不同阐述，确实有点让人迷惑，如果获取的信息不全或比较片面的话，可能对这个问题和现象有不同的理解。

 

 

参考资料：

 

Ora-01031 When Compiling A Synonym (Doc ID 1299001.1)

Ora-01031 When Executing Alter Synonym Compile For Other Schema (Doc ID 1435898.1)

Bug 4189542 - Only SYS can "alter synonym... compile" for another users's synonym (Doc ID 4189542.8)