Home > Oracle Datenbank Monitoring > DDL Logging

DDL Logging

Teil 3: DDL Logging für alle Editions

Oracle hat den Parameter ENABLE_DDL_LOGGING mit Oracle 11g eingeführt. Wenn man diesen auf TRUE setzt, schreibt Oracle <DIAG_DEST>/rdbms/<DBName>/<Instance_name>/log/ddl/log.xml (XML) bzw in <DIAG_DEST>/rdbms/<DBName>/<Instance_name>/log/ddl_>Instance_name<.log (Text) mit, welches DDL Statement abgesetzt wurde. Allerdings darf man den Parameter nur dann auf TRUE setzen, wenn man eine Oracle Enterprise Edition Datenbank UND das Database Lifecycle Management Pack lizensiert hat - somit VORSICHT: Lizenzfalle!

Wie würde jetzt so ein Eintrag überhaupt aussehen - Beispiel eines ddl*.txt Eintrags:

2018-05-25T04:25:20.435508-04:00
diag_adl:create table test_enable_ddl_logging (x number)

Was kann man jetzt machen, wenn man gerne ein DDL Logging hätte und einen oder mehrere der folgenden Punkte erfüllt?

 

  • Man hat keine Oracle Enterprise Edition Datenbank inklusive Lifecycle Management Pack.
  • Die Informationen sollen INNERHALB der Datenbank Instanz zur Verfügung stehen.
  • Weitere Informationen - neben dem Statement - wie ausführender Benutzer, betroffenes Objekt,... wären hilfreich
  • Es soll für alle Oracle Datenbank Editionen einheitlich, gleich funktionieren.
  • Man hat vielleicht sogar noch ein Oracle 10g im Einsatz ... (sollte man wirklich langsam upgraden!)

In diesen Fällen muss man etwas Hand anlegen - es ist aber ganz einfach, versprochen!

DDL Logging mit Hilfe von Datenbank Event Triggern

Datenbank Event Trigger gibt es schon seit Oracle 10g und sind in allen Oracle Editions möglich. Im folgenden zeigen wir zwei Beispiel - eines für NON-CDB Datenbanken und ein zweites für CDB Datenbanken.

NON-CDB Datenbanken (oder die normalen Datenbanken ohne Pluggable Database Funktionalität).

Das folgende Beispiel funktioniert in grundsätzlich für alle Oracle Datenbanken (auch CDB). Sofern Sie einheitlichen Code haben möchten, können Sie immer diese Variante einsetzen. Zu den Eigenheiten für CDB Datenbanken finden Sie mehr im nächsten Abschnitt.

Die Scripts gehen davon aus, dass man AS SYSDBA angemeldet ist. Der TRIGGER muß als SYS angelegt sein.

sqlplus / as sysdba

Cleanup - muß nur dann erfolgen, wenn man schon ältere Versionen im Einsatz hatte

drop trigger ddl_logging_trigger;
drop sequence system.ddl_logging_seq;
drop table system.ddl_logging_table purge;

Zuerst wird eine Tabelle angelegt, in der das DDL_Logging erfolgt.

create table system.ddl_logging_table
       (EventID         number
       ,Zeitpunkt       timestamp
       ,Login_User      VARCHAR2(30)
       ,Object_owner    VARCHAR2(128)
       ,Object_name     VARCHAR2(128)
       ,Object_type     VARCHAR2(30)
       ,Trigger_Event   VARCHAR2(30)
       ,machine         VARCHAR2(64)
       ,program         VARCHAR2(64)
       ,osuser          VARCHAR2(30)
       ,sql_id          VARCHAR2(13)
       ,Befehl          varchar2(4000))
       tablespace sysaux;

Dann noch eine Sequence - wir wollen ja wissen in welcher Reihenfolge die Statements abgesetzt wurden - speziell wenn öfter Scripts laufen.

create sequence system.ddl_logging_seq;

Last but not least, der Trigger (muss als SYS angelegt sein!)

create or replace trigger ddl_logging_trigger
  AFTER DDL ON DATABASE
  BEGIN
    insert into system.ddl_logging_table
           (EventID
           ,Zeitpunkt
           ,Login_User
           ,Object_owner
           ,Object_name
           ,Object_type
           ,Trigger_Event
           ,machine
           ,program
           ,osuser
           ,sql_id
           ,Befehl)
    select system.ddl_logging_seq.nextval,
           SYSTIMESTAMP,
           ORA_LOGIN_USER,
           ORA_DICT_OBJ_OWNER,
           ORA_DICT_OBJ_NAME,
           ORA_DICT_OBJ_TYPE,
           ORA_SYSEVENT,
           machine,
           program,
           osuser,
           sa.sql_id,
           substr(regexp_replace(sa.sql_fulltext, '( [[:space:]]+)|([[:cntrl:]])',' '),1,4000)
      from sys.V_$sqlarea sa full outer join sys.V_$SESSION s ON (s.sql_id = sa.sql_id)
     where s.audsid = userenv('SESSIONID');
   End;
/

Erläuterungen zu den Spalten und den Inhalten

  • Event Id      Laufende Nummer aus der Sequenz - die Reihenfolge in der die Statements abgesetzt wurden.
  • Zeitpunkt     Timestamp, wenn wurde das DDL Statement abgesetzt.
  • Login_User    Unter welchem Benutzer (angemeldet) wurde der Befehl ausgeführt.
  • Object_Owner  Wenn es ein Datenbank Objekt betroffen hat, wer ist/war der Eigentümer des Objekts.
  • Object_Name   Wenn ein Datenbank Objekt betroffen war, der Name des Objekts
  • Object_type   Wenn ein Datenbank Objekt betroffen war, der Objekt Type
  • Trigger_Event Was war der auslösende Grund - meist der Statement Type
  • Maschine      Von woher war der Benutzer angemeldet (von welcher Client Maschine aus)
  • Program       Mit welchem Programm war der Client angemeldet
  • OSUSer        Was war der Betriebssystem Benutzer vom Client
  • SQL_ID        Die SQL_ID des Statements - damit kann man es in V$SQLAREA auffinden, sofern es noch nicht aus der SQL Area herausgefallen ist.
  • Befehl        Die ersten 4000 Zeichen des SQL Statement ohne unnötige LEERZEICHEN oder WHITESPACES (Tabs, Zeilenumbrüche).

Wenn Sie weitere Informationen aus V$SESSION protokolliert haben möchten, müssen Sie nur die Tabelle DDL_LOGGING und den Trigger DDL_LOGGING_TRIGGER entsprechend erweitern.

DDL Logging - Erweiterung für CDB Datenbanken

Da Pluggable Datenbanken voneinander abgeschottet sind, ist es leider nicht möglich eine zentrale Tabelle für das DDL Logging anzulegen - sprich man muss sowohl die Tabelle, die Sequence und den Trigger in allen Pluggable Databases (inkl. ROOT Container) anlegen. Der Unterschied zum vorigen Beispiel ist lediglich, dass die CON_ID mit befüllt wird.

create table system.ddl_logging_table
       (EventID         number
       ,Zeitpunkt       timestamp
       ,Login_User      VARCHAR2(30)
       ,Object_owner    VARCHAR2(128)
       ,Object_name     VARCHAR2(128)
       ,Object_type     VARCHAR2(30)
       ,Trigger_Event   VARCHAR2(30)
       ,machine         VARCHAR2(64)
       ,program         VARCHAR2(64)
       ,osuser          VARCHAR2(30)
       ,con_id          number
       ,sql_id          VARCHAR2(13)
       ,Befehl          varchar2(4000))
       tablespace sysaux;

create sequence system.ddl_logging_seq;

create or replace trigger ddl_logging_trigger
  AFTER DDL ON DATABASE
  BEGIN
    insert into system.ddl_logging_table
           (EventID
           ,Zeitpunkt
           ,Login_User
           ,Object_owner
           ,Object_name
           ,Object_type
           ,Trigger_Event
           ,machine
           ,program
           ,osuser
           ,con_id
           ,sql_id
           ,Befehl)
    select system.ddl_logging_seq.nextval,
           SYSTIMESTAMP,
           ORA_LOGIN_USER,
           ORA_DICT_OBJ_OWNER,
           ORA_DICT_OBJ_NAME,
           ORA_DICT_OBJ_TYPE,
           ORA_SYSEVENT,
           machine,
           program,
           osuser,
           sa.con_id,
           sa.sql_id,
           substr(regexp_replace(sa.sql_fulltext, '( [[:space:]]+)|([[:cntrl:]])',' '),1,4000)
      from sys.V_$sqlarea sa full outer join sys.V_$SESSION s ON (s.sql_id = sa.sql_id)
     where s.audsid = userenv('SESSIONID');
   End;
/

DDL Logging mittels UNIFIED AUDITING

Oracle Datenbanken ab Version 12c unterstützen mit Unified Auditing eine granularere Audit Variante mit der man ebenfalls ein DDL Logging durchführen kann. Der Vorteil dabei ist, dass diese dann Einheitlich mit anderen Audit Events zum Datenbank Security monitoring genutzt werden kann.

Unified Auditing konfigurieren

Um Unified Auditing nutzen zu können, muss man dies erst Einschalten und für ein regelmäßiges Cleanup sorgen (sonst explodiert der SYSTEM bzw. SYSAUX Tablespace auf Grund der vielen Audit Informationen).

Die Beschreibung, wie man Unified Auditing konfiguriert, finden Sie im Artikel Unified Audit konfigurieren.

Zuerst muss man sich überlegen, was man alles auditieren möchte - sagen wir einmal, alles was möglich ist! Damit man eine vollständige Liste bekommt, holt man diese direkt aus der Datenbank:

select name
  from AUDITABLE_SYSTEM_ACTIONS
where COMPONENT = 'Standard'
   /* Wir wollen kein DML und kein Login/Logout oder TRANSAKTIONEN */
   and name not in ('SELECT', 'INSERT', 'UPDATE', 'DELETE', 'MERGE', 'EXECUTE', 'CALL METHOD', 'ALL' , 'LOGON','LOGOFF','COMMIT','ROLLBACK')
   /* Und auch nicht die "Do not use" */
   and name not like 'Do not use%'
   /* und die, die nicht funktionieren -- ORA 956 */
   and name not in ('VALIDATE INDEX','SAVEPOINT','EXPLAIN','ALTER TRACING','SET CONSTRAINTS','CREATE SUMMARY','ALTER SUMMARY','DROP SUMMARY','UPDATE INDEXES','UPDATE JOIN INDEX','PURGE USER RECYCLEBIN','PURGE DBA RECYCLEBIN','UNDROP OBJECT');

Mit dieser Liste kann man nun eine Audit Policy anlegen. Dazu meldet man sich als DBA oder AS SYSDBA an.

create audit policy DDL_LOGGING
       ACTIONS
       CREATE TABLE,
       CREATE CLUSTER,
       ALTER CLUSTER,
       DROP CLUSTER,
       CREATE INDEX,
       DROP INDEX,
       ALTER INDEX,
       DROP TABLE,
       CREATE SEQUENCE,
       ALTER SEQUENCE,
       ALTER TABLE,
       DROP SEQUENCE,
       CREATE SYNONYM,
       DROP SYNONYM,
       CREATE VIEW,
       DROP VIEW,
       CREATE PROCEDURE,
       ALTER PROCEDURE,
       LOCK TABLE,
       RENAME,
       COMMENT,
       CREATE DATABASE LINK,
       DROP DATABASE LINK,
       ALTER DATABASE,
       CREATE ROLLBACK SEGMENT,
       ALTER ROLLBACK SEGMENT,
       DROP ROLLBACK SEGMENT,
       CREATE TABLESPACE,
       ALTER TABLESPACE,
       DROP TABLESPACE,
       ALTER SESSION,
       ALTER USER,
       SET TRANSACTION,
       ALTER SYSTEM,
       CREATE USER,
       CREATE ROLE,
       DROP USER,
       DROP ROLE,
       SET ROLE,
       CREATE SCHEMA,
       CREATE TRIGGER,
       ALTER TRIGGER,
       DROP TRIGGER,
       ANALYZE TABLE,
       ANALYZE INDEX,
       ANALYZE CLUSTER,
       CREATE PROFILE,
       DROP PROFILE,
       ALTER PROFILE,
       DROP PROCEDURE,
       ALTER RESOURCE COST,
       CREATE MATERIALIZED VIEW LOG,
       ALTER MATERIALIZED VIEW LOG,
       DROP MATERIALIZED VIEW LOG,
       CREATE MATERIALIZED VIEW ,
       ALTER MATERIALIZED VIEW ,
       DROP MATERIALIZED VIEW ,
       CREATE TYPE,
       DROP TYPE,
       ALTER ROLE,
       ALTER TYPE,
       CREATE TYPE BODY,
       ALTER TYPE BODY,
       DROP TYPE BODY,
       DROP LIBRARY,
       TRUNCATE TABLE,
       TRUNCATE CLUSTER,
       ALTER VIEW,
       CREATE FUNCTION,
       ALTER FUNCTION,
       DROP FUNCTION,
       CREATE PACKAGE,
       ALTER PACKAGE,
       DROP PACKAGE,
       CREATE PACKAGE BODY,
       ALTER PACKAGE BODY,
       DROP PACKAGE BODY,
       ALTER MINING MODEL,
       CREATE MINING MODEL,
       CREATE DIRECTORY,
       DROP DIRECTORY,
       CREATE LIBRARY,
       CREATE JAVA,
       ALTER JAVA,
       DROP JAVA,
       CREATE OPERATOR,
       CREATE INDEXTYPE,
       DROP INDEXTYPE,
       ALTER INDEXTYPE,
       DROP OPERATOR,
       ASSOCIATE STATISTICS,
       DISASSOCIATE STATISTICS,
       CREATE DIMENSION,
       ALTER DIMENSION,
       DROP DIMENSION,
       CREATE CONTEXT,
       DROP CONTEXT,
       ALTER OUTLINE,
       CREATE OUTLINE,
       DROP OUTLINE,
       ALTER OPERATOR,
       CREATE SPFILE,
       CREATE PFILE,
       CHANGE PASSWORD,
       ALTER SYNONYM,
       ALTER DISK GROUP,
       CREATE DISK GROUP,
       DROP DISK GROUP,
       ALTER LIBRARY,
       PURGE TABLESPACE,
       PURGE TABLE,
       PURGE INDEX,
       FLASHBACK TABLE,
       CREATE RESTORE POINT,
       DROP RESTORE POINT,
       CREATE EDITION,
       DROP EDITION,
       DROP ASSEMBLY,
       CREATE ASSEMBLY,
       ALTER ASSEMBLY,
       CREATE FLASHBACK ARCHIVE,
       ALTER FLASHBACK ARCHIVE,
       DROP FLASHBACK ARCHIVE,
       CREATE SCHEMA SYNONYM,
       DROP SCHEMA SYNONYM,
       ALTER DATABASE LINK,
       CREATE PLUGGABLE DATABASE,
       ALTER PLUGGABLE DATABASE,
       DROP PLUGGABLE DATABASE,
       CREATE AUDIT POLICY,
       ALTER AUDIT POLICY,
       DROP AUDIT POLICY,
       CREATE LOCKDOWN PROFILE,
       DROP LOCKDOWN PROFILE,
       ALTER LOCKDOWN PROFILE,
       ADMINISTER KEY MANAGEMENT,
       CREATE MATERIALIZED ZONEMAP,
       ALTER MATERIALIZED ZONEMAP,
       DROP MATERIALIZED ZONEMAP,
       DROP MINING MODEL,
       CREATE ATTRIBUTE DIMENSION,
       ALTER ATTRIBUTE DIMENSION,
       DROP ATTRIBUTE DIMENSION,
       CREATE HIERARCHY,
       ALTER HIERARCHY,
       DROP HIERARCHY,
       CREATE ANALYTIC VIEW,
       ALTER ANALYTIC VIEW,
       DROP ANALYTIC VIEW,
       GRANT,
       REVOKE,
       AUDIT,
       NOAUDIT;

Jetzt nur noch auditieren und schon hat man gewonnen!

       audit policy DDL_LOGGING;

Audit Information auslesen

Die Unified Audit Information wird zuerst in eine Memory Struktur geschrieben und danach erst in die Datenbank persistiert. Will man ganz sicher sein, dass man alle Informationen sieht, muss man diesen Memory Bereich zuerst persistieren. Dann steht der Abfrage nichts mehr im Weg!

EXEC SYS.DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; select UNIFIED_AUDIT_POLICIES, SESSIONID, DBUSERNAME, STATEMENT_ID, EVENT_TIMESTAMP, OBJECT_SCHEMA, OBJECT_NAME, DBID, SQL_TEXT
from unified_audit_trail
where UNIFIED_AUDIT_POLICIES like '%DDL_LOGGING%';

UNIFIED_AUDIT_POLICIES SESSIONID  DBUSERNAME STATEMENT_ID EVENT_TIMESTAMP
       ---------------------- ---------- ---------- ------------ ----------------------------
OBJECT_SCHEMA OBJECT_NAME DBID       SQL_TEXT
------------- ----------- ---------- ---------------------------
DDL_LOGGING            1125260843 SYS                  17 25-MAY-18 04.06.31.354692 AM
SYS           FOO         3904456092 create table foo (x number)

Löschen der DDL_Logging Audit Policy

Wenn man die Policy wieder abschalten möchte, sind folgende Schritte nötig.

noaudit policy DDL_LOGGING;
drop audit Policy DDL_LOGGING;

Hinweis

Wenn man Datenbank Auditing nutzt, muss man sich um das Management des/der Audit Trails kümmern. Weitereführende Informationen zu diesem Thema finden Sie in unseren Artikeln zu Unified Audit auf unserer Homepage.