MySQL暗号化 Community EditionとEnterprise

“AES 128 MySQL 4.0.2~”

SET @key_str = SHA2('password',512);
SELECT HEX(AES_ENCRYPT("AES暗号化-個人情報01",@key_str)) into @AES_ENC;
SELECT @AES_ENC;
SELECT AES_DECRYPT(UNHEX(@AES_ENC),@key_str);

“AES 256 MySQL 5.6.17~ “

SELECT @@session.block_encryption_mode;
SET block_encryption_mode = 'aes-256-cbc';
SELECT @@session.block_encryption_mode;
SET @key_str = SHA2('password',512);
SELECT @key_str;
SET @init_vector = RANDOM_BYTES(16);
SET @crypt_str = HEX(AES_ENCRYPT("AES暗号化-個人情報01",@key_str,@init_vector));
SELECT @crypt_str;
SELECT AES_DECRYPT(UNHEX(@crypt_str),@key_str,@init_vector);

“RSAを同じセッションで確認。MySQL 5.6.21~”
こちらはEnterpriseのみで利用可能なPlugin (鍵をPublicとPrivateに分ける事が可能)

SELECT CREATE_ASYMMETRIC_PRIV_KEY('RSA', 1024) INTO @priv_key;
SELECT CREATE_ASYMMETRIC_PUB_KEY('RSA', @priv_key) INTO @pub_key;
SELECT ASYMMETRIC_ENCRYPT('RSA','プライベート鍵にて暗号化しています', @priv_key) INTO @enc_priv;
SELECT ASYMMETRIC_ENCRYPT('RSA','公開鍵にて暗号化しています', @pub_key) INTO @enc_pub;
SELECT ASYMMETRIC_DECRYPT('RSA', @enc_pub, @priv_key);
SELECT ASYMMETRIC_DECRYPT('RSA', @enc_priv, @pub_key);

―以下メモ―
■暗号化された文字列の長さの確認
The length of crypt_str can be calculated using this formula:
16 * (trunc(string_length / 16) + 1)

root@localhost > SELECT 16 * (TRUNCATE(100/16,1) + 1);
+-------------------------------+
| 16 * (TRUNCATE(100/16,1) + 1) |
+-------------------------------+
|                         115.2 |
+-------------------------------+
1 row in set (0.00 sec)

root@localhost > SELECT 16 * (TRUNCATE(100/16,0) + 1);
+-------------------------------+
| 16 * (TRUNCATE(100/16,0) + 1) |
+-------------------------------+
|                           112 |
+-------------------------------+
1 row in set (0.00 sec)

root@localhost > 


root@localhost > select LENGTH("AES暗号化-個人情報01");
+---------------------------------------+
| LENGTH("AES暗号化-個人情報01")        |
+---------------------------------------+
|                                    27 |
+---------------------------------------+
1 row in set (0.00 sec)

root@localhost > SELECT 16 * (TRUNCATE(27/16,0) + 1);
+------------------------------+
| 16 * (TRUNCATE(27/16,0) + 1) |
+------------------------------+
|                           32 |
+------------------------------+
1 row in set (0.00 sec)

root@localhost > SELECT LENGTH(@crypt_str);
+--------------------+
| LENGTH(@crypt_str) |
+--------------------+
|                 64 |
+--------------------+
1 row in set (0.00 sec)

root@localhost > 

■HEXでサイズが2倍になっているので、UNHEXしてあげるとサイズが想定通りであると確認出来る。

root@localhost > SELECT LENGTH(UNHEX(@crypt_str));
+---------------------------+
| LENGTH(UNHEX(@crypt_str)) |
+---------------------------+
|                        32 |
+---------------------------+
1 row in set (0.00 sec)

root@localhost > 

MySQL5.6.21の商用版でよりセキュアな暗号化が提供されたようなので後程確認するとして、
まずは現状のAESの暗号化を復習してみる。

以下抜粋
—————————————————-
http://dev.mysql.com/doc/refman/5.6/en/enterprise-encryption.html

As of MySQL 5.6.21, MySQL Enterprise Edition includes a set of encryption functions
based on the OpenSSL library that expose OpenSSL capabilities at the SQL level.
These functions enable Enterprise applications to perform the following operations:

Implement added data protection using public-key asymmetric cryptography
Create public and private keys and digital signatures
Perform asymmetric encryption and decryption
Use cryptographic hashing for digital signing and data verification and validation
Enterprise Encryption supports the RSA, DSA, and DH cryptographic algorithms.
Enterprise Encryption is supplied as a user-defined function (UDF) library, from which individual functions can be installed individually.

■これまでのAESを利用しての暗号化の復習
name列は暗号化された時の桁数を計算するのが面倒だったので大目で1024で設定しています。

CREATE TABLE `Personal_Info` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(1024) NOT NULL DEFAULT '',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

root@localhost > CREATE TABLE `Personal_Info` (
    ->   `id` int(20) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(1024) NOT NULL DEFAULT '',
    ->    PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.12 sec)

root@localhost > INSERT INTO Personal_Info(name) values (HEX(AES_ENCRYPT("AES暗号化-個人情報01", 'password')));
Query OK, 1 row affected (0.04 sec)

root@localhost > select * from Personal_Info;
+----+------------------------------------------------------------------+
| id | name                                                             |
+----+------------------------------------------------------------------+
|  1 | D8F991170C3468696E4D963AE4A8E2A1D6404D7066F30A5D0419C0EC80D0602B |
+----+------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost > SELECT CONVERT(AES_DECRYPT(UNHEX(name),"password") USING utf8) name FROM Personal_Info;
+-----------------------------+
| name                        |
+-----------------------------+
| AES暗号化-個人情報01        |
+-----------------------------+
1 row in set (0.00 sec)

root@localhost >