Data Level Security in OBIEE11g / Implementing Data level security in OBIEE 11g with example / Row level security in OBIEE11g

Data level security is nothing but groups of users have access to set of reports, but the visibility of the data will be different within the reports due to filters which are applied in the back-end of the report development.
As a report developer you can apply filters while developing reports ..but these filters are visible, But data level security will apply automatically, according to a set of rules, even user will not aware of that, because we are not using filters in analysis level.

For example: Here I'm using SH schema. For now I will explain with one user.

In above screen-shots-1: when we used weblogic user ..it is an administrator account so all regions are visible.

Screen-shot 2: After applying data level security to the country_region column for Asia user.

Below are the main steps we need to follow when we are applying data level security :

1) Create one dedicated connection pool for data level security.
    Use this connection pool while creating Initialization block.

2) Create one user in weblogic console (Ex: Asia)

3) Assign that user to BIAuthor group
4) Open EM and create one application role for that user.

5) Create one Initialization block and one variable.

6) Open Identity manager > Application role > Permissions > Data Filters. Here we need to assign variable to the particular column.(On which column you want to apply data level security)

I'm using SH schema to explain Data level security.

Import below tables with default join conditions.

Create one new connection pool.  

 Login to weblogic console and click on security realms

Click on myrealms.

Click on Users and Groups Tab

 Click on new tab to create a new user

Fill the new user fields with necessary info. After creating new user ...click on new user to assign BIAuthor  role as shown in below screen shot

Logout console

 

Login to EM to create application role.

 Click on security tab and click on Configure and Manage Application Roles

Create one new application role and add newly created user as shown in below screen shot.

 Restart all BI components once to reflect new application role in the RPD.

 Create one new Initialization block with below SQL query

select distinct country_region as varreg from countries
where country_region=':USER' 

create one variable 'varreg' as shown in below screen shot.

Go to Identity manager > Application Roles 

Click on Permissions and click on data filters

 Click on countries table and select country_region column.

Assign session variable to the selected column in fx as shown below

"SH"."COUNTRIES"."COUNTRY_REGION" = VALUEOF(NQ_SESSION."varreg") 

Chenkin changes in RPD and reload  metadata once in answers.

And login with Asia user.You are able to see only Asia related data.

 

Here we can use Row Wise Initialization instead of creating variable.

Click on Row Wise Initialization to learn.


Bye .................Until next post