Table of Contents

OBIEE 11G - BISQLProvider

About

A authentication provider called BISQLGroupProvider that provide the groups through database look-up.

This group can then be linked to application role.

The database just stores the groups to be associated with users. There is no password for the users.

Restriction

You can only have a single LDAP Authenticator linked to a single database Group look-up 'authenticator'.

For example, if you need Delivers to deliver content to members of an application role the following restrictions apply:

Prerequisites

Steps

Installation

The JAR file BISecurityProviders.jar contains the authenticator. and must be copied from 

MW_HOME/ORACLE_HOME/bifoundation/security/providers

to 

MW_HOME/wlserver_10.3/server/lib/mbeantypes

Then restart Weblogic (ie the Administration Server)

Schema

The advantage of defining tables (or views) identical to the below figure is that the configuration of the BISQLGroupProvider can use the default SQL

Obiee 11g Bisqlprovider Schema

GROUPMEMBERS_VW is a view with an outer join between GROUPMEMBERS and GROUPS tables.

You must map the users in your LDAP store to Groups in your database table by login name. The value of G_MEMBER in the GROUPMEMBERS table must match the value of the LDAP attribute used for login (for example, uid, cn or mail), as specified in the LDAP authenticator. For example, you should not map the database groups by uid if the login attribute is mail.

Database Connection

Data Sources > New > Generic Data Source.

Obiee Bidatabasegroupds

New Authentication Provider

Create a New Authentication Provider with

SQL statements

In the Provider Specific tab, you can specify the SQL statements used to query and authenticate against your database tables.

the question mark (?) is a runtime query placeholder and must be always present (rather than hardcode a user or group name).

As the data from the database is case sensitive, be sure to include the UPPER function in any BISQLGroupProvider SQL statements: 

SELECT G_MEMBER FROM GROUPMEMBERS WHERE UPPER(G_NAME) =UPPER( ? ) AND UPPER(G_MEMBER) =UPPER(?)

SQL List Groups

SELECT G_NAME FROM GROUPS WHERE G_NAME LIKE ?

The SQL statement used to retrieve group names that match a wildcard. The SQL statement requires a single parameter for the group name and must return a resultSet containing matching groups.

SQL Group Exists

SELECT G_NAME FROM GROUPS WHERE G_NAME = ?

The SQL statement used to look up a group. The SQL statement requires a single parameter for the group name and must return a resultSet containing at most a single record containing the group.

SQL Is Member

SELECT G_MEMBER FROM GROUPMEMBERS WHERE G_NAME = ? AND G_MEMBER = ?

The SQL statement used to look up members of a group. The SQL statement requires two parameters:

It must return a resultSet containing the group members that matched.

SQL List Member Groups

SELECT G_NAME FROM GROUPMEMBERS WHERE G_MEMBER = ?

The SQL statement used to look up the groups a user or group is a member of. The SQL statement requires a single parameter for the username or group name and returns a resultSet containing the names of the groups that matched.

SQL Get Group Description (if description supported enabled)

SELECT G_DESCRIPTION FROM GROUPS WHERE G_NAME = ?

The SQL statement used to retrieve the description of a group. Only valid if Descriptions Supported is enabled. The SQL statement requires a single parameter for the group name and must return a resultSet containing at most a single record containing the group

Enabling Virtualization

You configure the identity store to enable virtualization so that more than one identity store can be used with the identity store service, and therefore user profile information can be split across different authentication providers (identity stores).

Weblogic Identity Store Virtualize Conf

If you set the virtualize custom property value to true, Oracle recommends that the BISystemUser must exist in only one identity store.

Authentication providers Control Flag

When using multiple authentication providers, configure the Weblogic - Login Module (Control Flag and authentication flow) setting for all authentication providers as follows:

Configure a database adaptor

You configure a database adaptor to make it appear like an LDAP server, which enables the virtualized identity store provider to retrieve group information from a database using the database adapter.

See 3.4.4.4.3 Configuring a Database Adaptor to Retrieve Group Information

You cannot modify an existing database adapter, so if you make an error in either the libovdadapter command, or the templates you use to create the adapters, you must delete then recreate the adapter.

Testing

Add a group to an application role

Configuration

Cache

Application Role Cache Bisqlprovider

Critical

Unable to Sign In
An error occurred during authentication.
Try again later or contact your system administrator

See 3.4.6 Configuring Multiple Authentication Providers so that When One Fails, Users from Others can Still Log In to Oracle Business Intelligence Manually edit adapters_os.xml in 

<MW_HOME>/user_projects\domains\bifoundation_domain\config\fmwconfig\ovd\default

and change the

true

to

false

for the BISQLProvider adapter.

Making this adapter non-critical (i.e. if it fails it will carry on) should not matter. After this change, restart the WLS server.

Documentation / Reference