Oracle Cloud offers a broad portfolio of software as a service applications, platform as a service, and social capabilities, all on a subscription basis. Oracle Cloud delivers instant value and productivity for end users, administrators, and developers alike through functionally rich, integrated, secure, enterprise cloud services.
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Subscribe to the OracleAppsHub to receive notifications when there are new posts:

 get RSS feed
 Oracle Fusion Applications (OFA) is a portfolio of next generation suite of software applications from Oracle Corporation. It is distributed across various product families; including financial management, human capital management, customer relationship management, supply chain management, procurement, governance, and project portfolio management
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

MOAC : Technical Insight

Posted on May 9th, 2008 by Sanjit Anand |Print This Post Print This Post |Email This Post Email This Post

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today

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:

dgreybarrow-211i

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>’);

dgreybarrow-2Release 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 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 !!

dgreybarrow-2How 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.

dgreybarrow-2Multi-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

dgreybarrow-2A Note on MO_GLOBAL

Here is the summrize list of the MO_GLOBAL API.

Name Type

Details

SET_POLICY_CONTEXT Procedure
  • p_access_mode
  • p_org_id number

This procedure will set the access mode, and when applicable the current operating unit context.

SET_ORG_ACCESS Procedure
  • p_org_id_char
  • p_sp_id_char
  • p_appl_short_name

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".

INIT Procedure
  • p_appl_short_name

Pass 'S' OR 'M' to initilize the OU for single or Multiple OU context
The procedure used by the applications when starting a new session.

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.

GET_CURRENT_ORG_ID Function

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

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.

dgreybarrow-2 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.

dgreybarrow-2 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');

dgreybarrow-2 Similar Post

dgreybarrow-2Reference 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?

Related Posts

Posted in R12, Release12, Technical | 4 Comments »Email This Post Email This Post | Print This Post Print This Post

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today
4 Responses
  1. Anil Bejugam Says:

    This post is very useful, Thanks Sanjit Anand

  2. Rajesh Says:

    Very Nicely presented. very simple to understand.

  3. chowreddy Says:

    Hi,
    You have provided a pretty good information regarding to the R12. I just want to know some more information about R12
    tecnical, i mean what are the tables gets changed in R12 when compare to 11i.please provide that information.

    Thanks
    Chowreddy.g

  4. Jeewan Says:

    Thanks a lot , Sanjit , this is really good one .

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.