This discussion is archived
4 Replies Latest reply: Sep 27, 2011 10:35 AM by user704954 RSS

Can I Audit a "drop table"?

AleC Newbie
Currently Being Moderated
Sometimes on my production database (10.1.0.5) the payload type of a queue changes !!!!!

I need to understand who/what changes that payload.

So I'm going to audit the DDL on the queue table.


I'm able to audit the create statement:

audit create table by XXXX;

but I am not able to find the right statement for the drop and the alter table.

Is there someone can help me?

Best,
A.
  • 1. Re: Can I Audit a "drop table"?
    591186 Guru
    Currently Being Moderated
    [http://www.oracle-base.com/articles/10g/Auditing_10gR2.php] check under View audit trail
    Auditing question executing the TRUNCATE command.
  • 2. Re: Can I Audit a "drop table"?
    user097815 Journeyer
    Currently Being Moderated
    What you can do is setup auditing on the table. Oracle can generate either
    a record into an audit table OR generate a flat file with information on
    the time, username, etc of who dropped the table.

    The syntax is:

    AUDIT DROP TABLE ON owner.table_name
    BY {SESSION/ACCESS}
    WHENEVER {NOT} SUCCESSFUL;

    So an example is:

    AUDIT DROP TABLE ON scott.emp
    BY SESSION
    WHENEVER SUCCESSFUL;

    Also, "AUDIT ANY" is a required privilege to issue audit commands. You can
    do various "SELECT * FROM SYS.AUD$" to view the audit information, which
    will tell you who and when the "scott.emp" table was dropped.
  • 3. Re: Can I Audit a "drop table"?
    OrionNet Guru
    Currently Being Moderated
    Hello,

    You can set schema level trigger to handle DDL (drop)
    CREATE OR REPLACE TRIGGER audit_trg
    BEFORE DROP
    ON MYSCHEMA_USER.SCHEMA
    DECLARE
    
    BEGIN
       --  your code here to handle 
       --  drop ddl
    
       EXCEPTION
         WHEN OTHERS THEN
           -- Consider logging the error and then re-raise
           RAISE;
    END audit_trg;
    Regards
  • 4. Re: Can I Audit a "drop table"?
    user704954 Newbie
    Currently Being Moderated
    You cannot issue a "AUDIT DROP TABLE" command successfully. You will get the following error:

    ERROR at line 1:
    ORA-00956: missing or invalid auditing option

    The only "audit" option for drop and truncate that I've found is "audit table"

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points