14
Jun

How to Create a Read Only APPS Schema in Oracle E-Business Suite

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

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to Top