External Table Authentication - Encrypted Password

I am trying to blog on this from long time now i have some time to write a blog :) how to store the encrypted passwords in database for External Database Authentication

Step1 : Create a function in Database by executing below code.

create or replace FUNCTION obiee_pass(p_login IN VARCHAR2,p_password IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN LTrim(To_Char(Dbms_Utility.get_hash_value(Upper(p_login)|| '/' || p_password,10000,Power(2,30)),RPad('X',29,'X') || 'X')); END;

Step2 : create a table with columns Login,Password,Loglevel,Group

we have to insert the values to the above table obiee_users

with the following statement
insert into OBIEE_USERS values('User3',obiee_pass('User3','User3'),'Group2',2);

Step3: In rpd we need to configure Click at step7 i..e. Initialization Block we need to change with following SQL CODE

SELECT USER_NAME,LOGLEVEL,GROUP_NAME FROM OBIEE_USERS where 
USER_NAME=':USER' NQS_PASSWORD_CLAUSE(and password= obiee_pass(':USER',':PASSWORD'))NQS_PASSWORD_CLAUSE

Step4: Login with users you will able to login to dashboards normally as before and apply the security as usual 

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. 

Oracle BI EE 10.1.3.3.3/2 – Public Reports and Dashboards – Bypassing authentication

Another common question that keeps circulating is, “how do we bypass login to certain BI EE Dashboards?”. The most obvious answer to this question is to pass the username and password through the url. But certain security requirements/standards in some companies do not allow passing of passwords through the URL. So, lets look at a simple approach wherein some dashboards can be seen without passing the passwords through the url. The idea is pretty simple. Identify a user, for example PUBLIC, that would get direct access to certain dashboards. This user should not exist in the BI EE repository. All the other users would exist in the BI EE repository. So, the first step to achieve this is to create a simple init block with the below shown sql.

1	select ':USER' from dual where upper( ':USER' ) = upper( 'Public' )

Make the above init block to set the USER system session variable. Also, check the “Required for Authentication” check box. The init block can connect to any oracle database connection pool.

     

Now, if you use the below shown go url, the dashboards would be shown. As you see we are not passing the passwords through the go url.

http://localhost:9704/analytics/saw.dll?Dashboard&nquser=Public

     

The above approach uses the concept of external table authentication. Basically, the init block checks for the PUBLIC user and ensures that the user is authenticated without even checking for the password. All the reports/dashboards that can be viewed by the “Everyone” privilege can be accessed by this PUBLIC user. Very simple but can be used in certain situations.

Configuring Scheduler and Ibots when using External Table Authentication

I was recently at a client site configuring scheduler on their development environment to set up ibots . The scheduler was configured as per the documentation and the Scheduler service started up just fine. We then went on to create ibots and selected multiple users as the recipients of the Ibot.  

However, the ibots executed successfully only for the Administrator and threw the following error for all other recipients.

+++ ThreadID: 334 : 2009-04-12 21:44:43.359
[nQSError: 77006] Oracle BI Presentation Server Error: A fatal error occurred while processing the request. The server responded with: Authentication Failure.
Error Codes: IHVF6OM7:OPR4ONWY:U9IM8TAC

Odbc driver returned an error (SQLDriverConnectW).
State: 08004.  Code: 10018.  [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused.
[nQSError: 43001] Authentication failed for emp 1 in repository Star: invalid user/password. (08004)

I should point out at this point that we had External Table Authentication configured for authenticating users into the application using the following initialization block:

select login from employee_d where login=’:USER’ and auth_id= ‘:PASSWORD’

The fix for this issue though available in the  documentation, could be easily overlooked.

The solution is to modify your Authentication initialization block as follows:

select login from employee_d where login=’:USER’ NQS_PASSWORD_CLAUSE (and auth_id= ‘:PASSWORD’)NQS_PASSWORD_CLAUSE

It is important that you use the NQS_PASSWORD_CLAUSE term in order to successfully schedule Ibots for users who are authenticated using external table authentication.