APPS READ ONLY schema

Creating APPS READ ONLY schema in Oracle Applications

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 schema. 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.

Cheers !!!