SQL braucht Manipulationsnachweise? SQL Ledger konfigurieren

Eine Zahlung wurde mit 100 erfasst und zeigt jetzt 120. Du möchtest die Änderung nachvollziehen und nachweisen können, dass jemand mit weitreichendem Zugriff den gespeicherten Verlauf nicht heimlich umgeschrieben hat. SQL Ledger bewahrt Zeilenversionen auf und verknüpft sie durch kryptografische Hashwerte. Ein Digest, ein kompakter kryptografischer Prüfpunkt außerhalb der Datenbank, ermöglicht später die Integritätsprüfung des Verlaufs.

Ledger-Tabelle erstellen

Verwende deine vorhandene Labdatenbank auf sql-ctappweu, etwa stctauditweu oder sqldb-cloudtrips. Verbinde dich als ctadmin über VS Code oder Query editor (preview). Erstelle bei Bedarf eine Datenbank mit Azure SQL Database erstellen.

Führe einmal aus, um eine änderbare Ledger-Tabelle und eine Zahlung anzulegen:

CREATE TABLE dbo.LedgerPayments (
    PaymentId int PRIMARY KEY,
    Amount decimal(10,2) NOT NULL
)
WITH (
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LedgerPaymentsHistory),
    LEDGER = ON (LEDGER_VIEW = dbo.LedgerPaymentsView)
);
INSERT INTO dbo.LedgerPayments VALUES (1, 100.00);

LEDGER = ON aktiviert Ledger für diese neue Tabelle. Azure erstellt auch die Verlaufstabelle und Ledger-Sicht. Diese Wahl ist für die Tabelle dauerhaft; verwende hier die Labtabelle.

Aufgezeichnete Änderung ansehen

Führe aus:

UPDATE dbo.LedgerPayments SET Amount = 120.00 WHERE PaymentId = 1;

SELECT PaymentId, Amount, ledger_operation_type_desc AS Operation
FROM dbo.LedgerPaymentsView
ORDER BY ledger_transaction_id, ledger_sequence_number;

Erwarte zuerst INSERT 100.00, dann DELETE 100.00 und INSERT 120.00 für die Änderung. Der aktuelle Betrag ist 120; der frühere Wert bleibt im aufgezeichneten Verlauf erhalten. Ein gewöhnliches SQL-Update wird aufgezeichnet; die Verifikation prüft die Unversehrtheit dieses Verlaufs.

Unabhängigen Prüfpunkt speichern

Führe aus:

EXEC sys.sp_generate_database_ledger_digest;

Kopiere das JSON-Objekt aus der Ergebniszelle latest_digest in ledger-digest.json. Exportierst du das Ergebnisraster, entnimm den Objektinhalt aus der latest_digest-Hülle und entferne die Escape-Backslashes der Zeichenkette, bevor du ihn unten verwendest.

Ein Block fasst bestätigte Transaktionen zusammen; Block-IDs beginnen bei 0. SQL berechnet Hashwerte der Zeilenversionen, fasst sie zu Transaktionshashes zusammen und verknüpft Blöcke über den Hash des vorherigen Blocks. Dadurch ist der gespeicherte Verlauf kryptografisch verbunden.

Dein JSON speichert database_name, block_id, den hash dieses Blocks und Zeitstempel. Der Hash ist ein Fingerabdruck des verknüpften Verlaufs an diesem Prüfpunkt. Bewahre das ursprüngliche JSON außerhalb der Datenbank zum späteren Vergleich auf; seine Werte variieren zwischen Ausführungen.

Dieses Lab speichert den Digest manuell. Im Produktivbetrieb benötigen Digests unabhängig geschützten Speicher, etwa unveränderlichen Blob Storage oder Azure Confidential Ledger. So kann jemand mit Änderungszugriff auf die Datenbank den Prüfpunkt nicht ebenfalls ersetzen. Azure SQL kann Digests über Datenbank → Security → Ledger automatisch hochladen.

Gegen den gespeicherten Digest prüfen

Die Verifikation liest gespeicherte Zeilen und Verlauf, berechnet ihre Hashwerte erneut und prüft Transaktions- und Blockverknüpfungen anhand deines gespeicherten Digests. Heimliches Umschreiben eines früheren Werts verändert die berechneten Hashes und führt zu einer Fehlermeldung. Spätere gewöhnliche Updates erhalten frühere Versionen und ergänzen aufgezeichnete Änderungen; der Verlauf bleibt überprüfbar.

Führe auf derselben Datenbank separat aus, um den für die Prüfung erforderlichen Isolationsmodus zu aktivieren:

ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;

Erfolgreiche Ausführung liefert normalerweise keine Zeilen. Ersetze anschließend den Platzhalter durch das gespeicherte JSON-Objekt, behalte die umgebenden eckigen Klammern und führe aus:

DECLARE @digests nvarchar(max) = N'[PASTE_SAVED_JSON_OBJECT_HERE]';
EXEC sys.sp_verify_database_ledger @digests;

Prüfe Messages auf Fehler und im Ergebnis last_verified_block_id. Für deinen gespeicherten Digest mit block_id: 0 bedeutet eine erfolgreiche Prüfung mit Ergebnis 0, dass SQL bis zum ersten Block geprüft hat. Behalte ältere Digests beim Erzeugen neuer: Jeder ist ein unabhängig gespeicherter Prüfpunkt für spätere Kontrollen.

Erkennt die Verifikation einen umgeschriebenen Verlauf, kann unter Messages beispielsweise stehen: „The hash of block xxxx in the database ledger doesn’t match the hash provided in the digest for this block.“ Dabei steht xxxx für die betroffene Block-ID.

Ein gewöhnliches SQL-UPDATE, etwa von 100 auf 120, wird im Verlauf aufgezeichnet und kann die Prüfung bestehen – auch bei einer böswilligen Änderung. Ledger erkennt manipulierte Historie; Geschäftsregeln und Zugriffskontrollen bestimmen, welche Änderungen erlaubt sind.

Bereinigen

Behalte die Labdatenbank für weitere Trips oder lösche sie nach Abschluss. Ledger erhält historische Daten auch nach dem Löschen einer Ledger-Tabelle; das Löschen der entbehrlichen Datenbank entfernt das gesamte Lab. Lösche die lokale Digestdatei, sobald du sie nicht mehr brauchst.