LDAP logons vs. Oracle schema names w/OBIEE and fine-grain security

Question

Here's my scenario:

* Our MS Active directory holds my corporate credentials, and is tied in to the WLS LDAP.

* My MSAD login/password is mthompson/xyz123.

* I can sign in to OBIEE as mthompson/xyz123.

* My assigned ORACLE schema/password is hpotter/snape.

* We have established fine-grain security access in the database.

* When I fire up SQL*Plus, connect as hpotter/snape, and execute a query, my select statements are appropriately restricted so that I only see the data I am supposed to see.

* Our OBIEE Connection Pool uses a shared logon.

Situation: Because we have established fine-grain access in the database, I want to turn off the use of the shared login in the connection pool.  But if I do that, the connection pool tries to use my LDAP signin credentials (mthompson/xyz123) to connect to the database when I execute a query in OBIEE.  I can still sign in to OBIEE just fine, but the database knows nothing of mthompson, so the queries fail.

Here is what I would like to do, or something similar: I want to sign in to OBIEE with my LDAP credentials, just as I do now.  When I sign in, I want to run a session variable initialization block that will read a table that contains the Oracle schema that corresponds to my LDAP ID.  It would read that table, find mthompson in the LDAP_ID field, and return hpotter from the ORACLE_ID field, storing hpotter in the session variable.  I then want to use the value of that session variable as the schema that connects to the database via the connection pool.

Is this possible?  I am thinking that perhaps something on the Connection Script tab might do the trick.  If so, what SQL do I put in the connection script?  And what do I do about a password?

I know that potentially the answer is to enable VPD on the database object, and that's fine.  But I can't find any truly comprehensive documentation that walks through EVERY step beyond ticking the VPD checkbox to actually make it work.

So anyway, short version: LDAP_ID ==> ORACLE_ID ==> CONNECTION POOL ==> DATA!!

Thanks!

[Deleted User] · Answer

Mark T. wrote:Christian, what if this didn't require a table at all? What if we could use a field in Active Directory to hold the Oracle Schema name for the AD user? Would we be able to pass the value of that LDAP field into the User field on the connection pool as you described above (i.e. use a session variable)? If so, we could also do something similar with the password, again with the issue of keeping them in sync between the two fields.

Reading stuff from the LDAP through the WLS security providers with regards to more or less arbitrary information doesn't IIRC.

Mark.Thompson · Answer

Nah, that's too simple.  Gotta be harder than that!!

Very elegant, Christian.  Thank you.  Now I'll need to think about that password.  We certainly don't want to store it in the database in clear text, so encryption will be required.  But that's not a big deal.  The bigger hassle will be keeping that password column in sync with the database schema passwords.

Christian, what if this didn't require a table at all?  What if we could use a field in Active Directory to hold the Oracle Schema name for the AD user?  Would we be able to pass the value of that LDAP field into the User field on the connection pool as you described above (i.e. use a session variable)?  If so, we could also do something similar with the password, again with the issue of keeping them in sync between the two fields.

BUT... What if we made sure that the password for the AD user and the password for its associated Oracle Schema were always the same?  When a user changes his AD password, what if we could run a script that runs a SQL script to update the IDENTIFIED BY for the Oracle schema?  If we figured out a way to do that, could we then pass only the ORACLE_ID session variable to the connection pool, and let the password field on the connection pool just grab whatever the user typed into the password field on the OBIEE sign-in screen?  I realize we would need to do a bit of back-end coding/script that updates IDENTIFIED BY for the Oracle schema whenever the User password changes in AD, but I think that's doable.

How do you feel about the viability of that scenario?

[Deleted User] · Answer

Yes. Session variables. But no need for connection scripts. You would simply replace the credentials in your connection pool with "VALUEOF(NQ_SESSION.ORACLE_ID)" and VALUEOF(NQ_SESSION.ORACLE_ID_PASSWORD).