DDL DROP Monitoring via Oracle Database Trigger

This trigger will monitor DDL, specifically DROP statements and log the schema (user) performing the drop into a table called DDL_LOG. A test script is presented below. Before you use this in production make sure you think through the location of the log table, and size it appropriately. If the trigger fails to fire there can be complications, so make sure you test this and understand it before you deploy it outside of a test instance.

conn / as sysdba

create user abc identified by abc;

CREATE TABLE ddl_log (
operation VARCHAR2(30),
obj_owner VARCHAR2(30),
object_name VARCHAR2(30),
sql_text VARCHAR2(64),
attempt_by VARCHAR2(30),
attempt_dt DATE);

CREATE OR REPLACE TRIGGER bds_trigger BEFORE DROP ON DATABASE
DECLARE 
oper system.ddl_log.operation%TYPE;
BEGIN 
INSERT INTO system.ddl_log 
SELECT ora_sysevent, 
ora_dict_obj_owner, 
ora_dict_obj_name, 
NULL, 
USER, 
SYSDATE
FROM dual;
END bds_trigger;

/

-- now let's test the logging mechanism 
select * from system.ddl_log;

drop user abc;

select * from system.ddl_log;

select * from DBA_USERS where lower(username) like lower('te%');