我們在編譯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 YESDBA CREATE ANY SYNONYM YES
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='SYSTEM';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---SYSTEM AQ_ADMINISTRATOR_ROLE YES YESSYSTEM 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)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/288942.html
標籤：其他