Timestamps and multiple table transactions

Hi,

We're evaluating DB Audit and we had a couple questions.

We are planning to use DB Audit to only log the data changes of certain tables. We would be using our own software to display data change logs to the end user and not use the built-in reporting tools. Our intent is to show the user the history of changes relevant to what the user is working on. Does anyone have experience using DB Audit in a similar manner?

Additionally, has anyone dealt with the issue of multiple tables being associated with a single transaction? For instance, if the user makes a change that touches multiple tables, is there an easy way to associate the data log changes among all the tables to that particular user action? It appears to us that only the only option is to use the column AUDIT_TIMESTAMP plus the primary keys for the original tables.

If that's the case, in the audit tables (for Oracle 10i), DB Audit uses a DATE datatype for the column AUDIT_TIMESTAMP. We're looking for more precision and were wondering, is it possible to set DB Audit to use TIMESTAMP as the datatype for that column as it carries fractions of a second?

Thanks,
Chris

In addition to primary keys and AUDIT_TIMESTAMP you should use the session id which is also recorded in the audit trail; together this will provide you with a unique combination. The precision of datetime column AUDIT_TIMESTAMP is sufficient for this purpose.

No you cannot change AUDIT_TIMESTAMP data type to Oracle's TIMESTAMP. audit record header columns are not customizable.

Just in case, if you are not aware, DB Audit provides an API that external programs and scripts can use to set audit settings, configure filters and fetch data. The data output format is customizable, by default XML is used, but other formats are also supported.

How does the session id tie back to the data audit tables? I assume that you're talking about the column in the AUD$ table? I'm not quite understanding how I would use it to get to a "transaction id" across multiple tables.

Apologies for creating a second userid, but for whatever reason, the forum software is neither recognizing my password for the first account, nor letting me reset the password.

Please disregard the reference to session id, it is incorrect for Oracle. Sorry about that misleading reference.

Yet, there is a known trick involving use of a custom procedure hook to get Oracle session id recorded in the audit trail tables. That will likely provide you with the required values. I will search the support system for the exact solution and post it here

Hi, just checking in to see if you'd managed to find the procedure hook to allow the session id to be recorded in the audit tables.

Ok, there is an ADVANCED button available on the Set Data-change Audit Options screen. You can use that button to "hook" your custom stored procedure. The procedure will be fed several parameters, including user name and expect you to return some other user name. What you can do here is appending session id (SID) to the start or end of the user name using some character separator between SID and user name. That new value will be saved in the username column and you can use it in your reports and queries.

To get a better idea, check examples of custom stored procedure available in DB Audit manual pages 108 - 110