OBIEE 11g: Data/Row Level Security

In this blog I will explain how row level security can be implemented in OBIEE 11g.

Row-level security is where groups of users have access to a set of reports, but they all see different sets of data within the reports due to filters being applied silently in the background. As a report author you can of course apply filters to an analysis, but in this case I think what people generally associate row-level security with is filters that are applied automatically, according to a set of rules, without the user being aware of it or even filters being visible when you add a filter view to the analysis’ compound layout.

Row level security is implemented by the BI server on the background automatically allowing users to see data or rows to which they are authorized to.

In this example I am going to implement data level security for DIM_PRODUCTS table.

DIM_PRODUCTS

AUTH_DIM_PRODUCTS

Initially without implementing security, I can see all the Products for user ‘TEST1’

We will authorize user TEST1 only for following Products,

Authorization table 'AUTH_DIM_PRODUCTS' will contain following data for user TEST1,

We also need one more authorization table which contains authorization details for all user i.e. in this table we map users with products to which they are authorized to.

  

Open repository in online mode and create 2 different connection pools

First connection pool (‘conpool’)is to import all dimensions and facts from AUTH_UAT schema and second connection pool (‘AUTH_CONN_POOL’) to import authorization tables (AUTH_DIM_PRODUCTS).

We create a dedicated connection pool for importing authorization tables as for data level security we create Initialization blocks which cannot be used with default connection pool used for general database queries.

Now, go to Manage>variables,

Here we will create an Initialization Block for session variable.

We create initialization block as ‘GET_PRODUCT_IB’

 Now we set the properties of this initialization block,

Select ‘Edit Data Source’.

Set connection pool as ‘AUTH_CONN_POOL’ which we used to import authorization table.

Select ‘Data Source Type’ as Database.

Select Default Initialization String. Enter following SQL in text box,

select 'PRODUCTS',product from dim_products pd,auth_dim_products up where pd.product_id=up.product_id and LOWER(up.user_name)=LOWER(':USER')

In above query we are checking for the products authorized for particular user.

‘PRODUCTS’ as a variable which we will be using in filters for fetching Poduct values from database.

‘:USER’ is a session variable used to fetch active user name.

Now select ‘Edit Data Target’ under Variable Target.

Select ‘Row Wise Initialization’ and ‘Use Caching’.

Row Wise Initialization needs to be selected when we are authorizing a user for multiple Product values.

That completes our Initialization Block for Session Variable.

 Next, we create Data Filter for Product.

For this go to Identity Manager in Manage tab.

Go to Application Roles.

I have added user ‘Test1’ under BI Consumer role.

So we create filter for this user under BI Consumer role.

Go to properties of BI Consumer and select Permissions.

Select Data Filters tab and add new data filter.

Select DIM_PRODUCTS from list.

Under Edit Expression add below formula,

"Auth_Demo"."DIM_PRODUCTS"."PRODUCT" = VALUEOF(NQ_SESSION.PRODUCTS)

This formula matches Products authorized for the user and adds filter for the same.

Now check for global consistency and save repository.

That finishes implementation of Data/Row level security for user TEST1 for DIM_PRODUCTS table.

When user logs into dashboard, he will be able to see only products authorized to it.

Thank you :)