创建视图时出现Oracle错误-ORA-01720 [英] Oracle Error When Creating View - ORA-01720

问题描述

我在Oracle 11.2.0.4.0环境中遇到了困扰.我有一个可以工作的，已编译的视图，可以从中选择数据，但是如果没有出现"ORA-01720"错误，就无法更改它.

I am having an issue in my Oracle 11.2.0.4.0 environment that has me stumped. I have a WORKING, COMPILED view in existence, which I can select data from, but I cannot alter it without getting an "ORA-01720" error.

关于我的设置的一些信息: 我有2个模式，简单来说就是"A"和"B".模式"A"拥有"TABLE1"，"TABLE2"，"TABLE3"，模式"B"当前具有从所有3个表中选择数据所需的授权.我已经确认可以通过查询以及存在的视图选择数据.

A bit about my setup: I have 2 schemas, "A" and "B" to be simple. Schema "A" owns "TABLE1", "TABLE2", "TABLE3" and schema "B" currently has the grants required to select data from all 3 tables. I have verified that I can select the data through a query, as well as the view that exists.

有人知道我的模式"B"已授予对这三个表中的模式"A"数据的访问权时，有什么可能阻止我更改工作视图吗?我已经从Google以及一些网站上看到了一些建议，这些建议都提到了授予选择带有授予选项"的选项，但我尝试过，但是当我直接从所有者而不是从受赠人中选择数据时，我看不到它会如何应用.错误消息如下:

Does anyone know what might be preventing me from altering a working view when my schema "B" has granted access to schema "A" data within those three tables? I saw several suggestions from google and various sites mentioning granting select "with grant option", which I have tried, but I do not see how it would apply when I am selecting data directly from the owner, not from a grantee. The error message is below:

SQL Error: ORA-01720: grant option does not exist for 'A.TABLE1'
01720. 00000 -  "grant option does not exist for '%s.%s'"
*Cause:    A grant was being performed on a view or a view was being replaced
           and the grant option was not present for an underlying object.
*Action:   Obtain the grant option on all underlying objects of the view or
           revoke existing grants on the view.

推荐答案

这是Oracle 11中引入的限制.即使使用create or replace force view.您必须先删除视图并重新创建它.

It is a limitation introduced in Oracle 11. You cannot add a table you do not own, but have been granted rights to, to an existing view even with create or replace force view. You have to drop the view first and the recreate it.

这篇关于创建视图时出现Oracle错误-ORA-01720的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！