Auditing

Oracle Auditing

Überblick

Oracle bietet für das Auditing verschiedene technische Ansätze. Je nach Datenbankversion und gewünschtem Einsatzgebiet, ist naturgemäß eine Möglichkeit der anderen vorzuziehen. Um die Entscheidung zu erleichtern, hier zunächst ein grober Überblick über die unterstützten Methoden, sowie ihre Vor- und Nachteile:

Methode	Auditing	Anmerkung
Oracle Audit	Audit von Objekten und Privilegien	Zum Aktivieren muss die Datenbank neu gestartet werden
System triggers	Audit von Objekten und Systemereignissen	Muss selbst programmiert werden
Fine Grained Auditing (FGA)	DML/SELECT Auditing	Erst ab 9i verfügbar. DML erst ab 10g
System Logs	Wichtige Systemereignisse werden automatisch protokolliert	Kein Auditing im eigentlichen Sinn

Auditing Methoden

Nachdem zunächst ein grober Überblick gegeben ist, wollen wir uns die Methoden ein wenig genauer anschauen:

Oracle Audit

Der große Nachteil dieser Methode ist, dass die Einrichtung einen Neustart der Datenbank erfordert – was in produktiven Umgebungen nicht immer eine Option ist (ist diese Option nicht gegeben, muss auf eine der anderen Methoden ausgewichen werden). Dieser Neustart muss erfolgen, nachdem man in der init.ora den Parameter audit_trail gesetzt hat. Mögliche Werte für diesen Parameter sind abhängig von der eingesetzten Oracle Version; wir wollen hier nur audit_trail = db betrachten, und für weitere Werte auf die Oracle Dokumentation verweisen.

Nach dem Neustart der Datenbank passiert zunächst einmal solange nichts, bis auch wirklich ein Ziel für das Auditing vorgegeben wurde. Erst dann können mögliche Ergebnisse aus den DBA-Views (dba_audit_*) gelesen werden. Die gewünschten Ziele "markiert" man mit dem Befehl AUDIT. Die Syntax des AUDIT Befehls ist:

audit <statement_option|privilege_option> [by user] [by <session|access>] [whenever <successful|unsuccessful>]

Als Ziele bieten sich an:

System Privilegien (z.b. AUDIT CREATE SESSION)
DDL (z.B. AUDIT TABLE)
Schema Objekte (z.B. AUDIT SELECT ON scott.emp)

Welche Informationen werden nun von Oracle protokolliert, sobald das Auditing aktiviert und auch Ziele definiert wurden?

Username
Session Identifier
Terminal Identifier
Name des Schema-Objektes, auf das zugegriffen wurde
Die durchgeführte bzw. versuchte Aktion
Der Ergebnis/Fehlercode der Aktion
Datum und Zeit des Zugriffs
Genutzte Systemprivilegien

Diese Informationen sammelt Oracle bei genannter Einstellung (audit_trail=db) in der Datenbank. Abfragen kann man die Zugriffe über die View DBA_AUDIT_TRAIL (bzw. USER_AUDIT_TRAIL auf User-Ebene). Welche Objekte/Privilegien gerade überwacht werden, findet sich auch in den entsprechenden Views (*_AUDIT_STATEMENT, *_AUDIT_OBJECT, etc. – ein Blick auf das Ergebnis der Abfrage SELECT view_name FROM dba_views WHERE view_name LIKE '%AUDIT%' gibt eine ausführliche Liste).

Das Auditing einzelner Objekte/Privilegien lässt sich gezielt mit NOAUDIT wieder abschalten. Um die ganze "Audit-Engine" stillzulegen, bedarf es allerdings wiederum eines Datenbank Neustarts, nachdem der Parameter audit_trail wieder entfernt bzw. auf FALSE gesetzt wurde.

Fazit: Wenn ein Neustart der Datenbank kein K.O. Kriterium ist, lässt sich mit Oracle Audit eine gute Analyse mit detaillierten Ergebnissen durchführen.

System Triggers

Trigger bieten eine Möglichkeit, sich selbst ein angepasstes Auditing zu erstellen, welches auch keinen Neustart der Datenbank erfordert. Dafür erfordern sie allerdings ein wenig Handarbeit: Zuerst muss eine Tabelle angelegt werden, in welcher die Ereignisse sodann protokolliert werden können. Und anschließend gilt es dann auch noch, sich die passenden Trigger zu bauen - was den komplizierteren Teil dieser Aufgabe darstellt. Dafür kann das Ergebnis dann sehr genau an die Anforderungen angepasst werden - was sonst nur mit [Fine Grained Auditing(#fga) möglich ist (und auch erst ab 9i bzw. gar 10g).

Was lässt sich nun alles triggern? Wie schon in der Übersicht aufgezeigt, sind dies Systemereignisse auf Datenbank- und Schemaebene, DML Statements und INSTEAD OF Triggers auf Views (die uns im Sinne des Auditing allerdings hier nicht interessieren). Generell können Trigger ausgeführt werden:

Vor dem eigentlichen Statement/Event (=BEFORE)
Nach selbigem (=AFTER)
Anstelle von (=INSTEAD OF)

Um nun aber unser Auditing zu realisieren, müssen wir etwas tiefer in die Trigg-Kiste greifen. Die Syntax für CREATE TRIGGER lässt sich leicht aus dem Oracle-Handbuch heraussuchen. Die eigentliche Aktion, die der Trigger nun durchführen soll (nämlich einen Eintrag in unsere Logging Tabelle zu erstellen, und zwar mit allen gewünschten Details), ist da schon etwas aufwändiger. Ein ausbaufähiges Beispiel hierfür findet sich in der Oracle Dokumentation, unter "Using Triggers", im Abschnitt "Auditing with Triggers: Examples" – wer also an dieser Methode interessiert ist, möge bitte dort weiterlesen.

Nicht (mehr) benötigte Trigger kann man mit DROP TRIGGER löschen oder, falls man später wieder auf selbigen zurückgreifen möchte, mit ALTER TRIGGER..DISABLE abschalten.

Fazit: Etwas aufwändiger - kann aber zu einem maßgeschneiderten Auditing führen. Allerdings nicht für SELECT Statements geeignet.

Fine Grained Auditing (FGA)

Während man bis zur Version 8i nur aus obigen beiden (und der letzten) Methoden wählen konnte, kommt ab Version 9i eine interessante Alternative hinzu: Das Fine Grained Auditing. Der Name lässt es bereits vermuten: Hier kann die Anpassung bis ins Detail gehen. Und das weit weniger Umständlich, als mit unseren gerade behandelten Triggern. Dafür sind sie aber auch auf SELECT und DML Statements beschränkt, bei Oracle 9i sogar nur auf erstere.

Das Ganze wird gesteuert über das Package dbms_fga, und lässt sich bis auf Spaltenebene granulieren. So ist es nicht nur möglich, alle Zugriffe auf eine Tabelle zu protokollieren - sondern man kann es sogar so konfigurieren, dass z.B. nur alle Zugriffe auf eine Tabelle protokolliert werden, bei dem der Wert für eine Spalte eine bestimmte Größe übersteigt. Großartige Programmierkünste sind dafür nicht notwendig - die Aufrufe sind leicht verständlich. Nähere Details finden sich in der Oracle Dokumentation sowie auf den im Anhang verlinkten Seiten.

Fazit: Spätestens ab 10g die zu bevorzugende Methode, wenn SELECT und/oder DML Statements zu überwachen sind.

DML FGA mit Oracle 9i

In Version 9i unterstützt Oracle lediglich FGA für SELECT Statements. Mit ein paar Tricks lässt sich das in geringem Umfang aufbohren, indem man FGA mit Triggern kombiniert. Um z.B. INSERT Statements auf eine Tabelle mit zu protokollieren, auf die man bereits FGA angesetzt hat, erstellt man sich einen Trigger AFTER INSERT ON <tabelle>, der aus den Daten des INSERT Statements ein SELECT generiert, und so den gerade eingefügten Datensatz wieder abfragt - und schon greift das FGA wieder. Um dieses "künstliche SELECT" von den regulären zu unterscheiden, hilft es z.B., ein Feld mit AS insert_cmd umzubenennen.

Das ist freilich etwas aufwändiger, da man für jede Tabelle manuell einen passenden Trigger generieren muss. Mit ein paar Programmierkenntnissen kann man sich jedoch ein kleines PL/SQL Skript erstellen, welches das Statement über die Informationen aus dem Data Dictionary für jede beliebige Tabelle generieren kann.

Einfacher ist es, wenn man nur feststellen möchte, ob auf ein Objekt überhaupt noch Zugriffe stattfinden, und wenn ja, von wem. Für diesen Fall lassen sich recht einfache kleine Prozeduren verwenden, die ich hier ausnahmsweise einmal quoten möchte:

Auditing aktivieren:

CREATE OR REPLACE PROCEDURE iz_audit_on (user_name IN VARCHAR2,table_name IN VARCHAR2) IS
 -- Establish auditing to the table specified by the parameters
 -- Param user_name : Owner of the table
 -- Param table_name: Name of the table
 PROCEDURE do_trigger(uname IN VARCHAR2,tname IN VARCHAR2) IS
   -- create the trigger
   -- uname: Owner
   -- tname: Table
   stmt VARCHAR2(2000);
 BEGIN
   stmt   := 'CREATE OR REPLACE TRIGGER '||uname||'.iz_audit_'||tname
           ||' BEFORE INSERT OR UPDATE OR DELETE ON '||uname||'.'||tname
           ||' DECLARE cnum NUMBER; BEGIN '
           ||' IF INSERTING THEN SELECT COUNT(*) AS insert_cmd'
           ||'   INTO cnum FROM '||uname||'.'||tname||' WHERE 1=0;'
           ||' ELSIF DELETING THEN SELECT COUNT(*) AS delete_cmd'
           ||'   INTO cnum FROM '||uname||'.'||tname||' WHERE 1=0;'
           ||' ELSIF UPDATING THEN SELECT COUNT(*) AS update_cmd'
           ||'   INTO cnum FROM '||uname||'.'||tname||' WHERE 1=0;'
           ||' END IF; END;';
   EXECUTE IMMEDIATE stmt;
 EXCEPTION
   WHEN OTHERS THEN
     dbms_output.put_line('! Could not create trigger on table '||uname||'.'||tname||': ');
	 dbms_output.put_line('- '||stmt);
	 dbms_output.put_line('- '||SQLERRM);
 END;
 PROCEDURE do_audit(uname IN VARCHAR2,tname IN VARCHAR2) IS
   -- enable auditing and then call the do_trigger procedure
   -- uname: Owner
   -- tname: Table
   stmt VARCHAR2(2000);
 BEGIN
   dbms_fga.add_policy(
     object_schema=>uname,
     object_name=>tname,
     policy_name=>tname||'_SELECT'
   );
   -- create the trigger only if the FGA policy was created (otherwise
   -- exception handler fires)
   do_trigger(uname,tname);
 EXCEPTION
   WHEN OTHERS THEN
     dbms_output.put_line('! Could not create FGA policy for table '||uname||'.'||tname||': '||SQLERRM);
 END;
BEGIN
 do_audit(user_name,table_name);
END;
/

audit_on.sql

Auditing deaktivieren:

CREATE OR REPLACE PROCEDURE iz_audit_off (user_name IN VARCHAR2,table_name IN VARCHAR2) IS
 -- Disable the auditing established with iz_audit_on
 -- Param user_name : Owner
 -- Param table_name: Name of the audited table
 PROCEDURE do_trigger(uname IN VARCHAR2,tname IN VARCHAR2) IS
   -- drop triggers
   -- uname: Owner
   -- tname: Table
   stmt VARCHAR2(2000);
 BEGIN
   stmt   := 'DROP TRIGGER '||uname||'.iz_audit_'||tname;
   EXECUTE IMMEDIATE stmt;
 EXCEPTION
   WHEN OTHERS THEN
     dbms_output.put_line('! Could not drop trigger on table '||uname||'.'||tname||': ');
	 dbms_output.put_line('- '||stmt);
	 dbms_output.put_line('- '||SQLERRM);
 END;
 PROCEDURE do_audit(uname IN VARCHAR2,tname IN VARCHAR2) IS
   -- drop FGA policies and call the do_trigger procedure
   -- uname: Owner
   -- tname: Table
   stmt VARCHAR2(2000);
 BEGIN
   dbms_fga.drop_policy(
     object_schema=>uname,
     object_name=>tname,
     policy_name=>tname||'_SELECT'
   );
 EXCEPTION
   WHEN OTHERS THEN
     dbms_output.put_line('! Could not drop FGA policy for table '||uname||'.'||tname||': '||SQLERRM);
 END;
BEGIN
 do_audit(user_name,table_name);
 -- drop the trigger even if FGA policy could not be dropped:
 do_trigger(uname,tname);
END;
/

audit_off.sql

Beide Prozeduren werden jeweils mit den Parametern UserName (d.h. das Schema, in welchem sich das zu überwachende Objekt befindet) und TableName (also dem Namen der zu überwachenden Tabelle) aufgerufen. Die erste Prozedur, iz_audit_on(), erstellt zunächst eine Policy für SELECT Statements auf die angegebene Tabelle, und legt anschließend den Trigger für die DML Statements an. Beides jeweils in dem Schema, in welchem sich auch das zu überwachende Objekt befindet (wem das nicht gefällt, der kann es ja im Skript einfach anpassen). Die zweite Prozedur (iz_audit_off()) macht den Vorgang wieder rückgängig – etwa bereits generierte Eintrage in der Tabelle SYS.FGA_LOG$ werden jedoch nicht wieder entfernt. Die Policies werden nach dem Schema <Tabellenname>_SELECT benannt, die Trigger heißen jeweils <Username>.IZ_AUDIT_<Tabellenname>.

Zu den Prozeduren sei noch angemerkt, dass es sich hier nur um "Rohfassungen" als "Anschauungsmaterial" handelt. Sie funktionieren zwar "Out-of-the-Box" (auch wenn ich selbstverständlich keinerlei Haftung und Gewährleistung übernehme), jedoch sollten speziell die Exceptions noch ein wenig besser angepasst werden.

Die geloggten Informationen finden sich in diesem Fall, wie beim FGA, in der genannten Tabelle SYS.FGA_LOG$, und lassen sich natürlich auch bequem über die entsprechende DBA-View DBA_FGA_AUDIT_TRAIL abfragen.

Fazit: Sollte Oracle Audit z.B. aufgrund des notwendigen Neustarts der Datenbank nicht in Frage kommen, und sich eine Migration auf 10g ebenfalls nicht anbietet, ist dies ein guter Kompromiss für das Überwachen von SELECT und/oder DML Statements – und ggf. auch alles andere, was Trigger hergeben.

System Logs

Die System-Logfiles sind zwar kein Auditing im eigentlichen Sinne - es lässt sich aber doch einige "Audit-Information" hier entnehmen. So werden sämtliche Startups/Shutdowns der Datenbank, die Logfile-Wechsel und vieles mehr protokolliert. Alle oben genannten Auditing Funktionen lassen sich nicht rückwirkend aktivieren – die Logs werden jedoch generell geschrieben, und geben so noch manche Information preis, die im alert_log zu finden sind.

Fazit: Grundlegende Informationen lassen sich auch mit einfachsten Mitteln finden …

Ein einfacher Login-Monitor

Ohne viel Zuarbeit (und ohne weitere Voraussetzungen) lässt sich ein einfacher Login-Monitor realisieren, der die Anmeldung eines gegebenen Benutzers im alert_log protokolliert:

CREATE OR REPLACE TRIGGER monitor_log
  AFTER LOGON ON DATABASE
  DECLARE
    line v$session%rowtype;
  BEGIN
    IF sys.login_user = 'MONITOR' THEN
      SELECT * INTO line FROM v$session WHERE sid=TO_NUMBER(SUBSTR(dbms_session.unique_session_id,1,4),'XXXX');
      dbms_system.ksdwrt(2,'MONITOR logged in from "'||line.machine||'" using "'||line.module||'" as OS-User "'||line.osuser);
    END IF;
  EXCEPTION
    WHEN OTHERS THEN dbms_system.ksdwrt(2,'Monitor logged in!');
  END;
/

audit_monitor_login.sql

Hierbei lassen sich natürlich die protokollierten Details beliebig ergänzen. Im Beispiel-Code wird der Login Zeitpunkt (automatisch bei einem neuen eintrag von Oracle ergänzt), der Name des Rechners, von dem der Login erfolgte, das vom Benutzer verwendete Programm, sowie der OS-Benutzer protokolliert. Ein Eintrag sieht dann etwa so aus:

Tue Oct 16 10:25:30 2007 MONITOR logged in from "machine.domain.com" using "sqlplus@machine.domain.com (TNS V1-V3)" as OS-User "monitor"

Weiterführende Links

Überblick / Allgemein

Auditing für die Oracle Datenbank
Oracle Security: Auditing
Detecting SQL Injection in Oracle (practical real-world examples of auditing)

Oracle Audit

Trigger

Auditing Oracle Data (Setting up customized Auditing w/ triggers)
Oracle Complete Data Audit (Setting up customized Auditing w/ triggers)
Happy (Audit) Trails to Oracle E-records Management
Extending Oracle for System Event Auditing
Create the Audit Trigger
Building an Audit Trail for Your Data

Fine Grained Auditing:

2020-11-18

IzzySoft

Oracle Auditing

Überblick

Inhalt

Auditing Methoden

Oracle Audit

System Triggers

Fine Grained Auditing (FGA)

DML FGA mit Oracle 9i

System Logs

Ein einfacher Login-Monitor

Weiterführende Links

Überblick / Allgemein

Oracle Audit

Trigger

Fine Grained Auditing: