Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Capture SQL for selected users

Re: Capture SQL for selected users

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sun, 21 Nov 2004 15:25:49 +1100
Message-ID: <41a018cc$0$7560$afc38c87@news.optusnet.com.au>


ghm wrote:
> Is it possible without creating triggers on every table in my database to
> capture all DDL/DML (excluding SELECT) for selected users? For audit
> purposes, I need to capture the SQL for certain users only.
>
> Thanks in Advance
>
>

The short answer is "no". Auditing in Oracle captures no SQL, merely the fact that a privilege was exercised (eg, "Select from emp" or "drop table").

Fine-grained auditing might be worth a look. It involves writing a policy for a table, so it's not 'intervention free'. And I don't think you could have it fire just for particular users... though you can certainly capture the username involved, and hence filter the results when necessary.

On the other hand, every piece of *normal* DML, and all DDL, generates redo. And Log Miner lets you read that redo in plain text... so there is already an audit trail which you can review at your leisure. It captures username, so again filtering results is possible. The DDL will be captured as plain text only in 9i. And you obviously need to be in archivelog mode if there is a need to construct a set of audit records at any time *from* any time in the past.

I expect others may have other ideas. Probably better ones too!

Regards
HJR Received on Sat Nov 20 2004 - 22:25:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US