Log DDL (drop and truncate) On the oracle database

By this steps, you logged all DDL attempt to your database or specific schema . you can prevent these also.


1. This is a log table
=======================

CREATE TABLE DDL_ATTEMP_LOG
(
OPERATION VARCHAR2(30 BYTE),
OBJ_OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(30 BYTE),
SQL_TEXT VARCHAR2(64 BYTE),
ATTEMPT_BY VARCHAR2(30 BYTE),
ATTEMPT_DT DATE,
IP_ADD VARCHAR2(200 BYTE),
OS_USER VARCHAR2(200 BYTE),
TER_NAME VARCHAR2(200 BYTE),
HOST_NAME VARCHAR2(200 BYTE)
)

=============================================================================
2. This procedure insert all drop/truncate ddl attempt to DDL_ATTEMP_LOG table
(when save_our_db_FROM_DDL trigger is fired)
=============================================================================


CREATE PROCEDURE ddl_attemp_log_proc (
ose ddl_attemp_log.operation%TYPE,
odoo ddl_attemp_log.obj_owner%TYPE,
odon ddl_attemp_log.object_name%TYPE
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ddl_attemp_log
SELECT ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name,
'Not Allowed', USER, SYSDATE,SYS_CONTEXT ('userenv', 'ip_address')
ip_add,sys_context('USERENV','OS_USER') os_user
,sys_context('USERENV', 'TERMINAL') ter_name,sys_context('USERENV',
'HOST') host_name
FROM DUAL;
if sql%found then

COMMIT;
else
rollback ;
end if ;
exception
when others then
null ;
END ddl_attemp_log_proc;



========================================================
3. This triger is fired when Drop/truncate ddl happen in
any objects of the Database.
========================================================


CREATE OR REPLACE TRIGGER save_our_db_FROM_DDL
BEFORE DROP OR TRUNCATE
or ALTER OR RENAME OR GRANT OR REVOKE OR AUDIT OR COMMENT OR CREATE OR ANALYZE
ON database ---on schema
DECLARE
oper ddl_attemp_log.operation%TYPE;
BEGIN

begin
SELECT ora_sysevent
INTO oper
FROM DUAL;
exception
when others then
null ;
end ;

if nvl(ora_dict_obj_name,'ZZZ') in ('STGLOBTM') then---global temporary table
null ;
else
ddl_attemp_log_proc(ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name);
end if ;
/*
IF oper = 'DROP'
THEN
raise_application_error (-20998, 'Attempt To Drop In Production DB Has Been
Restricted contract with DBA'
);
END IF;
*/
----ALTER TRIGGER LOGG.SAVE_OUR_DB_FROM_DDL DISABLE;
END save_our_db_FROM_DDL;
/



4) Query your log history table.
====================================

SELECT * FROM DDL_ATTEMP_LOG;