Oracle重編譯同義詞(Synonym)遇到ORA-01031總結
本文轉載自微信公眾號「DBA閑思雜想錄」，作者瀟湘隱者。轉載本文請聯系DBA閑思雜想錄公眾號。
我們在編譯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)的介紹如下：
文檔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)
