Multi Org Structure Virtual Private Database

Categories: ERP

Please read the Multi-Org Background information

Virtual Private Database (VPD)

The Virtual Private Database (VPD) feature allows developers 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.

Image

Database Schema – Access to one operating unit

Image

Database Schema – Access to multiple operating units

You can rewrite reference views that join data from multiple single organization views with the security policy attached to one secured synonym and the remaining reference to _ALL tables instead of single organization views. This improves performance because the policy is used once for the reference views that join data from multiple single organization views.
Multiple Organizations Security Policy Predicate

Synonyms replace single organization views that contain the CLIENT_INFO predicate attached to them. When installing, you must attach a security policy function to the multiple organizations synonyms. This indicates that the security is in place irrespective of the tools used to access the data.

The security policy function returns different predicate based on the number of accessible operating units. An application context attribute “ACCESS_MODE” is set based on the accessible operating units. Context sensitive security policy is used for multiple organizations access control to minimize the coding impact. The multiple organizations code in previous releases works in the context of only one operating unit. It was not anticipated that multiple organizations access would be supported. A solution to code impact is to change the policy predicate whenever needed. For example, when you open a form using a responsibility that can access multiple operating units and when you select an operating unit, the operating unit context is established and you do not need to modify the code that is used for validation from that point onwards, if the synonyms return data for the selected operating unit.

If the access mode is M (Multiple), then the policy predicate issues an EXISTS sub-query to a global temporary table. The global temporary table is a new feature in Oracle 8i. 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 simple predicate using CLIENT_INFO is used for the following case:
* Access control is not enabled for older releases of the applications, it is not backward compatible: You cannot enable the multiple organizations access control feature for all products simultaneously because multiple organizations views are shared between products at different levels. For example, if you choose to upgrade Payables but choose to keep an earlier version of Purchasing then Payables is access control enabled, but Purchasing is not. Therefore, Purchasing must replace the views it shares with Payables, such as PO_VENDOR_SITES, and PO_HEADERS, with secured synonyms. The secured synonyms must work as before for Purchasing, since you have not upgraded Purchasing and Purchasing still relies on CLIENT_INFO.
The simple predicate using current_org_id is used for the following cases:
* Access control is limited to only one operating unit: In this case, the access mode is 'S'. An example is when a user can access to only one operating unit through the MO: Security Profile or the MO: Security Profile is not set and the user access depends on MO: Operating Unit.
* Access control is enabled with access to multiple operating units: The security profile provides access to multiple operating units, but in the scope of a transaction since the operating unit is controlled, a simple predicate eliminates additional changes to the server and client side code.
The complex predicate is used for these cases:
* Access is enabled and the security profile gives access to multiple Operating Units. The access mode is set to 'M' for this case.
For example, any statement on RA_CUSTOMER_TRX (synonym to which the security policy is attached) is dynamically modified to use the policy predicate.
A simple query by the user:
SELECT trx_number from ra_customer_trx
is modified at runtime if the responsibility can access multiple operating units to:
SELECT trx_number from ra_customer_trx
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 trx_number from ra_customer_trx
ORG_ID = sys_context('multi_org2','current_org_id')
« « Multi Org Structure                        Key Performance Indicators, How to keep your KPIs Smart? » »

Comments are closed.