Hello all,
I want to audit the drop trigger statements from any user. I tested it by creating a generic user, a table and a trigger on that table. Then I dropped the trigger and went to the dba_audit_trail view to check if there's any record registering that action. There's none.
SQL> audit drop any trigger whenever successful;
Audit succeeded.
SQL> audit drop any trigger whenever not successful;
Audit succeeded.
SQL> create user test_trg identified by "test_trg123";
User created.
SQL> grant connect, resource, select_catalog_role to test_trg;
Grant succeeded.
SQL> conn test_trg/test_trg123
Connected.
SQL> create table t (id number primary key);
Table created.
SQL> create trigger test_trigger before insert on t for each row begin null; end;
2 /
Trigger created.
SQL> drop trigger test_trigger;
Trigger dropped.
Then I queried the DBA_AUDIT_TRAIL and found no records reflecting the DROP TRIGGER TEST_TRIGGER statement I just executed.
SQL> SELECT USERNAME, ACTION_NAME, TIMESTAMP FROM DBA_AUDIT_TRAIL WHERE ACTION_NAME NOT LIKE 'LOG%';
USERNAME ACTION_NAME TIMESTAMP
------------------------------ ---------------------------- ---------
SYSTEM DROP PUBLIC SYNONYM 26-FEB-15
SYSTEM DROP PUBLIC SYNONYM 26-FEB-15
SYSTEM CREATE PUBLIC SYNONYM 26-FEB-15
SYSTEM CREATE PUBLIC SYNONYM 26-FEB-15
Where's my mistake?
Thanks.
Martin