How to Create and Support APPS Read-Only DB user

Hi All


Here You can find the scripts for creating APPS read-only DB user.


1. Create tablespace for APPS Read-only user (optional):
create tablespace APPS_RO datafile '<dbf top path>/apps_ro01.dbf'

size                                                  10M
autoextend on maxsize                       200M
extent management local uniform size  64K;


2. Create APPS read-only DB user:
create user apps_ro identified by apps_ro default tablespace  APPS_RO  temporary tablespace TEMP;


3.Grant  APPS read-only DB User:
grant connect, resource to  APPS_RO;


4. Linux SH Script add_to_apps_ro.sh:

#!/bin/ksh
SYSTEM_PWD=$1
OBJECT=$2
OBJECT=`echo $OBJECT | tr [:lower:] [:upper:]`
export SYSTEM_PWD OBJECT


if [ "$SYSTEM_PWD" = "" ] || [ "$OBJECT" = "" ]
then
echo "Usage: install_conc.sh <system password> <object_name>"
echo "For all DB objects please run: add_to_apps_ro.sh <system password> < % >" 
exit
fi;


if [ "$OBJECT" = "%" ]
then
echo "The procedure will run for all DB objects"
echo "--------------------------------------------"
echo "All DB objects will be added to APPS_RO user..."
echo "--------------------------------------------"
sqlplus "/as sysdba" << EOF
@apps_ro_all_create.sql
EOF
echo "Done..."
exit
fi;


echo "--------------------------------------------"
echo $OBJECT " will be added to APPS_RO user..."
echo "--------------------------------------------"
sqlplus "/as sysdba" << EOF
@apps_ro_object_create.sql $OBJECT
EOF
echo "Done..."


5. apps_ro_all_create.sql Script

set serveroutput on size 99999
set verify off
set feedback off
set pagesize 0
set linesize 150
set verify off
spool cre_grant.sql
select 'grant select on ' || owner || '.'|| object_name || ' to apps_ro;' 
FROM   all_objects
where    object_type IN ('VIEW', 'TABLE')
/
spool off
set serveroutput on size 99999
set verify off
set feedback off
set pagesize 0
set linesize 150
set verify off
spool cre_synonym.sql
select 'create or replace synonym apps_ro.' || object_name || ' for '  || owner || '.'|| object_name || ';'
FROM   all_objects
where    object_type IN ('VIEW', 'TABLE')
/
spool off
@cre_grant.sql
/
@cre_synonym.sql
/
exit


6. apps_ro_object_create.sql  Script:

set serveroutput on size 99999
set verify off
set feedback off
set pagesize 0
set linesize 150
set verify off
spool cre_grant.sql
select 'grant select on ' || owner || '.'|| object_name || ' to apps_ro;' 
FROM   all_objects
where    object_type IN ('VIEW', 'TABLE')
and object_name = '&1'
/
spool off
set serveroutput on size 99999
set verify off
set feedback off
set pagesize 0
set linesize 150
set verify off
spool cre_synonym.sql
select 'create or replace synonym apps_ro.' || object_name || ' for '  || owner || '.'|| object_name || ';'
FROM   all_objects
where    object_type IN ('VIEW', 'TABLE')
and object_name = '&1'
/
spool off
@cre_grant.sql
/
@cre_synonym.sql
/
exit

7 . How to use it:

• Connect to Linux server with oracle user.
• Create Folder apps_ro and put sh and sql's there.
• chmod +x add_to_apps_ro.sh
• Run add_to_apps_ro.sh

Usage: install_conc.sh <system password> <object_name>
For all DB objects please run: add_to_apps_ro.sh <system password> < % >" 


8 . Schedule the compilation apps_ro invalid objects (conditional).


Good Luck ...