CODEWITHSUNDEEP
oracle-databaseoracle11g
Mike Christensen
Mike Christensen

Reputation: 91598

How to audit deletes in a certain table with Oracle?

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

Upvotes: 2

Views: 17791

Answers (1)

kubanczyk
kubanczyk

Reputation: 5931

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

Upvotes: 3

Related Questions