SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Database に追加された Ledger を学習する – 概要 –

leave a comment

Build 2021 で SQL Database の新機能として Ledger (台帳) がプレビュー機能として発表されました。

Ledger の初出は PASS Summit 2020 の Day2 Keynote になるのではと思いますが、実機でこの機能を検証することができるようになりましたので、試してみたいと思います。

公式ドキュメントは次の内容となるかと。

最終的にはすべての地域でプレビュー機能が利用できるようですが、投稿を書いている時点では「米国中西部」の論理サーバーでのみ利用することが可能です。(SLO は Basic でも使用できたので、従量課金で検証する場合も、基本機能検証であればコストは抑えられると思います)

そのため、Ledger データベース / CREATE TABLE の LEDGER=ON を実行するためには、米国中西部の論理サーバーに作成したデータベースで検証を行う必要があります。

学習の最初のステップとして Azure SQL Database ledger の内容を見ながら、Ledger がどのようなものなのかを学習したいと思います。

Ledger 全体の構成はこのようになります。


Ledger で実現できること

アナウンスに記載されている「The power of blockchain, the simplicity of SQL」が Ledger のシンプルな表現なのではないでしょうか。

SQL Server ベースの環境では、MapReduce を SQL で表現するために PolyBase が、グラフを SQL で表現するためにグラフテーブルの実装が行われました。

Ledger は従来型の中央集権的なシステムに対して、Blockchain (ブロックチェーン) のようなデータ改ざんの耐性を持った機能を提供し、トランザクションの台帳 (Ledger Table) に対してのすべての変更を履歴テーブルに透過的に管理 / 維持することができます。
データ改ざんの耐性を持つテーブルを Blockchain の開発ツールではなく、「Transact-SQL を用いて従来からの RDBMS 上に構築」することが可能となります。

PASS Summit 2020 のデモはデータ改ざんの耐性を示すものとしてわかりやすかったです。

  • Ledger Table を有効にしたテーブルのデータを DBCC WRITEPAGE を使用して強制的に変更
    • DBCC WRITEPAGE を directORbufferpool を 0 にして実行します。
    • この方法による変更はバッファの内容をチェックサムを再計算を行う方法となります。
    • つまり、トランザクションログには出力が行われないが、SQL Server 上のデータを直接変更した状態 (不正な変更を実施) となります。
  • チェックサムが再計算された状態の変更なので DBCC PAGE でも検出できない

 

具体的な変更の方法としては次のようなクエリです。

最初にテスト用のテーブルを作成し、データを投入します。

DROP TABLE IF EXISTS WriteDemo
GO
CREATE Table WriteDemo(
    C1 varchar(36)
)
GO
INSERT INTO WriteDemo VALUES(NEWID()),(NEWID())
GO

SELECT * FROM WriteDemo
OUTER APPLY sys.fn_PhysLocCracker(%%physloc%%)
GO


CHECKPOINT
GO

今回はページ ID 280 にデータの投入が行われていることが確認できますね。

image

今回は「WritePage」というデータベース上に上記のテーブルを作成していますので、WritePage DB の 280 ページを次のクエリで変更し、トランザクションログの内容を確認します。


SELECT * FROM WriteDemo
GO

DBCC WRITEPAGE('WritePage', 1, 280, 107, 1, 0x4B, 0)
GO

SELECT * FROM WriteDemo
GO

SELECT * FROM sys.fn_dblog(NULL, NULL)

実際の変更内容がこちらです。

image

バッファ上のデータを直接書き換えましたので、トランザクションログには変更が記録されていませんが「2612DA43」が「K612DA43」に変更されていることが確認できます。(K は Kazuaki の K)

directORbufferpool を 0 で変更した場合、チェックサムの再計算も実施されていますので、チェックサムの不正なページも見つかりません。(directORbufferpool が 1 の場合は、チェックサムの再計算を実施せず Direct Write により書き込みますのでチェックサムの不正なページは見つかります)

image

通常のテーブルでは、悪意のあるユーザーが SQL Server をバイパスして正しい形式でページの情報を書き換えた場合、不正な変更が行われたことを検出することができません。(トランザクションログで該当のレコードの変化を眺めるという荒業がありますが現実的ではありません)

Ledger を使用した場合は、悪意のあるユーザーが上記のような不正にデータを書き換えた場合も検知ができるようになります。

つまり、データ改ざんに耐性のある形でデータの変更の履歴が保持され、厳密なデータの変更の追跡を行うことができるということです。

Ledger のユースケース

Azure SQL Database ledger では、次の 3 種類が使用例として挙げられています。

  • データの信頼性を維持し、監査を合理化
    • データの完全性をシステムとして証明することで、データ改ざんが発生した場合の監査プロセスの実施 (監査ログ / アクセス状況の確認) を合理化
    • 該当のデータ変更が悪意のあるデータ変更でない (通常のプロセスによる変更) であることを証明する
  • 複数者間のビジネスプロセス
    • データの利用者は中央集権的に収容されたデータの整合性を検証できるソリューションを提供 (非中央集権ではない)
  • Blockchain 用の信頼できるオフチェーンストレージ
    • Blockchain ネットワークを持たず、データベースをオフチェーンストアとして利用した状態で、Blockchain ネットワークのオフチェーンストレージに必要となる、Blockchain の持つデータの完全性を提供し、システム全体で完全なデータの信頼性を確保

SQL Database を信頼されたストレージとして、Blockchain ネットワークを持たずにデータの信頼性を向上させることができる機能として活用を行うことができるようです。

Ledger の仕組み

Azure SQL Database ledger の続きを見ながら仕組みを見ていきましょう。

Ledger が有効なテーブルは各トランザクションが暗号化されてハッシュ化されます。

ハッシュ化の際のハッシュ関数は、現在のトランザクションと前回のトランザクションのハッシュを入力として使用することで、Blockchain と同様にすべてのトランザクションが結びつけられた状態になり、データ改ざんへの耐性が強いデータ構造となります。

Ledger では台帳管理を行うために次のような様々な仕組みが利用されています。

Ledger データベース

Ledger データベースは、データベース内のすべてのテーブルがデフォルトでは、更新可能な Ledger テーブルとして作成されます。

データベースの作成時に、「Enable for all future tables in this database」を有効にすると Ledger データベースとなり、データベース内に作成したテーブルは自動的に Ledger テーブルとなります。

image

現状、Ledger データベース化はデータベースの作成時にのみ設定することができ、既存のデータベースに対して適用すること / 適用しているデータベースを無効化することはできません。

Ledger データベース化されているデータベース内については、すべてのテーブルが Ledger テーブルとして作成されることが保証されます。

Ledger データベース化されていないデータベースで Ledger テーブルが使用できないかというとそういうことはありません。

通常のデータベースでも、テーブルの作成時に明示的に「LEDGER=ON」を指定してテーブルを作成することで、「更新可能な Ledger テーブル」「追記専用 Ledger テーブル」を作成することは可能です。

(現時点では、「米国中西部」に作成している DB でのみ、Ledger データベース化していないデータベースでも Ledger=ON を設定できるようです)


全てのテーブルが Ledger テーブルであることを保証する必要がある場合には、Ledger データベースとしての作成が必要になるということですね。

Ledger テーブル

Ledger で利用可能なテーブル

Ledger では、次の 2 種類のテーブルが使用することができます。

  • 更新可能な Ledger テーブル : 挿入以外に、データの更新 / 削除が可能
  • 追記専用 Ledger テーブル : 挿入のみ可能

Ledger テーブルについては、どちらのテーブルも共通で、「誰がどのタイミングで該当のトランザクションを発生させたか」がトラッキングできるようになっており、更新可能な Ledger テーブルの場合には、データの変更の履歴も保持されるようになります。

またどちらの種類のテーブルでも、各テーブル毎に、Ledger ビューというビューが自動的に作成され、内部の情報を見やすく整形したビューが提供されます。

更新可能な Ledger テーブル

更新可能な Ledger テーブルは、Ledger データベースに対して通常の CREATE TABLE を実行するか、Ledger データベース以外に対しては、明示的に「WITH (SYSTEM_VERSIONING = ON, LEDGER = ON)」を指定して作成したテーブルです。

SQL Server には、テンポラルテーブル という履歴テーブルにより、データの変更を自動的に管理するテーブルを作成することができます。

更新可能な Ledger テーブルは、テンポラルテーブルの履歴テーブルの仕組を Ledger によるトランザクションの管理用に拡張して使用している仕組みとなるようです。(Ledger のトランザクション ID によりバージョン管理が行われるようです)

データの変更が行われると自動的に変更前のデータが履歴テーブルに格納されます。

これにより、データの変更の時系列は、履歴テーブルの内容から確認することができ、どのようなデータの変更が行われたのかを把握することができます。

参考 : Azure SQL Database updatable ledger tables

追記専用 Ledger テーブル

追記専用 Ledger テーブルは、Ledger データベースでも、それ以外のデータベースでも利用時には明示的に「WITH(LEDGER=ON (APPEND_ONLY=ON))」を指定して作成することになるかと思います。

こちらは追記専用ですので履歴テーブルは持たず、更新可能な Ledger テーブル同様に、テーブル内には、Ledger のトランザクション ID が保持される形となります。(更新可能と異なり History Table が存在していません)

UPDATE / DELETE / TRUNCATE を行おうとするとエラーとなり、INSERT のみが可能なテーブルということが保証されます。

参考 : Azure SQL Database append-only ledger tables

データベース Ledger

システムで管理されたトランザクションの暗号ハッシュを格納するためのシステムテーブルで構成されます。

Ledger テーブルに対しての全てのトランザクションはコミットしたタイミングで、データベース Ledger のトランザクションエントリ (sys.database_ledger_transactions) として追加されます。

(更新可能な Ledger テーブルの場合は、履歴テーブルにも格納される)

image

自動的にデータベースダイジェストを生成するようにしている (Enable automatic digest storage が有効)  場合は、30 秒毎にデータベースで処理されたトランザクションは、Merkle ツリーのデータ構造を用いてハッシュ化され、ルートハッシュが生成され、これが、ブロック化 (sys.database_ledger_blocks) され、Blockchain が形成されます。

image

自動的にデータベースダイジェストを生成するようにしていない場合は、明示的に sys.sp_generate_database_ledger_digest を実行して生成されたデータベースダイジェストを個別に保存しておく必要があります。

(Enable automatic digest storage が有効な場合は、30 秒毎に自動的に Ledger ダイジェストが生成され、Digest Storage にアップロードされ、トランザクションがブロック化されます)

このような情報をデータベース Ledger として、システムテーブルに格納され保護されます。

参考 : What is the database ledger

データベースダイジェスト

データベースの状態を表す暗号ハッシュ (Database Digests : データベースダイジェスト) は、定期的に生成され、Azure SQL Database の外部にある、改ざん不可能な場所に保存されます。

データベースダイジェストは、ダイジェスト内のハッシュと、データベース内の計算されたハッシュを比較し、データベースの整合性を検証するために使用され、データベース Ledger の最新ブロックのハッシュがデータベースダイジェストと呼ばれます。

データベースダイジェストは、生成された時点でのデータベースの状態を表し、ダイジェストを改ざんから保護することが重要となります。

改ざんから保護するために、自動的にデータベースダイジェストを生成する場合は、保存先 (Digest Storage) としては、任意の Azure Storage または、Azure Confidential Ledger を指定することが可能です。

データベースダイジェストを手動で生成し、任意の場所に保存しておくということもできるようですが、その場合は保存先のセキュリティ保護には意識をしておく必要があるかと。

参考 : Digest management and database verification

Ledger の検証

データベースダイジェストを使用することで、 Ledger テーブルに保存されているデータが改ざんされていないかを

  • sys.sp_verify_database_ledger_from_digest_storage
    • 自動的にデータベースダイジェストを生成した Digest Storage のダイジェストを使用
  • sp_verify_database_ledger
    • sp_generate_database_ledger_digest 実行時に生成されたダイジェストや、Digest Storage に保存されているデータベースダイジェストの JSON を明示的に指定 (特定の Block のタイミングを指定して検証ができるのかと)

で検証することができます。

データベースにアクセスできる対象者が不正にデータを改ざんした場合、データベースダイジェストと、Ledger テーブルの現在の状態に基づいて、データベース Ledger に保存されているハッシュを再計算し、計算されたハッシュが入力された Digest と一致しない場合は、データの改ざんが行われたと判断することができます。

Ledger の検証は、データベース内のすべてのトランザクションのハッシュを再計算することになるため、リソースの消費が多く、頻繁に実行する操作ではありません。

検証が必要な場合は、特定のテーブルのみや、Ledger のサブセットのみを検証するオプションを指定することができます。

(ハッシュ再計算の範囲を限定的にして負荷を軽減)

参考 : Digest management and database verification

Ledger の制限

現状の制限 (制約) は Limitations for Azure SQL Database ledger に記載されています。

現状、Ledger でないテーブルを Ledger に変換 (またはその逆) はできないため、台帳化したいテーブルについてはデータ移行が必要となるようです。

 

Azure SQL Database ledger の内容の概略ですがこのようになるのではないでしょうか。

SQL Database では、WRITEPAGE が使用できないので、データ改ざんをどのように実施しようかが悩ましいですが、どのようにデータ改ざんを防止するためのデータ構造が生成されているかを確認することはできるのではないでしょうか。

Share

Written by Masayuki.Ozawa

5月 26th, 2021 at 1:58 pm

Leave a Reply