Skip to content
Home » Oracle » How to Resolve ORA-01720: grant option does not exist

How to Resolve ORA-01720: grant option does not exist

ORA-01720

Got error ORA-01720 when we tried to grant an object privilege to another user. ORA-01720 means that the grantor doesn't have the right privilege to grant a view to the third user.

Let's see the following example.

SQL> conn sh/sh
Connected.
SQL> set head off;
SQL> grant select on happy_employees to oe;
grant select on happy_employees to oe
                *
ERROR at line 1:
ORA-01720: grant option does not exist for 'HR.EMPLOYEES'

The error message of ORA-01720 reveals two things:

  1. The object is a view.
  2. The base table of the view is owned by the third user.

In this case, there're 3 users involved and they play different roles.

  • SH: The GRANTOR.
  • OE: The GRANTEE.
  • HR: The OWNER of the base table.

In fact, SELECT privilege of the base table has already been granted to the grantor (SH) and the grantee (OE) by the owner (HR) before the statement was issued. We can check said privilege like the following:

SQL> conn hr/hr
Connected.
SQL> column grantee format a10;
SQL> select grantee, grantable from user_tab_privs where table_name = 'EMPLOYEES' and privilege = 'SELECT';

GRANTEE    GRA
---------- ---
OE         NO
SH         NO

Please note that, the GRANTABLE option is off at this moment.

Solutions

In such situation, we have to know that the database doesn't care whether the grantee has already had the object privilege on the table or not. It does care two things:

  • The base table is owned by the third party.
  • The grantor must have WITH GRANT OPTION privilege modifier.

WITH GRANT OPTION

To solve ORA-01720, you have to grant the object privilege WITH GRANT OPTION to SH. Specifying WITH GRANT OPTION enables SH to grant the object privileges to other users and roles.

SQL> grant select on employees to sh with grant option;

Grant succeeded.

SQL> select grantee, grantable from user_tab_privs where table_name = 'EMPLOYEES' and privilege = 'SELECT';

GRANTEE    GRA
---------- ---
OE         NO
SH         YES

Now the grantor is allowed to grant the view to grantee.

SQL> conn sh/sh
Connected.
SQL> grant select on happy_employees to oe;

Grant succeeded.

Please note that, granting GRANT ANY OBJECT PRIVILEGE system privilege to the grantor cannot solve ORA-01720. This error specifically complains of missing WITH GRANT OPTION.

WITH ADMIN OPTION

If the re-granting issue is becoming global, you may consider to grant a more powerful system privilege to the re-grant user, SELECT ANY TABLE WITH ADMIN OPTION.

SQL> grant select any table to sh with admin option;

Grant succeeded.

30 thoughts on “How to Resolve ORA-01720: grant option does not exist”

  1. This is one of the best explanations for ANY Oracle problem that I have ever seen. So clear, logical, and easy to follow. Thank you!

  2. Hi Ed,

    I’ll echo the previous posters’ thanks – the explanation is indeed clear and helpful.

    But should this comment:
    “In fact, the base table has already been granted to the grantee before the statement was issued” read ” ….. to the GRANTOR ….”?

  3. Hi,

    What if the owner of the base object is on a remote server connected via db-link? What if functions are are also involved? What if we need a role to handle all of these privileges?

    1. The privilege problem of remote objects should be solved at remote database. The grantee would be the remote user used by the database link.

  4. I was getting the same error granting SELECT ON to object-relational tables. It is the same three-way ownership issue and can be resolved with:

    GRANT EXECUTE ANY TYPE TO SH WITH ADMIN OPTION;

    Thank you for the clear explanation.

  5. We found that the issue was caused by something completely different. It wasn’t related to the table mentioned in the ORA-01720 statement. The actual problem was that the Oracle system package body, MDSYS.SDO_UTIL, which was referenced on the line mentioned in the error message, was invalid. Once we compiled the package body the error went away.

Leave a Reply

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