Creating APPS READ ONLY schema in Oracle Applications

step 1: Connect as sysdba and create the database user to be used for apps read only schema.

bash $ sqlplus "/ as sysdba"

SQL > create user appsro identified by appsro default tablespace APPS_TS_TX_DATA;

SQL> grant connect, resource to appsro;

SQL> grant create synonym to appsro;

SQL> exit;

step 2: Connect as APPS user and run the SQL commands:

bash $ sqlplus apps/******

SQL>set head off

SQL> set newpage none

SQL> set pagesize 9999

SQL> spool create_synonyms.sql

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

SQL> spool off

SQL> spool grant_select.sql

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

SQL> spool off

SQL> exit;

step 3:
- connect as sysdba :

bash $ sqlplus "/as sysdba"

SQL> @grant_select.sql

SQL> exit;

step 4:
- connect as appsro

bash $ sqlplus appsro/appsro

SQL> @create_synonyms.sql

SQL> exit;

You are done. Now your users can use “appsro” schema to have the read only access to Apps Data.