Blog Johannes

Blog Johannes

johannes blog ohne logo

Blog Johannes

Verwaltung von Auditing Informationen

Veröffentlicht: 07. Januar 2015
Geschrieben von Johannes Ahrends

In den letzten Jahren werde ich wiederholt um Hilfe gebeten, weil bei einer Oracle Datenbank der SYSTEM Tablespace immer weiter anwächst. Der Grund hierfür ist relativ simpel: Seit Oracle 11.2 werden standardmäßig DDL-Befehle, die nicht mit der SYSDBA Berechtigung ausgeführt werden, sowie jeder Login-Versuch überwacht, d.h. sowohl das erfolgreiche Ausführen als auch fehlerhafte Befehle werden in der AUD$ Tabelle protokolliert. Und entgegen der Aussage in einigen Dokumenten liegt die AUD$ Tabelle, genau so wie die FGA_LOG$ (für Fine Grain Auditing) im SYSTEM Tablespace und nicht im Tablespace SYSAUX.

SYSTEM Tablespace Size

Über den Oracle Parameter AUDIT_TRAIL=NONE kann man das Auditing für diese Aktionen ausschalten, aber zum einen ist dies kein dynamischer Parameter, sondern die Instanzen müssen durchgestartet werden und zum anderen ist es ja auch ganz sinnvoll, diese Befehle zu protokollieren. Zu wissen, welcher Benutzer welches DDL-Kommando abgesetzt hat bzw. vergeblich versucht hat, sich anzumelden, ist ja nicht verkehrt.

Audit Tablespace

Da die Tabelle AUD$ je nach Anwendungsart sehr schnell wachsen kann, sollte sie also zunächst einmal in einen eigenen Tablespace verschoben werden. Da man Tabellen des Data-Dictionaries nicht einfach so verschieben darf, liefert Oracle für die Verwaltung der Audit-Tabellen ein eigenes Package mit (dbms_audit_mgmt).

CREATE TABLESPACE AUDITTS
   DATAFILE '/u02/oradata/HANNES/auditts01.dbf' size 100M
   AUTOEXTEND ON NEXT 100M MAXSIZE 5000M;
--
-- Initialisierung des Audit Managements
--
BEGIN
  dbms_audit_mgmt.init_cleanup(
    AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std,
    DEFAULT_CLEANUP_INTERVAL => 1 /*Stunden*/
  );
END;
/

VORSICHT! Diese Initialisierung des Managements führt dazu, dass die AUD$ Tabelle vom SYSTEM in den SYSAUX Tablespace verschoben wird. Wenn also die AUD$ Tabelle aufgrund ihrer Größe bereits ein Problem darstellt, wird das nicht eben kleiner. Sie sollten also manuell Datensätze aus der Tabelle löschen oder gar ein TRUNCATE ausführen, bevor Sie diese Prozedur aufrufen. Als "Best Practice" würde ich Ihnen empfehlen, dieses Package sofort nach der Erstellung der Datenbank auszuführen.

Als nächstes kann jetzt die AUD$ in ihren entgültigen Tablespace AUDITTS verschoben werden. Dafür wird wieder das Package dbms_audit_mgmt verwendet.

BEGIN
   dbms_audit_mgmt.set_audit_trail_location(
    audit_trail_type           => dbms_audit_mgmt.audit_trail_aud_std,
    audit_trail_location_value => 'AUDITTS');
END;
/

Damit wird die AUD$ Tabelle in den entgültigen Tablespace verschoben und unser Problem ist, vorerst, behoben. Allerdings würde ich empfehlen, dass Sie sich auch gleich Gedanken darüber machen, wie die Audit-Daten verwaltet werden sollen. Auch dafür gibt es über das Package dbms_audit_mgmt eine Lösung, allerdings ist die nicht ganz so offensichtlich (zumindest meiner Ansicht nach).

BEGIN
   dbms_audit_mgmt.create_purge_job(
    AUDIT_TRAIL_TYPE           => dbms_audit_mgmt.audit_trail_aud_std,
    AUDIT_TRAIL_PURGE_INTERVAL => 24 /* Stunden */,
    AUDIT_TRAIL_PURGE_NAME     => 'Audit_Purge_Job',
    USE_LAST_ARCH_TIMESTAMP    => TRUE
  );
END;
/

Damit wird ein Scheduler Job mit dem Namen "Audit Purge Job" erstellt, der einmal täglich ausgeführt wird und dann die Audit-Einträge löscht, die archiviert worden sind (USE_LAST_ARCH_TIMESTAMP); Wenn dieser Parameter auf "FALSE" steht, werden einfach alle Audit-Einträge gelöscht.

Leider funktioniert das aber so nicht. Bei der anschließenden Ausführung über den Scheduler bekommt man die Fehlermeldung:

ORA-12012: Fehler beim autom Ausführen von Job "SYS"."AUDIT_PURGE_JOB"
ORA-46258: Cleanup für den Audit-Trail nicht initialisiert

D.h. auch bei der Ausführung dieses Befehls durch den Scheduler muss zunächst eine Initialisierung erfolgen. Also muss entweder der Oracle Scheduler direkt benutzt (dbms_scheduler) oder anschließend die PL/SQL Prozedur editiert werden. Über den Toad sieht das dann so aus:

Toad Purge job

Oracle geht davon aus, dass Audit-Daten nur für eine kurze Zeit in der Datenbank gespeichert werden und die Langzeitspeicherung in einer separaten Datenbank erfolgt. Die dann nicht mehr benötigten Daten werden entsprechend markiert und beim nächsten "purge" gelöscht. Für ein "einfaches" Auditing brauchen wir also noch einen zweiten Job, der die Audit Einträge, z.B. nach 30 Tagen, als archiviert markiert.

BEGIN
  dbms_scheduler.create_job (
    job_name   => 'Audit_Archive_Timestamp',
    job_type   => 'PLSQL_BLOCK',
    job_action => 'BEGIN 
                         dbms_audit_mgmt.init_cleanup(
                            AUDIT_TRAIL_TYPE         => dbms_audit_mgmt.audit_trail_aud_std,
                            DEFAULT_CLEANUP_INTERVAL => 1);
                         dbms_audit_mgmt.set_last_archive_timestamp(
                            AUDIT_TRAIL_TYPE         => dbms_audit_mgmt.audit_trail_aud_std,
                            LAST_ARCHIVE_TIME        => sysdate-31); 
                         dbms_audit_mgmt.deinit_cleanup(
                            AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std);   
                  END;', 
    start_date => to_date('2015-01-01 00:30:00','YYYY-MM-DD HH24:MI:SS'), 
    repeat_interval => 'FREQ=HOURLY;INTERVAL=24', 
    enabled    =>  TRUE,
    comments   => 'Create Archive timestamp'
  );
END;
/

Dieser Job ruft die Prozedur set_last_archive_timestamp auf und setzt damit den Timestamp für alle Einträge, die älter als 31 Tage sind. Auch hier gilt wieder, dass die Initialisierung mit angegeben werden muss.

Damit ist das Management der Audit Daten abgeschlossen und wir sollten bzw. können die Initialisierung wieder zurücknehmen.

BEGIN
   dbms_audit_mgmt.deinit_cleanup(
      AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_aud_std
   );
END;
/

Überprüfung

Folgende Views eignen sich gut dazu, zu überprüfen, ob Auditing eingeschaltet ist und ob unsere Prozeduren richtig ausgeführt wurden:


SELECT audit_option, success, failure FROM dba_stmt_audit_opts; AUDIT_OPTION SUCCESS FAILURE ---------------------------------------- ---------- --------- CREATE EXTERNAL JOB BY ACCESS BY ACCESS CREATE ANY JOB BY ACCESS BY ACCESS GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS EXEMPT ACCESS POLICY BY ACCESS BY ACCESS CREATE ANY LIBRARY BY ACCESS BY ACCESS GRANT ANY PRIVILEGE BY ACCESS BY ACCESS DROP PROFILE BY ACCESS BY ACCESS ALTER PROFILE BY ACCESS BY ACCESS DROP ANY PROCEDURE BY ACCESS BY ACCESS ALTER ANY PROCEDURE BY ACCESS BY ACCESS CREATE ANY PROCEDURE BY ACCESS BY ACCESS ALTER DATABASE BY ACCESS BY ACCESS SYSTEM GRANT BY ACCESS BY ACCESS GRANT ANY ROLE BY ACCESS BY ACCESS CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS DROP ANY TABLE BY ACCESS BY ACCESS ALTER ANY TABLE BY ACCESS BY ACCESS CREATE ANY TABLE BY ACCESS BY ACCESS PROFILE BY ACCESS BY ACCESS ROLE BY ACCESS BY ACCESS DATABASE LINK BY ACCESS BY ACCESS PUBLIC SYNONYM BY ACCESS BY ACCESS DROP USER BY ACCESS BY ACCESS ALTER USER BY ACCESS BY ACCESS CREATE USER BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS SYSTEM AUDIT BY ACCESS BY ACCESS ALTER SYSTEM BY ACCESS BY ACCESS col JOB_NAME format a30 col JOB_FREQUENCY format a40 SELECT job_name,job_status,audit_trail,job_frequency FROM dba_audit_mgmt_cleanup_jobs; JOB_NAME JOB_STAT AUDIT_TRAIL JOB_FREQUENCY ------------------------------ -------- ---------------------------- ----------------------- DAILY_AUDIT_PURGE_JOB ENABLED STANDARD AUDIT TRAIL FREQ=HOURLY;INTERVAL=24 SELECT job_name, next_run_date, state, enabled FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%'; JOB_NAME NEXT_RUN_DATE STATE ENABL ------------------------------ ----------------------------------- --------------- ----- AUDIT_ARCHIVE_TIMESTAMP 08.01.15 00:30:00,000000 +01:00 SCHEDULED TRUE DAILY_AUDIT_PURGE_JOB 08.01.15 09:16:30,800000 +01:00 SCHEDULED TRUE

Natürlich möchte man auch irgendwann mal die Auditinformationen auswerten. Hier ein Beispiel für den Logon an die Datenbank. Wenn ein Returncode zurückgegeben wird, bedeutet dies, dass der Logon nicht erfolgreich war:


SELECT os_username, username, userhost, extended_timestamp, action, action_name, returncode as return
FROM dba_audit_trail
WHERE action_name = 'LOGON'
ORDER BY TIMESTAMP desc;

OS_USERNAME       USERNAME USERHOST               EXTENDED_TIMESTAMP               ACTION ACTION_NAM  RETURN
----------------- -------- ---------------------- -------------------------------- ------ ---------- -------
Johannes Ahrends  SYSTEM   CARAJANDB\CDB-JA       08.01.15 12:11:58,150458 +01:00     100 LOGON            0
oracle            SYSTEM   mozart.carajandb.intra 08.01.15 12:11:37,148850 +01:00     100 LOGON            0
oracle            SYSTEM   mozart.carajandb.intra 08.01.15 12:11:32,535973 +01:00     100 LOGON         1017
oracle            SYSTEM   mozart.carajandb.intra 08.01.15 12:10:16,180199 +01:00     100 LOGON         1017
oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:33,723828 +01:00     100 LOGON            0
oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:31,224429 +01:00     100 LOGON        28000
oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:31,736211 +01:00     100 LOGON            0
oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:31,430337 +01:00     100 LOGON            0
oracle            DBSNMP   mozart.carajandb.intra 08.01.15 11:18:31,497521 +01:00     100 LOGON            0
oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:31,588672 +01:00     100 LOGON            0
oracle            SYSMAN   mozart.carajandb.intra 08.01.15 11:18:31,712832 +01:00     100 LOGON            0
oracle            DBSNMP   mozart.carajandb.intra 08.01.15 11:18:10,574229 +01:00     100 LOGON            0
oracle            DBSNMP   mozart.carajandb.intra 08.01.15 11:18:05,167148 +01:00     100 LOGON        28000
oracle            SYSTEM   mozart.carajandb.intra 08.01.15 11:03:49,565045 +01:00     100 LOGON            0
oracle            SYSTEM   mozart.carajandb.intra 08.01.15 11:03:49,491996 +01:00     100 LOGON            0

Zusammenfassung

Spätestens, wenn Ihr SYSTEM Tablespace größer als 2 GB geworden ist, sollten Sie sich die AUD$ oder auch die FGA_LOG$ Tabelle ansehen und in einen eigenen Tablespace verschieben (bei der FGA_LOG$ muss der AUDIT_TRAIL_TYPE = dbms_audit_mgmt.audit_trail_fga_std sein). Problematisch ist, dass Sie es wahrscheinlich nicht schaffen werden, den SYSTEM (oder auch SYSAUX) Tablespace wieder zu verkleinern, weil sicherlich andere Objekte Extents "hinter" die AUD$ Tabelle angelegt haben.

Derzeit beschäftige ich mich gerade mit dem Unified Auditing von Oracle 12c und werde sicherlich bald auch darüber berichten.

Johannes Ahrends

CarajanDB GmbH

Siemensstr. 25  50374 Erftstadt

Fon: +49 (2235) 170 91 83

Fax: +49 (2235) 170 79 78

Mail: info@carajandb.com

 

carajan-db-logo