SQL Queries and multi-org Architecture in Release 12

In this Document

Abstract

History

Details

Previous releases

Release 12

multi-org Session Context

Backwards compatibility

Applies To:Oracle Project foundation-version 12.0.0 to 12.1.3 [Release 12.0 to 12.1]
information in this document applies to any platform.
***checked for relevance on 02-dec-2013***


ABSTRACT

This paper would review changes in the multi-org architecture in Release all designed to enable the multi-org Access Control (MOAC) feature. It would discuss how this impacts the users ability to access data from Multi-org enabled objects via Sql*plus or other CLI ENT query tools outside of the applications.

History

Author: Andrew Lumpe
Create Date 11-oct-2007
Update Date 11-oct-2007
Expire Date

DETAILS

In Release, the architecture of multi-org and the which data is partitioned by operating unit have changed signif icantly. As a result the ways in which data is stored and accessed has changed.

Previous releases

In previous releases, data is restricted to a single operating unit US ing views which striped base table data based on the current operating unit setting.

• Base tables (generally named with ' _all ', e.g. My_table_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 is then restricted by using restricted views (e.g. my_table) which would only return rows which corresponded to The current operating unit ' s organization ID.
• the current operating unit is stored in the first ten characters of the database application Context variable client_i NFO.
• when logging into the applications, the Client_info value is set to the appropriate operating unit organization ID FO R the session based on the Profiles option setting for "Mo:operating Unit".
• in Sql*plus, Client_info could is set to point to a particular operating unit using

Execute dbms_application_info.set_client_info (' <ORG_ID> ');

Release 12

In release the more flexible architecture have been put in place to the support multi-org Access Control (MOAC). This architecture allows users to define security profiles so, users may access data for more than one operating unit Within a single responsibility.

To accomplish this

• multi-org views has been removed, and replaced with synonyms. For example, My_table would no longer is a view defined on My_table_all, but rather a synonym which points to my_table_all
• The data restriction is accomplished by assigning a virtual private database (VPD) policy to the synonym. This policy allows the system to dynamically generate restricting conditions when queries is run against the synonym.

For Example:In release the APPS schema, Pa_projects and Pa_projects_all is both synonyms which point to the table Pa.pa_project S_all. However, the view pa_projects_all is unrestricted, whereas, Pa_projects would only display data for the user's current Oper ating unit (s) because of the VPD policy that have been assigned to it.

Data relating to defined VPD policies are viewable in the data dictionary view dba_policies. These policies associate a function with an object, and if the object is accessed, this function can return additional R Estrictions on the object to restrict the data returned. The particular policy used to implement multi-org in release are:

• Policy_name:org_sec
• Policy_group:sys_default
• Package:mo_global
• Function:org_security

The function (mo_global.org_security) is called with the following parameters:

• Obj_schema-the object schema, in the case APPS
• Obj_name–the object name (e.g., my_table)

The function then returns additional WHERE clause conditions to restrict the data accessible from the object. The structure of this function would dynamically generate conditions which would either:

1. Restrict the data to a single operating unit if the access mode was single
2. Restrict the data to multiple operating units if the access mode is multiple
3. Restrict the data to eliminate only seed data rows if the access mode was all
4. Restrict the data to no return any rows if the access mode is None

The conditions returned in each case is as follows:

Single OU Access

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 has the to is set to the current operating unit as described is Low.


Multiple OU Access

EXISTS (SELECT 1
From Mo_glob_org_access_tmp OA
WHERE oa.organization_id = org_id)

The user would be able to access data for any org_id which have been populated into mo_glob_org_access_tmp. When a session was initialized in the applications, values would be populated to mo_glob_org_access_tmp for each of the OP Erating units The user has access to based on their "mo:security profiles" setting.


All OU Access

org_id <>-3113

Seed template records, which is used to the create new seed data when a new operating unit is created. is created with an org_id of–3113. So's this mode, the only these template records, which does not correspond to any actual operating unit, would be filtered out.


No OU Access

1 = 2

The condition is never satisfied. No data is 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 infor Mation for a user ' s session. The utility Sys_context (<NAMESPACE>, <attribute>) is used to retrieve this data. The key context items are:

namespace	attribute	value
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 a single

For Example: The following example shows how to could set the access mode or determine the current setting:

dbms_session.set_context (' multi_org ', ' access_mode ', ' S ');

MyVar: = Sys_context (' multi_org ', ' access_mode ');
Dbms_output.put_line (' Access Mode: ' | | MyVar);

However, it's generally preferable to use the following wrapper functions from Mo_global which would call the appropriate Utilities to maintain the various elements of the multi-org context:

Procedure:set_policy_context(P_access_mode varchar2, p_org_id number)
This procedure would set the access mode, and when applicable the current operating unit context.

procedure:set_org_access(P_org_id_char varchar2, P_sp_id_char varchar2, P_appl_short_name varchar2)
This procedure determines if the application specified have multi-org access control enabled, by querying Fnd_mo_produc T_init for the application short name. If This was enabled, and a security profile was specified (P_sp_id_char), then all orgs the user have access to would be Popul Ated in Mo_glob_org_access_tmp. If there is more than one such org, the access method is set to "multiple". Otherwise if no security profile ID is specified, it would use the value of p_org_id to set the current operating unit Valu E and set the access mode to ' single '.

Procedure:init (P_appl_short_name varchar2)
The procedure used by the applications when starting a new session. Based on the Profiles options "Mo:operating Unit" (org_id) and "Mo:security profile" (xla_mo_security_profile_level), thi s procedure calls set_org_access to establish the multi-org context for the session. To withing SQL, the profile option context should has been initialized for the session.

function:get_current_org_id
Returns The current operating unit setting. This should being null if the access mode is not ' S '

Function:get_access_mode
Returns The current access mode value.

For Example:

1) When logging-a SQL session to set the Org context as it would is for a particular user in a particular Responsibil ity:

a) If you know the security_profile_id for this responsibility and user, you could call:

Execute mo_global.set_org_access (null, <sp_id>, ' PA ');

Security profiles was stored in Per_security_profiles, and the final parameter was the application short name of th e application associatedwith the responsibility your would be using.

b) If you don't know the security profile or operating unit profile option settings for your user, responsibility and Application, you could use code similar to the following-get this information:

Declare

L_USER_ID Fnd_user.user_id%type;
L_RESP_ID Fnd_responsibility.responsibility_id%type;
L_APPL_ID Fnd_application.application_id%type;
L_appl_short_name Fnd_application_vl.application_short_name%type;
L_ou_value Fnd_profile_option_values.profile_option_value%type;
L_sp_value Fnd_profile_option_values.profile_option_value%type;

Begin

Select user_id into l_user_id
From Fnd_user
where user_name = Upper (' &user_name ');

Select responsibility_id into l_resp_id
From FND_RESPONSIBILITY_VL
where Responsibility_name = (' &resp_name ');

Select application_id, application_short_name into l_appl_id, l_appl_short_name
From FND_APPLICATION_VL
where application_short_name = Upper (' &appl_short_name ');

L_ou_value: = Fnd_profile.value_specific (
' org_id ', l_user_id, l_resp_id, l_appl_id);
L_sp_value: = Fnd_profile.value_specific (
' Xla_mo_security_profile_level ', l_user_id, l_resp_id, l_appl_id);

Dbms_output.put_line (' mo:operating Unit: ' | | L_ou_value);
Dbms_output.put_line (' mo:security profile: ' | | L_sp_value);

If L_sp_value is null and l_ou_value are null then
Dbms_output.put_line (' No operating unit or security profile information
Found ');
Else
Mo_global.set_org_access (L_ou_value, L_sp_value, l_appl_short_name);
End If;

Exception when others then
Dbms_output.put_line (' Error: ' | | SQLERRM);
End
/

2) To set the operating unit context to a single operating unit, you could simply use:

Execute Mo_global.set_policy_context (' S ',<org_id>);

Backwards compatibility

When running queries on multi-org objects in SQL, you can still use the old Client_info settings to gather data and run Qu Eries against multi-org objects if the profile option:

Mo:set client_info for Debugging (Fnd_mo_init_ci_debug)

is set to "Yes".

When this profile option was set to "Yes" and the global access mode setting was null (as it would be in a sql*plus or other Client session unless specifically set), the VPD function mo_global.org_security would return the following as the Additio NAL WHERE clause condition for the object:

org_id = SUBSTRB (Userenv (' Client_info '), 1,10)

This would limit the data returned by the object to the current value set in Client_info. This value is set as described on the beginning of this article under "Previous releases".

SQL Queries and multi-org Architecture in Release 12