Create a read-only user in your Oracle EBS database

To create a read-only user in Oracle EBS database, use the following commands:

sqlplus / as sysdba
create user appsro identified by <password>;
grant connect, resource to appsro;
grant create synonym to appsro;
commit;
sqlplus apps/<password>
set head off
set newpage none
set pagesize 9999
spool create_synonyms.sql
select 'create synonym ' || OBJECT_NAME || ' for ' || OWNER ||'.' ||OBJECT_NAME || ';' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');

spool off
spool grant_select.sql
select 'grant select on '|| OWNER ||'.' ||OBJECT_NAME || ' to appsro;' from all_objects where OWNER not in ('SYS','SYSTEM') and OBJECT_NAME not like '%/%' and OBJECT_TYPE in ('TABLE','VIEW','SYNONYM');
spool off
exit

sqlplus / as sysdba
@grant_select.sql
exit;
sqlplus SYSTEM/<password>
alter session set current_schema=APPS;
exec AD_ZD_PREP.ENABLE_CUSTOM_USER('APPSRO');

sqlplus appsro/<password>
@create_synonyms.sql
exit;