Create read only user access for non FCUBS

Create read only user access for non FCUBS

In Production database if application user want trouble shoot the issue in back end.
As per policy you cannot give application schema credentials,So you can create user for grant select privileges as below.

1. create role READONLY_ROLE not identified;

2. begin
for t1 in ( select owner,table_name from dba_tables where owner='<FCUBS_SCHEMA>') loop
execute immediate ' grant select on ' || t1.owner || '.' || t1.table_name || ' to READONLY_ROLE';
end loop;
end;


3. set head off
spool grand_view.sql
select 'grant select on ' || owner || '.' || view_name || ' to READONLY_ROLE;' from dba_views where owner='<FCUBS_SCHEMA>';
spool off
@grand_view.sql

4.
 begin
for t1 in ( select owner,object_name from dba_objects where owner='<FCUBS_SCHEMA>' and object_type in ('PROCEDURE','FUNCTION','PACKAGE','BODY')) loop
execute immediate ' grant debug on ' || t1.owner || '.' || t1.object_name || ' to READONLY_ROLE';
end loop;
end;


5.Trigger creation script. ( which will give the privs when objects were just created.)Loging to schema and run

create or replace trigger <FCUBS_SCHEMA>.TRIGGER_<FCUBS_SCHEMA>_USER after CREATE on schema
declare
l_stat varchar2(255);
l_job number;
begin
if ( ora_dict_obj_type = 'TABLE' )
then
l_stat := 'execute immediate "grant select on ' || ora_dict_obj_name || ' to READONLY_ROLE";';
dbms_job.submit( l_job, replace(l_stat,'"','''') );
end if;
if ( ora_dict_obj_type = 'VIEW' )
then
l_stat := 'execute immediate "grant select on ' || ora_dict_obj_name || ' to READONLY_ROLE";';
dbms_job.submit( l_job, replace(l_stat,'"','''') );
end if;

if ( ora_dict_obj_type in ('PROCEDURE','FUNCTION','PACKAGE','BODY'))
then
l_stat := 'execute immediate "grant debug on ' || ora_dict_obj_name || ' to READONLY_ROLE";';
dbms_job.submit( l_job, replace(l_stat,'"','''') );
end if;
end;

Now you can grant this read only role of FCUBS(application) objects to other users.
grant READONLY_ROLE to <USER Name>; -- Assuming the user already exist if not create the user and grant the role