Question: How does the
enable_ddl_logging parameter work to audit DDL?
Why is enable_ddl_logging superior to using a DDL
trigger?
Answer:
There are many ways to enable DDL logging, see my notes on
Oracle DDL auditing. Using enable_ddl_logging as
a DDL Log Auditing Alternative is easy starting in 11g, and
alternative to
DDL
triggers. If the
enable_ddl_logging parameter is set to TRUE in 11g, then
schema changes can be tracked in real time.
DDL Logging is enabled with this command:
alter system set
enable_ddl_logging=true;
The DDL logging data
is written in XML format to the file located at $DIAG\rdbms\DBName\INSTNAME\log\ddl
Wrong
changes can then be corrected easily by regenerating the
corresponding database objects from the dictionary baseline.
You can easily reverse-engineer the database and schema
definitions, capture and version baselines, and compare
databases and schemas or baselines.
Laurent Schneider adds this regarding a new and cool
alternative offered in Oracle 11g. This option involves the
use of enable_ddl_logging.
Setting enable_ddl_logging will allow the
tracking of all DDL's in the alert log using the following:
ALTER SYSTEM SET enable_ddl_logging=TRUE
Later, you issue create table:
create table t(x number)
and you see in the alertLSC01.log:
Tue Apr 05 14:43:32 2015
create
table t(x number);
Remember the alert log is just there for backward
compatibility, it is time you start looking in the xml file:
<msg time='2011-04-05T14:43:42.210+02:00'
org_id='oracle' comp_id='rdbms'
msg_id='opiexe:3937:4222333111'
client_id='' type='NOTIFICATION'group='schema_ddl'
level='16' host_id='srv01' host_addr='192.168.0.141'
module='TOAD Beta 11.0.0.52'
pid='2777799'>
<txt>create table t(x number)
</txt>
</msg>
There is not really much more there but the module, which
indeed reveals someone is using TOAD to access my database !
Unfortunately for many shops, enable_ddl_logging
is an
additional cost feature available only to Enterprise Edition
users. The Oracle docs note that you need to
purchase the database lifecycle management pack to use
enable_ddl_logging=true:
"The init.ora parameter
ENABLE_DDL_LOGGING is licensed as part of the Database
Lifecycle Management Pack when set to TRUE.
When set to TRUE, the database reports
schema changes in real time into the database alert log
under the message group schema_ddl. The default setting is
FALSE.
