R12 Multi-Org Access Control (MOAC)
In previous releases, data was restricted to a single operating unit using views that striped base table data based on the current operating unit setting.
In release 12 a more flexible architecture has been put in place to support Multi-Org Access Control (MOAC). This architecture allows users to define security profiles so that users may access data for more than one operating unit within a single responsibility.
To accomplish this
• Multi-org views have been removed, and replaced with synonyms. For example, MY_TABLE
would no longer be a view defined on MY_TABLE_ALL, but rather a synonym which points
to MY_TABLE_ALL
• The data restriction is accomplished by assigning a virtual private database (VPD) policy to
the synonym. This policy allows the system to dynamically generate restricting conditions
when queries are run against the synonym.
Data relating to defined VPD policies is viewable in the data dictionary view DBA_POLICIES. These policies associate a function with an object, and when the object is accessed, this function can return additional restrictions on the object to restrict the data returned. The particular policy used to
implement Multi-Org in release 12 is:
• Policy_name: ORG_SEC
• Policy_group: SYS_DEFAULT
• Package: MO_GLOBAL
• Function: ORG_SECURITY
MOAC Setup
The setup for MOAC can be found in the HR Foundation Responsibility. Use the Navigation Path
Security => Global Profile. This Profile should be set up to have access to all Operating Units defined
in the E-business Suite. To do this a Security Type of Secure organization by organization hierarchy and/or organization list should be assigned to the Global Profile and each of the valid Operating Units
within your business should be added to the list of Organization Names. E.g.
Additional profiles can be added through the Security => Profile screen. Each profile can be set up to
access one or a number of operating units. A profile must be set up for each combination of operating
units you wish to access through the E-business suite.
We will use a worked example throughout this document to demonstrate the MOAC functionality. For the example, I have set the Global Profile to include 2 operating units (these operating units had org id’s of 82 and 84 respectively).
Security Profiles can then be assigned to the MO: Security Profile profile at Site, Application,
Responsibility, Organization and User levels. For the purpose of this document I have set it up at user level for my own user.
When I now try to access data through the UI can see data spanning the 2 Operating Units.
Demonstrate this we will look at 2 the Standard AR Receipts screen (Below). We can clearly see that
the screen is showing Receipts from 2 different Operating Units.
Database Multi-Org Access Control
In previous versions of the E-business Suite you may have used standard applications procedures such as
DBMS_APPLICATION_INFO.SET_CLIENT_INFO or FND_CLIENT_INFO.SET_ORG_CONTEXT.
These are superseded in Oracle Release 12 by the procedure MO_GLOBAL.INIT.
The MO_GLOBAL.INIT procedure accepts one parameter, Application Short Name. In order for the procedure to work, the FND_GLOBAL.APPS_INITIALIZE procedure must also be run so that theMO_GLOBAL package can see the Application Profile Options Values defined in the in the E-business Suite.
To use MOAC at database level through an SQL Editing tool such as Toad or SQL Developer, both
these procedures must be run.
Before we do this however, we demonstrate the effect of trying to access data through the new standard synonyms without running the procedures first. We again turn to our previous example, where we displayed 2 AR Receipts.
Firstly we try to access the information from the base table AR_CASH_RECEIPTS_ALL without
setting any session variables. This demonstrates that the data is there and that the 2 Receipts span both Operating Units:
We now try to access the data using AR_CASH_RECEIPTS synonym (remember that this is now a
synonym that points to the base table, but has a VPD Policy assigned to it). We demonstrate here that
the data cannot be seen.
We now setup all the variables needed for MOAC by running the 2 procedures. (In this example I have
already looked up my own User ID = 1299, the Responsibility ID for Receivables = 50538 and the
Receivables Application ID = 222). We then run the same query using AR_CASH_RECEIPTS and the data is now visible for both Operating Units.
Understanding the VPD
We have already seen that we must execute the MO_GLOBAL.INIT procedure to access data via
synonyms with VPD assignments. When we do this the MO_GLOBAL.INIT procedure calls the
function MO_GLOBAL.ORG_SECURITY with the following parameters:
• obj_schema - the object schema, in this case APPS
• obj_name – the object name (e.g., MY_TABLE)
The function then returns additional where clause conditions to restrict the data accessible from the
object. The structure of this function will dynamically generate conditions that will either:
1. Restrict the data to a single operating unit if the access mode is Single
2. Restrict the data to multiple operating units if the access mode is Multiple
3. Restrict the data to eliminate only seed data rows is the access mode is All
4. Restrict the data to not return any rows if the access mode is None
The conditions returned in each case are as follows:
Single OU Access
org_id = sys_context('multi_org2','current_org_id')
Only data for the current operating unit is accessible. The value of
sys_context('multi_org2','current_org_id') would have to be set to the current operating unit.
Multiple OU Access
EXISTS (SELECT 1
FROM mo_glob_org_access_tmp oa
WHERE oa.organization_id = org_id)
The user will be able to access data for any org_id which has been populated into
mo_glob_org_access_tmp. When a session is initialized in the applications, values will be populated into
mo_glob_org_access_tmp for each of the operating units the user has access to based on their "MO:
Security Profile" setting.
All OU Access
org_id <> -3113
Seed template records, which are used to create new seed data when a new operating unit is created, are created with an org_id of –3113. So in this mode, only these template records, which do not correspond to any actual operating unit, will be filtered out.
No OU Access
1 = 2
The condition is never satisfied. No data will be returned from the object.
Switching Off MOAC
MOAC can be easily switched off by simply not setting the MO: Security Profile profile at any level.
The MO_GLOBAL.INIT procedure will then simple set the context to for the Single Operating Unit as defined in the profile MO: Operating Unit.
Backwards Compatibility
When running queries on multi-org objects in SQL, you can still use the old CLIENT_INFO settings to gather data and run queries against multi-org objects if the following profile option is set to "Yes":
User Profile Name MO: Set Client_Info for Debugging
Profile Nam: FND_MO_INIT_CI_DEBUG
When this profile option is set to “Yes” and the global access mode setting is null (as it would be in a
SQL*Plus or other client session unless specifically set), the VPD function
MO_GLOBAL.ORG_SECURITY will return the following as the additional where clause condition for the object:
org_id = substrb(userenv('CLIENT_INFO'),1,10)
This will limit the data returned by the object to the current value set in CLIENT_INFO.
