How to create APPS readonly user in Oracle Applications

Step 1:

Connect as SYS user to create Tablespace and APPS_RO user

CREATE TABLESPACE APPS_RO DATAFILE '/u02/oracle/db/apps_st/data/apps_ro1.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 2G;

create user apps_ro identified by appsro default tablespace APPS_RO;

grant connect, resource to apps_ro;

grant create synonym to apps_ro;

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

SQL> spool off


SQL> spool grant_select.sql

SQL> select ‘grant select on ‘|| owner ||’.’ ||object_name || ‘ to apps_ro;’ 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 apps_ro;’ 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:


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:


Connect as apps_ro

bash $ sqlplus apps_ro/apps_ro

SQL> @create_synonyms.sql  -- To create synonyms on apps_ro schema.

SQL> exit;