Oracle 12c DDL Logging

By default Oracle database does not log any DDL operations performed by any user. The default settings for auditing only logs DML operations. However that does not mean that you cannot do anything about it. Oracle has provided several ways by which you can track the DDL operations performed on the database. In this article we will look at two of the most common and easy to use ways.

Oracle 12c DDL Logging – ENABLE_DDL_LOGGING

The first method is by using the enabling a DDL logging feature built into the database. By default it is turned off and you can turn it on by setting the value of ENABLE_DDL_LOGGING initialization parameter to true.

SQL> show parameter DDL_logging;
NAME TYPE VALUE
------------------------------------ ----------- 
ENABLE_DDL_LOGGING boolean FALSE

We can turn it on using the following command. The parameter is dynamic and you can turn it on/off on the go.

SQL> alter system set ENABLE_DDL_LOGGING=true;
System altered.
Elapsed: 00:00:00.05
SQL>

Once it is turned on, every DDL command will be logged in the alert log file and also the log.xml file. The information in the alert log will be very concise. However you can find some session related information in the log.xml file but you won’t be able to get the information about the user.

Now lets run a DDL command and see what information is available in both files.

SQL> create table emp as select * from scott.emp;
Table created.
Elapsed: 00:00:00.35
SQL>

The alert log file has the following information about the command.
Mon Apr 22 00:26:39 2013
create table emp as select * from scott.emp

The log.xml file however has some session level details as shown below.

<msg time='2013-04-22T00:26:39.242+05:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:3961:4222364190' client_id='' type='NOTIFICATION'
group='schema_DDL' level='16' host_id='localhost.localdomain'
host_addr='::1' module='sqlplus@localhost.localdomain (TNS V1-V3)' pid='3498'>
<txt>create table emp as select * from scott.emp</txt>
</msg>

Using AUDIT_TRAIL

The second way to audit the DDL command is to use the built-in auditing functionality. By default the value of AUDIT_TRAIL parameter is set to DB which is not good enough if you want to audit DDL. You need to set this parameter to DB_EXTENDED.

SQL> show parameter AUDIT_TRAIL;
NAME TYPE VALUE
------------------------------------ ----------- -----------------
AUDIT_TRAIL string DB
SQL> alter system set AUDIT_TRAIL='db_extended' scope=spfile;
System altered.

Elapsed: 00:00:00.15

The parameter is static and will require a database restart.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1690705920 bytes
Fixed Size 1345380 bytes
Variable Size 1040189596 bytes
Database Buffers 637534208 bytes
Redo Buffers 11636736 bytes
Database mounted.
Database opened.
SQL> show parameter AUDIT_TRAIL;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
AUDIT_TRAIL string DB_EXTENDED

That’s it. From now on every DDL statement will have an audit record in SYS.AUD$ table except for the DDL executed by the SYS user itself. The statements run by SYS will not be audited.

The following command will turn the auditing on for tables, if not already set.

SQL> audit table;
Audit succeeded.
Elapsed: 00:00:00.13

Lets check this in action. We will create a new table using Scott schema and then we will drop it. We will also drop an already existing table from SYS schema as well. We will finally view the AUD$ table to see the audit records of these statements.

$ sqlplus scott/tiger
...
SQL> create table emp_t as select * from emp;
Table created.

Elapsed: 00:00:00.20

SQL> drop table emp_t;
Table dropped.

Elapsed: 00:00:00.40

Now we will drop a table using SYS.

SQL> drop table emp;
Table dropped.

Elapsed: 00:00:00.14
SQL>

On querying the AUD$ table we got the following results.

SQL> select obj$creator,sqltext from aud$;
OBJ$CREATOR SQLTEXT
------------------------------ -------------------------------------------------------
SCOTT create table emp_t as select * from emp
SCOTT drop table emp_t

Elapsed: 00:00:00.01
SQL>

As you can see the actions performed by Scott are recorded. However actions from Sys are not audited.

Although in my experiments I have found nothing special when it comes to DDL but you can turn on the supplemental logs to have some additional information. As this is the recommended way for auditing. By default the supplemental logging is turned off and you can turn them on using the following.
SQL> select name,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
NAME SUPPLEME
--------- --------
ORCL NO

Elapsed: 00:00:00.01
SQL> alter database add supplemental log data;
Database altered.

Elapsed: 00:00:00.33
SQL> select name,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
NAME SUPPLEME
--------- --------
ORCL YES

Elapsed: 00:00:00.01
SQL>