Security

Grant with grant option as a workaround for ORA-01720

User Rating:

/ 5

Details: Parent Category: Articles; Created on Thursday, 18 March 2010 21:33; Last Updated on Monday, 12 March 2012 13:54; Published on Thursday, 18 March 2010 21:33; Written by Guy Lambregts; Hits: 44646

Grant with grant option as a workaround for ORA-01720

User 1 needs select privileges on tables and views owned by schema owners.
These are one of the daily re-occuring DBA tasks. Sometimes I get bored with it, I still like a challenge.

However while I tried to grant select privileges on some views I came accross a somewhat particular error

grant select on OWNER_VIEW.VIEW_1 to USER_1


                           *
 
 ERROR at line 1:
 
 ORA-01720: grant option does not exist for 'OWNER_TABLE.TABLE_1'

The reason for it is that the view VIEW_1 owned by OWNER_VIEW is built on top of the table TABLE_1 owned by someone else ie OWNER_TABLE.

OWNER_VIEW cannot give privileges on these kind of views to someone else ie USER_1 as long as OWNER_VIEW has not the privileges WITH GRANT OPTION for the underlying tables

The solution

DB_USER >Grant select on OWNER_TABLE.TABLE_1 to OWNER_VIEW with grant option;
 
 Grant succeeded.
 
  
 
 DB_USER >grant select on OWNER_VIEW.VIEW_1 to USER_1;
 
 Grant succeeded.