Dziennik zdarzeń tylko do dopisywania i łańcuch skrótów SHA-256 w SQL Server

Autor: Adam Siemiątkowski, SoftwareStudio Group Sp. z o.o.

Dziennik zdarzeń tylko do dopisywania przechowuje historię dokumentu jako kolejne wiersze, których kod nigdy nie zmienia. Piszemy dla programisty T-SQL, który projektuje taki dziennik do listu przewozowego, dokumentu magazynowego albo rejestru zgód i musi wiedzieć, przed czym chroni każda z dostępnych technik. Nazwy tabel i kolumn w przykładach są wymyślone. Składnię sprawdziliśmy w dokumentacji Microsoftu, ale kodu nie uruchamialiśmy na serwerze, więc przed użyciem przetestuj go w jednorazowej bazie.

Blokada zapisu i łańcuch skrótów robią co innego. Blokada zatrzymuje zmianę w chwili jej wykonania. Łańcuch niczego nie zatrzymuje, ale pozwala później udowodnić, że zapis się zmienił. Dziennik, który ma służyć jako dowód w sporze, potrzebuje obu.

Każda technika ochrony dziennika zostawia inną lukę

Tabela poniżej zestawia cztery mechanizmy. Żaden z nich nie wystarcza sam, a wybór zależy głównie od wersji serwera i od tego, kto ma uprawnienie ALTER na tabeli.

Tabela ledger z APPEND_ONLY przyjmuje wyłącznie INSERT

SQL Server 2022 i Azure SQL mają tabele ledger w trybie tylko do dopisywania. Opcję podaje się przy tworzeniu tabeli i nie da się jej później wyłączyć ani dodać do tabeli istniejącej. Silnik dopisuje dwie ukryte kolumny, ledger_start_transaction_id i ledger_start_sequence_number, oraz widok księgi.

CREATE TABLE dbo.ListZdarzenie (
    ListId         bigint        NOT NULL,
    Nr             int           NOT NULL,
    Tresc          nvarchar(max) NOT NULL,
    PoprzedniSkrot varbinary(32) NOT NULL,
    Skrot          varbinary(32) NOT NULL,
    CONSTRAINT PK_ListZdarzenie PRIMARY KEY (ListId, Nr)
)
WITH (LEDGER = ON (APPEND_ONLY = ON));
GO

Przykład ilustracyjny, nazwy wymyślone. Składnię sprawdziliśmy w dokumentacji Microsoftu, kodu nie uruchamialiśmy na serwerze.

Przed wyborem tej drogi sprawdź ograniczenia tabel ledger. Usuwanie starszych danych nie jest obsługiwane, podobnie jak przełączanie partycji, więc retencji typu „kasuj po sześciu latach” nie zrealizujesz w tej samej tabeli. Nie zadziała też replikacja transakcyjna. Dodać można tylko kolumnę dopuszczającą NULL.

Na starszym serwerze UPDATE i DELETE odrzuca wyzwalacz INSTEAD OF

Przed SQL Server 2022 tę samą regułę egzekwuje wyzwalacz. INSTEAD OF działa zamiast instrukcji, więc wiersze w ogóle nie są dotykane, a THROW zwraca aplikacji czytelny błąd. Wzorzec odrzucania zapisu przez wyzwalacz opisuje też artykuł o wyzwalaczu odrzucającym aktualizację bez wpisu.

-- Ta sama tabela, ale bez klauzuli WITH (LEDGER ...), dla wersji sprzed SQL Server 2022.
CREATE TRIGGER dbo.tr_ListZdarzenie_TylkoDopisywanie
ON dbo.ListZdarzenie
INSTEAD OF UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    THROW 50120, N'Dziennik listu przyjmuje tylko dopisywanie.', 1;
END;
GO

Przykład ilustracyjny, nazwy wymyślone. Składnię sprawdziliśmy w dokumentacji Microsoftu, kodu nie uruchamialiśmy na serwerze.

TRUNCATE TABLE nie uruchamia tego wyzwalacza, a konto z uprawnieniem ALTER może go wyłączyć. Dlatego aplikacja dostaje tylko EXECUTE na procedurze dopisującej i SELECT na tabeli. Klucz obcy z tabeli podpisów wskazujący na dziennik blokuje dodatkowo TRUNCATE TABLE. Administrator bazy zachowuje jednak pełną władzę nad tabelą, i właśnie w tym miejscu zaczyna się rola łańcucha.

Skrót każdego wiersza obejmuje skrót poprzedniego

Procedura dopisująca odczytuje skrót ostatniego zdarzenia listu i liczy nowy z dwóch składników: bajtów poprzedniego skrótu oraz bajtów treści. Pierwsze zdarzenie ma pusty poprzedni skrót. Zmiana treści w dowolnym starszym wierszu przestaje pasować do jego zapisanego skrótu, a zmiana skrótu przestaje pasować do następnego wiersza.

Dwa równoległe zapisy do tego samego listu mogłyby przeczytać tego samego poprzednika. Klucz główny (ListId, Nr) odrzuciłby drugi z nich, ale aplikacja dostałaby błąd zamiast kolejnego numeru. Blokada sp_getapplock na czas transakcji szereguje zapisy tylko w obrębie jednego listu, tak jak w artykule o idempotencji wywołania zewnętrznego.

CREATE OR ALTER PROCEDURE dbo.ListZdarzenie_Dopisz
    @ListId bigint,
    @Tresc  nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @Zasob nvarchar(255) = CONCAT(N'ListZdarzenie:', @ListId);
    DECLARE @Wynik int, @Nr int, @Poprzedni varbinary(32);

    BEGIN TRANSACTION;

    -- Jeden list naraz: dwa zapisy nie dostaną tego samego poprzednika.
    EXEC @Wynik = sys.sp_getapplock
         @Resource = @Zasob, @LockMode = 'Exclusive',
         @LockOwner = 'Transaction', @LockTimeout = 5000;
    IF @Wynik < 0
        THROW 50121, N'Brak blokady dziennika listu.', 1;

    SELECT TOP (1) @Nr = Nr, @Poprzedni = Skrot
    FROM dbo.ListZdarzenie
    WHERE ListId = @ListId
    ORDER BY Nr DESC;

    SET @Nr = ISNULL(@Nr, 0) + 1;
    SET @Poprzedni = ISNULL(@Poprzedni, 0x);

    INSERT INTO dbo.ListZdarzenie (ListId, Nr, Tresc, PoprzedniSkrot, Skrot)
    VALUES (@ListId, @Nr, @Tresc, @Poprzedni,
            HASHBYTES('SHA2_256', @Poprzedni + CAST(@Tresc AS varbinary(max))));

    COMMIT TRANSACTION;
END;
GO

Przykład ilustracyjny, nazwy wymyślone. Składnię sprawdziliśmy w dokumentacji Microsoftu, kodu nie uruchamialiśmy na serwerze.

Na wersji starszej niż SQL Server 2016 SP1 zamień CREATE OR ALTER na CREATE, a pamiętaj też, że przed SQL Server 2016 wejście HASHBYTES jest ograniczone do 8000 bajtów.

Skrót chroni tylko to, co do niego weszło. Czas zdarzenia i wykonawcę trzeba więc umieścić w Tresc, na przykład jako JSON, a kolumny obok traktować wyłącznie jako kopię do wyszukiwania. Tekst przed skrótem musi być zapisany dokładnie w tej postaci, w której był liczony, bo każda zmiana formatowania daje inny wynik.

Drugi szczegół to kodowanie. nvarchar to UTF-16, więc HASHBYTES('SHA2_256', N'CMR') zwraca wartość zaczynającą się od 0xBF050773, a ten sam napis w varchar z kodowaniem jednobajtowym od 0x51D43178. Obie wartości policzyliśmy poza serwerem, a założenie o UTF-16 potwierdzają przykłady z dokumentacji HASHBYTES. Jeśli skrót ma przeliczać ktoś poza bazą, kodowanie musi być częścią opisu formatu. Skrót treści jako klucz idempotencji pokazuje artykuł o imporcie JSON z hashem SHA-256.

Weryfikacja przelicza łańcuch i wskazuje pierwszy niezgodny wiersz

Funkcja tabelowa porównuje każdy wiersz z poprzednim i ponownie liczy skrót z zapisanej treści. Pusty wynik oznacza łańcuch spójny. Pierwszy wiersz wyniku to miejsce, od którego historii nie można ufać. Funkcja czyta całą historię listu, więc jest tania dla pojedynczego dokumentu, a przy sprawdzaniu wszystkich listów warto ją uruchamiać partiami poza godzinami szczytu.

CREATE OR ALTER FUNCTION dbo.ListZdarzenie_Sprawdz (@ListId bigint)
RETURNS TABLE
AS
RETURN
WITH e AS (
    SELECT Nr, Tresc, PoprzedniSkrot, Skrot,
           ISNULL(LAG(Skrot) OVER (ORDER BY Nr), 0x) AS OczekiwanyPoprzedni,
           ROW_NUMBER() OVER (ORDER BY Nr) AS Pozycja
    FROM dbo.ListZdarzenie
    WHERE ListId = @ListId
)
SELECT TOP (1) Nr,
       CASE WHEN Nr <> Pozycja THEN N'luka w numeracji'
            WHEN PoprzedniSkrot <> OczekiwanyPoprzedni THEN N'inny skrót poprzednika'
            ELSE N'treść nie zgadza się ze skrótem' END AS Powod
FROM e
WHERE Nr <> Pozycja
   OR PoprzedniSkrot <> OczekiwanyPoprzedni
   OR Skrot <> HASHBYTES('SHA2_256', PoprzedniSkrot + CAST(Tresc AS varbinary(max)))
ORDER BY Nr;
GO

Przykład ilustracyjny, nazwy wymyślone. Składnię sprawdziliśmy w dokumentacji Microsoftu, kodu nie uruchamialiśmy na serwerze.

Test poniżej zakłada wariant z wyzwalaczem, bo w tabeli ledger kroku trzeciego nie da się wykonać. Tabela pod kodem podaje wynik oczekiwany z logiki przykładu, a nie odczytany z serwera.

-- 1. Trzy zdarzenia jednego listu.
EXEC dbo.ListZdarzenie_Dopisz @ListId = 1, @Tresc = N'{"typ":"UTWORZONY"}';
EXEC dbo.ListZdarzenie_Dopisz @ListId = 1, @Tresc = N'{"typ":"ZALADOWANY"}';
EXEC dbo.ListZdarzenie_Dopisz @ListId = 1, @Tresc = N'{"typ":"PODPIS_NADAWCY"}';
SELECT Nr, Powod FROM dbo.ListZdarzenie_Sprawdz(1);

-- 2. Zwykły UPDATE kończy się błędem z wyzwalacza.
BEGIN TRY
    UPDATE dbo.ListZdarzenie SET Tresc = N'{"typ":"ZMIANA"}' WHERE ListId = 1 AND Nr = 2;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS Blad, ERROR_MESSAGE() AS Komunikat;
END CATCH;

-- 3. Konto z ALTER wyłącza wyzwalacz i zmienia treść.
ALTER TABLE dbo.ListZdarzenie DISABLE TRIGGER tr_ListZdarzenie_TylkoDopisywanie;
UPDATE dbo.ListZdarzenie SET Tresc = N'{"typ":"ZMIANA"}' WHERE ListId = 1 AND Nr = 2;
ALTER TABLE dbo.ListZdarzenie ENABLE TRIGGER tr_ListZdarzenie_TylkoDopisywanie;
SELECT Nr, Powod FROM dbo.ListZdarzenie_Sprawdz(1);

Przykład ilustracyjny, nazwy wymyślone. Składnię sprawdziliśmy w dokumentacji Microsoftu, kodu nie uruchamialiśmy na serwerze.

Bez kopii poza bazą nie wykryjesz przepisania całej historii

Łańcuch liczony w tej samej bazie ma dwie granice. Kto może zmieniać wiersze, może też przeliczyć wszystkie skróty od miejsca zmiany do końca, a wtedy funkcja zwróci pusty wynik. Usunięcie ostatnich wierszy również nie psuje łańcucha, bo pozostała część nadal jest spójna.

• Własna kopia: numer i skrót ostatniego zdarzenia zapisane poza bazą, na wydruku albo w systemie drugiej strony.
• Skrót bazy: dokument z sp_generate_database_ledger_digest, sprawdzany przez sp_verify_database_ledger.

-- Kopia końca łańcucha: numer i skrót zapisz poza tą bazą.
DECLARE @ListId bigint = 1;
SELECT TOP (1) Nr AS OstatniNr, CONVERT(varchar(66), Skrot, 1) AS OstatniSkrot
FROM dbo.ListZdarzenie
WHERE ListId = @ListId
ORDER BY Nr DESC;

-- Tabele ledger: skrót całej bazy. Wynik JSON zapisz poza bazą.
EXECUTE sys.sp_generate_database_ledger_digest;

-- Później: weryfikacja względem zapisanego dokumentu JSON.
EXECUTE sys.sp_verify_database_ledger N'<zapisany dokument JSON>';

Przykład ilustracyjny, nazwy wymyślone. Składnię sprawdziliśmy w dokumentacji Microsoftu, kodu nie uruchamialiśmy na serwerze.

Skrót bazy obejmuje wszystkie tabele ledger i sprawdza go serwer. Do uruchomienia weryfikacji potrzebne jest uprawnienie VIEW LEDGER CONTENT oraz opcja ALLOW_SNAPSHOT_ISOLATION w bazie. Skrót wierszy jednego listu działa inaczej: można go wyeksportować z dokumentem i przeliczyć poza serwerem. Pierwszy odpowiada na pytanie, czy baza jest nienaruszona, drugi na pytanie, czy ten dokument jest nienaruszony, dlatego w dzienniku dowodowym oba mogą pracować obok siebie.

Elektroniczny list przewozowy jako przypadek użycia na PostgreSQL

Wzorzec wykorzystuje linkway.eCMR, elektroniczny list przewozowy CMR i krajowy z platformy Linkway. Działa na PostgreSQL z funkcją sha256(), a nie na SQL Server, więc kod T-SQL z tego artykułu nie jest kodem produktu. Skrót liczony jest jako HASH = sha256(PREV_HASH || PAYLOAD_TEXT), wyzwalacz bazy odrzuca UPDATE i DELETE na dzienniku, a funkcja weryfikacji przelicza łańcuch i wskazuje pierwsze niezgodne zdarzenie. Każda firma ma osobną instancję i bazę.

Na tym przypadku widać, po co dziennikowi druga warstwa. Skrót danych wyświetlonych przy podpisie musi być równy bieżącemu skrótowi listu, inaczej podpis zostaje odrzucony z kodem ECMR-40104. Do bazy trafiają tylko skróty tokenów i kodów, a identyfikator listu ustalany jest z tokenu z linku SMS, nigdy z formularza. Efekt końcowy opisuje strona o dowodzie dostawy i dzienniku zdarzeń. Produkt jest dziś wersją demonstracyjną bez pieczęci kwalifikowanej, a program pilotażowy zaplanowano na styczeń-marzec 2027 roku.

Pytania do analizy

Przed wdrożeniem dziennika odpowiedz na cztery pytania.

1. Czy serwer produkcyjny to SQL Server 2022 lub nowszy, czy zostaje wariant z wyzwalaczem?
2. Jak długo dane muszą być przechowywane i co ma się z nimi stać po tym terminie, skoro tabela ledger nie pozwala kasować starych wierszy?
3. Które konta mają ALTER na tabeli dziennika i kto kontroluje te konta?
4. Gdzie trafia kopia końca łańcucha, tak by administrator bazy nie mógł jej zmienić?