Enable DDL Auditing in Oracle

Enable DDL Auditing in Oracle

Enable DDL logging in Oracle can be done in several ways.

1. Enable the parameter ENABLE_DDL_LOGGING in Oracle 12c
2. Enable the audit of the database and enable DDL auditing on objects level.
3. Make a DDL trigger on database.

Enable the Parameter ENABLE_DDL_LOGGING
This feature introduced in Oracle 12c, Log generated at particular diag location: %ORACLE_BASE%/diag/rdbms/DBNAME/SID/log/ddl.
It can also make an entry of DDL command in alert log file.
Note: ENABLE_DDL_LOGGING is licensed under Oracle Change Management Pack when set to TRUE. By default parameter value if FALSE.

--Enable the DDL logging
alter system set enable_ddl_logging=TRUE scope=both;


--Check the log generated at location:
%ORACLE_BASE%/diag/rdbms/DBNAME/SID/log/ddl

Enable the audit of the database
Enable audit by setting AUDIT_TRAIL parameter.

alter system set AUDIT_TRAIL='db,extended' scope=spfile;
--restart the database

--Enable Auditing for table
audit table;

--Check the log generated with AUD$ table.
select sqltext from aud$;

Make a DDL trigger for the Database
For making the DDL trigger you need to use the user environment and pick user value and need to create a table to save these values.
Please use the link for system event reference used in trigger: https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg14evt.htm

-- Create table for auditing the DDL statements
CREATE TABLE DDL_AUDIT
(
OBJECT_OWNER VARCHAR2 (30),
OSUSER VARCHAR2 (255),
SESSION_USER VARCHAR2 (255),
HOST VARCHAR2 (255),
TERMINAL VARCHAR2 (255),
MODULE VARCHAR2 (255),
TYPE VARCHAR2 (30),
Executed_date DATE,
OBJECT_NAME VARCHAR2 (30),
SQL_STATEMENT VARCHAR2 (2000)
);

-- Create trigger for DDL operation on database or on schema as you need.
create or replace trigger audit_ddl_trg after ddl on database
declare
v_sql_text ora_name_list_t;
v_stmt VARCHAR2(2000) := '';
v_n number;
begin
v_n:=ora_sql_txt(v_sql_text);
for i in 1..v_n
loop
v_stmt:=substr(v_stmt||v_sql_text(i),1,2000);
end loop;

insert into DDL_AUDIT(object_owner,osuser,session_user,host,terminal,module,type,executed_date,Object_name,sql_statement)
values(
ora_dict_obj_owner,
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_type,
sysdate,
ora_dict_obj_name,
v_stmt
);
end;
/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.