Créer une table ledger, lire son historique de modifications et vérifier son intégrité

Commande

USE [Musteri];
-- Ledger verification requires snapshot isolation:
ALTER DATABASE [Musteri] SET ALLOW_SNAPSHOT_ISOLATION ON;

-- Updatable ledger table (history table maintained automatically):
CREATE TABLE dbo.Hesap (
    HesapId int           NOT NULL PRIMARY KEY,
    Bakiye  decimal(18,2) NOT NULL
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Hesap_History), LEDGER = ON);

-- Append-only ledger (UPDATE/DELETE are blocked):
CREATE TABLE dbo.IslemLog (Id bigint IDENTITY, Mesaj nvarchar(400))
WITH (LEDGER = ON (APPEND_ONLY = ON));

-- Who changed what, and when:
SELECT t.commit_time, t.principal_name, l.HesapId, l.Bakiye, l.ledger_operation_type_desc
FROM   dbo.Hesap_Ledger l
  JOIN sys.database_ledger_transactions t ON t.transaction_id = l.ledger_transaction_id
ORDER BY t.commit_time DESC;

-- Take a digest (store it somewhere trusted outside) and verify:
EXEC sys.sp_generate_database_ledger_digest;
EXEC sys.sp_verify_database_ledger
     N'{"database_name":"Musteri","block_id":0,"hash":"0x...","last_transaction_commit_time":"...","digest_time":"..."}';

Ce que fait la commande

Crée une table ledger qui enregistre les modifications de lignes dans une chaîne de hachage cryptographique ; la vue _Ledger indique qui a effectué chaque INSERT/UPDATE/DELETE et quand, et la vérification par rapport à un digest stocké prouve que les données n'ont pas été modifiées après coup (y compris par les DBA).

Quand l'utiliser

Pour les données dont l'immuabilité doit être prouvable : enregistrements financiers, pistes d'audit, enregistrements de contrats/d'approbations.

Attention

Ledger ne peut pas être désactivé après la création de la table ; une table ledger ne peut pas être convertie en table ordinaire, et une table ledger supprimée est conservée en tant que dropped ledger table. Un updatable ledger écrit une ligne d'historique à chaque modification et le stockage grossit. Si les digests ne sont pas stockés hors de la base dans un stockage immuable (WORM, Azure immutable blob), la vérification n'a aucune valeur probante. sp_verify_database_ledger échoue avec l'erreur 37498 sans snapshot isolation.