Zenn
❄️

Snowflake で暗号化したデータを他の DBMS で復号する (and vice versa)

2020/12/17に公開
MySQL
PostgreSQL
SQL
Snowflake
Oracle
tech

この記事は Snowflake Advent Calendar 2020 の 17 日目です。

こんにちは、Snowflake でサポートエンジニアをやっている @indigo13love です。

Snowflake と他の DBMS を連携する際に、データを暗号化してやり取りしたいというニーズがあるかもしれません。

Snowflake では AES (128-bit, 192-bit, 256-bit) でのデータの暗号化/復号に対応しているので、今回はこの機能を使って Snowflake 以外の DBMS との暗号化/復号をする方法を解説します。

(本記事は所属する組織の公式見解ではなく、個人的なプラクティスの共有になります。)

Snowflake で暗号化→外部で復号

ENCRYPT() と ENCRYPT_RAW()

Snowflake には ENCRYPT()/DECRYPT() と ENCRYPT_RAW()/DECRYPT_RAW() の 2 種類の暗号化/復号関数があります。

ENCRYPT()/DECRYPT() はパスフレーズで直接暗号化/復号するのではなく、PBKDF2 で Snowflake 内部のシードを使ってキー導出 (Key Derivation) した結果のキーを使うため、Snowflake 外部で復号させたり、外部で暗号化されたデータを復号したりする用途には使用できません。

https://docs.snowflake.com/ja/sql-reference/functions/encrypt.html

パスフレーズは、入力の暗号化/復号化に直接使用されません。代わりに、パスフレーズは、同じパスフレーズに対して常に同じである暗号化/復号化キーを取得するために使用されます。Snowflakeは、https://en.wikipedia.org/wiki/PBKDF2 キー派生関数とSnowflake内部のシードを使用して、指定されたパスフレーズから暗号化/復号化キーを計算します。

こうしたキーの導出が原因のため、以下に対して暗号化/復号化機能は使用できません。

・ 外部で暗号化されたデータの復号化。
・ 外部で復号化されるデータの暗号化。

これらのいずれかを実行するには、 ENCRYPT_RAW または DECRYPT_RAW を使用します。

https://en.wikipedia.org/wiki/PBKDF2

そのため、今回の例では ENCRYPT_RAW() を使用して暗号化していきます。

Snowflake で暗号化する

まずは元データを用意します。テーブルには 7 人の名前が格納されています。

create or replace table t1 (name varchar) as
select * from values ('Mell'), ('Chico'), ('Aro'), ('Marin'), ('Poco'), ('Lou'), ('Nina');

Mell
Chico
Aro
Marin
Poco
Lou
Nina

さっそくこのデータを ENCRYPT_RAW() で暗号化していきましょう。

ENCRYPT_RAW() は 5 つの引数を持つ、やや複雑な関数です。

https://docs.snowflake.com/ja/sql-reference/functions/encrypt_raw.html

ENCRYPT_RAW( <value_to_encrypt> , <key> , <iv> ,
         [ [ <additional_authenticated_data> , ] <encryption_method> ]
       )

ENCRYPT_RAW() がどう動作するかを理解するために、それぞれの引数を説明していきます。

<value_to_encrypt>

<value_to_encrypt> は暗号化されるデータです。今回は t1 テーブルのカラムになりますが、注意点として、暗号化されるデータはバイナリ(BINARY 型)である必要があります。

Snowflake での VARCHAR → BINARY 変換の定石は、

  1. HEX_ENCODE(str) で 16 進数文字列に変換
  2. TO_BINARY(hexstr, 'hex') でバイナリに変換

となるので、今回もこれで元データを変換してあげます。

<key>

<key> は、暗号化キーです。これもバイナリである必要があります。今回は適当に RANDSTR() で生成した文字列をパスフレーズとして使用し、これを HEX_ENCODE() + TO_BINARY() でバイナリに変換します。

select randstr(32, random());

flrFrxbYBpPqOXddl3hLhzsIsxFqJ5hy

なぜ 32 文字のパスフレーズにしたかというと、ENCRYPT_RAW() はアルゴリズムのキー長を指定することができず、与えられたキーの長さから自動的に判別してアルゴリズムのキー長を設定するからです。

今回は AES 256 (256-bit) が使用したかったため、キー長も 32-byte (= 256-bit) にしています。

<iv>

<iv> は初期化ベクタ (Initialization Vector / IV) で、暗号化/復号をする際のシードのようなものです。暗号化側と復号側で IV が一致していないと、正常に復号することができません。

ENCRYPT_RAW() は <iv> 引数がない場合、IV をランダムに生成して、生成した IV を結果として返してくれるので、基本的に NULL で問題ないです。

<additional_authenticated_data>

<additional_authenticated_data> は GCM などのブロック暗号モードで用いられる追加認証データ (Additional Authenticated Data / AAD) で、この AAD 自体は暗号化されず、復号時に追加で要求される情報として使用されます。

今回は Oracle の DBMS_CRYPTO.DECRYPT() との互換性のために CBC をブロック暗号モードとして使用するため、AAD も NULL にしています。

<encryption_method>

<encryption_method> は、下記のような表記で、暗号化に使うアルゴリズム、ブロック暗号モード、パディングの指定をします。

<algorithm>-<mode> [ /pad: <padding> ]

今回は AES (32-byte キーにより AES 256)、CBC、PKCS (PKCS#5/PKCS#7) をそれぞれ指定したいので、'AES-CBC' を指定します。パディングは PKCS (デフォルト) か NONE しかないので、明示的な指定なしで問題ないです。

返り値

ENCRYPT_RAW() は VARIANT 型、つまり JSON オブジェクトを返します。

このオブジェクトは "ciphertext" と "iv" の 2 つのプロパティを持っており、それぞれ暗号化されたデータと IV の 16 進数文字列になります。

{
  "ciphertext": "5F561A1BC9C816511592CD95B9231DC9",
  "iv": "E1AE9C0844F046C4E72F49B0366216BC"
}

外部に持ち出して復号する場合は、暗号化データだけでなく IV もないと復号できないため、必ずどちらも保管するようにしてください。もしくは IV に固定の値を与えて、それを外部で保管するという手段もあります。

上記を踏まえて、暗号化されたデータ (name_encr) と IV (iv) を格納したテーブル t1_encr を、下記のような感じで作成していきます。

create or replace table t1_encr (name_encr varchar, iv varchar) as
with c as (
    select encrypt_raw(
        to_binary(hex_encode(name), 'hex'),
        to_binary(hex_encode('flrFrxbYBpPqOXddl3hLhzsIsxFqJ5hy'), 'hex'),
        null,
        null,
        'AES-CBC'
    ) encr
    from t1
)
select
    encr:ciphertext::varchar ciphertext,
    encr:iv::varchar iv
from c;

NAME_ENCR                               IV
5F561A1BC9C816511592CD95B9231DC9        E1AE9C0844F046C4E72F49B0366216BC
1D8A4A972616ED6DF5D1A98E4DE2728B        E1AE9C0844F046C4E72F49B0366216BC
8F9BF1A94E9DA51B79AEFF4AF77CC737        E1AE9C0844F046C4E72F49B0366216BC
64808B5A7B76A994418BFAAE5DE913E3        E1AE9C0844F046C4E72F49B0366216BC
31AAAE5E6EF7A8F5E26C18D08544791C        E1AE9C0844F046C4E72F49B0366216BC
87E014560F74967431DF2C542A3FC26C        E1AE9C0844F046C4E72F49B0366216BC
FDD2F502A62BB267B5EE84F503DC70F6        E1AE9C0844F046C4E72F49B0366216BC

今回はこれを CSV でエクスポートして、いろいろな DBMS に持ち込んで復号してみます。

Oracle Database で復号する

まずは Oracle Database で復号してみましょう。

検証環境は、下記のリポジトリと Docker Compose を使って構築/起動した Oracle Database 18c Express Edition を使用します。

https://github.com/bjarteb/oracle-apex-ords

まずログインしてコピー先のテーブルを作成します。

sqlplus sys/oracle@localhost:1521/XE as sysdba

create table t1_encr (name_encr varchar2(32), iv varchar2(32));

データのロードはいろいろ試行錯誤した結果何もうまくいかなかったので、Oracle SQL Developer を使ってみたら超簡単でした。

https://www.oracle.com/jp/tools/technologies/sql-developer.html

下記のドキュメントが参考になります。

https://docs.oracle.com/cd/E57425_01/121/ADMQS/GUID-7068681A-DC4C-4E09-AC95-6A5590203818.htm

あとは DBMS_CRYPTO.DECRYPT() 関数で復号するだけです。

https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_CRYPTO.html#GUID-7A041A3D-439B-4F7C-9FB9-8BC93E6DCC6A

DBMS_CRYPTO.DECRYPT() は、暗号化されたデータ (src)、暗号化方式 (typ)、暗号化キー (key)、IV (iv) の 4 引数を取ります。

src, key, iv は特に難しいことはなく、注意点としては、こちらもバイナリ (RAW 型) と取ることぐらいです。

テーブルに格納されている name_encr と iv は 16 進数文字列で、これは Oracle では RAW 型への暗黙の型変換が効くので、そのまま渡してしまって問題ないです。

key はパスフレーズが普通の文字列なので、UTL_I18N.STRING_TO_RAW() を使って、文字列から RAW 型に変換してあげます。

ここで超難しいのが　typ で、これは暗号化方式を数値で取ります。

DBMS_CRYPTO.ENCRYPT_AES256 のような定義済みの定数もあるのですが、これらは PL/SQL のストアドプロシージャ内部でしか使用できないため、純粋に関数として使用したい場合、マジックナンバーを渡してあげる必要があります。

https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_CRYPTO.html#GUID-CE3CF17D-E781-47CB-AEE7-19A9B2BCD3EC

インラインでさくっと PL/SQL を書いて、DBMS_CRYPTO.ENCRYPT_AES256, DBMS_CRYPTO.CHAIN_CBC, DBMS_CRYPTO.PAD_PKCS5 がそれぞれどんな値なのか確認してみます。

set serveroutput on
begin
dbms_output.put_line(dbms_crypto.encrypt_aes256 || '+' || dbms_crypto.chain_cbc || '+' || dbms_crypto.pad_pkcs5);
end;
/
8+256+4096

PL/SQL procedure successfully completed.

それぞれ、8, 256, 4096 だということがわかったので、typ には 8+256+4096 を渡せばいいことがわかりました。

上記を踏まえて DBMS_CRYPTO.DECRYPT() を呼んだ結果が下記となります。

set pagesize 1000
set linesize 1000
col name format a5

select
    name_encr,
    iv,
    utl_i18n.raw_to_char(dbms_crypto.decrypt(
        name_encr,
        8+256+4096,
        utl_i18n.string_to_raw('flrFrxbYBpPqOXddl3hLhzsIsxFqJ5hy', 'AL32UTF8'),
        iv
      ),
      'AL32UTF8'
   ) name
from t1_encr;

NAME_ENCR			 IV				  NAME
-------------------------------- -------------------------------- -----
5F561A1BC9C816511592CD95B9231DC9 E1AE9C0844F046C4E72F49B0366216BC Mell
1D8A4A972616ED6DF5D1A98E4DE2728B E1AE9C0844F046C4E72F49B0366216BC Chico
8F9BF1A94E9DA51B79AEFF4AF77CC737 E1AE9C0844F046C4E72F49B0366216BC Aro
64808B5A7B76A994418BFAAE5DE913E3 E1AE9C0844F046C4E72F49B0366216BC Marin
31AAAE5E6EF7A8F5E26C18D08544791C E1AE9C0844F046C4E72F49B0366216BC Poco
87E014560F74967431DF2C542A3FC26C E1AE9C0844F046C4E72F49B0366216BC Lou
FDD2F502A62BB267B5EE84F503DC70F6 E1AE9C0844F046C4E72F49B0366216BC Nina

7 rows selected.

DBMS_CRYPTO.DECRYPT() の返り値は RAW 型なので、UTL_I18N.RAW_TO_CHAR() (なぜ STRING_TO_RAW() と非対称……) で文字列に変換してあげることで、無事元のデータが復号できました。

MySQL で復号する

次に MySQL で復号してみます。

検証環境は、普通に MySQL Community Downloads から macOS 版のインストーラで 8.0.21 をインストールしました。

https://dev.mysql.com/downloads/mysql/

CSV は LOAD DATA LOCAL INFILE でロードします。MySQL 8.0 からクライアント/サーバ両方で local_infile システム変数を 1 に設定する必要があるので注意してください。

$ mysql --local-infile=1

create table t1_encr (name_encr varchar(32), iv varchar(32));

set global local_infile = 1;

load data local infile '/Users/ymatsuzaki/demo/t1_encr.csv'
into table t1_encr fields terminated by ',' ignore 1 lines;

MySQL では AES_DECRYPT() 関数が AES の復号関数になります。また、アルゴリズムとブロック暗号モードは関数の引数ではなく block_encryption_mode システム変数で指定します。

AES_DECRYPT() は Snowflake の DECRYPT_RAW() や Oracle の DBMS_CRYPTO.DECRYPT() とほぼ同じような使い方ですが、パスフレーズを文字列で受ける(バイナリに変換する必要がない)のが特殊です。

暗号化データと IV はバイナリ文字列で受けるので、16 進数文字列である name_encr と iv を UNHEX() 関数でバイナリ文字列に変換して渡します。

set block_encryption_mode = 'aes-256-cbc';

select
    name_encr,
    iv,
    cast(
        aes_decrypt(
	    unhex(name_encr),
	    'flrFrxbYBpPqOXddl3hLhzsIsxFqJ5hy',
	    unhex(iv)
	)
	as char(6)
    ) name
from t1_encr;

+----------------------------------+----------------------------------+-------+
| name_encr                        | iv                               | name  |
+----------------------------------+----------------------------------+-------+
| 5F561A1BC9C816511592CD95B9231DC9 | E1AE9C0844F046C4E72F49B0366216BC | Mell  |
| 1D8A4A972616ED6DF5D1A98E4DE2728B | E1AE9C0844F046C4E72F49B0366216BC | Chico |
| 8F9BF1A94E9DA51B79AEFF4AF77CC737 | E1AE9C0844F046C4E72F49B0366216BC | Aro   |
| 64808B5A7B76A994418BFAAE5DE913E3 | E1AE9C0844F046C4E72F49B0366216BC | Marin |
| 31AAAE5E6EF7A8F5E26C18D08544791C | E1AE9C0844F046C4E72F49B0366216BC | Poco  |
| 87E014560F74967431DF2C542A3FC26C | E1AE9C0844F046C4E72F49B0366216BC | Lou   |
| FDD2F502A62BB267B5EE84F503DC70F6 | E1AE9C0844F046C4E72F49B0366216BC | Nina  |
+----------------------------------+----------------------------------+-------+
7 rows in set (0.00 sec)

AES_DECRYPT() の返り値はバイナリ文字列なので、CHAR にキャストしてあげることで、こちらも無事元のデータが復号できました。

PostgreSQL で復号する

最後に PostgreSQL で復号してみます。

検証環境は、下記の記事を参考に docker run で PostgreSQL 12.5 を起動して使用します。

https://qiita.com/hoto17296/items/070857971f95017b5e07

$ docker run --rm -d -p 15432:5432 -v postgres-tmp:/var/lib/postgresql/data -e POSTGRES_HOST_AUTH_METHOD=trust postgres:12-alpine
...
$ psql -h127.0.0.1 -p15432 -Upostgres

PostgreSQL では、外部で暗号化されたデータの復号をする機能は DECRYPT_IV() 関数によって提供されますが、この関数は pgcrypto 拡張に入っているので、まずはこれを CREATE EXTENSION で有効化します。

create extension pgcrypto

その後、テーブルを作成し psql の \copy コマンドを使用して、CSV をロードします。

psql の \copy と PostgreSQL 本体の COPY は文法もほぼ共通で、同じような機能を提供しますが、COPY がサーバ上からファイルをロードするのに対して、\copy はローカルからファイルをロードします。つまり、MySQL における LOAD DATA INFILE と LOAD DATA LOCAL INFILE の違いと同じです。

create table t1_encr(name_encr varchar(32), iv varchar(32));

\copy t1_encr from '/Users/ymatsuzaki/demo/t1_encr.csv' csv header;

ロードしたら DECRYPT_IV() で復号していきます。

DECRYPT_IV() も AES_DECRYPT() や DBMS_CRYPTO.DECRYPT() とほぼ同じ構造なので、ここまでやってきたことを踏まえれば余裕です。引数は暗号化データ、暗号化キー、IV、暗号化方式の順に取ります。

暗号化データ、暗号化キー、IV はすべてバイト列 (BYTEA 型) で取ります。16 進数文字列である name_encr と iv は DECODE() 関数でバイト列に変換します。普通の文字列であるパスフレーズは、シンプルに BYTEA 型にキャストするだけでバイト列に変換できます。

DECRYPT_IV() も Snowflake の ENCRYPT_RAW()/DECRYPT_RAW() と同じく AES のキー長を自動で判別してアルゴリズムのキー長を選択するので、暗号化法には 'aes-cbc' を指定します。

select
    name_encr,
    iv,
    encode(
        decrypt_iv(
            decode(name_encr, 'hex'),
            'flrFrxbYBpPqOXddl3hLhzsIsxFqJ5hy'::bytea,
            decode(iv, 'hex'),
            'aes-cbc'
        ),
        'escape'
    ) as name
from t1_encr;

            name_encr             |                iv                | name
----------------------------------+----------------------------------+-------
 5F561A1BC9C816511592CD95B9231DC9 | E1AE9C0844F046C4E72F49B0366216BC | Mell
 1D8A4A972616ED6DF5D1A98E4DE2728B | E1AE9C0844F046C4E72F49B0366216BC | Chico
 8F9BF1A94E9DA51B79AEFF4AF77CC737 | E1AE9C0844F046C4E72F49B0366216BC | Aro
 64808B5A7B76A994418BFAAE5DE913E3 | E1AE9C0844F046C4E72F49B0366216BC | Marin
 31AAAE5E6EF7A8F5E26C18D08544791C | E1AE9C0844F046C4E72F49B0366216BC | Poco
 87E014560F74967431DF2C542A3FC26C | E1AE9C0844F046C4E72F49B0366216BC | Lou
 FDD2F502A62BB267B5EE84F503DC70F6 | E1AE9C0844F046C4E72F49B0366216BC | Nina
 (7 行)

DECRYPT_IV() の返り値は例によって例のごとくバイト列なので、ENCODE() 関数で文字列に変換してあげることで、こちらでも無事元のデータが復号できました。

外部で暗号化→Snowflake で復号

Snowflake で暗号化したデータを Oracle, MySQL, PostgreSQL で復号できることが確認できたので、今度は逆にこれらの DBMS で暗号化したデータを Snowflake で復号してみます。

暗号化関数の使い方はほぼ復号関数と同じなので、説明は省いていきます。

Oracle Database で暗号化する

下記のような感じで DBMS_CRYPTO.ENCRYPT() を呼びます。

create table t1_encr2 (name_encr, iv) as
with iv as (
    select /*+ materialize */ dbms_crypto.randombytes(16) iv from dual
)
select
    rawtohex(dbms_crypto.encrypt(
        utl_i18n.string_to_raw(name, 'AL32UTF8'),
        8+256+4096,
        utl_i18n.string_to_raw('flrFrxbYBpPqOXddl3hLhzsIsxFqJ5hy', 'AL32UTF8'),
        iv
      )
   ) name_encr,
   rawtohex(iv) iv
from t1, iv;

ほぼ DBMS_CRYPTO.DECRYPT() のときと同じですが、後の取り回しのために RAWTOHEX() で16 進数文字列に変換して出力しておきます。

また Snowflake と違って IV の自動生成機能がないので、CTE (WITH 句) でランダムなバイナリを生成して IV として使用します。

ここで注意しなければならないのが、Oracle の CTE は必ずマテリアライズされるわけではないことです。マテリアライズされない場合、インラインビュー、つまりカラムの呼び出しごとに DBMS_CRYPTO.RANDOMBYTES() が呼ばれ、別の値を生成してしまいます。

そうなると、iv 列に格納されている値と、実際に暗号化に使われた IV の値が別物になってしまうので、復号に失敗することになります。私もこれで数分ハマったので、/*+ materialize */ ヒントを使って、必ずマテリアライズされるようにしましょう。

完成した t1_encr2 はこんな感じです。

NAME_ENCR			 IV
-------------------------------- --------------------------------
1C1B17FE1F256B7B067C3795A61CAC78 C56B7E411B2DD980A995137E36217FFD
F75A0A05003818B0D8D0EE01BB9269B0 C56B7E411B2DD980A995137E36217FFD
78E960E22A51675A11112B8AF9039C03 C56B7E411B2DD980A995137E36217FFD
1EF5C6DB4C0F21A856F47FF00D8EE0B4 C56B7E411B2DD980A995137E36217FFD
D97DEA47479DA1D4A061DBDA2D3605C7 C56B7E411B2DD980A995137E36217FFD
D7788495890D683B4FE8B9FB8CE9AE86 C56B7E411B2DD980A995137E36217FFD
9598E10A73ACD9AF2E4CCEEF0E910E53 C56B7E411B2DD980A995137E36217FFD

これを SQL Developer でヘッダーなし、クォーテーションなしの CSV としてエクスポートしておきます。

MySQL で暗号化する

同じく、下記のような感じで AES_ENCRYPT() を呼びます。

create table t1_encr2 (iv varchar(100), name_encr varchar(100)) as
with iv as (
    select random_bytes(16) iv
)
select
    hex(aes_encrypt(
        name,
        'flrFrxbYBpPqOXddl3hLhzsIsxFqJ5hy',
        iv
    )) name_encr,
    hex(iv) iv
from t1, iv;

AES_ENCRYPT() の暗号化対象データは VARCHAR のままで大丈夫です。

MySQL の CTE は常に 1 回だけマテリアライズされるので、そのまま使って問題ありません。

https://mysqlserverteam.com/mysql-8-0-improved-performance-with-cte/

できあがった t1_encr2 はこんな感じです。

+----------------------------------+----------------------------------+
| name_encr                        | iv                               |
+----------------------------------+----------------------------------+
| 3A91FF80D3AA8EA5328BF4636A12185A | 7D070FD3A6D14B883F8FACCDDC49DA16 |
| B176D8D5CCE0E13EC23EB7F199E4932A | 7D070FD3A6D14B883F8FACCDDC49DA16 |
| 2E1FA5BB6E5348C802BE1F4F12838CD1 | 7D070FD3A6D14B883F8FACCDDC49DA16 |
| 741EF3FF5A197A6DC87AE9EC9FE6D7B8 | 7D070FD3A6D14B883F8FACCDDC49DA16 |
| 00A93FA81E412B6DDEB063F75B7D7477 | 7D070FD3A6D14B883F8FACCDDC49DA16 |
| 7CC14DD99CBA4974390531B592065CDC | 7D070FD3A6D14B883F8FACCDDC49DA16 |
| A7C2EBFAA0354B3C6773CA2E7F0FADA5 | 7D070FD3A6D14B883F8FACCDDC49DA16 |
+----------------------------------+----------------------------------+
7 rows in set (0.00 sec)

mysqld がローカルで起動しているので SELECT INTO OUTFILE でエクスポートしてもいいのですが、--secure-file-priv がめんどくさいので mysql コマンドでエクスポートします。

mysql -NB -e "select * from d1.t1_encr2"|gsed 's/\t/,/' > /Users/ymatsuzaki/demo/t1_encr-mysql.csv

PostgreSQL で暗号化する

下記のような感じで ENCRYPT_IV() を呼びます。

create table t1_encr2 as
with iv as (
    select digest(random()::text::bytea, 'md5') as iv
)
select
    encode(
        encrypt_iv(
            name::bytea,
            'flrFrxbYBpPqOXddl3hLhzsIsxFqJ5hy'::bytea,
            iv,
            'aes-cbc'
        ),
        'hex'
    ) as name_encr,
    encode(iv, 'hex') iv
from t1, iv;

これも特にひっかかる部分はなさそうです。

できあがった t1_encr2 は下記のような感じです。

----------------------------------+----------------------------------
 6073f0818070550b46fbcf4117420d10 | 79a85d1a01c618d922b70e71415e4d1c
 23c53f883c81db07b0453191d411d57b | 79a85d1a01c618d922b70e71415e4d1c
 bf2eb5c6ebee217c79753371c466e92b | 79a85d1a01c618d922b70e71415e4d1c
 72cb6dbaddb980ea7c7eb940872d6cde | 79a85d1a01c618d922b70e71415e4d1c
 27ccb737343da33a71650f4899a0192f | 79a85d1a01c618d922b70e71415e4d1c
 2e14d363f17e2e9c826a1e3f6e73a1de | 79a85d1a01c618d922b70e71415e4d1c
 e9fdad08e6c71fc11af5ee9b67d0cc3e | 79a85d1a01c618d922b70e71415e4d1c
(7 行)

これを \copy でエクスポートしておきます。

\copy t1_encr2 to '/Users/ymatsuzaki/demo/t1_encr-postgres.csv' csv;

Snowflake で復号する

さて、ようやく暗号化データが出揃ったので、さっそく Snowflake にロードして復号してみましょう。

データのロードは内部ステージを作って PUT で投入してから COPY でテーブルにロードしていきます。

create or replace table t1_encr2 (name_encr varchar, iv varchar);

create or replace stage stage_encr
file_format = (type = csv field_optionally_enclosed_by = '"');

put file:///Users/ymatsuzaki/demo/t1_encr-postgres.csv @stage_encr;
put file:///Users/ymatsuzaki/demo/t1_encr-mysql.csv @stage_encr;
put file:///Users/ymatsuzaki/demo/t1_encr-oracle.csv @stage_encr;

copy into t1_encr2 from @stage_encr;

今回はすべてのファイルが同じフォーマットなので、まとめて同じテーブルに突っ込みます。

これを DECRYPT_RAW() で復号します。引数の取り方は ENCRYPT_RAW() と同じです。

create or replace table t1_decr (name varchar) as
select
    hex_decode_string(decrypt_raw(
        to_binary($1, 'hex'),
        to_binary(hex_encode('flrFrxbYBpPqOXddl3hLhzsIsxFqJ5hy'), 'hex'),
        to_binary($2, 'hex'),
        null,
        'AES-CBC'
    )::varchar)
from @stage_encr;

少しトリッキーなのが返り値の処理で、DECRYPT_RAW() はバイナリを返すので、これを文字列に変換する必要があります。

バイナリは VARCHAR にキャストすることで 16 進数文字列に変換されるので、これを HEX_DECODE_STRING() で文字列にデコードすれば OK です。

select name, count(*) from t1_decr group by name;

+-------+----------+
| NAME  | COUNT(*) |
|-------+----------|
| Chico |        3 |
| Lou   |        3 |
| Mell  |        3 |
| Aro   |        3 |
| Marin |        3 |
| Poco  |        3 |
| Nina  |        3 |
+-------+----------+
7 Row(s) produced. Time Elapsed: 0.524s

無事すべてのデータが復号できました。

まとめ

今回は用意しやすい環境として、Oracle, MySQL, PostgreSQL で試してみましたが、お察しの通り、たまたまこれらの DBMS だけが Snowflake と互換性を持っていたわけではありません。

共通の暗号アルゴリズムとブロック暗号モードに対応していて、かつ IV を使用したデータの暗号化/復号関数を持っている環境間であれば、相互に暗号化されたデータのやり取りをして、手元で復号することができます。

データレベルの暗号化はなかなか使いどころが難しい機能ではありますが、要件として必要になることもあるかと思いますので、そんなときの参考になったらいいなと思います。

Discussion