RE: Re: RE: Re: RE: Monitor changes (INSERTs, UPDATEs, DELETs) in a certain TABLE possible?

Yes, it's just called "Auditing".   That logs just when something happened and who did it, but not the sql statement that was executed.

Check the Oracle documentation for more details, it's all in there.  Toad supports standard Oracle auditing, so you can check our docs too.

-John


From: [email protected] [mailto:[email protected]] On Behalf Of Ben Stover
Sent: Monday, October 03, 2011 3:04 AM
To: [email protected]
Subject: [toad] Re: RE: Re: RE: Monitor changes (INSERTs, UPDATEs, DELETs) in a certain TABLE possible?



Message from: BenSto


@jdorlon: Do you wanted to tell me something?
Your reply appears to be empty


_______________________________________

Historical Messages


Author: Ben Stover
Date: Mon Oct 03 01:03:56 PDT 2011
@jdorlon: Do you wanted to tell me something?
Your reply appears to be empty

__

Author: John Dorlon
Date: Fri Sep 30 06:05:46 PDT 2011

__

Author: Ben Stover
Date: Thu Sep 29 22:37:48 PDT 2011
>Run this in the editor
>BEGIN
>SYS . DBMS_FGA.ADD_POLICY (
>object_schema => 'KARL'
>, object_name => 'TABLE123'
>, policy_name => 'AUD_POL_TABLE123'
>, audit_condition => NULL
>, audit_column => NULL
>, handler_schema => NULL
>, handler_module => NULL
>,enable => FALSE
>, statement_types => 'SELECT,INSERT,UPDATE,DELETE'
>, audit_trail => SYS . DBMS_FGA.DB + SYS . DBMS_FGA.EXTENDED
>, audit_column_opts => SYS . DBMS_FGA.ANY_COLUMNS );
>END;
>/
<Now, any insert, select, update, delete (you can shorten the list of statement
>types if you wish) statement will be captured by Oracle

@jdorlon: Great! Thank you.

One more question: As far as I understand this is called FGA (Fine Grain Auditing).
This let me think that there is a non-fine (=dumb, global) auditing (which is available in non-Enterprise Editions as well).

How does this global auditing work (e.g. for TABLE "karl.table123" or at least full Schema "karl")?

Thank you
Ben

__

Author: Norman Dunbar
Date: Wed Sep 28 06:53:58 PDT 2011
On 28/09/11 14:44, Bert Scalzo wrote: > OK just found the answer on metalink ? oracle standard edition = no, > oracle enterprise edition = yes That's what I found as well. It's separate from the advanced Security "pay extra" feature. -- Cheers, Norm. [TeamT]
__

Author: John Dorlon
Date: Wed Sep 28 06:45:35 PDT 2011
Be sure to tip your waiters and waitresses folks, he's here all week!
__

Author: Norman Dunbar
Date: Wed Sep 28 06:45:35 PDT 2011
Hi John, On 28/09/11 14:38, John Dorlon wrote: > I?m not sure Norm. Wouldn?t surprise me though. Do you know, Bert? Yes, I know Bert - we've never met though! ;-) Sorry, couldn't resist! -- Cheers, Norm. [TeamT]
__

Author: Bert Scalzo
Date: Wed Sep 28 06:44:40 PDT 2011
OK just found the answer on metalink - oracle standard edition = no, oracle enterprise edition = yes
__

Author: Bert Scalzo
Date: Wed Sep 28 06:42:22 PDT 2011
According to July 2011 Oracle price list it appears not to be - but I'll have to double check the advanced security option to 100% sure - but I really think this one does not cost extra
__

Author: John Dorlon
Date: Wed Sep 28 06:42:22 PDT 2011
I'm not sure Norm. Wouldn't surprise me though. Do you know, Bert?
__

Author: Norman Dunbar
Date: Wed Sep 28 06:42:22 PDT 2011
Hi John, On 28/09/11 14:26, John Dorlon wrote: > Run this in the editor > > BEGIN > > SYS.*/DBMS_FGA.ADD_POLICY/*( > >... I'm wondring, is FGA (Fine Grained Auditing) an additional expense? I don't know for sure, but I think it might be. Unless I'm mixing it up with some other Oracle TLA! ;-) -- Cheers, Norm. [TeamT]
__

Author: Bert Scalzo
Date: Wed Sep 28 06:36:11 PDT 2011
John, won't this be in the first post 11.0 beta since you already coded it J
__

Author: John Dorlon
Date: Wed Sep 28 06:33:29 PDT 2011

__

Author: John Dorlon
Date: Wed Sep 28 06:26:15 PDT 2011
Hi Ben There is nothing in Toad to do this yet (there will be in 11.1) but here is what you can do. Run this in the editor BEGIN SYS . DBMS_FGA.ADD_POLICY ( object_schema => 'KARL' , object_name => 'TABLE123' , policy_name => 'AUD_POL_TABLE123' , audit_condition => NULL , audit_column => NULL , handler_schema => NULL , handler_module => NULL ,enable => FALSE , statement_types => 'SELECT,INSERT,UPDATE,DELETE' , audit_trail => SYS . DBMS_FGA.DB + SYS . DBMS_FGA.EXTENDED , audit_column_opts => SYS . DBMS_FGA.ANY_COLUMNS ); END; / Now, any insert, select, update, delete (you can shorten the list of statement types if you wish) statement will be captured by Oracle. To see the statements, do this: Select * from sys.dba_fga_audit_trail where policy_name = 'AUD_POL_TABLE123' The statements are in the SQL_TEXT column, and the binds are in the SQL_BIND column. This view only shows the first 2000 characters. If your statements are less than 2000 characters, great. If not, you'll need to select from sys.fga_log$ instead. When you are ready to stop auditing, run this: BEGIN SYS . DBMS_FGA.DROP_POLICY ( object_schema => 'KARL' , object_name => 'TABLE123' , policy_name => 'AUD_POL_TABLE123' ); END; / -John
__

Author: Ben Stover
Date: Wed Sep 28 00:50:17 PDT 2011
Assume I have a TABLE karl.table123 I want to monitor now all changes which occur from a certain time onwards. So I would like to have a button/menu "START monitoring" and a button/menu "STOP monitoring". When I press (after previous START) the STOP button then all table data column cells should be highlighted in red which were changed during the monitor period. Is this somehow possible with Toad? Ben
__
_______________________________________