首页 > 数据库 >Oracle、MySQL、ClickHouse的通用AES256加解密如何实现?

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

时间:2024-10-30 15:47:45浏览次数:1  
标签:aes AES256 encrypt string decrypt 加解密 dbms raw MySQL

 

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

标签:aes,AES256,encrypt,string,decrypt,加解密,dbms,raw,MySQL
From: https://www.cnblogs.com/PiscesCanon/p/18515978

相关文章

  • MySQL数据库详细介绍:从入门到进阶
    MySQL是一个广泛使用的开源关系型数据库管理系统,被广泛应用于Web应用程序、企业级应用以及各种数据分析场景。本文将详细介绍MySQL数据库的基本概念、安装、配置、管理以及优化等方面的内容,帮助大家从入门到进阶了解MySQL。 一、MySQL安装可以通过以下链接下载MySQL安装包:......
  • MySQL 篇
    MySQL篇整体内容优化-如何定位慢查询如何定位慢查询问答环节优化-SQL语句执行的很慢,如何分析解决思路问答环节优化-索引概念及索引底层数据结构数据结构对比问答环节优化-聚簇索引、非聚簇索引、回表查询回表查询问答环节优化-覆盖索引、超大......
  • 基于Java+SpringBoot+Mysql实现的古诗词平台功能设计与实现九
    一、前言介绍:1.1项目摘要随着信息技术的迅猛发展和数字化时代的到来,传统文化与现代科技的融合已成为一种趋势。古诗词作为中华民族的文化瑰宝,具有深厚的历史底蕴和独特的艺术魅力。然而,在现代社会中,由于生活节奏的加快和信息获取方式的多样化,古诗词的传播和阅读面临着一......
  • 基于Java+SpringBoot+Mysql实现的古诗词平台功能设计与实现十
    一、前言介绍:1.1项目摘要随着信息技术的迅猛发展和数字化时代的到来,传统文化与现代科技的融合已成为一种趋势。古诗词作为中华民族的文化瑰宝,具有深厚的历史底蕴和独特的艺术魅力。然而,在现代社会中,由于生活节奏的加快和信息获取方式的多样化,古诗词的传播和阅读面临着一......
  • 为什么MySQL单表不能超过2000万行? (1)
    ​最近看到一篇《我说MySQL每张表最好不要超过2000万数据,面试官让我回去等通知》的文章,非常有趣。文中提到,他朋友在面试的过程中说,自己的工作就是把用户操作信息存到MySQL里,因为数据量超大(5000万条左右),需要每天定时生成3张表,然后将数据取模分别存到这三张表里。下面是两人的对......
  • MySQL的临时表空间
    InnoDB使用会话临时表空间和全局临时表空间。会话临时表空间会话临时表空间用于存储用户创建的临时表,以及在InnoDB被配置为磁盘上内部临时表的存储引擎时由优化器创建的内部临时表。从MySQL8.0.16开始,磁盘上内部临时表使用的存储引擎是InnoDB。(以前,存储引擎由internal_tmp_d......
  • windows下Mysql8.0.12安装详解
    MySQL的安装过程还是比较繁琐,为了以后安装节约时间,将其详细安装过程总结如下:1>下载对应版本下载地址:https://dev.mysql.com/downloads/mysql/2>将下载的.zip文件解压到需要安装的目录下,在该目录下创建一个名为data的空文件夹和一个my.ini文件3>配置初始化的my.ini文件......
  • 连接云虚拟主机中MySQL数据库时出现“Too many connections”报错信息
    在使用云虚拟主机过程中,当尝试连接MySQL数据库时,可能会遇到以下错误信息:  CannotconnecttoMySQLserverError:Toomanyconnections这表示MySQL数据库服务器当前的连接数已经达到了最大限制,无法处理更多的连接请求。可能原因应用程序未及时释放连接:应用程序在......
  • 基于Java+SpringBoot+Mysql实现的古诗词平台功能设计与实现七
    一、前言介绍:1.1项目摘要随着信息技术的迅猛发展和数字化时代的到来,传统文化与现代科技的融合已成为一种趋势。古诗词作为中华民族的文化瑰宝,具有深厚的历史底蕴和独特的艺术魅力。然而,在现代社会中,由于生活节奏的加快和信息获取方式的多样化,古诗词的传播和阅读面临着一定的挑......
  • 基于Java+SpringBoot+Mysql实现的古诗词平台功能设计与实现八
    一、前言介绍:1.1项目摘要随着信息技术的迅猛发展和数字化时代的到来,传统文化与现代科技的融合已成为一种趋势。古诗词作为中华民族的文化瑰宝,具有深厚的历史底蕴和独特的艺术魅力。然而,在现代社会中,由于生活节奏的加快和信息获取方式的多样化,古诗词的传播和阅读面临着一定的挑......