OBIEE security integration with Oracle E-Business Suite

 

Architecture
This article is to discuss the integration of the Oracle E-Business Suite with Oracle BI EE in an Oracle BI Applications environment.  BI Applications for EBS integration solution consists of an application integration from a user interface perspective: single sign on, integrated user interface, action links (Action link screens enable to drill back into the transactional application screen from an Oracle BI request or dashboard) from a data source perspective. The following section discusses how to integrate OBIEE user interface with EBS, leveraging the EBS security framework.

Assumptions
It is assumed the following pre-requisites are fulfilled:
1. Oracle E-Business Suite (11.x) is installed
2. Daily Business Intelligence is installed and is configured
3. All necessary EBS patches (5556799, 5473858) have been applied by the EBS application DBA
3. Client Browser accepts cookies
4. Oracle Business Intelligence Platform (10.1.3.x) is installed in the same network domain

Integration of the User Interface
A user will be able to access the OBIEE environment if this user is administered as an EBS user with the right responsibilities.
The integration of Oracle e-Business Suite (EBS) to Oracle Business Intelligence (OBIEE) consists of the following steps:
1. Specification of OBIEE base URL in EBS
2. Specify the authentication part of the integration in the instanceconfig.xml
3. Modify the connection pools in the repository
4. Specify Static Repository variables

OBIEE URL in EBS
The EBS administration screen for managing profile options (System Administrator > Profile > System) allows to specify the hostname and location of the web application to integrate with.  The URL will be interpreted by the OBIEE Presentation Server. Set the value of the following Profile Option Name,

"FND: Oracle Business Intelligence Suite EE base URL" = http://[hostname.domain_name]:[port_number] (No slash is required at the end of this URL)

Authentication Integration

The authentication integration for OBIEE is via http cookies combined with a URL. The cookie is also used for the action link integration (drill back to EBS screen from an OBIEE request). This integration is specified in the instanceconfig.xml. The integration and authentication steps are:
1. Login into EBS
2. When clicking on an OBIEE link in the EBS client browser, EBS builds up an URL starting with the OBIEE base URL specified in the previous step. This base URL is extended with:
[base_url]/analytics/saw.dll?[module_invoked]&acf=
Here [module_invoked] is for example Dashboard or Answers and [acf_id] is a 10 digit number generated by EBS. This number is used to retrieve other authentication and authorization information from the EBS session by the Oracle BI Server
3. EBS sends a cookie to the browser, most likely the cookie file name is equal to the ICX base domain.
Tip: Using Mozilla Firefox browser allows you to find the cookie named value pair you are looking for.
This cookie named value pair which indicates the user ICX session id. The name of this value pair is very important and needs to be captured for the instanceconfig.xml, i.e. the configuration file of the Presentation Server. The value is used for the actual authentication in OBIEE.
4. By the instanceconfig.xml for OBIEE, the OBIEE presentation server is programmed for external authentication (ExternalLogon enabled=true in the [Auth] tag area).
In order to complete the authentication the OBIEE session will try to resolve two parameters:
- NQ_SESSION.ICX_SESSION_COOKIE
- NQ_SESSION.ACF
The first is resolved from a cookie with the name specified in the instanceconfig.xml configuration file by the nameInSource attribute of the [Param] tag. The attribute value is the name of the cookie. The value of the cookie is the ICX session cookie ID stored in the OBIEE Server parameter NQ_SESSION.ICX_SESSION_COOKIE and is passed to the OBIEE Server. The second parameter is resolved from the URL by [acf_id].
The correct specification of the instanceconfig.xml [Auth] tag is as follows (after the ParamList tag):
Param name="NQ_SESSION.ICX_SESSION_COOKIE"
source="cookie"
nameInSource="[cookie name]"
and
Param name="NQ_SESSION.ACF"
source="url"
nameInSource="acf"
After the changes have been applied, the OBIEE Presentation Server needs to be rebooted.

Repository Configurations
Two authentication initialization blocks populating repository session variables are most important for the integration of EBS with OBIEE. Assume the name for these initialization blocks are:
1. EBS Security Context (old version- FndGetSecContext )
2. FndGetResp (Authorization)

The first initialization block, “EBS Security Context”, populates the following variables:
RESP_ID, RESP_APPL_ID, RESP_APPL_ID, RESP_APPL_ID, USER_ID, EMPLOYEE_ID, USER
using a database query:
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
FROM DUAL
within a connection pool, dedicated for the authentication purpose:
EBS_Authentication_Pool
Of course this initialization block can be extended to populate other variables than the ones mentioned in this example. The connection pool uses a static user name and password to connect to the EBS OLTP database, referenced by the static repository variables, e.g.:
- Static_USER_ID
- Static_DSN_OLTP
When connection is established, the first thing the OBIEE Server will invoke is the following package (stored procedure) call:
call /* valueof(NQ_SESSION.ACF) */
APP_SESSION.validate_icx_session('valueof(NQ_SESSION.ICX_SESSION_COOKIE)')
The value of the parameter NQ_SESSION.ICX_SESSION_COOKIE is passed through by the presentation server, obtained from the EBS session cookie.
If this package call fails the authentication fails. When the call is successful, the authentication session variables are populated, after which the next init block is executed for Authorization.

FndGetResp. This initialization block will query the responsibilities from the EBS OLTP database:
SELECT RESPONSIBILITY_KEY
FROM FND_RESPONSIBILITY
WHERE RESPONSIBILITY_ID = 'valueof(NQ_SESSION.RESP_ID)'
This query will populate the (single valued) GROUP repository variable and assign the user to one of the preconfigured repository groups and web catalog WEBGROUPS. Based on these groups the user will have access to role-specific content.

Because of the tight and enforced EBS security integration, the OBIEE repository is only accessible using EBS: an EBS session ID must exist in the EBS database in order to be authenticated. Each database query to the EBS database will check whether the session ID still exists. If not Authentication, Authorization or the Query fails.