Integrated Document Solutions
for Business Applications

Sort Posts

Get Fresh Updates

Home / Blog / Current Page

My lucky find for ORA-01720: Grant Option Does Not Exist.

Posted on by Brent Lowe

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

To 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!

 


Share This



10 Responses to My lucky find for ORA-01720: Grant Option Does Not Exist.

  1. Pingback: Oracle Open World 2014 Edition | STR Software

  2. H.Schuerger says:

    I had a support case open with our ERP vendor because one of their patches threw this error. I could not convince them that, per the , the fact that their script ever worked didn’t make their script correct.

    The security domain does not contain roles when a CREATE VIEW statement is used. For example, a user who is granted the SELECT ANY TABLE system privilege or the SELECT object privilege for a table through a role cannot use either of these privileges to create a view on a table that belongs to another user. This is because views are definer’s rights objects, so when creating them you cannot use any privileges (neither system privileges or object privileges) granted to you through a role. If the privilege is granted directly to you, then you can use the privilege.

    If both of the following conditions are true, then the grantee can create views on the table, and grant the corresponding privileges on the views to any user or role in the database:
    * The grantee receives object privileges for the table with the GRANT OPTION.
    * The grantee has the CREATE VIEW or CREATE ANY VIEW system privilege.

    So, I decided to check for the least expected, but not unheard of reason… we upgraded Oracle. This isn’t the first time Oracle has tightened enforcement of design (signature mismatch was a biggie years ago).

    WORKAROUND
    A. GRANT SELECT ON referenced-object WITH GRANT OPTION TO view-owner;
    B. REVOKE existing grants then CREATE OR REPLACE VIEW
    C. DROP VIEW (drops existing grants) then CREATE VIEW

    Option A would be the preferred method as revoking existing grants would break most likely break other security configurations.

  3. Vedran says:

    Tx, now you saved us the day :-)… In our case we extended the view with an additional select on a new introduced table. We did “CREATE OR REPLACE VIEW… ” but it did not work. Then we added the missing grant but still the same error when trying do “CREATE OR REPLACE…” the view.
    The steps that worked: 1) Drop the view 2) Add the grant to new table 3) Create the view…

  4. Neo says:

    thanks.. it was very useful.

  5. Prosenjt Sinha says:

    Tx. for sharing it worked.

  6. Parashu says:

    It works for my after dropping view and creating.
    Thanks much.

  7. PARTHA DAS says:

    Thanks. What a find. Saved my day

  8. Josefina says:

    this also worked for me, thanks so much.

Leave a Reply to H.Schuerger Cancel reply

Your email address will not be published. Required fields are marked *