(ref) Oracle DDL Triggers

DDL Triggers

Can be used to:

• Monitor significant events in the database.
• Monitor errant code that can that can corrupt or destabilize your database.
• Use these in development, test, and stage systems to understand and monitor the dynamics of database activities.
• Also useful when you patch your application code. They can let you find
  potential changes between releases.
• During an upgrade: use instead-of create trigger to enforce table creation storage clauses or partitioning rules.
• Track the creation and modification of tables by application programs that lead to database fragmentation.
• Effective security tools: monitor GRANT and REVOKE privilege statements.

When can they fire? DDL Events
ALTER, ANALYZE, ASSOCIATE STATISTICS, AUDIT, COMMENT, CREATE, DDL (means: ANY DDL event), DISASSOCIATE STATISTICS, DROP, GRANT, NOAUDIT, RENAME, REVOKE, TRUNCATE

System-defined event attribute functions
ORA_CLIENT_IP_ADDRESS	Returns the client IP address as a VARCHAR2. DECLARE ip_address VARCHAR2(11); BEGIN IF ora_sysevent = 'LOGON' THEN ip_address := ora_client_ip_address; END IF; END;
ORA_DATABASE_NAME	Returns the database name. BEGIN (...) db_name := ora_database_name; (...) END;
ORA_DES_ENCRYPTED_PASSWORD	- Returns the DES-encrypted password as VARCHAR2. - Equivalent to the value in the SYS.USER$ table PASSWORD col (11g). - Passwds are no longer accessible in DBA_USERS or ALL_USERS BEGIN IF ora_dict_obj_type = 'USER' THEN password := ora_des_encrypted_password; END IF; END;
ORA_DICT_OBJ_NAME	Returns the name of the object target of the DDL statement. DECLARE database VARCHAR2(50); BEGIN database := ora_obj_name; END;
ORA_DICT_OBJ_NAME_LIST	The function returns the number of elements in the list as a PLS_INTEGER datatype. The name_list contains the list of object names touched by the triggering event. DECLARE name_list DBMS_STANDARD.ORA_NAME_LIST_T; counter PLS_INTEGER; BEGIN IF ora_sysevent = 'ASSOCIATE_STATISTICS' THEN counter := ora_dict_obj_name_list(name_list); END IF; END;
ORA_DICT_OBJ_OWNER	returns an owner of the object acted upon by the event. database := ora_dict_obj_owner;
ORA_DICT_OBJ_OWNER_LIST	formal parameter datatype is defined in the DBMS_STANDARD package as ORA_NAME_LIST_T (table of varchar2(64)). The function returns the number of elements in the list. The owner_list contains the list of owners of objects affected by the event. DECLARE owner_list DBMS_STANDARD.ORA_NAME_LIST_T; counter PLS_INTEGER; BEGIN IF ora_sysevent = 'ASSOCIATE_STATISTICS' THEN counter := ora_dict_obj_owner_list(owner_list); END IF; END;
ORA_DICT_OBJ_TYPE
ORA_GRANTEE
ORA_INSTANCE_NUM
ORA_IS_ALTER_COLUMN
ORA_IS_CREATING_NESTED_TABLE	BEGIN IF ora_sysevent = 'CREATE' AND ora_dict_obj_type = 'TABLE' AND ora_is_creating_nested_table THEN INSERT INTO logging_table VALUES (ora_dict_obj_name||'.'||' created with nested table.'); END IF; END;
ORA_IS_DROP_COLUMN
ORA_IS_SERVERERROR
ORA_LOGIN_USER>
ORA_PARTITION_POS	returns the numeric position with the SQL text where you can insert a partition clause. This is only available in an INSTEAD OF CREATE trigger. DECLARE sql_text ORA_NAME_LIST_T; sql_stmt VARCHAR2(32767); partition VARCHAR2(32767) := 'partitioning_clause'; BEGIN FOR i IN 1..ora_sql_txt(sql_text) LOOP sql_stmt := sql_stmt || sql_text(i); END LOOP; sql_stmt := SUBSTR(sql_text,1,ora_partition_pos – 1) ||' ' || partition||' '|| SUBSTR(sql_test,ora_partition_pos); -- Add logic to prepend schema because -- this runs under SYSTEM. sql_stmt := REPLACE(UPPER(sql_stmt),'CREATE TABLE ' ,'CREATE TABLE '||ora_login_user||'.'); EXECUTE IMMEDIATE sql_stmt; END;
ORA_PRIVILEGE_LIST	Returns list of privileges granted/revoked.
ORA_REVOKEE
ORA_SERVER_ERROR
ORA_SERVER_ERROR_DEPTH
ORA_SERVER_ERROR_MSG
ORA_SERVER_ERROR_NUM_PARAMS
ORA_SERVER_ERROR_PARAM
ORA_SQL_TXT	Returns the substring of the processed SQL statement that triggered the event.
ORA_SYSEVENT
ORA_WITH_GRANT_OPTION
SPACE_ERROR_INFO

DDL Triggers: Syntax

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} ddl_event ON {DATABASE | SCHEMA}
[WHEN (logical_expression)]
[DECLARE]
 declaration_statements;
BEGIN
 execution_statements;
END [trigger_name];
/

DDL Trigger on Creation statements

(a) create table audit_creation
(b) create sequence audit_creation_s1
(c) Create trigger audit_creation
(d) create a synonym to fire the trigger
(e) check the inserted row on audit_creation

CREATE TABLE audit_creation
( audit_creation_id NUMBER PRIMARY KEY,
  audit_owner_name VARCHAR2(30) NOT NULL,
  audit_obj_name   VARCHAR2(30) NOT NULL,
  audit_date       DATE NOT NULL);

CREATE SEQUENCE audit_creation_s1;



CREATE OR REPLACE TRIGGER audit_creation
 BEFORE CREATE ON SCHEMA
BEGIN
  insert into audit_creation values
  (audit_creation_s1.nextval, 
   ORA_DICT_OBJ_OWNER,
   ORA_DICT_OBJ_NAME,
   sysdate);
END audit_creation;

SQL> Create synonym empsym for hr.employees;

synonym created.
SQL> Select * from audit_creation;

AUDIT_CREATION_ID    AUDIT_OWNER_NAME       AUDIT_OBJ_NAME      AUDIT_DATE
-------------------  ---------------------- ------------------- ---------------
1                    HR                     EMPSYN              22-AUG-10