Re: Auditing

From: Christo Kutrovsky <>
Date: Wed, 4 Mar 2009 11:17:20 -0500
Message-ID: <>

What about using Oracle Streams ? Or if not possible use logminer.

You would only have to dump some extra info in the transaction to 'bind it' to a specific user.

Christo Kutrovsky
Senior DBA
The Pythian Group -
I blog at

On Wed, Mar 4, 2009 at 10:58 AM, Bill Ferguson <> wrote:

> This general subject has been broached many times before, but now
> project management has finally given some specifics on what they want.
> To me, this seems like major overkill for a fairly simple database of
> scientific (mineral site related) information. Even for something like
> a full blown accounting app, this seems close to overkill.
> I've thought about how to begin to even approach this, and nothing at
> all seems easy. Trying to use the information in
> FLASHBACK_TRANSACTION_QUERY takes hours at a time for each query. This
> seems like it would require some modifications to every table in the
> schema, at least modifications to every trigger, and then a full-blown
> application built to query and report on the data they say they want
> to monitor.
> I'm using 11g on Windows. Does anybody else out there, since many of
> you are in businesses where this level of detail may not be overkill,
> have any professional opinions on managements 'demands'?
> Here is what management just sent me:
> Specification for data infrastructure and a specialized administrative
> interface to review changes to the database:
> Types of changes that must be reported:
> 1. Modifications of fields in an existing row
> 2. Deleted rows in subordinate tables (for example commodities_base)
> 3. Inserted rows in subordinate tables
> 4. Deleted records (rows in deposits_base table)
> 5. Inserted records
> 6. Schema changes (added or removed fields or tables)
> Information to be reported about each change (numbers match numbers above):
> 1. date, time, userid, dep_id, table,{field, old value, new value},
> {field, old, new}, ...
> 2. date, time, userid, dep_id, table,{old field values}
> 3. date, time, userid, dep_id, table,{new field values}
> 4. date, time, userid, dep_id, a complete copy of the existing record
> at time of deletion (possibly in XML)
> 5. date, time, userid, new dep_id
> 6. date, time, userid, table,[field],description of change
> Ways in which this information must be available:
> by time interval
> by userid
> for dep_id
> for table
> for field within table
> The user interface must allow any or all of these to be specified to
> present a list of changes in its report.
> Desirable features whose absence does not inhibit opening the database
> to wider editing:
> a. Include with each change an indication of the mechanism by which
> the change was made and the rationale for making the change. For some
> types of changes this could be automatically generated (for example
> use of the APEX web interface) while in others it would be more
> important to add intentionally (for example deleting a table or field,
> or making changes through SQL outside the APEX web interface).
> b. Undo or restore capabilities (automated):
> (1) revert one field to its previous value
> (2) restore one whole record previously deleted
> (3) show dependencies--what must be done in order to revert a change
> that was made.
> (4) restore a deleted table
> --
> -- Bill Ferguson
> --
-- Christo Kutrovsky Senior DBA The Pythian Group - I blog at --
Received on Wed Mar 04 2009 - 10:17:20 CST

Original text of this message