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.
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.
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. 1 Answer
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.
