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:



This works correctly.