Category: Oracle eBS

Setup Oracle eBS 2 Oracle BI SSO

I made several blogposts in the past. That’s why it could be useful to collect these posts into one document. This document covers all the steps needed to integrate Oracle eBS (R12) and Oracle BI (11g) with each other. SSO might be a little bit confusing. In this case it’s meant that you can log into Oracle BI via Oracle eBS without having to log in again and while maintaining context.

Integrating Oracle eBS Responsibility Profiles and Oracle BI 11g Application Roles

I have been blogging earlier about the integration between Oracle eBS R12 and Oracle BI 11g as well as inheriting the Oracle eBS Security in Oracle BI. Instead of making a Oracle BI Application Role for each Oracle eBS Responsibility you would like to use in Oracle BI, make use of an Oracle eBS Profile Option. You could define an Oracle eBS Profile Option (e.g. XXBI_SECURITY_PROFILE – Oracle BI Security Profile) and apply this to an Oracle eBS Responsibility. This makes it easier to maintain the Security Inheritance. When you add a new Responsibility to Oracle eBS, you do not have add this Responsibility to Oracle BI. Just make sure the Oracle eBS Responsibility gets the Oracle eBS Profile Option applied or define the Oracle BI Security Profile on Site Level as a default.

The Oracle BI Security Configuration for Oracle BI basically looks like this;

Oracle eBS - Oracle BI 11g Security Configuration

 

So instead of matching the Oracle eBS Responsibility to a Oracle BI Application Role, match an Oracle eBS Profile Option (assigned to an Oracle eBS Responsibility or on Site Level) to a Oracle BI Application Role.

You have to follow a few steps in Oracle eBS.

Lookup Type

Navigate to Application Developer – Application, Lookups, Common to add a lookup type with the different values for the Oracle BI Security Profile.

You can check the values via the following query:

select t.lookup_type
 , t.meaning
 , a.application_name
 , t.description
 from applsys.fnd_lookup_types_tl t
 , applsys.fnd_lookup_types b
 , applsys.fnd_application_tl a
 where b.lookup_type = t.lookup_type
 and b.security_group_id = t.security_group_id
 and b.view_application_id = t.view_application_id
 and a.application_id = b.application_id
 and b.lookup_type = '&XXBI_SECURITY_PROFILE'
 and t.language = '&LANGUAGE'
 and a.language = '&LANGUAGE'
;

select flv.lookup_code
, flv.meaning
, flv.description
from applsys.fnd_lookup_values flv
where lookup_type = '&XXBI_SECURITY_PROFILE'
and flv.language = '&LANGUAGE'
and trunc(sysdate) >= trunc(flv.start_date_active)
and trunc(sysdate) < nvl(trunc(flv.end_date_active), trunc(sysdate) +1)
;

Oracle BI Security Profile

Navigate to Application Developer – Profile to add a Oracle BI Security Profile

You can check the values via the following query:

select fpo.profile_option_name
, a.application_name
, fpotl.user_profile_option_name
, fpotl.description
from applsys.fnd_profile_options fpo
 , applsys.fnd_profile_options_tl fpotl
 , applsys.fnd_application_tl a
where fpotl.profile_option_name = fpo.profile_option_name
 and a.application_id = fpo.application_id
 and a.language = fpotl.language
 and fpo.profile_option_name = '&XXBI_SECURITY_PROFILE'
 and fpotl.language = '&LANGUAGE'
;

Add the following code as SQL Validation

******

SQL=”SELECT MEANING \”BI Security Profile\”, LOOKUP_CODE
into :visible_option_value,
:profile_option_value
from applsys.fnd_lookup_values fl
where fl.lookup_type = ‘Enter Lookup Type here’
and fl.language = ‘Enter Language here’
and trunc(sysdate) >= trunc(fl.start_date_active)
and trunc(sysdate) <nvl(trunc(fl.end_date_active), trunc(sysdate) + 1)”
COLUMN=”\”BI Security Profile\”(10)”

******

Assign Oracle BI Security Profile to Oracle eBS Responsibility

Navigate to System Administrator – Profile, System to assign the Oracle BI Security Profile to the Oracle eBS Responsibility

You can check the values via the following query:

Site Level (Default)

select fpov.profile_option_value bi_type_gebruiker_site_level
 from apps.fnd_profile_options_vl fpovl
 , applsys.fnd_profile_option_values fpov
 , applsys.fnd_profile_options fpo
 , applsys.fnd_profile_options_tl fpotl
 where fpov.profile_option_id = fpovl.profile_option_id
 and fpo.profile_option_id = fpov.profile_option_id
 and fpotl.profile_option_name = fpo.profile_option_name
 and fpov.level_id = 10001 -- Site
 and fpotl.user_profile_option_name = '&XXBI_SECURITY_PROFILE'
 and fpotl.language = '&LANGUAGE'
;

Responsibility Level (Specific)

select fr.responsibility_id
 , fr.responsibility_name
 , fpov.profile_option_value bi_type_gebruiker_resp_level
 from apps.fnd_responsibility_vl fr
 , applsys.fnd_profile_option_values fpov
 , applsys.fnd_profile_options fpo
 , applsys.fnd_profile_options_tl fpotl
 where fpov.level_value = fr.responsibility_id
 and fpo.profile_option_id = fpov.profile_option_id
 and fpotl.profile_option_name = fpo.profile_option_name
 and fpov.level_id = 10003 -- Responsibility
 and fpotl.user_profile_option_name = '&XXBI_SECURITY_PROFILE'
 and fpotl.language = '&LANGUAGE'

The remainder of the setup in the Oracle Enterprise Manager and the actual match via an Initialization Block is described here. The following query could be used to retrieve the Oracle eBS Profile Option and assign it to the ROLES-session variable

Oracle BI Initialization Block: GetApplicationRoles

select NVL ( rl.bi_security_profile_resp_level
 , sl.bi_security_profile_site_level ) bi_type_gebruiker
 from ( select fpov.profile_option_id
 , fpotl.language
 , fpov.profile_option_value
 bi_type_gebruiker_resp_level
 from applsys.fnd_responsibility fr
 , applsys.fnd_profile_option_values fpov
 , applsys.fnd_profile_options fpo
 , applsys.fnd_profile_options_tl fpotl
 where fpov.level_value = fr.responsibility_id
 and fpo.profile_option_id = fpov.profile_option_id
 and fpotl.profile_option_name = fpo.profile_option_name
 and fpov.level_id = 10003
 and fpo.profile_option_name = '&XXBI_SECURITY_PROFILE'
 and fpotl.language = '&LANGUAGE'
 and fr.responsibility_id = fnd_global.resp_id
 and fr.application_id = fnd_global.resp_appl_id
 ) rl
 , ( select fpov.profile_option_id
 , fpotl.language
 , fpov.profile_option_value
 bi_type_gebruiker_site_level
 from applsys.fnd_profile_option_values fpov
 , applsys.fnd_profile_options fpo
 , applsys.fnd_profile_options_tl fpotl
 where fpo.profile_option_id = fpov.profile_option_id
 and fpotl.profile_option_name = fpo.profile_option_name
 and fpov.level_id = 10001
 and fpo.profile_option_name = '&XXBI_SECURITY_PROFILE'
 and fpotl.language = '&LANGUAGE' ) sl
 where sl.language = rl.language(+)
 and sl.profile_option_id = rl.profile_option_id(+)

Feel free to comment.

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 &quot;ROLES&quot;
 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 &quot;ROLES&quot;
 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 ) )
 &quot;ROLES&quot;
 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)

Related articles

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.