Why error "ORA-01720: grant option does not exist for 'SYS.DUAL'" when there is a Public.Dual?

Question

I'm on Oracle 11g, and I do understand the issue of a 3rd-party grant.

But, given that have user1 creating a view "view1" as Select 'foo' from dual.

Then I grant Select on view1 to user2 and I get this error.

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.

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?

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.

Answer 1

After consulting our dbas, the issue is an Oracle "Feature" in 11.2.0.4:

TL;DR verison: As of v 11.0.4, if your View uses Dual, then you can't grant that View anything but SELECT.

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".

---

Full version:

(Quoted from Oracle site 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:

• 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:

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:

1. Remove all grants on the view before REPLACING the view. This will ensure that no incompatible grants exist.
2. 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