Setup Oracle BIA 7963 – Data Security

Customer wants to secure their data based on things like Ledger, Company, Operating Unit, etc. All users who login via Oracle eBS should inherit (based on the current responsibility) the same security settings in Oracle BI. The following is a possible solution.

Environment

  • Oracle eBS R12.1.1
  • Oracle BI EE 11.1.1.6.0
  • Oracle BIA 7.9.6.3
Setup

The setup consists of a few different parts

Data Security

The Data Security is based on different Roles and a Profile Option assigned to the responsibilities in Oracle eBS

Oracle eBS

  • Create ‘BI Type User’-profile option
  • Assign ‘BI Type User’-profile option to Responsibility
  • Assign Responsibility to User

Each Responsibility has either a specific ‘BI Type User’-profile option or a ‘BI Type User’-profile option on Site level. A view (xx_obia_user_groups_v) in Oracle eBS ‘holds’ the profile option information.

select fpov.level_value responsibility_id
 , fpov.level_value_application_id application_id
 , 'OBIA '
 || fpov.profile_option_value autorisatierol_code
 , fl.meaning autorisatierol
 from applsys.fnd_profile_option_values fpov
 , apps.fnd_profile_options_vl fpo
 , apps.fnd_lookups fl
 where fpo.profile_option_id = fpov.profile_option_id
 and fpo.application_id = fpov.application_id
 and fpov.profile_option_value = fl.lookup_code
 and fl.lookup_type = 'BI_TYPE_GEBRUIKER'
 and fpo.profile_option_name = 'XXBI_TYPE_GEBRUIKER'
 and fpov.level_id = 10003

Oracle BI

In Oracle BI, there is a Initialization Block which populates the; ROLES Session Variable

select ( select sector
 from apps.xx_obia_user_groups_v
 where responsibility_key = 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)'
 and responsibility_id = valueof ( nq_session.oltp_ebs_resp_id ) )
 || ';'
 || ( select autorisatierol_code "ROLES"
 from apps.xx_obia_user_groups_v
 where responsibility_key = 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)'
 and responsibility_id = valueof ( nq_session.oltp_ebs_resp_id ) )
 || ';'
 || ( select responsibility_key "ROLES"
 from apps.xx_obia_user_groups_v
 where responsibility_key = 'VALUEOF(NQ_SESSION.OLTP_EBS_RESP_KEY)'
 and responsibility_id = valueof ( nq_session.oltp_ebs_resp_id ) )
 "ROLES"
 from DUAL

Oracle Enterprise Manager (EM)

In the EM ‘all’ the different Application Roles, related to the Data Security are created.

There are a few different Application Roles;

  • Out-of the box
  • Data Security
  • eBS Profile

Out-of-the-Box

  • BIAdministrators
    • Administration privileges.
  • BIAuthors
    • Create, use or consume content.
  • BIConsumers
    • Use / consume content,
    • Every authenticated user.
  • BISystem
    • Component connections between products.

Data Security & eBS Profile

Oracle BI Administrator (Identity Manager)

In the Identity Manager, the Business Model Filters are applied to the Data Security Application Roles

The Business Model Filters are based on the Initialization Blocks. Some out-of-the-box, others custom.

This setup should be sufficient to apply Data Security to all queries, which query the Logical Tables with the Business Model Filters applied to it.

Note: Application roles data filters won’t apply for users with BI Administrator role.

By definition the BIAdministrator application role is granted the “oracle.bi.server.manageRepositories” permission, which is equivalent to the 10g “Administrator” user who also had unrestricted access. Hence, data filters won’t affect users with BIAdministrator Role. (source: Oracle Support)

Logout Link disappearing in Oracle BI 11g / Oracle eBS R12 integration

We have successfully integrated Oracle BI 11g with Oracle eBS R12. We log into Oracle BI 11g via Oracle eBS R12. One of the additional requirements is to have a Log out Link in Oracle BI 11g which takes us back to Oracle eBS R12. Therefor we have added the following to our instanceconfig.xml

<!--Adding Logout Link-->
<SchemaExtensions>
<Schema name="EBS-ICX" logonURL="http://<Oracle eBS R12 Server:port>/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE" logoffURL="http://<Oracle eBS R12 Server:port>/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE"/>
</SchemaExtensions>
<!--Adding Logout Link-->

This works like expected, but every time we do a restart of the BI service, the instanceconfig.xml get’s backed up and replaced a ‘new’ instanceconfig.xml, without the Log out Link.

This ‘problem’ is related to the following BUG; 14249563 on My Oracle Support. The good news is that it will be fixed in 11.1.1.7. The bad news is that at present the only workaround is to manually re-set the link.

Simulate Oracle eBS to Oracle BI login via SQL

There is an interesting (for me at least) topic on OTN about integrating Oracle eBS security into Oracle BI. It is a topic about getting Oracle eBS HR-Security to work in Oracle BI. Robin Moffat has a blogpost about;  ‘Validating EBS-BI authentication, without BI’. He refers to an My Oracle Support article (758392.1), which provides some (additional) troubleshooting details.

When I want to simulate Oracle eBS to Oracle BI login via SQL, I use some SQL-statements / scripts, to see what happens or at least should happen. If I need to know which HR-Orgs I could expect based on the Oracle eBS HR-Security I use the following scripts;

Query User – Responsibility — Rol

Check which responsibilities a user has within Oracle eBS.

select fu.user_id
 , frv.responsibility_id
 , fa.application_id
 , frv.responsibility_key
 , frv.responsibility_name
 from apps.fnd_responsibility_vl frv
 , applsys.fnd_application fa
 , applsys.fnd_request_groups frg
 , apps.fnd_user_resp_groups_all furga
 , applsys.fnd_user fu
 where fa.application_id = frv.application_id
 and TRUNC ( SYSDATE ) between fu.start_date
 and NVL ( fu.end_date
 , to_date ( '31-DEC-4712'
 , 'DD-MON-YYYY' ) )
 and fu.user_id = furga.user_id
 and TRUNC ( SYSDATE ) between furga.start_date
 and NVL ( furga.end_date
 , to_date ( '31-DEC-4712'
 , 'DD-MON-YYYY' ) )
 and furga.responsibility_id = frv.responsibility_id
 and frv.request_group_id = frg.request_group_id(+)
 and fu.user_name like :p_user_name
 and frv.responsibility_name like :p_responsibility_name
order by frv.responsibility_name

Excecute Apps Initialize

Run the Apps Initialize script to get the session in context of the user / responsibility combination of your choice, based on the previous query.

BEGIN
apps.fnd_global.apps_initialize(user_id, resposnsibility_id, resposnsibility_application_id, security_group_id);
END;

Validate the Context

Check the output of the previous step

select fnd_global.resp_id
 , fnd_global.resp_appl_id
 , fnd_global.security_group_id
 , fnd_global.resp_name
 , fnd_global.user_id
 , fnd_global.employee_id
 , fnd_global.user_name
 , ( select distinct responsibility_key
 from fnd_responsibility
 where responsibility_id = fnd_global.resp_id )
 responsibility_key
 from DUAL

Validate HR-Security

If all the above steps have completed like expected, you are able to check the query within the Oracle BI Initialization Block (IB) related to HR-Security. In my case it’s IB; ‘HR Organization’. This IB has the following query;

SELECT
 DISTINCT 'HR_ORG'
,TO_CHAR(SEC_DET.ORGANIZATION_ID)
FROM
(
SELECT
 'HR_ORG',
 ASG.ORGANIZATION_ID
FROM
 FND_USER_RESP_GROUPS URP
,FND_USER USR
,PER_SECURITY_PROFILES PSEC
,PER_PERSON_LIST PER
,PER_ALL_ASSIGNMENTS_F ASG
WHERE
 URP.START_DATE < TRUNC(SYSDATE)
AND (CASE WHEN URP.END_DATE IS NULL THEN TRUNC(SYSDATE) ELSE TO_DATE(URP.END_DATE) END) >= TRUNC(SYSDATE)
AND USR.USER_NAME = 'VALUEOF(NQ_SESSION.USER)'
AND USR.USER_ID = URP.USER_ID
AND TRUNC(SYSDATE)
 BETWEEN URP.START_DATE AND NVL(URP.END_DATE, HR_GENERAL.END_OF_TIME)
AND PSEC.SECURITY_PROFILE_ID = FND_PROFILE.VALUE_SPECIFIC('PER_SECURITY_PROFILE_ID', URP.USER_ID, URP.RESPONSIBILITY_ID, URP.RESPONSIBILITY_APPLICATION_ID)
AND PER.SECURITY_PROFILE_ID = PSEC.SECURITY_PROFILE_ID
AND PER.PERSON_ID = ASG.PERSON_ID
AND ASG.PERSON_ID = USR.EMPLOYEE_ID
AND TRUNC(SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE
AND URP.RESPONSIBILITY_ID = DECODE(FND_GLOBAL.RESP_ID,
 -1, URP.RESPONSIBILITY_ID,
 NULL, URP.RESPONSIBILITY_ID,
 FND_GLOBAL.RESP_ID)
UNION
SELECT DISTINCT 'HR_ORG',
 ORGANIZATION_ID
FROM PER_ALL_ASSIGNMENTS_F ASG,
 FND_USER USR
WHERE ASG.PERSON_ID = USR.EMPLOYEE_ID
AND USR.USER_NAME = 'VALUEOF(NQ_SESSION.USER)'
AND TRUNC(SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE
AND ASG.PRIMARY_FLAG = 'Y'
) SEC_DET

You should replace the ‘VALUEOF(NQ_SESSION.USER)’ part with either the user or fnd_global.user_name.

Now you are able to see whether the Initialization Block is working like expected and retrieves the same values as it does in Oracle eBS. If you can conform these steps and the the Security isn’t working, it’s probably because one of the related IB’s doesn’t retrieve (all) the necessary values.

This whole post assumes that the integration is setup correctly.

Good Luck.

Automatic login to Oracle BI via Oracle eBS

I have been blogging about the integration between Oracle eBS and Oracle BI. It’s possible to login to Oracle BI via Oracle eBS. In these situations it’s necessarily to have an account and a responsibility in Oracle eBS.

Some people make use of Oracle BI and not Oracle eBS. Normally the wouldn’t need an Oracle eBS account.

In cases of an integration between the two systems you have to login to Oracle eBS, select a responsibility and select a link to Oracle BI.

For some people these are to many steps to get to Oracle BI.

It’s possible to set a default start page when you login to Oracle eBS. You could eg. choose ‘Oracle BI Answers’ as your default start page.

This could be set per user via the Preferences of the user who has logged in.

Here you could set the start page

Be careful setting this option, because once you set it, you wont be able to return to Oracle eBS. In such a case when a user wants to get rid of this startpage use the Oracle eBS Profile Option; ‘Applications Start page’. This option could be set for a specific user or a responsibility via the ‘System Administrator’-responsibility.

Via ‘Profile’, ‘System’, you can find the specific ‘System Profile Value’

For the user; ‘DBAKBOORD’ the value of this profile is;

This value can be retrieved from the database via the following query;


select fpov.level_value user_id

, fu.user_name

, fpov.profile_option_value

from applsys.fnd_profile_option_values fpov

, apps.fnd_profile_options_vl fpo

, applsys.fnd_user fu

where fpo.profile_option_id = fpov.profile_option_id

and fpo.application_id = fpov.application_id

and fu.user_id = fpov.level_value

and fpo.profile_option_name = 'APPLICATIONS_START_PAGE'

and fpov.level_id = 10004

The output of this query is the following;

Using the Oracle eBS Profile Option; ‘Applications Start page’ could also be an easy solution to set a startpage for a whole group of user. After setting this option they all will login to Oracle BI Answers directly without selecting the menuoption in Oracle eBS first.

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:

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.