Apps read only access

[raiq1]

Dear Ermaan,

We have a environement for R12.1.3 with 11.2.0.4 database . there is a requirement for me to gives the access of Apps read only privilege for our technical people instead of giving apps user every time.


My technical team need apps read only user to view the all reports with limited previlige instead of  using apps id every time.

Kindly assist me to do this exercise.

Regards,
Raiq

[ErmanArslansOracleBlog]

You can easily do this  by connecting your database and checking the APPS grants.
After getting the apps user grants, filter them and gather a list of read only grants.
Once you gather the read only grants, just create a db user such as APPSRO and grant those grants to it.

That's what you need to do. You can do this by using dynamic sql as well..

There are lots of publically available blog posts about this subject. You can check them if you don't know how to gather and filter APPS user grants.

[raiq1]

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;

[ErmanArslansOracleBlog]

There is no need to create synonyms.. You can just use the schema name to access the objects stored in Apps.

The following query is a dynamic sql to create the statements for granting select privilege to APPSREAD user. But it does that for all the objects which are table, view or Synonym and which are not owned by SYSTEM or SYS..

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’);

However, I suggested you something different.
You asked to have a read only APPS user.
I said, check the objects which APPS user has rights for select, and grant select privilege for those objects to APPSREAD user..

You can accomplish this by doing some simple modifications in the query above.

Note that, also you can use the  query as is.. But this time you will have some extra select privileges for  APSREAD user.. If it is okay for you , then you can go ahead with the actions written in that post.