Re: Database comparisons

Thanks for all the excellent suggestions.

Obviously I need to describe the app better. This is a vendor-supplied 3-tier application so there are server processes that process requests from the clients. But then my company has extensively modified the application with client customizations. Even though we are testing from a single client, the database will see changes from the server processes as well as through several direct client connections.

Yes, we do application validation before applying the changes (client and database) to production. As part of that validation we currently execute the application and then verify the correct data changes were made in the database. Fortunately this application has fewer than 200 tables.

The auditor's question was essentially that "you are making changes to table A and verifying those changes, but how do you know the application didn't also change table B". I wasn't involved in the original question that led to the finding.

I'm hoping for something that I can set up to screen out the expected data changes to make the unexpected data changes more obvious.

I hadn't considered using auditing - thanks Niall for that suggestion.

Robert, thanks for the Flashback Version Query suggestion. I'll look into that as well. I should be able to make UNDO large enough.

Dennis

On Mon, Jan 11, 2010 at 1:01 PM, Niall Litchfield < niall.litchfield_at_gmail.com> wrote:

> Hi, that sounds a bit daft but still wouldn't a db audit trail with
> reports periodically sent to the auditor do the job? You know audit
> update, insert delete on ....
>
> On 1/11/10, Dennis Williams <oracledba.williams_at_gmail.com> wrote:
> > List,
> >
> > We have an audit finding related to data integrity. I'm looking for a way
> to
> > detect all database changes on a small test database. Fortunately the
> > environment is well-contained. Typically when we've made application
> > changes, we verify that the data changes are what we expect. The auditors
> > are insisting that we somehow verify there aren't unexpected changes in
> > other tables. The environment is Oracle 10.2.0.4 on Solaris. I have three
> > thoughts:
> >
> > 1. The test database is freshly loaded from an export. After the tests,
> take
> > an export and use UNIX "diff" and compare with the import.
> > 2. Log Miner, or somehow more directly inspecting the archive logs.
> > 3. Use some of the new flashback features to detect changes. This just
> > occurred to me and I haven't had time to investigate it.
> >
> > Has anyone else done anything like this before?
> >
> > Dennis Williams
> >
>
> --
> Sent from my mobile device
>
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>

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