Oracle APEX 18.2 Tutorial Teil 1 – SQL Workshop

Deutschsprachiges Einsteiger Tutorial für Oracle APEX 18.2. Tabellen mit dem SQL Workshop erstellen.

In dieser Beitragsreihe möchte ich Ihnen anhand einer einfachen Anwendung zur Verwaltung von Kochrezepten eine Einführung in die Anwendungsentwicklung mit der Low-Code Entwicklungsumgebung Oracle Application Express bieten. Um dieses Tutorial nachstellen zu können, benötigen Sie eine lauffähige Apex Installation. Wie Sie APEX einrichten habe ich in dem Beitrag Installation Oracle Apex dieses Blogs beschrieben. Alternativ können Sie sich unter https://apex.orale.com einen Testzugang anlegen und die von Oracle in der Cloud betriebenen APEX-Plattform zu Bildungszwecken nutzen.

Aufgabenstellung

Als Beispiel soll eine einfache Anwendung erstellt werden, mit der Kochrezepte verwaltet werden können. Ein Rezept kann dabei mehrere Zutaten besitzen. Die Verarbeitung der Zutaten soll in einfachen Arbeitsschritten dargestellt werden.

Datenmodell

  • Kochrezepte (cooking_recipe)
    • name
    • description
    • number_of_persons
  • Zutaten (ingredients)
    • name
    • quantity
    • unit
    • product_classification ( bio, vegetarian, vegan, …)
    • calories
  • processing_step
    • name
    • description
    • Pictures/Videos
    • product_id
    • order_number

APEX-Anwendung

Haben Sie sich für eine eigene APEX Installation entschieden, müssen Sie als Erstes als Instanzadministrator einen sogenannten WORKSPACE und eine  Workspace-Developer anlegen. (siehe dazu auch Installation Oracle Apex )

Bei der Nutzung des Server von Oracle apex.oracle.com erfolgt dieser Schritt im Rahmen der Erstanmeldung.

Je nach Variante gibt es verschiedene URLs über die Sie die APEX-Entwicklungsumgebung starten. Sie sollten bei allen Varianten zu der in Abbildung 1 dargestellten Login-Seite gelangen.

Abbildung 1: Anmeldung als APEX-Developer
Abbildung 1: Anmeldung als APEX-Developer

Hier melden Sie sich an Ihrem WORKSPACE an.

Anschließend gelangen Sie zur Startseite der APEX-Entwicklungsumgebung.

Abbildung 2: APEX-Entwicklungsumgebung
Abbildung 2: APEX-Entwicklungsumgebung

Dort können Sie die einzelnen Module von APEX starten. Für den Anfang sind für Sie der APPLICATION BUILDER und der SQL-WORKSHOP von Bedeutung.

Über den SQL-WORKSHOP können Sie mit Hilfe verschiedener Werkzeuge in Ihrem Datenbankschema Objekte wie z.B. Tabellen, Views,… und Daten bearbeiten.

Mit dem APPLICATION BUILDER erstellen Sie die APEX-Anwendungen.

Bei der Entwicklung einer APEX Anwendung werden Sie durch unterschiedliche Wizzards unterstützt. Alle diese Assistenten nutzen massiv die Metadaten Ihrer Oracle Datenbank. Die Metadaten der Datenbankobjekte fungieren quasi als Modell ( im Sinne eines MDA-Ansatzes ) für die Erstellung der Anwendung. Aus diesem Grund, ist es ratsam immer zuerst die zugrunde liegenden Datenbankobjekte anzulegen.

SQL-Workshop

Der SQL-Worshop bietet diverse Werkzeuge zum Bearbeiten von Datenbankobjekten.

Abbildung 3: SQL-WORSHOP
Abbildung 3: SQL-WORKSHOP

Der OBJECT BROWSER zeigt Ihnen alle Datenbankobjekte der Schematas, die dem Apex Workspace zugeordnet sind. Diese können mit diesem Tool bearbeitet werden.

Mit dem Werkzeug SQL COMMANDS können Sie direkt SQL-Befehle ausführen.

Hinter dem Bereich SQL SCRIPTS verbirgt sich ein Verwaltungswerkzeug zum Speichern und Ausführen ganzer SQL Skripte.

Der UTILITIES-Bereich fasst nützliche Datenbankwerkzeuge zusammen.

Das sehr mächtige Modul RESTFUL SERVICES ermöglicht es Ihnen Restfull Web Services auf Basis von SQL und Pl/SQL Anweisungen zu definieren.

Tabelle mit dem OBJECT BROWSER erstellen

Wie bereits erwähnt, sollten Sie mit dem Datenmodell beginnen. Anhand der Tabelle cooking_recipe möchte ich Ihnen das Erstellen einer Tabelle mit dem OBJECT BROWSER zeigen.

Starten Sie als erstes den OBJECT BROWSER.

Abbildung 4: Object Browser
Abbildung 4: Object Browser

Auf der linken Seite können Sie mittels verschiedener Filter unterschiedliche Datenbankobjekte anzeigen lassen. Diese lassen sich selektieren und dann über entsprechende Eingabemöglichkeiten bearbeiten.

Um eine neue Tabelle anzulegen, klicken Sie im rechten Bereich auf + und dann auf Tabelle.

Abbildung 5: Tabelle definieren
Abbildung 5: Tabelle definieren

Legen Sie hier die Spalten der Tabelle fest.

Mit Next > geht es weiter.

Abbildung 6: Sequence und Trigger erzeugen
Abbildung 6: Sequence und Trigger erzeugen

Im nächsten Schritt des Wizzards können Sie definieren, wie der Primärschlüssel der Tabelle erzeugt werden soll. Wie Abbildung 6 zeigt, können Sie durch APEX eine Sequence anlegen lassen.

Klicken Sie auf Next > .

Im nächsten Schritt können Sie Foreign Key Constraints anlegen. In diesem Beispiel überspringen Sie den Punkt mit Next > .

Jetzt folgt eine Seite, über die Sie weitere Constraints für Ihre Tabelle ergänzen können. Auch darauf können Sie hier verzichten. Weiter mit Next > .

Abbildung 7: Create Table
Abbildung 7: Create Table

Abschließend bietet der Assistent Ihnen die Funktion sich das SQL Skript anzusehen.


CREATE table "CO_COOKING_RECIPE" (
   "ID" NUMBER,
   "NAME" VARCHAR2(50),
   "DESCRIPTION" VARCHAR2(2000),
   "NUMBER_OF_PERSONS" NUMBER,
   "CREATED_BY" VARCHAR2(50),
   "CREATED" DATE,
   "CHANGED_BY" VARCHAR2(50),
   "CHANGED" DATE,
  constraint "CO_COOKING_RECIPE_PK" primary key ("ID")
)
/

CREATE sequence "CO_COOKING_RECIPE_SEQ"
/

CREATE trigger "BI_CO_COOKING_RECIPE"
  before insert on "CO_COOKING_RECIPE"
  for each row
 begin
   if :NEW."ID" is null then
     select "CO_COOKING_RECIPE_SEQ".nextval into :NEW."ID" from sys.dual;
   end if;
end;
/

Lassen Sie die Tabelle über Create Table anlegen.

Abbildung 8: Tabelle im Objekt Browser
Abbildung 8: Tabelle im Object Browser

Sie landen nun wieder im Object Browser. Dort können Sie weitere Strukturanpassungen an der Tabelle vornehmen. Für dieses Beispiel ist das aber nicht notwendig.

Die anderen Tabellen werden in den folgenden Beiträgen angelegt, um andere Werkzeuge und Aspekte der Anwendungsentwicklung mit APEX zu verdeutlichen.

Beitragsreihe

[catlist name=”Apex 18.2 Tutorial”]

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Oracle-Datenbank: Teamwork mit Bordmitteln

Synchronisieren Sie mit DDL-Triggern gemeinsames Arbeiten durch Ein -und Ausschenken von Objekten. Und das sogar für beliebige Datenbanktools.

Viele namhafte Tools zur Datenbankentwicklung bieten Funktionen zur Unterstützung von Entwicklerteams. So können Sie Datenbankobjekte zur Bearbeitung ausschenken. Andere Entwickler, können solange keine Änderungen an diesem Objekt mehr vornehmen, bis dieses wieder eingecheckt wurde. Leider funktioniert das meist nicht werkzeugübergreifend, da diese Tools oft eigene Tabellen für die Verwaltung der Bearbeitungszustände der Datenbankobjekte nutzen.

In diesen Beitrag möchte ich Ihnen eine einfache, aber effektive Möglichkeit zeigen, wie Sie so eine Funktion mit Bordmitteln der Oracle Datenbank selber entwickeln können. Vor allem ist bei der vorgestellten Lösung beachtenswert, dass Sie werkzeugübergreifend funktioniert.

Datenmodell

Benötigt werden zwei Tabellen. Über die Tabelle ddl_object_state wird der Bearbeitungsstand der Datenbankobjekte verwaltet. Sobald ein Entwickler ein Objekt bearbeiten möchten, blockt er dieses. Dabei wird ein Datensatz in dieser Tabelle eingefügt. Ist die Bearbeitung abgeschlossen, so wird das Objekt wieder freigegeben und der zugehörige Einträge entfernt.


CREATE TABLE ddl_object_state (
          object_type VARCHAR2(50 BYTE),
          object_owner VARCHAR2(32 BYTE),
          object_name VARCHAR2(32 BYTE), 
          changed_on DATE,
          changed_by VARCHAR2(32 BYTE)
)
/

Listing 1: Tabelle ddl_object_state

In der zweiten Tabelle ddl_object_state_hist wird beim Ein- und Auschecken neben den Statusinformationen auch ein DDL-Extrakt des bearbeiteten Objektes eingetragen. So dass Sie auf einen Stand zu Beginn und zum Ende der Bearbeitung zurückgreifen können.


CREATE TABLE ddl_object_state_hist
(
   object_type VARCHAR2 (50 BYTE),
   object_owner VARCHAR2 (32 BYTE),
   object_name VARCHAR2 (32 BYTE),
   status_code VARCHAR2 (20 BYTE),
   changed_on DATE,
   changed_by VARCHAR2 (32 BYTE),
   ddl_sql CLOB
)
/

Listing 2: Tabelle ddl_object_state_hist

PL/SQL Package ddl_util

Das PL/SQL Package fasst die benötigten Prozeduren zusammen.

PROCEDURE lock_object (
     p_object_type VARCHAR2,
     p_owner VARCHAR2,
     p_name VARCHAR2,
     p_trace_hist BOOLEAN DEFAULT true
  ) IS
    l_c NUMBER;
    l_locked_by VARCHAR2(32);
    l_changed_on DATE;
    l_ddl_extract CLOB;
BEGIN
    SELECT
      COUNT(*)
     INTO l_c
    FROM ddl_object_state
    WHERE object_owner = p_owner
       AND object_name = p_name;

  IF l_c = 0 THEN
       INSERT INTO ddl_object_state (
          object_type,
          object_owner,
          object_name,
          changed_on,
          changed_by )
       SELECT
          p_object_type,
          p_owner object_owner,
          p_name object_name,
          SYSDATE changed_on,
          ora_login_user changed_by
       FROM dual;

    IF p_trace_hist THEN
       l_ddl_extract := dbms_metadata.get_ddl(p_object_type,p_name);

       INSERT INTO ddl_object_state_hist (
          object_type,
          object_owner,
          object_name,
          ddl_sql,
          status_code,
          changed_on,
          changed_by )
       SELECT
          p_object_type,
          p_owner object_owner,
          p_name object_name,
          l_ddl_extract,
          'LOCK',
          SYSDATE changed_on,
          ora_login_user changed_by
       FROM dual;
    END IF;
 ELSE
      SELECT
          changed_by,
          changed_on
      INTO
        l_locked_by,
        l_changed_on
      FROM ddl_object_state
      WHERE object_owner = p_owner
        AND object_name = p_name;

     IF l_locked_by <> ora_login_user THEN
        raise_application_error(-20001,'Object was already locked by '
             || l_locked_by
             || ' at '
             || TO_CHAR(l_changed_on,'dd.mm.yyyy HH24:Mi:ss') );
     END IF;
 END IF;

END;

Listing 3: Procedure lock_object

Die Prozedur lock_object erhält als Parameter beim Aufruf den Objekttyp, den Besitzer des Objektes und den Namen des Objektes.

Als erstes prüft die Prozedur, ob das Objekt bereits von einem anderen User ausgescheckt wurde. Ist das der Fall wird ein application_error -20001 ausgelöst. Wenn das Objekt nicht in Bearbeitung ist, trägt die Prozedur diese in der Tabelle ddl_object_state ein und extrahiert eine DDL-Anweisung des Objektes. Diese wird dann in der Tabelle ddl_object_state archiviert.

PROCEDURE unlock_object (
     p_object_type VARCHAR2,
     p_owner VARCHAR2,
     p_name VARCHAR2,
     p_trace_hist BOOLEAN DEFAULT true,
     p_secure_mode BOOLEAN DEFAULT true) 
IS
     l_c NUMBER;
     l_locked_by VARCHAR2(32);
     l_changed_on DATE;
     l_ddl_extract CLOB;
BEGIN
     SELECT COUNT(*)
       INTO l_c
     FROM ddl_object_state
     WHERE object_owner = p_owner
       AND object_name = p_name;

 IF l_c = 1 THEN
     SELECT
       changed_by,
       changed_on
     INTO
       l_locked_by,
       l_changed_on
     FROM ddl_object_state
     WHERE object_owner = p_owner
       AND object_name = p_name;

    IF p_secure_mode AND l_locked_by <> ora_login_user  THEN
        raise_application_error(-20002,'You have no permissions in secure mode to unlock the object. The Object was locked by '
              || l_locked_by
              || ' at '
              || TO_CHAR(l_changed_on,'dd.mm.yyyy HH24:Mi:ss') );
    END IF;

    DELETE ddl_object_state
      WHERE object_owner = p_owner
        AND object_name = p_name;

    IF p_trace_hist THEN
      l_ddl_extract := dbms_metadata.get_ddl(p_object_type,p_name);

      INSERT INTO ddl_object_state_hist (
            object_type,
            object_owner,
            object_name,
            ddl_sql,
            status_code,
            changed_on,
            changed_by )
      SELECT
            p_object_type,
            p_owner object_owner,
            p_name object_name,
            l_ddl_extract,
            'UNLOCK',
            SYSDATE changed_on,
            ora_login_user changed_by
      FROM dual;
   END IF;
 
 END IF;

END;

Listing 4: Procedure unlock_object

Um ein Datenbankobjekt nach der Bearbeitung wieder frei zu geben, nutzen Sie die Prozedur unlock_object. Diese prüft im Secure Mode, ob Sie im Vorfeld das entsprechende Objekt auch selber ausgelockt haben. Ist das nicht der Fall, wird der Applikationsfehler -20002 ausgelöst. Ein von Ihnen ausgelocktes Objekt wird durch das Löschen des zugehörigen Eintrags in der Tabelle ddl_object_state wieder zur Bearbeitung frei gegeben. In der Tabelle ddl_object_state_hist wird wieder ein DDL-Extrakt gespeichert.

Objekte, die von einem anderen Entwickler als Sie selbst geblockt wurden, können Sie durch das Setzen des Parameters p_secure_mode = false wieder freigegeben. Auch dabei wird ein Historiendatensatz erzeugt. Anschließend können Sie so den Besitz durch Aufrufen von lock_object übernehmen.

PROCEDURE check_object_state (
             p_object_type VARCHAR2,
             p_owner VARCHAR2,
             p_name VARCHAR2
           );

Listing 5: Procedure check_object_state

Mit der dritten Procedur check_object_state können Sie einfach den Bearbeitungsstand eine Datenbankobjektes überprüfen.

Wie bereits erwähnt, speichern die Prozeduren beim Ein- und Auschecken jeweils ein DDL-Statements in der Historientabelle. Wenn Sie eine genaue Protokollierung aller Anpassungen benötigen, möchte ich Sie auf den Artikel Oracle Datenbankentwicklung: DDL-Trigger zur Auditierung von Strukturänderungen hinweisen.

DDL-Trigger

Anschließend benötigen Sie noch zwei DDL-Trigger, die Sie in dem Entwicklungsschema anlegen müssen.


CREATE OR REPLACE TRIGGER trg_ddl_util_before 
     BEFORE ALTER OR DROP 
    ON DATABASE 
DECLARE 
BEGIN
      IF ora_dict_obj_name NOT IN (
               'TRG_DDL_UTIL_BEFORE',
               'TRG_DDL_UTIL_AFTER',
               'DDL_UTIL' ) 
          AND ora_login_user NOT IN (
               'SYS',
               'SYSTEM' ) 
      THEN
         ddl_util.lock_object(ora_dict_obj_type,ora_dict_obj_owner,ora_dict_obj_name);
      END IF;
END;
/

Listing 6: Trigger trg_ddl_util_before

Der Trigger trg_ddl_util_before wird bei ALTER- und DROP-Anweisungen ausgeführt. Für einen Create-Befehl macht dieser Zeitpunkt keinen Sinn, da das Objekt in der Datenbank noch nicht vorliegen kann.

Bei der Verwendung von DDL-Triggern sollten Sie besondere Vorsicht walten lassen. Da diese Trigger auch bei Änderungen an sich selbst gefeuert werden, kann es vorkommen, dass man bei einer fehlerhaften Programmierung evtl. keine Änderungen mehr an den Triggern vornehmen kann. Aus diesem Grund prüft der Trigger zu Beginn, ob Änderungen an ihm selbst bzw. an dem Trigger trg_ddl_util_after ausgeführt werden sollen. Des Weiteren werden die Accounts das und system von der Verarbeitung ausgenommen. Allerdings würde auch ein invaliden Package ddl_util zu Problemen führen können.

Die eigentlich Logik des Triggers besteht dann nur noch im Aufruf der Prozedur ddl_util.lock_object. Diese prüft, ob das Objekt von einem anderen Entwickler zur Bearbeitung ausgescheckt wurde. In diesem Fall wird wie bereits erwähnt eine Exception gefeuert und so die Änderungen an dem Objekt dem aktuellen Nutzer verwährt. Bei Objekten, die noch nicht ausgescheckt sind, wird das über diesen Trigger automatisch erledigt.

Alternativ kann es aber auch durchaus sinnvoll sein, dass Ausschecken eines Objektes vor der Bearbeitung manuell durch das Aufrufen von  ddl_util.lock_object durchzuführen. So erspart man sich, dass Änderungen beim Speichern durch den Trigger erst im Nachhinein verworfen werden.

CREATE OR REPLACE TRIGGER trg_ddl_util_after 
    AFTER CREATE OR DROP 
 ON DATABASE 
DECLARE 
BEGIN
    IF ora_dict_obj_name NOT IN (
              'TRG_DDL_UTIL_BEFORE',
              'TRG_DDL_UTIL_AFTER',
              'DDL_UTIL')
    AND ora_login_user NOT IN (
              'SYS',
              'SYSTEM' )
    THEN
        IF ora_sysevent = 'CREATE' THEN
            ddl_util.lock_object(ora_dict_obj_type,ora_dict_obj_owner,ora_dict_obj_name);
       END IF;

       IF ora_sysevent = 'DROP' THEN
           ddl_util.unlock_object(ora_dict_obj_type,ora_dict_obj_owner,ora_dict_obj_name,false,true);
       END IF;

    END IF;
END;
/

Listing 7: Trigger trg_ddl_util_after

Wird nun ein neues Objekt angelegt, kann dieses nicht im Vorfeld geblockt werden. In so einem Fall kommt nun der zweite Trigger trg_ddl_util_after ins Spiel. Dieser prüft ob ein Create-Statement ausgeführt wurde. Wenn das der Fall war, wird das neu erstellte Datenbankobjekt automatisch ausgecheckt und der DDL-Extrakt archiviert.

Bei einem Drop-Befehl verhält es sich etwas anders. Hier existiert das Objekt zum Ausführungszeitpunkt ( AFTER ) des Triggers nicht mehr. Es muss aber noch aus der Tabelle ddl_objet_state entfernt werden.

Abbildung 1: Übersicht ddl_util

In Abbildung 1 sehen Sie alle beschriebenen Objekte.

Drop …

Jetzt habe ich der Einfachheit halber Ihnen noch ein kurzes Skript hier eingefügt, mit dem Sie die Tabellen, Trigger und das Package wieder los werden können.


DROP TRIGGER trg_ddl_util_before;
DROP TRIGGER trg_ddl_util_after;
DROP PACKAGE ddl_util;
DROP TABLE ddl_object_state;
DROP TABLE ddl_object_state_hist;

Beispiel

Im Folgenden möchte ich Ihnen die Nutzung der beschrieben Methode anhand einiger kleiner Beispiele demonstrieren. Dazu wurden in dem Schema MY_PROJECTS die beiden Tabellen, das Package und die beiden Trigger angelegt. diesen Account wurde eine Verbindung im SQL Developer hergestellt.

Dann gibt es einen weiteren Account namens DEVELOPER1, welcher mit DBA-Rechten versehen wurde. Der DEVELOPER1  ist via. sqlplus mit der Datenbank verbunden.

Tabelle T1 anlegen

Zu Beginn soll eine Tabelle erzeugt werden.

Abbildung 2: Tabelle T1 anlegen

Wie Sie der Abbildung 2 entnehmen können, wurde die Tabelle T1 ohne Probleme erzeugt. Dabei wurde der Trigger trg_ddl_util_after ausgeführt und ein Lock-Eintrag in der Tabelle ddl_object_state eingefügt.

Abbildung 3: Datenbanktabelle T1 für ausgecheckt

Tabelle T2 erweitern

Jetzt soll der DEVELOPER1 dieser Tabelle eine neue Spalte hinzufügen.

Abbildung 4 : Spalte in T1 ergänzen

Wie zu erwarten war, wird dieser Vorgang mit der Exception ORA-20001 durch den Trigger trg_ddl_util_before unterbunden. Der Meldung können Sie entnehmen, dass die Tabelle durch den User MY_PROJECTS am 17.06.2018 um 20:59:17 gesperrt wurde.

Abbildung 5: Objekt einchecken

Erst wenn der Benutzer MY_PROJECTS die Tabelle wieder zur Bearbeitung frei gibt, kann der DEVELOPER1 die Spalte ergänzen.

Abbildung 6: Spalte hinzufügen

Dies hat wiederum zu Folge, dass die Tabelle jetzt durch den User DEVELOPER1 ausgecheckt wurde.

Tabelle T1 löschen

Setzt nun der User MY_PROJECTS eine Drop Anweisung für die Tabelle T1 ab, wird auch diese mit einer entsprechenden Fehlermeldung abgebrochen.

Abbildung 7: Drop der Tabelle nicht möglich

Möchten Sie jetzt aber die Tabelle trotzdem löschen und die Sperre durch den DEVELOPER1 übergehen, so können Sie das durch den Aufruf der Prozedur unlock_objects mit folgenden Parametern tun.


BEGIN
  DDL_UTIL.UNLOCK_OBJECT(
         P_OBJECT_TYPE = 'TABLE',
         P_OWNER =       'MY_PROJECTS',
         P_NAME =        'T1',
         P_TRACE_HIST =  true,
         P_SECURE_MODE = false
        );
   commit;
END;

Änderungshistorie

Die Trigger bzw. das PL/SQL Package ddl_util sorgen beim Ein- und Auschecken der Datenbankobjekte auch dafür, dass ein DDL-Extrakt in der Tabelle ddl_object_state_hist archiviert wird. So können Sie auf einfache Weise auf ältere Entwicklungsstände zugreifen.

Abbildung 8: Änderungshistorie

Download

Abschließend habe ich Ihnen ein DDL-Script zum Anlegen der beschriebenen Objekte an diesen Beitrag angefügt. Die Verwendung erfolgt aber auf eigene Gefahr.

Oracle-Datenbank: Teamwork mit Bordmitteln Download ddl_util.sql

Dieses DDL-Sript ist im Rahmen des Beitrags Oracle-Datenbank: Teamwork mit Boardmitteln entstanden.

Oracle Datenbankentwicklung: DDL-Trigger zur Auditierung von Strukturänderungen

Oracle bietet mit sogenannten DDL-Triggern eine einfache Möglichkeit Änderungen an Datenbankobjekten zu protokollieren. Eine nützliche Methode für Entwicklerteams.

Eine automatische Auditierung von DDL (Data Definition Language) -Aktionen in einem Datenbank-Schema ist in vielerlei Situationen nützlich. Z.B. können Sie auf diese Weise in einer Entwicklungsumgebung alle Strukturänderungen, die nach einem bestimmten Zeitpunkt erfolgt sind, nachvollziehen. Auf Basis so eines Protokolls sind Sie dann beispielsweise in der Lage ein Upgrade-Skript zu erstellen, mit dem Sie anschließend das produktive System aktualisieren können. Auch aus Sicherheitsgründen kann es sinnvoll sein, strukturelle Änderungen an einer Datenbank zu protokollieren.

Das Thema der DDL- oder auch Systemtrigger ist umpfangreich, soll hier aber nicht in Gänze betrachtet werden. In diesem Beitrag geht es um die Entwicklung  eines Auditierungsmechanismusses, der alle Änderungen an einem Schema protokolliert. Das sind vor allem create, alter, drop Befehle.

Eine Oracle-Datenbank bietet verschiedene Möglichkeiten der Auditierung. Die hier vorgestellte Variante ist aber recht leichtgewichtig und bietet durch ihre Anpassbarkeit viele Gestaltungsmöglichkeiten. So können Sie beispielsweise auch eine DSGVO angepasste, datensparsame  Variante erstellen.

Die dargestellten Beispiele sind mit viel Sorgfalt entstanden, allerdings übernehme ich keine Gewähr für etwaige Fehler oder Probleme, die bei der Nutzung auftreten. Bitte prüfen Sie selber die Beispiele vor der Nutzung.

Datenmodell

Das Datenmodell für dieses Szenario besteht aus zwei Tabellen.

Die Tabelle ddl_log nimmt für jede Änderung der Datenbankobjekte deren Name und Besitzer, den Typ des Objektes, den Zeitpunkt der Änderung und die Art der Änderung auf. Auch die Benutzerdaten sowie das Programm, mit dem die Änderung durchgeführt wurde, werden vermerkt. Zur Erzeugung eines künstlicher Schlüssel wird später eine Sequence verwendet.

Die zweite Tabelle ddl_log_sql ergänzt diese Information um das eigentliche DDL-Statement, welches  ausgeführt wurde.

CREATE TABLE ddl_log
(
  event_id NUMBER (10, 0),
  event_date DATE,
  user_id VARCHAR2 (30 BYTE),
  object_name VARCHAR2 (30 BYTE),
  owner VARCHAR2 (30 BYTE),
  object_type VARCHAR2 (30 BYTE),
  system_event VARCHAR2 (30 BYTE),
  machine VARCHAR2 (64 BYTE),
  program VARCHAR2 (64 BYTE)
)
/

CREATE TABLE ddl_log_sql
(
  event_id NUMBER (10, 0),
  sqlline NUMBER (10, 0),
  sqltext VARCHAR2 (4000 BYTE)
)
/

CREATE SEQUENCE ddl_log_id INCREMENT BY 1
  START WITH 1
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  NOCYCLE
  ORDER
  CACHE 20
/

Listing 1: Datenmodell anlegen

Mit Listing 1 können Sie die beschriebenen Objekte anlegen. Dies sollten Sie in dem Schema tun, in dem Sie die Änderungen protokollieren möchten. Alternativ können Sie aber auch ein eigenes Schema dafür definieren. Allerdings müssen Sie dann den Schema-Namen im Quellcode des Triggers ergänzen und denn Tabellen sowie der Sequence voranstellen.

DDL-Trigger

Ein DDL-Trigger wird anders als ein DML-Trigger bei DDL- bzw. Systemereignissen ausgeführt. Das können z.B. Strukturänderungen wie das Erstellen einer Tabelle oder eines PL/SQL Packages, das Droppen einer Tabelle oder das Anlegen einer View aber auch das Anmelden eines Benutzers sein.

Der Ausführungspunkt so eines Triggers kann vor (BEFORE) oder nach (AFTER) der Abarbeitung des DDL-Statements festgelegt werden.

Sie können einen DDL-Trigger global für die komplette Datenbank (DATABASE) oder nur für ein bestimmtes SCHEMA definieren.

Einen DDL-Trigger, der alle Strukturellen Änderungen an Ihrem Schema protokolliert, können Sie – wie in Listing 2 gezeigt – erzeugen. Führen Sie dazu das Skript mit einem Datenbanktool (sqlplus, SQL Developer, APEX SQL Workshop,…) aus. Melden Sie sich dabei mit dem SCHEMA Account an.

Tauschen Sie das keyword SCHEMA durch DATABASE, so wird der Trigger bei allen Änderungen an der gesamten Datenbank gefeuert. Aber Achtung, Sie müssen dafür dann das Schema, in denen sich die verwendeten Tabellen befinden, in dem Trigger hinzufügen.


CREATE OR REPLACE TRIGGER trg_ddl_log
 AFTER DDL
 ON SCHEMA
DECLARE
  l_sql ora_name_list_t;
  l_id NUMBER (10, 0);
BEGIN
  SELECT ddl_log_id.NEXTVAL INTO l_id FROM DUAL;

  INSERT INTO ddl_log (event_id,
    event_date,
    user_id,
    object_name,
    owner,
    object_type,
    system_event,
    machine,
    program)
  (SELECT l_id,
          SYSDATE,
          ora_login_user,
          ora_dict_obj_name,
          ora_dict_obj_owner,
          ora_dict_obj_type,
          ora_sysevent,
          UPPER (SYS_CONTEXT ('USERENV', 'TERMINAL')),
          SYS_CONTEXT ('USERENV', 'MODULE')
       FROM DUAL);

FOR l IN 1 .. ora_sql_txt (l_sql)
  LOOP
    INSERT INTO ddl_log_sql (event_id, sqlline, sqltext)
      VALUES (l_id, l, l_sql (l));
  END LOOP;
END;
/

Listing 2: Create DDL-Audit-Trigger

Der Trigger wird nach allen Änderungen, die an Objekten des jeweiligen Schemas ausgeführt werden, gefeuert.

Zu Begin des Triggers wird mit Hilfe der Sequence ddl_log_id ein künstlicher Schlüssel erzeugt und in der Variable l_id gespeichert.

Das erste Insert-Statement protokolliert die Art der Änderung in der Tabelle ddl_log. Dabei wird dem Datensatz die zuerst erzeugte ID aus der Variablen l_id als Primärschlüssel zugeordnet. Hier werden neben dem Zeitpunkt der Änderung und das geänderte Datenbankobjekt, auch die Benutzerdaten und das verwendete Programm ermittelt und gespeichert.

Im nächsten Schritt wird das DDL-Statement, welches zur Ausführung des Triggers geführt hat, zeilenweise in der Tabelle ddl_log_sql eingefügt. Jeder Zeile wird neben einer Nummerierung auch die l_id als Fremdschlüssel zugeordnet.

Sollten Sie auf die persohnenpersonen Daten – wie den Benutzername und den Maschinennamen des Clients – verzichten wollen, können Sie den Trigger in einer DSGVO freundlicheren Version mit dem Listing 3 anlegen. Dabei werden diese beiden Spalten einfach mit null vorbelegt.


CREATE OR REPLACE TRIGGER trg_ddl_log
 AFTER DDL
 ON SCHEMA
DECLARE
  l_sql ora_name_list_t;
  l_id NUMBER (10, 0);
BEGIN
  SELECT ddl_log_id.NEXTVAL INTO l_id FROM DUAL;

  INSERT INTO ddl_log (event_id,
    event_date,
    user_id,
    object_name,
    owner,
    object_type,
    system_event,
    machine,
    program)
  (SELECT l_id,
          SYSDATE,
          null,
          ora_dict_obj_name,
          ora_dict_obj_owner,
          ora_dict_obj_type,
          ora_sysevent,
          null,
          SYS_CONTEXT ('USERENV', 'MODULE')
       FROM DUAL);

FOR l IN 1 .. ora_sql_txt (l_sql)
  LOOP
    INSERT INTO ddl_log_sql (event_id, sqlline, sqltext)
      VALUES (l_id, l, l_sql (l));
  END LOOP;
END;
/

Listing 3: DSGVO freundlicherer DDL-Audit-Trigger

Möchten Sie den Trigger inklusive der zugehörigen Datenbankobjekte aus Ihrem Schema entfernen, können Sie dafür das Listing 4 nutzen. Wichtig dabei ist die vorgegebene Ausführungsreihenfolge. Würden Sie die Tabellen zuerst zu droppen, so würden Sie dem DDL-Trigger seine abhängigen Objekte entziehen. Anschließend würde der Trigger invalid und zu einem Fehler führen.


DROP TRIGGER trg_ddl_log;
DROP TABLE ddl_log;
DROP TABLE ddl_log_sql;
DROP SEQUENCE ddl_log_id;

Listing 4: Trigger und Tabellen löschen

Generell sollten Sie bei der Verwendung von DDL-Triggern etwas Vorsicht walten lassen, da Sie bei fehlerhafter Verwendung, sich quasi bestimmter Datenbank-Funktionalitäten entledigen könnten.

Anwendungsbeispiele

Ich habe in dem Beitrag Oracle Application Express – Quick SQL bespielhaft ein einfaches Datenmodell für eine Projektverwaltung erstellt. Das zugehörige DDL-Skript habe ich exemplarisch ausgeführt und mit Hilfe des DDL-Audit-Triggers aus Listing 3 protokollieren lassen.

Im Folgenden finden Sie einige einfachen select-Anweisungen, mit deren Hilfe Sie verschiedene Fragestellungen beantworten können.

Wie Sie der Abbildung 1 entnehmen können, wurden folgende Änderungen an der Datenbank vorgenommen.

Abbildung 1: DDL-Log Änderungshistorie

Wenn Sie sich dieses Log ansehen möchten, genügt folgende select-Statement Anweisung:


select event_id,
       event_date,
       user_id,
       object_name,
       owner,
       object_type,
       system_event,
       machine,
       program
 from ddl_log
  order by event_id

Listing 5: Änderungshistorie anzeigen

Das konkrete DDL-Statement, dass für Anlegen der Tabelle projects verantwortlich war,  wurde in der Tabelle ddl_log_sql protokolliert. Eine kleine Unschönheit bringt dieses Protokoll mit sich. Bitte achten Sie darauf, dass die zeilenweise Speicherung teilweise mitten in einer SQL-Anweisung die Zeile umbricht. Dies muss bei der Rekonstruktion des Skriptes beseitigt werden.

Abbildung 2: DDL-Statement

Dieses läßt sich für einen einzelnen Schritt – wie in Listing 6 gezeigt – ermitteln.


select event_id, 
       event_date, 
       object_type,
       sqltext
from ddl_log_sql 
  where event_id = 2
 order by sqlline

Listing 6: DDL-Anweisungen für event_id =2 ermitteln

Interessieren Sie sich für die Änderungen,  die zwischen zwei Releaseständen Ihres Datenbank-Schemas  durchgeführt wurden, können Sie die select-Anweisungen um folgende where Klausel ergänzen. Alternativ können Sie auch die Einschränkungen über den Zeitstempel vornehmen.


select l.event_id, 
       event_date, 
       object_type,
       sqltext
from ddl_log l, 
     ddl_log_sql s
where l.event_id = s.event_id
  and l.event_id > 2 and l.event_id < 10
order by l.event_id, 
         sqlline

Listing 7: Alle DDL-Anweisungen die nach event_id 2 bis zur event_id 5 ausgeführt wurden

Auf Basis so eines Protokolls, können Sie anschließend ein Upgrade-Skript erarbeiten oder die Release-Notes für die neue Softwareversion ergänzen.

Auch die Änderungen an einem bestimmten Datenbankobjekt, hier der Tabelle TO_DOS, können nachvollzogen werden. Dazu finden Sie in Listing 8 ein einfaches Beispiel.


select l.event_id , 
       l.object_name, 
       event_date, 
       object_type,  
       sqltext
from ddl_log l, 
     ddl_log_sql s
where l.event_id = s.event_id
  and l.object_name = 'TO_DOS'
order by l.event_id, 
         sqlline

Listing 8: DDL-Statement zum Erstellen der Tabelle TO_DOS ermitteln

So einen DDL-Audit-Trigger ist gerade auch für Entwicklerteams hilfreich. Hier ist für alle Programmierer leicht nachvollziehbar, welche Änderungen an dem Datenmodell durchgeführt wurden. Auch zum prüfen von Installationsvorgängen lässt sich die Protokollierung einsetzen.

Wie Sie meine ScreenShots entnehmen konnten, setzte ich diesen Trigger auch gerne bei der Entwicklung von APEX-Anwendungen ein. Gerade durch die Beschränkung seiner Ausführung auf die Änderungen eines Datenbankschemas können so geziehlt alle Entwicklungsschritte auf Datenbankebene nachvollzogen werden. Auf Basis der Tabelle ddl_log_sql lassen sich dann Scripte für die Supported Objects erstellen.

 

 

 

 

Oracle Datenbank härten

Sehr viele Systeme im geschäftlichen Umfeld nutzt im Backend eine Datenbank. In diesem Beitrag möchte ich Ihnen einige Anregungen geben, wie Sie eine Oracle Datenbank sicher betreiben können.

In der Praxis eines Softwareentwicklers wird dessen Leistungsfähigkeit in der Regel an der Geschwindigkeit gemessen, mit der eine Anforderung umgesetzt wird. Dabei geht es meist um Funktionalität aber auch Aussehen einer Anwendung. Dem Komplex Sicherheit – ein zugegebener Maßen – lästiges und mitunter recht aufwendiges Thema wird meist eine nachgelagerte Rolle zugewiesen. Sicherheit wird immer dann aktuell, wenn es zu Problemen gekommen ist. Das müssen nicht immer spektakuläre Hacks sein, bei denen Kundendaten oder Kreditkarteninformationen entwendet werden. Bei fehlenden oder auch fehlerhaft vergebenen Berechtigungen können leicht Informationen in die falschen – wenn auch interne – Hände gelangen. Unterschätzen sie nicht den Wissensdurst der eigenen Leute.

Die erste Überlegung, die Sie zum Thema Sicherheit anstellen sollten, ist die Klassifizierung Ihrer Anwendung. Welche Verfügbarkeitsanforderungen muss das System gerecht werden? Wie brisant sind die Daten, die mit der Anwendung verwaltet werden? Ist die Anwendung für den internen Gebrauch gedacht, oder soll ein Zugriff über das Internet möglich sein? Werden personenbezogene Daten verarbeitet? Welche maximal tolerierbare Ausfallzeit können Sie in kauf nehmen? Sind gesetzliche Vorgaben und staatliche Vorschriften (Compliance) zu beachten? Welche Kosten und welcher Image-Verlust entstehen durch Kompromittierung?

Betrachten Sie immer das Gesamtsystem

Eine datenbankgestütze Anwendung benötigt mindestens einen Server, eine Datenbank und ein Frontend. Diese Komponenten werden in einer IT-Infrastruktur aus Netzwerk, Domaine, usw. betrieben.  Um ein sicheres Gesamtsystem zu erhalten, ist es notwendig alle beteiligten Komponenten zu betrachten und abzusichern.

Zu einer genaueren Risikoanalyse möchte ich Sie an dieser Stelle auf den IT-Grundschutz-Katalog des Bundesamtes für Sicherheit in der Informationstechnik aufmerksam machen. Sie finden auf der Web-Seite https://www.bsi.bund.de umfangreiche Informationen rund um das Thema Sicherheit.

  1. Je nach Sicherheitsbedürfnis des Systems müssen Sie verschiedene Maßnahmen ergreifen.

Server härten

Alle beteiligten Server eines Gesamtsystems – seien es Application Server, Web Server oder Datenbank-Server müssen gehärtet werden. Da eine Abhandlung dazu ganze Bücher füllen kann und natürlich auch von den verwendeten Systemen abhängt, beschränke ich mich hier auf eine Auflistung wichtiger Punkte und Schritte, die hier zu beachten sind.

  • Sie sollten immer eine Neuinstallation des Systems durchführen, wobei Sie ausschließlich originale Installationsmedien verwenden müssen. Widmen Sie keine bestehenden Server (Installationen) um.
  • Legen Sie sich eine Patch-Strategie fest. Automatisches Patchen ist nur bedingt zu empfehlen, da komplexere Systeme erst getestet werden müssen. Hier bieten sich auch automatische Testverfahren (z. B. Unit-Tests) an.
  • Nicht benötigte Dienste/Prozesse stellen ein unnötiges Gefahrenpotenzial dar. Betreiben Sie nur die Dienste, die auch wirklich benötigt werden. Dabei sollten Sie darauf achten, diese mit möglichst geringen Privilegien zu betreiben.
  • Verwenden Sie keine administrativen Accounts um Dienste zu betreiben. Jeder Dienst ist über einen eigenen User mit eigenem Kennwort auszuführen.
  • Löschen Sie nicht benötigten Accounts.
  • Verwenden Sie starke Passwörter, wobei Sie deren Komplexität über entsprechende Policies erzwingen sollten. Kennwörter für Benutzer sind regelmäßig zu ändern. Systemkennwörter sind entsprechend lang und komplex anzulegen. Überschreiben Sie jedes Default-Passwort. Auflistungen gängiger Accounts sind ganz einfach im Internet zu erhalten.
  • Beschränken Sie die Systemressourcen für Daten, die von außen in das System hereingetragen werden können. Dies gilt z. B. für Mails, hoch ladbare Dateien aber auch von Log-Files.
  • Prüfen Sie regelmäßig Log- und Tracefiles sowie Event-Log Einträge.
  • Verwenden Sie einen Virenscanner.
  • Lassen Sie nur die notwendige Kommunikation mit dem System zu. Sichern Sie den Zugriff über eine Firewall.
  • Begrenzen Sie administrative Zugriffe auf die benötigten Services. Lassen Sie den Zugriff nur von ausgewählten Rechnern (Admin-Konsolen) zu und begrenzen Sie den Personenkreis mit administrativen Rechten.
  • Beschränken Sie den physischen Zugriff auf dass System (Rechenzentrum abschließen).
  • Schützen sie das System mit einem BIOS-Passwort und evtl. mit einem Passwort für den Boot loader. Ändern Sie die Bootreihenfolge so, dass das System nicht von externen Medien gebootet werden kann.
  • Verwenden Sie sichere Dateisysteme und verschlüsseln Sie bei Bedarf auch die Festplatte.
  • Halten Sie die Systemdokumentation auf einem separaten System, so ersparen Sie es sich entsprechende Reader zu installieren und diese aktuell zu halten. Im Fehlerfall nützt Ihnen die beste Dokumentation nichts, wenn Sie nicht darauf zugreifen können.
  • usw. …

Härten der Datenbank

Haben Sie es zu einer sicheren Basis für die benötigten Server gebracht, können Sie sich um die Einrichtung der Datenbank kümmern. Den Grundsatz des Härtens eines Systems finden Sie auch hier wieder. Beschränken Sie sich auf das Notwendige und halten Sie das System aktuell.

Datenbankversion und Patchen

Oracle bietet für eine gewisse Zeit, Updates und Sicherheits-Patches für seine supporteten Produkte. Aber nach gewisser Zeit werden ältere Versionen aus dem Support genommen. Sicherheitslücken werden dann nicht mehr geschlossen.

Setzen Sie also für Ihre Systeme immer eine supportete möglichst aktuelle Datenbankversion ein.

Oracle veröffentlicht regelmäßig sogenannte Critical Patch Updates (CPU). Diese fixen die bis dahin bekannten Sicherheitslücken. Sie können sich auch proaktiv von Oracle per Mail über sicherheitsrelevante Themen benachrichtigen lassen. Sie sollten diese nach Prüfung auf einem Testsystem, möglichst zeitnah einspielen.

Vereinbaren Sie nach Möglichkeit mit den Nutzern Ihrer Anwendungen bestimmte Wartungsfenster, in denen Sie oder der entsprechende DBA diese Arbeiten erledigen kann.

Default-Accounts

Eine Oracle Standard-Installation bring diverse Accounts mit auf die mittels Default-Passwort zugegriffen werden kann. Recherchieren Sie mal im Internet danach, so finden Sie verschiedene Listen dieser Accounts. Es gibt diverse Scanner mit denen Sie eine Datenbank auf diese Benutzer prüfen können. Sie sollten – dem Minimalprinzip folgende – nach Möglichkeit die Accounts entfernen. Wenn das nicht möglich ist, können Sie noch den User deaktivieren oder mit einem unbekannten sicheren Passwort schützen.

Folgende Auflistung zeigt die gängigsten Benutzer und eine Handlungsempfehlung, wie mit Ihnen umzugehen ist.

Benutzer

Password

Nutzung

Löschen möglich

Passwort ändern

SYS

CHANGE_ON_INSTALL ODER INTERNAL

Oracle Data Dictionary

Nein

Ja

SYSTEM

MANAGER

Standard DBA

Nein

Ja

SYSMAN

CHANGE_ON_INSTALL

Administration aus Enterprise Manager

Nein

Ja

OUTLN

OUTLN

Administration der Tuning-Statistik

Nein

Ja

SCOTT

TIGER

Beispiele

Ja

Ja

ADAMS

WOOD

Beispiele

Ja

Ja

JONES

STEEL

Beispiele

Ja

Ja

CLARK

CLOTH

Beispiele

Ja

Ja

BLAKE

PAPER

Beispiele

Ja

Ja

HR

HR

Beispiele

Ja

Ja

OE

OE

Beispiele

Ja

Ja

SH

SH

Beispiele

Ja

Ja

DBSNMP

DBSNMP

Enterprise Manager

Nein

Ja

CTXSYS

CTXSYS

Text-Extender-Funktion

Nein

Ja

DIP

DIP

Administration der Directory Integration Platform (DIP)

Ja über

“oidprovtool”

Wenn DIP nicht mehr benötigt.

Ja über

“oidprovtool”

DMSYS

DMSYS

Data-Mining

Ja

Ja

EXFSYS

EXFSYS

Expression-Filter-Indizierung

Ja mittels catnoexf.sql

Ja

LBACSYS

LBACSYS

Label Based Access

Control owner

Ja

CSMIG

Database

Character Set Migration

Utility

Ja

Ja

Eine Übersicht der in der Datenbank vorhandenen Benutzer verschaffen Sie sich am besten über ein Admin- bzw. Datenbankentwicklungswerkzeug. Mit dem SQL-Developer können Sie sich die entsprechenden Benutzer anzeigen lassen.

Sie können Sich aber auch über den dba-View (select * from dba_users ) die entsprechenden Accounts auflisten lassen.

Seit Oracle11g können Sie in regelmäßigen Abständen über die dba-View DBA_USERS_WITH_DEFPWD prüfen, ob in Ihrer Datenbank User mit Standardpasswörten vorhanden sind.

Der SQL Developer bietet Ihnen auch die Möglichkeit benutze zu sperren, zu löschen und das Passwort zu ändern. Die folgende Abbildung zeigt, wie man den Benutzer HR sperren kann.

HR Account sperren

Über SQLPLUS lässt sich das über den Befehl ALTER USER “HR” PASSWORD EXPIRE ACCOUNT LOCK ; erledigen.

Mit dem Befehl alter user <Benutzername> identified by <Passwort>; können Sie einem Account ein neues Kennwort geben.

Der Befehl drop user username cascade; entfernt den Benutzer aus der Datenbank. Der Postfix cascade sorgt dafür, dass auch alle Objekte (Tabellen, Packages, …) welche sich im Besitz dieses Users befanden ebenfalls entfernt werden.

Berechtigungen

Haben Sie nun alle überflüssigen Accounts gesichert, so können Sie sich den Berechtigungen der verbliebenen einmal näher ansehen.

Grundsätzlich gilt – gehen Sie sparsam mit den Berechtigungen um.

Oracle unterscheidet verschiedene Arten von Rechten.

Über Systemberechtigungen wird in Oracle Datenbanken gesteuert, welcher User welche DDL-Befehle ausführen darf. Eine Übersicht der aktuell erteilten Privilegien liefert Ihnen folgendes Statement.

select * from dba_sys_privs;

Seien Sie vor allem sehr sparsam mit der Vergabe von „ANY“ Privilegien.

select * from dba_sys_privs where privilege like ‘%ANY%’

Objektberechtigungen steuern, welche DML-Befehle ein User für ein bestimmtes Datenbank-Objekt ausführen darf.

select * from dba_tab_privs; zeigt alle vergebenen Objektberechtigungen.

Noch granularer lassen sich die Zugriffe über sogenannte Spaltenberechtigungen vergeben.

select * from dba_col_privs;

Rechte warden über den Befehl GRANT <Recht> TO <Benutzer> ergeben und mit REVOKE <Recht> FROM <Benutzer> entzogen.

Rechte können auch zur Gruppen – sogenannten Rollen – zusammengefasst werden.

select * from role_sys_privs;

select * from role_tab_privs;

Diese Rollen werden dann wieder als Recht einem User zugewiesen.

Rollen wiederum können auch anderen Rollen zugeordnet werden, so das eine verschachtelte Struktur entsteht. Eine Übersicht liefert

select * from role_role_privs; .

Um nun zu ermitteln, welcher Benutzer welche Rolle(n) besitzt, führen sie folgende Anweisung aus. select * from dba_role_privs

In der Praxis spielt oft die Zeit für eine Lösung eine erhebliche Rolle, wodurch es recht häufig dazu kommt, dass einem User zu viele Rechte oft über mächtige Rollen (DBA) zugewiesen werden. Diese Rechte ermächtigen den Benutzer dann schnell mit seiner Arbeit fortfahren zu können, stellen aber ein erhebliches Risiko dar.

Überprüfen Sie regelmäßig die Berechtigungen und schränken Sie diese auf das Nötige ein.

select * from dba_role_privs where granted_role =’DBA’

Optionen und Features

Oracle bietet für seine Datenbanken ein sehr umfassendes Spektrum an Funktionen. Viele diese Möglichkeiten werden zu sogenannten Optionen zusammengefasst, die dann meist auch lizenzrechtlich zusätzlich betrachtet werden müssen. Auch wenn die Frage der Lizenzierung geklärt ist, sollten Sie sich auf die notwendigen Optionen für Ihr System beschränken. Jede Option bietet eine potenzielle Schwachstelle und muss bei Nutzung natürlich auch entsprechend in die Patch-Strategie mit einbezogen werden.

Die View V$OPTION (select * from V$OPTION) liefert Ihnen einen Überblick der Installierten Optionen und Features. Features sind Bestandteile der entsprechenden Datenbank-Version, während Optionen auch noch manuell hinzugefügt werden können. Über select * from DBA_REGISTRY können Sie alle installierten Optionen sichten.

Wenn Sie sich unsicher sind, welche Features von Ihrer Anwendung verwendet werden, können Sie sich über select * from DBA_FEATURE_USAGE_STATISTICS das entsprechende Nutzungsverhalten ansehen.

Im Verzeichnis $ORACLE_HOME/rdbms/admin finden Sie für die meisten Fälle die entsprechenden Skripte zum Deinstallieren. Hier hilft die Lektüre der entsprechenden Systembeschreibungen von Oracle.

Gesicherte Kommunikation – Oracle LISTENER

Um mit einer Oracle Datenbank über ein Netzwerk in Kontakt treten zu können, wird auf Server-Seite der sogenannte LISTENER benötigt. Der LISTENER ist ein Prozess, welcher auf einem TCP/IP Port – Default ist hier 1521 – lauscht und bei Anforderung durch den Client eine Verbindung zur Datenbank aufbaut. Die Konfiguration des LISTNERS erfolgt über die Datei listener.ora, welche Sie im Verzeichnis $ORACLE_HOME/network/admin finden.

01 […]

02 LISTENER =

03 (DESCRIPTION_LIST =

04 (DESCRIPTION =

05 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

06 (ADDRESS=(PROTOCOL=TCP)(HOST=<SERVER_NAME>)

07 (PORT= 1521))

08 )

09 )

10

11 DEFAULT_SERVICE_LISTENER = (XE)

12 […]

LISTENER.ORA ohne Anpassungen

Der LISTENER bietet also einen entsprechenden Angriffspunkt für einen Hacker. Der erste Schritt um es einem Angreifer zu erschweren, sich diesen Prozess zunutze zu machen ist es, den vorgegebenen Namen LISTENER zu ändern. Tragen Sie dazu wie in Zeile 02 des folgenden Konfigurations-Files apxlsn ein.

Darüber hinaus sollte niemals der Standard-Port 1521 verwendet werden. Hier können Sie einfach einen freien Port zwischen 1024 und 65535 wählen. In diesem Beispiel lautet der Port 2012.

Oracle Datenbanken bieten die Möglichkeit, externe Prozeduren aus Klassenbibliotheken (*.DLL) über den LISTENER zugänglich zu machen. Diese können dann per PL/SQL verwendet werden. Erhält nun ein Angreifer Zugriff auf die Datenbank, könnte er über diesem Weg direkt auf den Server zugreifen. Dies geschieht dann mit den Berechtigungen der Datenbankprozesse. Können Sie in Ihrer Anwendung auf diese Möglichkeit verzichten, so sollten Sie die Zeile 05 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) aus der Konfiguration entfernen.

01 […]

02 apxlsn =

03 (DESCRIPTION_LIST =

04 (DESCRIPTION =

05 (ADDRESS = (PROTOCOL = TCP)(HOST = <SERVER_NAME>)

06 (PORT = 2012))

07 )

08 )

09

10 DEFAULT_SERVICE_LISTENER = (XE)

11 CONNECTION_RATE_apxlsn = 5

12 LOCAL_OS_AUTHENTICATION_apxlsn = ON

13 […]

Gehärtete Version der LISTENER.ORA

In Zeile 11 wird über das setzten der connection_rate festgelegt, dass maximal 4 Verbindungen pro Sekunde zur Datenbank neu aufgebaut werden dürfen. Hiermit können Sie einem DoS (Denial of Service)–Angriff entgegenwirken.

Über die Zeile 12 wird verhindert, dass der LISTENER remote beendet werden kann. Ein DBA muss sich also mit entsprechenden Rechten direkt am Server anmelden, um dies zu bewerkstelligen. Also richten Sie entsprechende Zugangsbeschränkungen auf Seite des Betriebssystems ein.

Speichern Sie die Änderungen und führen, dass über die Kommandozeile den Befehl lsnrctl stop und anschließend lsnrctl start  aus. Hierdurch wird der vorhandene LISTENER beendet und die angepasste Version gestartet. Unter Windows finden Sie anschließend einen neuen Dienst mit dem Namen des LISTENERs.

Den aktuellen Status des LISTENERs können Sie sich mit lsnrctl stat <name> ansehen.

Um die Kommunikation zwischen dem LISTENR und der Datenbank korrekt wiederherzustellen, müssen Sie noch über SQLPLUS direkt auf dem Server folgendes Statement absetzen. SQLPLUS können Sie wie folgt starten: sqlplus system as sysdba. Führen Sie dann folgende Anweisung aus.

ALTER SYSTEM SET local_listener = ‘(address=(protocol=tcp)(host=<SERVER_NAME>)(port=2012))’ SCOPE=both;

Im nächsten Schritt geht es nun daran, den Zugriff auf den LISTENER und damit auf die Datenbank auf einen exklusiven Kreis einzuschränken.

Neben der Datei listener.ora finden Sie im Verzeichnis $ORACLE_HOME/network/admin eine weitere Konfigurationsdatei namens sqlnet.ora. Diese Datei finden Sie auch auf der Client-Seite.

01 SQLNET.AUTHENTICATION_SERVICES = (NONE)

02 SQLNET.INBOUND_CONNECT_TIMEOUT = 6

03

04 TCP.VALIDNODE_CHECKING = YES

05 TCP.INVITED_NODES = <IP_WEB_SERVER>, 192.168.178.*

06

07

08 sqlnet.encryption_server = required

09 sqlnet.encryption_types_server = AES256

10 sqlnet.encryption_client = required

11 sqlnet.encryption_types_client = AES256

12

13 sqlnet.crypto_checksum_server = required

14 sqlnet.crypto_checksum _types_server = MD5,SHA1

15 sqlnet.crypto_checksum_client = required

16 sqlnet.crypto_checksum _types_client = MD5,SHA1

Gehärtete Version der SQLNET.ORA auf dem Server

In Zeile 01 sollten Sie den Standardeintrag SQLNET.AUTHENTICATION_SERVICES = (NTS) auf NONE setzen. Sonst ist es einem User, welcher zugriff auf das Betriebssystem hat und Mitglied in der lokalen Benutzergruppe ORA_DBA ist, möglich sich über sqlplus / as sysdba ohne Angabe eine Passwortes an der Datenbank mit DBA-Rechten anmelden kann.

Durch den Eintrag in Zeile 02 wird der LISTERNER dazu gebracht, dass er maximal sechs Sekunden für den Aufbau einer Verbindung für einen Client zu Datenbank aufwenden soll. In Kombination mit dem Parameter CONNECTION_RATE_apxlsn = 5 aus der LISTENER.ORA wird dies DoS-Attacken entgegen.

Über die Zeilen 04,05 beschränken Sie den Zugriff auf die Datenbank auf die Clients, welche Sie mithilfe des Parameters TCP.INVITED_NODES festlegen können. In dem eingangs geschilderten Konstrukt, sollte es genügen den Zugriff auf den Web-Server und die internen Clients zu beschränken.

Ein weiterer Schachpunkt bei der Normalkonfiguration ist der Netzwerkverkehr. Daten und Befehle werden unverschlüsselt übertragen, sodass über einen einfachen Netzwerk-Sniffer diese leicht abgefangen werde können. Dies umfasst u.a. auch die Benutzernamen und Passwörter, welche zur Anmeldung an der Datenbank übertragen werden müssen. Abhilfe können Sie durch die Nutzung vorn verschiedenen Verschlüsselungsverfahren schaffen. Durch die Zeilen 08/09 wird auf Serverseite, dass die Kommunikation mit AES256 verschlüsselt wird. Auf Clientseite muss ebenfalls die Datei sqlnet.ora wie auch hier in Zeile 10/11 angegeben werden, da sonst keine Verbindung zustande kommen kann. Die Zeilen 10 und 11 sollten Sie auch auf dem Datenbankserver eintragen, da eine Datenbank – z. B. über Datenbanklinks – auch als Client fungiert. Oracle bietet folgende Verschlüsselungsverfahren an.

  • AES256: Advanced Encryption Standard (AES), Nachfolger von DES und 3DES, basiert auf dem Rijndael-Algorithmus.
  • AES192: Advanced Encryption Standard (AES), mit einer Blockgröße von 192 bits.
  • AES128: Advanced Encryption Standard (AES), mit einer Blockgröße von 128 bits.
  • RC4_256, RC4_128, RC4_40: Rivest Cipher 4 (RC4), Stream-Verschlüsselung, wird z. B. bei folgenden Protokollen eingesetzt HTTPS, SSH 1 und WEP bzw. WPA.
  • 3DES168, 3DES112: Triple Data Encryption Standard (TDES) , nicht mehr sicher genug
  • DES, DES40: Data Encryption Standard (DES) 56-bit Schlüssel, nicht mehr sicher genug

In Zeile 13 bis 16 wird noch ein Verfahren festgelegt, über welches die übertragenen Daten signiert werden. Hierdurch können Sie einem Man-in-the-middle-Angriff (MITM-Angriff) entgegenwirken, wobei ein potenzieller Angreifer die übertragenen Datenpakete abfangen und manipuliert weiterleiten möchte.

Jetzt müssen Sie nur noch die tnsnames.ora auf dem Server und auf allen Clients anpassen, de auf die Datenbank zugreifen sollen. Die notwendigen Modifikationen stellen sich recht moderat dar. Ändern Sie einfach den Port auf 2012 und speichern Sie die Datei in dem Verzeichnis $ORACLE_HOME/network/admin.

01 XE =

02 (DESCRIPTION =

03 (ADDRESS = (PROTOCOL = TCP)(HOST = <SERVER_NAME>)

04 (PORT = 2012))

05 (CONNECT_DATA =

06 (SERVER = DEDICATED)

07 (SERVICE_NAME = XE)

08 )

09 )

Beschränken Sie über das Betriebssystem den Zugriff auf das Verzeichnis $ORACLE_HOME und seine untergeordneten Ordner auf einen eingeschränkten Kreis von Administratoren.

Achtung! Vergessen Sie nicht den technischen User, über den die Datenbankinstanz ausgeführt wird ebenfalls zu berechtigen.

Je nach Konfiguration des SQL-Developers müssen Sie auch hier den neuen Port für den Verbindungsaufbau berücksichtigen.