As we know in R12 , the existing Multiorg architecture includes a new feature Multiple Organizations Access Control (MOAC). Lot of queries and question were asked by my readers, therefore this post completely focus on some of technical Insight because of architectural change.The minimum you should suppose to know is that Access Control feature has backward compatible. Technically means that there are no code or procedural changes if MOAC is not implemented (i.e. The user is assigned one operating unit for a responsibility).
Lets take a quick look with some high points in two major family products:
In 11i, data was restricted to a single operating unit using views which partition the base table data based on the OU setting controlled by Profile Options.
- Based out of base tables (generally named with â€˜_ALLâ€™) contained data for all operating units.
- Each such table contained a column named ORG_ID to indicate what operating unit a particular row belonged to.
- Data was then restricted by using restricted views (without _ALL table) which would only return rows which corresponded to the current operating unitâ€™s organization ID.
- The current OU was stored in the first 10 characters of the database Application Context variable CLIENT_INFO.
- When logging into the applications, the CLIENT_INFO value was set to the appropriate operating unit organization ID for the session based on the profile option setting for â€œMO: Operating Unitâ€.
- In SQL*Plus, CLIENT_INFO could be set to point to a particular operating unit using
In release 12 a more flexible architecture allows users to define security profiles so that users may access data for more than one operating unit within a single responsibility.
Oracle Multi-org views have been removed, and replaced with synonyms.
Thats means in R12 table with _ALL is no longer be a view defined on base table as Oracle have used the convention earlier, but provided a synonym which points to _ALL table
- The data is restriction 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.
The next question , you have in mind is what is VPD..right?
As said some time by Steven  blog, VPD  is one feature that allows development community to enforce security by attaching a security policy to database objects such as tables, views and synonyms.
It attaches a predicate function to every SQL statement to the objects by applying security policies.
When a user directly or indirectly accesses the secure objects, the database rewrites the user's SQL statement to include conditions set by security policy that are visible to the user.Sounds good !!
How it works
If you see the Oracle Documentation , ORG_SEC policies with these information restrict on the object to restrict the data returned.
- Policy_name : ORG_SEC
- Policy_group : SYS_DEFAULT
- Package : MO_GLOBAL
- Function : ORG_SECURITY
Important to note function (MO_GLOBAL.ORG_SECURITY) is called with the following parameters:
- obj_schema - the object schema, in this case APPS
- obj_name â€“ the object name
Which then returns additional where clause conditions to restrict the data accessible from the object.The structure of this function will dynamically generate conditions which will either:
- Restrict the data to a single operating unit if the access mode is Single
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.
- Restrict the data to multiple operating units if the access mode is Multiple
You need to add additionally in your query.
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.
- Restrict the data to eliminate only seed data rows is the access mode is All
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.
- Restrict the data to not return any rows if the access mode is None
1 = 2
The condition is never satisfied. No data will be returned from the object.
Multi-Org Session Context
The database utility DBMS_SESSION.SET_CONTEXT(<namespace>, <attribute>, <value>) is used to initialize and set the Multi-Org context information for a userâ€™s session.
The key context items are:
|multi_org||access_mode||S=Single, M=Multiple, A=All, X=None|
|multi_org2||current_org_id||Operating unit org id, only applicable if access mode is Single|
A Note on MO_GLOBAL
Here is the summrize list of the MO_GLOBAL API.
This procedure will set the access mode, and when applicable the current operating unit context.
This procedure determines if the application specified has multi-org access control enabled, by querying FND_MO_PRODUCT_INIT for the application short name.
If this is enabled, and a security profile is specified (p_sp_id_char), then all orgs the user has access to will be populated in MO_GLOB_ORG_ACCESS_TMP.
If there are more than one such org, the access method will be set to "Multiple". Otherwise if no security profile id is specified, it will use the value of p_org_id to set the current operating unit value and set the access mode to "Single".
Pass 'S' OR 'M' to initilize the OU for single or Multiple OU context
Based on the profile options "MO: Operating Unit" (ORG_ID) and "MO: Security Profile" , this procedure calls set_org_access to establish the multi-org context for the session. To call this from withing SQL, the profile option context should have been initialized for the session.
Returns the current operating unit setting.
This should be null if the access mode is not 'S'
|GET_ACCESS_MODE||Function||Returns the current access mode value(Access mode S,M or A) strored in the application Context.|
Other Procudure or Function can be used as
- Type : Function
- Purpose :This is used to check if the specified operating unit exists in the session's access control list.Very similar to check_access function but have capability to displays an error message
- Type: Function
- Purpose: This function returns the number of records (operating units) stored in the multiple organizations temporary table.
- Type : Function
- Purpose :This will check if multiple organizations is initialized. The order is Temp table -> Current Org -> Access Mode (e.g. S, M or A) . It will return Value Y if initialization is done, else N.
Multi-Org Access Control(MOAC) Profile Options
MO: Security Profile
The MO Security Profile controls the list of operating units that a responsibility or user can access. If you set the security profile at the responsibility level, then all users using that responsibility will have access to only the operating units available in the security profile. If you set the security profile at the user level, then the user will have access to only those operating units, irrespective of application responsibility that they log into.
MO: Default Operating Unit
The MO: Default Operating Unit is optional and allows you to specify a default operating unit that defaults when you open different subledger application pages. Because you can access multiple operating units, you may want to set up a default one instead of forcing users to constantly have to choose one. User Preferences allows you to specify a default operating unit at the user level. Use the MO: Default Operating Unit profile option to set the operating unit context or default operating unit when accessing an applications.
MO: Operating Unit
This profile option is for backwards compatibility and to support products that do not use Multiple Organizations. The release 11i setting was for this is preserved during upgrade. The Release 11i MO: Operating Unit profile option is also supported in Release 12.
Example of Setting of Single Org Context
then you need to set the mo security profile so that sqlplus will have same org context as given responsibility
By SQL you can do setup as:
Oracle Integration Repository
Note 211431.1 :
Note 420787.1 :Oracle Applications Multiple Organizations Access Control for Custom Code
Note:437119.1 :How to set the Organization Context in R12?