MOAC : Technical Insight

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:

11i

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

execute dbms_application_info.set_client_info(‘<ORG_ID>’);

Release 12

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 ^[1] blog, VPD ^[2] 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.

1. Policy_name : ORG_SEC
2. Policy_group : SYS_DEFAULT
3. Package : MO_GLOBAL
4. 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:

A Note on MO_GLOBAL

Here is the summrize list of the MO_GLOBAL API.

Other Procudure or Function can be used as

1.check_valid_org

• 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

2.get_ou_count

• Type: Function
• Purpose: This function returns the number of records (operating units) stored in the multiple organizations temporary table.

3.is_mo_init_done

• 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

BEGIN
MO_GLOBAL.SET_POLICY_CONTEXT('S', <orgid>);
END;

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:
execute fnd_global.apps_initialize(&user_id,&responsibility_id,&responsibility_application_id);
execute mo_global.init('&product_short_name');

Similar Post

• http://www.oracleappshub.com/release12/moac-from-multi-orgto-multi-access/ ^[3]

Reference Note

• 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?