Re: SQL audit

From: Jared Still <jkstill_at_gmail.com>
Date: Mon, 21 Dec 2009 14:17:42 -0800
Message-ID: <bf46380912211417n4b5ac575x33166c833416fe0e_at_mail.gmail.com>

On Mon, Dec 21, 2009 at 12:15 PM, Rich Tylka <rtylka_at_gmail.com> wrote:

> So, I've never had to turn on any auditing within the Oracle DB...and now
> the auditors want every SQL statement that inserts, updates, or deletes data
> in our database. We run Oracle Apps 11i and have Oracle 11.1.0.7 as the
> database. I know the scheduled processes, application server, and Grid
> Control agent hit the database constantly, so I have two questions:
>
> 1. What's the best way to grab and save any DML sql?
>
>

Docs for the AUDIT statement are here

http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_4007.htm#i2059073

This will allow you to capture DML against specific database objects.

> 2. How do I filter out all the "junk" that I don't want to see? In
> essense, I only want SQL that is run directly against the database from
> sqlplus, sql*developer, etc.
>
>

The auditors may need to be more specific:

Do they want all DML?

Or do they want all DML, which account ran it, and when?

Via the AUDIT statement you can't specify the client, only what and when to audit (user/session).

Trying to do an audit by identifying the client is kind of pointless, because it
is so easy to spoof the client name.

Personally, I would ask them to justify this request.

Find out what they think they can prove by having all the DML SQL.

My experience with auditors has been that they don't really have a good understanding of databases and database security. Asking around with a few colleagues makes me think this not an unusual experience.

Q: Have they asked to see the SYSDBA logs?

I've never been asked for them.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist Oracle Blog: http://jkstill.blogspot.com Home Page: http://jaredstill.com

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

Received on Mon Dec 21 2009 - 16:17:42 CST