Introduction
11i Concept
In ORACLE database, data is organized to contain multiple Operating Units. The data is classified or extracted by having a column named ORG_ID in the ORG based tables. And in the 11i systems we have the extraction criteria as “WHERE ORG_ID = <P_org_id>”, this means we will pass the ORG_ID as a parameter or alternatively pick the ORG_ID from profile fnd_profile.value ('ORG_ID') and pull the records. So here there is a restriction of viewing one org records at a time. In short data was restricted to a single operating unit view which partition the base table data on the OU setting and which is controlled by the profile.
11i concept work on
- The Base tables , with _ALL which contains the data for all OUs
- Each org_based tables contains a column called ORG_ID indicating the Operating Unit to which the data belongs to
- 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
- SQL plus can set this usingExecute dbms_application_info.set_client_info(‘<ORG_ID>’);
R12 Concept
With the R12 release ORACLE came up with a new concept called Multiple Organization Access Control (MOAC), in this concept ORACLE allows more flexibility in accessing the data across multiple OUs without having the ORG_ID parameter. This is a generic concept that can be applied to various objects such as Forms, Reports, and OAF etc. This means you can access the data across different OUs from a single responsibility.
Advantages
- Users can access and perform multiple tasks across different OUs without changing OUs
- This can improve on the efficiency of transactions for companies that have centralized business functions
- Speed up the whole transaction processing
- Reduce the overhead of setup, maintenance and better control.
- Better decision making on Suppliers, Sites across different OUs and Customer Records.
- Allows a global consolidated view across different OUs and Legal Entities.
MOAC - How this is done?
Create Security Profiles (using form function ‘Define Global Security Profile’)
Security Profiles are the driving key for MOAC. Each security profile can be setup to contain multiple OUs.
- Enter a unique name for the security profile.
- To restrict access by discrete list of organizations, select ‘Secure organizations by organization hierarchy and/or organization list for the Security Type’.
- Check the Exclude Business Group check box to remove the business group in the list of organizations.
- Use the Classification field to limit the list of values (LOV) in the Organization Name field. For example, if you select the classification to Operating Unit, only operating units will display in the LOV.
- In the organization name field, select the Operating Unit for which you want access.
- Repeat until you have included all organizations to which you need access.
Here we can see that the Security Profile XXMOAC Test is setup and it contains two operating units named Vision Operations and Vision USD
Run the concurrent program “Security List Maintenance Program” from the standard request submission form.
The “Security List Maintenance Program” can be run for a single named security profile to prevent impact to other security profiles.
Assign appropriate security to the profile option “MO: Security Profile” for your users and responsibilities
Navigate to the “System Administrator” responsibility > System Profile Options
Assign the security profiles to MO: Security Profile for your responsibilities and/or users.
MO: Operating Unit shall be blank; otherwise it’ll work just like 11i works
Assign a value for profile option “MO: Default Operating Unit” (Optional)
Navigate to System Administrator Responsibility > System Profile Options
Assign a default operating unit to “MO: Default Operating Unit” profile option for your responsibilities and/or user.
Assign MO: Operating Unit (Mandatory for only Single Org or if MO: Security Profile is not defined)
Note that if you already set the security profile as per the above screen-shot then skip this step. This is applicable when you have only one OU data.
Navigate to System Administrator Responsibility > System Profile Options
Assign the Operating unit to MO: Operating Unit profile option for your responsibility or user.
MOAC is initialized when you open a Form, OA page or a Report. The first MOAC call checks if the profile “MO: Security Profile” has a value. If yes, then the list of operations units to which access is allowed is fetched and the list of values (LOV) is populated. Then default value of the LOV is set to the operating unit specified in “MO: Default Operating Unit”. This is how MOAC works in Oracle Release 12 when the value of “MO: Security Profile” is set.
When the profile “MO: Security Profile” does not have a value MOAC switches to the 11i single organization mode. As in 11i, the profile “MO: Operating Unit” is checked and the operating unit is initialized to the one defined in it.
Developer Guidelines and Approach
To increase the flexibility and performance in a multiple organizations environment and provide the same level of data security, the DBMS Virtual Private Database (VPD) feature replaces the CLIENT_INFO function.
The Virtual Private Database (VPD)
This is a feature that 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.
View Changes
- Drop the single organization view
- Create a synonym with the same name as the obsolete single organization view
- Attach a policy function to the synonym
Table Changes
- Create the Custom Base tables as DEMO_TABLE_ALL
- Add the ORG_ID column if it does not exist
- Create SYNONYM on the base table as DEMO_TABLE
- Apply Security Policy to DEMO_TABLE
Code Changes
- Remove the Views and Base Tables from the code
- Use SYNONYMS
- Remove the Org_id parameter if any.
MOAC Setup for Concurrent Program
- System Administrator (responsibility):
- System Administration: Concurrent: Programs
- Query by your program name
- Click on the edit icon
- Click on the Requests tab
- System Administrator (responsibility):
- Multiple – you can’t select the operating unit that you want to run for that report – it’ll run for all OU depending on the Security Profile attached to the responsibility
- Single – you have to enter which operating unit you want to run this report against – it’ll be a required field to be enter to run the reportThis is the screen-print when the operating mode is set to SINGLE and the same will be disabled when we set it as MULIPLE and this will fetch the data across all the OUs attached to the Security Profile that we explained above.
API Handling
- Do not use the multiple organizations temporary table directly in the SQL query.
- Rewrite the SQL joins with two or more views to use just one secured synonym depending on the driving table for the query and replace the remaining views by _ALL tables.
- Add the ORG_ID to the WHERE clause of the SQL to avoid Cartesian joins for tables that include ORG_ID the composite or driving key.
- Use MO_GLOBAL.Set_Policy_Context.
This API has 2 parameters –
- Operating unit
- Context
Context has 2 values
- M
- S
When policy context is set to ‘M’, data from all accessible Operating Units will be returned.
When policy context is set to ‘S’, then only data from the specified Org_Id will be returned.
- Products must call the MO_GLOBAL.init() API to execute the multiple organizations initialization.
Workflow Handling
With multiple organizations access control, you must set the current organization ID and not the CLIENT_INFO org context. You must derive the current organization ID from item keys. Do not rely on MO: Security Profile, MO: Default Operating Unit and MO: Operating Unit profile options when setting the organization context because the operating unit must be validated before initiating the workflow.
MOAC Defaulting Rule
If the profile option “MO: Security Profile” is not set, then “MO:Operating Unit” value is used as the default Operating Unit even if “MO: Default Operating Unit” profile is set to a different value.
If the profile option “MO: Security Profile” is set and gives access to one Operating Unit, the default Operating Unit will return this value even if “MO: Default Operating Unit” is set to a different value.
If the profile option “MO: Security Profile” is set and gives access to multiple Operating Units, then the profile value “MO: Default Operating Unit” if set is validated against the list of Operating Units in “MO: Security Profile”. If the Operating Unit is included in the security profile then it is returned as the default value. Otherwise there is no Operating Unit default. Moreover, if the Profile Option “MO: Default Operating Unit” is not set, then there is no default Operating Unit.
Pre R12 Multi-Org Features
Base data tables exist in the product schema with a naming convention of %_ALL. The data in this table is striped by ORG_ID (Operating Unit).
A view in the APPS schema provides the Multi-Org filtering based on the following statement in the where clause. SUBSTRB (USERENV (’CLIENT_INFO’), 1, 10)
R12 Multi-Org Features
Base data tables exist in the product schema with a naming convention of %_ALL. The data in this table is striped by ORG_ID (Operating Unit).
A synonym in the APPS schema provides the Multi-Org filtering based the Virtual Private Database feature of the Oracle 10G DB Server.
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 sub ledger 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.
SQL Session Initialization
Pre-R12
You could set your SQL session context for multi-org with the following:
BEGIN
dbms_application_info.set_client_info(101);
END;
In this example 101 is the ORG_ID for the Operating Unit or you could have used FND_GLOBAL.APPS_INITIALIZE to set your context.
R12
You can set your SQL session context for a single OU with the following:
BEGINExecute mo_global.set_policy_context(’S',101);END;
BEGINExecute mo_global.set_policy_context(’S',101);END;
‘S’ -> means Single Org Context
101 -> is the ORG_ID you want set
Example of Setting of Single Org Context
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:
,&responsibility_id
,&responsibility_application_id
);
Execute mo_global.init('&product_short_name');
You can set your SQL session context for multiple OUs with the following:
Execute mo_global.set_org_access(NULL,111,‘ONT’);
END;
111 -> is the Security Profile you want to use
‘ONT’ -> is the application short name associated with the responsibility you will be using to find the security profiles:
The following SQL will dump out the Security Profiles and Operating Unit Names assigned to them:
SELECT psp.SECURITY_PROFILE_NAME,
psp.SECURITY_PROFILE_ID,
hou.NAME,
hou.ORGANIZATION_ID
FROM PER_SECURITY_PROFILES psp,
PER_SECURITY_ORGANIZATIONS pso,
HR_OPERATING_UNITS hou
WHERE pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID
AND pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;
No comments:
Post a Comment