Re: SQL audit
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: rtylka@xxxxxxxxx, jkstill@xxxxxxxxx
- Date: Mon, 21 Dec 2009 18:02:05 -0800 (PST)
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@xxxxxxxxx> wrote:
From: Jared Still <jkstill@xxxxxxxxx>
Subject: Re: SQL audit
To: rtylka@xxxxxxxxx
Cc: "Oracle-l" <oracle-l@xxxxxxxxxxxxx>
Date: Monday, December 21, 2009, 3:17 PM
On Mon, Dec 21, 2009 at 12:15 PM, Rich Tylka <rtylka@xxxxxxxxx> 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
Other related posts:
