[[409302]]
本文转载自微信公众号「DBA闲思杂想录」,译同义词m遇作者潇湘隐者。重编转载本文请联系DBA闲思杂想录公众号。译同义词m遇
我们在编译INVALID的重编同义词(synonym)的时候,可能在某些版本会遇到一些特殊权限问题,译同义词m遇具体来说是重编遇到ORA-01031错误。下面构造这样一个例子:
如下所示,译同义词m遇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用户下去编译同义词时,译同义词m遇就会遇到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下的译同义词m遇同义词的话,也会遇到这个错误。重编按照我的理解,如果用户拥有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)
责任编辑:武晓燕 来源: DBA闲思杂想录 OracleSynonymORA
(责任编辑:休闲)
深圳国际(00152.HK)遭UBS Group AG减持291.65万股 涉资约3681.5万港元
前10个月安徽省重点项目完成投资15725亿 开工3235个
斗鱼TV、花椒、映客、梦想、YY直播 谁是2016最具代表直播平台?
2021年前三季度国内旅游总人次26.89亿 旅游收入2.37万亿元