ORACLE重编译同义词(synonym)遇到ORA-01031总结
2021-07-05 11:04 潇湘隐者 阅读(2080) 评论(0) 编辑 收藏 举报我们在编译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.
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)

【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
2018-07-05 ALTER SYSTEM ARCHIVELOG CURRENT挂起案例
2017-07-05 You (root) are not allowed to access to (crontab) because of pam configuration
2016-07-05 backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.