How to set session variables using url variables

The goal is to set session variables using url variables, but can you also do this for the user and password ?
url variable (&Upwd) is not passed to session variable USER_PWD.
The variable USER is correctly passed, the variable USER_PWD is not!

Solution

The steps to set an OBIS session variable via a URL call utilizing the
instanceconfig.xml tag should be as follows

1. Create a session init block that will act as a ‘placeholder’ for the
session variable to be set via the url call – the variable can be set to
anything.

2. Set the ‘Enable any user to set the value’ option for the variable.

3. Add the following tag block to the instanceconfig.xml file anywhere
between the tags:

source=”url”
nameInSource=”SETVAR”/>
“TEST_VAR” should match the session variable name (case sensitive).

4. The following option will need to be appended to the OBI url passed -
&SETVAR=’variable value to pass. So a full example would be:

http://localhost:9704/analytics/saw.dll?Dashboard&nqUser=USER001&nqPassword=US

ER001&SETVAR=SomeValue

However, note that you cannot set the value of any System Security Session variable (specifically USER, PROXY, GROUP and WEBGROUPS) using any source method (e.g.: url, cookie, httpHeader) by design. Having this ability would open possible security breaches.

If you attempt to set the USER variable with the following instanceconfig.xml setting:

You will get the following error when using the url: http://localhost:9704/analytics/saw.dll?Dashboard&nquser=user1&nqpassword=public :

nQSError: 10018: Access for the requested connection is refused
nQSError: 1315 You do not have the permission to set the value of the variable :USER

Row level security using Initialization Blocks and Session Variables

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

Data level Security

To test the initialization block and session variables, perform the following steps:

1.	Click Manage > Security to open the Security Manager.
2.	Click Groups in the left pane.
3.	Right-click the white space in the right pane and select New Security Group.
4.	Name the group CountryManagers.
5.	Click the Permissions button to open the User / Group Permissions dialog box.
6.	Click the Filters tab.
7.	Click the Add button.
8.	Click the Customers presentation table.
9.	Click Select to add Customers to the User / Group Permissions dialog box.
10.	Click the Expression Builder button (three dots) on the right to open the Expression Builder (you may need to scroll to see the button).
11.	Select Logical Tables > Customers > Country and then click the Insert button to add Country to the formula.
12.	Click the = operator to add it to the formula.
13.	Select Session Variables > UserCountry and click the Insert button to add UserCountry to the formula as an argument in the VALUEOF()function.
14.	Click OK to close the Expression Builder. The filter is added in the User / Group Permissions dialog box.
15.	Click OK to close the User / Group Permissions dialog box.
16.	Click OK to close the Group dialog box. CountryManagers is added as a group in the Security Manager.
17.	Click Action > Close to close the Security Manager.
18.	Check in changes.
19.	Save the repository.
20.	Return to Answers.
21.	Log out of Answers.
22.	Test the initialization block by logging in to Answers as one of the users in the initialization block: Kurt, Keiko,Charles, or Karen.
23.	Create the following query: Customers.Country, Times.Calendar.Calendar Year, Sales Facts.Amount Sold.
24.	Click Results. The results of the query should correspond to the the user's country (Kurt = Germany, Keiko = Japan, Charles = United Kingdom, Karen = United States of America). The picture shows the results when the user = Kurt.
25.	Leave Answers open.

Create an Initialization Block for Session Variables

Create an Initialization Block for Session Variables

Session variables are like dynamic repository variables in that they obtain their values from initialization blocks. Unlike dynamic repository variables, however, the initialization of session variables is not scheduled.

When a user begins a session, Oracle BI Server creates new instances of session variables and initializes them. Unlike a repository variable, there are as many instances of a session variable as there are active sessions on Oracle BI Server. Each instance of a session variable could be initialized to a different value.

A session is an instance of a user running the client application. The session starts when the application is started and ends when the application is exited.

To create an initialization block for session variables, perform the following steps:

1.	Return to the SH repository, which should still be open in online mode in the Administration Tool.
2.	Click Manage > Variables to open the Variable Manager.
3.	Click Session > Initialization Blocks.
4.	Right-click in the white space on the right and select New Initialization Block.
5.	In the Session Variable Initialization Block dialog box, type setUser in the Name field.
6.	Click the Edit Data Source button to open the Session Variable Initialization Block Data Source dialog box.
7.	Click the Browse button and select SH > Connection Pool in the Select Connection Pool dialog box.
8.	Double-click Connection Pool or click the Select button to add the connection pool to the Session Variable Initialization Block Data Source dialog box.
9.	In the Default Initialization String field, type the following initialization string: select ':USER', case when upper(':USER') = 'KURT' then 'Germany' when upper(':USER') = 'KEIKO' then 'Japan' when upper(':USER')= 'CHARLES' then 'United Kingdom' when upper(':USER') = 'KAREN' then 'United States of America' end, 'CountryManagers', 2 from Dual
10.	Click OK to close the Session Variable Initialization Block Data Source dialog box. The initialization string is visible in the Session Variable Initialization Block dialog box.
11.	Click Edit Data Target to open the Session Variable Initialization Block Variable Target dialog box.
12.	Click New to open the Session Variable dialog box.
13.	In the Name field, type USER.
14.	Click OK to close the Session Variable dialog box.
15.	Click Yes when prompted about the USER session variable having a special purpose.
16.	The USER variable is added to the Session Variable Initialization Block Variable Target dialog box.
17.	Repeat the steps and add three more variables: UserCountry, GROUP, and LOGLEVEL. Click Yes when promoted about the GROUP and LOGLEVEL session variables having a special purpose. The order is important. The order of the variables must match the order of the values for the variables in the initialization string in the initialization block.
18.	Click OK to close the Session Variable Initialization Block Variable Target dialog box. The variables are displayed in the Variable Target section of the Session Variable Initialization Block dialog box.
19.	Click OK to close the Session Variable Initialization Block dialog box. The setUser initialization block is visible in the Security Manager.
20.	Click Action > Close to close the Variable Manager.
21.	Check in changes.
22.	Save the repository.