Using Initialization Blocks with LDAP and database queries to control authentication and authorization

Someone on OTN asked me to elaborate on a comment I made on a post that Mark Rittman wrote last week. What I need to achieve here is to use an existing LDAP server to validate the credentials of the use who is logging on to the OBIEE. To set this up we first open the Administrator tool and in the Manager->Security... tool we define a new LDAP server:

The values you enter here are, of course, dependent on your system and your LDAP administrator can give you the correct values you need. But once you have defined an LDAP server we pop over to the Manager->Variables... tool and start the real work.

First off we define an Initialization Block called something like LDAP Init Block. An Init Block needs a data source, so we define one by clicking on the 'Edit Data Source...' button and choose data source type: LDAP. Click on the 'Browse...' button and select the LDAP server we defined before. You can actually create new LDAP servers here as well, by clicking the 'New...' button (which just starts the same wizard we used previously).

Next we need to map LDAP attributes to Session Variables. Click the 'Edit Data Target...' button and add the following system variables:

The warning dialog you will get after creating each variable is normal, since these are special variables used in by the system. The LDAP variable column might differ on your LDAP server, so make sure you get the correct attribute names from your LDAP administrator as well. Close the dialog and make sure you check the ' Required for Authentication' box at the bottom:

Notice the 'Test...' button? This is a really nice feature that allows you to debug your block and execute it against your LDAP server.

Now you should be able to log in to the BI Dashboard using credentials stored in your LDAP server.

The special System Session Variables we are populating here can also be populated using other Init Blocks. Let's say that you already have your groups defined in a database table, something like the following:

CREATE TABLE OBIEE_USERS (USERNAME VARCHAR(100),GROUPNAME VARCHAR(2000))

For sake of simplicity I assume here we simply store the groups as a simple semicolon delimited list, one row per user.

We now want to create a new Init block for session variables, that will fire after the authentication block has been fired:

Click on the 'Edit Data Source ...' button and create a new Data Source of type Database. The SQL you enter should look something like this (mind the quotes):

select groupname
from obiee_users
where username=':USER'

Just make sure that the username that is used in the connection pool you choose, has SELECT privs (and no more than that) on the table you use to store your group permissions.

Next we need to define the variable that will hold the results of the data source we just defined. This variable is called GROUP and is populated after the user logs in. The Presentation Service parses this variable and grants the user access to any group that is defined there and matches a semicolon delimited value in the variable. Any unmatched value is ignored. So if the table contains something like:

SCOTT SALES;Managers;Research and Development

Now click on the 'Edit Data Target...' button and create a new Variable called GROUP:

Now try to log in to the Dashboard and see your blocks in action. Make sure that the groups you have in your database table are actually defined in the Presentation service. A good and quick test is to also create dashboards for these groups. Once you log in you should be automatically granted access to different dashboards depending on the values in your table.

If you want to populate more variables using the same SQL statement, make sure the column order in the query matches to variable order in the 'Data Target' settings.