by Siva Kumar Mopuri

Security Implementation Benefits

This solution is beneficial in OLTP applications like Oracle EBS that are integrated with OBIEE. In such scenarios, all the responsibilities/groups from OLTP source would be forwarded to OBIEE. Instead of having all the responsibilities in OBIEE, the user has the privilege to select the required responsibilities at runtime and those would be passed to OBIEE.

Note: This blog post considers Oracle EBS as an OLTP source which is integrated with OBIEE.

Requirement

The user needs the privilege to select any one responsibility/group at runtime using an external Java web application from the list of assigned responsibilities/groups. If the user selects the responsibility "AP Manager", the user logged into OBIEE will have the privileges (object/data level) of the selected responsibility (i.e. AP Manager) but without the available responsibility.

Note: Both OBIEE and the External Web Java Application are SSO enabled.

• The user logs into the portal
• The user is provided a link to the landing page where they can view the list of the responsibilities for Oracle EBS users
• The user can select a responsibility from the list and navigate to the OBIEE analytics page with the selected role for EBS responsibilities (Note: All the OBIA roles would be passed to analytics)
• BISQLGroupProvider is used to fetch the roles from the external table
  
  

General Practice 

Once the user logs into OBIEE, the complete list of EBS groups/responsibilities would be available.

Example: If the user has the following EBS responsibilities as a practice, they will be available in the OBIEE page. (Oracle BI Apps related roles)

• AP Manager
• AR Analyst
• Budget Director
• Budget Analyst
• Financial Analyst

Flow Diagram

Proposed Solution 

• Configuring External LDAP (AD, OID etc.)
• ETL Migration for Security Tables
• Configuring BISQLGroup Authorization Provider
• External Web Java Application (Landing Page)
• Configuring OBIEE and the Landing Page to act as a SAML 2.0 Service Provider for SSO
• Testing the Setup

Implementation Steps 

1. Configuration of LDAP

Please refer the below URL for LDAP configuration: https://docs.oracle.com/cd/E23943_01/bi.1111/e10543/privileges.htm#BIESC534

Test Setup

Once the LDAP configuration is successful, please test the following scenarios:

1. All the users have to be visible on the console page
2. Try login into OBIEE analytics using LDAP credentials and the user has to successfully login to the analytics

2. ETL Migration for Security Tables

The following tables are loaded with the BI users and their corresponding responsibilities: X_SECURITY_D

X_SECURITY_TEMP_D

1. The users with at least one OBIA role have an extra record with user type as SSO
2. BISQLGroupProvider is pointed to X_SECURITY_TEMP_D

ETL Migration document for security tables

3. Configuring BISQLGroup Authorization Provider

Here the groups are from the external table, so please refer the below URL for configuring the BISQLGroup Authorization.

https://docs.oracle.com/cd/E23943_01/bi.1111/e10543/privileges.htm#CJAFBFID

4. External Landing Page Web Application

The Landing Page application is a Java based web application (e.g. JSP page) deployed on the bi_server1 of WebLogic domain. Users with privileges can view the list of EBS responsibilities and assigned OBIA roles. The user has the privilege of selecting an EBS responsibility from the list and navigate to OBIEE Analytics with the selected responsibility.

Technical Details of Landing Page Application

Request Flow of the Landing Page Application after User Login 

1. As the WebLogic is a SSO enabled, the user who logged in can be retrieved from the security context using the WebLogic.security.principal.*
2. Connection to the database is established using JNDI CONTEXT using the data source (i.e. BIDatabaseGroupDS)
3. Execute a query on the X_SECURITY_D to get the list of OBIA Roles
4. Execute a query on the X_SECURITY_D to get the list of EBS Responsibilities and select EBS responsibility. The selected value will be stored in role_name and navigates to XX.jsp once the user clicks on OBI Analytics button.
5. The selected group will be updated to X_SECURITY_TEMP_D
6. Once the selected group is updated in the database, the request will redirect to the OBIEE Analytics page.

5. Configuring OBIEE and Landing Page to act as a SAML 2.0 Service Provider for SSO

To configure OBIEE 11.1.1.9.0 as a SAML 2.0 Service Provider (SP) in a SAML 2.0 Federation for Single Sign-On (SSO), below are the steps required to integrate OBIEE with SAML in order to provide single-sign on and secure access to the Oracle BI/Analytics URL.

The approach involves configuring the WebLogic domain hosting BI as a SAML Service Provider.  The Service Provider will redirect requests for protected resources to a third-party Identity Provider (i.e. Ping Identity) which will authenticate the user and produce a SAML token to present back to the Service Provider. A SAML Asserter is configured on the BI domain to accept a SAML assertion provided by a third-party SAML Identity Provider. The UserID is extracted from the Subject of the SAML assertion and passed to a WebLogic Authenticator that is supported for use with OBI 11g.

The steps to configure SAML and OBIEE are given in detail in the following Oracle support link: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=374148388064734&id=2248571.1&displayIndex=3&_afrWindowMode=0&_adf.ctrl-state=m7l0t60qd_126#aref_section212

Note: While configuring the Identity Provider Partner, set the both the applications in Redirect URL:

/analytics/*

/LandingPage/*

Landing Page Application Deployment

Go to

1. <MIDDLEWARE_HOME>/Oracle_BI1/bifoundation/jee/
2. Open urlConfig.properties
3. Update the properties file for the Landing Page application
4. redirectUrl=http://<hostname>:9704/analytics

Update the URL according to the environment where the landing page needs to be deployed      

Deploy the LandingPage.war application on bi_server1 (follow the same steps used for analytics application)

Parameter Changes for the Config Files

Change 1: Add the parameter change to enable WebLogic Data Source.

• $cd oraapps/middleware/user_projects/domains/bifoundation_domain/bin
• edit setDomainEnv.sh and change: " WLS_JDBC_REMOTE_ENABLED=-DWebLogic.jdbc.remoteEnabled=true"

Change 2: Add tags to disable the ACL cache in instanceconfig.xml under:.
<Cache>

<ACLs>
<Enabled>false</Enabled>
</ACLs>

..
</Cache>
</ServerInstance> 

Change 3:

1. Login to the WebLogic console
2. Navigate to Servers --> bi_server1 --> Configuration --> Server Start

Add the parameter -DWebLogic.security.identityAssertionTTL=2 in the arguments section

3. Shut down all the services

Change 4: Go to

/oraapps/middleware/user_projects/domains/bifoundation_domain/config/fmwconfig/ovd/default and update maxPoolSize and maxPoolChecks in adapters.os_xml file

Search for
<ldap id="LDAP"

And set the below parameters value to 100
<maxPoolSize>100</maxPoolSize>
<maxPoolChecks>100</maxPoolChecks>

Change 5:

Take back up of DOMAIN_HOME/servers/AdminServer/tmp and DOMAIN_HOME/servers/AdminServer/cache folders
Remove in the DOMAIN_HOME/servers/AdminServer/tmp and DOMAIN_HOME/servers/AdminServer/cache folders.

Perform the same steps for Managed server (bi_server1 and odi_server1/)

Take back up of DOMAIN_HOME/servers/bi_server1/tmp and DOMAIN_HOME/servers/bi_server1/cache and remove the tmp and cache folders.remove the tmp and cache folders.

Take back up of DOMAIN_HOME/servers/ odi_server1/tmp and DOMAIN_HOME/servers/ odi_server1/cache and remove the tmp and cache folders.

4. Start all the services

6. Testing the setup

Point your browser at the Landing Page app on your BI Domain e.g. http://<HOST_NAME>:9704/LandingPage/Home.jsp

It should prompt for the SSO page and upon providing the credentials, it should display the Landing Page. Once the user selects the group and clicks on OBIAnalytics it should navigate to OBIEE Home with the selected group.

Siva Kumar Mopuri has over 7 years of experience in data warehousing with expertise in OBIEE, ODI, APEX, scripting languages. He is a Certified Hortonworks Administrator (HDPCA) and Developer (HDPCD) and has worked on conversion utilities from Brio to other reporting tools.