Snowflakeの暗号化機能を試してみた
分析屋の中田(ナカタ)です。
Snowflakeの暗号化・復号化関数を試してみました。
暗号化・復号化とは
テーブルのデータを暗号化する機能です。
用途によって、専用の関数が4種類用意されています。
ENCRYPT()、DECRYPT()、ENCRYPT_RAW()、DECRYPT_RAW()
暗号化機能 | Snowflake Documentation
今回やること
暗号化・復号化用の関数をそれぞれ試してみます。
関数の使い方、注意点を探っていきます。
環境
Snowflakeのエディション：エンタープライズ版
クラウド：AWS（東京リージョン）
事前準備
以下の変数を用意します。
base_text：暗号化したい元文
pass_phrase：パスフレーズ。暗号化に必要な鍵
-- 原文を変数に格納
SET base_text = 'hello_world';
SELECT RANDSTR(32, RANDOM());
SET pass_phrase = 'bZj9P3J9dU9vaYcqBQEE1xiQCne6IzNA';2文目では、32文字のランダムな文字列を作り出しています。
表示された文字列を、3文目で変数に格納しています。
後に暗号化のアルゴリズムを決めるのですが、アルゴリズムによって鍵の長さに指定があります。
ここでは32文字としていますが、使用するアルゴリズムによって変更が必要です。
ENCRYPTとDECRYPTの検証
以下のクエリを実行、暗号化してみます。
SELECT ENCRYPT($base_text, $pass_phrase);実行結果
元文「hello_world」がヘンテコな文字列に暗号化されました。
ここで注意点ですが
ENCRYPT関数の暗号化は、鍵だけではなくSnowflake内部のシード値を使用しています。
シード値は刻々と変化しているため、暗号化の結果も変化していきます。
よって、実行結果の文字列とパスフレーズ（暗号化の鍵）が分かったとしても
1秒後には復号できないということになります。
暗号化しつつ復号化を同時に行う場合は以下のように書きます。
-- 暗号化＆復号化
SELECT TO_VARCHAR(
DECRYPT(
ENCRYPT($base_text, $pass_phrase)
,$pass_phrase)
,'utf-8')
;DECRYPT関数で復号化しています。
第１引数に暗号化された結果
第２引数にパスフレーズ
を指定しています。
実行すると元文が表示されます。
ENCRYPT_RAW と DECRYPT_RAWの検証
暗号化結果を保持して、一定時間経過後も復号できるようにしたい場合
ENCRYPT_RAW と DECRYPT_RAWを使用します。
-- 暗号化
WITH temp AS(
SELECT ENCRYPT_RAW(TO_BINARY(HEX_ENCODE($base_text),'hex')
,TO_BINARY(HEX_ENCODE($pass_phrase),'hex')
,NULL
,TO_BINARY(HEX_ENCODE('additional data'), 'hex')
,'AES-GCM'
) AS encr
)SELECT * FROM temp;
実行結果
実行文も結果も、急にややこしい感じになっちゃいました。
実行文について、暗号化した結果をWITH句の一時テーブルに入れたのは好みです。
ENCRYPT_RAW関数の引数について
ざっくりまとめると以下の通りです。
第１引数：暗号化したい元文（バイナリー型）
第２引数：パスフレーズ（バイナリー型）
第３引数：初期化ベクトル（NULLでもOK）
第４引数：認証済み追加データ（バイナリー型）
※復号化で使用するための合言葉のようなものです。
第５引数：暗号化アルゴリズム
ということでVARCHAR型からバイナリー型に変換する必要があります。
HEX_ENCODE関数で16進法に変換し、TO_BINARY関数でバイナリー型に変換しています。
実行結果はJSON形式で返ってきています。
ciphertext：暗号化した結果の文字列
iv：初期化ベクトル
tag：タグ
上記はそれぞれ、復号化で必要な情報です。
今度はWITH句の一時テーブルの情報を復号化してみます。
-- 暗号化
WITH temp AS(
SELECT ENCRYPT_RAW(TO_BINARY(HEX_ENCODE($base_text),'hex')
,TO_BINARY(HEX_ENCODE($pass_phrase),'hex')
,NULL
,TO_BINARY(HEX_ENCODE('additional data'), 'hex')
,'AES-GCM'
) AS encr
)
-- 復号化
SELECT HEX_DECODE_STRING(TO_VARCHAR(
DECRYPT_RAW(AS_BINARY(GET(encr, 'ciphertext'))
,TO_BINARY(HEX_ENCODE($pass_phrase),'hex')
,AS_BINARY(GET(encr, 'iv'))
,TO_BINARY(HEX_ENCODE('additional data'), 'HEX')
,'AES-GCM'
,AS_BINARY(GET(encr, 'tag'))
)))
FROM temp;実行結果
元文「hello_world」が復活しました！
DECRYPT_RAW関数の引数について
第１引数：暗号化した結果の文字列（バイナリー型）
第２引数：パスフレーズ（バイナリー型）
第３引数：初期化ベクトル（バイナリー型）
第４引数：認証済み追加データ（バイナリー型）
※暗号化した際の文字列と同じものを指定します。
第５引数：暗号化アルゴリズム
第６引数：タグ（バイナリー型）
ここでもバイナリー型に変換が必要です。
WITH句の暗号化した結果のJSON形式を利用するため
GET関数を使用しています。
GET関数は配列名、キー名の順で引数を指定して値を取得できます。
復号化した結果を文字列型に変換しますが、そのままでは16進法表記なので
HEX_DECODE_STRING関数で表記を元に戻しています。
ここまでの練習ではコード内にいろいろな情報を直打ち、（ハードコーディング）したり
一時テーブルや変数にそのまま格納したりしていますが公式ドキュメントでそれは避けましょうと記載されています。
*********************************************
ご用心
この例を簡略化するために、暗号化/復号化キーは、暗号化された値でテーブルから読み込まれます。これは安全ではありません。キーは、暗号化されたデータを保存するテーブルに、暗号化されていない値として 決して 保存しないでください。
*********************************************
最後に
引数が多く、慣れが必要ですが
個人情報や社外秘の情報を扱う際にはセキュリティ対策として必要な機能だと思います。
ここまでお読みいただき、ありがとうございました！
この記事が少しでも参考になりましたら「スキ」を押していただけると幸いです！
これまでの記事はこちら！
株式会社分析屋について
ホームページはこちら。
noteでの会社紹介記事はこちら。
【データ分析で日本を豊かに】
分析屋はシステム分野・ライフサイエンス分野・マーケティング分野の知見を生かし、多種多様な分野の企業様のデータ分析のご支援をさせていただいております。 「あなたの問題解決をする」をモットーに、お客様の抱える課題にあわせた解析・分析手法を用いて、問題解決へのお手伝いをいたします！
【マーケティング】
マーケティング戦略上の目的に向けて、各種のデータ統合及び加工ならびにPDCAサイクル運用全般を支援や高度なデータ分析技術により複雑な課題解決に向けての分析サービスを提供いたします。
【システム】
アプリケーション開発やデータベース構築、WEBサイト構築、運用保守業務などお客様の問題やご要望に沿ってご支援いたします。
【ライフサイエンス】
機械学習や各種アルゴリズムなどの解析アルゴリズム開発サービスを提供いたします。過去には医療系のバイタルデータを扱った解析が主でしたが、今後はそれらで培った経験・技術を工業など他の分野の企業様の問題解決にも役立てていく方針です。
【SES】
SESサービスも行っております。