Re: Auditing
- From: Pete Finnigan <pete@xxxxxxxxxxxxxxxx>
- To: wbfergus@xxxxxxxxx
- Date: Mon, 09 Mar 2009 14:08:39 +0000
Hi Bill,
I have seen some similar requests in the past and some attempts to build
similar functionallity. I have also seen third party finance apps that
do some of this. All the cases I have seen rely on shadow tables to
store the changes. E-Business Suite is a good example with Row Level
Auditing (RLA) where a screen allows you to "design" these types of
audit requirements. This generates a whole set of triggers, views,
procedures, packages and shadow tables to store the changed fields,
deleted rows etc.
There are severe performance issues doing this. Flashback is touted as
something that will allow this sort of functionallity but the storage
required and cost may mean that its easier to simply use shadow tables.
The big downside to all of this is that usually the audit trails are
stored in the same database as the original data. This means that they
are easily open to abuse and change. You then need complex "layers" of
security to secure, manage, archive etc. You also need audit on the
audit. Its complex to do securely.
Ron has a new book out in a few days on how to create audit trails in
the database with the tools and features that Oracle provides and he
suggests in there one option to use redo/archive logs and to use CDC.
Also, whilst very old the O'Reilly book had a chapter on simple audit
trails using shadow tables and triggers and also a simple Forms app to view.
hth
cheers
Pete
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
>
>
>
--
Pete Finnigan
Director
PeteFinnigan.com Limited
Specialists in database security.
If you need help to audit or secure an Oracle database, please ask for
details of our courses and consulting services
Phone: +44 (0)1904 791188
Fax : +44 (0)1904 791188
Mob : +44 (0)7742 114223
email: pete@xxxxxxxxxxxxxxxx
site : http://www.petefinnigan.com
Registered Office: 9 Beech Grove, Acomb, York, YO26 5LD, United Kingdom
Company No : 4664901
VAT No. : 940 6681 14
Please note that this email communication is intended only for the
addressee and may contain confidential or privileged information. The
contents of this email may be circulated internally within your
organisation only and may not be communicated to third parties without
the prior written permission of PeteFinnigan.com Limited. This email is
not intended nor should it be taken to create any legal relations,
contractual or otherwise.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
