Enable DDL logging in Oracle databaase (安全审计)

Oracle database use DDL statements to define structures such as tables to store data and functions to store code. Monitoring, auditing and logging changes to DDL are key components of a database security program. By default Oracle database does not log any DDL operations performed by any user.When someone do some destructive DDL in DB, we often need the DDL log.

The options for DDL auditing include:
• Standard auditing
• System Triggers
• Oracle Database Lifecycle Management Pack
• Third party tools
• logminer

In this article I will  only record the method of using Oracle Database Lifecycle Management Pack( Enable_ddl_logging),The init.ora parameter ENABLE_DDL_LOGGING is licensed as part of the Database Lifecycle Management Pack when set to TRUE.

ENABLE_DDL_LOGGING

In Oracle 11G oracle has introduced new parameter ENABLE_DDL_LOGGING. This parameter enables or disables the writing of a subset of data definition language (DDL) statements into a log.

Oracle 11g
DDL statements are written to the alert log in: $ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/trace/alert_${ORACLE_SID}.log

Oracle 12c R1
12.1 DDLs were written into alter.log  and  written to dedicated log stored in new directory directory.There are two DDL logs that contain the same information. One is an XML file, and the other is a text file. The DDL log is stored in the log/ddl subdirectory of the ADR home.

Oracle 12c R2 and newer
12.2 DDL statement not written alert log, written to dedicated log only. Now if you look in the following text file:
$ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/log/ddl_${ORACLE_SID}.log

There is also a XML version:
$ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/log/ddl/log.xml

Enable DDL logging

sing 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.

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.

Demo

version oracle 12.2 on OEL6
JAVA CODE

[oracle@anbob ~]$ more DDL.java
import java.sql.*;
public class DDL{

public static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@anbob.com:1521/pdbanbob.com";
    String username = "anbob";
    String password = "anbob";

    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }

  public static void  droptable(Connection conn, String tableName) throws SQLException {
    // select the number of rows in the table
    Statement stmt = null;
    ResultSet rs = null;
    try {
      stmt = conn.createStatement();
      String sql="drop table "+ tableName;
      System.out.println("SQL Text: "+sql);
      stmt.executeUpdate(sql);
    } finally {
      stmt.close();
    }
  }

 public static void main(String[] args) {
    Connection conn = null;
    try {
      conn = getConnection();
      String tableName = "test100";
      System.out.println("tableName=" + tableName);
      System.out.println("conn=" + conn);
          // to do
      droptable(conn, tableName);

    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    } finally {
      // release database resources
      try {
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

create table with sqlplus and drop table with java jdbc.

DDL LOG

[oracle@anbob log]$ pwd
/u02/app/oracle/diag/rdbms/anbob/anbob/log

[oracle@anbob log]$ ls
ddl ddl_anbob.log debug debug.log hcs imdb test
[oracle@anbob log]$ more ddl_anbob.log
2020-05-22T21:51:55.794795+08:00
diag_adl:create table test1(id int)
2020-05-22T21:52:02.180012+08:00
diag_adl:drop table test1
2020-05-22T21:56:33.734658+08:00
diag_adl:create table anbob.t100(id int)
2020-05-22T21:59:16.404601+08:00
diag_adl:create table test100(id int)
2020-05-22T21:59:55.817096+08:00
diag_adl:drop table test100
[oracle@anbob log]$ ls ddl
log.xml
[oracle@anbob log]$ more ddl/log.xml
<msg time='2020-05-22T21:51:55.793+08:00' org_id='oracle' comp_id='rdbms'
msg_id='kpdbLogDDL:21798:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='anbob' host_addr='192.168.56.101'
pid='4590' version='1' con_uid='3629755513'
con_id='3' con_name='PDBANBOB'>
<txt>create table test1(id int)
</txt>
</msg>
<msg time='2020-05-22T21:52:02.179+08:00' org_id='oracle' comp_id='rdbms'
msg_id='kpdbLogDDL:21798:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='anbob' host_addr='192.168.56.101'
pid='4590' con_uid='3629755513' con_id='3'
con_name='PDBANBOB'>
<txt>drop table test1
</txt>
</msg>
...

Or using ADRCI

adrci> show log -l ddl

ADR Home = /u02/app/oracle/diag/rdbms/anbob/anbob:
*************************************************************************
Output the results to file: /tmp/utsout_3020_140224_1.ado

2020-05-22 21:51:55.793000 +08:00
create table test1(id int)
2020-05-22 21:52:02.179000 +08:00
drop table test1
2020-05-22 21:56:33.734000 +08:00
create table anbob.t100(id int)
2020-05-22 21:59:16.404000 +08:00
create table test100(id int)
2020-05-22 21:59:55.815000 +08:00
drop table test100

— OVER–

转自我方ACE:https://www.anbob.com/archives/5787.html