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: ghm <ghm_at_dslextreme.com>
Date: Sat, 20 Nov 2004 21:48:26 -0800
Message-ID: <10q0asa6rad1a6c@corp.supernews.com>


Thank you. I have considered the Log Miner approach, but it's a bit more labor/dba intensive then I'd like. The ideal would be to be able to grab the contents of the v$sqltext when one of these users issues a statement. BTW, is it possible to put a trigger on v$session?

Other ideas greatly appreciated.

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:41a018cc$0$7560$afc38c87_at_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 - 23:48:26 CST

Original text of this message

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