Новое в SQL Server 2022: Database Ledger

Автор: Deepam Ghosh, Database Ledger in SQL Server 2022

Защита данных от несанкционированного доступа сегодня — одна из главных задач. SQL Server 2022 предлагает новую функцию Database Ledger, усиливающую безопасность данных. Она защищает данные как от злоумышленников, так и от пользователей с повышенными привилегиями — администраторов баз данных, системных и облачных администраторов.

Database Ledger работает как традиционный реестр, фиксируя историю данных. Когда строка обновляется, SQL Server сохраняет её прежнее значение в таблице истории. Эта возможность использует технологию блокчейна, обеспечивая криптографическую целостность данных. SQL Server хеширует каждую транзакцию по SHA-256, формирует корневой хеш и связывает его с хешем предыдущего блока — создавая надёжную цепочку записей. В этой статье мы познакомимся с новой функцией SQL Server на практическом примере.

Реестровая база данных (Ledger Database)

В SQL Server 2022 появились реестровые базы данных (Ledger Databases), специально предназначенные для хранения реестровых таблиц. Такие базы используют технологию блокчейна, благодаря чему данные становятся защищёнными от подмены, криптографически проверяемыми, встроенными в платформу и прозрачными. Это полезно для приложений, которым нужны журналы аудита, а также высокий уровень безопасности, доверия и соответствия требованиям.

Примечание: при создании таблицы внутри реестровой базы данных SQL Server всегда создаёт её как реестровую таблицу.

Включается это параметром WITH LEDGER = ON при создании базы данных:

CREATE DATABASE [SQL2022-LedgerDB] WITH LEDGER = ON

Либо, если вы создаёте базу данных через графический интерфейс, установите параметр Is Ledger Database = True.

Реестровые таблицы (Ledger Tables)

В SQL Server 2022 появились реестровые таблицы (Ledger Tables) — особый тип таблиц, обеспечивающий неизменяемость данных и обнаружение попыток подмены путём криптографической связки транзакций для последующей проверки. Вместе с реестровыми базами данных они составляют функциональность SQL Server Ledger.

Примечание: по умолчанию все таблицы внутри реестровой базы являются реестровыми. Если вы хотите создать реестровую таблицу внутри обычной (не реестровой) базы, нужно явно включить это через WITH (LEDGER = ON).

Существует два типа реестровых таблиц: обновляемые и только для добавления.

Обновляемые реестровые таблицы (Updatable Ledger Tables)

Они допускают изменения. При создании обновляемой реестровой таблицы SQL Server автоматически создаёт таблицу истории, где фиксируется каждая версия строки. Этот тип полезен там, где данные часто меняются, например статус платежа или сведения о клиенте.

SQL Server также создаёт представление реестра (Ledger View), объединяющее основную и историческую таблицы, чтобы все изменения можно было видеть в одном месте.

Ниже — пример создания обновляемой реестровой таблицы:

CREATE TABLE dbo.UpdatableLedger (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100)
)
WITH (
        SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[UpdatableLedger_History])
        , LEDGER = ON (LEDGER_VIEW = [dbo].[UpdatableLedger_LedgerView])
);

Пояснение синтаксиса:

• SYSTEM_VERSIONING = ON делает таблицу системно-версионируемой. Системно-версионируемые таблицы, появившиеся в SQL Server 2016, отслеживают изменения с течением времени: основная таблица хранит актуальные данные, а таблица истории — все изменения. Функция Ledger опирается на этот механизм.
• HISTORY_TABLE явно задаёт имя таблицы истории. Если не указать, имя сгенерирует SQL Server.
• WITH (LEDGER = ON) включает функцию реестра, делая таблицу защищённой от подмены. Изменения также отражаются в метаданных реестра: sys.database_ledger_transactions, sys.database_ledger_blocks и sys.database_ledger_digest_locations.
• LEDGER_VIEW явно задаёт имя представления реестра. Если не указать, имя сгенерирует SQL Server.

Результаты показаны на рисунке ниже. Обратите внимание на дополнительные столбцы, которые SQL Server автоматически добавляет в вашу таблицу:

• ledger_start_transaction_id (BIGINT, NOT NULL) — идентификатор транзакции, вставившей или изменившей строку.
• ledger_end_transaction_id (BIGINT, NULL) — идентификатор транзакции, удалившей или изменившей строку.
• ledger_start_sequence_number (BIGINT, NOT NULL) — порядковый номер операции внутри транзакции.
• ledger_end_sequence_number (BIGINT, NULL) — порядковый номер, помечающий завершение периода актуальности строки.

Пример — обновляемые реестровые таблицы

В этом примере мы создадим обновляемую реестровую таблицу CustomerLedger. Мы выполним вставку, обновление и удаление нескольких записей, чтобы посмотреть на её поведение. В завершение обратимся к представлению реестра, чтобы просмотреть полную историю всех операций над таблицей.

Сначала создадим обновляемую реестровую таблицу.

CREATE TABLE dbo.CustomerLedger (
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(100),
    City NVARCHAR(100)
)
WITH (
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerLedger_History),
    LEDGER = ON (LEDGER_VIEW = dbo.CustomerLedger_LedgerView)
);

Затем вставим несколько записей в таблицу.

--1st Transaction
INSERT INTO dbo.CustomerLedger (CustomerID, Name, City)
VALUES (1, 'Mike', 'Delhi'),
       (2, 'Matt', 'Mumbai'),
       (3,'Robin','Sydney'),
       (4,'Hailey','New Jersey')
GO
-- 2nd Transaction
INSERT INTO dbo.CustomerLedger (CustomerID, Name, City)
VALUES (5, 'Kyle', 'Texas')
GO

Проверим вставленные данные.

SELECT [CustomerID]
      ,[Name]
      ,[City]
      ,[ledger_start_transaction_id]
      ,[ledger_end_transaction_id]
      ,[ledger_start_sequence_number]
      ,[ledger_end_sequence_number]
  FROM [SQL2022-LedgerDB].[dbo].[CustomerLedger]

Несколько наблюдений по вставленным данным:

• У первых четырёх строк одинаковый идентификатор транзакции 1189 в столбце ledger_start_transaction_id — это подтверждает, что их создал первый оператор вставки.
• Порядок вставки идентификаторов клиентов зафиксирован как 1 -> 2 -> 3 -> 4 в столбце ledger_start_sequence_number со значениями 0 -> 1 -> 2 -> 3.
• У пятой строки («Customer Id - 5») идентификатор транзакции 1192, что подтверждает её появление во второй вставке. Значение 0 в столбце ledger_start_sequence_number дополнительно показывает, что в этой транзакции вставлялась только одна строка.
• Так как обновлений и удалений ещё не было, столбцы ledger_end_transaction_id и ledger_end_sequence_number остаются NULL.

Теперь обновим одну строку и проверим основную и историческую таблицы реестра.

UPDATE dbo.CustomerLedger
SET City = 'Chennai'
WHERE CustomerID = 2;
GO
SELECT [CustomerID]
      ,[Name]
      ,[City]
      ,[ledger_start_transaction_id]
      ,[ledger_end_transaction_id]
      ,[ledger_start_sequence_number]
      ,[ledger_end_sequence_number]
  FROM [SQL2022-LedgerDB].[dbo].[CustomerLedger]
SELECT TOP (1000) [CustomerID]
      ,[Name]
      ,[City]
      ,[ledger_start_transaction_id]
      ,[ledger_end_transaction_id]
      ,[ledger_start_sequence_number]
      ,[ledger_end_sequence_number]
  FROM [SQL2022-LedgerDB].[dbo].[CustomerLedger_History]

Наблюдения после обновления записи:

• У второй строки (Customer Id 2) значение ledger_start_transaction_id после обновления стало 1194. Его прежнее значение 1189 переместилось в таблицу истории. Аналогично ledger_start_sequence_number изменился на 0, так как в транзакции был единственный оператор; прежнее значение 1 переместилось в таблицу истории.
• В таблице истории ledger_end_transaction_id равен 1194, что подтверждает изменение именно этой инструкцией UPDATE.

Далее удалим запись и снова проверим основную и историческую таблицы реестра.

DELETE FROM dbo.CustomerLedger
WHERE CustomerID = 1;
GO

Наблюдения после удаления записи:

• Запись с Customer ID 1 удалена из основной реестровой таблицы и перемещена в таблицу истории.
• В таблице истории ledger_start_transaction_id равен 1189 (исходный идентификатор транзакции), а ledger_end_transaction_id — 1203 (текущий идентификатор транзакции). Это означает, что данные были вставлены транзакцией 1189 и удалены транзакцией 1203.

Проверим также представление реестра.

SELECT TOP (1000) [CustomerID]
      ,[Name]
      ,[City]
      ,[ledger_transaction_id]
      ,[ledger_sequence_number]
      ,[ledger_operation_type]
      ,[ledger_operation_type_desc]
  FROM [SQL2022-LedgerDB].[dbo].[CustomerLedger_LedgerView]
  ORDER BY [ledger_transaction_id]

Мы видим следующие результаты. Представление реестра показывает полную последовательность событий, произошедших с реестровой таблицей. Обратите внимание, что SQL Server трактует оператор UPDATE как комбинацию операций INSERT + DELETE (Transaction ID 1194).

Реестровые таблицы только для добавления (Append-Only Ledger Tables)

Таблицы только для добавления позволяют исключительно вставки. Они не поддерживают обновления и удаления, поэтому SQL Server не создаёт для них таблицу истории. Такие таблицы полезны для журналов аудита и протоколирования. Как и для обновляемых реестровых таблиц, SQL Server создаёт представление реестра и для таблиц только для добавления.

Ниже пример создания реестровой таблицы только для добавления:

CREATE TABLE dbo.AppendOnlyLedger (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100)
)
WITH (
       LEDGER = ON (
                    APPEND_ONLY = ON,
                    LEDGER_VIEW = [dbo].[AppendOnlyLedger_LedgerView])
);

Пояснение синтаксиса:

• WITH (LEDGER = ON) включает функцию реестра и делает таблицу защищённой от подмены.
• APPEND_ONLY = ON гарантирует, что разрешены только вставки, делая таблицу неизменяемой.
• LEDGER_VIEW явно задаёт имя представления реестра.

Дополнительные столбцы, которые SQL Server добавляет автоматически:

• ledger_start_transaction_id (BIGINT, NOT NULL) — идентификатор транзакции, вставившей строку.
• ledger_start_sequence_number (BIGINT, NOT NULL) — порядковый номер операции внутри транзакции.

Поскольку обновления и удаления не выполняются, SQL Server не добавляет столбцы ledger_end_transaction_id и ledger_end_sequence_number.

Пример — реестровые таблицы только для добавления

В этом примере мы создадим таблицу только для добавления под названием «AuditLedger». Вставим в неё несколько записей, затем попробуем выполнить обновление и удаление. Наконец, обратимся к представлению реестра, чтобы просмотреть историю всех выполненных операций.

Создадим таблицу только для добавления следующим кодом:

CREATE TABLE dbo.AuditLedger (
    AuditID INT PRIMARY KEY,
    Action NVARCHAR(100),
    ActionTime DATETIME2 DEFAULT SYSUTCDATETIME()
)
WITH (
    LEDGER = ON (APPEND_ONLY = ON, LEDGER_VIEW = dbo.AuditLedger_LedgerView)
);

Теперь вставим несколько записей.

--1st Transaction
INSERT INTO dbo.AuditLedger (AuditID, Action)
VALUES (1, 'Login by user A'),
       (2, 'Password change by user B')
GO
--2nd Transaction
INSERT INTO dbo.AuditLedger (AuditID, Action)
VALUES (3, 'Logout by user D')
GO

Проверим вставленные данные.

SELECT TOP (1000) [AuditID]
      ,[Action]
      ,[ActionTime]
      ,[ledger_start_transaction_id]
      ,[ledger_start_sequence_number]
  FROM [SQL2022-LedgerDB].[dbo].[AuditLedger]

Поведение при вставке такое же, как и у обновляемых реестровых таблиц. Попробуем обновить и удалить несколько записей.

UPDATE dbo.AuditLedger
SET Action = 'Login by user X'
WHERE AuditID = 1
GO
DELETE FROM dbo.AuditLedger
WHERE AuditID = 2
GO

При попытке обновить или удалить строки SQL Server вернул ошибку — обновления не разрешены для таблицы реестра только для добавления «dbo.AuditLedger». Это подтверждает, что такие таблицы неизменяемы и защищены от подмены. Как только данные вставлены, изменить или удалить их уже нельзя.

Проверим представление реестра.

SELECT TOP (1000) [AuditID]
      ,[Action]
      ,[ActionTime]
      ,[ledger_transaction_id]
      ,[ledger_sequence_number]
      ,[ledger_operation_type]
      ,[ledger_operation_type_desc]
  FROM [SQL2022-LedgerDB].[dbo].[AuditLedger_LedgerView]

На приведённом снимке экрана представление реестра показывает полную последовательность событий, произошедших с реестровой таблицей. Для каждой строки указаны идентификатор транзакции, тип операции и порядковый номер, что обеспечивает контролепригодность и защиту от подмены.

Важные соображения перед использованием Database Ledger

Несмотря на весомые преимущества, перед включением SQL Server Ledger стоит учесть и возможные издержки:

• Неуклонный рост хранилища. Таблицы истории фиксируют каждую версию строк, поэтому объём данных быстро увеличивается при интенсивной записи.
• Влияние на производительность. Каждое изменение требует дополнительных записей в таблицу истории, метаданные и вычисления криптографических хешей. Реестровые таблицы работают медленнее обычных и не подходят для онлайновых транзакционных систем (OLTP).
• Ограниченная гибкость. Нельзя создавать обычные таблицы внутри реестровой базы. Поскольку реестровые таблицы в основном служат задачам аудита и соответствия требованиям, их область применения ограничена.

Заключение

В статье мы рассмотрели функцию SQL Server Ledger в версии 2022 года. Мы узнали, как устроены реестровые базы и реестровые таблицы, и показали их работу на примерах. Взвешенно оценивая компромиссы, организации могут использовать эту возможность для усиления безопасности данных, улучшения аудита и соответствия требованиям регламентов.