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,

Satya Ranki Reddy