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.