- OracleApps Epicenter - http://www.oracleappshub.com -

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.

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.

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 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:

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:

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.

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.

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.

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.

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.

Multi-Org Access Control(MOAC) Profile Options

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

Reference Note

4 Comments (Open | Close)

4 Comments To "MOAC : Technical Insight"

#1 Comment By Anil Bejugam On September 4, 2008 @ 4:06 am

This post is very useful, Thanks Sanjit Anand

#2 Comment By Rajesh On December 5, 2008 @ 12:57 pm

Very Nicely presented. very simple to understand.

#3 Comment By chowreddy On May 12, 2010 @ 1:02 am

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 Comment By Jeewan On March 20, 2012 @ 3:36 am

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