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 >