Audit truncate and drop

Question

I want to capture truncate and drop statements on one table.

In some google searches i got that it's impossible to execute:

audit truncate, drop on schema.table;

So I tried:

audit table;

As I understand, this supposed to capture create, drop and truncate about all the tables.

So, my questions:

1. Is that realy the best way to do it? I want only on one specific table.
2. Even with that statement - after I execute a truncate on table, it's not captured.

DBA_AUDIT_TRAIL

stay with no data..

Thanks a lot.

Answer 1

Yes you can audit DROP TABLE and TRUNCATE TABLE on a specific table.

Here is my audit_trail parameter and it's value.

SQL> conn / as sysdba
SQL> show parameter audit

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest              string  /u01/app/oracle/admin/orcl/adu
                         mp
audit_sys_operations             boolean     TRUE
audit_syslog_level           string
audit_trail              string  DB, EXTENDED
unified_audit_sga_queue_size         integer     1048576

I have created table table called tbl1.

SQL> conn jay
Password:

Connected.
SQL> create table tbl1(id number);

Table created.

Now, let's enable auditing on that table. If you specify ALL on a table then oracle will audit CREATE TABLE, DROP TABLE and TRUNCATE TABLE operations. Specify BY ACCESS if you want oracle to write one record for each audited statement and operation.

SQL> conn / as sysdba
SQL> audit all on jay.tbl1 by access; -- or  just audit all on jay.tbl1

Audit succeeded.

Create new session using Jay user and perform some operations.

SQL> conn jay

SQL> truncate table tbl1; 

Table truncated.

SQL> drop table tbl1;

Table dropped.

Let's check the audit trail.

SQL> conn / as sysdba

SQL> select action_name from dba_audit_trail where obj_name='TBL1';

ACTION_NAME
----------------------------
DROP TABLE
TRUNCATE Table