Loggen von Schema-Änderungen in einer Datenbank mit DDL Trigger

Immer mal wieder wurde ich gefragt, “Wie kann man loggen und die Audit-logs inspizieren, wenn Entwickler Objekte verändern, aber keinen Zugriff zum SQL Audit haben?“

Nun sollte selbst eine Sicherheits-fokussierte Person wie ich versuchen, die wahre Motivation dahinter zu verstehen, bevor man zu früh zur Schlussfolgerung gelangt, das Auditing die einzige richtige Lösung ist.

Lasst uns also zunächst die möglichen Hintergründe verstehen:

Was ist der Zweck?

In diesem Fall ist der Hintergrund, einfach einen Eintrag mitzuschneiden, wer welche Datenbankobjekte geändert hat, um typische Fragen zu beantworten wie:

• “Hat jemand vor kurzem irgendeinen Index verändert?”
• “Ich dachte, wir hatten einen Index auf dieser Tabelle, hat jemand ihn gelöscht?”
• “Wer hat meine Prozedur geändert?” (Obwohl ich dazu raten würde, als Best-Practice einen Eintrag zu allen Änderungen im Header jedes Moduls zu festzuhalten.)
• “Wann lief bzw. stoppte das letzte Deployment?“
• “Meine alter SQL-Funktionscode ist weg; hat jemand eine Kopie?”

Die Motivation dahinter is grob gesehen das Troubleshooting von Schema-Veränderungen. Das Ziel ist nicht, Sicherheitsverletzungen zu entdecken oder gar manipulationssichere Sicherheitsprüfungen zu unterstützen.

Wenn dies der Fall ist, rate ich hier dazu, überhaupt kein Auditing zu solch einem Zweck zu verwenden.
Die Auditing-Features in SQL Server und Azure SQL Database sind wirklich nur dazu da, um von Sicherheitspersonen oder Administratoren mit hohen Privilegien verwendet zu werden:

• Die Fähigkeit, Audits zu erstellen, zu verändern, zu stoppen und zu entfernen, wird unter einer Berechtigung gesammelt: entweder ALTER ANY SERVER AUDIT oder ALTER ANY DATABASE AUDIT, je nach Bereich.
• Um das Ergebnis des Audits zu lesen, ist für Audits mit Ziel Anwendungs- oder Sicherheitslog als Minimum ALTER ANY SERVER AUDIT erforderlich, aber CONTROL SERVER für Audits in eine Datei. Bei Azure SQL Datenbank ist es CONTROL DATABASE, wenn man die Systemfunktion fn_get_audit_file anwendet, um von einem Dateiziel in Blob Storage zu lesen. Auf Event Hub oder Log Analytics wird von außerhalb der SQL Engine zugegriffen; sie sind nicht durch SQL-Berechtigungen kontrolliert.

Die obenstehende Anforderung wird viel leichter mit einer einfachen Lösung realisiert, die es ermöglicht, die Ergebnisse der DDL-Aktivitäten direkt innerhalb einer Datenbanktabelle zu speichern. Das ist eine praktische Methode um zu verstehen, welche Scripte oder Ad-hoc-Veränderungen des Datenbankschemas passiert sind – aber es wird nicht als Mittel gegen bösartige Akteure oder Angreifer dienen.

Sicherheits-Hinweis
Wenn es die Anforderung ist, einen Audit-trail aus Sicherheits- und Compliance-Gründen bereitzustellen, außerhalb der Reichweite normaler Entwickler oder irgendjemandem, der Zugriff auf SQL Server alleine hat, dann ist SQL Auditing die richtige Lösung. DDL Trigger und lokale Tabellen können leicht manipuliert werden, wenn sich ein Angreifer erst einmal zu datawriter oder ddl_admin-level Berechtigungen hochgestuft hat.

Die Lösung

Wenn ihr euer Entwicklerteam mit einem einfachen Mitschnitt aller DDL-Statements (DDL-Data-Definition Language) gegenüber einer Datenbank unterstützen wollt, dann empfehle ich euch, DDL Trigger zu verwenden.

Ein DDL Trigger ist vergleichbar mit einer gespeicherten Prozedur, außer das er aktiviert wird, wenn irgendein DDL-Event auftritt, für welches er erstellt wurde. Die Liste von Events, die verwendet werden können, ist hier: DDL Events – SQL Server | Microsoft Docs.

Persönliche Erfahrung
Über die Jahre hinweg, in denen ich mit Kundensystemen arbeitete, habe ich persönlich es als unbezahlbar empfunden, jede Datenbank, die ich entworfen habe, als Best-Practice mit solch einem kleinen Trigger und einer DDL-Log-Tabelle auszustatten, nur für den Fall der Fälle. Viele Male hat dies geholfen, Probleme mit Deployment-Skripten, mit nicht-geskripteten Veränderungen am System, und Probleme mit Source Control zu lösen und einfach schnell Antworten zu erhalten. Dieses Konzept ist fast trivial, und da DDL-Veränderungen gewöhnlich nicht in leistungskritischen Code-Pfaden vorhanden sind, ist der theoretische Overhead bei den DDL-Statement-Laufzeiten unerheblich. (Es sei denn, häufige Schema-Veränderungen sind Teil einer leistungssensiblen Workload – in dem Fall würde ich bezweifeln, dass es eine gute Idee ist, DDL überhaupt an solchem einem Ort zu verwenden. Auf DDL an Temporären Tabellen reagieren DDL-Triggern übrigens nicht.)

Wenn wir “fast jedes DDL Statement“ loggen wollen, können wir das Keyword DDL_DATABASE_LEVEL_EVENTS verwenden, und anschließend können wir bestimmte Events innerhalb des Triggerbodys ignorieren.

Hinweis
Events, bei den es typischerweise Sinn ergibt, sie zu ignorieren, sind: reguläre Index Rebuild, Reorganize und Update Statistics – Routinen.
– Rebuild und Reorganize werden beide durch das ALTER_INDEX Event abgedeckt. Aber ich würde empfehlen, ein Auge auf ALTER INDEX … DISABLE zu haben; daher findet ihr in meinem Beispiel einen Code-Abschnitt um ALTER INDEX nur auszuschließen, das nicht von der DISABLE-Option verursacht wird. (“Enable” würde dann via eines Rebuild gemacht, so dass es leider eine kleine Lücke gibt: wir werden nicht wissen, wann der Index wieder aktiviert wird. Falls sich dies als wichtig herausstellen sollte, sollte man auch die Rebuild-Option einschließen, aber in diesem Fall mit einer hohen Anzahl von Events rechnen.)

Innerhalb des Triggers haben wir Zugriff auf die EVENTDATA()-Funktion, welche die Details zum Event in xml-Format zurückgibt. Daraus extrahiert der Trigger-Code die nützlichsten Daten.
Die auf diese Weise festgehaltenen Events können dann in eine lokale Datenbanktabelle geschrieben werden, was viel praktischer fürs Troubleshooting ist, als zu einem Sicherheitsaudit gehen zu müssen.

Hinweis
Wenn ihr an den Set-Optionen interessiert seid, die verwendet wurden, als der Befehl durchgeführt wurde (dies kann für Debugging-Zwecke nützlich sein), dann ist der SetOptions-Node unter TSQLCommand sehr praktisch.

Und zum Abschluss müssen wir sicherstellen, dass der Trigger auch in die Tabelle zu schreiben kann, selbst wenn der der Nutzer, der das Event getriggert hat, nicht explizite Berechtigungen haben mag, in irgendwelche Tabellen zu schreiben. Daher berechtige ich in meinem einfach gehaltenen Beispiel die public-Rolle das INSERT auf die betreffende Tabelle. Das ist eine einfache Methode und für viele Szenarien ausreichend. Solltet ihr extra Sicherheit benötigen, empfehle ich, ein dediziertes Konto nur zur Impersonation während der Trigger-Ausführung unter Verwendung der EXECUTE AS-Klausel zu verwenden. Am Ende des Skripts findet ihr ein Beispiel dafür, wie man das macht. Auf diese Weise könnt ihr sicherstellen, die geringsten Berechtigungen („least privileges“) angewendet zu haben und auch nur, wenn es wirklich nötig ist.

Im Anhang findet ihr das T-SQL Skript, das die folgenden Objekte erstellen wird:

• Einen Datenbank-Level DDL Trigger
• Eine Tabelle in einem dedizierten Schema
• Eine gespeicherte Prozedur, um bei Bedarf Daten aus der Tabelle zu entfernen.