RE: SQL audit
Date: Mon, 21 Dec 2009 17:34:40 -0500
Are they suggesting you produce all the sql you run in the form of E-Biz suite patches and cpus? The running applications themselves? Some sqlplus will run. Some maintencance jobs such as cleaning up workflow tables.
Do you license AWR? If you trigger license compliance issues in the course of complying with the auditors, are they planning to pay any excess license costs?
What is the nature of the authority of auditors? Do they want sql you will run in the future, or do they want to establish that you log ad hoc updates that are not part of the applications suite?
Do you have feeder systems you use to pump data into Oracle APIs for the applications? Those probably constitute much more than sql, but verifying the feeds are correct is essential.
Hearing their request to you second hand may be losing the sense of what they really want. They should be helping you deliver what they really want. But from the sound of it they want a current dump from you rather than tracking your procedures.
Changes to financial information made outside the application suite should definitely be tracked and you should have a process for tracking it as it is run with very specific details and before and after reports on any data you change, and why the change was made.
Good luck making sense of this unless they have the competence to lead you more in the definition of what they want to see. Do you track headroom remaining on disk? Is that stored in your database in dba tables that have nothing to do with the business information? Do they want to see that? Why or why not?
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Rich Tylka
Sent: Monday, December 21, 2009 3:16 PM
Subject: SQL audit
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 188.8.131.52 as the database. I know the scheduled processes, application server, and Grid Control agent hit the database constantly, so I have two questions:
- What's the best way to grab and save any DML sql?
- 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.
I tried to explain that we only run SQL against the database when we're fixing problems or as the solution to a customer request and we keep all the emails and document everything, but that just isn't good enough for them.
Any help that points me in the right direction would be appreciated.