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

Home -> Community -> Usenet -> c.d.o.server -> Re: An issue of auditing DML operations

Re: An issue of auditing DML operations

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Fri, 23 Nov 2007 01:37:56 -0800 (PST)
Message-ID: <cd387202-b46a-42d1-b5de-0c599c06c570@b15g2000hsa.googlegroups.com>


nirav wrote:
> Hi All,
>
> I have a requirement described below.
>
> Requirement:
> -------------------------
> We want to audit all DML operations done on a table by the application
> users. There are several application users who do the DML and it is
> required to track which application user did what DML operation.
>
> When connection is made to the database, the schema to which all the
> application users connect is the one and the same - i.e. we use SCOTT
> schema credentials for establishing DB connection for all different
> application users.
>
> Therefore the detail of which application user is performing the DML
> is not available for oracle. It is available in the application code.
> Our primary requirement is that we need to have the detail of the
> application user doing the transaction to be passed into the database
> so that we can audit the DML operations done.
>
> To take an example:
>
> Let's say that the table to be audited is EMP which is in SCOTT
> schema:
>
> SQL> desc EMP
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> EMPNO NOT NULL NUMBER (4)
> ENAME VARCHAR2 (10)
> JOB VARCHAR2 (9)
> MGR NUMBER (4)
> HIREDATE DATE
> SAL NUMBER (7,2)
> COMM NUMBER(7,2)
> DEPTNO NUMBER(2)
>
> Let's say that there are 4 application users; user1, user2, user3 &
> user4. Now when they connect to the database, the connection is to
> schema SCOTT. So all the 4 application users will connect to the same
> db schema 'Scott'. The app users will do some transactions; they may
> roll it back etc. And we need to track that. - The tricky part being
> that we need to track which application user has done the DML.
>
> We are planning to track the Edit Audits through Triggers. Before the
> actual Data record Insert into the EMP table the trigger should insert
> the Record into the Audit Table, along with the UserID.
>
> We have distributed transactions (under the COM+ Environment)
>
> I am unable to figure out how to meet this requirement...shall be very
> thankful for any help/suggestions.
>
> Thanks,
> Nirav

One of the options might be creating package with variable containing your app username. For every call to database at first populate this vaiable and use it in your triggers. To make easier your job (and catch possible bugs :) you can use pragma serially_reusable for this package, it allows to populate variables only for one call and as soon as the call has finished variables are cleaned. So you won't get any unnecessary legacy from previous application user.

Gints Plivna
http://www.gplivna.eu Received on Fri Nov 23 2007 - 03:37:56 CST

Original text of this message

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