Basic External table authentication with password encription

Hello

Today, I want to talk about some real basic stuff in OBIEE 10G, which is external table authentication. I know there are many similar posts out there on the internet, I still want to post my own version. This is not to show off or anything, just so I don't have to memorize these things in the future if the article is kept in my own space. I know that we are talking about 11G nowadays, everybody is talking about cool things they can do with 11G, google maps, unstructured data and so on. I like to get into those things too, in fact I have. But I still believe that the understanding of basic security approach in OBIEE, the usages of session variables, the standard process of user management can benefit in the long run.

So without further digression, the external table authentication is a way to manage users and user groups in an external DB table for OBIEE to authenticate user's accessibility to the application. LDAP Authentication on the other hand, is to store user information in LDAP server. For external table authentication, the definition of the table isn't always the same from company to company. But the basics are the same. You typically will have 3 tables:

First one is user table that stores user ID and other user Infos;

second one is user group table that stores user group information:

Now because 1 user can belong to many groups and 1 group can have many users, they naturally form n:n relationship. Therefore, we have a third table which map each user to user group:

Now that we have the structures ready, we will implement external table authentication in OBIEE by creating session variables. My first initialization block is 'Isauthentication' which is associated to several session variables:
User
Displayname
Loglevel
Email
Password

The follow screenshot is misleading, it should've been printed 'create 5 session variables for this block':

The initialization string for this block is:
(The bold text are the variable names with just created)



select USERNAME, DISPLAYNAME, LOGLEVEL, EMAIL,PASSWORD from IS_SEC_USER
where UPPER(USERNAME) = UPPER(':USER') and PASSWORD =  IS_GET_MD5_VAL(':PASSWORD')

This query basically goes into user table and find all of the record where column 'username' = the value of 'USER' variable and column Password = the value of the 'PASSWORD' variable.  Due to the nature of session variable, these values will be assigned when user initiates a session by attempting to log in.  The test result of this initialization block is shown:

Now what is 'IS_GET_MD5_VAL'? It is a DB function that encrypts the password in user table:

This is just an fyi, you don't have to bother about it:


create or replace FUNCTION IS_GET_MD5_VAL (p_in VARCHAR2)
   RETURN VARCHAR2
IS
   l_hash   VARCHAR2 (2000);
BEGIN
   l_hash :=RAWTOHEX(UTL_RAW.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.md5 (input_string=> p_in)));
   RETURN l_hash;
END;

So the password in the user table looks like this:

 Anyways, let's move on to the next initialization block that we will create, which in my case is authorization.
The purpose of this is to get all of the user groups that the users belong to. The previous initialization block gets the users from user table, therefore, it should take precedence over this one.

Note the row-wise initialization is needed here. It will return all of the rows that the user belong to, instead of a single value, which variable typically does.

The initialization string is this:


ISAuthorization= select 'GROUP', UG.GROUPNAME from IS_SEC_USER_USERGROUPS FFF, IS_SEC_USER U, IS_SEC_USERGROUP UG where
UG.ID = FFF.USERGROUPS and
U.ID = FFF.IS_SEC_USER and
UPPER(U.USERNAME) = UPPER(':USER')

This is clear that this query joins all of the 3 tables and find the right group that any user belong to. This is executed after the previous initialization block.

So the idea here is that when users log on, the first session will get the user's ID and password from user table if it exist, then it will join to the group table and find out what are all of the groups this user belong to. So not only user is able to log into OBIEE, but also it's privilege will also be defined based on the group privilege. We can define user/group privilege in OBIEE later.

So from previous screenshot we know there is a dummy user call 'Admin', although the password shown in the table is encrypted, but we created its password to be 'Admin'.

Now lets log on to OBIEE via Admin/Admin:

Now the result is:

Thanks

Until next time