Auditing
- From: Bill Ferguson <wbfergus@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 4 Mar 2009 08:58:49 -0700
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
