Recently I described how a read-only user can be created for Oracle E-Business Suite.
This works great, however, when trying to access the pre-built views of E-Business Suite, many of them do not show any data:
Let’s see how we can remedy this.
When connected with the APPS user, this can be solved by running:
This internally populates session-specific information on which operating units are accessible in a “M”ulti-Org.
Then, the above Statement returns all invoices as expected:
However, this query obviously can not run with the XXREAD user as created in the previous blog post, since we did not grant execute privileges to mo_global.
Let’s fix this:
exec AD_ZD.GRANT_PRIVS(‘EXECUTE’,’MO_GLOBAL’, ‘XXREAD’);
Unfortunately, this is not enough since the MO_GLOBAL package calls more procedures and is defined with AUTHID CURRENT_USER (see https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/managing-security-for-definers-rights-and-invokers-rights.html).
There are basically two options to solve this problem:
- You can create a “proxy package” with “AUTHID DEFINER” in the APPS schema, grant execute privs on that package to XXREAD and then call mo_global.ini from there. This approach can be used for other functions as well.
- You need to grant the following privileges to XXREAD:
exec AD_ZD.GRANT_PRIVS(‘EXECUTE’,’FND_GLOBAL’, ‘XXREAD’);
exec AD_ZD.GRANT_PRIVS(‘ALL’,’MO_GLOB_ORG_ACCESS_TMP’, ‘XXREAD’);
Afterwards, the mo_global.init procedure can also be run as XXREAD.
Extending the Logon-Trigger
If you prefer to not run the mo_global.init function after each session-creation, you can extend the logon-trigger as follows:
create or replace TRIGGER xxread.xxread_query_logon_trg
AFTER logon ON XXREAD.SCHEMA
EXECUTE IMMEDIATE ‘ALTER SESSION SET CURRENT_SCHEMA =APPS’;
By extending the XXREAD user created according to my previous blog post as described above, it is easily possible to access VPD-protected views and tables. It is thus even easier to replace (more dangerous) access as the APPS user with a less privileged user; in many scenarios where 3rd party systems are only reading the APPS user can be replaced with this XXREAD user without further modifications in the 3rd party systems.