How-to: Enable Case Insensitive User Name Log-in with OBIEE 11g

In my previous article 'OID Authentication with Groups Stored in a Database Table' we discussed how to configure OBIEE 11g to authenticate against an LDAP directory (OID, MSAD) when group credentials are stored in a database table.

In summary, OBIEE 11g authenticates using an OID authenticator via OPSS (Oracle Platform Security Services), followed by a BISQLGroupAuthenticator - both configured in Weblogic Admin Console:

After configuring these authenticators (outlined here and here), you run a test by logging in a user who exists in your LDAP authenticator:

Example 1: Logging in with a valid username, valid password and correct username case sensitivity

• username: cookjr (username is stored as 'cookjr' in external database table)
• member of the following application roles: Authenticated User, BIConsumer, BIAuthor, BIAdministrator

From the above result, you can see the user cookjr has logged in and their application roles are correctly assigned.

Example 2: Logging in with a valid username, valid password and incorrect username case sensitivity

• username: cOoKjR (username is stored as 'cookjr' in external database table)
• member of the following application roles: Authenticated User, BIConsumer, BIAuthor, BIAdministrator
  

 Application roles after login attempt:

 Note how none of the custom application roles were applied to this login attempt even though it's the same user logging in.

WHY?
It is important to remember that the BISQLGroupAuthenticator used for group authentication is a driver that is used to generate Oracle SQL statements that you configure when setting up the authenticator in Weblogic Admin Console:

Keeping this in mind, also remember that Oracle SQL is case sensitive, meaning that:

select * from table where name = 'john' does not equal select * from table where name = 'jOhn'.

To configure the BISQLGroupAuthenticator to be case insensitive for username logins, you'll need to modify your SQL statements in the BISQLGroupAuthenticator 'Provider Specific' tab to use the 'UPPER' function, which takes the username passed in from the Answers login screen, converts it to all caps, and compares it against the UPPER(G_MEMBER) field.

Example:

• SQL List Groups: SELECT G_NAME FROM GROUPS WHERE UPPER(G_NAME) LIKE UPPER(?)
• SQL List Groups: SELECT G_NAME FROM GROUPS WHERE UPPER(G_NAME) LIKE UPPER(?)
• SQL Is Member: SELECT G_MEMBER FROM GROUPMEMBERS WHERE UPPER(G_NAME) = UPPER(?) AND UPPER(G_MEMBER) = UPPER(?)
• SQL List Member Groups SELECT G_NAME FROM GROUPMEMBERS WHERE UPPER(G_MEMBER) = UPPER(?)
• SQL Get Group Description (if description supported enabled) SELECT G_DESCRIPTION FROM GROUPS WHERE UPPER(G_NAME) = UPPER(?)

Using this logic, the user will now be able to log in using any username case sensitivity. Note that you may still have an issue where the user is able to login with an invalid (or no) password. This is most likely due to a legacy LDAP authenticator in your repository. I've outlined a solution for that in my article 'User able to log-in with invalid (or no) password'

 

keywords: OBIEE 11g, OBIEE Security, Weblogic, OPSS, OID, External Table Authentication,  Authentication, data level security, object level security