oracle 当有 Public.Dual 时，为什么会出现错误“ORA-01720：'SYS.DUAL' 的授予选项不存在”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29289236/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why error "ORA-01720: grant option does not exist for 'SYS.DUAL'" when there is a Public.Dual?
提问by jimo3
I'm on Oracle 11g, and I do understand the issue of a 3rd-party grant.
我使用的是 Oracle 11g，我确实了解第 3 方授权的问题。
But, given that have user1 creating a view "view1" as Select 'foo' from dual.
但是，鉴于让 user1 创建视图“view1”作为 Select 'foo' from dual。
Then I grant Select on view1 to user2 and I get this error.
然后我将 view1 上的 Select 授予 user2 并出现此错误。
But note the "dual" in the view is not qualified as sys.dual, it's just dual. I would think with a synonym public.dual that the actual "dual" used would be public.dual, not sys.dual, so no 3rd party issue should exist because it's public.
但请注意，视图中的“双重”不符合 sys.dual 的条件，它只是双重的。我认为同义词 public.dual 实际使用的“dual”将是 public.dual，而不是 sys.dual，所以不应该存在第 3 方问题，因为它是公开的。
And if sys.dual is the one Oracle assumes in this view, one would think that given the use of dual is common in views, and that granting privs on views to other users is also common--wouldn't thousands of users be reporting this issue?
如果 sys.dual 是 Oracle 在此视图中假定的那个，那么人们会认为鉴于在视图中使用 dual 很常见，并且将视图的 priv 授予其他用户也很常见——不会有成千上万的用户报告这个问题？
I do see sporadic posts about this but no real solution except to create another copy of dual for the user creating the view, but this doesn't make sense to me. Thanks for any help.
我确实看到了关于此的零星帖子，但除了为创建视图的用户创建另一个双副本之外，没有真正的解决方案，但这对我来说没有意义。谢谢你的帮助。
回答by jimo3
After consulting our dbas, the issue is an Oracle "Feature" in 11.2.0.4:
在咨询了我们的 dbas 后，问题是 11.2.0.4 中的 Oracle“特性”：
TL;DR verison: As of v 11.0.4, if your View uses Dual, then you can't grant that View anything but SELECT.
TL;DR 版本：从 v 11.0.4 开始，如果您的 View 使用 Dual，那么除了 SELECT 之外您不能授予该 View 任何东西。
Why would we want to grant a view more than Select? In our case the app vendor packaged their updates in such a way that the database portion of the updates automatically scripted full CRUD grants to the master app-schema on every new object, and this included views, because it was simply easier to script that way. This all worked fine until 11.0.4, when Oracle said/enforced "Hey, you can't do that".
为什么我们想要授予一个视图而不是 Select？在我们的案例中，应用程序供应商以这样一种方式打包他们的更新：更新的数据库部分自动脚本化完整的 CRUD 授予每个新对象的主应用程序架构，这包括视图，因为以这种方式编写脚本更容易. 这一切都很好，直到 11.0.4，当 Oracle 说/强制执行“嘿，你不能那样做”时。
Full version:
完整版本：
(Quoted from Oracle site https://support.oracle.com/epmos/faces/BugDisplay?parent=DOCUMENT&sourceId=1628033.1&id=17994036)
（引自 Oracle 网站https://support.oracle.com/epmos/faces/BugDisplay?parent=DOCUMENT&sourceId=1628033.1&id=17994036）
Oracle Database - Enterprise Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2] Information in this document applies to any platform. SYMPTOMS After upgrading from 11.2.0.3 to 11.2.0.4, you encounter the following error while executing the "create or replace view" statement: ORA-01720: grant option does not exist Views were created before the upgrade and "CREATE OR REPLACE VIEW" had worked fine. CAUSE The observed behavior is correct. You will get this ORA-1720 error when REPLACING a view that selects from some other user's tables and both of the following conditions are true:
Oracle 数据库 - 企业版 - 版本 11.2.0.4 至 11.2.0.4 [版本 11.2] 本文档中的信息适用于任何平台。症状 从 11.2.0.3 升级到 11.2.0.4 后，您在执行“创建或替换视图”语句时遇到以下错误：ORA-01720：授权选项不存在在升级和“创建或替换视图”之前创建了视图工作得很好。原因 观察到的行为是正确的。当替换从其他用户的表中选择的视图并且以下两个条件都为真时，您将收到此 ORA-1720 错误：
- you have already granted select or other privileges on the VIEW to some other user
- the view owner does not have the GRANT option on the tables being selected from (or view owner may have some privilege with grant option but not others) Development has explained it as follows:
- 您已经将 VIEW 的选择或其他权限授予其他用户
- 视图所有者在从中选择的表上没有 GRANT 选项（或者视图所有者可能具有授予选项的某些权限，但没有其他权限）开发人员对此进行了如下解释：
The code was changed in 11.2.0.4 so that create view behavior is similar to grant. If you try to make a GRANT on an existing view and the view owner does not have the grant option, then ORA-1720 is the expected result (even in 11.2.0.3). In 11.2.0.4, we have simply made CREATE VIEW consistent with the GRANT behavior, i.e. if an incompatible grant exists, then the new view definition must not be allowed (even with FORCE). In other words, we do not allow incompatible grants to coexist with the view definition and so an error must be thrown. The behavior in release 11.2.0.3 (and earlier) was incorrect; the new behavior in 11.2.0.4 is intentional and correct. SOLUTION To avoid this issue, you can do either of the following:
代码在 11.2.0.4 中进行了更改，因此创建视图行为类似于授予。如果您尝试对现有视图进行 GRANT 并且视图所有者没有授予选项，那么 ORA-1720 是预期结果（即使在 11.2.0.3 中）。在 11.2.0.4 中，我们简单地使 CREATE VIEW 与 GRANT 行为一致，即如果存在不兼容的授予，则不得允许新的视图定义（即使使用 FORCE）。换句话说，我们不允许不兼容的授权与视图定义共存，因此必须抛出错误。版本 11.2.0.3（及更早版本）中的行为不正确；11.2.0.4 中的新行为是有意且正确的。解决方案 为避免此问题，您可以执行以下任一操作：
- Remove all grants on the view before REPLACING the view. This will ensure that no incompatible grants exist.
- Drop and recreate the view. Dropping the view will automatically remove all grants.
- 在替换视图之前删除视图上的所有授权。这将确保不存在不兼容的赠款。
- 删除并重新创建视图。删除视图将自动删除所有授权。
REFERENCES BUG:17994036 - POST UPGRADE TO 11.2.0.4 CREATE OR REPLACE FAILS WITH ORA-01720 BUG:18024486 - ORA-1720 WHEN CREATING VIEW AFTER TO HAVE UPGRADE FROM 11.2.0.3.0 TO 11.2.0.4.0
参考 BUG：17994036 - 升级到 11.2.0.4 后创建或替换失败，ORA-01720 BUG：18024486 - ORA-1720 在从 11.1.0.2.1.1.00.TO 升级后创建视图时。
回答by Vase Tusevski
With sys (as sysdba ) database user grant the necessary privileges, and after that try yo recreate the view with sys (as sysdba ) database user. This was helpful for me.
使用 sys （作为 sysdba ）数据库用户授予必要的权限，然后尝试使用 sys （作为 sysdba ）数据库用户重新创建视图。这对我很有帮助。
Regards, Vase Tusevski
问候， 花瓶·图谢夫斯基
