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

  • by

ORA-01720

Got error ORA-01720 when we tried to grant an object privilege to another user. ORA-01720 means that the grantee doesn’t have privileges on the base table.

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

Solutions

That is to say, 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.

Leave a Reply

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