Create an APPS READONLY user
There are 2 ways to achieve this:
1) Create a user and grant select option to that user.
This will have to be done individually for all users that you want to have as read only.
2) Create a readonly role and assign users this role.
This will have to be done once and then assign this role to the users.
1. Create a user and grant select option to that user.
sqlplus / as sysdba
SQL> create user apps_readonly identified by apps_readonly;
User created.
SQL> grant connect to apps_readonly;
Grant succeeded.
SQL> grant select any table to apps_readonly;
Grant succeeded.
Connect with the apps_readonly user
SQL> conn apps_readonly
Enter password:
Connected.
SQL> show user
USER is "APPS_READONLY"
Try to access an apps schema table
SQL> select count(*) from apps.po_headers_all;
COUNT(*)
----------
132206
Try to update an apps schema table
SQL> update apps.po_headers_all set attribute1 = '1' where po_header_id = 126;
update apps.po_headers_all set attribute1 = '1' where po_header_id = 126
*
ERROR at line 1:
ORA-01031: insufficient privileges
2. Create a read only role and assign users this role.
SQL> create user apps_readonly identified by apps_readonly;
User created.
SQL> grant connect to apps_readonly;
Grant succeeded.
SQL> create role role_readonly;
Role created.
SQL> grant select any table to role_readonly;
Grant succeeded.
SQL> grant role_readonly to apps_readonly;
Grant succeeded.
SQL> conn apps_readonly
Enter password:
Connected.
SQL> show user
USER is "APPS_READONLY"
SQL> select count(*) from apps.po_headers_all;
COUNT(*)
----------
132206
SQL> update apps.po_headers_all set attribute1 = '1' where po_header_id = 126;
update apps.po_headers_all set attribute1 = '1' where po_header_id = 126
*
ERROR at line 1:
ORA-01031: insufficient privileges
Grant another user the 'read only' role
SQL> create user apps_readonly1 identified by apps_readonly1;
User created.
SQL> grant connect to apps_readonly1;
Grant succeeded.
SQL> grant role_readonly to apps_readonly1;
Grant succeeded.
SQL> conn apps_readonly1
Enter password:
Connected.
SQL> show user
USER is "APPS_READONLY1"
SQL> select count(*) from apps.fnd_concurrent_requests;
COUNT(*)
----------
75724
SQL> select count(*) from apps.po_headers_all;
COUNT(*)
----------
132206
SQL> update apps.po_headers_all set attribute1 = '1' where po_header_id = 126;
update apps.po_headers_all set attribute1 = '1' where po_header_id = 126
*
ERROR at line 1:
ORA-01031: insufficient privileges