Oracle Apps : APPLSYS & APPS schema roles and create READ ONLY APPS schema

Role of APPLSYS & APPS schema in Oracle Applications:

Applsys schema contains all the tables required for administarative purpose and is same as other oracle users like AP, AR, GL etc which hold their set of tables, views etc.
In the same way APPLSYS Account holds its set of tables like FND_USER and FND_APPLICATION, AD_APPLIED_PATCHES etc.
Applsys schema has applications technology layer products/objects like FND and AD,WF related data like tables and Indexes…etc.

Apps is a universal schema, it has synonyms to all base product tables and sequences. This also has code objects for all products (triggers, views, packages, synonyms etc).
APPS is central Schema which holds synonyms for all other Users Database Objects.

APPLSYS and APPS should have same password.

To change password of APPS with FNDCPASS:

$FNDCPASS APPS/[apps_pass] 0 Y SYSTEM/[system_pass] SYSTEM APPLSYS [new_passs]

the APPS password will be updated when the APPLSYS password is changed.

Apps schema contains only Synonyms we can’t create tables in apps schema,where as other schemas contains tables, & all the objects.

Sometimes, developer/functional team might ask you access for APPS schema. In Production environments, its not at all recommended to give APPS access to developers.
In this case, we can create read_only Apps schema and give to developer team.

Here we are going to create a schema similar to the APPS schema but has only read-only priviliges.

step.1: Create the read-only schema, in this case lets call it APPSREAD.

SQL> CREATE USER APPSREAD IDENTIFIED BY xxxxxx default tablespace APPS_TS_TX_DATA;;

SQL> GRANT CONNECT, RESOURCE,CREATE SYNONYM TO APPSREAD;

step.2 : Generate a Script for creating database objects of Application User ‘APPS’

set echo off set
pagesize 0
set linesize 300
spool create_synonyms.sql

select ‘create synonym ‘ || OBJECT_NAME || ‘ for ‘|| OWNER ||’.’ ||OBJECT_NAME || ‘;’ from all_objects where OWNER in (‘APPS’) and
OBJECT_NAME not like ‘%/%’ and OBJECT_TYPE in (‘TABLE’,’VIEW’,’SYNONYM’);

spool off

step.3: Generate a script for selecting database objects of Application User ‘APPS’

spool Grant_select.sql
select ‘grant select on ‘|| OWNER ||’.’ ||OBJECT_NAME || ‘ to APPSREAD;’ from all_objects where OWNER not in (‘SYS’,’SYSTEM’) and OBJECT_NAME not like ‘%/%’ and OBJECT_TYPE in (‘TABLE’,’VIEW’,’SYNONYM’);
spool off

step.4 : Connect to sqlplus as sysdaba and execute the following script

SQL> @GrantSelect.sql
SQL> @cr8synonyms.sql

Step.5 : Connect to Read only schema User and check the objects.

SQL> conn APPSREAD/xxxxx
SQL> select count(*) from tab;

SQL>exit;