DDL Event Security in Oracle Database

We have various levels of security (inside and outside of Oracle database) that can be implemented according to one's requirements. Mentioned here is a way of implementing security against structural changes or Data Definition Language (DDL) changes. This security is put within the database for logical objects.

What it means for the DBA

DDL commands are critical and cannot be rolled back. As a norm, new developments/changes should be done on test boxes before promoting them to Production. Such promotions to production can be done as scheduled upgrades during off-peak hours or when a maintenance window is available as system downtime. At all other times the system should be up and running as desired.

DDL event security is aimed at preventing structural changes when the site application is up and running. An application user may never fire such commands explicitly or may not have access to do so, but then, this security can be implemented in general and not aimed at a specific set of users. For example, a script may be fired on the production database by the IT teamin error.

Commands such as CREATE, ALTER, DROP and TRUNCATE can be tracked and audited or prevented from executing. The requirement here is to maintain the stability and availability of the system and prevent mishaps when the users are working.

The idea is to track down DDL commands when they are fired. This can be done using system event triggers introduced in Oracle 8i. Mentioned below is a simple process that I use at my site. If you plan to set up something similar, you can use the code below or modify it as per your needs.