Re: Auditing - Capture Select Statements

Well, you can get the SQL statements (SELECT or other) from v$sql, and you can get the userid, etc., from v$session using the session number from v$sql (something like that). The only way I can think of right off to automate this is to write a stored procedure that you then submit to execute every x minutes or seconds, having it perform the necessary logic and store the results in a table for each new entry in DBA_AUDIT_TRAIL. It would be a bit complicated, but you could do it. Or perhaps you could attach a trigger to each table of interest, grab the SQL currently being executed by that UID from v$sql, then insert that into the necessary table. (I'm not sure about the trigger approach. You might get the SELECT statement, or you might get the SQL being executed by the trigger. Not sure. Try it and see, email me if it works!)

Hope this helps,
Dave

Ed Jennings wrote in message <354910FE.3A10_at_mindspring.com>...
>I'm running Oracle 7.3.3 in an NT environment. I have
>a requirement to capture the SQL for every query
>launched against the database from the application.
>It's a secret Govt system where they need to track
>no only changes, but who retrieved what from the DB.
>INSERT, UPDATE, & DELETE can be done via triggers,
>but I can't figure out how to capture the text of
>queries. I'd prefer to have this functionality on
>the server rather than in the application. The
>application is in Oracle FORMS 4.5, which provides
>a way to retrieve the LAST_QUERY text, but there is
>no way to perform a DB update while it is in query mode.
>
>Can this level of detail be captured on the server side??
>Any suggestions???
>
>TIA
>
>Ed Jennings
>--
>~~~~~~~~~~~~~~~~~~~~~~~~~
>jenningse_at_mindspring.com
>
>"The opinions expressed here are my own, not those of DOMAIN
>technologies"
Received on Thu Apr 30 1998 - 21:29:10 CDT