How to create APPS readonly user in Oracle Applications

Step 1: Create Apps Read only user.

Connect as SYS user to create APPSRO user

create user appsro identified by appsro default tablespace APPS_TS_TX_DATA;

grant connect, resource to appsro;

grant create synonym to appsro;

exit; 

Step 2: Connect to apps user now to get the synonyms and grant details. 

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’,’PACKAGE’,’PACKAGE BODY’,’PROCEDURE’,’FUNCTION’);

 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> spool grant_execute.sql

 SQL> select ‘grant execute on ‘|| OWNER ||’.’ ||OBJECT_NAME || ‘ to appsro;’ from all_objects where OWNER not in (‘SYS’,’SYSTEM’) and OBJECT_NAME not like ‘%/%’ and OBJECT_TYPE in (‘PACKAGE’,’PROCEDURE’,’FUNCTION’);

 SQL> spool off

SQL> exit;

 Step 3: Run the spool files under sysdba user.

 - connect as sysdba :

bash $ sqlplus “/as sysdba”

SQL> @grant_select.sql  — To grant select privileges on TABLES,VIEWS and SYNONYMS.

SQL>@grant_execute.sql — To grant execute privileges on PACKAGES, FUNCTIONS and PROCEDURES.

SQL> exit;

 Step 4: Now connect as read only user and create synonymous. 

 - connect as appsro

bash $ sqlplus appsro/appsro

SQL> @create_synonyms.sql

SQL> exit;