Grant Access to all objects on a schema

Grant all privs on a schema

REM connect ord/ord;
set verify off
set echo off
set sqlnumber off
set heading off
set feedback off
set termout off
set linesize 300
spool ‘/tmp/grant_privs_for_ord_Role.sql’

select ‘grant select on ‘ ||owner || ‘.’ || table_name || ‘ to ORD_RO_ROLE;’ from all_tables where owner like ‘Schema_name’;

select ‘grant select on ‘ ||owner || ‘.’|| view_name || ‘ to ORD_RO_ROLE;’ from all_views  where owner like ‘Schema_name’;

select ‘grant select,insert,update,delete on ‘ ||owner || ‘.’|| table_name || ‘ to ORD_RW_ROLE;’ from all_tables where owner like ‘Schema_name’;

select ‘grant select,insert,update,delete on ‘ ||owner || ‘.’|| View_name || ‘ to ORD_RW_ROLE;’ from all_views  where owner like ‘Schema_name’;

select ‘grant EXECUTE on ‘ ||owner || ‘.’|| object_name || ‘ to ORD_RO_ROLE, ORD_RW_ROLE;’ from all_objects where owner = ‘Schema_name’ and object_type in (‘PROCEDURE’,’FUNCTION’,’PACKAGE’ );

select ‘grant ORD_RW_ROLE to ETL_ORI_TGT;’ from dual;
select ‘grant ORD_RW_ROLE to ETL_REI_TGT;’ from dual;
select ‘grant ORD_RW_ROLE to ETL_BPM_TGT;’ from dual;
spool off

Leave a Reply

Your email address will not be published. Required fields are marked *