Re: SOX Reporting Requirement

From: David Barbour <david.barbour1_at_gmail.com>
Date: Thu, 28 Aug 2014 12:28:12 -0500
Message-ID: <CAFH+iffZ_6rTJegA+O4JfLBDJ6Ep+KpqAjQr6J1_W8GJfrjWWg_at_mail.gmail.com>

I was trying to avoid triggers by using the built-in features. Fine-Grained Auditing provides the same functionality as db, extended on a table basis which gives me when and what the changes are, but not what the prior values were. Reading up on Mark B.'s suggestion involving Total Recall, it looks like that might fit the bill and alleviate concerns regarding undo retention as well.

I'll give it a try and let you know what happens.

On Thu, Aug 28, 2014 at 11:21 AM, Powell, Mark <mark.powell2_at_hp.com> wrote:

> Yes, I would think that the built-in audit feature would be one option
> as would using table row triggers to write all changes to a history table.
> With a history table you can capture the complete row or just change data
> as works best for your needs along with the change date and username making
> the change. Then you can purge it using a different retention time
> criteria than used with your audit trail if desired.
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Andrew Kerber
> *Sent:* Thursday, August 28, 2014 11:13 AM
> *To:* David Barbour
> *Cc:* oracle-l mailing list
> *Subject:* Re: SOX Reporting Requirement
>
>
>
> I believe you can get the information by just turning on extended auditing
> on the table, As I recall it will put all of that information on the aud$
> table.
>
>
>
> On Thu, Aug 28, 2014 at 10:05 AM, David Barbour <david.barbour1_at_gmail.com>
> wrote:
>
> Morning,
>
> I was wondering how others might be handling the SOX reporting/auditing
> issue we've been assigned.
>
> The audit folks want to know when DML occurs on a particular table and the
> original and new value(s). I've implemented FGA on the table and can
> capture the change. Using the transaction ID, I can then go back to the
> flashback_transaction_query and get the original values. Of course, the
> only guarantee of being able to pull the undo sql containing the original
> values is that the query is performed before the undo retention expires.
> Pre-supposing I have a job that queries dba_fga_audit_trail and grabs the
> undo in time, what might happen next? I was thinking of storing the values
> in a table created specifically for this purpose. Then I'd probably create
> a view to generate the report.
>
> I'd appreciate any other ideas or refinements. This is a pretty busy
> database and I've got to be careful bumping undo retention too high. I'm
> undoubtedly missing something .............
>
>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>

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

Received on Thu Aug 28 2014 - 19:28:12 CEST