DomainKey-Signature: a=rsa-sha1; c=nofws;        d=gmail.com; s=gamma;        h=mime-version:in-reply-to:references:date:message-id:subject:from:to         :cc:content-type;        b=lw6Td2rQmRpQjubkX4kIPzYydQ1aNtRVcejPM/GJTOR442EGMJrdoWl3a1QqHjAht8         lAS6XfldbvkVNawC91v0QVAj//yB32ZbbOP/RGKmOQvw2KBditEpxMImzNvl9MvIRyhW         YDR97FmtaeITZYGJ8azJ2KyGnpFVVsgdJKr3A=
MIME-Version: 1.0
In-Reply-To: <7765c8971001111101v287384c0m1503ad636273f7cc@mail.gmail.com>
References: <de807caa1001110924k1729e5b9yf6c6a9b1e6405eb6@mail.gmail.com>	 <7765c8971001111101v287384c0m1503ad636273f7cc@mail.gmail.com>
Date: Mon, 11 Jan 2010 13:38:14 -0600
Message-ID: <de807caa1001111138y2ee1c6c6q933382db8b90cb81@mail.gmail.com>
Subject: Re: Database comparisons
From: Dennis Williams <oracledba.williams@gmail.com>
To: Niall Litchfield <niall.litchfield@gmail.com>
Cc: oracle-l@freelists.org
Content-Type: multipart/alternative; boundary=0016368e1cb3a30134047ce8ad82
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: oracledba.williams@gmail.com
--0016368e1cb3a30134047ce8ad82
Content-Type: text/plain; charset=ISO-8859-1

Toon, Jared, Robert, Richard, Niall,

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@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@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
>

--0016368e1cb3a30134047ce8ad82
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div>=A0</div>
<div>Toon, Jared, Robert, Richard, Niall,</div>
<div>=A0</div>
<div>Thanks for all the excellent suggestions.</div>
<div>=A0</div>
<div>Obviously I need to describe the app better. This is a vendor-supplied=
 3-tier application so there are server processes that process requests fro=
m the clients. But then my company has extensively modified the application=
 with client customizations. Even though we are testing from a single clien=
t, the database will see changes from the server processes as well as throu=
gh=A0several direct client connections.</div>

<div>=A0</div>
<div>Yes, we do application validation before applying the changes (client =
and database) to production. As part of that validation we currently=A0exec=
ute the application and then verify the correct data changes were made in t=
he database. Fortunately this application has fewer than 200 tables.</div>

<div>=A0</div>
<div>The auditor&#39;s question was essentially that &quot;you are making c=
hanges to table A and verifying those changes, but how do you know the appl=
ication didn&#39;t also change table B&quot;. I wasn&#39;t involved in the =
original question that led to the finding. </div>

<div>=A0</div>
<div>I&#39;m hoping for something that I can set up to screen out the expec=
ted data changes to make the unexpected data changes more obvious. </div>
<div>=A0</div>
<div>I hadn&#39;t considered using auditing - thanks Niall for that suggest=
ion.</div>
<div>=A0</div>
<div>Robert, thanks for the Flashback Version Query suggestion. I&#39;ll lo=
ok into that as well. I should be able to make UNDO large enough.</div>
<div>=A0</div>
<div>Dennis<br><br></div>
<div class=3D"gmail_quote">On Mon, Jan 11, 2010 at 1:01 PM, Niall Litchfiel=
d <span dir=3D"ltr">&lt;<a href=3D"mailto:niall.litchfield@gmail.com">niall=
.litchfield@gmail.com</a>&gt;</span> wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"PADDING-LEFT: 1ex; MARGIN: 0px 0=
px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">Hi, that sounds a bit daft but s=
till wouldn&#39;t a db audit trail with<br>reports periodically sent to the=
 auditor do the job? You know audit<br>
update, insert delete on ....<br>
<div>
<div></div>
<div class=3D"h5"><br>On 1/11/10, Dennis Williams &lt;<a href=3D"mailto:ora=
cledba.williams@gmail.com">oracledba.williams@gmail.com</a>&gt; wrote:<br>&=
gt; List,<br>&gt;<br>&gt; We have an audit finding related to data integrit=
y. I&#39;m looking for a way to<br>
&gt; detect all database changes on a small test database. Fortunately the<=
br>&gt; environment is well-contained. Typically when we&#39;ve made applic=
ation<br>&gt; changes, we verify that the data changes are what we expect. =
The auditors<br>
&gt; are insisting that we somehow verify there aren&#39;t unexpected chang=
es in<br>&gt; other tables. The environment is Oracle 10.2.0.4 on Solaris. =
I have three<br>&gt; thoughts:<br>&gt;<br>&gt; 1. The test database is fres=
hly loaded from an export. After the tests, take<br>
&gt; an export and use UNIX &quot;diff&quot; and compare with the import.<b=
r>&gt; 2. Log Miner, or somehow more directly inspecting the archive logs.<=
br>&gt; 3. Use some of the new flashback features to detect changes. This j=
ust<br>
&gt; occurred to me and I haven&#39;t had time to investigate it.<br>&gt;<b=
r>&gt; Has anyone else done anything like this before?<br>&gt;<br>&gt; Denn=
is Williams<br>&gt;<br><br></div></div><font color=3D"#888888">--<br>Sent f=
rom my mobile device<br>
<br>Niall Litchfield<br>Oracle DBA<br><a href=3D"http://www.orawin.info/" t=
arget=3D"_blank">http://www.orawin.info</a><br></font></blockquote></div><b=
r>

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