Create Read only user in Oracle Apps

Sometime you will get request from the developer to create READ ONLY schema in oracle apps.

conect as sysdba

create user appsrod identified by appsrod#123;
grant connect, resource to appsrod;
grant create synonym to appsrod;

SQL> create user appsrod identified by appsrod#xyz;

User created.

SQL> grant connect, resource to appsrod;

Grant succeeded.

SQL> grant create synonym to appsrod;

Grant succeeded.

SQL>

bash $ sqlplus apps/******

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 appsrod;' 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

– connect as appsrod

@create_synonyms.sql

exit;