4 Replies Latest reply: Sep 27, 2011 12:35 PM by user704954-Oracle RSS

    Can I Audit a "drop table"?

    AleC
      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
          [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
            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
              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-Oracle
                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"