Auditing unter Oracle 11g Release 2

Ziel:

Mit diesem Blogeintrag soll gezeigt werden, welche Audit Funktionen die Oracle Datenbank in der Version 11g Release 2 bietet und wie diese eingesetzt werden können, um zum Beispiel Änderungen (DELETE, INSERT, UPDATE etc.) an einer Tabelle zu protokollieren.

Was ist Auditing?

Hinter dem Begriff Auditing verbirgt sich das Überwachen und Aufzeichnen von bestimmten Benutzeraktivitäten, wie zum Beispiel das Löschen einer ganzen Tabelle. Dabei werden Informationen über den Löschvorgang und den Benutzer, der diesen Vorgang ausgeführt hat in einer Tabelle oder Datei protokolliert. Dadurch ist es dem Datenbankadministrator möglich herauszufinden wer diese Tabelle wann gelöscht hat.

Oracle selbst protokolliert immer Anmeldungen mit administrativen Rechten, Datenbankstarts und -stops mit, auch wenn das Auditing nicht expliziert aktiviert wurde.

Die aktuelle Version der Datenbank bietet die folgenden Audit-Möglichkeiten, die im folgenden näher betrachtet werden.

• Standard Auditing (wird gesteuert über AUDIT und NOAUDIT Statements)
• Fine-Grained Auditing (fein granulares Auditing bis auf Spaltenebene herunter)
• Value-Based Auditing (Aufzeichnung von geänderten Tabellenspalten – z.B. Vorherwerte)

Voraussetzungen:

Um dieses Tutorial erfolgreich absolvieren zu können muss eine Oracle 11g Release 2 Datenbank vorhanden sein. Zudem muss das Beispielschema HR in der Datenbank vorhanden sein. Die darin enthaltenden Objekte werden als Grundlage für die Auditing Beispiele verwendet.

Standard Auditing:

Beim Standard Auditing werden die Befehl AUDIT und NOAUDIT verwendet, um das Protokollieren von Benutzeraktionen zu aktivieren bzw. deaktivieren. Dabei kann das Auditing für SQL Statements, Privilegien, Schema Objekte usw. aktiviert werden. Die Speicherung der Audit Daten erfolgt entweder in Datenbank Audit Trail Tabelle SYS.AUD$ oder in einer Betriebssystemdatei erfolgen.

Gesteuert wird das Auditing über den Initialisierungsparameter AUDIT_TRAIL, der folgende Werte erlaubt:

Wert	Beschreibung
DB	Aktiviert das Auditing und speichert die Audit Daten im Datenbank Audit Trail SYS.AUD$.
OS	Aktiviert das Auditing und speichert die Audit Daten in einer Betriebssystemdatei. Das Standardverzeichnis ist $ORACLE_BASE/admin/$SID/adump. Der Pfad zur Datei kann über den Parameter AUDIT_FILE_DEST entsprechend angepasst werden.
NONE	Deaktiviert das Auditing.
DB, EXTENDED	Wie DB, aber füllt zusätzlich die SQL Bind und CLOB-type Spalten der SYS.AUD$ Tabelle.
XML	Wie OS, nur werden die Daten im XML Format gespeichert.
XML, EXTENDED	Wie XML, aber füllt zusätzlich die SQL Bind und CLOB-type Spalten der SYS.AUD$ Tabelle.

Aktivieren des Auditing

Der Parameter AUDIT_TRAIL kann nicht im laufendem Betrieb verändert werden, dass heißt, dass ein Datenbankneustart notwendig ist.

[oracle@rac01 ~]$ sqlplus / as sysdba  

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 6 09:47:45 2011  
Copyright (c) 1982, 2009, Oracle.  
All rights reserved.  

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options  

SQL> ALTER SYSTEM SET audit_trail='DB' SCOPE=spfile SID='*';  

System altered.  

SQL> shutdown immediate 
Database closed. 
Database dismounted. 
ORACLE instance shut down.
 
SQL> startup 
ORACLE instance started.  
Total System Global Area  801701888 bytes 
Fixed Size                  2217632 bytes 
Variable Size             587204960 bytes 
Database Buffers          209715200 bytes 
Redo Buffers                2564096 bytes 
Database mounted. 
Database opened.

Das Datenbank Auditing ist nun aktiviert.

Beispiel – Audit von DML Statements:

In diesem Beispiel wird das Auditing für die folgenden Benutzeraktivitäten eingeschaltet.

• SELECT, INSERT, UPDATE, DELETE für alle Tabellen im HR Schema

Zunächst werden die Audit Regeln festgelegt. Dafür meldet man sich als SYS Benutzer an der Datenbank an und führt das folgende AUDIT SQL Statement aus.

SQL> CONNECT sys/password AS SYSDBA  
SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY hr BY ACCESS;

Durch das BY ACCESS wird ein detailierterer Audit Eintrag in der SYS.AUD$ Tabelle erzeugt. Standardmäßig ist BY SESSION gesetzt. Oracle empfiehlt die Verwendung von BY ACCESS.

Hinweis: In früheren Versionen sorgte der Zusatz BY SESSION dafür, dass gleiche Benutzeraktionen in der Session zu einem Audit Eintrag zusammengefasst wurden. BY ACCESS sorgte dagegen dafür, dass jeder Zugriff auf die Objekte protokolliert wurde. In Version 11g Release 2 schreibt nun auch BY SESSION einzelne Einträge für jeden Zugriff in die SYS.AUD$ Tabelle.

Um nun das Auditing zu testen, meldet man sich mit dem Benutzer HR an und führt Benutzeraktionen für die Tabelle EMPLOYEES aus.

SQL> CONNECT hr/hr   
SQL> SELECT COUNT(*) FROM employees; 
SQL> UPDATE employees SET salary=3900 WHERE employee_id=122; 
SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id) 
     VALUES(999,'Mustermann','mustermann(at)dba-blog.de',sysdate,'SH_CLERK'); 
SQL> DELETE FROM employees WHERE employee_id=999;

Anschließend melden wir uns wieder als SYS Benutzer an und überprüfen die Audit Tabelle. Für das Auslesen der Audit Informationen des Standard Auditing bietet Oracle die View DBA_AUDIT_TRAIL an. Für das Auslesen kann das folgende SQL Statement verwendet werden.

SQL> SET linesize 250  
SQL> COL username FOR A15 
SQL> COL userhost FOR A30 
SQL> COL extended_timestamp FOR A40 
SQL> COL owner FOR A15 
SQL> COL obj_name FOR A30 
SQL> COL action_name FOR a10  
SQL> SELECT username,
            userhost,
            extended_timestamp, 
            owner,
            obj_name,
            action_name
       FROM dba_audit_trail 
      WHERE username = 'HR' 
   ORDER BY username,
            timestamp;  

USERNAME        USERHOST                       EXTENDED_TIMESTAMP                       OWNER           OBJ_NAME                       ACTION_NAM 
--------------- ------------------------------ ---------------------------------------- --------------- ------------------------------ ---------- 
HR              rac01.dba-blog.de              06-APR-11 11.16.46.134257 AM +02:00      HR              EMPLOYEES                      SELECT 
HR              rac01.dba-blog.de              06-APR-11 11.17.09.873371 AM +02:00      HR              EMPLOYEES                      UPDATE 
HR              rac01.dba-blog.de              06-APR-11 11.19.30.563658 AM +02:00      HR              EMPLOYEES                      INSERT 
HR              rac01.dba-blog.de              06-APR-11 11.19.48.582245 AM +02:00      HR              EMPLOYEES                      DELETE 

Wie man sieht wurden alle durchgeführten DML Statements aufgezeichnet. Das Auditing funktioniert somit einwandfrei.

Ausschalten des Auditing

Um die oben vorgenommenen Audit Einstellungen rückgängig zu machen muss das folgende NOAUDIT Kommando ausgeführt werden.

SQL> CONNECT syst/passsword AS SYSDBA    
SQL> NOAUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY hr;

Weitere Informationen zur Verwendung des AUDIT Befehls finden sich in der Oracle® Database SQL Language Reference.

Fine-Grained Auditing (FGA):

Mit dem Fine-Grained Auditing bietet Oracle die Möglichkeit das Auditing bis hin auf eine bestimmte Spalte einer Tabelle zu beschränken. Zudem kann man Bedingungen angeben, die zunächst erfüllt sein müssen bevor ein Audit Eintrag erstellt wird, das kann zum Beispiel ein bestimmter Wertebereich sein.

Für das Auditing von Objekten werden sogenannte FGA Policies angelegt, die über die ADD_POLICY Methode des PL/SQL Paketes DBMS_FGA erstellt werden können. Diese Methode bietet zu dem die Möglichkeit eigenen PL/SQL Code in Form einer Prozedur einzubinden – dies wird als Handler Module bezeichnet.

Die Prozedur muss dabei die folgende Schnittstelle implementieren. 

SQL> PROCEDURE name ( 
        object_schema IN VARCHAR2, 
        object_name   IN VARCHAR2, 
        policy_name   IN VARCHAR2
) AS ... 

Hinweis: Sollte die Prozedur einen Fehler werfen, bricht auch das SQL Statement des Benutzers mit einem Fehler ab. An dieser Stelle darf es also nicht zu Problemen kommen. Von daher sollten Ausnahmen dringend abgefangen werden.

Anlegen eines Handler Modules

Um die Handler Module Funktion zu demonstrieren lege ich zunächst eine Prozedur an. Diese Prozedur gibt lediglich Informationen aus, die dem Benutzer darüber informieren, dass seine SQL-Abfrage protokolliert wurde.

SQL> CREATE OR REPLACE PROCEDURE fga_user_info (
        p_schema IN VARCHAR2,
        p_object IN VARCHAR2,
        p_policy IN VARCHAR2
) AS 
BEGIN
   -- Ausgabe eines Hinweis, dass die ausgeführte Abfrage protokolliert wurde. 
   DBMS_OUTPUT.PUT_LINE('Ihre Abfrage "' || SYS_CONTEXT('USERENV', 'CURRENT_SQL') || '" wurde aufgezeichnet.');
   DBMS_OUTPUT.PUT_LINE('Ausgelöst durch Policy "' || p_policy || '".');
END;
/

Die Benutzer, dessen Aktionen protokolliert werden sollen müssen über Ausführungsrechte für diese Prozedur verfügen. Im folgenden wird dies für den Benutzer HR eingerichtet.

SQL> GRANT execute on sys.fga_user_info TO hr;

Hinzufügen einer FGA Policy

Im nächsten Schritt wird eine FGA Policy für die Tabelle EMPLOYEES angelegt. An dieser Stelle zeige ich zudem wie man eine Policy auf Spaltenebene einrichtet. Zudem wird eine Bedingung angegeben, die nur einen Audit Eintrag erzeugt, wenn jemand den Wert der SALARY Spalte auf unter 2000 ändert.

SQL> BEGIN
   DBMS_FGA.ADD_POLICY (
      object_schema=>'HR',
      object_name=>'EMPLOYEES',
      policy_name=>'FGA_EMP_SAL_POL',
      audit_column=>'SALARY',
      audit_condition=>'salary < 2000',
      statement_types=>'UPDATE',
      handler_schema=>'SYS',
      handler_module=>'FGA_USER_INFO',
      enable=>TRUE
   );
END;
/

Testen der Policy

Für das Testen melden wir uns als HR Benutzer an und führen die folgende UPDATE Anweisung aus.

SQL> CONNECT hr/hr
SQL> SET serveroutput ON
SQL> UPDATE employees SET salary=1900 WHERE employee_id=122;

In diesem Fall würde die Bediging SALARY < 1000 greifen und ein Audit Eintrag würde erstellt werden. Durch das Setzen des Handler Modules wird folgende Ausgabe generiert.

Ihre Abfrage "UPDATE employees SET salary=1900 WHERE employee_id=122" wurde aufgezeichnet. 
Ausgeloest durch Policy "FGA_EMP_SAL_POL".

Überprüfen der Audit Tabelle

Nun wird überprüft, ob auch der Audit Eintrag in der Tabelle SYS.AUD$ vorhanden ist. Für das Anzeigen der Audit Einträge, die durch das Fine-Grained Auditing entstanden sind, bietet Oracle die View DBA_FGA_AUDIT_TRAIL an.

SQL> CONNECT sys/password AS SYSDBA  
SQL> SET linesize 250 
SQL> COL db_user FOR A10
SQL> COL userhost FOR A20
SQL> COL object_name FOR A20
SQL> COL sql_text FOR A60
SQL> SELECT db_user,
            userhost,
            object_name,
            policy_name,
            statement_type,
            sql_text
       FROM dba_fga_audit_trail
      WHERE db_user='HR'
   ORDER BY db_user, 
            extended_timestamp;

DB_USER    USERHOST             OBJECT_NAME          POLICY_NAME                    STATEME SQL_TEXT
---------- -------------------- -------------------- ------------------------------ ------- ------------------------------------------------------------
HR         rac01.dba-blog.de    EMPLOYEES            FGA_EMP_SAL_POL                UPDATE  UPDATE employees SET salary=1900 WHERE employee_id=122

Wie die Ausgabe zeigt, wurde die Änderung des Gehalts auf 1900 für den Benutzer mit der ID 122 erfolgreich protokolliert.

Löschen der FDA Policy

Für das Löschen der Policy bietet das PL/SQL Paket DBMS_FGA die Prozedur DROP_POLICY.

SQL> BEGIN
   DBMS_FGA.DROP_POLICY (
      object_schema=>'HR',
      object_name=>'EMPLOYEES',
      policy_name=>'FGA_EMP_SAL_POL'
   );
END;
/

Value-Based Auditing

Das Value-Based Auditing wird häufig zur Aufzeichnung von historischen Daten verwendet. Dabei kommen Trigger zum Einsatz, die zum Beispiel beim Ändern einer Tabellenzeile, die alten Werte in eine seperate Tabelle schreiben.

Es können Trigger für UPDATE, DELETE, INSERT Statements erstellt werden. Das Protokollieren von Lesezugriffe auf Tabellen ist mit diesem Audit Typ nicht möglich.

Anlegen der Protokolltabelle

Im ersten Schritt wird die Tabelle angelegt, in der die historischen Daten abgelegt werden sollen. Ich verwende als Grundlage für die Tabelle, die Struktur der EMPLOYEES Tabelle des HR Schemas.

SQL> CONNECT sys/password AS SYSDBA  
SQL> CREATE TABLE employee_hist AS SELECT * FROM hr.employees WHERE 1=2; 
SQL> ALTER TABLE employee_hist ADD action VARCHAR2(10); 
SQL> ALTER TABLE employee_hist ADD action_date DATE; 
SQL> ALTER TABLE employee_hist ADD action_user VARCHAR2(20);

Hinweis: In der CREATE TABLE AS SELECT (kurz CTAS) Anweisung verwende ich die WHERE Klausel 1=2. Diese Bedigung wird niemals war. Dadurch wird nur die Tabelle erstellt, die Daten werden nicht übernommen.

Damit der HR Benutzer auch in die History Tabelle schreiben kann, weise ich ihm INSERT Berechtigungen auf diese Tabelle zu.

SQL> GRANT insert on sys.employee_hist TO hr;

Hinweis: Der Benutzer, in diesem Fall HR, sollte keine DELETE Berechtigungen auf der Protokolltabelle haben, anderenfalls könnte er Einträge löschen.

Trigger anlegen

Im nächsten Schritt wird nun der Trigger angelegt. Ich diesem Beispiel wird der Trigger im SYS Schema abgelegt. In Produktivumgebungen sollte dafür ein seperater “Audit Benutzer” angelegt werden. 

SQL> CONNECT sys/password AS SYSDBA
SQL> CREATE OR REPLACE TRIGGER employee_trg 
AFTER INSERT OR UPDATE OR DELETE ON hr.employees 
FOR EACH ROW  
DECLARE
   v_action varchar2(10) := '';
BEGIN
   -- Ermittlung des Actiontyps
   IF INSERTING THEN
      v_action := 'INSERT';
   ELSIF UPDATING THEN
      v_action := 'UPDATE';
   ELSIF DELETING THEN
      v_action := 'DELETE';
   END IF;

   -- Insert in History Tabelle
   INSERT INTO sys.employee_hist (action, action_date, action_user, employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) 
   VALUES(v_action, sysdate, sys_context('USERENV','SESSION_USER'), :OLD.employee_id, :OLD.first_name, :OLD.last_name, :OLD.email, :OLD.phone_number, :OLD.hire_date, :OLD.job_id, :OLD.salary, :OLD.commission_pct, :OLD.manager_id, :OLD.department_id);
END;
/

Testen des Triggers

Zum Abschluss wird der Trigger überprüft. Dafür wird die folgende UPDATE Anweisung als HR Benutzer ausgeführt.

SQL> CONNECT hr/hr
SQL> UPDATE employees SET salary=3900 WHERE employee_id=122;

Im letzten Schritt wird die History Tabelle überprüft.

SQL> SELECT action,
            action_date,
            action_user,
            employee_id,
            salary
       FROM employee_hist
   ORDER BY action_date;

ACTION     ACTION_DA ACTION_USER          EMPLOYEE_ID     SALARY
---------- --------- -------------------- ----------- ----------
UPDATE     06-APR-11 HR                           122       1900

Wie man sieht wurde der alte Wert der Spalte SALARY in der History Tabelle gespeichert. Zudem sieht man welcher Benutzer die Daten verändert hat.

Löschen des Triggers

Um den Trigger wieder zu entfernen, wird die DROP TRIGGER Anweisung ausgeführt. Alternativ kann der Trigger auch deaktiviert werden.

SQL> -- Trigger löschen 
SQL> DROP TRIGGER employee_trg; 
SQL> -- Trigger deaktivieren 
SQL> ALTER TRIGGER employee_trg DISABLE