Oracle DDL Triggers

Oracle DDL Triggers Oracle Tips by Burleson Consulting

Using Oracle DDL Triggers Auditing database changes: Oracle provides DDL triggers to audit all schema changes and can report the exact change, when it was made, and by which user.  There are several ways to audit within Oracle and the following auditing tools are provided: SQL audit command (for DML) Auditing with object triggers (DML auditing) Auditing with system-level triggers (DML and DDL) Auditing with LogMiner (DML and DDL) Fine-grained auditing (select auditing) DDL triggers: Using the Data Definition Language (DDL) triggers, the Oracle DBA can automatically track all changes to the database, including changes to tables, indexes, and constraints. The data from this trigger is especially useful for change control for the Oracle DBA. DDL triggers execute every time a DDL statement is executed, and adds new entries to your new table, as shown below: connect sys/manager create or replace trigger DDLTrigger AFTER DDL ON DATABASE BEGIN insert into perfstat.stats$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; / What is truly exciting is that we can combine the system-level triggers with STATSPACK extension tables, thereby building a mechanism that will give complete auditing information on user logon and logoff, DDL, and server errors. Just as an Oracle trigger fires on a specific DM event, system-level triggers are fired at specific system events such as logon, logoff, database startup, DDL execution, and servererror triggers DDL triggers -- Using Oracle DDL triggers, the Oracle administrator can automatically track all changes to the database including changes to tables, indexes, and constraints. The data from this trigger is especially useful for change control for the Oracle DBA.  Laurent Schneider also has this alternative DDL triggers script for DDL auditing: CREATE TABLE AUDIT_DDL (   d date,   OSUSER varchar2(255),   CURRENT_USER varchar2(255),   HOST varchar2(255),   TERMINAL varchar2(255),   owner varchar2(30),   type varchar2(30),   name varchar2(30),   sysevent varchar2(30));    create or replace trigger audit_ddl_trg after ddl on schema begin   if (ora_sysevent='TRUNCATE')   then     null; -- I do not care about truncate   else     insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent)     values(       sysdate,       sys_context('USERENV','OS_USER') ,       sys_context('USERENV','CURRENT_USER') ,       sys_context('USERENV','HOST') ,       sys_context('USERENV','TERMINAL') ,       ora_dict_obj_owner,       ora_dict_obj_type,       ora_dict_obj_name,       ora_sysevent     );   end if; end; / Now whenever a change is made, the details will be written to your table, and you can create audit reports for management. This report is critical for the Oracle DBA who must track changes to his or her production database. This report uses the DDL trigger and produces a complete audit log of all Oracle databases changes, as shown below: DDL table -- Using the code snippet shown below, we create an Oracle table to capture all of the salient metrics required to do effective change management within an Oracle environment. Note this table contains the date that the DDL was made, the ID of the user who originated the DDL change, the type of the object, and the object's name. This information can be quite useful for tracking purposes. DDL Triggers - DDL triggers execute every time a DDL statement is executed and adds new entries to the stats$ddl_log table. DDL Reports - This report is critical for the Oracle DBA who must track changes to their production database. This report uses Oracle DDL triggers and produces a complete audit log of all Oracle databases changes.