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
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
John, won’t this be in the first post 11.0 beta since you already coded it
J
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]
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
I’m not sure Norm. Wouldn’t surprise me though. Do you know, Bert?
OK just found the answer on metalink – oracle standard edition = no,
oracle enterprise edition = yes
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]
Be sure to tip your waiters and waitresses folks, he’s here all week!
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]
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
@jdorlon: Do you wanted to tell me something?
Your reply appears to be empty
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