Always Encrypted における列暗号化キーのローテーションについて

Always Encrypted における列暗号化キーのローテーションについて:

この記事は、2016 年 11 月 7 日 に Data Platform Tech Sales Team Blog にて公開された内容です。

Microsoft Japan Data Platform Tech Sales Team

中川

以前 DB Online の記事にて Always Encrypted に関するキーの管理・運用について概要をお伝えしました。その中で、列暗号化キーのローテーションについても概略をお伝えいたしましたが、今回の投稿では、実際に列暗号化キーのローテーションを実行した際に SQL Server 側でどのようなことが行われているのかをご紹介いたします。

上記記事でもご紹介しましたが、列暗号化キーをローテーションするためにはデータの再暗号を実施する必要があります。こちらの “Rotating a Column Encryption Key” セクションに具体的なスクリプトが載っておりますが、実際にデータの再暗号化処理が始まるのは、列の定義を新しい列暗号化キーを使用するように設定するスクリプト内の以下コマンドを実行した時です。

Set-SqlColumnEncryption -ColumnEncryptionSettings $ces -InputObject $database

このコマンドを実行した際に、SQL Server 側では以下の処理が実行されます。

① 該当する表と同じ定義を持った表が別名で作成される

② コマンドを実行したノードが該当する表からデータを取得しつつ、Driver 経由でデータの(旧列暗号化キーを使用した)復号化、(新列暗号化キーを使用した)暗号化を行い、その別名の表に対して insert bulk にてデータを挿入

➂ 既存表の削除

④ 別名の表の名前を変更

➄ 付随するインデックスが存在する場合には、インデックスの再作成

実際に、上記が行われていることを確認するために “Set-SqlColumnEncryption” コマンドを実行した際に SQL トレースを取得してみました。

先ずは、テスト用に以下のような表を作成し”生年月日”列を Always Encrypted にて列暗号化定義し、数件のデータを投入しています。

列マスターキーを正しく配置したノードにて、SSMSで接続パラメーター”Column Encryption Setting=Enabled”を指定しデータベースに接続後、表のデータを参照すると問題なく暗号化列含めてデータを参照できます。

もちろん上記パラメーターを指定しないと、暗号化列のデータは暗号化されたままで中身を確認することはできません。

ここで、“Set-SqlColumnEncryption” コマンドを実行し、SQL トレースを追っていきます。

① 該当する表と同じ定義を持った表が別名で作成される

以下のように全く同じ定義の表が別名で作成されています。

CREATE TABLE [dbo].[tmp_ms_xx_個人情報マスター1] (
 
  [ID] INT NOT NULL,
 
  [エリア] NVARCHAR (32) NOT NULL,
 
  [氏名] NVARCHAR (32) NOT NULL,
 
  [メールアドレス] VARCHAR (1024) MASKED WITH (FUNCTION = 'email()') NOT NULL,
 
  [カード番号] VARCHAR (1024) MASKED WITH (FUNCTION = 'partial(0, "************", 4)') NOT NULL,
 
  [生年月日] DATE ENCRYPTED WITH (
 
    COLUMN_ENCRYPTION_KEY = [CEK2],
 
    ALGORITHM = N'AEAD_AES_256_CBC_HMAC_SHA_256',
 
    ENCRYPTION_TYPE = DETERMINISTIC
 
    ) NOT NULL,
 
  CONSTRAINT [tmp_ms_xx_constraint_PK_個人情報マスター1] PRIMARY KEY CLUSTERED ([ID] ASC)
 
);

さりげなく(？)、SQL Server 2016 の新機能である Dynamic Data Masking にて”メールアドレス”列と”カード番号”列にマスキング設定をしていましたが、その定義も問題なく引き継いでいます。

② コマンドを実行したノードが該当する表からデータを取得しつつ、Driver 経由でデータの(旧列暗号化キーを使用した)復号化、(新列暗号化キーを使用した)暗号化を行い、その別名の表に対して insert bulk にてデータを挿入

insert bulk にて別名表にデータが挿入されていることが確認できます。

insert bulk [dbo].[tmp_ms_xx_個人情報マスター1] ([ID] Int, [エリア] NVarChar(32) COLLATE Japanese_CI_AS, [氏名] NVarChar(32) COLLATE Japanese_CI_AS, [メールアドレス] VarChar(1024) COLLATE Japanese_CI_AS, [カード番号] VarChar(1024) COLLATE Japanese_CI_AS, [生年月日] VarBinary(81)) with (TABLOCK, FIRE_TRIGGERS)
 

➂ 既存表の削除、④ 別名の表の名前を変更

既存表の削除、および別名表のリネームが行われています。

DROP TABLE [dbo].[個人情報マスター]; 
 
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_個人情報マスター1]', N'個人情報マスター';
 

➄ 付随するインデックスが存在する場合には、インデックスの再作成

今回は非クラスタ化インデックスが作成されていましたので、同定義のものが再作成されています。

CREATE UNIQUE NONCLUSTERED INDEX [idx_個人情報マスター_カード番号] ON [dbo].[個人情報マスター]([カード番号] ASC);
 

列暗号化キーのローテーションを行うにはデータの再暗号化処理が必要ですが、“Set-SqlColumnEncryption” コマンドを使うことによって上記処理を全て透過的に行ってくれます。

但し、暗号化処理が実行されている表に対してはデータの書き込みが行えない、また一時的にデータの二重持ちが発生することになりますので、実際に実行する際にはユーザー様への影響、リソース状況(ストレージ容量含む)を考慮してから実行するようにしてください。

以上、今回はAlways Encrypted における列暗号化キーのローテーションについてお伝えしました。



関連リンク

• クラウドでの利用も安心！ SQL Server におけるデータ暗号化