创建账本表、读取其变更历史并验证完整性

命令

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":"..."}';

作用

创建一张账本表，以加密哈希链记录行变更；_Ledger 视图显示每次 INSERT/UPDATE/DELETE 由谁在何时执行，对照已存储的摘要进行验证可证明数据事后未被篡改（包括被 DBA 篡改）。

适用场景

用于不可篡改性必须可证明的数据：财务记录、审计跟踪、合同/审批记录。

注意

表创建后无法关闭账本；账本表不能转换为普通表，被删除的账本表会作为已删除的账本表保留。可更新账本表每次变更都会写一行历史，存储会增长。除非摘要存放在数据库之外的不可变存储（WORM、Azure 不可变 blob）中，否则验证不具备证据价值。未启用快照隔离时，sp_verify_database_ledger 会以错误 37498 失败。