Oracle、MySQL、ClickHouse的通用AES256加解密如何实现？

 

Oracle、MySQL、ClickHouse的通用AES256加解密如何实现？

 

前段时间研究了加密算法aes，写了个文档，分享到博客上来。

防。

1   说明

应XXX安全生产需求，对目标库目标表业务字段敏感信息进行加密密文存放，查询时通过解密得到明文进行数据使用，要求使用AES256。

目前公司所使用主要的数据库类型有Oracle，MySQL以及ClickHouse，要求同一套key+iv在所有数据库通用，并且能够使用在线的AES对库中密文进行解密。

2   资料参考

https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_CRYPTO.html

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_block_encryption_mode

https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html#function_aes-encrypt

https://clickhouse.com/docs/en/sql-reference/functions/encryption-functions

https://clickhouse.com/docs/en/sql-reference/statements/create/function

https://www.cnblogs.com/PiscesCanon/p/18515978

3   算法信息

以算法AES_256_CBC为例子：

密钥key（32位）：9Fkwza7e4cb24pNB2NKP3cAtpj36G8sA

偏移量iv（16位）：WphknmzxC9ZjKPfc

加密的示例明文：'自由文明'

注：数据库字符集有UTF8

4   数据库实现

要求同一套算法得到的密文一致，并且各自库有加密同时也有解密的功能。

4.1  MySQL

默认MySQL是使用aes-128-ebc算法的，有参数block_encryption_mode控制，该参数也可以在session层面修改进行调试。

SET session block_encryption_mode = 'aes-256-cbc';
以下主主架构所有数据库实例都要设置：
SET global block_encryption_mode = 'aes-256-cbc';
vi /etc/my.cnf
[mysqld]
block_encryption_mode = aes-256-cbc
 
加密：
select hex(aes_encrypt("自由文明","9Fkwza7e4cb24pNB2NKP3cAtpj36G8sA","WphknmzxC9ZjKPfc")) encrypt_value;
得到的密文：3DDCE96F78E871CF42BE5B407CA054E8
解密：
select convert(aes_decrypt(unhex("3DDCE96F78E871CF42BE5B407CA054E8"), "9Fkwza7e4cb24pNB2NKP3cAtpj36G8sA",'WphknmzxC9ZjKPfc') using utf8mb4) decrypt_value;

 

 

4.2  ClickHouse

加密：
SELECT hex(encrypt('aes-256-cbc','自由文明','9Fkwza7e4cb24pNB2NKP3cAtpj36G8sA','WphknmzxC9ZjKPfc')) encrypt_value;
得到的密文：3DDCE96F78E871CF42BE5B407CA054E8
解密：
SELECT decrypt('aes-256-cbc',unhex('3DDCE96F78E871CF42BE5B407CA054E8'),'9Fkwza7e4cb24pNB2NKP3cAtpj36G8sA','WphknmzxC9ZjKPfc') decrypt_value;

 

 

4.3  Oracle

Oracle实现相对复杂，通过dbms_crypto包实现。

加解密过程：
set serveroutput on
DECLARE
 l_ccn_raw RAW(128) := utl_i18n.string_to_raw('自由文明','AL32UTF8');
 l_key     RAW(128) := utl_i18n.string_to_raw('9Fkwza7e4cb24pNB2NKP3cAtpj36G8sA','AL32UTF8');
 l_mod     NUMBER := dbms_crypto.encrypt_aes256 + dbms_crypto.chain_cbc + dbms_crypto.pad_pkcs5;
 l_iv      RAW(128) := utl_i18n.string_to_raw('WphknmzxC9ZjKPfc','AL32UTF8');
 
 l_encrypted_raw RAW(2048);
 l_decrypted_raw RAW(2048);
BEGIN
  dbms_output.put_line(chr(10));
  dbms_output.put_line('示例明文 : ' || '自由文明');
  dbms_output.put_line('key : ' || utl_i18n.raw_to_char(l_key,'AL32UTF8'));
  dbms_output.put_line('iv : ' || utl_i18n.raw_to_char(l_iv,'AL32UTF8')||chr(10));
 
  l_encrypted_raw := dbms_crypto.encrypt(src => l_ccn_raw,      typ => l_mod, key => l_key, iv => l_iv);
  dbms_output.put_line('加密后密文 : ' || rawtohex(l_encrypted_raw));
 
  l_decrypted_raw := dbms_crypto.decrypt(src => l_encrypted_raw,typ => l_mod, key => l_key, iv => l_iv);
  dbms_output.put_line('解密后明文 : ' || utl_i18n.raw_to_char(l_decrypted_raw,'AL32UTF8'));
 
END;
/

 


 

5   使用函数封装

使用函数封装后隐藏key和iv值，同时在数据库中也更加便于使用加解密功能。

5.1  MySQL

5.1.1 创建载体库

create database ecp;

 

 

5.1.2 加密函数

DELIMITER //
create function ecp.f_encrypt(input_string varchar(4000)) 
returns varchar(4000)
no sql
deterministic
sql security invoker
begin
  return hex(aes_encrypt(input_string,"9Fkwza7e4cb24pNB2NKP3cAtpj36G8sA","WphknmzxC9ZjKPfc"));
end//
DELIMITER ;

 

 

5.1.3 创建角色等

创建角色并授权，设置并激活强制角色：
create role ecp_role;
grant execute on function ecp.f_encrypt to ecp_role;
grant select on ecp.* to ecp_role;
 
以下主主架构所有数据库实例都要设置：
set global mandatory_roles = 'ecp_role';
set global activate_all_roles_on_login = on;

参数文件：
vi /etc/my.cnf
[mysqld]
mandatory_roles = 'ecp_role'
activate_all_roles_on_login = on

 

 

5.1.4 加密示例

select ecp.f_encrypt('自由文明');
密文：3DDCE96F78E871CF42BE5B407CA054E8

 

 

5.1.5 解密函数

DELIMITER //
create function ecp.f_decrypt(input_string varchar(4000)) 
returns varchar(4000)
no sql
deterministic
sql security invoker
begin
  return convert(aes_decrypt(unhex(input_string), "9Fkwza7e4cb24pNB2NKP3cAtpj36G8sA",'WphknmzxC9ZjKPfc') using utf8mb4);
end//
DELIMITER ;

 

 

5.1.6 解密示例

select ecp.f_decrypt('3DDCE96F78E871CF42BE5B407CA054E8');
明文：自由文明

 


 

5.2  ClickHouse

存在加解密的时候结果的临时列带有key和iv信息的问题。

目前尚无法解决。

5.2.1 加密函数

create function f_encrypt as (input_string) -> hex(encrypt('aes-256-cbc',input_string,'9Fkwza7e4cb24pNB2NKP3cAtpj36G8sA','WphknmzxC9ZjKPfc'));

 

 

5.2.2 加密示例

select f_encrypt('自由文明');
密文：3DDCE96F78E871CF42BE5B407CA054E8

 

 

5.2.3 解密函数

create function f_decrypt as (input_string) -> decrypt('aes-256-cbc',unhex(input_string),'9Fkwza7e4cb24pNB2NKP3cAtpj36G8sA','WphknmzxC9ZjKPfc');

 

 

5.2.4 解密示例

select f_decrypt('3DDCE96F78E871CF42BE5B407CA054E8');
明文：自由文明

 

 

5.3  Oracle

5.3.1 创建函数

vi f_encrypt.sql
create or replace function sys.f_encrypt(input_string varchar2) return raw is
  encrypted_raw raw(2000);
begin
  encrypted_raw := dbms_crypto.encrypt(src => utl_i18n.string_to_raw(input_string, 'AL32UTF8'),
                                       typ => dbms_crypto.encrypt_aes256 +
                                              dbms_crypto.chain_cbc +
                                              dbms_crypto.pad_pkcs5,
                                       key => utl_i18n.string_to_raw('9Fkwza7e4cb24pNB2NKP3cAtpj36G8sA', 'AL32UTF8'),
                                       iv  => utl_i18n.string_to_raw('WphknmzxC9ZjKPfc', 'AL32UTF8'));
  return rawtohex(encrypted_raw);
end;
/
加密函数本身内容：
wrap iname=f_encrypt.sql oname=f_encrypt.plb
@f_encrypt.plb

 

 


 

5.3.2 加密示例

select f_encrypt('自由文明') from dual;
密文：3DDCE96F78E871CF42BE5B407CA054E8

 

 

5.3.3 解密函数

vi f_decrypt.sql
create or replace function sys.f_decrypt(input_raw raw) return varchar2 is
  output_string varchar2(4000);
  decrypted_raw raw(2000);
begin
  decrypted_raw := dbms_crypto.decrypt(src => input_raw,
                                       typ => dbms_crypto.encrypt_aes256 +
                                              dbms_crypto.chain_cbc +
                                              dbms_crypto.pad_pkcs5,
                                       key => utl_i18n.string_to_raw('9Fkwza7e4cb24pNB2NKP3cAtpj36G8sA', 'AL32UTF8'),
                                       iv  => utl_i18n.string_to_raw('WphknmzxC9ZjKPfc', 'AL32UTF8'));
  output_string := utl_i18n.raw_to_char(decrypted_raw, 'AL32UTF8');
 
  return output_string;
end;
/
 
加密函数本身内容：
wrap iname=f_decrypt.sql oname=f_decrypt.plb
@f_decrypt.plb

 

 

5.3.4 解密示例

select f_decrypt('3DDCE96F78E871CF42BE5B407CA054E8') from dual;
明文：自由文明

 

 

5.3.5 授权，创建同义词

grant execute on sys.f_encrypt to public;
create public synonym f_encrypt for sys.f_encrypt;
grant execute on sys.f_decrypt to public;
create public synonym f_decrypt for sys.f_decrypt;

 

 

6   在线加解密验证

https://www.wushuangzl.com/encrypt/aes.html

posted @ 2024-10-30 15:55  PiscesCanon  阅读(44)  评论(2)  编辑  收藏  举报