ORA-01720: grant option does not exist for a table

We need users of specific role, need select privileges on tables and views owned by schema owners.


However while We try to grant select privileges on some views, I come accross a somewhat particular error as:

paul @ agentdb-live > grant select on AGENTBIP.VIEW_CUST_AGENT_DIST_INFO to ROLE_ITDD_OT;
grant select on AGENTBIP.VIEW_CUST_AGENT_DIST_INFO to ROLE_ITDD_OT
                         *
ERROR at line 1:
ORA-01720: grant option does not exist for 'MMUSER_GW.DBBL_AGENT_DATA'

The reason for it is that the view VIEW_CUST_AGENT_DIST_INFO owned by AGENTBIP is built on top of the table DBBL_AGENT_DATA owned by someone else ie MMUSER_GW.

AGENTBIP 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

paul @ agentdb-live > Grant select on MMUSER_GW.DBBL_CONSUMER_ACCOUNT to AGENTBIP with grant option;

Grant succeeded.

SYSTEM @ agentdb-live > grant select on AGENTBIP.VIEW_CUST_AGENT_DIST_INFO to ROLE_ITDD_OT;

Grant succeeded.