How to: OBIEE 11g LDAP Authorization using database tables & stored procedures (advanced)

In our last post, we focused on the first step of OBIEE security configuration - 'authentication'. Authentication is the process of OBIEE validating your login credentials against a pre-defined source, such as LDAP, an external database, or even the RPD. This blog will focus on the second  step of OBIEE security configuration  - 'authorization'.

Authorization is 2 fold.

First: It can be used to control access to dashboards and reports, this is known as object level security.

Second: It can be used to control the viability of data on a report, this is known as data level security.

This how to will focus on the authorization using an external database table as the source.

Step 1:

Create a connection pool (to be used for your authorization) init block:

Best practice is to store the data source name (your database) and user name (your schema) in static repository variables such as OLAP_DSN and OLAP_AUTH_USER

Step 2:

In Variable Manager, create a new initialization block:

Step 3:

Configure the initialization block to point to the connection pool you created by clicking 'Edit Data Source' -> Specify the data source type as 'Database' -> Browse to the connection pool

Step 4:

You'll notice that once you specify the data source type as 'database' and configure the connection pool to point to the authorization connection pool you created earlier, you'll now see a box called 'Default initialization string'. This is where you put the SQL code to return the groups for a specific user.

In the above example, we are using:

 SELECT getSecurityGroup(UPPER(':USER')) FROM DUAL                                        )

This statement says 'run the getSecurityGroup' stored procedure from the OLAP_DSN connection we specified in our connection pool and pass getSecurityGroup the parameter 'USER' which is a session variable populated in the Authentication process. 

getSecurityGroup stored procedure code is basically:

     Select Group From wc_auth_table_d

      where upper(ldap_id)=upper( name_in);

A username is passed in, and the group that user belongs to is returned to OBIEE

The wc_auth_table_d needs to have atleast the following columns:

Name      Null     Type             
--------- -------- -----------------
LDAP_ID   NOT NULL VARCHAR2(50 CHAR)

GROUP NOT NULL VARCHAR2(50 CHAR)

LOGLEVEL  NOT NULL VARCHAR2(20 CHAR)

 

 Step 5:

The above SQL statement returns 'GROUP' for a specified user, which now needs to be saved to a session variable.

Save the result to a variable target called 'GROUP' by clicking Edit Data Target and adding a new session variable called 'GROUP'.

Step 6:

Authentication needs to be ran prior to this Authorization block so you must set the execution precedence to run 'Authentication' first. Click 'Edit Authentication Precedence' and add your authentication block.

  

Step 7:

Your Authorization init block should be similar to the image below: 

Now if you log into your answers environment, your access will be limited to the groups 

 in wc_auth_table_d. If you pass authentication but but fail authorization (i.e. your username does not appear in the auth table), you will see the following error: