创建视图时出现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屋!