Zugriffe prüfen
Nachverfolgen, wer was sehen darf
Zugriffe prüfen ist eine kostenlose SQL Academy-Lektion auf CoddyKit. Dies ist Lektion 4 von 4. Du kannst die komplette Lektion unten kostenlos lesen – dann übst du sie direkt im Browser mit einem integrierten Code-Editor und einem KI-Tutor rund um die Uhr. Sie ist Teil des SQL Academy-Lernpfads, und dein Fortschritt wird über Web und CoddyKit-App synchronisiert. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.
Warum Zugriffe auditieren
Zu wissen, wer auf welche Daten wann zugegriffen hat, ist ein Grundpfeiler der Datenbanksicherheit. Die Audit-Protokollierung schafft eine verlässliche Ereignisspur, mit der Sie unbefugten Zugriff erkennen, Vorfälle untersuchen und Compliance-Anforderungen wie GDPR, HIPAA oder SOC 2 erfüllen können.
In dieser Lektion lernen Sie, wie Sie Audit-Tabellen entwerfen, Zugriffsereignisse automatisch mit Triggern erfassen, die integrierten Protokollierungsfunktionen von PostgreSQL verwenden und den Audit-Verlauf abfragen, um die Frage zu beantworten: wer kann was sehen?
Eine Audit-Log-Tabelle entwerfen
Der erste Schritt ist eine eigene Tabelle, die jedes relevante Ereignis aufzeichnet. Ein gutes Audit-Log speichert den Tabellennamen, die Art der Operation, die alten und neuen Werte, den Benutzer, der die Aktion ausgeführt hat, sowie den genauen Zeitstempel.
Das folgende Beispiel erstellt eine allgemein verwendbare audit_log-Tabelle mit JSONB-Spalten zum Speichern von Zeilensnapshots — flexibel genug, um jede Tabelle ohne Schemaänderungen zu verarbeiten.
CREATE TABLE audit_log (
id BIGSERIAL PRIMARY KEY,
event_time TIMESTAMPTZ NOT NULL DEFAULT now(),
db_user TEXT NOT NULL DEFAULT current_user,
app_user TEXT,
table_name TEXT NOT NULL,
operation TEXT NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE','SELECT')),
row_id BIGINT,
old_data JSONB,
new_data JSONB
);Aktuellen Benutzer erfassen
PostgreSQL stellt mehrere integrierte Funktionen bereit, mit denen Sie ermitteln können, wer eine Abfrage ausführt. current_user gibt den Rollennamen zurück, der nach jedem SET ROLE wirksam ist. session_user gibt unabhängig von Rollenwechseln immer die ursprüngliche Login-Rolle zurück.
Für Anwendungen, die eine einzelne gemeinsame DB-Rolle verwenden, aber einen Benutzer auf Anwendungsebene über SET LOCAL app.current_user übergeben, können Sie diese Einstellung mit current_setting() auslesen.
-- Who is the database user right now?
SELECT current_user,
session_user;
-- Read an application-level user injected by the app layer
SELECT current_setting('app.current_user', true) AS app_user;Eine Audit-Trigger-Funktion schreiben
Eine Trigger-Funktion ist die zuverlässigste Möglichkeit, Datenänderungsereignisse zu erfassen, da sie automatisch ausgelöst wird — kein Anwendungscode kann sie umgehen. Die folgende Funktion protokolliert jedes INSERT, UPDATE und DELETE in jeder Tabelle, an die sie angehängt ist, und speichert die alten und neuen Zeilenwerte als JSONB.
Beachten Sie die Verwendung von TG_TABLE_NAME (der Tabelle, die den Trigger ausgelöst hat) und row_to_json(), um Zeilenwerte in ein speicherbares Format umzuwandeln.
CREATE OR REPLACE FUNCTION fn_audit_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
INSERT INTO audit_log (
db_user,
app_user,
table_name,
operation,
row_id,
old_data,
new_data
) VALUES (
current_user,
current_setting('app.current_user', true),
TG_TABLE_NAME,
TG_OP,
COALESCE(NEW.id, OLD.id),
CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE row_to_json(OLD)::JSONB END,
CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE row_to_json(NEW)::JSONB END
);
RETURN NULL;
END;
$$;Den Trigger an eine Tabelle anhängen
Sobald die Trigger-Funktion vorhanden ist, hängen Sie sie mit einer CREATE TRIGGER-Anweisung an jede Tabelle an, die Sie auditieren möchten. AFTER stellt sicher, dass die Daten tatsächlich geschrieben wurden, bevor der Protokolleintrag erstellt wird. Die Klausel FOR EACH ROW löst den Trigger für jede geänderte Zeile einmal aus.
Hier wird der Trigger auf eine hypothetische Tabelle patients angewendet, um jedes INSERT, UPDATE und DELETE automatisch zu erfassen.
CREATE TRIGGER trg_audit_patients
AFTER INSERT OR UPDATE OR DELETE
ON patients
FOR EACH ROW
EXECUTE FUNCTION fn_audit_changes();SELECT-Abfragen auditieren
Trigger für Datenänderungen erfassen nur Schreibvorgänge. Um Lesezugriffe zu auditieren, benötigen Sie einen anderen Ansatz. Eine Möglichkeit ist ein Trigger auf Anweisungsebene mit AFTER SELECT (in PostgreSQL 14+ in bestimmten Kontexten unterstützt). Häufiger ist es, Lesevorgänge explizit in einer Funktion oder View zu protokollieren, die die sensible Tabelle kapselt.
Das folgende Beispiel kapselt eine sensible Tabelle in einer Funktion, die jeden Lesezugriff protokolliert, bevor sie die Ergebnisse zurückgibt.
CREATE OR REPLACE FUNCTION get_patient_record(p_id INT)
RETURNS SETOF patients
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
-- Log the read access
INSERT INTO audit_log (db_user, app_user, table_name, operation, row_id)
VALUES (
current_user,
current_setting('app.current_user', true),
'patients',
'SELECT',
p_id
);
RETURN QUERY
SELECT * FROM patients WHERE id = p_id;
END;
$$;In PostgreSQL integrierte Protokollierung
Die postgresql.conf von PostgreSQL bietet eine leistungsfähige serverseitige Protokollierung, für die kein Anwendungscode erforderlich ist. Das Setzen von log_min_duration_statement protokolliert jede Abfrage, die einen Schwellenwert überschreitet. Das Setzen von log_connections und log_disconnections erfasst, wer sich anmeldet und abmeldet.
Die folgende Abfrage verwendet die Systemansicht pg_stat_activity, um aktuell aktive Sitzungen anzuzeigen — eine einfache Form der Live-Zugriffsüberwachung.
-- See who is currently connected and what they are running
SELECT pid,
usename AS db_user,
application_name,
client_addr,
state,
query_start,
LEFT(query, 80) AS current_query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start DESC;Das Audit-Log abfragen
Ein Audit-Log ist nur dann wertvoll, wenn Sie es effektiv abfragen können. Häufige Fragen sind: Welcher Benutzer hat zuletzt auf einen Datensatz zugegriffen, was hat sich im Laufe der Zeit in einer Zeile geändert und wie viele sensible Lesezugriffe gab es in den letzten 24 Stunden?
Die folgende Abfrage findet alle Benutzer, die auf einen bestimmten Patientendatensatz zugegriffen haben, sortiert nach dem neuesten Zugriff zuerst.
SELECT event_time,
db_user,
app_user,
operation,
old_data,
new_data
FROM audit_log
WHERE table_name = 'patients'
AND row_id = 42
ORDER BY event_time DESC
LIMIT 20;Verdächtige Zugriffsmuster erkennen
Sobald Audit-Daten erfasst werden, können Sie Abfragen schreiben, die Anomalien erkennen. Wenn ein Benutzer plötzlich deutlich mehr Zeilen als üblich liest oder auf denselben sensiblen Datensatz innerhalb eines kurzen Zeitraums mehrfach zugegriffen wird, kann dies auf einen Versuch zur Datenexfiltration hindeuten.
Die folgende Abfrage zählt die SELECT-Ereignisse pro Anwendungsbenutzer innerhalb der letzten Stunde und hebt alle hervor, die mehr als 100 Zeilen gelesen haben.
SELECT app_user,
COUNT(*) AS records_accessed
FROM audit_log
WHERE operation = 'SELECT'
AND event_time >= now() - INTERVAL '1 hour'
GROUP BY app_user
HAVING COUNT(*) > 100
ORDER BY records_accessed DESC;Das Audit-Log selbst schützen
Ein Audit-Log, das geändert werden kann, ist nicht vertrauenswürdig. Sie sollten es so absichern, dass gewöhnliche Benutzer und Anwendungsrollen keine Zeilen löschen oder aktualisieren können. Am sichersten ist es, der Anwendungsrolle nur INSERT zu gewähren und SELECT einer dedizierten Auditorenrolle vorzubehalten.
Sie können die Unveränderlichkeit außerdem mit einem Trigger erzwingen, der eine Ausnahme auslöst, wenn jemand versucht, eine Audit-Zeile zu aktualisieren oder zu löschen.
-- Only the app role may insert; nobody may update or delete
REVOKE ALL ON audit_log FROM PUBLIC;
GRANT INSERT ON audit_log TO app_role;
GRANT SELECT ON audit_log TO auditor_role;
-- Trigger to block any tampering
CREATE OR REPLACE FUNCTION fn_protect_audit()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
RAISE EXCEPTION 'audit_log rows are immutable';
RETURN NULL;
END;
$$;
CREATE TRIGGER trg_protect_audit
BEFORE UPDATE OR DELETE ON audit_log
FOR EACH ROW EXECUTE FUNCTION fn_protect_audit();RLS-Policy-Entscheidungen auditieren
Wenn Row-Level Security aktiv ist, blendet PostgreSQL Zeilen stillschweigend aus, anstatt Fehler auszulösen. Dadurch ist schwer zu erkennen, ob ein Benutzer versucht hat, eine Zeile zu lesen, die er nicht sehen durfte. Eine Möglichkeit besteht darin, eine permissive Policy hinzuzufügen, die immer einen Audit-Eintrag einfügt, bevor die restriktive Policy Zeilen herausfiltert.
Die folgende Abfrage zeigt, wie Sie untersuchen können, welche RLS-Policies für eine Tabelle existieren und für welche Rollen sie gelten.
-- View all RLS policies on the patients table
SELECT polname AS policy_name,
polcmd AS command,
polroles::TEXT AS applies_to,
polqual::TEXT AS using_expression,
polwithcheck::TEXT AS with_check_expression
FROM pg_policy
WHERE polrelid = 'patients'::REGCLASS
ORDER BY polname;Wissensüberprüfung
Testen Sie Ihr Verständnis der Zugriffsüberwachung in SQL.
Zusammenfassung der Lektion
In dieser Lektion haben Sie untersucht, wie Sie in PostgreSQL ein vollständiges System zur Überwachung von Zugriffen erstellen:
- Audit-Log-Tabelle — eine auf JSONB basierende Tabelle, die erfasst, wer wann was getan hat.
- Trigger-Funktion — protokolliert automatisch INSERT-, UPDATE- und DELETE-Ereignisse für jede verknüpfte Tabelle und verwendet dabei
TG_TABLE_NAME,row_to_json()undcurrent_user. - Lesezugriffe überwachen — sensible Tabellen in Funktionen kapseln, die SELECT-Ereignisse protokollieren, bevor sie Daten zurückgeben.
- Integrierte Überwachung —
pg_stat_activityzeigt aktive Sitzungen; die serverseitige Protokollierung erfasst Abfragen ohne Änderungen am Code. - Anomalien erkennen — aggregierte Abfragen über das Audit-Log können ungewöhnlich viele Zugriffe aufdecken.
- Unveränderlichkeit — UPDATE/DELETE für die Audit-Tabelle widerrufen und einen blockierenden Trigger hinzufügen, um Manipulationen zu verhindern.
Ein gut konzipierter Audit-Trail ist Ihr zuverlässigstes Werkzeug, um zu beantworten, wer worauf zugegriffen hat, und bildet die Grundlage jeder Compliance- oder Sicherheitsuntersuchung.
Häufig gestellte Fragen
Ist die Lektion „Zugriffe prüfen“ kostenlos?
Ja — der vollständige Text von „Zugriffe prüfen“ ist hier im Web kostenlos zu lesen. Um sie interaktiv zu üben (integrierter Code-Editor und 24/7 KI-Tutor) und den Rest des SQL Academy-Kurses freizuschalten, upgrade auf CoddyKit PRO. Der SQL Academy-Kurs umfasst insgesamt 4 Lektionen.
Was lerne ich in „Zugriffe prüfen“?
Nachverfolgen, wer was sehen darf Du übst SQL Academy mit praktischem Code, den du direkt im Browser ausführst, und ein 24/7 KI-Tutor beantwortet deine Fragen während du die Lektion bearbeitest.
Brauche ich Erfahrung, um SQL Academy zu starten?
Keine Vorkenntnisse erforderlich. SQL Academy auf CoddyKit ist für Anfänger bis fortgeschrittene Lernende strukturiert, sodass du hier starten oder von Anfang an beginnen und in deinem eigenen Tempo voranschreiten kannst. Dies ist Lektion 4 von 4.
Wie lange dauert die Lektion „Zugriffe prüfen“?
Die meisten CoddyKit-Lektionen dauern etwa 5–10 Minuten. Jede ist kompakt und interaktiv, sodass du stetig Fortschritte machst und genau dort weitermachst, wo du aufgehört hast – im Web und in der App.
Kann ich in dieser SQL Academy-Lektion Code schreiben und ausführen?
Ja. Jede SQL Academy-Lektion enthält einen integrierten Code-Editor, sodass du echten Code direkt in deinem Browser schreibst und ausführst und sofort KI-Feedback erhältst — ohne lokale Einrichtung erforderlich.
Alle Lektionen in diesem Kurs
- Rollen und Berechtigungen
- Richtlinien für Sicherheit auf Zeilenebene
- Berechtigungen auf Spaltenebene
- Zugriffe prüfen