OBIEE 12c Security - LDAP Authentication & DB Authorization using BISQLGroupProvider

OBIEE 12c has integrated with the Oracle Fusion Middleware Security architecture. By default, an OBIEE 12c installation is configured with an authentication provider that uses the Oracle WebLogic Server embedded LDAP server for user and group information.

In a real business scenario, the user prefers to use the same login credentials which he/she is using to access all other applications in the company. It means, there is a need to integrate OBIEE 12c with another LDAP active directory where the company has stored their user’s login information. This can be achieved by reconfiguring the domain to use another LDAP security provider for authentication and BISQLGroupProvider for database authorization.

Authentication is nothing but validating users to confirm that they have permission to access the system whereas authorization is ensuring users can do and see what they are authorized to do and see after logging in to the system.

The security can be implemented in the following two phases:

• Configure Active Directory (AD) LDAP Authentication
• Configure Database (DB) authorization using BISQLGroupProvider

1. Steps to configure AD LDAP Authentication

Prerequisites

The following prerequisites must be satisfied before attempting to configure LDAP authentication:

1. OBIEE 12c must be installed and running
2. AD LDAP server must be configured

Login to WebLogic console http://hostname:portnumber/console

In the Left pane select Security Realms under Domain Structure, then click on myrealm in the right pane.

Next click on Providers, by default you will see three different authentication providers:

1. Trust Service Identity Asserter
2. DefaultAuthenticator
3. DefaultIdentityAsserter

Click on DefaultAuthenticator provider, then click on Lock & Edit button in the top-left pane. Now change the Control Flag from REQUIRED to SUFFICIENT and then save the changes.

Once the Settings updated successfully message displayed, click on the Activate Changes button in left pane. Then to restart the service's message would be displayed. The services can be restarted now using ./stop.sh and ./start.sh command.

To set up a new LDAP Authentication Provider, Go to Security Realms → myrealm → Providers, click on button New.

The LDAP Authentication can be renamed like ADProvider (can be renamed based on the requirement). Then select the type as ActiveDirectoryAuthenticator & click on button OK

Once done, then click on ADProvider and change Control Flag to SUFFICIENT and click on button SAVE.

Now click on Provider Specific and fill the following details.

Host: domain.com

Port: port number

Principal: CN = srv_oraclebi2, OU = Open Service, OU=Users - All, DC=jade, DC=com

Credential: (Enter the password for the Account listed above)

Confirm Credential: Re-enter the same

User Base DN: OU = Users - All, DC = jade, DC = com

All Users Filter:

User From Name Filter: (&(sAMAccountName = %u)(objectclass = user))

User Search Scope: subtree

User Name Attribute: sAMAccountName

User Object Class: user

Group Base DN: OU=Groups - All, DC = jade, DC = com

Group From Name Filter: (&(cn = %g)(objectclass = group))

Group Search Scope: Subtree

Group Membership Searching: limited

Max Group Membership Search Level: 5

Static Group Name Attribute: cn

Static Group Object Class: group

Static Member DN Attribute: member

Static Group DN from Member DN Filter: (&(member = %M)(objectclass = group))

Dynamic Group Name Attribute:

Dynamic Group Object Class:

Dynamic Member URL Attribute:

User Dynamic Group DN Attribute:

Connection Pool Size: 6

Connect Timeout: 0

Connection Retry Limit: 1

Parallel Connect Delay: 0

Results Time Limit: 0

Follow Referrals: check

Cache Enabled: check

Cache Size: 32

Cache TTL: 60

Cache Statistics Enabled: check

GUID Attribute: object GUID

Identity Domain:

There is no need to change Performance tab configuration, keep it default. Click Save and click Activate Changes. Then to restart the services message would be displayed.

Now restart the BI services using ./stop.sh and ./start.sh command.

To validate newly configured AD LDAP authentication, log in to http://hostname:portnumber/console and then navigate to
Security Realms →myrealm → Users and Groups → Users → Customize this table

Enter user-id under Criteria & hit the Enter key, you should be able to see the fetched user id from LDAP

Enabling Virtualization by Configuring the Identity Store:

To enable virtualization enabling the use of multiple identity stores with the identity store service, Log in to Fusion Middleware Control (EM) http://hostname:portnumber/em

From the navigation pane expand the “WebLogic Domain”, select “Security” & then “Security Provide Configuration”

Expand “Identity Store Provide” and then click on “Configure” to display the Identity Store Configuration page.

In the Custom Properties area, use the + Add option to add the following custom properties:

• Property Name = virtualize
  Value = true
• Property Name = OPTIMIZE_SEARCH
  Value = true

Note: Use lowercase for the Property Name virtualize, and use uppercase for OPTIMIZE_SEARCH.

Click OK to save the changes. Restart Admin Server & Managed Servers

2. Steps to Configure DB authorization using BISQLGroupProvider

OBIEE provides BISQLGroupProvider, an authentication provider for WebLogic Server that enables the user to use this method. This authentication provider does not authenticate end-user credentials but enables external group memberships held in a database table/materialized view to contribute to an authenticated user's identity.

Prerequisites

The following prerequisites must be satisfied before attempt to configure LDAP authentication:

1. OBIEE 12c must be installed and running.
2. A suitable database schema containing at least one table/materialized view (MV) which will hold AD user & group information. In the below example, we have used MV & this MV is scheduled to refresh on daily basis to keep it in sync with LDAP
3. Copy bi-sql-group-provider.jar from /OBIEEServer/OBI12C/ebs/bi/plugins/security to /OBIEEServer/OBI12C/ebs/wlserver/server/lib/mbeantypes. After that stop & start entire OBIEE servers using ./stop.sh & ./start.sh resp. (Ignore if .jar file is available at target location already)

Select the Driver and click next 

Click Next again without any change

Then enter the following details and click next

Check all details & Test Configuration. It should succeed and click next

Select the Targets, choose the servers as a deployment target for your data source

Click on Finish. The Data source will be created.

• Configure BISQLGroupProvider SQL Authenticator

  BISQLGroupProvider authenticator uses Materialized View “MV_USER_GROUP_LIST”, which should have all LDAP users with their respective groups.

  

  Go back to Services >> Security Realms >> myrealm >> Providers. Click New.

  Put the Name= BIGroups, Type=BISQLGroupProvider & then Click OK.

  

  Observe ‘control Flag’ under configuration → common tab for ‘BIGroups’. It should be Optional.

  

  Click on ‘Provider Specific’ tab & add the following details.

  Data Source JNDIName = JDBC/BIGroupSource

  Group Membership Searching = Unlimited

  Max Group Membership Search Level = 0

  SQLList Groups = SELECT DISTINCT GROUP_NAME FROM MV_USER_GROUP_LIST WHERE GROUP_NAME LIKE?

  SQLGroup Exists = SELECT DISTINCT GROUP_NAME FROM MV_USER_GROUP_LIST WHERE GROUP_NAME = ?

  SQLIs Member = SELECT USER_ID FROM MV_USER_GROUP_LIST WHERE GROUP_NAME = ? and UPPER(USER_ID) LIKE UPPER(?)

  SQLList Member Groups = SELECT GROUP_NAME FROM MV_USER_GROUP_LIST WHERE UPPER(USER_ID)=UPPER(?)

  Descriptions Supported = Uncheck the box

  

  Save. Go back to Providers List and reorder provides as below.BIGroup should be in the first position

  Activate the changes & Restart the entire OBIEE servers. Check the “Users and Groups” tab to confirm that the database users and groups are reflecting.

• Configure Database Adaptor to Retrieve Group Information:

  Now to enable the virtualized identity store provider to retrieve group information from a database using the database adapter configure a database adaptor to make it like an LDAP server

  In this task, we create a file containing the elements for adapter templates that specify how to use database tables as an identity store to map groups.

  Use below attached bi_sql_groups_adapter_template.xml file.

  

  Update the appropriate attribute as per your environment in above file like <param name = "ReplaceAttribute" value = "uniquemember = {cn = %uniquemember%,cn = users,dc = oracle,dc = com}"/>

  • Copy the bi_sql_groups_adapter_template.xml adapter file into the following folder
    /OBIEEServer/OBI12C/ebs/oracle_common/modules/oracle.ovd/templates
  • Open a command prompt/terminal at:
    /OBIEEServer/OBI12C/ebs/oracle_common/bin
  • Make sure the following environment variables are set, for example:
    ORACLE_HOME = /OBIEEServer/OBI12C/ebs/
    WL_HOME = /OBIEEServer/OBI12C/ebs/wlserver
    JAVA_HOME = ORACLE_HOME/JDK/jre (set the Jave home path)
  • Execute the libovdadapterconfig script to create a database adapter from the template file. Parameter values like root, cn, dc, need to change as per the environment.

    The syntax is:  

    libovdadapterconfig -adapterName <adapterName> -adapterTemplate <name of template file which defines adapater> -host localhost -port <Admin Server port> -userName <user id of account which has administrative privileges in the domain> -domainPath <path to the BI domain> -dataStore DB -root <nominal specification of a pseudo-LDAP query to treat as the "root" of this adapter - must match that specified in template for adapter 2 above> -contextName default -dataSourceJNDIName <JNDI name for DataSource which points at the database being mapped>

    For Eg.

    ./libovdadapterconfig.sh -adapterName biSQLGroupAdapter -adapterTemplate bi_sql_groups_adapter_template.xml -host localhost -port 12300 -userName weblogic -domainPath /OBIEEServer/OBI12C/ebs/user_projects/domains/EBSTEST -dataStore DB -root OU = "Users - All,DC = jade,DC = ca" -contextName default -dataSourceJNDIName jdbc/BIGroupSource

  • Restart WebLogic Administration Server and Managed servers.

    Note: When you start WebLogic, you can ignore the following Warning: BISQLGroupsProvider: Connection pool not usable.

Now you should be able to log in to Oracle Business Intelligence & see the allocated roles to your id.

References: