An issue of auditing DML operations

From: nirav <shivam71_at_gmail.com>
Date: Thu, 22 Nov 2007 22:59:45 -0800 (PST)
Message-ID: <0d990a1c-4c23-4842-9b50-7f29457b12a4@s19g2000prg.googlegroups.com>

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 Received on Fri Nov 23 2007 - 00:59:45 CST