Creating READ ONLY schema in Oracle Applications

I will show you how to create a custom schema which will be clone of apps schema but will have read only access to all the objects in apps schema.

Sometimes, some of your developers might ask you access for “apps” schema. In Production environments, its not at all recommended to give “apps” access to developers as they may screw up the code. However if they can be granted read only access if management approves this.

To do so, we need to create an additional schema which will be having only read only access to all the objects owned or accessed by “apps”. Please find below the detailed procedure of creating such a “read only apps schema”.

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.