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.
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;
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.
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
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.
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.
- Oracle eBS R12.1.1
- Oracle BI EE 126.96.36.199.0
- Oracle BIA 188.8.131.52
The setup consists of a few different parts
- Integration Oracle eBS and Oracle BI
- Data Security
The Data Security is based on different Roles and a Profile Option assigned to the responsibilities in 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
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
- Administration privileges.
- Create, use or consume content.
- Use / consume content,
- Every authenticated user.
- 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)
- Integrating Oracle eBS R12 and Oracle BI 11g (obibb.wordpress.com)
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 184.108.40.206. The bad news is that at present the only workaround is to manually re-set the link.
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
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.
I have made a blogpost in the past about integrating Oracle eBS R12 and Oracle BI 10g. In the course of an upgrade of Oracle BI Applications (OBIA) to OBIA 220.127.116.11, I came to the subject of integrating Oracle eBS R12 and Oracle BI 11g. Of course you should start with the documentation. Next to that, Oracle provides a note on Oracle Support (ID 1343143.1). A lot of integration steps are equal to the 10g version.
There are two sides (Oracle eBS & Oracle BI) where you need to make some preparations.
You need to define the link from to Oracle eBS to Oracle BI. This functionality hasn’t changed and I have described that process here. Combined with the ‘FND: Oracle Business Intelligence Suite EE base URL’-profile option in Oracle eBS, you now are ready to navigate from Oracle eBS to Oracle BI.
Now the Oracle eBS side is ready, you’ll have to prepare Oracle BI for accepting login requests from Oracle eBS. This parts differs a little from 10g. The changes in the Repository are still the same and consist of validation of the ICX-cookie in the Oracle eBS Connection Pool and the Authentication via Session variables. You can choose to either authenticate via the GROUP- system variable or directly via the new 11g ROLES-system variable.
After that you need to change the Oracle BI configuration;
- authenticationschemas.xml (ORACLE_HOME/bifoundation/web/display)
- instanceconfig.xml (ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/coreapplication_obips1)
Note: Don’t get mislead by the following sentence; ‘<!–This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control–>’. You must adjust these settings directly in the instanceconfig.xml itself.
This should (all) be sufficient to log into Oracle BI via a selected responsibility in Oracle.
In a following post I will cover the subject of applying Data Security in Oracle BI, based on the Oracle eBS Responsibility.
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.