Research on Oracle EBS multi-organization shielding

1.1 example of tables with OU shielding

SELECT t.org _ id, t. * FROM po. po_headers_all t -- unshielded table, running data in PL/SQL
SELECT t.org _ id, t. * FROM apps. po_headers t -- contains OU blocking and no data is queried in PL/SQL.

1.2 principle of multi-organization shielding

A. Create a table on the PO Schema named PO_HEADERS_ALL.
B. Create a synonym (synonym) PO_HEADERS_ALL on the APPS schema, pointing to PO. PO_HEADERS_ALL
C. Do not create a wildcard character (synonym) in APPS: PO_HEADERS, pointing to PO_HEADERS_ALL
D. Use MO_GLOBAL.ORG_SECURITY to apply row-level security to PO_HEADERS.
You can run SQL select * from all_policies where object_name = 'po _ headers' to confirm again
E. The impact of this policy is that, whenever you access PO_HEADERS, Oracle RLS dynamically extends the WHERE Condition Statement, as shown in figure
SELECT * FROM PO_HEADERS where exists (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE oa. organization_id = org_id)

1.3 simulate logon so that apps. po_headers can query data in PL/SQL

BEGIN
Fnd_global.pai_initialize (user_id => 1433, resp_id => 50691,
Resp_appl_id = & gt; 222 );
Mo_global.init ('M ');
END;

Here, the pai_initialize parameter can be obtained through the following steps:
1. Help-> diagnosis-> check
2. Fill in the value in "Block": $ PROFILES $
3. Enter user_id, resp_id, and resp_appl_id in the fields to obtain the values respectively.
4. After simulating logon, you can run the query statement in PL/SQL.
SELECT t.org _ id, t. * FROM apps. po_headers t -- after Simulated logon, query contains data

1.4 introduction to the mo_glob_org_access_tmp table

A. From the second point, we know that when you access PO_HEADERS, Oracle RLS dynamically extends the WHERE Condition Statement, such:

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

A. query the table mo_glob_org_access_tmp before running the simulated logon. It is null. The WHERE statement is false, so we can directly query po_headers and find that no data is found.

B. After running the simulated logon, query the table mo_glob_org_access_tmp again and obtain the following query results:

A. query po_headers again and find the data. The data found through data analysis is consistent with the data that can be queried:

A. Therefore, we can draw a conclusion: When "simulated Logon" is run, the system inserts the ORG_ID that is allowed to access to the database table mo_glob_org_access_tmp. When querying tables that contain OU shielding, the system automatically determines the ORG_ID that can be queried in the mo_glob_org_access_tmp table of the current session, so we can access the data of these org_ids.

A. When we delete a piece of data from the mo_glob_org_access_tmp table, such as deleting the first data: 81 OU_AWL, we re-query the po_headers table and find the following data:

A. When we re-open a session and do not simulate logon, we directly insert ORG_ID into the mo_glob_org_access_tmp table and re-query the po_headers table. We can also obtain data. So we can draw the following conclusions:

When you access PO_HEADERS, Oracle RLS dynamically extends the WHERE Condition Statement.

SELECT *
FROM po_headers
Where exists (SELECT 1
FROM mo_glob_org_access_tmp oa
WHERE oa. organization_id = org_id)
A.
After Multi Org initialization in your session, in the mo_glob_org_access_tmp table, your session will have X records. X indicates the number of organizations assigned to the MO Security Profile.

1.5 mo_global.init objective

It checks whether the new Multi Org Security Profile is set to determine whether a new Security Profile method is used.

If a new MO security profile is set, mo_global.init inserts a new record to the mo_glob_org_access_tmp table for each organization in Org Hierarchy.

This method will be called immediately after you log on or switch the role. Just like FND_GLOBAL.INITIALIZE is called, it can be safely confirmed that Oracle will call MO_GLOBAL.INIT after FND_GLOBAL.INITIALIZE.

1.6 MO_GLOBAL.SET_POLICY_CONTEXT ('s ', 101)

ORG_ID 101 will be assigned to your current session.

Internally, when you set Context for a single org, this code will be executed: dbms_session.set_context ('multi _ org2 ', 'current _ org_id ', 101 );