Re: A question of tracking application users

On 15 Jun 2006 04:16:26 -0700, "nirav" <shivam71_at_gmail.com> wrote:

>Hi Experts,
>
>I have a very interesting issue on the above. The scenario is:
>
>We have a web application with oracle as the data tier. All application
>users who log to the application use a common database account... (E.g.
>if the application has 60 users, (named m, n etc) when they connect to
>the database, at the database level the schema is only one (i.e. abc).
>So when user m connects then also the database user is abc and when n
>connects then also the database user is abc. (Ie. In the database the
>session is created with userid of "abc")
>
>Now we want to implement auditing and we need to track which
>application user has made what change. i.e. if user m changes a table
>emp and adds a record then in an audit table a row should be added
>WHICH HAS THE USER M. similarly if N does it then the row for user N
>has to be there. But how do we track the application user because at
>the database level we do not have application user at all!

Shortly, except if you are going to use globally authenticated end-users, coming with Oracle Single Sign On: you can't. That are the dire consequences of implementing a 'solution' before even verifying possibilities at the Oracle level. I don't know whether your application sets variables, but that is an application issue, not a database issue. If your application can set a context by all means use that mechanism. Further info on setting contexts is of course in the docs you so carefully and persistently avoid to read, misusing this forum as a replacement.

--
Sybrand Bakker, Senior Oracle DBA