Verwaltung von Auditing Informationen

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.

Ü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,     
	 udit_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 /* hours*/,     
	  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:

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.