Re: audit select statements

Hi Roger

You cannot use triggers to capture the SQL from select statements. If you have 9i then you can use Fine Grained Auditing to capture select statements. You need to set up a predicate that will always match for the tables you wish to audit. The standard function captures the SQL statement.

The only other more esoteric options are to read the SQL from the SGA but this can lead to you missing SQL and also reading a lot of SQL that is irrelevant. You can use standard audit features and capture who accessed a table with "select" but you won't get the SQL. You could turn on SQL Net tracing at support level and parse the SQL from the files. But this needs managing as a huge amount of trace will quickly fill your file system, you could also use a tool like snoop to capture network packets and parse them.

This subject has been discussed a few times recently on this group and on Jareds list. FGA is your only sensible option if you wish to capture the SQL statement.

hth

kind regards
Pete

-- 
Pete Finnigan
email:pete_at_petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.