ストアドファンクションを使った暗号鍵の守り方

2023年4月20日

この記事は最終更新から1年以上経過しています。内容が古くなっている可能性があります。

ストアドファンクションを使った暗号鍵の守り方

アプリケーションの中でDBにセンシティブなデータの保存を行う際に、セキュリティの観点から暗号化を行いたい場面は出てきます。

暗号化自体は様々なプログラミング言語のライブラリでサポートされているので、実装そのものはあまり難しくありません。ただし暗号化を行う際に問題となるのが暗号鍵の扱いです。

言うまでもなくプログラムの中に暗号鍵をハードコーディングを行うことは避けるべきですし、設定ファイル等でプログラムの外で管理する場合も注意が必要です。

もしアプリケーションサーバーがサイバー攻撃により乗っ取られてしまうような最悪の場合には、暗号鍵まで流出してしまう恐れも出てきます。そのため暗号鍵をどこに保管して、どこで暗号化/復号化を行うのかはアプリケーションが要求する条件によって異なります。

MySQLにも暗号化関数が実装されているので、ストアドファンクションを用いてアプリケーション側に暗号鍵を持たせない方法でデータベースに保存するデータの暗号化/復号化を行う手法を紹介したいと思います。

暗号化データの保存方法

アプリ用のDB接続ユーザーにはアクセスできない場所に暗号鍵を保管して、ストアドファンクションに設定されたユーザーの権限で暗号鍵を読み取って暗号化/復号化を行います。

アプリケーション用データベース & 暗号化データ保存テーブル

CREATE DATABASE app;

USE app;

CREATE TABLE encrypt_data_store (
  id int NOT NULL,     /* データ保存用キーID */
  key_id int NOT NULL, /* 暗号鍵ID */
  encypted_data BLOB NOT NULL, /* 暗号化済みデータ本体 */
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE DATABASE app;

USE app;

CREATE TABLE encrypt_data_store (

id int NOT NULL, /* データ保存用キーID */

key_id int NOT NULL, /* 暗号鍵ID */

encypted_data BLOB NOT NULL, /* 暗号化済みデータ本体 */

PRIMARY KEY (id)

) ENGINE=InnoDB;

暗号化したデータをこのテーブルに保存します。

アプリケーション用DB接続ユーザーの作成

CREATE USER 'app'@'%'
  IDENTIFIED BY '##_Example_Password_00';

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON app.*
  TO 'app'@'%';

CREATE USER 'app'@'%'

IDENTIFIED BY '##_Example_Password_00';

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON app.*

TO 'app'@'%';

アプリケーション用のDB接続ユーザーとして、appユーザーを作成してappデータベースに対して通常のアプリケーションで必要なSELECT,INSERT,UPDATE,DELETE権限のほかにストアドファンクションを実行できるEXECUTE権限も与えます。

暗号鍵保存用データベース & テーブル作成

CREATE DATABASE key_store;

USE key_store;

CREATE TABLE key_store (
  key_id int NOT NULL,
  secret_key varbinary(255) NOT NULL,
  iv varbinary(255) NOT NULL,
  PRIMARY KEY (key_id)
) ENGINE=InnoDB;

CREATE DATABASE key_store;

USE key_store;

CREATE TABLE key_store (

key_id int NOT NULL,

secret_key varbinary(255) NOT NULL,

iv varbinary(255) NOT NULL,

PRIMARY KEY (key_id)

) ENGINE=InnoDB;

暗号鍵の保管用なのでアクセス権の付与には十分注意を払ってください。

暗号化/復号化ストアドファンクション専用ユーザーの作成

CREATE USER 'encrypt_key_reader'@'localhost'
  IDENTIFIED WITH mysql_no_login;

GRANT SELECT ON key_store.key_store
  TO 'encrypt_key_reader'@'localhost';
GRANT EXECUTE ON app.*
  TO 'encrypt_key_reader'@'localhost';

CREATE USER 'encrypt_key_reader'@'localhost'

IDENTIFIED WITH mysql_no_login;

GRANT SELECT ON key_store.key_store

TO 'encrypt_key_reader'@'localhost';

GRANT EXECUTE ON app.*

TO 'encrypt_key_reader'@'localhost';

今回作成するストアドファンクション専用ユーザーはログインする必要がないため、mysql_no_loginプラグインを使用しています。

key_storeテーブルの読み取り権限とappデータベースでストアドファンクションを実行する権限のみを与えます。

暗号化/復号化ストアドファンクション作成

USE app;

DELIMITER //

/* 暗号化関数 */
CREATE 
  DEFINER = 'encrypt_key_reader'@'localhost'
FUNCTION  
  encrypt_func(plain_text TEXT, key_id INT)
RETURNS 
  BLOB
  DETERMINISTIC
  READS SQL DATA
BEGIN
    DECLARE secret_key BLOB;
    DECLARE iv BLOB;

    SELECT key_store.secret_key, key_store.iv INTO secret_key, iv FROM key_store.key_store WHERE key_store.key_id = key_id;
    IF secret_key IS NULL THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Key ID not found';
    END IF;
    RETURN AES_ENCRYPT(plain_text, secret_key, iv);
END;
//

/* 復号化関数 */
CREATE 
  DEFINER = 'encrypt_key_reader'@'localhost'
FUNCTION  
  decrypt_func(encypt_data BLOB, key_id INT)
RETURNS 
  TEXT
  DETERMINISTIC
  READS SQL DATA
BEGIN
    DECLARE secret_key BLOB;
    DECLARE iv BLOB;

    SELECT key_store.secret_key, key_store.iv INTO secret_key, iv FROM key_store.key_store WHERE key_store.key_id = key_id;
    IF secret_key IS NULL THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Key ID not found';
    END IF;
    RETURN AES_DECRYPT(encypt_data, secret_key, iv);
END;
//
DELIMITER ;

USE app;

DELIMITER //

/* 暗号化関数 */

CREATE

DEFINER = 'encrypt_key_reader'@'localhost'

FUNCTION

encrypt_func(plain_text TEXT, key_id INT)

RETURNS

BLOB

DETERMINISTIC

READS SQL DATA

BEGIN

DECLARE secret_key BLOB;

DECLARE iv BLOB;

SELECT key_store.secret_key, key_store.iv INTO secret_key, iv FROM key_store.key_store WHERE key_store.key_id = key_id;

IF secret_key IS NULL THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'Key ID not found';

END IF;

RETURN AES_ENCRYPT(plain_text, secret_key, iv);

END;

/* 復号化関数 */

CREATE

DEFINER = 'encrypt_key_reader'@'localhost'

FUNCTION

decrypt_func(encypt_data BLOB, key_id INT)

RETURNS

TEXT

DETERMINISTIC

READS SQL DATA

BEGIN

DECLARE secret_key BLOB;

DECLARE iv BLOB;

SELECT key_store.secret_key, key_store.iv INTO secret_key, iv FROM key_store.key_store WHERE key_store.key_id = key_id;

IF secret_key IS NULL THEN

SIGNAL SQLSTATE '45000'

SET MESSAGE_TEXT = 'Key ID not found';

END IF;

RETURN AES_DECRYPT(encypt_data, secret_key, iv);

END;

DELIMITER ;

暗号化/復号化関数ともにMySQLに実装されているAES暗号化関数を用いて、key_storeテーブルの暗号鍵とIVを用いて暗号化/復号化を行っているだけです。

登録されていない暗号鍵IDを指定して呼び出された場合、SIGNALを使ってエラーとしています。

復号化関数を使う際に暗号化を行った時とは違う暗号鍵IDを指定された場合、正しい鍵を渡さなかったときのAES_DECRYPT関数の戻り値がNULLになるのでこの関数もNULLが返ります。利用する際には、正しい暗号鍵IDを指定してください。

暗号化/復号化ストアドファンクションの使い方

それでは暗号鍵を登録して暗号化、復号化を試してみましょう。

暗号鍵の登録方法

INSERT key_store VALUES (1, SHA2('My secret key',512),  RANDOM_BYTES(16));
INSERT key_store VALUES (2, SHA2('Open sesame',512),  RANDOM_BYTES(16));

1 2	INSERT key_store VALUES (1, SHA2('My secret key',512), RANDOM_BYTES(16)); INSERT key_store VALUES (2, SHA2('Open sesame',512), RANDOM_BYTES(16));

rootユーザーなどappユーザー以外のkey_storeテーブルにINSERT可能な権限を持つユーザーが、事前に登録してください。

暗号化方法

ここからはappユーザでDBに接続して試します。

mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| app@%          |
+----------------+
1 row in set (0.00 sec)

mysql> INSERT encrypt_data_store VALUES ( 1, 1,  encrypt_func("plain text", 1));
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> INSERT encrypt_data_store VALUES ( 2, 2,  encrypt_func("plain text", 2));
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> INSERT encrypt_data_store VALUES ( 3, 2,  encrypt_func("Bad encrypt", 1));
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> INSERT encrypt_data_store VALUES ( 4, 99,  encrypt_func("Key ID not found", 99));
ERROR 1644 (45000): Key ID not found

mysql> SELECT current_user();

+----------------+

| current_user() |

+----------------+

| app@% |

+----------------+

1 row in set (0.00 sec)

mysql> INSERT encrypt_data_store VALUES ( 1, 1, encrypt_func("plain text", 1));

Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> INSERT encrypt_data_store VALUES ( 2, 2, encrypt_func("plain text", 2));

Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> INSERT encrypt_data_store VALUES ( 3, 2, encrypt_func("Bad encrypt", 1));

Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> INSERT encrypt_data_store VALUES ( 4, 99, encrypt_func("Key ID not found", 99));

ERROR 1644 (45000): Key ID not found

encrypt_func関数に平文と暗号鍵の鍵IDを渡すだけで使えます。

暗号化を行った鍵IDと保存する鍵IDを一致させないと復号化できなくなるので注意してください。

もし、登録されていない暗号鍵IDを指定した場合エラーが発生します。

復号化方法

mysql> SELECT current_user();
+----------------+
| current_user() |
+----------------+
| app@%          |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT id, decrypt_func(encypted_data, key_id), encypted_data FROM encrypt_data_store;
+----+-------------------------------------+------------------------------------+
| id | decrypt_func(encypted_data, key_id) | encypted_data                      |
+----+-------------------------------------+------------------------------------+
|  1 | plain text                          | 0xA68B5575C05016C7E993F6F0E3B62DC5 |
|  2 | plain text                          | 0xEE372C6C19ED98F53D30B6E12D8CF55D |
|  3 | NULL                                | 0x0C4D54BE6FDC9F9ACB5AFF26987FB2C0 |
+----+-------------------------------------+------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM key_store.key_store;
ERROR 1142 (42000): SELECT command denied to user 'app'@'192.168.1.10' for table 'key_store'

mysql> SELECT current_user();

+----------------+

| current_user() |

+----------------+

| app@% |

+----------------+

1 row in set (0.00 sec)

mysql> SELECT id, decrypt_func(encypted_data, key_id), encypted_data FROM encrypt_data_store;

+----+-------------------------------------+------------------------------------+

| id | decrypt_func(encypted_data, key_id) | encypted_data |

+----+-------------------------------------+------------------------------------+

| 1 | plain text | 0xA68B5575C05016C7E993F6F0E3B62DC5 |

| 2 | plain text | 0xEE372C6C19ED98F53D30B6E12D8CF55D |

| 3 | NULL | 0x0C4D54BE6FDC9F9ACB5AFF26987FB2C0 |

+----+-------------------------------------+------------------------------------+

3 rows in set (0.00 sec)

mysql> SELECT * FROM key_store.key_store;

ERROR 1142 (42000): SELECT command denied to user 'app'@'192.168.1.10' for table 'key_store'

復号化もdecrypt_func関数に暗号文と暗号鍵の鍵IDを渡すだけです。
当たり前ですが、鍵が異なれば同じ平文でも別の暗号文になります。

idが3の行は暗号化に使用した鍵IDとテーブルに登録した鍵IDが間違っているので復号化に失敗してNULLになっています。

appユーザーは、暗号鍵のあるテーブルにアクセスしようととしても権限がないため暗号鍵にアクセスができず、decrypt_func関数以外の方法で暗号文を平文に戻すことはできません。

補足事項

通信プロトコルの暗号化

センシティブな情報を扱う場合、保存するデータの暗号化だけではなくアプリケーションとMySQLの通信経路もTLSで暗号化を行うことをお勧めします。詳細に関してはマニュアルの暗号化された接続の使用を参照してください。

MySQL Enterprise Edition

MySQL Enterprise Editionでは、AESのような共通鍵暗号方式以外にも、RSAのような公開鍵暗号方式もサポートされています。

外部からの侵入の危険性のある場所では暗号化のみを行い安全な場所で復号化を行うようなアプリケーションの場合には、RSAの公開鍵を使った暗号化が最適かもしれません。

暗号鍵を扱うためのプラグインとして、暗号化キーリングプラグインやAWS KMSキーリングプラグインなどもあるので、MySQL Enterprise Editionをお使いの方はこちらを使ったほうが鍵の管理は楽かもしれません。(MySQL Community Editionで使えるのは平文のキーリングプラグインだけなので、開発目的のみで使用してください)

AES block_encryption_mode

MySQLのblock_encryption_modeのデフォルト値は"aes-128-ecb"とECBモードを利用しているので設定の変更をお勧めします。

SET PERSIST block_encryption_mode = 'aes-256-cbc';

1	SET PERSIST block_encryption_mode = 'aes-256-cbc';

ECBモードは暗号化ブロック毎に分割された平文が同じ場合に同じ暗号文が出現するので、平文の特徴が暗号文に残り安全性に欠けます。ECBモード以外でAESで暗号化/復号化を行うにはInitial Vector(IV)を必ず設定する必要があります。

詳しくは Wikipediaの暗号利用モードを参照してください。

ストアドファンクションの中のuser関数とcurrent_user関数

ストアドファンクションの中でuser関数を呼び出した場合、DB接続ユーザーが返されます。ホスト名部分は接続元ホスト名になります。

'app'@'192.168.1.10'

1	'app'@'192.168.1.10'

ストアドファンクションの中でcurrent_user関数を呼び出した場合、DEFINERで指定したユーザーが返されます。

'encrypt_key_reader'@'localhost'

1	'encrypt_key_reader'@'localhost'

必要があればuser関数を用いて呼び出し元のDB接続ユーザー名を使ってストアドファンクション側で細かいアクセス制御を実装することも可能です。

まとめ

データの暗号化を行う際に必ず検討課題となる暗号鍵の取り扱いについて、一つの手法としてストアドファンクションを使って暗号鍵をDB側で保存＆利用する方法を紹介してみました。

個々のシステムごとに要求されるセキュリティ要件や運用面、暗号化と復号化をDB側で行う負荷等、様々な点を検討する必要があるので紹介した手法をそのまま適用可能な場面は限られますが、MySQLを使うときのテクニックの一つとして参考になれば幸いです。

参考資料

よかったらシェアしてね！

URLをコピーしました！

ストアドファンクションを使った暗号鍵の守り方

ストアドファンクションを使った暗号鍵の守り方

暗号化データの保存方法

アプリケーション用データベース & 暗号化データ保存テーブル

アプリケーション用DB接続ユーザーの作成

暗号鍵保存用データベース & テーブル作成

暗号化/復号化ストアドファンクション専用ユーザーの作成

暗号化/復号化ストアドファンクション作成

暗号化/復号化ストアドファンクションの使い方

暗号鍵の登録方法

暗号化方法

復号化方法

補足事項

通信プロトコルの暗号化

MySQL Enterprise Edition

AES block_encryption_mode

ストアドファンクションの中のuser関数とcurrent_user関数

まとめ

参考資料

スマートスタイルTECHブログについて

関連記事