Oracle重編譯同義詞(Synonym)遇到ORA-01031總結(jié)
本文轉(zhuǎn)載自微信公眾號(hào)「DBA閑思雜想錄」，作者瀟湘隱者。轉(zhuǎn)載本文請(qǐng)聯(lián)系DBA閑思雜想錄公眾號(hào)。
我們?cè)诰幾gINVALID的同義詞(synonym)的時(shí)候，可能在某些版本會(huì)遇到一些特殊權(quán)限問題，具體來說是遇到ORA-01031錯(cuò)誤。下面構(gòu)造這樣一個(gè)例子：
如下所示，scott用戶下面創(chuàng)建了一個(gè)公共同義詞emp(下面測(cè)試環(huán)境為Oracle 10.2.0.5)
- SQL> show user;
- USER is "SCOTT"
- SQL> create public synonym emp for scott.emp;
- Synonym created.
- SQL>
假設(shè)我們需要重新編譯同義詞，在system用戶下去編譯同義詞時(shí)，就會(huì)遇到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
另外一種情況也會(huì)遇到這個(gè)錯(cuò)誤，UserA 去編譯UserB下的同義詞的話，也會(huì)遇到這個(gè)錯(cuò)誤。按照我的理解，如果用戶擁有CREATE ANY SYNONYM和DROP ANY SYNONYM權(quán)限的話，應(yīng)該是可以編譯其它SCHEMA下的同義詞。而且SYSTEM用戶被授予了DBA角色，DBA角色擁有CREATE ANY SYNONYM 或DROP ANY SYNONYM的權(quán)限。
- 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提示：同義詞的編譯時(shí)，要么是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)中也有相關(guān)簡(jiǎn)單闡述
在Oracle 11.2.0.4.0中測(cè)試也是如此。但是后續(xù)官方文檔中將這個(gè)現(xiàn)象視為一個(gè)Bug，官方文檔4189542 - Only SYS can "alter synonym... compile" for another users's synonym (Doc ID 4189542.8)中有簡(jiǎn)單介紹，并且在后面的一些版本中修復(fù)了這個(gè)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.
相同的現(xiàn)象在不同文檔中有不同闡述，確實(shí)有點(diǎn)讓人迷惑，如果獲取的信息不全或比較片面的話，可能會(huì)對(duì)這個(gè)問題和現(xiàn)象有不同的理解。
參考資料：
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)
