SQL & PL/SQL

Hi,

We have several data driven ASP.Net applications using Oracle 9i for the database (possibly soon to be 10g) and a typical requirement is to provide a data auditing facility that snapshots before and after data for any insert, update, or delete and includes the time and user. To make this simple and standardise the way in which this is done, we have implemented some framework code across .Net and PL/SQL.

In essence we are using triggers on insert, update, and delete on every table in the application schema to record the time and before & after data into a separate auditing table, which is very simple since the new and current data is readily available to triggers. This means that developers writing procedures do not have to remember to call some auditing function or paste auditing code every time and changes are recorded even when a developer is carrying out support work to update the database directly. The auditing data is also readily available to the application and our users usually require access to this to determine what work has been done in some cases.

The tricky part is picking up the username of the user that actually triggered the data change. For a developer doing support work directly in the database SYS_CONTEXT('USERENV','OS_USER')) appears to work nicely. However, any calls from the ASP.Net application results in the username that IIS is running as being passed through this PL/SQL call even though users authenticate with the application via Windows Authentication. To get around this, our .Net framework calls an Oracle procedure in an auditing package within the application database schema passing in the Windows username as soon as the connection to the schema is opened for each page request. The username is then stored in a package variable, which as far as I can tell from Oracle documentation should be stored in the session context that as I understand it should have a one to one mapping with each connection from ASP.Net via ODBC. Any subsequent data changes for the same page request/postback then fire the auditing tiggers that pick up the username from the auditing package variable and store this with the change in the auditing table.

The problem that we occasionally have (maybe once or twice a day in an application with 50,000+ page requests/postbacks per day) is that data changes get attributed to the wrong user. Is my understanding of the scope of the package variable incorrect? It seems as though when we have overlapping package requests/postbacks that make data changes, the package variable appears to be storing the username of someone else who was making a change to some other data at the same time.

To explain the process more simply, it goes through the following steps:

1) User posts back to a web page with data changes
2) ASP.Net starts processing postback, our code opens a single database connection for this one postback and calls the Oracle procedure to store the username
3) The rest of the postback is processed by our .Net code that will at some point call an Oracle procedure that will change some data
4) Auditing trigger is fired, picks up the before and after data as well as the username from the package variable and stores this in the auditing table
5) .Net completes processing of the postback and the database connection is closed

At no point does our .Net code share a database connection between users as each connection is opened and closed for every page request/postback. We are using the standard ODBC connection pooling but I thought this simply kept TCP connections to the database server ready and would not lead to Oracle sessions being shared between users.

Are my understandings of Oracle contexts wrong and is there an even simpler way of doing this kind of application data auditing?

TIA