Importieren Sie Ihre Online Banking-Daten aus MySQL bzw. MariaDB mit Bordmitteln nach Excel.
Der erste Teil dieses Tutorials beschäftigte sich vor allem mit der Abholung der Daten Ihrer Bankkonten unter Zuhilfenahme des open Source Online-Banking Tools hibiscus und der exemplarischen Auswertung der Daten via. SQL. Hibiscus bietet die Möglichkeit, über HBCI Kontodaten bzw. Kontobewegungen von den Banken abzurufen und diese in einer relationalen Datenbank zu speichern.
Im zweite Teil des Tutorial Hibiscus 2 Excel geht es um das regelmäßige Übertragen Ihrer Onlinebanking Daten nach Excel. Microsoft Excel bietet dafür einige Möglichkeiten, die Sie in der Ribbonbar unter Daten finden. So können Sie Daten z.B. aus CSV-Files, anderen Excel Dokumenten oder Access-Datenbanken importieren. Da Hibiscus die Möglichkeit bietet MySQL bzw. MariaDB zur Verwaltung der Bankdaten zu verwenden, möchte ich in diesem Tutorial den Import aus MariaDB über einen ODBC-Treiber demonstrieren.
MariaDB – ODBC Treiber
Bei MariaDB handelt es sich um einen Fork von MySQL. MariaDB ist eine relationale Datenbank, die Ihre Daten verteilt in Tabellen speichert. Um auf diese Daten zuzugreifen, gibt es verschiedene Möglichkeiten. In diesem Beispiel soll der Zugriff über einen ODBC-Treiber erfolgen. Diesen könne Sie hier für MariaDB herunterladen und anschließend installieren.
Abbildung 01: ODBC Data Source
Um aus Excel heraus auf eine MariaDB via. ODBC-Verbindung zugreifen zu können, müssen Sie eine Verbindung konfigurieren. Öffnen Sie dazu die APP ODBC Data Sources (32bit).
Im Register Benutzer-DSN können Sie einen neue Datenquelle Hinzufügen.
Abbildung 02: Datenquelle hinzufügen
Zuerst vergeben Sie einen Namen (hier hibiscus). Klicken Sie auf Next > .
Abbildung 03: ODBC Verbindung
Anschließend geben Sie noch den Server-Namen an, auf dem Sie Ihre Datenbank betreiben. Bei der aktuellen MariaDB Version erfolgt im Standard die Verbindung über den TCP/IP-Port 3307. Je nach Konfiguration der Datenbank (z.B. SSL) sind noch weitere Parameter zu definieren. In der Regel können Sie über Next > Next > Finish die Einrichtung abschließen.
Sie sollten (siehe Abbildung 03) bei der Einrichtung durch Eingabe des Datenbankbenutzers und Passworts + Button Test DSN die Verbindung prüfen. Dies reduziert die spätere lästige Fehlersuche.
Daten in Excel importieren
In Excel können Sie Daten aus MariaDB (oder auch einer einer anderen ODBC Datenquelle) importieren.
Abbildung 04: Excel – Daten abrufen
Dazu navigieren Sie in das Register Daten und klicken dort auf Daten abrufen. Wählen Sie in dem DropDown-Menü Aus anderen Quellen > Aus ODBC.
Abbildung 05: ODBC-Datenquelle wählen
Als nächstes selektieren Sie die ODBC-Datenquelle, die Sie anfangs angelegt haben. in diesem Beispiel “hibiscus”. Weiter geht es mit OK. Geben Sie Ihre Zugangsdaten für die Datenbank ein.
Abbildung 06: Tabellen bzw. Views
Wenn Sie die Verbindung korrekt angelegt haben, sollten Sie eine Übersicht alles Tabellen und Views Ihrer Datenbank sehen. Wählen Sie z.B. die Tabelle konto.
Der Wizzard zeigt Ihnen nun eine Übersicht des Tabelleninhaltes. Über den Button Laden können Sie die Daten in die aktuell gewählte Excel Tabelle importieren. Zusätzlich wird die Abfrage in der Excel Datein gespeichert.
Daten aktualisieren
Sie können über verschiedene Wege den Importvorgang wiederholen, so dass die Daten in Ihrer Excel-Datei aktualisiert werden.
Abbildung 07: Daten aktualisieren
Im Register Daten finden Sie den Eintrag Alle aktualisieren. Über diesen werden alle, in Ihrer Excel Datei enthaltenen Abfragen, neu ausgeführt und die Daten erneut eingefügt.
Über die Schaltfläche Abfragen und Verbindungen können Sie aber auch einzelne Abfragen gezieht ausführen und diese verwalten.
Auswertung
Zur Auswertung Ihrer Daten stehen Ihnen nun alle Möglichkeiten von Excel zur Verfügung. Sie können mit den Daten rechnen, Sie in Pivot-Tabellen analysiere oder mit Hilfe von Diagrammen visualisieren.
Datenbank-Views
Wie Sie eben sehen konnten, wurden Ihnen nur Tabellen und sogenannt Datenbank-Views zum Import angezeigt. Die im ersten Teil des Tutorials entwickelten Select-Statements können Sie nicht direkt eingeben.
Eine Möglichkeit komplexere Abfragen verwenden zu können bieten Datenbank-Views. Eine View ist eigentlich nichts anderes als ein in der Datenbank gespeichertes Select-Statement. Um z.B. die Umsätze Ihrer Konten in Excel auswerten zu können, legen Sie eine entsprechende View in der Datenbank (z.B. über HeidiSQL) an.
CREATE VIEW V_UMSAETZE AS
SELECT k.id,
k.kontonummer,
k.blz,
k.bezeichnung,
u.empfaenger_konto,
u.empfaenger_blz,
u.empfaenger_name,
u.zweck,
u.zweck2,
u.betrag,
u.saldo,
u.datum,
u.art
FROM konto k, umsatz u
WHERE u.konto_id = k.id
Quellcode 01: View V_Umsaetze
Diese View können Sie dann wie oben beschrieben in Excel importieren.
Hibiscus ist eine freie Online-Banking Software. In diesem Beitragsreihe geht es um eine Schnittstelle zu Excel als Alternative zu Money in Excel und die Analyse Ihrer Finanzdaten. Über diesen Weg lässt sich das eigene Haushaltsbuch automatisieren.
Beschäftigt man sich mit mit den eigenen Finanzen, findet man auf diversen Finanzblogger-Seiten – hier mit frugalisten.de, finanzfluss.de mal einige interessante Beiträge zu dem Thema – immer wieder den Ratschlag, ein Haushaltsbuch zu führen. . Oft sind sich die Blogger und auch die Kommentatoren einig, dass ein Haushaltsbuch die Mühe Wert ist, aber eben auch einiges an Mühe und Disziplin kostet, es zu führen. In sehr vielen Fällen läuft es auf eine Lösung in Excel oder einer anderen Tabellenkalkulation heraus. Auch bieten gerade fintechs und mittlerweile auch Banken für Ihre Kunden Analysefunktionen der Kontobewegungen.
In den folgenden zwei Beiträgen möchte ich Ihnen einen Open Source Ansatz zeigen, mit dem Sie eine ähnliche Funktionalität wie Money in Excel selber nachbilden können. Hierzu dient das Open Source Online Banking Tool hibiscus. Es fungiert als Schnittstelle zu Ihren Bankdaten. Wie Sie diese Daten dann nach Excel transportieren können, wird Thema des zweiten Beitrags werden.
Money in Excel
Microsoft bietet mit Money in Excel eine interessante Erweiterung von Microsoft 365 an. Die neue Lösung, die bisher nur in der US Version verfügbar ist, kommt als Template mit einem AddIn daher. Money in Excel bietet die Möglichkeit Konto- und. Kreditkartendaten in Excel zu importieren. Die Excel Vorlage bringt diverse Darstellungen und Auswertungen der eigenen Finanzdaten mit. So können Konten konsolidiert, regelmäßige Ausgaben ermittelt und in entsprechenden Charts dargestellt werden. Abgerufene Daten werden in sogenannten Snapshots eingefroren.
Das interessante an dieser Lösung ist, dass man die Auswertungen selber beliebig erweitern kann. Wer sich einen erste Eindruck zu Money in Excel verschaffen möchte, findet hier eine entsprechenden Artikel von Microsoft.
Microsoft bedient sich für den Import der Kontodaten der Dienste von Plaid. All denen , die Ihre Daten nicht über Plait abholen lassen möchten oder nicht auf die Veröffentlichung von Money in Excel in unseren Gefilden warten möchten, möchte ich hier eine alternative Lösung, basierend auf dem Open Source Online Banking Tool hibiscus, zeigen.
Hibiscus – Open Source Online Banking
Olaf Willhun initiierte mit seinem unter der GPL lizenzierten hibiscus, einem AddIn zu Jameica, ein Open Source Online-Banking Tool. Hibiscus bietet die Möglichkeit, vor allem über HBCI, Kontodaten abzurufen und Überweisungen sowie Lastschriften zu tätigen.
Hibiscus bringt ein eigenes Reporting mit. Es sind zeitliche Verläufe von Kontoständen sowie Einnahmen und Ausgaben Übersichten möglich. Eine sehr gelungene Funktion ist auch die automatisierte Kategoriezuordnung der einzelnen Buchungen. Dazu können Suchbegriffe und sogar Reguläreausdrücke verwendet werden. Kategorien können hierarchisch organisiert und über diese Hierarchie aggregiert ausgewertet werden. So ist es z.B. möglich, alle Ausgaben bei verschiedenen Discountern zu einer Kategorie Lebenshaltung zusammenzufassen.
Hibiscus bietet die Möglichkeit, die Kontobewegungen als CSV-Datei zu exportieren. Dies wäre ein einfacher Ansatz, um die Kontodaten in Excel weiter zu bearbeiten. Allerdings bedarf es dabei immer noch des manuellen Abgleichs.
Eine andere Möglichkeit, die Daten in einem eigenen Reporting respektive Excel-Haushaltsbuch für sich nutzbar zu machen, besteht darin, diese direkt aus der Datenbank zu lesen und in Excel zu importieren.
Hibiscus auswerten
Hibiscus nutzt in der Standard – Version der Anwendung eine dateibasierte H2 Datenbank. Es ist aber auch möglich, die Daten in einer eigenen MySql oder MariaDB – Datenbank zu speichert. Wie das geht, ist hier auf der Entwicklerseite beschrieben.
Nachdem Sie über hibiscus Ihre Bankverbindungen eingerichtet und die zugehörigen Kontobewegungen abgerufen haben, können Sie auf diese Daten über ein Datenbanktool wie z.B. HeidiSQL oder PhPMyAdmin zugreifen.
Abbildung 01 zeigt die Tabellen der aktuellen hibiscus – Version.
Kontostand und Umsatz
Den aktuellen Kontostand finden Sie beispielsweise in der Tabelle Konto.
SELECT k.id,
k.kontonummer,
k.blz,
k.bezeichnung,
k.saldo,
k.saldo_datum
FROM konto k
Quellcode 01: Aktueller Kontostand
Über die Tabelle umsatz, lassen sich alle Kontobewegungen ermitteln. Zur besseren Übersichtlichkeit, kann man die Tabelle konto zu den Umsätzen joinen.
SELECT k.id,
k.kontonummer,
k.blz,
k.bezeichnung,
u.empfaenger_konto,
u.empfaenger_blz,
u.empfaenger_name,
u.zweck,
u.zweck2,
u.betrag,
u.saldo,
u.datum,
u.art
FROM konto k, umsatz u
WHERE u.konto_id = k.id
Quellcode 02: Umsätze und Konto
Abbildung 02 zeigt, wie das Ergebnis dieser Abfrage aussehen könnte.
Abbildung 03: Umsätze
Wie bereits oben erwähnt, bietet hibiscus die Möglichkeit, Umsätze automatisch einer Kategorie zuzuordnen. Um diese Kategorien auszuwerten, können Sie das vorhergehende Select-Statement um einen weiteren Join zur Tabelle umsatztyp ergänzen. Sollten Sie in hibiscus nicht alle Umsätze einer Kategorie zugeordnet haben, ist hier ein outer join notwendig.
SELECT k.id,
t.name AS kategorie,
k.kontonummer,
k.blz,
k.bezeichnung,
u.empfaenger_konto,
u.empfaenger_blz,
u.empfaenger_name,
u.zweck,
u.zweck2,
u.betrag,
u.saldo,
u.datum,
u.art
FROM konto k,
umsatz u left join umsatztyp t ON u.umsatztyp_id = t.id
WHERE u.konto_id = k.id
AND year(u.datum) = YEAR(NOW())
Quellcode 03: Umsatz nach Kategorien
Das Ganze sieht dann in etwa so aus.
Abbildung 04: Umsatz mit Kategorie
Via SQL können Sie natürlich eine Vielzahl an Analysen durchführen. Ich möchte Ihnen aber abschließend nur noch zwei kleine Beispiel zeigen.
Umsatz nach Kategorie
Wenn Sie ermitteln möchten, welche Beträge Sie in Abhängigkeit einer Kategorie erzielt bzw. ausgegeben haben, können Sie ausgehende von dem oben entwickelten Select-Statement einfach eine Gruppierung (group by …) nach der Kategorie (hier t.name) durchführen und die Betrag-Spalte summieren. Mit MIN(DATUM) bzw. MAX(DATUM) ermitteln Sie noch den Zeitraum, in dem die Buchungen durchgeführt wurden. Das Ganze grenzen Sie dann noch über eine entsprechende WHERE – Klausel – hier mit year(datum ) = YEAR(NOW()) auf die Umsätze des aktuellen Jahres – ein.
SELECT year(u.datum) AS jahr,
nvl(t.name,'?') AS kategorie,
MIN(u.datum) AS von,
MAX(u.datum) bis,
SUM(u.betrag) AS sum_betrag
FROM umsatz u left join umsatztyp t ON u.umsatztyp_id = t.id
where year(datum ) = YEAR(NOW())
group BY nvl(t.name,'?')
Quellcode 04: Auswertung nach Kategorie
Durch eine geschickte absteigende Sortierung nach dem summierten Betrag, sehen Sie sofort, in welche Kategorien Sie die größten Summen bewegen.
Gruppieren Sie die Daten neben der Kategorie noch nach dem aktuellen Monat (… GROUP BY nvl(t.name,’?’), MONTH(datum) ), so können Sie sich die Beträge je Kategorie pro Monat anzeigen lassen.
SELECT MONTH(datum) AS monat,
t.name kategorie,
MIN(datum) AS von,
MAX(datum) bis,
SUM(betrag) AS sum_betrag
FROM umsatz u left join umsatztyp t ON u.umsatztyp_id = t.id
where year(datum ) = YEAR(NOW())
group BY
nvl(t.name,'?'),
MONTH(datum)
ORDER BY 1
Quellcode 05: Monatsauswertung nach Kategorie
Wiederkehrende Umsätze
In einem anderen Szenario geht es jetzt darum, herauszufinden, welche regelmäßigen Ausgaben Sie haben. ein erster Ansatz könnte so aussehen.
SELECT COUNT(*) AS anzahl_buchungen,
u.empfaenger_konto,
u.empfaenger_name,
MIN(datum) AS von,
MAX(datum) bis,
MAX(MONTH(datum)) AS anzahl_monate,
SUM(betrag) AS sum_betrag
FROM konto k, umsatz u
WHERE u.konto_id = k.id
AND year(datum ) = YEAR(NOW())
GROUP BY
u.empfaenger_konto,
u.empfaenger_name
HAVING COUNT(*) > 1
ORDER BY 1 desc
Quellcode 06: Anzahl Buchungen
Mit dem Select- Statement aus dem Quellcode 06 gruppieren Sie Ihre Umsätze nach dem Empfängerkonto und zählen einfach die Anzahl der Buchungen. Ist die Anzahl größer als 1 ( HAVING COUNT(*) > 1 ) , wird es sich evtl. um eine wiederkehrende Buchung handeln.
Werten Sie, wie in diesem Beispiel nur ein Jahr aus, so können Sie mit MAX(MONTH(datum)) die Anzahl der Monate in denen es Buchungen gibt, ermitteln. Vergleichen Sie die Anzahl der Buchungen mit der Anzahl der Monate, sehen Sie grob, ob sich eine Buchung mehrmals pro Monat wiederholt. Auch über MIN(datum) bzw. MAX(datum) können Sie Rückschlüsse ziehen und sehen in welchem Zeitraum die Buchungen aufgetreten sind.
Sie können durch geschicktes Sortieren – z.B. absteigend nach dem Betrag (ORDER BY SUM(betrag) desc ) – sich auch erst um die großen Posten kümmern. Oder Sie schauen sich die vielen kleinen (HAVING COUNT(*) > 12) Buchungspositionen an.
Zwischenbilanz
Ich hoffe, dass die wenigen Beispiele hier, bereits deutlich machen, welche Informationen Ihre ganz persönlichen Daten hergeben. Fängt man erst einmal damit an, sich mit der Auswertung der Daten zu beschäftigen, fallen einem Dinge auf, die im Tagesgeschäft schnell untergehen. Seien es ungekündigte Verträge oder sich wiederholende Kleinstausgaben, die sich aber über die Zeit zu anschaulichen Beträgen summieren.
Sie können über SQL-Statements auch die Sparquote je Monat berechnen und sich die Kategorien mit den höchsten Umsätzen und damit verbunden mit dem größten Sparpotential ansehen.
Aber auch andere Erkenntnisse sind möglich. Wie oft geht man pro Woche/Monat zum Discounter? Wie oft – und vielleicht wie schnell – wird eine Bestellung bei amazon und Co. platziert? Was kostet im Jahr ein Auto ? (Versicherung, Reparaturen, Steuern, Tanken,..)
Welche Schlüsse Sie letztendlich aus diesen Informationen ziehen, bleibt natürlich Ihnen überlassen. Tipps und Diskussionen dazu finden Sie aber sicherlich in den entsprechenden Finanzblogs.
Wie geht es weiter?
Im zweiten Teil dieser Reihe, wird es – wie im Titel angekündigt – darum gehen, wie Sie die (eben) gewonnenen Daten mit Bordmitteln nach Excel automatisch und reproduzierbar übertragen können. Es ist natürlich auch möglich, die Daten in einem anderen Reporting-Tool auszuwerten. Allerdings ist der Aufwand, dies zu bewerkstelligen recht groß. Excel bietet so viele Möglichkeiten Daten auszuwerten und zu visualisieren, dass es sich im privaten Umfeld einfach anbietet Excel zu verwenden.
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.
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.
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.
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.
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
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.
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.
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.
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 Oracle11gkönnen Sie in regelmäßigen Abständen über diedba-View DBA_USERS_WITH_DEFPWDprü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.
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.
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.
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.
Diese Website benutzt Cookies. Wenn Sie die Website weiter nutzt, gehen wir von Ihrem Einverständnis aus.
Weitere Informationen zu der Nutzung von Cookies finden Sie in unserer Datenschutzerklärung.OKDatenschutzerklärung
