Generate Oracle ReGrants "WITH GRANT OPTION"

Oracle user "tableowner" owns tables.
User tableowner grants SELECT to user "reportowner".
Report owner creates view "V" and grants SELECT on V to role "reportrole".
Role "reportrole" is granted to user "enduser".

The reportowner is able to create and select from the views. Enduser receives permission errors when selecting from reportowner's views. To resolve this issue, grant select WITH GRANT OPTION to the selectrole.

In this case the SELECT grants were already in place, and they needed be upgraded to include "WITH GRANT OPTION". This SQL will generate a SQL script that can be run to apply the "WITH GRANT OPTION".

$ cat gen_grantSelect.sql
set lines 90
set pages 0
set feedback off

spool grantSelect.sql
prompt spool grantSelect

select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || 'REPORTOWNER with grant option;'
from dba_tab_privs
where grantor='TABLEOWNER' and grantee = 'REPORTOWNER'
order by 1
/
spool off