Multi Org functionality and SQL in Oracle Apps R11 and R12

To set the org context in an SQL session:
Release 11

DECLARE
BEGIN
apps.Fnd_Client_Info.set_org_context('xx');
END;

or

DECLARE
BEGIN
DBMS_APPLICATION_INFO.set_client_info('xx');
END;

Release 12

Also see Metalink Note: 462383.1

BEGIN
PA_MOAC_UTILS.SET_POLICY_CONTEXT('S',&P_ORG_ID);
END;

or

BEGIN
MO_GLOBAL.SET_POLICY_CONTEXT('S',&P_ORG_ID);
END;

For example, the MO policy for a specific table in R12:

SELECT object_name
,      policy_name
,      package
,      function
,      enable
FROM   sys.all_policies
WHERE  object_name = upper('PA_EXPENDITURES')

How is CLIENT_INFO being replaced in R12?

Lets take an example.

In Release 11, the following methodology was used for, say, PO_HEADERS_ALL:

  1. A table is created in the PO Schema, named PO_HEADERS_ALL.
  2. A synonym named PO_HEADERS_ALL is created in the APPS schema, referring to PO.PO_HEADERS_ALL.
  3. Create a view PO_HEADERS in APPS schema, using…
SELECT *
FROM   po_headers_all
WHERE  org_id = client_info

But now in Release 12, the following will happen:

  1. A table is created in the PO Schema, named PO_HEADERS_ALL.
  2. A synonym named PO_HEADERS_ALL is created in the APPS schema, referring to PO.PO_HEADERS_ALL.
  3. Another synonym named PO_HEADERS is created in APPS, referring to PO_HEADERS_ALL.
  4. Row Level security is applied to the PO_HEADERS synonym, using the packaged function MO_GLOBAL.ORG_SECURITY.

This can be double-checked by running the following SQL…

SELECT *
FROM   all_policies
WHERE  object_name='PO_HEADERS'

The effect of this policy is that whenever PO_HEADERS is queried, the following WHERE clause will dynamically added:

SELECT *
FROM   po_headers
WHERE  EXISTS
  (
  SELECT 1
  FROM   mo_glob_org_access_tmp oa
  WHERE  oa.organization_id = org_id
  )

Also see **** below

FAQ

Does this mean, if I create a new custom table, I will have to apply RLS [Row Level Security] against the custom table as well?

Yes indeed, if it contains data partitioned by ORG_ID. The packaged function MO_GLOBAL.ORG_SECURITY must be assigned to the custom table/synonym/view.

Will the Multi Org Row Level security be applied against the table or the synonym or the view?

In theory, RLS can be applied against any of the above objects. However in practice, you will apply RLS against objects in the APPS schema. In other words RLS will be applied to synonyms. Basically, the Multi Org views in R11 have been replaced by RLS secured synonyms. Hence no code change is required where the pre-R12 Multi-Org secured view was being accessed. The responsibility of securing data as per ORG_ID now lies with RLS [also known as VPD – Virtual Private Database].

I have made changes to my Multi Org Security Profile, by attaching a new Org Hierarchy. Do I need to run any processes?

Just like we do in HRMS, it is advised that any changes to Security Profiles must be followed by running “Security List Maintenance”.

What is the purpose of MO_GLOBAL.INIT?

  • checks if a new Multi Org Security Profile is set, to decide if a new Security Profile method will be used;
  • if the new MO security profile is set, then mo_global.init inserts one record into the table mo_glob_org_access_tmp for each Organization in the Org Hierarchy.

When, and from where, is mo_global.init called?

This packaged procedure is called when you log in, or when you switch responsibility. Just like FND_GLOBAL.INITIALIZE is called. It is safe to assume that Oracle will invoke MO_GLOBAL.INIT after FND_GLOBAL.INITIALIZE.

Is the mo_glob_org_access_tmp table a global temporary table?

Yes, it is. Hence, after Multi Org has been initialised for your session, your session will have X number of records in the table mo_glob_org_access_tmp. X is the number of organisations assigned to the MO Security profile [view org hierarchy or org list in security profile].

What is the purpose of MO_GLOBAL.ORG_SECURITY?

The purpose of Row-Level-Security is to hide certain data [based on some conditions]. RLS does this by appending a WHERE clause to the secured object.

  • MO_GLOBAL.ORG_SECURITY is a function that returns a predicate for the WHERE clause in a query.
  • The WHERE clause will be appended to the Table/Synonym/View for which Multi Org Row Level security is enabled.

What is the purpose of MO_GLOBAL.SET_POLICY_CONTEXT?

This procedure has two parameters:

p_access_mode

  • Pass a value “S” when you want your current session to access data for a single ORG_ID.
  • Pass a value of “M” when you want your current session to access data for multiple ORG_ID’s.

p_org_id

  • Only applicable if p_access_mode is passed value of “S”.

In SQL*Plus, I wish to set my session to work against a specific Org [one single org]. How do I do that in R12?

SQL>> exec MO_GLOBAL.SET_POLICY_CONTEXT('S',101);

In the above case, ORG_ID 101 will be assigned as the current org_id for your session.

Internally, the following code will be executed by Oracle when you set your context to single Org,

dbms_session.set_context('multi_org2', 'current_org_id', 101);

**** If the current database session is initialised for Single Org [as above], then the WHERE clause appended to the object by Row-Level-Security will be

WHERE org_id = sys_context('multi_org2','current_org_id')

Will I ever use MO_GLOBAL.SET_POLICY_CONTEXT?

Let’s say you wish to call an API to create invoices in ORG_ID 101. If the API does not have a parameter for Org_id, you can do as follows:

  1. exec MO_GLOBAL.SET_POLICY_CONTEXT(‘S’,101).
  2. Call the Invoice API, which will internally read the ORG_ID from MO Current Context.

From SQL*Plus, I wish to simulate a login to a specific responsibility. How do I do this?

  • Call FND_GLOBAL.INITIALIZE

This will set your responsibility id, user_id, etc., then…

  • Call MO_GLOBAL.INIT

This will read the MO profile option values for your responsibility/user, and will initialize the Multi Org Access.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s