Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Auditing original user in an n-tier environment

Re: Auditing original user in an n-tier environment

From: Thomas Day <tomday2_at_gmail.com>
Date: Mon, 23 May 2005 09:18:20 -0400
Message-ID: <a8c50459050523061821bb5214@mail.gmail.com>


We are fighting the exact same fight. Oracle provides a mechanism with DBMS_SESSION.set_context and sys_context. However, this is not a magic bullet. We have a web-based application with pooled connections.

Quoting from the Oracle docs -

"Web-based applications typically have hundreds if not thousands of users, and the web is stateless. There may be a persistent connection to the database (to support data retrieval for a number of user requests), but these connections are not specific to each web-based user. Web-based applications typically set up and reuse connections instead of having different sessions for each user, to provide scalability. For example, web user Jane and Ajit connect to a middle tier application, which establishes a session in the database used by the application on behalf of both users. Typically, neither Jane nor Ajit are known to the database. The application is responsible for switching the username on the connection, so that, at any given time, it's either Jane or Ajit using the session."

Note the last sentence. Every time you reuse a connection (typically once per page) the application must reestabilsh the context.

In the application you have to capture who the "real" user is and the application must keep Oracle informed, on each connection re-use, of who the connection is being used by.

The context is kept in a SYS owned table -=20  TYPE AppCtxRecTyp IS RECORD ( namespace varchar2(30), attribute varchar2(3= 0),

     value varchar2(4000));
 TYPE AppCtxTabTyp IS TABLE OF AppCtxRecTyp INDEX BY BINARY_INTEGER;

It is very easy to home-roll a solution similar to Oracle's but I don't know of any way to do it better than Oracle's solution. And Oracle's solution has the benefit of being documented (even if not well known).

--
http://www.freelists.org/webpage/oracle-l
Received on Mon May 23 2005 - 09:23:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US