Executive Vice President – Head of Platforms & Development
In Oracle E-Business Suite environments it is often necessary to query data. Typically, all data can be accessed through the APPS user. However, that user is highly privileged and, when in use, can easily “break things”. This is obviously a big compliance issue. Having a dedicated read-only user is thus a much better approach. Let’s see how such a “XXREAD”-user can be created.
In order for the APPS user to be able to grant access to objects the user doesn’t actually own (e.g. tables in XX schemes), the APPS user needs to have privileges on those objects “including grant option”. This can be achieved with a script such as the following one, which should be run whenever new custom objects are created (as the XX scheme user):
set echo off;
set verify off;
FOR r IN (
'grant all on '
|| ' to apps with grant option' statement_to_run
(object_type IN ( 'SEQUENCE', 'TABLE' )
or object_type='VIEW' and object_name like '%#')
AND NOT EXISTS (
privs.table_name = dbos.object_name
AND privs.type = dbos.object_type
AND privs.grantable = 'YES'
AND privs.owner = user
order by object_type asc
EXECUTE IMMEDIATE r.statement_to_run;
Creating a user and granting privileges
First, create a XXREAD user and produce a script to grant privs:
sqlplus -s system/<system-pwd>
create user XXREAD identified by MyAppsReadPwd;
grant create session to XXREAD;
grant alter session to XXREAD;
SET echo off
SET feedback off
SET term off
SET pagesize 0
SET linesize 200
SET newpage 0
SET space 0
select 'exec AD_ZD.GRANT_PRIVS(''READ'','''||VIEW_NAME || ''', ''XXREAD'');' from all_views where OWNER ='APPS' and view_NAME not like '%/%';
select 'exec AD_ZD.GRANT_PRIVS(''READ'','''||table_NAME || ''', ''XXREAD'');' from all_tables where OWNER ='APPS' and table_NAME not like '%/%';
select 'exec AD_ZD.GRANT_PRIVS(''READ'','''||SYNONYM_NAME || ''', ''XXREAD'');' from all_synonyms syns, all_objects objs where
syns.OWNER ='APPS' and syns.SYNONYM_NAME not like '%/%'
and syns.table_owner=objs.owner and syns.table_name=objs.object_name
and objs.object_type in ('TABLE','VIEW');
Then, run the spooled script and register the XXREAD user:
sqlplus apps/<appspwd> @$AD_TOP/patch/115/sql/ADZDREG.sql apps <appspwd> XXREAD
sqlplus apps/<appspwd> @/u01/install/APPS/fs1/EBSapps/appl/ad/12.0.0/sql/adutlrcmp.sql
First tests and logon trigger
After having initially created the user, sign in as XXREAD and do some initial testing by accessing apps.* tables.
Unfortunately, so far, it is still necessary to do all selects with an “apps.” prefix. This can be solved with two approaches:
- Create a synonym in XXREAD for each APPS object.
- Create a logon trigger that changes the CURRENT_SCHEMA.
Such a trigger can look as follows:
AFTER logon ON XXREAD.SCHEMA
EXECUTE IMMEDIATE 'declare begin '||'dbms_application_info.set_client_info ( 101 );end;';
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA =APPS';
With the above procedure, it is possible to create a XXREAD user that can access (in a reading fashion) everything from the APPS user. This is a lot more secure than really using the APPS user/password since in that way, no one can accidentally run a “truncate” on some table. Also keep in mind, though, that such a XXREAD user is not the perfect solution and allows a lot of (reading) access, which still might be a GDPR issue; obviously, you also want to limit access to this XXREAD user as restrictive as possible.