Initializing E-Business Suite Context for a Read-Only-User

14. September 2022

Johannes Michler PROMATIS Horus Oracle


Executive Vice President – Head of Platforms & Development

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.

Initializing Context

When connected with the APPS user, this can be solved by running:
exec mo_global.init(‘M’);
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.

More grants

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
DECLARE
BEGIN
EXECUTE IMMEDIATE ‘ALTER SESSION SET CURRENT_SCHEMA =APPS’;
apps.mo_global.init(‘M’);
END;

Summary

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.