Accessing Groups in LDAP for use in Oracle Business Intelligence

This one is useful if you’re trying to set-up BI to work with LDAP

Oracle BI allows for integration with LDAP servers for authentication and security
out-of-the-box. This document articulates the solution for retrieving Security Groups
defined within LDAP and reuses them within the context of Oracle BI repository
seamlessly. This document assumes that the users are using an Oracle Database and can
leverage the DBMS_LDAP package built into the Oracle Database for this
purpose.

Typically Organizations use LDAP servers as a central infrastructure for storing the
Users security credentials and use these servers to authenticate and authorize users
access to various applications within the organization. Tapping into this security
infrastructure helps the organization maintain its security in a central infrastructure.

Currently, OBI EE can connect to an LDAP server and authenticate a user with
user and password credentials, but it is limited in its ability to extract the groups
defined within the LDAP server and to leverage these groups in the repository.

Scope and Application

The work around suggested in this paper would allow the admin to reuse the
groups in the LDAP server using the DBMS_LDAP package available within the
Oracle Database.

Accessing Groups in LDAP for use in Oracle Business Intelligence

The goal is to allow access to the Users and Groups defined within LDAP Server,
without having to redefine these in a database. This allows the enterprise to
leverage a single common security infrastructure and allows OBI EE to plug into
this infrastructure.
The following are the high level steps to access the Groups defined within the
LDAP server.
1. Using the DBMS_LDAP package provided within the Oracle Database,
write a stored function to connect to the LDAP Server and expose the
Groups as a virtual table.
This PL/SQL package creates a virtual table within the database, which acts as
a gateway to LDAP server. It is now possible to write queries in standard SQL
form to this virtual table that would in turn be translated to the LDAP server.
2. Provide parameters needed to connect to LDAP for authentication. In
order to do this, open the Administration Tool used for managing the
OBI EE repository. From Manage -> Security -> LDAP Servers menu,
provide the necessary parameters needed to connect to the LDAP Server.
(for additional details follow the steps detailed in the Server Admin Guide
for OBI EE).

The above picture is a sample of properties required for connecting to a LDAP
server.
3. The next step is to create a Session Initialization Block within the OBI EE
Admin tool and wire the LDAP server property to this initialization block.
The user id defined in the LDAP server should be associated with the “USER”
session variable. USER is a system session variable within the Oracle BI stack
and is used to store the USER information entered during login from the
presentation server.

4. Next, create another initialization block within the OBI EE Admin tool to
store the Group information. The group information will be queried from
the Virtual Table (defined as part of stored procedure/function defined in
step 1) and to get the group information using row-wise initialization. This
Initialization block should be executed after the Initialization block
defined in the previous step.

The screen shot above shows an example of the SQL query being passed to
the Oracle DB where the PL/SQL stored procedure (from step 1) was created
and extracting the Group information stored in LDAP using row-wise
initialization.