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, the base table has already been granted to the grantee before the statement was issued.

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 has WITH GRANT OPTION privilege modifier.

WITH GRANT OPTION

To solve ORA-01720, you have to grant the object privilege WITH GRANT OPTION to the grantor. Specifying WITH GRANT OPTION enables the grantee 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.

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

Leave a Reply

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