Wednesday, May 22, 2013

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

11:07 AM

Share it Please
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

3 comments:

  1. If you have any of the below requirements, you need a Business Intelligence solution.

    Boost productivity
    Access to better and structured information
    Competent and smart decision making
    Results closer to established goals
    Better ROI

    Oracle Business Intelligence solutions help organizations of all sizes thrive by enabling them to discover new ways to strategize, plan, optimize business operations, and capture new market opportunities.

    About Evosys: Evosys is an Oracle Partner providing consulting services for implementation and maintenance of Oracle Applications. We are a market-focused, process-centric organization that develops and delivers innovative solutions to our customers, consistently outshining other market players.http://evosys.co.in/Business-Intelligence

    For any Oracle Service, Business Partnerships, Sales Referrals, Collaborations & Tieups, Please contact business@evosys.co.in & we shall contact you immediately.

    Contact Business@evosys.co.in

    ReplyDelete
  2. The evidence is clear that data breaches are a pervasive problem for most organizations in the United States today. Yet, despite negative repercussions in terms of cost outlays and reputation diminishment, many companies do not take appropriate steps to prevent the data breach or to prepare for and mitigate the risks when the inevitable occurs. In reality, it's rather easy by using secure vdr data room services.

    ReplyDelete
  3. In cloud hosting, you will get almost unlimited storage capacity. So you can store information. In this hosting, there is no need to worry about the storage space & increasing the current storage space.
    security-online

    ReplyDelete