Creating APPS Readonly Access schema for Oracle Application Users

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 apps_readonly identified by apps_readonly default tablespace APPS_TS_MEDIA;

SQL> grant connect, resource to apps_readonly;

SQL> grant create synonym to apps_readonly;

SQL> exit;


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

bash $ sqlplus apps/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 apps_readonly;' 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:

bash $ sqlplus "/as sysdba"

SQL> @grant_select.sql

SQL> exit;

step 4:  connect as apps_readonly

bash $ sqlplus apps_readonly/apps_readonly

SQL> @create_synonyms.sql


SQL> exit;

Now users can use “apps_readonly” schema to have the read only access to Applications Data