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;
/
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?