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:
- The object is a view.
- 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.
this worked like a charm, thanks
My pleasure!
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!
Thank you, I appreciate the compliment.
I completely agree the clearest explanation i have come across and it worked perfectly.Thank you
I’m glad it’s helpful.
Great Post. Loved it to read and solved my issue. Thanks for this post.
I am glad it’s useful.
Great explanation, I agree !!!
Thanks!
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 ….”?
Thanks for you feedback, I really meant GRANTEE, but it can be improved and clearer by rephrasing it.
Thank You!
My pleasure!
The article helped to fix the exact issue we faced. Thanks a lot.
My pleasure!
I knew the GRANT OPTION, but never knew these implications till facing ORA-01720 and reading your excellent explanation.
I’m glad it works!
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?
The privilege problem of remote objects should be solved at remote database. The grantee would be the remote user used by the database link.
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.
Good for you!
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.
Thanks for sharing.
Thanks!
You’re welcome!
Thank you!! the article resolved our problem. the explanation was very clear and up to the point.
I’m glad it helps.
Great post, Ed. Very helpful.
Thanks!
Awesome. it worked like Charm 🙂 thanks!!
My pleasure!