DDL triggers are the triggers which are created over DDL statements such as CREATE, DROP or ALTER. Using this type of trigger you can monitor the behavior and force rules on your DDL statements. [Tweet This]
For detailed theory on the subject I would suggest you to read Introduction of Triggers in Oracle database. In this tutorial we will concentrate on the particular part where we will learn how to create a DDL trigger for auditing the schema/user and the whole database.
In order to proceed ahead and start writing the trigger first we need a table in which we can journal the auditing information created by the trigger.
Then Write, Execute and Compile the below trigger.
If you will notice carefully the second line of the code (“AFTER DDL ON SCHEMA”) indicates that this trigger will work on the schema in which it is created. On successful compilation this trigger will insert the respective information such as the date when the DDL is executed, username who executed the DDL, type of database object created, name of the object given by the user at the time of its creation and the type of DDL into the table which we created earlier.
Suggested reading: How to connect to the database using SYS user.
After doing that you have to create the above shown table under the same user so that your trigger can dump the auditing data without any read and write errors.
If you notice the second line of this code carefully then you will find that we have replaced the keyword Schema with the keyword Database which indicates that this trigger will work for the whole database and will perform the underlying work.
To create a trigger on database we require ADMINISTER DATABASE TRIGGER system privilege. All the administrative users such as sys or system already has these privileges by default that is the reason we created this database auditing DDL trigger using these users. Though you can create the same trigger with any user by granting the same privileges to them but that is not advisable because of your database security reasons.
Hope you enjoyed this tutorial and learnt something. You can help your friends or other people by sharing this blog on your social or by any means you find convenient. Thanks have a great day.
You can DOWNLOAD SQL script and presentation used in the Video and in this article.