From oracle-l-bounce@freelists.org Mon May 23 09:23:06 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j4NEN475002168 for ; Mon, 23 May 2005 09:23:04 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j4NEN4Ni002163 for ; Mon, 23 May 2005 09:23:04 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 11CB79121A; Mon, 23 May 2005 08:20:09 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 13286-01; Mon, 23 May 2005 08:20:09 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8A7C59126B; Mon, 23 May 2005 08:20:08 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=ZuZJDdwQLXa4lcJGacmuoYgg45aAFW/jjf86HyOneTxN6AqFnO/kBbaQgjoYQWbVPfsK3WZNEK8iWcfozpRaNAcYcMWXAok7NdN4j4oqHf2P50IRMmrZ5wgY4f0T7WBWPOnwVentHZ1V7lZb3C4tmdyT12fG1oykBPE3HrbHqyc= Message-ID: Date: Mon, 23 May 2005 09:18:20 -0400 From: Thomas Day To: oracle-l@freelists.org Subject: Re: Auditing original user in an n-tier environment In-Reply-To: <017701c55f7d$e197cd60$e300a8c0@user12344> Mime-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Content-Disposition: inline References: <017701c55f7d$e197cd60$e300a8c0@user12344> X-archive-position: 20108 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: tomday2@gmail.com Precedence: normal Reply-To: tomday2@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=AWL autolearn=ham version=2.63 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