Data Level Security In OBIEE11g

Data Level Security is basically securing the data. Users belonging to particular group should see a certain set a data whereas users outside that groups shouldn’t see that data. Example: Users belonging to Asia group should see only the data for Asia region whereas users belonging to US region should see data for US region.

Data-level security controls the visibility of data (content rendered in subject areas, dashboards, Oracle BI Answers, and so on) based on the user's association to data in the transactional system.

This controls the type and amount of data that you can see in a report. When multiple users run the same report, the results that are returned to each depend on their access rights and roles in the organization. For example, a sales vice president sees results for all regions, while a sales representative for a particular region sees only data for that region.

Example

Here we will look at creating and using a session variable and how to implement row level security. This is mainly used to restrict data based on the user rights. The row level security will be useful in situations like:

1. Allowing user to see data that she has access to.

2. Showing data based on current date.

3. A sales manager can be shown data in his region only. A CEO can be shown data for all regions.

In this post we look at showing units ordered in the current month. We use a security filter to filter data for the current month.

Steps:

1. The first step is to create the session variable for the current month. To do so

 a) In the Administration window, click on Action - > New -> Session -> Variable. Give CURRENT_MONTH as the name of the variable. Click on 'New' near the initialization block.

 b) Give CURRENT_MONTH_INIT as the name of the initialization block. Click on Edit Data Source.

c) A new window opens. Select the connection pool by using the browse button.

d) Use database as the data source type.

e) Type in the following query:" select month (curdate ()); " in the default initialization string.

f) Click OK to close the dialog.

g) In the Session variable initialization block, click on edit data target.

h) Select the CURRENT_MONTH variable. Click on Ok.

i) Click on OK to create the session variable.

2. The next step is to use this session variable to filter the result for this month.

a) In the Administration tool. Click on Manage -> Security.

b) Create a new User called Monthly User.

c) Create a new group called MonthlyUserGroup. Assign MonthlyUser to this group.

d) Open the MonthlyUserGroup dialog and click on Permissions.

e) Click the tab that says filters. Click on 'Add'

f) In the name of the filter select the name of the table that you want to apply the filter on.

g) Click on the ellipsis in the business model filter column. Apply the filter
h) the group is now created. 

3. Login to BI answers using the MonthlyUser user. Select the columns from the store database. View results. You will notice that the results show data for the current month only. If you login by a user from the administrators group, data for all months will be visible.