How To Enable DDL Auditing in Oracle 11g and 12c(enable_ddl_logging)

dbtut April 23, 2019 ORACLE

Enable DDL Auditing in 11g

It is possible to enable ddl auditing in a database or shema. In this way, we can monitor all DDL changes and we can undo the wrong operation in case of a problem.

Create a table and trigger to audit all DDL operations in the database.

Create a Table for DDL Auditing

CREATE TABLE AUDIT_DDL
(
   D              DATE,
   OSUSER         VARCHAR2 (255),
   SESSION_USER   VARCHAR2 (255),
   HOST           VARCHAR2 (255),
   TERMINAL       VARCHAR2 (255),
   MODULE         VARCHAR2 (255),
   OWNER          VARCHAR2 (30),
   TYPE           VARCHAR2 (30),
   NAME           VARCHAR2 (30),
   SYSEVENT       VARCHAR2 (30),
   SQL_TEXT        VARCHAR2 (4000)
);

CREATE TABLE AUDIT_DDL

(

D DATE,

OSUSER VARCHAR2 (255),

SESSION_USER VARCHAR2 (255),

HOST VARCHAR2 (255),

TERMINAL VARCHAR2 (255),

MODULE VARCHAR2 (255),

OWNER VARCHAR2 (30),

TYPE VARCHAR2 (30),

NAME VARCHAR2 (30),

SYSEVENT VARCHAR2 (30),

SQL_TEXT VARCHAR2 (4000)

);

Create a Trigger For DDL Auditing

The following trigger will enable DDL auditing at the entire database level. If you write “on schema” instead of “on database” in the trigger, it will enable DDL auditing for only the user who created the trigger.

create or replace trigger audit_ddl_trg after ddl on database
declare
  sql_text ora_name_list_t;
  stmt VARCHAR2(4000) := '';
  n number;
begin
    n:=ora_sql_txt(sql_text);
    for i in 1..n
    loop
      stmt:=substr(stmt||sql_text(i),1,4000);
    end loop; 
    insert into audit_ddl(d, osuser,session_user,host,terminal,module,owner,type,name,sysevent,sql_txt)
    values(
      sysdate,
      sys_context('USERENV','OS_USER') ,
      sys_context('USERENV','SESSION_USER') ,
      sys_context('USERENV','HOST') , 
      sys_context('USERENV','TERMINAL') ,
      sys_context('USERENV','MODULE') ,
      ora_dict_obj_owner,
      ora_dict_obj_type,
      ora_dict_obj_name,
      ora_sysevent,
      stmt
    );
end;
/

create or replace trigger audit_ddl_trg after ddl on database

declare

sql_text ora_name_list_t;

stmt VARCHAR2(4000) := '';

n number;

begin

n:=ora_sql_txt(sql_text);

for i in 1..n

loop

stmt:=substr(stmt||sql_text(i),1,4000);

end loop;

insert into audit_ddl(d, osuser,session_user,host,terminal,module,owner,type,name,sysevent,sql_txt)

values(

sysdate,

sys_context('USERENV','OS_USER') ,

sys_context('USERENV','SESSION_USER') ,

sys_context('USERENV','HOST') ,

sys_context('USERENV','TERMINAL') ,

sys_context('USERENV','MODULE') ,

ora_dict_obj_owner,

ora_dict_obj_type,

ora_dict_obj_name,

ora_sysevent,

stmt

);

end;

Enable DDL Auditing in 12c

It is possible to enable DDL auditing for Oracle 11g and earlier versions by performing the above operations.

Oracle introduced a new feature with 12c; now it is possible to enable DDL auditing without needing these operations.

When the ENABLE_DDL_LOGGING parameter is enabled, DDL changes can be logged in the following directory as log and xml file.

$ORACLE_BASE/diag/rdbms/DBNAME/SID/log/ddl

1	$ORACLE_BASE/diag/rdbms/DBNAME/SID/log/ddl

The parameter ENABLE_DDL_LOGGING can be changed as follows.

SQL> show parameter ENABLE_DDL_LOGGING

NAME				     TYPE		VALUE
---------------------------------------------
enable_ddl_logging		 boolean	FALSE


SQL> alter system set enable_ddl_logging=TRUE scope=both sid='*';

System altered.

NAME				     TYPE		VALUE
---------------------------------------------
enable_ddl_logging		 boolean	TRUE

SQL> show parameter ENABLE_DDL_LOGGING

NAME TYPE VALUE

---------------------------------------------

enable_ddl_logging boolean FALSE

SQL> alter system set enable_ddl_logging=TRUE scope=both sid='*';

System altered.

NAME TYPE VALUE

---------------------------------------------

enable_ddl_logging boolean TRUE

After the parameter change, in the first ddl operation, a file such as log.xml is created under the $ORACLE_BASE/diag/rdbms/DBNAME/SID/log/ddl directory and all the DDL operations are written as follows.

<msg time='2018-05-11T13:40:37.219+03:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4695:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='db01.local' host_addr='10.10.1.120'
 pid='326761' version='1'>
 <txt>create table a(ad varchar(100))
 </txt>
</msg>
<msg time='2018-05-11T13:55:40.162+03:00' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4695:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='db.local' host_addr='10.10.1.120'
 pid='393307'>
 <txt>alter table a add soyad varchar(100)
 </txt>
</msg>

<msg time='2018-05-11T13:40:37.219+03:00' org_id='oracle' comp_id='rdbms'

msg_id='opiexe:4695:2946163730' type='UNKNOWN' group='diag_adl'

level='16' host_id='db01.local' host_addr='10.10.1.120'

pid='326761' version='1'>

<txt>create table a(ad varchar(100))

</txt>

</msg>

<msg time='2018-05-11T13:55:40.162+03:00' org_id='oracle' comp_id='rdbms'

msg_id='opiexe:4695:2946163730' type='UNKNOWN' group='diag_adl'

level='16' host_id='db.local' host_addr='10.10.1.120'

pid='393307'>

<txt>alter table a add soyad varchar(100)

</txt>

</msg>

2 comments

datacoffee
September 7, 2020 at 12:29 pm
A small typo exists in this post atm,
in table creation script there is
column name SQL_TEXT but in trigger creation script there is “insert into ..values(sql_txt)
jonny
January 5, 2021 at 8:12 am
And it would be better if you notice also that “ENABLE_DDL_LOGGING” requires licence of “Oracle Database Lifecycle Management Pack”, and it is not so cheap.
Licensed Parameters:
The init.ora parameter ENABLE_DDL_LOGGING is licensed as part of Oracle Database Lifecycle Management Pack for Oracle Database when set to TRUE.
-https://docs.oracle.com/database/121/DBLIC/editions.htm#DBLIC109-

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux