7 Replies Latest reply: Apr 2, 2013 12:58 AM by Hemant K Chitale RSS

    Trigger to audit DML and DDL nor working

    Tapan Kumar Saha
      Requirement: Application team complains that everyday within a fixed timeframe, 1 entry from a particular table is being deleted. They are unable to track it, which particular job is it doing it.

      DBA: We had 3 solutions: 1. AUDIT_TRAIL
      2. FGA
      3. Customized TRIGGER to track the DML and DDL.
      After discussion, option 3 has been implemented. Here is the code.

      DML Trigger
      =======
      CREATE OR REPLACE TRIGGER SLCPROD.STORE_TERM_ID_HST_TRG
      BEFORE INSERT OR UPDATE OR DELETE
      ON SLCPROD.STORE_TERM_ID FOR EACH ROW
      BEGIN

      IF UPDATING THEN

      INSERT INTO STORE_TERM_ID_HIST
      ( STR_NUM,
      TERM_ID,
      HOST_ENV,
      USR_TERMINAL,
      SESSION_USR,
      OS_USER,
      ACTION_DATE,
      ACTION_NAME)
      VALUES
      ( TRUNC(:OLD.STR_NUM)||','||:NEW.STR_NUM,
      TRUNC(:OLD.TERM_ID)||','||:NEW.TERM_ID,

      sys_context('USERENV', 'HOST'),
      sys_context('USERENV', 'TERMINAL'),
      sys_context('USERENV', 'SESSION_USER'),
      sys_context('USERENV', 'OS_USER'),
      SYSDATE,
      'UPDATE');

      ELSIF DELETING THEN

      INSERT INTO STORE_TERM_ID_HIST
      ( STR_NUM,
      TERM_ID,
      HOST_ENV,
      USR_TERMINAL,
      SESSION_USR,
      OS_USER,
      ACTION_DATE,
      ACTION_NAME)
      VALUES
      ( :OLD.STR_NUM,
      :OLD.TERM_ID,

      sys_context('USERENV', 'HOST'),
      sys_context('USERENV', 'TERMINAL'),
      sys_context('USERENV', 'SESSION_USER'),
      sys_context('USERENV', 'OS_USER'),
      SYSDATE,
      'DELETE');

      END IF;

      END;
      /

      DDL Trigger
      ========
      CREATE OR REPLACE TRIGGER SLCPROD.STORE_TERM_ID_HST_TRG_TRUNC
      AFTER DDL ON DATABASE
      declare

      object_name varchar2(30);

      BEGIN

      select distinct ora_dict_obj_name into object_name from dual;

      if UPPER(object_name)= 'STORE_TERM_ID'
      then

      INSERT INTO STORE_TERM_ID_HIST
      ( STR_NUM,
      TERM_ID,
      HOST_ENV,
      USR_TERMINAL,
      SESSION_USR,
      OS_USER,
      ACTION_DATE,
      ACTION_NAME)
      VALUES
      ( 'NA',
      'NA',

      sys_context('USERENV', 'HOST'),
      sys_context('USERENV', 'TERMINAL'),
      sys_context('USERENV', 'SESSION_USER'),
      sys_context('USERENV', 'OS_USER'),
      SYSDATE,
      ora_sysevent);

      END IF;

      END;
      /

      TABLE STORE_TERM_ID_HIST DESC
      =======================
      Name Null? Type
      ----------------------------------------- -------- ----------------------------
      STR_NUM CHAR(25)
      TERM_ID CHAR(25)
      HOST_ENV VARCHAR2(50)
      USR_TERMINAL VARCHAR2(50)
      SESSION_USR VARCHAR2(50)
      OS_USER VARCHAR2(50)
      ACTION_DATE DATE
      ACTION_NAME VARCHAR2(50)

      Though we have these triggers in place, still we can see that 1 record is getting deleted (do not know how as I haven't been able to track it) between 8 AM - 8:50 AM. We have verified it from COUNT and also know which particular record is getting deleted.
      We tried to see if any error logged due to these triggers in ALERT logs, but no error specific to it has been seen. We have tested the TRIGGERs using manuals queries, and they seem to be working fine.

      Could anyone please help what could be happening here? Or any better solution of resolving it.

      Thanks,
      Tapan

      Edited by: TapanKumar Saha on Mar 31, 2013 10:04 PM

      updated the code.
        • 1. Re: Trigger to audit DML and DDL nor working
          sb92075
          TapanKumar Saha wrote:
          Requirement: Application team complains that everyday within a fixed timeframe, 1 entry from a particular table is being deleted. They are unable to track it, which particular job is it doing it.

          DBA: We had 3 solutions: 1. AUDIT_TRAIL
          AUDIT is trivial to implement & low overhead.

          All DML is recorded in REDO & can be "read" using DBMS_LOGMNR.
          • 2. Re: Trigger to audit DML and DDL nor working
            Tapan Kumar Saha
            Client's own DBA didn't approve to enable AUDIT. So, we had to think of customized TRIGGERs to implement the same functionality.

            Could you please provide me the detail steps of finding the DMLs through LOGMNR.
            Also, will it help if the complete table is TRUNCATed and filled up using SQL Loader. As I said, I do not know or unable to track how a record is getting deleted.

            Thanks,
            Tapan
            • 3. Re: Trigger to audit DML and DDL nor working
              sb92075
              TapanKumar Saha wrote:
              Client's own DBA didn't approve to enable AUDIT. So, we had to think of customized TRIGGERs to implement the same functionality.

              Could you please provide me the detail steps of finding the DMLs through LOGMNR.
              Also, will it help if the complete table is TRUNCATed and filled up using SQL Loader. As I said, I do not know or unable to track how a record is getting deleted.

              Thanks,
              Tapan
              You have offered NO proof that record actually gets deleted.
              • 4. Re: Trigger to audit DML and DDL nor working
                Tapan Kumar Saha
                As I said earlier, I have checked that the count(*) on table before and after the 8-8:50 timeframe, and can see 1 recorded deleted.

                Thanks,
                Tapan
                • 5. Re: Trigger to audit DML and DDL nor working
                  367852
                  1. Either the trigger isn't working or disabled or your count is incorrect. Simple Logic says that both can't be true.
                  2. So for the heck of it, please check AGAIN whether the trigger is created on the same "owner.object" being used for deriving the count discrepancy.
                  • 6. Re: Trigger to audit DML and DDL nor working
                    Tapan Kumar Saha
                    Thank you all for all the help.

                    My issue is resolved now. Here is the analysis:

                    Our triggers (both for DML and DDL) were working earlier as well. However, it was not capturing the actions. We could see that last_ddl_date is being updated with the time when we are expecting the mischievous to happen. So, after having internal discussions, we concluded that "ALTER INDEX.." could also update the last_ddl_date. But it has nothing to do with the DELETE. However, to track the INDEX DDLs we added trigger on INDEX as well. Here is the modified code:

                    DDL Trigger
                    =======
                    CREATE OR REPLACE TRIGGER SLCPROD.STORE_TERM_ID_HST_TRG_TRUNC
                    AFTER DDL ON DATABASE
                    declare

                    object_name varchar2(30);

                    BEGIN

                    select distinct ora_dict_obj_name into object_name from dual;

                    if UPPER(object_name)= 'STORE_TERM_ID'
                    then

                    INSERT INTO STORE_TERM_ID_HIST
                    ( STR_NUM,
                    TERM_ID,
                    HOST_ENV,
                    USR_TERMINAL,
                    SESSION_USR,
                    OS_USER,
                    ACTION_DATE,
                    ACTION_NAME)
                    VALUES
                    ( 'NA',
                    'NA',

                    sys_context('USERENV', 'HOST'),
                    sys_context('USERENV', 'TERMINAL'),
                    sys_context('USERENV', 'SESSION_USER'),
                    sys_context('USERENV', 'OS_USER'),
                    SYSDATE,
                    ora_sysevent);

                    elsif UPPER(object_name)= 'STORE_TERM_ID_PK'
                    then

                    INSERT INTO STORE_TERM_ID_HIST
                    +( STR_NUM,+
                    TERM_ID,
                    HOST_ENV,
                    USR_TERMINAL,
                    SESSION_USR,
                    OS_USER,
                    ACTION_DATE,
                    ACTION_NAME)
                    VALUES
                    +( 'NA',+
                    +'NA',+

                    sys_context('USERENV', 'HOST'),
                    sys_context('USERENV', 'TERMINAL'),
                    sys_context('USERENV', 'SESSION_USER'),
                    sys_context('USERENV', 'OS_USER'),
                    SYSDATE,
                    ora_sysevent);
                    END IF;

                    END;
                    /

                    It started tracking the TRUNCATE on the table.
                    Also, we we increased the length of the data types for the temporary table to avoid any insertion errors. And finally put an INSERT trigger after seeing TRUNCATE DDLs. And we thus we resolved the issue.
                    • 7. Re: Trigger to audit DML and DDL nor working
                      Hemant K Chitale
                      How is the table maintained ? Is it in a Logical Standby configuration ? Is it subject to Advanced Replication / Streams / Golden Gate ?
                      Is it possible that there is a job that disables custom triggers, executes DML and then re-enables custom triggers ?


                      Hemant K Chitale
                      Update : I was composing my post and went away for a while. Didn't see your latest update. Yes, a TRUNCATE and INSERT pattern would also bypass the ON DELETE trigger.

                      Edited by: Hemant K Chitale on Apr 2, 2013 1:58 PM