Oracle Error When Creating View - ORA-01720

Question

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.

Clam

Are you saying you still get the error after you grant select on a.table1 to b with grant option, and do the same for table2 and table3? Or that those grants have fixed the issue but you want to understand why it was necessary? – Peritonitis

When you say you're trying to alter a working view, which schema are you trying to alter it from? A or B? And what alterations are you trying to make? – Upshaw

Also, does this blog post explain what might be happening? – Upshaw

Hello, thanks for the replies. The blog post from Boneist did the trick, but I am not sure I understand the root cause still. Isn't the point of "create or replace" so that you don't have to drop and create a view? – Clam

Could you tell me why you don't create view on Schema "A" and grant privilege on the view to Schema "B"? If you don't have any reason, this way might be solution. – Hopkins

I am not sure why it was created under schema "B", but I know schema "B" contains data for a particular webapp and the view is related to that. – Clam

Answer 1

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.

Erebus

After I did drop and recreate the view once, and then added back the grants on the view, I was actually able to further use create or replace force view to modify it. Seems quite strange, as if the "old" grants were not the same as the "new" ones, and somehow got in the way of replacing the view. – Aberration

Answer 2

One thing you can try is adding with grant option to the end of the grant statements of the tables within the views:

grant select on A.TABLE1 to B with grant option;

Subsistence