OBIEE 11g – Advanced Row level security


Most of the companies that use BI as the enterprise reporting tool require a row level security mechanism.

At this stage, it is important to bear in mind that the row level security has nothing to do with the user authorization to see the different reports or dashboards but its main target is to limit the data that the users can see.

It is essential that the process of granting the right access to users runs seamlessly in the background. This can be implemented in OBIEE 11g and this article explains how this can be achieved.

The organization details

Suppose that the company is structured with the following hierarchy:

On the other hand, the data security policy is defined by the following requirements*:

Requirement 1

Every employee that logs in OBI should be able to see his/her department data only.

Requirement 2

Users can also be assigned extra privileges. To make the solution flexible, a data security administrator will be in charge of assigning extra data privileges to the company users.

Requirement 3

Those employees having a BI Author or BI Administrator application role will be granted full data access.

Requirement 4

Full data access can be given to employees also as an extra data security privilege.
*To exemplify the specifications requested by the business users, let´s consider the following scenario:

User A works in department a – Section I but the data security administrator also grants him access to see the data that belong to department c and section VII, then the user should be able to see the information shown in the image below:

Technical solution

1. Database table

In order to cover requirement 2, the extra security privileges will be stored in a DWH table (OBIEE_SECURITY). A security administrator will be in charge of maintaining these exceptions.

In case the administrator wants to grant Full data access to User B, then there would be a table record like the following one:

2. Session Initialization blocks

The process of assigning the different data security privileges automatically to each user is managed by session initialization blocks in three steps:

Step 1. Assign full data access to BI Authors and BI Administrators

Step 2. Grant full data access to those users that are referenced in the data security table
Step 3. Store in a session variable all the organization units that the user will be able to visualize regardless of the levels of the organizational hierarchy

Taking a look back at the example explained before, this variable would contain the following values for User A:

3. Data filtering

The levels of organizational hierarchy will be represented by three application roles in Weblogic:

- Division User

- Department User

- Section User

Note that the authenticated-role has been included in the membership list for all three of them. Therefore, any user that gets authenticated through OBI, he/she will be automatically assigned to these roles.

Finally, we need to set the data security filters through the Identity manager for every application role created in the previous point:

A few comments before concluding:

- Make sure that you don’t have other data security filters defined for the same objects in lower application roles since they can get disabled, i.e. in BI Consumer.

- The session manager is the best place where you can check the session variable values and detect possible bugs in your initialization blocks.

2 Responses to “OBIEE 11g – Advanced Row level security”

  1. Hugo Martinez says:

    Hi Jordi,

    Could you please explain how this would work with dashboard prompts?
    Also, for requirement #1, does this resolve in having an ‘IN’ statement with all the sections within a department?

    In our current scenario we have Departments and Sections in separate tables, is this design scalable to two dimensions?

    Thank you,

    Systems Analyst
    University of British Columbia

privacy policy - Copyright © 2000-2010 ClearPeaks