Create Apps Read Only User

Agenda:

Create apps read only user for Oracle Database.

Steps to create the Apps Read Only User:

Step 1:

SQL> create user appsro identified by appsro;

User created.

SQL> alter user appsro default tablespace APPS_TS_TX_DATA temporary tablespace temp;

User altered.

SQL> grant connect, resource to appsro;

Grant succeeded.

SQL> grant create synonym to appsro;

Grant succeeded.

Step 2:

Connect as apps user  and create a spool file . as below

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 username;' 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:

sqlplus "/as sysdba"

SQL> @grant_select.sql

SQL> exit;

bash $ sqlplus usrername/Password 

SQL> @create_synonyms.sql

SQL> exit;