Multiple Organization Access Control (MOAC)

The single most major change in R12 is the introduction of a concept known as Multiple Organizations Access Control (MOAC). This is a new feature in the existing Multi-org architecture.

Previously, in 11i, data was restricted to a single operating unit using views which partition the base table data, based on the Operating Unit setting controlled by Profile Options.

• Data from all the operating units are present in tables that are usually followed by a ‘_ALL’ in their name.
• Each table contained a column called ORG_ID that represented which operating unit a particular row belonged to.
• Data was then restricted using views (without _ALL) that returned data corresponding to the current organization’s ORG_ID.

This is replaced by a more flexible architecture in R12 that allows users to access data from more than one operating unit using a single responsibility. Defining security profiles does this.

• Oracle Multi-org views have been removed, and replaced with synonyms.
• The synonym points to the _ALL table.

Attaching a Virtual Private Database (VPD) policy to the synonym does the data restriction.

Features of 11.5.9

• MO: Operating Unit profile option at responsibility level sets the Operating Unit context
• FND Initialize client_info() to the value of MO: Operating Unit when a new session is started

Features of R12

Responsibility level profile options:

• MO: Operating Unit
• MO: Security Profile
• MO: Default Operating Unit
• Profile 'MO: Operating Unit' will restrict data access for a single Operating Unit
• Profile 'MO: Security Profile' will ensure access to multiple operating units from single responsibility
• The operating unit field in the form will automatically default to the value set in profile ‘MO: Default Operating Unit'

Multi-Org Initialize:

• De-centralized from FND Initialization
• Global Temporary Table initialized to content of MO: Security Profile

Example:

In pre Release 12, you would have had following methodology for PO_HEADERS_ALL

a. A table is created in PO Schema, named PO_HEADERS_ALL

b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring

    to PO.PO_HEADERS_ALL

c. Create a view PO_HEADERS in APPS schema, as

SELECT  *

FROM po_headers_all

WHERE org_id = client_info

But now in R12, following will happen

a. A table is created in PO Schema, named PO_HEADERS_ALL

b. A synonym named PO_HEADERS_ALL is created in APPS schema, referring

    to PO.PO_HEADERS_ALL

c. Another synonym named PO_HEADERS is created in APPS, referring to

    PO_HEADERS_ALL

d. A Row Level security is applied to PO_HEADERS, using package

function MO_GLOBAL.ORG_SECURITY.

This can be double-checked by running SQL

SELECT * FROM all_policies

WHERE object_name='PO_HEADERS'

Purpose of MO_GLOBAL.ORG_SECURITY:

The purpose of Row-Level-Security is to hide certain data [based on some conditions]. The function implementing the security policy works in the following manner:

• If the access mode is M (Multiple), then the policy predicate issues an EXISTS sub-query to a global temporary table. The table stores and manipulates data specific to a SESSION or TRANSACTION.
• If the access_mode is S (Single), then a simple equality predicate is used for performance reasons, since it is cost effective in comparison to the temporary table.
• An access mode A (All) is incorporated to bypass the security for functionality that needs full table access.

If the access mode is not set or is NULL, then a simple predicate that uses the CLIENT_INFO value for ORG_ID is used for the policy predicate to support backward compatibility.

MO_GLOBAL.Org_Security function:

FUNCTION org_security (obj_schema VARCHAR2

                       obj_name VARCHAR2)

RETURN VARCHAR2

IS

BEGIN  --

  -- Returns different predicates based on the access_mode

  -- The codes for access_mode are

  -- M - Multiple OU Access

  -- A - All OU Access

  -- S - Single OU Access

  -- Null - Backward Compatibility - CLIENT_INFO case

  --

 IF g_access_mode IS NOT NULL THEN

   IF g_access_mode = 'M' THEN

     RETURN 'EXISTS (SELECT 1

                       FROM mo_glob_org_access_tmp oa

                       WHERE oa.organization_id = org_id)';

   ELSIF g_access_mode = 'A' THEN -- for future use

     RETURN NULL;

   ELSIF g_access_mode = 'S' THEN

     RETURN 'org_id = sys_context (''multi_org2'',''current_org_id'')';

   END IF;

 ELSE

   RETURN 'org_id = substrb (userenv (''CLIENT_INFO''), 1,10)';

 END IF;   

END org_security;

The effect of this policy is that, whenever you access PO_HEADERS,

Oracle RLS will dynamically append WHERE CLAUSE similar to below

SELECT * FROM PO_HEADERS

WHERE EXISTS (SELECT 1

              FROM mo_glob_org_access_tmp oa

                 WHERE oa.organization_id = org_id)

Or is modified at runtime if the user’s access responsibility can access one Operating Unit with access control enabled for the module to:

SELECT * FROM PO_HEADERS

WHERE ORG_ID = sys_context ('multi_org2','current_org_id');

The following code templates are to be used in R12.

1. To ADD Row Level Security (RLS) POLICY TO CUSTOM OBJECTS



BEGIN

   DBMS_RLS.add_policy(object_schema        => 'apps',

                        object_name          => 'xx_ad',

                        policy_name          => 'org_sec',

                        function_schema      => 'apps',

                        policy_function      => 'MO_GLOBAL.ORG_SECURITY',

                        statement_types      => 'select, insert,update, delete',

                        update_check         => TRUE,

                        ENABLE               => TRUE,

                        static_policy        => FALSE,

                        policy_type          => DBMS_RLS.shared_context_sensitive

                       );

END;

2. R12 INITIALISATION

To initialize to multiple operating units with same Responsibility


BEGIN

FND_GLOBAL.APPS_INITIALIZE('user_id','resp_id','resp_appl_id');

mo_global.init('appl_shrt_name');

END;
Purpose of MO_GLOBAL.INIT :-

It will check if new Multi Org Security Profile is set, to decide if new Security Profile method will be used. If the new MO security
profile is set, then mo_global.init inserts one record, for each Organization in Org Hierarchy, in table
mo_glob_org_access_tmp. This package procedure will be called as soon as you login or as soon as you switch responsibility. Just like FND_GLOBAL.INITIALIZE
is called.
 
To initialize to a single Operating Unit


BEGIN

MO_GLOBAL.SET_POLICY_CONTEXT('S','org_id');

END;
Purpose of MO_GLOBAL.SET_POLICY_CONTEXT :-

This procedure has two parameters

p_access_mode: Pass a value "S" in case you want your current session to work against Single ORG_ID
               Pass a value of "M" in case you want your current session to work against multiple ORG_ID's

p_org_id:  Only applicable if p_access_mode is passed value of "S"