How to create Apps Read Only Mode in Oracle R12

Method :2

1. create role READ_ONLY not identified;

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

3. begin
for t1 in ( select owner,view_name from dba_views where owner='READ_ONLY') loop
execute immediate ' grant select on ' || t1.owner || '.' || t1.view_name || ' to READ_ONLY';
end loop;
end;

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

5.Trigger creation script. ( which will give the privs when objects were just created.)

create or replace trigger APPSREAD.trigger_READ_ONLY_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 READ_ONLY";';
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 READ_ONLY";';
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 execute on ' || ora_dict_obj_name || ' to READ_ONLY";';
dbms_job.submit( l_job, replace(l_stat,'"','''') );
end if;
end;


6. Grant the READ_ONLY Role to the eligible users


7. Grant READ_ONLY to APPSREAD;