Wednesday, February 19, 2014

R12 – MOAC Security


Multiple Organizations Access Control (MOAC) security give the possibility of querying or seeing transactions of one or more Operating Units (OU) without changing responsibility.
In 11i we were securing transactions by Operating Unit (OU) only and we would have to choose a corresponding responsibility to see the transactions:
  • OU1 – Receivables Manager
  • OU2 – Receivables Manager
  • OU3 – Receivables Manager
In R12 it is now possible to have a shared services responsibility like:
  • ALL – Receivables Manager
This new kind of security is based on Security Profiles which is a hierarchy of operating units.
So best practice in R12 is now to secure all responsibilities by Security Profile rather than by Operating Unit.
11i non-MOAC setup:

Site Level Receivables (Vision)
MO: Default Operating Unit
 
MO: Operating Unit Vision Operations Vision Operations
MO: Security Profile    
GL Set of Books Name   Vision Operations
So a global shared service responsibility was not possible in 11i so some companies had very long lists of responsibilities.
Also note in R11i site level setting takes precedence at lower levels so the responsibility level setting in this case is not needed – only if the OU had been different.
R12 MOAC setup:
  Site Level Receivables (Vision) Receivables (Global)
MO: Default Operating Unit   Vision Operations Vision Services
MO: Operating Unit      
MO: Security Profile Vision Global Vision Operations Vision Global
       
Assuming we have a hierarchy like this where OU and security profiles have same names:
> Vision Global
>> Vision US
>>> Vision Operations (OU)
>>> Vision Services (OU)
>> Vision Europe (OU)
We do not need to have a security profile for all branches in the hierarchy above but it is best practice to do so.
So in R12 GL Set of Books Name is gone and you leave MO: Operating Unit empty and leave it to the Security Profile.
Note: MO: Default Operating Unit is not required and can be left empty however I have had several bugs in R12.1.1 and R12.1.2 related to missing default operating unit in IEX so try leaving it empty first and if you get problems put a default value in this and try again.
So Oracle has created MOAC but forgot a few details here and there.
Mixed R11i/R12 MOAC setup:
Some modules in R12 does not yet fully support MOAC so a mixed setup may be needed.
Luckily Oracle changed the MO profile settings inheritance so the site level setting does NOT override at responsibility level as in R11i – well at least this setup worked in R12.1.1.
  Site Level Receivables (Vision) Receivables (Global)
MO: Default Operating Unit     Vision Operations
MO: Operating Unit Vision Operations    
MO: Security Profile Vision Global Vision Operations Vision Global
       
Using the setup above you will get a site level default OU of “Vision Operations” however at responsibility level the security profile takes precedence so Receivables (Global) will be able to see all OU’s within “Vision Global”. In 11i you would have been locked down to “Vision Operations”.

Technical

11i – View Based Security

11i used views to secure transactions in individual schemas:
APPS.AR_PAYMENT_SCHEDULES on AR.AR_PAYMENT_SCHEDULES_ALL
With where clause using CLIENT_INFO set from the responsibility context.
In PL/SQL programs you would set:
  • fnd_global.apps_initialize( var_user_id, var_resp_id, var_application_id);
and:
  • dbms_application_info.set_client_info(var_organization_id);

R12 – VPD Based Security

R12 uses the Virtual Private Database (VPD) technology for MOAC security.
Synonym APPS.AR_PAYMENT_SCHEDULES on AR.AR_PAYMENT_SCHEDULES_ALL
With a policy attached to synonym APPS.AR_PAYMENT_SCHEDULES.
In PL/SQL program you still need to set:
  • fnd_global.apps_initialize( var_user_id, var_resp_id, var_application_id);
and for single OU access:
  • mo_global.set_policy_context(‘S’,var_organization_id);
or for multi OU access:
  • mo_global.set_policy_context(‘M’,null);

Accessible Organisation Units

In 11i it was easy to see what access you had:
  • dbms_application_info.read_client_info(var_organization_id);
Which would return your current org_id however in R12 you need to do the following:
  • select organization_id
    from HR_ORGANIZATION_UNITS
    where MO_GLOBAL.CHECK_ACCESS(organization_id)=’Y’
    ;
I usually use the following script:
declare
  l_user_id number;
  l_responsibility_id number;
  l_application_id number;
  l_organization_id number;
begin
--  dbms_output.enable;
  select application_id,responsibility_id
    into l_application_id,l_responsibility_id
    from fnd_responsibility_tl
    where responsibility_name = 'General Ledger Super User';
  select user_id
    into l_user_id
    from fnd_user where user_name='ORACLE';
  select organization_id
    into l_organization_id
    from hr_all_organization_units
    where name='Vision Operations';
  fnd_global.apps_initialize(l_user_id,l_responsibility_id,l_application_id);  
  --mo_global.set_policy_context('S',l_organization_id);
  mo_global.set_policy_context('M',l_organization_id);
--  dbms_output.put_line('Done...');
end;

Concurrent Requests

Concurrent request can now be setup to run as in 11i (default) or as MOAC compliant programs.
Use the “System Administrator” OAF screen “Concurrent Program” to set Operating Unit Mode to Single (OU) or Multi (MOAC compliant):
image

1 comment: