Oracle DDL Event Triggers
Version 12.1.0.1

General Information
Library Note This page has been updated for Oracle release 12cR1 and shown to be compatible in the Container DB.
Data Dictionary Objects
ALL_TRIGGERS TRIGGER$ USER_TRIGGERS
DBA_TRIGGERS    
Object Privileges No object privileges are associated with a DDL Event Trigger
System Privileges

* Reqd. for ON DATABASE triggers
ADMINISTER DATABASE TRIGGER * CREATE ANY TRIGGER DROP TRIGGER
ALTER ANY TRIGGER CREATE TRIGGER  
Triggering Events BEFORE / AFTER ALTER
BEFORE / AFTER ANALYZE
BEFORE / AFTER ASSOCIATE STATISTICS
BEFORE / AFTER AUDIT
BEFORE / AFTER COMMENT
BEFORE / AFTER CREATE
BEFORE / AFTER DDL
BEFORE / AFTER DISASSOCIATE STATISTICS
BEFORE / AFTER DROP
BEFORE / AFTER GRANT
BEFORE / AFTER NOAUDIT
BEFORE / AFTER RENAME
BEFORE / AFTER REVOKE
BEFORE / AFTER TRUNCATE
AFTER SUSPEND
Find Datbase Level Triggers SELECT a.obj#, a.sys_evts, b.name
FROM trigger$ a,obj$ b
WHERE a.sys_evts > 0
AND a.obj#=b.obj#
AND baseobject = 0;
Find Schema Level Triggers SELECT a.obj#, a.sys_evts, b.name
FROM trigger$ a,obj$ b
WHERE a.sys_evts > 0
AND a.obj#=b.obj#
AND baseobject = 88;
 
Demo Setup
Demo Privileges -- user uwclass creation can be found on the SETUP page of the library

GRANT create session TO uwclass;
GRANT create procedure TO uwclass;
GRANT create sequence TO uwclass;
GRANT create table TO uwclass;
GRANT create trigger TO uwclass;
GRANT create view TO uwclass;

GRANT select ON gv_$open_cursor TO uwclass;

conn uwclass/uwclass
Table To Capture DDL Trigger Output 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);
 
DDL Triggers
Trigger To Log A Single DDL Activity On A Schema CREATE OR REPLACE TRIGGER <trigger_name>
<BEFORE | AFTER> <triggering_action>
ON <SCHEMA | DATABASE>

DECLARE
 -- variable declarations
BEGIN
  -- trigger code
EXCEPTION
  -- exception handler
END <trigger_name>;
/
CREATE OR REPLACE TRIGGER bcs_trigger
BEFORE CREATE
ON SCHEMA

BEGIN
  INSERT INTO ddl_log
  SELECT ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, NULL, USER, SYSDATE
  FROM dual;
END bcs_trigger;
/

col object_name format a20

SELECT object_name, object_type
FROM user_objects;

col triggering_event format a20
SELECT trigger_name, trigger_type,
triggering_event, base_object_type
FROM user_triggers;

SELECT * FROM ddl_log;

CREATE SEQUENCE s1_test;

CREATE TABLE t1_test (
testcol VARCHAR2(20));

CREATE OR REPLACE VIEW v_test AS
SELECT * FROM t_test;

set linesize 151

SELECT operation, obj_owner, object_name
FROM ddl_log;

TRUNCATE TABLE ddl_log;

conn system/manager

CREATE TABLE uwclass.xyz (
testcol VARCHAR2(20));

conn uwclass/uwclass

SELECT operation, obj_owner, object_name
FROM ddl_log;

TRUNCATE TABLE ddl_log;
Trigger To Log A Single DDL Activity On The Database CREATE OR REPLACE TRIGGER <trigger_name>
<BEFORE | AFTER> <triggering_action>
ON <SCHEMA | DATABASE>

DECLARE
 -- variable declarations
BEGIN
  -- trigger code
EXCEPTION
  -- exception handler
END <trigger_name>;
/
conn system/manager

GRANT administer database trigger TO uwclass;

conn uwclass/uwclass

CREATE OR REPLACE TRIGGER bcd_trigger
BEFORE CREATE
ON DATABASE

BEGIN
  INSERT INTO ddl_log
  SELECT ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name, NULL, USER, SYSDATE
  FROM dual;
END bcd_trigger;
/

col object_name format a20
SELECT object_name, object_type
FROM user_objects;

col triggering_event format a20
SELECT trigger_name, trigger_type,
triggering_event, base_object_type
FROM user_triggers;

SELECT * FROM ddl_log;

CREATE SEQUENCE s2_test;

CREATE TABLE t2_test (
testcol VARCHAR2(20));

CREATE OR REPLACE VIEW v_test AS
SELECT * FROM t_test;

set linesize 150

SELECT operation, obj_owner, object_name
FROM ddl_log;

TRUNCATE TABLE ddl_log;

conn system/manager

CREATE TABLE uwclass.xyz (
testcol VARCHAR2(20));

conn uwclass/uwclass

SELECT operation, obj_owner, object_name
FROM ddl_log;

DROP TRIGGER bcd_trigger;

TRUNCATE TABLE ddl_log;
Trigger To Log Multiple DDL Activities CREATE OR REPLACE TRIGGER <trigger_name>
<BEFORE | AFTER> <triggering_action> OR <trigger_action>
ON SCHEMA
DECLARE
 -- variable declarations
BEGIN
  -- trigger code
EXCEPTION
  -- exception handlers
END <trigger_name>;
/
desc ddl_log

CREATE OR REPLACE TRIGGER ddl_trigger
BEFORE CREATE OR ALTER OR DROP
ON SCHEMA

DECLARE
 oper ddl_log.operation%TYPE;
 sql_text ora_name_list_t;
 i        PLS_INTEGER;
BEGIN
  SELECT ora_sysevent
  INTO oper
  FROM dual;

  i := sql_txt(sql_text);

  IF oper IN ('CREATE', 'DROP') THEN
    INSERT INTO ddl_log
    SELECT ora_sysevent, ora_dict_obj_owner,
    ora_dict_obj_name, sql_text(1), USER, SYSDATE
    FROM dual;
  ELSIF oper = 'ALTER' THEN
    INSERT INTO ddl_log
    SELECT ora_sysevent, ora_dict_obj_owner,
    ora_dict_obj_name, sql_text(1), USER, SYSDATE
    FROM sys.gv_$sqltext
    WHERE UPPER(sql_text) LIKE 'ALTER%'
    AND UPPER(sql_text) LIKE '%NEW_TABLE%';
  END IF;
END ddl_trigger;
/

col operation format a20
col obj_owner format a10

SELECT * FROM ddl_log;

conn / as sysdba

alter system flush shared_pool;
alter system flush shared_pool;

conn uwclass/uwclass

CREATE TABLE new_table (
charcol VARCHAR2(20));

SELECT * FROM ddl_log;

ALTER TABLE new_table
ADD (numbcol NUMBER(10));

SELECT * FROM ddl_log;

DROP TABLE new_table PURGE;

SELECT * FROM ddl_log;

TRUNCATE TABLE ddl_log;
 
Demo Triggers
DDL Trigger To Stop And Log Attempts To Drop Or Truncate

Requires the stored procedure below is created first
-- NOTE: Be sure to build the log_proc procedure (below) before trying to create this trigger

CREATE OR REPLACE TRIGGER save_our_db
BEFORE DROP OR TRUNCATE
ON SCHEMA

DECLARE
 oper ddl_log.operation%TYPE;
BEGIN
  oper := ora_sysevent;
  log_proc(ora_sysevent, ora_dict_obj_owner, ora_dict_obj_name);

  IF oper = 'DROP' THEN
    RAISE_APPLICATION_ERROR(-20998, 'Attempt To Drop
    In Production Has Been Logged');
  ELSIF oper = 'TRUNCATE' THEN
    RAISE_APPLICATION_ERROR(-20999, 'Attempt To Truncate A
    Production Table Has Been Logged');
  END IF;
END save_our_db;
/

SELECT * FROM ddl_log;

DROP TRIGGER ddl_trigger;

SELECT * FROM ddl_log;

ALTER TRIGGER save_our_db DISABLE;

DROP TRIGGER ddl_trigger;

SELECT * FROM ddl_log;

TRUNCATE TABLE ddl_log;

SELECT * FROM ddl_log;

ALTER TRIGGER save_our_db ENABLE;

DROP VIEW v_test;

SELECT * FROM ddl_log;

SELECT object_name FROM user_objects;

DROP SEQUENCE v1_test;

SELECT * FROM ddl_log;

SELECT object_name FROM user_objects;

DROP TRIGGER save_our_db;

TRUNCATE TABLE ddl_log;
Logging Procedure CREATE OR REPLACE PROCEDURE log_proc (
ose  ddl_log.operation%TYPE,
odoo ddl_log.obj_owner%TYPE,
odon ddl_log.object_name%TYPE)
IS
 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO ddl_log
  SELECT ora_sysevent, ora_dict_obj_owner,
  ora_dict_obj_name, 'Not Allowed', USER, SYSDATE
  FROM dual;
  COMMIT;
END log_proc;
/
DDL Trigger To Prevent Creating Objects That Whose Names Begin With The Letter 'X' CREATE OR REPLACE TRIGGER no_xtabs
BEFORE CREATE
ON SCHEMA

DECLARE
 x user_tables.table_name%TYPE;
BEGIN
  SELECT ora_dict_obj_name
  INTO x
  FROM dual;

  IF SUBSTR(x, 1, 1) = 'X' THEN
    RAISE_APPLICATION_ERROR(-20099, 'Table Names Can Not Start With The Letter X');
  END IF;
END no_xtabs;
/

CREATE TABLE ztest (
testcol VARCHAR2(20));

CREATE TABLE xtest (
testcol VARCHAR2(20));
Demo of an application that identifies and logs all DDL conn / as sysdba

CREATE TABLESPACE logging
DATAFILE 'c:\temp\logtsp01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 64K;

CREATE USER loguser
IDENTIFIED BY loguser
DEFAULT TABLESPACE logging
TEMPORARY TABLESPACE temp
QUOTA 0 ON SYSTEM
QUOTA UNLIMITED ON logging;

-- note that no system privileges have been granted the table is being built by SYS

CREATE TABLE loguser.ddl_log (
user_name     VARCHAR2(30),
ddl_date      DATE,
ddl_type      VARCHAR2(30),
object_type   VARCHAR2(18),
owner         VARCHAR2(30),
object_name   VARCHAR2(128))
TABLESPACE logging;

CREATE OR REPLACE TRIGGER ddl_trig
AFTER DDL
ON DATABASE

BEGIN
  INSERT INTO loguser.ddl_log
  (user_name, ddl_date, ddl_type,
   object_type, owner,
   object_name)
  VALUES
  (ora_login_user, SYSDATE, ora_sysevent,
   ora_dict_obj_type, ora_dict_obj_owner,
   ora_dict_obj_name);
END ddl_trig;
/

conn uwclass/uwclass

CREATE TABLE t3_test (
testcol DATE);

conn / as sysdba

SELECT * FROM loguser.ddl_log;
Disable granting privileges to PUBLIC GRANT ALL ON servers TO SCOTT;
GRANT ALL ON servers TO PUBLIC;
REVOKE ALL ON servers FROM SCOTT;
REVOKE ALL ON servers FROM PUBLIC;

CREATE OR REPLACE TRIGGER ddl_trig
BEFORE GRANT
ON DATABASE

DECLARE
 g_list dbms_standard.ora_name_list_t;
 n      PLS_INTEGER;
BEGIN
  n := ora_grantee(g_list);
  FOR i IN 1..n LOOP
    IF g_list(i) = 'PUBLIC' THEN
      RAISE_APPLICATION_ERROR(-20997,'Public Grants Not Allowed');
    END IF;
  END LOOP;
END;
/

GRANT ALL ON servers TO SCOTT;
GRANT ALL ON servers TO PUBLIC;
REVOKE ALL ON servers FROM SCOTT;
REVOKE ALL ON servers FROM PUBLIC;
Disasble granting privileges not part of the standard application image conn sys@pdborcl as sysdba

CREATE OR REPLACE TRIGGER grant_control
BEFORE GRANT
ON DATABASE

DECLARE
 g_list dbms_standard.ora_name_list_t;
 n      PLS_INTEGER;
 lValid BOOLEAN := FALSE;

 TYPE rl_array IS TABLE OF VARCHAR2(30)
 INDEX BY BINARY_INTEGER;
 rp_list rl_array;
 
 TYPE sp_array IS TABLE OF VARCHAR2(30)
 INDEX BY BINARY_INTEGER;
 sp_list sp_array;
BEGIN
  p_list(1) := 'CONNECT';
  p_list(2) := 'RESOURCE';
  p_list(3) := 'CREATE ANY CONTEXT';
  p_list(4) := 'CREATE ANY TYPE';
  p_list(5) := 'UNLIMITED TABLESPACE';

  n := ora_privilege_list(g_list);

  FOR i IN 1..n LOOP
    FOR j IN 1..p_list.last LOOP
      IF g_list(i) = p_list(j) THEN
        lValid := TRUE;
        dbms_output.put_line(g_list(j) || ' OK');
        EXIT;
      END IF;
    END LOOP;
    IF NOT lValid THEN
        EXIT;
      END IF;
  END LOOP;
END grant_control;
/

    IF g_list(i) = 'PUBLIC' THEN
      RAISE_APPLICATION_ERROR(-20997,'CAB Approval Required');
    END IF;

Related Topics
Instead Of Trigger
System Events
System Event Triggers
SYS_CONTEXT
Table Triggers
$ORACLE_HOME/rdbms/admin/utlrdt.sql

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved