Step to Create READ ONLY DB user in Oracle Application

Please Visit http://www.conacent.com/?page_id=218


Document Title
How to Create APPS READ ONLY user

Document PurposeTo create user similer to Apps but with limited access for Security

Document DetailsExecute the steps as SYSTEM user
Step 1.  sqlplus system/manager
             Create user conacent identified by conacent DEFAULT TABLESPACE APPS_TS_TX_DATA QUOTA UNLIMITED ON APPS_TS_TX_DATA;
Step 2. Grant connect,resource,DBA to conacent with admin option;
Step 3. ALTER USER conacent ENABLE EDITIONS;

Execute this steps in O/S DB user
Step 4. vi syns.sql
set echo off
set pagesize 0
set linesize 300
set feedback off
spool create_synonyms.sql
SELECT 'create or replace synonym '||synonym_name||' for '||table_owner||'.'||synonym_name||';' FROM user_synonyms;
SELECT 'create or replace synonym '||object_name||' for APPS.'||object_name||';' FROM user_objects WHERE object_type IN ('TABLE','VIEW');
spool off
exit;


Execute this steps in APPS DB user
Step 5. sqlplus apps/apps
Step 6. SQL>@syns.sql
Note: This query will create one file name as create_synonyms.sql.
Step 7. Exit from apps user.

Execute this steps in Newly created DB user
Step 8. sqlplus conacent/conacent
Step 9. @create_synonyms.sql
Note. This SQL will taken nearly about 30-40 minutes to execute.

Step 10. Exit from the newly created user

Execute this steps in system user in DB
Step 11. revoke connect,resource,DBA from conacent;
Step 12. Grant create session, select any table to conacent with admin option;

Now you can test your newly created user.