grant select rights to views and tables for role


Here is a simple script to grant select on all tables and views under the selected schema’s ‘APPOWNER1’ , ‘APPOWNER2’ and grant them to ‘APPS_READ_ROLE’


set serveroutput on size 1000000

declare
sql_statement varchar2(200);
cursor_id number;
ret_val number;

begin

for objects in (select table_name, owner from dba_tables where owner in (‘APPOWNER1′,’APPOWNER2’) and table_name not in ( select table_name from dba_tables where owner in (‘APPOWNER1′,’APPOWNER2’) and iot_type = ‘IOT_OVERFLOW’ )) loop

sql_statement := ‘grant select on ‘||objects.owner||’.’||objects.table_name||’ to APPS_READ_ROLE’;
execute immediate sql_statement;

end loop;

end;
/
exit;