Row level security using Initialization Blocks and Session Variables

What is Initialization Block and session variable in OBIEE +?

How to use row level security in OBIEE ?

If you a familiar with the web development you should be aware of the concept call constructor. Constructor is automatically called when object is initiated. And object initiated means; object is assigned to the memory. Destructor is does the exactly opposite work by cleaning/freeing the memory used by object.

And object here is Session.
So long story short. You can use initialized the session variable under Initialization Block in OBIEE +.

Session variables are like a global variable. They are available across the repository.

Types of Session Variable

System: Session variable

System session variables are session variables that the Oracle BI Server and Oracle BI Presentation Services use for specific purposes. System session variables have reserved names that cannot be used for other kinds of variables (such as static or dynamic repository variables and non system session variables).
Examples of the System session variable

USER Holds the value the user enters as his or her logon name.
EMAIL Contains the user’s default email address for use with Answers

Non System: Session variable

The procedure for defining non system session variables is the same as for system session variables.

A common use for non system session variables is setting user filters. For example, you could define a non system variable called   USERNAME in UsageTracking.rpd repository. So that each user can just see their usage not every one else’s Usage.

Below are the steps on how to achieve this.

Step 1: Create an initialize block called setUser

Initialization Block

Variable Manager

Step 2: Create session variable called: LOGIN

Variable Creation

Step 3: Set the filter in Presentation Layer for the Column.

Variable Filter

Step 4: Run the report in Answer to verify that filter and all is working.

Answer's Report

Wow…. We just got the data related to customer’s only

Step 5: Check the sessions

Now when report is running check in the OBIEE Administrator
go to Manage – -> Sessions

Session Manager

Great we can check the Session Variable LOGIN and its Value is Administrator which is user who is currently logged in.
It gives lots of other information about the session like
Session id which is unique for each Session
Variable contains the name of the session etc

Step 6: Check logs

Make sure that for Administrator or the user for which you want to see the logs , proper log level has been set. Logging level 5 is good enough to see the query being sent to database.

Logging level 0 Means no Logging
Logging level 7 Max logging

Please note: On production system Logging is overhead unless and until it is absolutely required.

Check Log Level

Go to \server\Log\ NQQuery.log

You will see the screen shot as attached below

Log file