Re: Username of a user connecting to a database via an application server

On 27 Jul 2006, Utpal.Dhar_at_gmail.com wrote:
> We have a J2EE application and we use Orion application server to
> connect to the database. All these connections to the database are
> made as a single user. Now it becomes difficult to find out what is
> the SID of a particular user because everyone connects as the same
> user. It becomes difficult to identify which user is doing what.
>
> Is there a way to overcome this.

Yes, but no way that won't involve some architecture overhaul around how you handle connections and credentials of your users, as well as tieing these to users in the database itself. (Your architects should be taken to task cause they should have thought of this long before the heavy coding started)

There is a concept in Oracle of "Proxy User", which your single user would be one. A proxy user's main purpose in life is really to "act on behalf of others". But, you need to code this in your connection classes cause it usually isn't supported by app server vendors, plus the database would need to know who the "on behalf of" is, so you'd need to create them as oracle users. (I don't know if the "behalf of" can work with a home grown users table).

There is LDAP support directly with Oracle, but you need to be running Oracle's Application server (Your app doesn't need to be executing in this container but the OAS needs to be executing). Then, you need to wire the LDAP up to Oracle with some sort of code (I think this uses context). I'm not sure if Oracle just gives this right out of the box.

There is also a homegrown way of doing this. Every time you get a connection to use for some sequence of operations, you can first issue a sys_context("userid behind the click") modification which lands who the user is on the context of the connection. But, yet again, the database needs to understand who this is.

At the end of the day, code in the database will need to understand "who is using the connection" if you want anything intelligent to happen once the wiring is set up. A simple example is triggers could pick up and log who did what operation.

-- 
Galen Boyer