LoginSignup
2
2
@gsrh(g s)

EFとSQLDBの暗号化について

Last updated at Posted at 2023-09-29

個人の備忘録として
DB内各テーブルのレコードの暗号化方式に悩んでいる方のお役に立てれば幸いです。

AzureSQLDBを使う上での暗号化種類

  1. ソルト・ハッシュ
  2. 透過暗号化
  3. Always Encrypted
  4. 動的データマスク

上記のうち、nvarcharといった文字列型のみの暗号化をサポートしている暗号化方式は、1～3になります。
文字列型以外も暗号化したい際は、「4. 動的データマスク」を使用してください。

ソルト・ハッシュ

こちらはDBMSに限らず、誰しもが経験あると思います。
ソルト値を定数ファイルに持たせるか、カラムに持たせるかプロジェクト次第ではあるかと思いますが、
パスワード平文にソルト値を付与し、ハッシュ化することでハッシュ単体での暗号化と比べ、
レインボーテーブル等による、攻撃などの対策になりますしセキュリティ対策としては、十分といえるんじゃないかと思ってます。

透過暗号化

所謂TDE(Transparent Data Encryption)というものですね。
Oracleでも提供してるようですが(?)筆者はOracleを触ったことがない+ここ数年扱ってきたDBMSはAzureSQL DBが主流なので、あくまでAzure SQL DBでの知見を記載いたします。

以下、MS公式ドキュメントの要約になります。

保存時に暗号化し、読み取り時は復号をデータベース エンジン プロセスで自動で実施してくれる。
通常の暗号化⇔復号であれば、アプリケーション上で行う必要があるが、
透過的 という名のように、意識せずとも扱えるようですね。

また、TDEで使用する暗号化アルゴリズムはAES 256であり、
サーバー単位で一意であり、証明書の保存先はAzure SQL DBもしくはAzure KeyVaultのどちらかを選択できるとのことで、
証明書交換をしたい場合は、Azure KeyVaultを選択するなど、
定期的なセキュリティ対策も講じれますね。

ただし注意点して、あくまで外部からの不正アクセスの対策となるため、
ヒューマンエラーといった内部による誤ったデータ流出の対策にはなりません。
使う際は前述で紹介した、透過暗号化の復号後でも平文で保存しない方式をとるなど他の対策が必要そうです。

Always Encrypted

こちら筆者が一番頭を抱えつつやった暗号化方式です。。。
正直もう使いたくない。。。

前提として、昨今MS公式でも主力パッケージとして力を入れている「Entity Framework」・「Entity Framework Core」ではまだ対応されておらず、独自で暗号化⇔複号をする必要があります。
Always Encryptedを使用した暗号化をするにあたって、
記事やStack Ovewflowを書いていただいた方々に、大変感謝しておりますm(__)m

以下Entity Framework CoreでAlways Encryptedを使用した暗号化⇔複号するにあたっての手順になります。

暗号化にあたり準備するもの

VisualStudioやNuget経由でEntity Framework Coreの導入および、
DBContext・エンティティの定義および、マイグレーションは既に完了しているものとします。

  1. Azure KeyVault
  2. Azure KeyVaultおよびAzure SQL DBに接続可能なAzure ADユーザー
  3. SSMS(17以降) ※1

※1

SSMSのリリースノートにも記載の通り、Always Encrypted周りでまだ不具合があるようなので、
既知の不具合の解消および、各Entity Frameworkでサポートされることを1日でも早く願っています。

1. Azure KeyVault上にAlways Encryptedで使用するキーを作成

Azure Key Vaultを作成し、作成されたKey Vault上で新しいキーを作成する。
2023-10-15 11 15 24.png
2023-10-15 11 19 26.png

2. SSMS上にて接続可能なAzure SQL DBユーザーに対し以下権限を付与。

VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
VIEW ANY COLUMN MASTER KEY DEFINITION

こちらAzure KeyVault上で作成したキーを用いての暗号化⇔複号するにあたっての、必要となる権限になります。

3. SSMS上にて、DB上のキーとAzure KeyVaultのキーを対称キーとして紐づけ

以下スクリプトを実行

CREATE COLUMN MASTER KEY [MainMasterKey]
WITH
(
	KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT',
	KEY_PATH = N'<Azure Key Vault Identfier>'
)

Azure Key Vault Identfierには「1. Azure KeyVault上にAlways Encryptedで使用するキーを作成」の手順にて発行したキー内、キー識別子を記載。

4. SSMS上にて、暗号化したいカラムに対して暗号化設定

https://learn.microsoft.com/ja-jp/sql/relational-databases/security/encryption/always-encrypted-wizard?view=sql-server-ver16
対称のテーブルを選択し、列の暗号化を選択。
alwaysencrypt.png

Always Encryptedのウィザード画面上で、列の暗号化キーで「3. SSMS上にて、DB上のキーとAzure KeyVaultのキーを対称キーとして紐づけ」で作成したマスターキーを指定。
Always Encryptedでの暗号化は上記で完了です。

次にEntityFramework Coreで暗号化⇔複号するコードになります。
Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider
上記NuGetパッケージをインストール

using Data.Entities;
using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider;

namespace EncryptSample.Data
{
    public class EncryptSampleDbContext : DbContext
    {
        private static bool _isInitialized;
        public static SqlColumnEncryptionAzureKeyVaultProvider Provider { get; private set; }
        // TokenCredentialについては各々適切なCredentialを指定
        private TokenCredential _credential = new ClientSecretCredential("tenanId", "clientId", "clientSecret");

        public EncryptSampleDbContext (DbContextOptions<EncryptSampleDbContext> options)
            : base(options)
        {
            if (_isInitialized)
            {
                return;
            }

            // KeyVaultより取得したキーから、DBへの接続セッション中、
            // AlwaysEncryptで使用するキーストアプロバイダーを設定
            Provider = new SqlColumnEncryptionAzureKeyVaultProvider(_credential);
            var providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>
            {
                {SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, Provider}
            };

            SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);

            _isInitialized = true;
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionBuilder)
        {
            optionsBuilder.UseSqlServer(@"DB接続文字列");
        }
    }
}

SSMS上で複号した値を確認したい場合

Column Encryption Setting=Enabled

上記をSSMS上で対象DBにログインする際、「Additional Connection Parameters」に入力してください。
また、アプリケーションからDBに接続するユーザーをAzure ADではなくSQL Serverユーザーを使用したい場合、接続文字列に上記を追記してください。

以上が、Always Encryptedによる暗号化手順になります。

こちらも透過暗号化同様、あくまで外部からの不正アクセスの対策となるため、
ヒューマンエラーといった内部による誤ったデータ流出の対策にはなりません。
使う際は前述で紹介した、Always Encryptedの復号後でも平文で保存しない方式をとるなど他の対策が必要そうです。

あくまで筆者の感想になりますが、暗号化⇔復号のビジネスロジックをアプリケーション・DB上の両方に定義する必要がある。
コードファーストでマイグレーションした場合、DBの定義をEntity Classとは別に、SSMS上で設定する必要がある。
また、Always Encryptedの設定をしているカラムに対し、INSERT時平文データを追加できないなど、初期データ作成やデータ移行もできないため、それらを済ませた後に設定する必要がある。
等々、煩雑すぎる上既知の不具合もあるようで、少し学習コストが高いといった印象を受けました。。。

動的データマスク

あくまでデータのマスクであり、暗号化された値でDB上に保持されているわけではないので、
厳密には暗号化とは言えませんが、方式として紹介しておきます。

1. SSMS上にてDBユーザーに対し以下権限を付与。

UNMASK

2. 動的データマスクの設定

標準のマスクポリシー

マスク付与

こちらも透過暗号化同様、あくまで外部からの不正アクセスの対策となるため、
ヒューマンエラーといった内部による誤ったデータ流出の対策にはなりません。
使う際は前述で紹介した、アンマスク後でも平文で保存しない方式をとるなど他の対策が必要そうです。

3. 確認

Sampleとして、メールアドレス形式の動的データ マスク ポリシーを設定しています。

UNMASK権限のないDBユーザーでのSELECT結果

mask.png

UNMASK権限をもつDBユーザーでのSELECT結果

unmask.png

まとめ

以下各種暗号化方式によっての簡単なメトリクスになります。

ソルト・ハッシュ 透過暗号化 Always Encrypted 動的データマスク
アプリケーション上での暗号化・複合の必要性の有無 〇 × 〇 ×
外部からのアクセスによる対策 〇 〇 〇 〇
内部からのデータ流出※1 〇 × × ×
暗号方式の変更 △
非可逆の暗号化方式の場合は不可		 〇 〇 〇

※1
いずれも単体での採用の場合、対策にならないため他の暗号化方式を組み合わせるなどすれば、十分な対策になるかと思います。

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
2