Title: KBACE Technologies
1Introduction to Multi-Org-Access-Control (MOAC)
R12
2Concepts of MOAC
- MOAC allows users of a single responsibility to
perform task in multiple operating units without
having to switch responsibilities.
3Shared Service Center?
Shared
Supplied to multiple business units
Service of Transaction Management
Service
Center
From a centralized Operations
4Goal Shift from Local to Global Systems
- Consolidate IT business operations
- Implement Self-Service Operations
- Drive down transaction costs
511i
Access by Changing Responsibility
6R12
Access without Changing Responsibility
7Key to Shared Service Center efficiency
- Separate data and function security
- Access many operating units to one responsibility
- Operating unit selected or derived for
transactions
8Demo of MOAC w.r.t Profile Options
9Access Control Architecture
- Multiple Organization Architecture was first
introduced in Release 10.6, for data security by
Operating Unit. - In Release 10.7, a column, ORG_ID was added and
tables renamed as _ALL - (VPD)Virtual Private Database feature - attaches
predicates for the security policies to every SQL
statement against the database objects where
policies are applied. MOAC Utilized this feature
for access control in R12
10Access Control Architecture 11i Vs R12
11i
R12
11Some useful Objects / APIs/Tables
- New Tables
- MO_GLOB_ORG_ACCESS_TMP - Table
- FND_MO_PRODUCT_INIT - Table
- Security Profile
- MO Security Profile
- MO Default Operating Unit
- Multi-Org APIs
- MO_GLOBAL.Check_Access
- MO_GLOBAL.get_ou_name
- MO_GLOBAL.check_valid_org
- Policy Context API
- MO_GLOBAL.set_policy_context Procedure
- MO_GLOBAL.get_current_org_id Function
- MO_GLOBAL.get_access_mode Function
- Org Defaulting APIs
- MO_UTILS.Get_Default_OU Procedure
- MO_UTILS.get_default_org_id Function
12New APIs
Public API Name Feature Area
mo_global.init Initialize multiple organizations context.
mo_global.jtt_init Initialize multiple organizations for JTT based application.
mo_global.is_multi_org_enabled Check if the Multiple Organizations instance is enabled.
mo_global.check_access Check if the operating unit is accessible.
mo_global.get_ou_name Get the operating unit name.
mo_global.check_valid_org Check if the organization is valid.
mo_global.set_policy_context Set the application policy context.
mo_global.get_current_org_id Get the current organization ID in the application context.
mo_global.get_access_mode Get the application context mode.
13Public API Name Feature Area
mo_global.get_ou_count Get the operating unit count on the access control list.
mo_global.get_valid_org Get the current default/valid organization.
mo_global.validate_orgid_pub_api Get the default organization and check if the organization ID is valid. Used by public APIs only.
mo_global.is_mo_init_done Check if multiple organizations is initialized.
mo_utils.get_ledger_name Returns the ledger name.
mo_utils.get_ledger_info Returns information about the ledger.
mo_utils.get_default_ou Gets the default operating unit from MO Default Operating Unit profile or from current organization.
mo_utils.get_default_org_id Returns the organization ID of the default operating unit.
mo_utils.check_org_in_sp Checks if the specified organization is present in the security profile.
mo_utils.check_ledger_in_sp Checks if all operating units of a ledger is included in the security profile.
mo_utils.check_org_name Returns the operating unit name for an organization ID.
mo_utils.get_orgid_fr_ledger Returns the operating unit ID and the number of operating units in the given ledger.
14Example 1 The view definition of single
organization view RA_BATCHES is shown below in
the example.
CREATE OR REPLACE VIEW RA_BATCHES AS SELECT "BATCH_ID", LAST_UPDATE_DATE", "LAST_UPDATED_BY", "CREATION_DATE", ... "ORG_ID" , "PURGED_CHILDREN_FLAG" , "ISSUE_DATE" , "MATURITY_DATE" , "SPECIAL_INSTRUCTIONS" , "BATCH_PROCESS_STATUS" , "SELECTION_CRITERIA_ID" FROM RA_BATCHES_ALL WHERE NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
- This single organization view RA_BATCHES must be
replaced by a synonym as given below
CREATE SYNONYM RA_BATCHES FOR AR.RA_BATCHES_ALL
- The summary of changes that must be done for
single organization views joined to single _ALL
table are given below - Drop the single organization view
- Create a synonym with the same name as the
obsolete single organization view - Attach policy function to the synonym
15Attach Security Policy to your Tables
BEGIN FND_ACCESS_CONTROL_UTIL.Add_Policy
( p_object_schema gt '1', --
Apps user name p_object_name gt
'FINANCIALS_SYSTEM_PARAMETERS',
p_policy_name gt 'ORG_SEC,
p_function_schema gt '1', -- Apps user name
p_policy_function gt
'MO_GLOBAL.ORG_SECURITY',
p_statement_types gt 'SELECT, INSERT, UPDATE,
DELETE', p_update_check gt TRUE,
p_enable gt TRUE,
p_static_policy gt FALSE) END
16MO_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
17Register Multi-Org Access Enabled in MO table
- Product teams must register their product in the
Multi-Org table FND_MO_PRODUCT_INIT to indicate
that Multi-Org Access Control is enabled - To enable access FND_MO_PRODUCT_INIT_PKG.registe
r_application('XXXCHR', SEED,Y) - To delete your application entry
FND_MO_PRODUCT_INIT_PKG.remove_application('XXXCH
R')
Column Name Size Type Rqd Value
APPLICATION_SHORT_NAME 80 VARCHAR2 Yes XXXCHR
PRODUCT_DEPENDENCY 80 VARCHAR2 Yes MO
18Forms Enhancements
- Multi-Org Initialization
- include the following code as given below in the
PRE-FORM trigger - BEGIN APP_STANDARD.EVENT(PRE-FORM)
MO_GLOBAL.init (XXXCHR) - END
19Multi-Org Initialization
- IMPORTANT AOL initialization (fnd_global.apps_ini
tialize()) is executed by the app_standard.event()
call in the Pre-Form trigger. Multi-Org
initialization should be executed after this
call. - Critical setup
- MO Security Profile
- MO Operating Unit
- MO Default Operating Unit
20Exercise
Step1 1) select from MO_GLOB_ORG_ACCESS_TMP
2) select hr.name operating_unit,
hr.organization_id org_id from
hr_operating_units hr where
mo_global.check_access(hr.organization_id)'Y
3) select from ar_system_parameters
21Step2 1) begin FND_GLOBAL.APPS_INITIALIZE(user_
id gt1013421 ,resp_id
gt 20678,resp_appl_id gt222 ,
security_group_id gt0) end 2) select
from MO_GLOB_ORG_ACCESS_TMP 3) select hr.name
operating_unit, hr.organization_id org_id
from hr_operating_units hr where
mo_global.check_access(hr.organization_id)'Y' 4)
select hr.name operating_unit,
hr.organization_id org_id
,mo_global.check_access(hr.organization_id)
from hr_operating_units hr 5) select from
ar_system_parameters
22Step3 1) FND_GLOBAL.APPS_INITIALIZE(user_id
gt1013421 ,resp_id gt
20678,resp_appl_id gt222 ,
security_group_id gt0)
mo_global.init('AR') --SQLAP for AP end
check table FND_MO_PRODUCT_INIT for init
parameter 2) select from MO_GLOB_ORG_ACCESS_T
MP 3) select hr.name operating_unit,hr.organizat
ion_id org_id from hr_operating_units hr
where mo_global.check_access(hr.organization_id)
'Y 4) select from ar_system_parameters