You Asked

We are trying to create a DDL trigger to track all the scripts that are being run from a specific user or specific set of objects.

We are using the following script to do so. This scripts works perfectly fine for all the objects but, it is not tracking any changes being done on triggers. Is it expected that trigger scripts cannot be tracked by a DB trigger?

Note: We are trying these scripts from ADMIN account in ATP database.

CREATE TABLE xxy_atp_ddl_audit_log
(
  object_owner      VARCHAR2(128)
, object_name       VARCHAR2(128)
, object_type       VARCHAR2(30)
, operation         VARCHAR2(30)
, sql_text          CLOB
, sid               NUMBER
, sessionid         NUMBER
, os_user           VARCHAR2(150)
, user_module       VARCHAR2(150)
, hostname          VARCHAR2(128)
, ip_address        VARCHAR2(150)
, execution_user    VARCHAR2(128)
, execution_time    TIMESTAMP
);

-- Script to be run from SYS/ADMIN schema
CREATE OR REPLACE TRIGGER xx_atp_ddl_audit_trg
AFTER DDL ON DATABASE
DECLARE
  l_complete_sql_statement  CLOB;
  l_sql_statement_t         ora_name_list_t;
  l_sql_line_count          NUMBER;
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  -- We need to track all the change happening to custom schemas or custom objects
  -- We dont need to track truncate table as it doesnt impact any table structure.
  IF (ora_dict_obj_owner LIKE 'XX%'
  OR ora_dict_obj_name LIKE 'XX%')
  AND ora_sysevent != 'TRUNCATE'
  THEN
    dbms_lob.createtemporary(lob_loc => l_complete_sql_statement, cache => TRUE);

    l_sql_line_count := ora_sql_txt (l_sql_statement_t);

    FOR l_idx IN 1 .. l_sql_line_count
    LOOP
      dbms_lob.append(l_complete_sql_statement, l_sql_statement_t (l_idx));
    END LOOP;

    INSERT INTO xx_atp_ddl_audit_log (object_owner
                                    , object_name
                                    , object_type
                                    , operation
                                    , sql_text
                                    , sid
                                    , sessionid
                                    , os_user
                                    , user_module
                                    , hostname
                                    , ip_address
                                    , execution_user
                                    , execution_time)
         VALUES (ora_dict_obj_owner
               , ora_dict_obj_name
               , ora_dict_obj_type
               , ora_sysevent
               , REPLACE(l_complete_sql_statement, CHR(0), '')
               , sys_context('USERENV', 'SID')
               , sys_context('USERENV', 'SESSIONID')
               , sys_context('USERENV', 'OS_USER')
               , sys_context('USERENV', 'MODULE')
               , sys_context('USERENV', 'HOST')
               , sys_context('USERENV', 'IP_ADDRESS')
               , sys_context('USERENV', 'SESSION_USER')
               , LOCALTIMESTAMP);

    COMMIT;

    dbms_lob.freetemporary(lob_loc => l_complete_sql_statement);
  END IF;
EXCEPTION
  WHEN OTHERS
  THEN
    -- Clear clobs if they are left initialized
    IF dbms_lob.isopen(l_complete_sql_statement) = 1
    THEN
      dbms_lob.freetemporary(lob_loc => l_complete_sql_statement);
    END IF;
END xx_atp_ddl_audit_trg;
/

and Chris said...

I can't reproduce on my Always Free 21c ATP instance:

CREATE TABLE xxy_atp_ddl_audit_log
(
  object_owner      VARCHAR2(128)
, object_name       VARCHAR2(128)
, object_type       VARCHAR2(30)
, operation         VARCHAR2(30)
, sql_text          CLOB
, sid               NUMBER
, sessionid         NUMBER
, os_user           VARCHAR2(150)
, user_module       VARCHAR2(150)
, hostname          VARCHAR2(128)
, ip_address        VARCHAR2(150)
, execution_user    VARCHAR2(128)
, execution_time    TIMESTAMP
);

CREATE OR REPLACE TRIGGER xx_atp_ddl_audit_trg
AFTER DDL ON DATABASE
DECLARE
  l_complete_sql_statement  CLOB;
  l_sql_statement_t         ora_name_list_t;
  l_sql_line_count          NUMBER;
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  -- We need to track all the change happening to custom schemas or custom objects
  -- We dont need to track truncate table as it doesnt impact any table structure.
  IF (ora_dict_obj_owner LIKE 'CHRIS%'
  OR ora_dict_obj_name LIKE 'CHRIS%')
  AND ora_sysevent != 'TRUNCATE'
  THEN
    dbms_lob.createtemporary(lob_loc => l_complete_sql_statement, cache => TRUE);

    l_sql_line_count := ora_sql_txt (l_sql_statement_t);

    FOR l_idx IN 1 .. l_sql_line_count
    LOOP
      dbms_lob.append(l_complete_sql_statement, l_sql_statement_t (l_idx));
    END LOOP;

    INSERT INTO xxy_atp_ddl_audit_log (object_owner
                                    , object_name
                                    , object_type
                                    , operation
                                    , sql_text
                                    , sid
                                    , sessionid
                                    , os_user
                                    , user_module
                                    , hostname
                                    , ip_address
                                    , execution_user
                                    , execution_time)
         VALUES (ora_dict_obj_owner
               , ora_dict_obj_name
               , ora_dict_obj_type
               , ora_sysevent
               , REPLACE(l_complete_sql_statement, CHR(0), '')
               , sys_context('USERENV', 'SID')
               , sys_context('USERENV', 'SESSIONID')
               , sys_context('USERENV', 'OS_USER')
               , sys_context('USERENV', 'MODULE')
               , sys_context('USERENV', 'HOST')
               , sys_context('USERENV', 'IP_ADDRESS')
               , sys_context('USERENV', 'SESSION_USER')
               , LOCALTIMESTAMP);

    COMMIT;

    dbms_lob.freetemporary(lob_loc => l_complete_sql_statement);
  END IF;
EXCEPTION
  WHEN OTHERS
  THEN
    -- Clear clobs if they are left initialized
    IF dbms_lob.isopen(l_complete_sql_statement) = 1
    THEN
      dbms_lob.freetemporary(lob_loc => l_complete_sql_statement);
    END IF;
END xx_atp_ddl_audit_trg;
/

conn chris@atpfree

create table t (
  c1 int
);

create or replace trigger trg after insert on t
begin
  null;
end;
/

select object_owner, object_name, object_type, sql_text 
from   admin.xxy_atp_ddl_audit_log;
/*
OBJECT_OWN OBJECT_NAM OBJECT_TYP SQL_TEXT                                                                        
---------- ---------- ---------- --------------------------------------------------------------------------------
CHRIS      T          TABLE      create table t (                                                                
                                   c1 int                                                                        
                                 )                                                                               

CHRIS      TRG        TRIGGER    create or replace trigger trg after insert on t                                 
                                 begin                                                                           
                                   null;                                                                         
                                 end;  
*/

How have you found this isn't tracking trigger changes? Can you supply a complete test case?

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

what if we recompile the trigger xx_atp_ddl_audit_trg

Pradeep, November 07, 2023 - 4:40 pm UTC

If we recompile the trigger

xx_atp_ddl_audit_trg

would it insert a row in audit table?

November 09, 2023 - 4:59 am UTC

A recompile on a valid object is basically a no-op, eg

SQL> CREATE TABLE SYSTEM.DDL_LOG
  2  (
  3    TSTAMP       TIMESTAMP(6)   NOT NULL,
  4    HOST         VARCHAR2(100),
  5    IP_ADDRESS   VARCHAR2(100),
  6    MODULE       VARCHAR2(100),
  7    OS_USER      VARCHAR2(100),
  8    TERMINAL     VARCHAR2(100),
  9    OPERATION    VARCHAR2(100),
 10    OWNER        VARCHAR2(50),
 11    OBJECT_NAME  VARCHAR2(50),
 12    OBJECT_TYPE  VARCHAR2(50),
 13    SQLTEXT      CLOB
 14  )
 15  /

Table created.

SQL> CREATE OR REPLACE TRIGGER SYSTEM.CAPTURE_ALL_DDL
  2  after ddl on database
  3  begin
  4      insert into SYSTEM.ddl_log
  5      values (systimestamp,
  6                  sys_context('USERENV','HOST'),
  7                  sys_context('USERENV','IP_ADDRESS'),
  8                  sys_context('USERENV','MODULE'),
  9                  sys_context('USERENV','OS_USER'),
 10                  sys_context('USERENV','TERMINAL'),
 11                  ora_sysevent,
 12                  ora_dict_obj_owner,
 13                  ora_dict_obj_name,
 14                  ora_dict_obj_type,
 15                  --
 16                  -- In my case I choose to not log PL/SQL source, just the fact that it had been changed
 17                  -- but you can do whatever you like here.
 18                  --
 19                  case when ora_dict_obj_type not in ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY') and ora_sysevent != 'DROP' then
 20                    ( select sql_fulltext from v$sql
 21                      where sql_id = ( select sql_id from v$session where sid = sys_context('USERENV','SID') )
 22                      and rownum = 1
 23                    )
 24                  end
 25             );
 26
 27  exception
 28    when others then null;  -- we wil not STOP the ddl if we fail to track it
 29  end;
 30  /

Trigger created.

SQL>
SQL> select last_ddl_time from user_objects
  2  where object_name = 'CAPTURE_ALL_DDL';

LAST_DDL_TIME
-------------------
09/11/2023 12:58:27

1 row selected.

SQL>
SQL> alter trigger SYSTEM.CAPTURE_ALL_DDL compile;

Trigger altered.

SQL> alter trigger SYSTEM.CAPTURE_ALL_DDL compile;

Trigger altered.

SQL> alter trigger SYSTEM.CAPTURE_ALL_DDL compile;

Trigger altered.

SQL> alter trigger SYSTEM.CAPTURE_ALL_DDL compile;

Trigger altered.

SQL> alter trigger SYSTEM.CAPTURE_ALL_DDL compile;

Trigger altered.

SQL> alter trigger SYSTEM.CAPTURE_ALL_DDL compile;

Trigger altered.

SQL>
SQL> select last_ddl_time from user_objects
  2  where object_name = 'CAPTURE_ALL_DDL';

LAST_DDL_TIME
-------------------
09/11/2023 12:58:27

1 row selected.

SQL>
SQL>

Pradeep, November 09, 2023 - 1:00 am UTC

@chris

It is not capturing the event when I recompile the same trigger again and again. Is that expected?

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

what if we recompile the trigger xx_atp_ddl_audit_trg

More to Explore

PL/SQL demos

PL/SQL docs

Create trigger scripts are not captured

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

what if we recompile the trigger xx_atp_ddl_audit_trg

More to Explore

PL/SQL demos

PL/SQL docs