BigQueryの列レベル暗号化で使われる確定的関数と非確定的関数

kobayashi.m

2024.09.08

はじめに

データアナリティクス事業本部のkobayashiです。

BigQueryのCloud KMSによる列レベルの暗号化を使う場合に確定的と非確定的な暗号化関数がありますが、それぞれがどの様な挙動でどの場面で使われるかを実際にデータを使って試してみたのでまとめます。

BigQueryの確定的暗号化関数と非確定的暗号化関数

BigQueryでCloud KMS による列レベルの暗号化を行う際に暗号化されたテストを生成する際に使うのが暗号化関数です。BiqQueryではこの関数として確定的と非確定的な暗号化関数があります。
確定的暗号化では、暗号化される前のデータが同一の場合には出力される暗号テキストとが同じものになります。一方非確定的暗号化では、暗号化される前のデータが同一の場合でも出力される暗号化テキストは別のものになります。
この特性から確定的暗号化はクラスタリングやSQLの実装時の集約・結合を行うことができます。対して非確定的暗号化はクラスタリングやSQLの実装時の集約・結合を行うことができませんが暗号化前のデータが同一でも全く別の暗号化テキストになるのでよりセキュアにデータを取り扱いたい場面で使うことができます。

Cloud KMS による列レベルの暗号化 | BigQuery | Google Cloud

BigQueryの確定的暗号化関数と非確定的暗号化関数を試してみる

Cloud KMSでキーを作成する

Cloud KMS による列レベルの暗号化を行うためには先にCloud KMSでキーを作成する必要があります。キーを作成するためにはキーリングも作る必要があるので以下のコマンドで作成します。
なお今回検証を行うのはBigQueryを含めasia-northeast1のロケーションで行っています。

$ gcloud kms keyrings create bq_col_encrypt_keyring --location asia-northeast1
$ gcloud kms keys create bq_col_encrypt_key
    --keyring bq_col_encrypt_keyring \
    --location asia-northeast1 \
    --purpose "encryption" \
    --protection-level "software"

これでCloud KMSで

'gcp-kms://projects/{プロジェクトID}/locations/asia-northeast1/keyRings/bq_col_encrypt_keyring/cryptoKeys/bq_col_encrypt_key'

キーが作成されますので、今後KMS_KEYとして使っていきます。

キーを作成 | Cloud KMS Documentation | Google Cloud

鍵セットを作成する

列レベルの暗号化では鍵セットを用いて確定的暗号化関数・非確定的暗号化関数で暗号化テキストを作成します。そのため先に鍵セットを作成しておく必要があります。今回は確定的暗号化関数と非確定的暗号化関数で別の型の鍵セットが必要なのでこれらの鍵をkey_setsテーブルに保存して使用したおと思います。
それでは先にテーブルを作成します。

> CREATE TABLE access_control.key_sets
(
    id      STRING,
    key_set BYTES
);

次に確定的暗号化関数と非確定的暗号化関数の鍵セットを作成してこのテーブルに保存します。

> BEGIN
    DECLARE KMS_KEY STRING DEFAULT 'gcp-kms://projects/{プロジェクトID}/locations/asia-northeast1/keyRings/bq_col_encrypt_keyring/cryptoKeys/bq_col_encrypt_key';

    INSERT access_control.key_sets (id, key_set)
    SELECT "deterministically_key", KEYS.NEW_WRAPPED_KEYSET(KMS_KEY, 'DETERMINISTIC_AEAD_AES_SIV_CMAC_256');
    INSERT access_control.key_sets (id, key_set)
    SELECT "non_deterministically_key", KEYS.NEW_WRAPPED_KEYSET(KMS_KEY, 'AEAD_AES_GCM_256');
END;

確定的暗号化関数は鍵セットを作成する際にDETERMINISTIC_AEAD_AES_SIV_CMAC_256型で作成、非確定的暗号化関数はAEAD_AES_GCM_256で作成する必要があるため上記のSQLとなります。

> SELECT * FROM access_control.key_sets;
| id | key_set |
| :--- | :--- |
| deterministically_key | 0x0A240082D4CD84F1355148E8B82F7140F56AE4D4A... |
| non_deterministically_key | 0x0A240082D4CD842EEAE66D01DE3FAACD31E172A... |

確定的暗号化関数と非確定的暗号化関数の鍵セットが作成されています。

これで準備は整ったので実際に確定的暗号化関数と非確定的暗号化関数を試してみます。

確定的暗号化関数と非確定的暗号化関数の挙動を確かめる

検証方法ですが、ユーザーデータのテーブルがありその中の郵便番号を暗号化関数を使って暗号化テキストに変換してみます。

次のユーザー情報のテーブルを先に作成しておきます。

> CREATE OR REPLACE TABLE access_control.table1
(
    customer_id   STRING,
    customer_name STRING,
    zip_code      STRING
);
INSERT INTO access_control.table1 (customer_id, customer_name, zip_code)
INSERT INTO access_control.table1
    (customer_id, customer_name, zip_code)
VALUES ('C001', '佐藤 太郎', '111-1111'),
       ('C002', '鈴木 花子', '111-1111'),
       ('C003', '田中 一郎', '111-1111'),
       ('C004', '高橋 美香', '111-1111'),
       ('C005', '渡辺 健太', '111-1111'),
       ('C006', '小林 洋子', '222-2222'),
       ('C007', '加藤 隆', '222-2222'),
       ('C008', '吉田 真理', '222-2222'),
       ('C009', '山本 健一', '222-2222'),
       ('C010', '中村 優子', '222-2222');

次に確定的暗号化関数と非確定的暗号化関数で郵便番号の暗号化を行ってみます。

> BEGIN
    DECLARE KMS_KEY STRING DEFAULT 'gcp-kms://projects/{プロジェクトID}/locations/asia-northeast1/keyRings/bq_col_encrypt_keyring/cryptoKeys/bq_col_encrypt_key';
    DECLARE DETERMINISTICALLY_KEY BYTES DEFAULT (SELECT key_set
                                                 FROM access_control.key_sets
                                                 WHERE id = "deterministically_key");
    DECLARE NON_DETERMINISTICALLY_KEY BYTES DEFAULT (SELECT key_set
                                                     FROM access_control.key_sets
                                                     WHERE id = "non_deterministically_key");
    CREATE OR REPLACE TABLE access_control.table2
    AS
    SELECT customer_id,
           customer_name,
           zip_code,
           DETERMINISTIC_ENCRYPT(KEYS.KEYSET_CHAIN(KMS_KEY, DETERMINISTICALLY_KEY), zip_code, '') as determin_zip_code,
           AEAD.ENCRYPT(KEYS.KEYSET_CHAIN(KMS_KEY, NON_DETERMINISTICALLY_KEY), zip_code,
                        '')                                                                       as non_determin_zip_code
    FROM access_control.table1;
END;

確定的暗号化関数ではDETERMINISTIC_ENCRYPT関数で暗号化を行い、非確定的暗号化関数ではAEAD.ENCRYPTで暗号化を行います。
両方の関数とも追加の認証データの指定は空文字にしています。

中身を見てみます。

> SELECT * FROM access_control.table2;
| customer_id | customer_name | zip_code | determin_zip_code | non_determin_zip_code |
| :--- | :--- | :--- | :--- | :--- |
| C003 | 田中 一郎 | 111-1111 | 0x01BFD44AE1871B3FF0AD3459083D22FE177A0DB9F42DF81CB81ED4D74A | 0x0162ECCA2B1542EEF468434283AAAE015841B87F4060DC4C4334AA84F21D9915A6BC233128D1D26B53 |
| C005 | 渡辺 健太 | 111-1111 | 0x01BFD44AE1871B3FF0AD3459083D22FE177A0DB9F42DF81CB81ED4D74A | 0x0162ECCA2BBDA7E3BD6C0E8D693F6B1630C674441F09618591BDCF52C7068AF9C01646E965858C520E |
| C001 | 佐藤 太郎 | 111-1111 | 0x01BFD44AE1871B3FF0AD3459083D22FE177A0DB9F42DF81CB81ED4D74A | 0x0162ECCA2B377B411339F70490D2C1A38F4744827DC1CBE849048C3757AEE2C4E233E62188C61D7B16 |
| C002 | 鈴木 花子 | 111-1111 | 0x01BFD44AE1871B3FF0AD3459083D22FE177A0DB9F42DF81CB81ED4D74A | 0x0162ECCA2BDFB1F1F0BC7330A9616F7E788F0566AF4D2BCF6CB307E9643C1B2603E21E7AADAC0D50D9 |
| C004 | 高橋 美香 | 111-1111 | 0x01BFD44AE1871B3FF0AD3459083D22FE177A0DB9F42DF81CB81ED4D74A | 0x0162ECCA2BC4C3917B0291ED238EDECC224B04E72A0ABBC66721E2050D6D52D9A90AF782E131E12CC0 |
| C006 | 小林 洋子 | 222-2222 | 0x01BFD44AE184052C372A28B63344B055C747B479F8A225B3CBFC26C2BB | 0x0162ECCA2BC1BECD2793980ED2EC2DD59D18AFC29CE4280F4C45CFCCDC38FFB17A3F8F4DF7D647E208 |
| C007 | 加藤 隆  | 222-2222 | 0x01BFD44AE184052C372A28B63344B055C747B479F8A225B3CBFC26C2BB | 0x0162ECCA2B4F1FD90AB9014528520EFB971DDA3A183BE162B6CCCCBC3E2CBC8FD631DC861267A5D5BB |
| C009 | 山本 健一 | 222-2222 | 0x01BFD44AE184052C372A28B63344B055C747B479F8A225B3CBFC26C2BB | 0x0162ECCA2BFD0B0151EC94A927F55AF4CA3694B0B21C4E739779121B2F1663FB1D7421871A3A8436C5 |
| C008 | 吉田 真理 | 222-2222 | 0x01BFD44AE184052C372A28B63344B055C747B479F8A225B3CBFC26C2BB | 0x0162ECCA2B38E14209406D09CD403DCA1D9337634107DBAD9DB0E709828F66D87BEB6DC06CABFF0BD2 |
| C010 | 中村 優子 | 222-2222 | 0x01BFD44AE184052C372A28B63344B055C747B479F8A225B3CBFC26C2BB | 0x0162ECCA2B3C709144BD3A1852A2DFE5A5D4FF81C24705BEDCBFED682CB2A00E7FC6E597D4D788D2D5 |

このような形で暗号化テキストに変換されています。
ここで注目したいのがdetermin_zip_codeとnon_determin_zip_codeの違いです。determin_zip_codeは郵便番号が111-1111のレコードでは0x01BFD44AE1871B3FF0AD3459083D22FE177A0DB9F42DF81CB81ED4D74A、222-2222のレコードでは0x01BFD44AE184052C372A28B63344B055C747B479F8A225B3CBFC26C2BBとなっている一方、non_determin_zip_codeは全てのレコードで別々の値になっています。これが確定的暗号化関数と非確定的暗号化関数の違いになります。同じ鍵セットと追加の認証データが同一であれば出力される暗号化テキストが同一になるのが確定的暗号化関数の特徴です。

では復号化を行ってみます。

> BEGIN
    DECLARE KMS_KEY STRING DEFAULT 'gcp-kms://projects/{プロジェクトID}/locations/asia-northeast1/keyRings/bq_col_encrypt_keyring/cryptoKeys/bq_col_encrypt_key';
    DECLARE DETERMINISTICALLY_KEY BYTES DEFAULT (SELECT key_set
                                                 FROM access_control.key_sets
                                                 WHERE id = "deterministically_key");
    DECLARE NON_DETERMINISTICALLY_KEY BYTES DEFAULT (SELECT key_set
                                                     FROM access_control.key_sets
                                                     WHERE id = "non_deterministically_key");
    SELECT customer_id,
           customer_name,
           zip_code,
           DETERMINISTIC_DECRYPT_STRING(KEYS.KEYSET_CHAIN(KMS_KEY, DETERMINISTICALLY_KEY), determin_zip_code,
                                        '') as determin_zip_code,
           AEAD.DECRYPT_STRING(KEYS.KEYSET_CHAIN(KMS_KEY, NON_DETERMINISTICALLY_KEY), non_determin_zip_code,
                               '')          as non_determin_zip_code
    from access_control.table2;
END; 
| customer_id | customer_name | zip_code | determin_zip_code | non_determin_zip_code |
| :--- | :--- | :--- | :--- | :--- |
| C001 | 佐藤 太郎 | 111-1111 | 111-1111 | 111-1111 |
| C004 | 高橋 美香 | 111-1111 | 111-1111 | 111-1111 |
| C002 | 鈴木 花子 | 111-1111 | 111-1111 | 111-1111 |
| C005 | 渡辺 健太 | 111-1111 | 111-1111 | 111-1111 |
| C003 | 田中 一郎 | 111-1111 | 111-1111 | 111-1111 |
| C010 | 中村 優子 | 222-2222 | 222-2222 | 222-2222 |
| C007 | 加藤 隆 | 222-2222 | 222-2222 | 222-2222 |
| C009 | 山本 健一 | 222-2222 | 222-2222 | 222-2222 |
| C008 | 吉田 真理 | 222-2222 | 222-2222 | 222-2222 |
| C006 | 小林 洋子 | 222-2222 | 222-2222 | 222-2222 |

確定的暗号化関数ではDETERMINISTIC_DECRYPT_STRING関数で暗号化を行い、非確定的暗号化関数ではAEAD.DECRYPT_STRINGで復号化を行います。

確定的暗号化関数の使い所

確定的暗号化関数の特徴である結合ができることを確かめてみます。
使用するのはユーザー情報を口座情報の2つのテーブルの結合でユーザー情報を口座情報の2つのテーブルを用意します。

> CREATE TABLE access_control.customer_details
(
    customer_id   STRING,
    customer_name STRING,
    acc_number    STRING
);
> CREATE TABLE access_control.account_details
(
    acc_number STRING,
    balance    NUMERIC,
    type       STRING
);
> INSERT INTO access_control.customer_details
    (customer_id, customer_name, acc_number)
VALUES ('C001', '佐藤 太郎', '1234567'),
       ('C002', '鈴木 花子', '2345678'),
       ('C003', '田中 一郎', '3456789'),
       ('C004', '高橋 美香', '4567890'),
       ('C005', '渡辺 健太', '5678901');
> INSERT INTO access_control.account_details
    (acc_number, balance, type)
-- 佐藤 太郎の口座
('1234567', 1000000.00, '普通預金'),
('1234567', 500000.00, '定期預金'),
('1234567', 1500000.00, '投資信託'),
('1234567', 300000.00, 'クレジットカード'),
('1234567', 20000000.00, '住宅ローン'),
('1234567', 2000000.00, 'カーローン'),

-- 鈴木 花子の口座
('2345678', 800000.00, '普通預金'),
('2345678', 1000000.00, '定期預金'),
('2345678', 2000000.00, '投資信託'),
...

このテーブルでは口座番号が暗号化されていないためこれを暗号化したテーブルを作成します。

> BEGIN
    DECLARE KMS_KEY STRING DEFAULT 'gcp-kms://projects/{プロジェクトID}/locations/asia-northeast1/keyRings/bq_col_encrypt_keyring/cryptoKeys/bq_col_encrypt_key';
    DECLARE KEY_SET BYTES DEFAULT (SELECT key_set FROM access_control.key_sets WHERE id = "deterministically_key");
    CREATE OR REPLACE TABLE access_control.enc_customer_details
    AS
    SELECT customer_id,
           customer_name,
           DETERMINISTIC_ENCRYPT(KEYS.KEYSET_CHAIN(KMS_KEY, KEY_SET), acc_number, '') as enc_acc_number
    FROM access_control.customer_details;
    CREATE OR REPLACE TABLE access_control.enc_account_details
    AS
    SELECT DETERMINISTIC_ENCRYPT(KEYS.KEYSET_CHAIN(KMS_KEY, KEY_SET), acc_number, '') as enc_acc_number,
           balance,
           type
    FROM access_control.account_details;
END;

中身を見てみます。

> SELECT * FROM access_control.enc_customer_details ORDER BY customer_id;
| customer_id | customer_name | enc_acc_number |
| :--- | :--- | :--- |
| C001 | 佐藤 太郎 | 0x01BFD44AE123E18E2A1AE2A7D4E07EC1393CF06836C15A622D3C50AE |
| C002 | 鈴木 花子 | 0x01BFD44AE1546D3B3A27DA79731546D317D992FDA34305102D28416A |
| C003 | 田中 一郎 | 0x01BFD44AE1B6533BB0FE27D7902C7645F950FD3E46E3F53763BE9D7C |
| C004 | 高橋 美香 | 0x01BFD44AE1B48FE6F29BC1B05E6383584D9DC00167F5CE0A395A7B73 |
| C005 | 渡辺 健太 | 0x01BFD44AE1C6AC4C991B619CE817BF524E75F7074A95A745F6553EEC |

> SELECT * FROM access_control.enc_account_details;
| enc_acc_number | balance | type |
| :--- | :--- | :--- |
| 0x01BFD44AE123E18E2A1AE2A7D4E07EC1393CF06836C15A622D3C50AE | 500000.000000000 | 定期預金 |
| 0x01BFD44AE123E18E2A1AE2A7D4E07EC1393CF06836C15A622D3C50AE | 1000000.000000000 | 普通預金 |
| 0x01BFD44AE123E18E2A1AE2A7D4E07EC1393CF06836C15A622D3C50AE | 1500000.000000000 | 投資信託 |
| 0x01BFD44AE123E18E2A1AE2A7D4E07EC1393CF06836C15A622D3C50AE | 2000000.000000000 | カーローン |
| 0x01BFD44AE123E18E2A1AE2A7D4E07EC1393CF06836C15A622D3C50AE | 300000.000000000 | クレジットカード |
| 0x01BFD44AE123E18E2A1AE2A7D4E07EC1393CF06836C15A622D3C50AE | 20000000.000000000 | 住宅ローン |
| 0x01BFD44AE1546D3B3A27DA79731546D317D992FDA34305102D28416A | 15000000.000000000 | 住宅ローン |
| 0x01BFD44AE1546D3B3A27DA79731546D317D992FDA34305102D28416A | 1000000.000000000 | 定期預金 |
| 0x01BFD44AE1546D3B3A27DA79731546D317D992FDA34305102D28416A | 200000.000000000 | クレジットカード |
| 0x01BFD44AE1546D3B3A27DA79731546D317D992FDA34305102D28416A | 2000000.000000000 | 投資信託 |
...

各ユーザーのaccount_numberを変換したenc_account_numberが各ユーザーごとに異なっています。
したがって、口座番号が暗号化テキストに置き換えられているテーブル同士でも結合して情報を取得することができるので口座情報に各ユーザーの情報を結合してクエリを実行することができます。

> SELECT customer_id, customer_name, balance, type
FROM access_control.enc_account_details a
         LEFT JOIN access_control.enc_customer_details c ON a.enc_acc_number = c.enc_acc_number
ORDER BY 1, 2, 3;
| customer_id | customer_name | balance | type |
| :--- | :--- | :--- | :--- |
| C001 | 佐藤 太郎 | 300000.000000000 | クレジットカード |
| C001 | 佐藤 太郎 | 500000.000000000 | 定期預金 |
| C001 | 佐藤 太郎 | 1000000.000000000 | 普通預金 |
| C001 | 佐藤 太郎 | 1500000.000000000 | 投資信託 |
| C001 | 佐藤 太郎 | 2000000.000000000 | カーローン |
| C001 | 佐藤 太郎 | 20000000.000000000 | 住宅ローン |
| C002 | 鈴木 花子 | 200000.000000000 | クレジットカード |
| C002 | 鈴木 花子 | 800000.000000000 | 普通預金 |
| C002 | 鈴木 花子 | 1000000.000000000 | 定期預金 |
| C002 | 鈴木 花子 | 2000000.000000000 | 投資信託 |
...

まとめ

BigQueryのCloud KMSによる列レベルの暗号化を使う場合に使用する確定的暗号化関数と非確定的暗号化関数を使ってみました。確定的暗号化はクラスタリングやSQLの実装時の集約・結合を行うことができるのでそのような要件がある場合はこちらの暗号化を行い、対して非確定的暗号化は全く別の暗号化テキストになるので極めてデータの分析や集計よりもセキュリティを優先するよ機密性の高いデータを扱う場合に適しています。

最後まで読んで頂いてありがとうございました。