Re: Auditing

From: Christo Kutrovsky <kutrovsky.oracle_at_gmail.com>
Date: Wed, 4 Mar 2009 11:17:20 -0500
Message-ID: <52a152eb0903040817h5b657e10t5e4d7b087ffe1f91_at_mail.gmail.com>

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 - www.pythian.com
I blog at http://www.pythian.com/blogs/


On Wed, Mar 4, 2009 at 10:58 AM, Bill Ferguson <wbfergus_at_gmail.com> 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

> --

> http://www.freelists.org/webpage/oracle-l

>

>

>




-- 
Christo Kutrovsky
Senior DBA
The Pythian Group - www.pythian.com
I blog at http://www.pythian.com/blogs/
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 04 2009 - 10:17:20 CST