RE: Database comparisons
- From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- To: <oracledba.williams@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 11 Jan 2010 12:57:33 -0500
Dennis,
For one thing I've got to take your question from the perspective of
my current situation where I don't create change scripts to production
database, just run them. In this case I'd simply refer the auditor to
the creator of the script & leave well enough alone. (the less I have to
do with auditors the better).
But from your message I take it that you are one, if not the only,
creator of these scripts and so the task lies in your lap. First off,
I'm going to assume (maybe reaching on this one) that the auditor is
sufficiently knowledgeable that he/she is not including the data
dictionary in their question. The reason is that any script that
changes data or structure in the database is going to make changes in
dictionary tables, heck for one the SCN is going to change. And we all
should know that this is normal and expected behavior. Outside of that
the only way one table can change data in another table is if there is a
trigger on the table(s) that you are modifying that propagates data to
other tables, like MLOG triggers. Probably the easiest way is to enable
auditing of the schema while the script is running. If the auditing
doesn't catch a change it never happen, simple.
On another note, I've recently run into the auditor who believed he
had a right to question changes made to any database within the company.
Thankfully the legal folks were happily able to correct his
interpretation of SOX.
Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Dennis Williams
Sent: Monday, January 11, 2010 12:25 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Database comparisons
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
Other related posts:
