2

I'm trying to record DELETE statements in a certain table using Oracle's auditing features. I ran:

SQL> AUDIT DELETE TABLE BY TPMDBO BY ACCESS;

Audit succeeded.

I'm unclear if this audits the deletion of a table schema itself (ie, dropping the table), or if it audits the deletion of one or more rows within any table (ie, the delete command). If the latter, how do I limit this auditing to only a table called Foo? Thanks!

UPDATE:

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
audit_file_dest                      string      /backup/audit
audit_sys_operations                 boolean     TRUE
audit_syslog_level                   string
audit_trail                          string      XML, EXTENDED
Mike Christensen
  • 88,082
  • 50
  • 208
  • 326

1 Answers1

3

There is a new feature called fine-grained auditing (FGA), that stores log in SYS.FGA_LOG$ instead SYS.AUD$. Here is the FGA manual.

BEGIN
  DBMS_FGA.ADD_POLICY(
   object_schema      => 'HR',
   object_name        => 'FOO',
   policy_name        => 'my_policy',
   policy_owner       => 'SEC_MGR',
   enable             =>  TRUE,
   statement_types    => 'DELETE',
   audit_condition    =>  'USER = ''myuser''',
   audit_trail        =>  DBMS_FGA.DB);
END;
/

Yes, your original command should audit DELETE operations (not DROP) for this user on all tables. Examine show parameter audit

kubanczyk
  • 5,184
  • 1
  • 41
  • 52
  • So with my `AUDIT` command above, does that only audit schema changes? That would explain why I saw nothing in the audit trail. – Mike Christensen Jan 04 '12 at 19:32
  • Yes, your original command should audit DELETE operations (not DROP) for this user on all tables. Examine `show parameter audit` – kubanczyk Jan 04 '12 at 19:50
  • Added, thanks! I've actually solved the mystery of the vanishing rows but I'd still like to learn about auditing for my own personal development :) – Mike Christensen Jan 04 '12 at 20:55