SLS: Subledger Security

I am in the proces of implementing security for Oracle BI Apps 7.9.6 in a Oracle eBS R12 environment. One of the requirements is Subledger Security (User Guide, My Oracle Support).

As per the documentation; Subledger Security is an extension to Oracle Financials that enables the user to selectively partition data within a single install of Oracle Financials. Subledger Security provides a system where all business units can access their own financial information only.

In my clients case it makes it possible to secure parties, customers and suppliers. Certain customers are only visible for selected responsibilities within the same operating unit.

For implementing the security I am interested in the database-implementation of SLS. Check the following diagram:

For all the Subledger Security tables you have to refer to the IGI-scheme in Oracle eBS:

• IGI_SLS_ALLOCATIONS
• IGI_SLS_ALLOCATIONS_AUDIT
• IGI_SLS_CONSOLIDATE_GROUPS
• IGI_SLS_GROUPS
• IGI_SLS_GROUPS_AUDIT
• IGI_SLS_SECURE_TABLES
• IGI_SLS_SECURE_TABLES_AUDIT
• IGI_SLS_SECURITY_GROUP_ALLOC
• IGI_SLS_UPG_ITF

Whether Subledger Security is applied depends on two profiles;

• ‘Subledger Security : Security Group’
• ‘Subledger Security : SLS Responsibility’

A query to retrieve the values of these profiles could be;

SELECT r.responsibility_id ,
  r.responsibility_key ,
  r.responsibility_name ,
  nvl(sr.profile_option_value, 'N') sls_responsibility ,
  nvl(sg.profile_option_value, 'No SLS Responsibility') sls_security_group ,
  nvl(i.sls_group, 'No SLS Responsibility') sls_group
FROM
  (SELECT t.profile_option_name ,
    t.user_profile_option_name ,
    v.level_id ,
    v.profile_option_value ,
    r.responsibility_id ,
    r.responsibility_key ,
    r.responsibility_name
  FROM fnd_profile_options_tl t ,
    fnd_profile_options p ,
    fnd_profile_option_values v ,
    fnd_responsibility_vl r
  WHERE t.profile_option_name    = p.profile_option_name
  AND p.application_id           = v.application_id
  AND p.profile_option_id        = v.profile_option_id
  AND r.responsibility_id        = v.level_value
  AND v.level_id                 = 10003 -- Responsibility
  AND t.user_profile_option_name = 'Subledger Security : Security Group'
  AND t.language                 = 'US'
  ) sg ,
  (SELECT t.profile_option_name ,
    t.user_profile_option_name ,
    v.level_id ,
    v.profile_option_value ,
    r.responsibility_id ,
    r.responsibility_key ,
    r.responsibility_name
  FROM fnd_profile_options_tl t ,
    fnd_profile_options p ,
    fnd_profile_option_values v ,
    fnd_responsibility_vl r
  WHERE t.profile_option_name    = p.profile_option_name
  AND p.application_id           = v.application_id
  AND p.profile_option_id        = v.profile_option_id
  AND r.responsibility_id        = v.level_value
  AND v.level_id                 = 10003 -- Responsibility
  AND t.user_profile_option_name = 'Subledger Security : SLS Responsibility'
  AND t.language                 = 'US'
  ) sr ,
  igi_sls_groups i,
  fnd_responsibility_vl r
WHERE sr.responsibility_id = sg.responsibility_id (+)
AND sr.responsibility_id (+)  = r.responsibility_id
AND i.sls_group (+)        = sg.profile_option_value ;

Other queries to find out which data a responsibility is allowed to view:

The query below gives insight in the available SLS Security Groups, eg”.: ‘AR Security for Dept A’

SELECT sls_groups --'AR Security for Dept A' 
FROM   igi_sls_groups
WHERE  sls_group_type = 'S';

The query below gives insight in which tables are ‘SLS Security’-enabled within a certain SLS Security Group.

SELECT sls_group --'AR Security for Dept A'
,      table_name -- HZ_PARTIES 
FROM   igi_sls_enabled_alloc_v;

The query below gives insight in which ‘IG_SLS_#’-table the ‘SLS-Security’-details are stored. Depending on the configuration, multiple ‘IG_SLS_#’-tables  (IG_SLS_1, IG_SLS_2, IG_SLS_3, IG_SLS_4, IG_SLS_5, etc.) can exist.

SELECT owner -- AR
,      table_name -- HZ_PARTIES
,      sls_table_name -- IG_SLS_1 
FROM igi_sls_secure_tables;

The query below gives insight in the actual id’s which are secured by the ‘SLS-Security’-settings. The rowid of the source table is stored in the ‘IG_SLS_#’-table.

SELECT sls1.sls_rowid    
,      sls1.sls_sec_grp    
,      p1.party_id    
,      p1.party_number    
,      p1.party_name 
FROM   igi_sls_1 sls1 -- HZ_PARTIES    
,      hz_parties p1
WHERE  p1.ROWID = sls1.sls_rowid;

Using these queries, you should be able to select which id’s are available to a selected responsibility. Within the Oracle eBS R12 application, this is managed by policies (VPD – Virtual Private Database, RLS – Row Level Security)

Check the following query, to see how it’s setup;

select object_owner --'AR'
,      object_name  -- 'HZ_PARTIES'
,      policy_name  -- 'IGI_SLS_3_POL'
,      pf_owner     -- 'APPS'
,      function     --'IGI_SLS_1_FUN'
from   dba_policies
where  policy_name like 'IGI%'

With this information I am able to built ‘SLS Security’ into Oracle BI Applications.