Thursday, June 28, 2012

OBIEE 11g Security Week : Connecting to Active Directory, and Obtaining Group Membership from Database Tables

In this final posting in the OBIEE 11g Security Week, we’re going to look at two common tasks that an OBIEE 11g administrator might have to perform:

Connecting the system to Microsoft Active Directory, so users can log-into the dashboard using their Windows Active Directory username and password, and retrieve group membership, and
Connecting the system to an external set of database tables that contain the group membership for users authenticated through Active Directory

Whilst OBIEE 11g comes with the embedded WebLogic LDAP server to hold users and groups, the license for this is restricted such that you can’t just move all your other user details from other applications into the LDAP server. Realistically, you wouldn’t want to do that anyway as it’s likely you’ve got a corporate directory somewhere that you want to leave user and group details in, with OBIEE instead just connecting to it as an authentication and authorisation source. Luckily, now that OBIEE 11g uses WebLogic and Fusion Middleware’s Oracle Platform Security Services framework, connecting to external directories such as Active Directory is pretty straightforward, especially with recent versions of OBIEE such as 11.1.1.6 that do this all a lot smoother now.
So in this example, we’ve got an Active Directory server running on the host pdc.gcbc.com, that contains three users:

ADBISystemUser, which will be used as the principal that OBIEE uses to connect to the Active Directory server
Anne Administrator, a user on Active Directory who wants to have administration rights in the Presentation Server and BI Server
AD User, another user that just wants to be able to create analyses and dashboards

These users are organised into three groups in the AD server:

ADBIAdministrators, analogous to the BIAdministrators group in the WLS LDAP server
ADBIAuthors, ditto
ADBIConsumers, ditto again

Now if you search the internet and Oracle docs for instructions on how to integrate OBIEE 11g with Active Directory, there seems to be about as many different ways to do it as there are sets of instructions. A lot of this is because Active Directory is highly-configurable, and a lot depends on how much you want to replace, or just work alongside, the existing WLS LDAP server. In this example, our objective is to keep the WLS LDAP server and the user accounts within it (including the biadmin administrator account), but then make it possible for Active Directory users to also log in, and be assigned to the standard application roles that the WLS LDAP users have. Keeping the WLS LDAP users and administration account considerably simplifies the configuration process, though you might still want to go the full way if you intend to completely replace WLS LDAP with Active Directory. For now though, we’ll have the two running alongside each other.
Looking at the Active Directory Users and Configuration utility, we can see the three users we’re interested in:
Sshot 1

And the three groups:
Sshot 2

The groups have just got those users as members, and the users are just regular AD users, including the ADBISystemUser account. Internally, the domain is called gcbc.com, with the users held in the Users directory and groups in the Builtin directory – fairly standard stuff.
So let’s go into the WebLogic Server Administration Console (http://[machine_name]:7001/console) and start configuring the system for Active Directory integration.

Log into the WebLogic Server Administration Console as an administration user, for example biadmin/welcome1
When the Admin Console homepage is displayed, click on the Security Realms menu item on the left-hand side, and then then on myrealm when the link is shown.
You are now going to alter the domain configuration, so press the Lock and Edit button. Then, click on the Providers tab in the Settings for my realm page.
Active Directory integration is achieved through registering a new authentication provider, using the Active Directory provider type. To register this, press the New button just under the Authentication Providers label.
The Create a New Authentication Provider page will be displayed. Give the provider a name (for example, ADProvider) and select ActiveDirectoryAuthenticator as the Type.
Now click on this new authentication provider in the list, and then when the Settings for ADProvider page is shown, set the Control Flag to SUFFICIENT, and press Save.
Then, click on the Provider Specific tab, and enter the following details for your Active Directory installation, amending the settings as appropriate for your AD server. Host : pdc.gcbc.com
Port : 389
Principal : CN=ADBISystemUser, CN=Users, DC=gcbc, DC=com
Credential : Welcome1
Confirm Credential : Welcome1
User Base DN : CN=Users,DC=gcbc, DC=com
User Name Attribute : cn
User Object Class : user
Group Base DN : CN=Builtin, DC=gcbc, DC=com
GUID Attribute : objectguid

Then, press Save to save and close the page.
Now go back to the list of providers, and click on the DefaultAuthenticator one. With the Configuration > Common sub-tab selected, set the Control Flag to OPTIONAL, and press Save.
Then, again with the list of authentication providers displayed, press the Reorder button and then change the order of the providers so that ADProvider is first, followed by DefaultAuthenticator and DefaultIdentityAsserter.
You’re now at the point where you can restart your BI domain and see the new users and groups within the WebLogic Admin Console. To do this, restart the BI Domain (the Admin and Managed Servers), and once complete, log in again into the WebLogic Admin Console and select Security Realms > myrealm > Users and Groups > Groups. You should then see the Active Directory users listed alongside the WLS LDAP ones.
Similarly, you should see your AD groups under the Groups tab. Note that you can’t edit these AD users and groups from within the WebLogic Admin Console, nor can you create new AD users here – to do that, you’d need to use Active Directory’s own console and tools.
Next we will switch over to Enterprise Manager, first to configure Fusion Middleware’s Oracle Platform Security Services to accept users and groups from both WLS LDAP and Active Directory when logging into the dashboard, and then we’ll map the Active Directory groups to their equivalent application roles. Log into Enterprise Manager, and select the WebLogic Domain > bifoundation_domain menu item on the left. Right-click on it and select Security > Security Provider Configuration. When the Security Provider Configuration page is displayed, expand the Identity Store Provider area and press the Configure… button.

The Identity Store Configuration page will then be displayed. Press the Add button next to the Custom Properties area, and add a new custom property with these settings :
Property Name : virtualize
Value : true
Press OK to close the page.
Now right-click on the Business Intelligence > coreapplication entry in the left-hand side menu, and select Security > Application Roles. As you may have done with the application role settings in yesterday’s postings, edit the BIAdministrator, BIAuthor and BIConsumer application roles so that the new Active Directory groups are listed as members.
Doing this ensures that the Active Directory users get the same type of Presentation Server and repository privileges as WLS LDAP users, but they won’t have administration access to WebLogic or Enterprise Manager.
You can, if you want, grant these users the same sorts of domain administrator rights as the WLS LDAP users, and you can indeed remove all of the WLS LDAP users and groups and move over to Active Directory entirely. But in most cases I see, this level of integration is sufficient, as it still allows the OBIEE administrators to control their own user accounts and privileges.
You should now be able to log in as one of the Active Directory users. In the screenshot below, the AD User user has logged in, and has been granted the BIAuthor role through their membership of the ADBIAuthors Active Directory group. If Anne Administrator, an Active Directory user assigned to the ADBIAdministrator group, logs in she will be able to administer the Presentation Server permissions and privileges, but she won’t be able to log into Enterprise Manager to change the repository, for example.

So what we’ve seen here so far is OBIEE 11g connecting to Active Directory, to retrieve in addition to the existing WLS LDAP users and groups, users and groups from this directory. But what if the groups in Active Directory bear no resemblance to the groups and application roles that you’d like to organise users into? Because you can map LDAP groups to roles in Enterprise Manager, it’s possible to “reshape” group membership to fit your BI requirements, but often organisations will solve this problem by creating a couple of database tables on a spare database, and use those to define which users belong to which group.
Now this is something that was done a lot in OBIEE 10g – using Active Directory to authenticate someone, then retrieve their group membership through a separate database table lookup – but you’re not supposed to mix WLS provider-based authentication with old-style init block authorisation, so how will this work, if, for example we’ve got a couple of tables called GROUPS and GROUPMEMBERS that detail which user belongs to which group:
Sshot 10

To handle this type of situation, OBIEE 11.1.1.5 (through the patch associated with Bug 11667221 / ARU 14523400) and OBIEE 11.1.1.6 (by default, though you need to copy the BISecurityProviders.jar file from [middleware_home]/Oracle_BI1/bifoundation/security/providers to [middleware_home]/wlserver_10.3/server/lib/mbeantypes, and then restart the Admin Server before it’s available), has a new authenticator called BISQLGroupProvider that can do this for you.
To use this new authenticator with either OBIEE 11.1.1.5 or 11.1.1.6, you’ll need to perform the following tasks:

Configure a data source within WebLogic that the provider will use to connect to the schema and tables described above
Configure a BISQLGroupProvider with the SQL SELECT statements required to access these tables
Re-order your authentication providers, and if you’ve not done so already, enable the virtualised identity store adapter (we did this infact in the previous example)
Configure a database adapter so that the Identity Store APIs can map your groups into application roles.

Full details of this new authenticator are in a document on My Oracle Support, Doc. ID. 1428008.1. So, with some new users added to my Active Directory server and corresponding entries in the two database tables, so that these users are assigned to groups such as QA Managers, HR Managers and SF Managers, let’s get this set up.

If you’ve not done so already, apply the above patch to OBIEE 11.1.1.5 if that’s the version you’re running, and then copy the BISecurityProviders.jar file as directed above (this applies to 11.1.1.6 as well, which already has the file without needing the patch applied). Once done, restart the WebLogic Admin Server.
Now you will configure the data source and BISQLGroupProvider. To do so, use your Web browser to navigate to the WebLogic admin console (http://[machine_name]:7001/console), and then press the Lock and Edit button. From the left-hand menu select Services > Data Sources. Then, from the Data Sources list, press New > Generic Data Source.
Then, on the Create a New JDBC Data Source page, enter or select the following details:
Name : BIDatabaseGroupsDS
JNDI Name : jdbc/BIDatabaseGroupsDS
Database Type : Oracle (for example)
On the following page, select the Database Driver, and then at the Connection Properties page, enter the connection details to your schema and database, for example:
Database Name : orcl
Host Name : obisrv1c
Port : 1521
Database User Name : gcbc_bi_groups
Password : password
Confirm Password : password
Once entered, test the connection on the next page, on the next page deploy the datasource to all of your WebLogic servers, then press Finish, and then press the Activate Changes button.
Next you will create a BISQLGroupProvider against this JDBC data source. The SQL that’s in the SELECT statements below is particular to the tables that I diagrammed earlier, and you’d need to change it if your table structure was different. Start by pressing the Lock & Edit button, to start editing the domain configuration. Then, select Security Realm > myrealm > Providers from the menus and tabs.
With the Providers tab selected, press the New button to create a new authentication provider. When prompted, enter MySQLGroupProvider as the Name, and select BISQLGroupProvider as the Type.

Then, press OK to close the page, and then click on the new MySQLGroupProvider authentication provide to display its settings page. Select the Provider Specific tab, and then type in the name of the JDBC datasource that you created earlier, i.e. jdbc/BIDatabaseGroupDS.
If you used the same table and column names as in the diagram before, the SQL settings for this provider will not need to be changed. If you did alter the table or column names though, update the SQL commands to reflect your actual database structure.

Once complete, press Save.
Now go back to the list of providers, and Reorder them so that the new MySQLGroupProvider is at the top of the list.
If you have not done so already, set the virtualized=true flag in the Identity Store Provider settings in Enterprise Manager - see the steps earlier in this posting for details on how to do this. Once you’ve done this, press the Activate Changes button and then stop, and then start your entire BI system, so that all WebLogic and OBIEE components restart.

Next, you are going to create an XML file that will be an adapter template for the database adapter, and will be used by the Identity Store APIs to map groups to application roles. Use a text editor and call the file bi_sql_groups_adapter_template.xml, and substitute your own LDAP details into the <param name=”ReplaceAttribute”value=”uniquemember={cn=%uniquemember%,cn=Users,dc=gcbc,dc=com}”/>
section, and also the:
<objectClass name=”groupofuniquenames” rdn=”cn“>
section. In addition, if you have used different database table names and columns, you’ll need to adjust the SQL statements in the XML file accordingly.

<?xml version = '1.0' encoding = 'UTF-8'?>

<adapters schvers="303" version="1" xmlns="http://www.octetstring.com/schemas/Adapters"

xmlns:adapters="http://www.w3.org/2001/XMLSchema-instance">

    <dataBase id="directoryType" version="0">

      <root>%ROOT%</root>

      <active>true</active>

      <serverType>directoryType</serverType>

      <routing>

         <critical>true</critical>

         <priority>50</priority>

         <inclusionFilter/>

         <exclusionFilter/>

         <plugin/>

         <retrieve/>

         <store/>

         <visible>Yes</visible>

         <levels>-1</levels>

         <bind>true</bind>

         <bind-adapters/>

         <views/>

         <dnpattern/>

      </routing>

      <pluginChains xmlns="http://xmlns.oracle.com/iam/management/ovd/config/plugins">

         <plugins>

            <plugin>

               <name>VirtualAttribute</name>

<class>oracle.ods.virtualization.engine.chain.plugins.virtualattr.VirtualAttributePlugin</class> <initParams>

                  <param name="ReplaceAttribute"

value="uniquemember={cn=%uniquemember%,cn=Users,dc=gcbc,dc=com}"/>

               </initParams>

            </plugin>

         </plugins>

         <default>

            <plugin name="VirtualAttribute"/>

         </default>

         <add/>

         <bind/>

         <delete/>

         <get/>

         <modify/>

         <rename/>

      </pluginChains>

      <driver>oracle.jdbc.driver.OracleDriver</driver>

      <url>%URL%</url>

      <user>%USER%</user>

      <password>%PASSWORD%</password>

      <ignoreObjectClassOnModify>false</ignoreObjectClassOnModify>

      <includeInheritedObjectClasses>true</includeInheritedObjectClasses>

      <maxConnections>10</maxConnections>

      <mapping>

<joins/>

         <objectClass name="groupofuniquenames" rdn="cn">

<attribute ldap="cn" table="GROUPMEMBERS" field="G_NAME" type=""/>

            <attribute ldap="description" table="GROUPMEMBERS" field="G_NAME" type=""/>

            <attribute ldap="uniquemember" table="GROUPMEMBERS" field="G_MEMBER" type=""/>

         </objectClass>

      </mapping>

      <useCaseInsensitiveSearch>true</useCaseInsensitiveSearch>

      <connectionWaitTimeout>10</connectionWaitTimeout>

      <oracleNetConnectTimeout>0</oracleNetConnectTimeout>

      <validateConnection>false</validateConnection>

   </dataBase>

</adapters>

Now, open a command-prompt session in the server running Oracle Business Intelligence, and enter the following commands, adjusting for your particular environment and LDAP settings:
cd c:\Middleware\oracle_common\bin
set ORACLE_HOME=c:\Middleware\Oracle_BI1
set WL_HOME=c:\Middleware\wlserver_10.3
set JAVA_HOME=c:\Middleware\jdk160_24
libovdadapterconfig -adapterName biSQLGroupAdapter -adapterTemplate bi_sql_groups_adapter_template.xml -host localhost -port 7001 -userName biadmin -domainPath c:\Middleware\user_projects\domains\bifoundation_domain -dataStore DB -root cn=Users,DC=gcbc,DC=com -contextName default -dataSourceJNDIName jdbc/BIDatabaseGroupDS
When prompted, enter the password for the Administration Server. Once complete, you should see the message:
Adapter created successfully: biSQLGroupAdapter

Now stop and restart the entire BI system. During the restart, you will see an error message saying that the connection pool you just created is unusable – this is expected and will not cause a problem. Now, go into Enterprise Manager and create a matching role for one of your new database-defined groups. You should see the new groups appearing when you go to add a group to the application role – if not, check the console output for the WebLogic Server for any diagnostic messages.
Finally, you’re now ready to test out the new roles and groups. Restart your entire BI system, then log in as one of the users with groups in the database tables, and then view the list of roles assigned to the user. You should see your new roles, corresponding to the group settings in the database tables, assigned to the user – in this case, the HR Manager role.

So that concludes my look this week at OBIEE 11g security.

Thanks
Satya Ranki Reddy

Column Level Security in OBIEE

Authorization can be done in two ways

Object level Security :In this we can restrict dashboards, pages, sections, tables
Data level Security: Here we can restrict access to values in columns.
Column level Security: Giving access to certain columns in a table.

Here we are having three users’ duser1, vuser1.

Groups for which the users belong to-

duser1 - DISNEY

vuser1 - VERIZON

These groups are created in repository under Manage->Security->Groups

Object & Data level security for Disney group:

When duser1 logs in and he belongs to DISNEY group who can view only Financial Dashboard (Object level security) and data in the column, based on the access given to him (Data level security). He can view only few clients (Brand 1). Restricting data in Client column.

Fig (1) : Restricting data from GROUP level

Object level security

1) Dashboard/Page/Section level security

Create these groups in Answers.

Admin->Manage Presentation Catalog Groups and Users->Create new catalog group

Give permissions to dashboards for each group as per requirement. (Object level)

Restricting the groups to a dashboard

Settings-->Administration-->Manage Interactive Dashboards-->Click on Dashboard permissions

After clicking on permissions tab then assign the respective group to your dashboard

When a duser1 logs in he will see only Financial dashboard

Column level security:

In the presentation layer of repository we need to restrict tables and columns for those groups

Double click on the table->Permissions tab->General

Check show all user/groups and change check box read to tick or cross mark. Now the users under that group cannot view that table in answers.

We have a subject area called Sample Sales Reduced. In this we restricted Other

Dimensions table. When duser1 logs in, he cannot see table from presentation

view/answers. In the below picture we can see Other Dimensions table in

Presentation Layer of repository but its not present in Answers

In the same way i restricted particular column in a table to this

user. Here we restricted No of customers,employees, orders these three columns from

Facts other table for this users. When the user login's he cannot view those columns, but i can see these columns in repository.

In the below picture we can see Facts Others with different columns in Answers and Repository

NOTE:

If a report is created using a column which is having access to one user and no access to other user, then the user who doesn’t have the access cannot view report they will get ERROR to avoid the error message you change the NQSCONFIG.INI file

PROJECT_INACCESSIBLE_COLUMN_AS_NULL = NO; under security in NQSConfig.INI Change it to YES so that he can view the report properly without that column.

Thanks

Satya Ranki Reddy

External Table Authentication OBIEE 11g

In OBIEE 11g how the security system works when a user login to the WLS/analytics.

The default security mechanism provides controls to manage users and groups, permission grants and credential store. Following are the security controls that are available after the installation.

1.An embedded LDAP server in WebLogic available to store users and groups known as

“Identity Store”

2.A file to store the permission grants information known as the “Policy Store”

3.A file to store user and system credentials for inter process communication known as the

“Credential Store.

Order of Authentication:

The Oracle BI Server populates session variables using the initialization blocks in the desired order that are specified by the dependency rules defined in the initialization blocks.

If the server finds the session variable USER, it performs authentication against an LDAP server or an external database table, depending on the configuration of the initialization block with which the USER variable is associated.

Authentication against the identity store configured in Oracle WebLogic Server Administration Console occurs first, and if that fails, then initialization block authentication occurs.
If you configure your external table authentication as in OBIEE 10g when the session variable USER is associated to the initialization block and LDAP server fails to get the respective user then the user's will authenticate(Identify store) over database(table).

Dont forgot to create Catalog group as we do normally in 10g

In 11g Analytics - Administration- Security - Manage Catalog groups -- (+) to add new groups and set permissions to the catalog folders w.r.t groups/users.

Security

External table authentication and row-wise initialization in OBIEE

There is a topic in Oracle Business Intelligence Server Administration Guide about security (chapter 15 - Security in Oracle BI).

Authentication is process where Oracle BI Server checks username and password after user signs on, to verify that the user have necessary permissions to login and retrieve the data.

Authentication types in OBIEE: LDAP authentication, external table authentication, database authentication on page, etc.

I'll explain how to use external table authentication and how to work with row-wise initialization.

There is a little explanation in table format where to use repository, session and presentation variables and how to call them: OBIEE variables overview.

In my situation I have defined some users in Oracle BI server repository and other in external table as well. I know that this isn't good example in reality. But we'll see how OBIEE works with different types of authentication together.

Part1 - External table authentication

Oracle BI Server users and groups:

UserC, UserN -> Users (A-M)
UserSC -> Special Group

External table authentication (external users):

*We need to import this table to physical layer.

This is taken from chapter 15 - Security in Oracle BI:

So we created users separately from Oracle BI Server (groups are already inside BI Server).

Initialization block:

:USER and :PASSWORD represents a username and password that the user entered in start page fields.

Now, when a user begins a session we will populate system session variables (USER, GROUP, DISPLAYNAME and LOGLEVEL) with values defined in our external table:

Now, before going to a Answers we'll do one more thing. Put some filters to users and groups for restricting data for particular set of tables to test users/groups permissions. I use SALES fact table.

UserC see all the data, but group Users (A-M) see only SALES data for Tele Sales channel. Users (N-Z) see only Catalog channel data in SALES. Privileges granted explicitly to a user have precedence over privileges granted through
groups so UserC does not have his group filter.

Now, lets test external table users.

First log on with UserA and create report:

Results:

UserA is added to a group and he see only Tele Sales channel data (permissions inherited from Users (N-Z) group). Same restriction is for UserB.

Logon as UserZ now.

Results:

Only Catalog channel data in SALES (permissions inherited from Users (N-Z) group).

Sign up as UserN:

UserN does not exist in external table and is populated from Oracle BI Server security. So we can retrieve USER, GROUP, DISPLAYNAME (if we set it) variable but not LOGLEVEL:

For some reasons we cannot see LOGLEVEL although we set it on user properties in Adnimistrator:

Remove VALUEOF(NQ_SESSION.LOGLEVEL) from column expression in Answers.

Results:

DISPLAYNAME is not set.
Only Catalog channel data selected from SALES.

Logon as UserC and remove LOGLEVEL from expression.

Results:

There is no inherited permissions for UserC from his group Users (A-M, privileges granted to a user explicitly have precedence in relation to privileges granted to a group).

NQQuery.log:

Part2 - External table authentication and row-wise initialization

In part 2 we use only users that we defined from external table. External table we use only for user authentication and if user pass check then we use another database table for retrieving session variables in row-wise initialization.

What is row-wise initialization?

The row-wise initialization allows us to create and set session variables dynamically. It is similar to external table authentication except here we have fixed number of columns (name, value) and each row for a particular user represents different name (variable name) and associated value (column value).

My example use this database table:

I know that this is not in practice, but I used hybrid solution for demonstration. First I check my user after log in, and for that I use the same table as in part 1 for authentication. This is first initialization block and I'll populate only USER system variable:

And after that we use another block with row-wise initialization. For current user we pick up column SESSION_VARIABLE_NAME from OBIEE_ROW_WISE_INIT. This column represents variable name, system or non-system. So this row-wise will dynamically create session variable and set it with value in SESSION_VARIABLE_VALUE column:

For UserA there are four variables to set dynamically. One of them is non-system, MARRIED.

This block depends on the results of the previous block. This can be set in execution precedence part of block:

We could also use VALUEOF(NQ_SESSION.USER) instead of :USER.

For now we have USER variable set from the first block and other variables set from row-wise dynamically in second block. All this happens in session level, after login.

In the next step we add another block with row-wise but we set dynamically session variable that we hardcoded in select statement in block:

Select:

select 'USERS_IN_GROUP', username
from hr.obiee_row_wise_init
where session_variable_name='GROUP'
and session_variable_value ='VALUEOF(NQ_SESSION.GROUP)'

USERS_IN_GROUP represents a list of values (we can use this as filter in Answers) of all users that have same group as the user that we use for login.

Note that there is also execution precedence.

Test, login as UserA.

USERS_IN_GROUP returns UserA and UserB in the list for Users (A-M) group, according to our OBIEE_ROW_WISE_INIT table.

Create a report in Answers with two expressions:

Expression 1:

case
when CHANNELS.CHANNEL_DESC='Catalog' then 'UserA'
when CHANNELS.CHANNEL_DESC='Direct Sales' then 'UserB'
else 'UserN'
end

Results without filter:

If we add row-wise session variable USERS_IN_GROUP as list of values in the filter to expression 1 column:

Filtered results:

Variable Concepts.

Before starting with this, we also have to understand the concepts of Session Variables and Row-wise initialization as they act as the baseline for this approach.
What is a Session Variable?
A Session variable is a variable which stores the values fetched from the initialization block and it gets initialized when a new session starts. Whenever, a user starts a session, OBIEE server creates a new instance of the session variable and initialize it with a value. This value is retained in the Session Variable till the session ends.
What is Row-wise Initialization?
As per what the name suggests, Row-wise initialization feature allows you to assign a list of values(present in some database table) to a single session variable. It also allows you to create multiple session variables dynamically and set their values when the session starts. This feature is present in the Session Variable initialization block in the RPD and is used to initialize a variable. The Session variable initialization blocks run for every new session created.
How to create Row-wise Session Variables?
Row-wise Session variables are dynamic session variables which are created dynamically when the Session Variable initialization block initializes. Below examples shows how to create Row-wise Session variable dynamically.

Example 1- Create multiple dynamic session variables and initialize them with values
In the RPD, under variables, create a new Session Variable Init Block ‘Test’ with the following query-
Select ’1′,’RJ’ FROM DUAL UNION Select ’2′,’SJ’ FROM DUAL

Then, assign a connection pool to the init block and set the Variable Target as Row-wise Initialization.
Now, when this session variable init block will run, it will create 2 dynamic session variables ’1′ and ’2′ with the values ‘RJ’ and ‘SJ’. Below images show how to display the same in Answers:

Diag1 Diag2

Example 2- Create single Session variable and initialize it with multiple values
This technique is generally used to assign multiple security groups to a single user while implementing Data Level Security. The below example shows how assigning multiple values to a single session variable can used in implementing Data Level Security:
Create a Session variable Init block named ‘Test1′ and add the below query.
Select ‘VAR’,'VAR1′ FROM DUAL UNION Select ‘VAR’,'VAR2′ FROM DUAL

This will assign values ‘VAR1′ and ‘VAR2′ to dynamic Session variable ‘VAR’ and when we will test this Init block, the result will be displayed in multiple rows. like:

VAR	VAR1
VAR	VAR2

Create another Session Variable Init block named ‘Test3′ and add the below query in that. Then, assign this Session Variable Init block to a variable ‘NEW’:
Select ‘VALUEOF(NQ_SESSION.VAR)’ FROM DUAL

Now, the point to note here is that, in previous scenarios we haven’t assigned the Init block a variable but in this scenario we are doing it. This is required because, since, we have to assign multiple groups to a single user, we should get the data in a single row instead of multiple rows. So, assigning the ‘Test3′ Init block to a variable ‘NEW’ and fetching the values of ‘VAR’ session variable in the query will bring the data in a single row in the ‘NEW’ session variable. The same can be seen in the below images:

So, the above images show that when we test the Session Variable Init block ‘Test3′ the values ‘VAR1′ and ‘VAR2′ are stored in the Session variable ‘NEW’ in a single row. The other image shows that the same can be used and displayed in answers.
This variable then can be further used for Data Level Security as filters can be applied on users and groups in RPD based on this variable. And this is how we make use of Row-wise initialization for Data Level Security.

List of all System Session Variables in OBIEE

Please find the list of all System/Predefined Session Variables that will be initiated whenever we login to OBIEEportal.

NQ_SESSION.USER,
NQ_SESSION.USERGUID,
NQ_SESSION.GROUP,
NQ_SESSION.GROUPGUIDS,
NQ_SESSION.WEBGROUPS,
NQ_SESSION.REALM,
NQ_SESSION.REALMGUID,
NQ_SESSION.TOKENS,
NQ_SESSION.REQUESTKEY,
NQ_SESSION.PORTALPATH,
NQ_SESSION.DISPLAYNAME,
NQ_SESSION.SKIN,
NQ_SESSION.STYLE,
NQ_SESSION.EMAIL,
NQ_SESSION.CURRENCYTAG,
NQ_SESSION.ACTUATEUSERID,
NQ_SESSION.TIMEZONE,
NQ_SESSION.DATA_TZ,
NQ_SESSION.DATA_DISPLAY_TZ,
NQ_SESSION.PROXYLEVEL,
NQ_SESSION.USERLOCALE,
NQ_SESSION.USERLANG,
NQ_SESSION.PREFERRED_CURRENCY

Thanks,