Re: Apps read only access

Posted by raiq1 on Mar 15, 2020; 9:21am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/Apps-read-only-access-tp8145p8150.html

Dear Erman,


Kindly suggest me below steps are correct as per my requirement.




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;