Implementing data-level security in Oracle BI (OBIEE)

Data Level Security involves securing the data available in an application in such a way that each user will see only the data that he/she is authorized to see, resulting in each user possibly seeing different results on the same report. In this post I will describe how to implement data-level security in Oracle Business Intelligence (OBIEE).

Let’s use an example to describe data-level security. Each user of the BI system works in or is assigned to a particular Business Unit. Each user is allowed to see only the data for his or her assigned Business Unit.

In our example, the below table lists the 4 users and the Business Unit that each of them works in or is assigned to, and therefore, should have access to. We will call this the USER_TO_BUSINESSUNIT table.

Jane and Xing should only be able to see data for Business Unit BU2000, Bill should be able to access data for both BU3000 and BU4000, and Venkat should be able to access data for BU4000.

Now, we will use the below table as the example data set that we need to secure with the Business Unit data-level security. We will call this table TRANSACTION_DATA.

When data-level security is applied …

Jane and Xing will be able to access/see the following data:

Bill will able to access/see the following data:

And Venkat will be able to access/see the following data:

So, now let’s move on to how to implement data-level security in OBI to achieve what was described above.

First, ensure that the USER_TO_BUSINESSUNIT table data is correct and up-to-date, and that there is an ETL in place or some other method of keeping that data updated. You want to ensure that if and when a user’s Business Unit changes, it is reflected in this table so that the user will have access to the appropriate data.

Next, create a Session Initialization Block with row-wise Initialization that will be used to get the list of Business Units that a user has access to.

This needs to be a “Session” Init block so that it will run each time a user logs in, and gets that user’s list of Business Units; and it needs to be row-wise because some users will have more than 1 value returned.

In the Session Variable Initialization Block Dialog, enter a Name for the Init Block.

Then click Edit Data Source

In the Data Source dialog, enter the SQL to get the Business Units for the current logged in user. Click OK when done which closes this window and brings you back to the Session Variable Initialization Block Dialog.

Enter your Variable name and check “Row-wise initialization”. As mentioned above, we need to select row-wise because our Init Block SQL may return more than 1 value for some users. For example, when Bill in our example above data logs in, the Initialization Block will return values BU3000 and BU4000, and store them in the Target Variable, “BUSINESS_UNIT”.

You may also check “Use caching” to store the values in cache. Click OK when done.

Then click OK to save the Init Block.

Next, apply data filter(s) to the appropriate data set(s) for the appropriate role(s) using the Target Variable above. You may have role(s) specifically used for data-level security and will need to apply it there, but if not, you will need to apply the filters in each role that has access to the datasets/dashboards/reports that you want to apply data-level security to.

Manage -> Identity

Go to the Application Roles tab, and select the Application Role to which you would like to apply the data-level security. In the APplication Role dialog, click Permissions.

In the Permissions dialog, select the layer and data table that you want to apply the data security to, and then enter the appropriate filter. In this example, you are filtering by BUSINESS_UNIT. This will cause the data to be filtered to only include each users’ Business Units.

Save your changes. You have now applied data-level security. This is what will happen now:

User logs in -> Init Block runs and selects the Business Units associated with the user’s User ID -> Init Block assigns value(s) to the variable BUSINESS_UNIT -> if the user is a member of a role that has data security applied to -and- the user visits the report -> the data filter will be triggered/run -> User only sees data for the Business Units the user is allowed to see.

Look out for my upcoming post on implementing a special type of data-level security: Reports-To Data Level Security.

–Issue :I have 2 users(User1,User2).When ever User1 enters into the application that he want to see the corresponding data & when ever User2 enters into the application that he want to see all the data.