- Business Needs
- Application Integrations
- Delivery Technology
- About STR Software
About STR Software
Strangest thing happened the other day during a customer upgrade. Our automated scripts were in the process of creating a view and I received the error:
ORA-01720: Grant Option Does Not Exist.
OK….. I get it… BUT…. I didn’t even execute a GRANT statement!? I was just executing a CREATE OR REPLACE VIEW…. what gives?
Doing some searching online, I didn’t find too much that fit my issue. Everyone was getting this error with a GRANT statement. So, I turned to My Oracle Support and I found my new favorite note (only because it saved the day). Because I could not find my solution outside of My Oracle Support, I thought I’d post it here to help others.
Note: 1628033.1 – Post Upgrade to 11.2.04, “create or replace view” execution fails with “ORA-01720: Grant Option Does Not Exist”
According to Oracle:
"The code was changed in 18.104.22.168 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 22.214.171.124). In 126.96.36.199, 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 188.8.131.52 (and earlier) was incorrect; the new behavior in 184.108.40.206 is intentional and correct."
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.
In my case, the customer had just upgrade to 11.2.04 and bingo, immediately ran into this issue. Hope this can help someone else!