Re: SQL audit

From: Kellyn Pedersen <kjped1313_at_yahoo.com>
Date: Mon, 21 Dec 2009 18:02:05 -0800 (PST)
Message-ID: <895901.74527.qm_at_web32003.mail.mud.yahoo.com>

I have had to implement auditing on Oracle Apps 11i and the auditors required it for SOX compliance, so first thing I would request is the auditor requirements that they need to track.  You might be surprised how little you actually have to audit or even turn on.  You also have the opportunity to challenge and demand they justify many of the requests, as they often will not even know what they are requesting or that some features are already tracked automatically by Oracle and you just need to write out a justification response of your own. The odd items I did need to implement were:

- a shell script to track that the listener was password protected at all times.
- any new users that were created.
- that Oracle application users passwords were set to the same complexity requirements as network logins.
- And scripted out the ability to reset all Oracle/App system passwords every 90 days.

 

The requirement documents are incredibly important-  without these, you won't know what they actually need and if it's for SOX, this is all required to be written out very clearly, but it's not written in stone-  you can demand a justification and respond with justifications for NOT implementing certain pieces.  From there, the auditors can agree or disagree to your recommendations.

Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
 

"Go away before I replace you with a very small and efficient shell script..."

• On Mon, 12/21/09, Jared Still <jkstill_at_gmail.com> wrote:

From: Jared Still <jkstill_at_gmail.com> Subject: Re: SQL audit
To: rtylka_at_gmail.com
Cc: "Oracle-l" <oracle-l_at_freelists.org> Date: Monday, December 21, 2009, 3:17 PM

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 - 20:02:05 CST