Creating APPS READ ONLY schema in Oracle Applications

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


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 appmqm identified by appmqm default tablespace APPS_TS_TX_DATA;

SQL> grant connect, resource to appsmqm;

SQL> grant create synonym to appsmqm;

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 appsmqm

bash $ sqlplus appsmqm/appsmqm

SQL> @create_synonyms.sql

SQL> exit;

Now your users can use “appsmqm” schema to have the read only access to Apps Data.