Oracle auditing: SQL select statements Oracle Database Tips by Donald Burleson

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.

See this example of turning-on select statement auditing by setting audit trail=db_extended. Beware, this will cause your audit trail file to grow large very quickly.

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 (when setting audit_trail=db_extended):
  
  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.