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:
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.
