21 Jul 2011 Authentication and Authorization with MS Active Directory

The process of integrating a BI platform within a company infrastructure can be quite challenging.

The average medium-sized company usually uses several technologies to run their business, sometimes specific to departments and other times implemented across different functions. It is also a difficult task to manage all groups and users from different departments so you have to find a trade-off solution in order to facilitate any future maintenance tasks. Leveraging existing LDAP technologies within the company is a classical example.

This article, as a matter of fact, explains the integration of Active Directory with OBIEE 10.x (and previous versions) for Authentication and Authorization.

In a nutshell, the process of Authentication consists of logic used to recognize and check login and password of the user, while Authorization involves assigning permissions and privileges through Groups.

For example, if one user belongs either to the group of Accountants or to the EMEA Region, then this user has restricted ability. The user is authenticated in Active Directory, but we can use AD to pass the user groups to OBIEE as well. Then OBIEE matches the groups via Initialization Blocks in the administration tool and the web catalog to control permissions and privileges to reports, dashboards, data, etc.

As we all know, the integration between OBIEE and the Oracle-proprietary LDAP tool, Oracle Internet Directory (OID) is seamless and easy.

What about Microsoft Active Directory? Well, there are a few limitations to overcome. For instance, we can not use import, import all or synchronize users from OBIEE Admin Tool.

Figure 1 – User list in the Active Directory Administration console.

There are several possible configurations to implement Authentication and Authorization in OBI.

A common strategy is to do the authentication against an LDAP server and then the authorization with an external table providing the groups the user belongs to.

While in a previous ClearPeaks blog article we explain how to implement SSO Authentication using IIS, which is a Microsoft Windows based technology as well, this article will explain how to do both against an Active Directory server.

Windows based environments are widely spread and single sign-on is very convenient for users.

First, you have to set up an LDAP server in OBI and do the authentication through it with a session initialization block that populates the user session variable.

This is simple and it is well described in this blog article as well as in this one.

The second task is to setup a function in the database to query the AD and dynamically get the groups the user belongs to.

This is one of the biggest selling points for this strategy as it allows to implement a single administration point for all user groups that can eventually be reused by different systems.

Figure 2: Row-wise initialization query to be implemented in OBI to get all user groups.

Notice the function called getusergroup in the figure above.

In the rest of the article we will describe how to create and use it in an OBI session initialization block to get the GROUP system session variable.

For those familiar with the way OBI manages the Authorization strategy, GROUP is a reserved session variable that provides the groups that the user belongs to.

Using the above mentioned function, we will populate GROUP from the Active Directory.

Figure 3 – User Groups are managed in a single point: AD console

Now let’s describe how to set up the getusergroup function.

This function uses the DBMS_LDAP_UTL package, an Oracle-built tool to communicate to an LDAP directory from a database.

For non-Oracle database technologies, similar packages are either provided or need to be created.

Detailed information regarding the DBMS_LDAP_UTL package can be found in the following Oracle documentation:

DBMS_LDAP & DBMS_LDAP Sample

Although the function DBMS_LDAP_UTL.get_group_membership is usually used to get all user groups, it works with OID but not with the implementation of LDAP by Microsoft Active Directory, as it will simply throw a “get_group_membership returns: -3″ message.

Therefore it is advised to use the function DBMS_LDAP.search_s (search scope) which performs a search within the AD tree looking, in this case, only for the attributes we are interested in, for example:

login name = USER (OBIEE session variable) = sAMAccountName  and GROUP (OBIEE session variable) =  memberOf

Find below the details about the getusergroup function. Of the whole code, we are going to focus on the most interesting parts.

It receives an input parameter, username, varchar2 type, which takes the USER system session variable (coming from sAMAccountName from AD during the authentication process).

NOTE: sAMAccountName variable is created in AD during the authentication process, so it is not available before the user logs in.

This function is not optimized for a production environment and it will likely require some modifications.

Thus, there are some portions of code commented that you can uncomment to “debug” the code, and some others you should edit or comment.

In the code above, the function DBMS_LDAP.search_s is used with the parameter my_attrs which contains the attributes we are interested in.

Selecting all, it would return some leaves with attributes of the LDAP tree that risk producing errors by the DBMS package because of special characters.

It is advisable, then, to apply filters so to only select the objects that carry out both objectclass=user.

At the same time, the login name is passed as parameter and compared to sAMAccountName, used during the authentication by AD.

The next step is to compile the edited function, and check it is working.

Query your database with a select statement similar to the one below, depending on your function name, in order to get a similar result to Figure 2.

Now it is time to go to the Administration Tool, and create two initialization blocks: one for the Authentication, which fills the system session variable USER, and another for the Authorization, which fills the system session variable GROUP.

In order to do that, as a best practice we suggest to create a specific connection pool, to be used uniquely for authorization.

The next step is to use the function in the Initialization blocks as per following instructions. Go to the Admin tool à Manage Variables.

1. Create a Session Variable Initialization block.

2. Edit the Data Source, Select Data Source Type: Database, Choose the Connection pool that you specifically created for the authorization.

3. Check Use database specific SQL, and then put in the text box the query above.

Figure 4 – Creating the Authorization Initialization block

Then go to Edit Data Target and check Row-wise initialization. This would convert the above SQL in the data source into a single row statement.

Figure 5 – Setting the Row-wise initialization rule

You can Edit the execution precedence and select the Authentication initialization block, initUser in this example, which you created previously.

Check Required for authentication.

Finally, you should get to the window shown in Figure 6.

Figure 6 – the Session Variable Initialization block Dialog Box

If we press the Test button we will get the username we have used in the authentication of the tool, and it will be used by the GROUP session variable initialization block:

Figure 7 – Testing the variable

Clicking on the Ok button, the new initGroup Initialization block executes the query in the database against Active Directory and gets the groups with the user passed as a parameter.

Figure 8 – Results of the test

If the test is successful as per Figure 8, check in the Initialization block and save it.

The next step is to test the user authorization: log into Answers with any user you have defined in your Active Directory.

The purpose is to see all groups belonging to each logged user.

Create a simple report with any row (that you can hide), and a title with this content:

You will get all the groups that the logged user belongs in the AD separated by semicolons ‘;’.

The final step, of course, is to implement any kind of security rule required across all levels in OBIEE (dashboards, presentation layer, data level, etc.) with the groups retrieved, which is straightforward and not the subject of this article.