Oracle auditing: SQL select statements Oracle Tips by Burleson Consulting

Question:  I need to audit each and every select statement for regulatory compliance.  How do I audit SQL select statements?  How do I store the select audit trail?

Answer:  Auditing SQL select statements is used in systems governed by GLB, SOX and HIPAA.  But beware, you will need a storage mechanism to hold your SQL select audits, a high data volume that can exceed the size of your whole database, everyday.

Auditing select statement is extremely complex because of the huge amount of data that is generated every day.  In large hospital systems, the select audit trails can exceed terabytes every month with millions of dollars in extra storage costs.

For complete details and working scripts, see the book “Oracle Privacy Security Auditing” by Arup Nanda, a great source of expert advice on Oracle SQL auditing.

SQL select auditing can be accomplished in several ways:

• Oracle audit table command:  audit select table by FRED by access;
   

• Oracle Fined-grained Auditing: (See my FGA notes here)

In a busy database, the volume of the select audit trail could easily exceed the size of the database every data.  Plus, all data in the audit trail must also be audited to see who has selected data from the audit trail.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.