在oracle迁移opengauss数据库时，可能会遇到在oracle数据库中使用dbms_crypto 加密的数据 ,在目标数据库opengauss有时也不需要完全等同，仅实现加密功能即可，需要我们改写对应的存储过程，或自定义包装function, 也需要合理规划数据迁移的一些方法，比如需要先解密，在目标库重新加密，尤其是加密方法不同，避免迁移源加密数据到目标库后无法解密，当然如果应用层能实现加密功能那是极好的。在很多年前写过<oracle10G 加密解密之dbms_crypto (一)>该函数的使用。前面也整理了对应的编码函数<Migrate oracle to openGauss： cast_to_raw/cast_to_varchar2 & base64_encode/base64_decode functions>
ORACLE
比如源库ORACLE
declare
input_string VARCHAR2 (200) := 'ANBOB.COM你好';
output_string VARCHAR2 (200);
encrypted_raw RAW (2000); -- stores encrypted binary text
decrypted_raw RAW (2000); -- stores decrypted binary text
num_key_bytes NUMBER := 256/8; -- key length 256 bits (32 bytes)
key_bytes_raw RAW (32); -- stores 256-bit encryption key
encryption_type PLS_INTEGER := -- total encryption type
DBMS_CRYPTO.ENCRYPT_AES256
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
begin
DBMS_OUTPUT.PUT_LINE ('Original string: ' || input_string);
key_bytes_raw := DBMS_CRYPTO.RANDOMBYTES (num_key_bytes);
encrypted_raw := DBMS_CRYPTO.ENCRYPT (
src => UTL_I18N.STRING_TO_RAW (input_string, 'AL32UTF8'),
typ => encryption_type,
key => key_bytes_raw
);
DBMS_OUTPUT.PUT_LINE ('encrypted raw: ' ||encrypted_raw);
DBMS_OUTPUT.PUT_LINE ('base64 code: ' ||utl_encode.base64_encode(encrypted_raw));
DBMS_OUTPUT.PUT_LINE ('base64 string: ' ||utl_raw.cast_to_varchar2(utl_encode.base64_encode(encrypted_raw)));
-- The encrypted value in the encrypted_raw variable can be used here:
decrypted_raw := DBMS_CRYPTO.DECRYPT
(
src => encrypted_raw,
typ => encryption_type,
key => key_bytes_raw
);
output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string);
end;
/
Original string: ANBOB.COM你好
encrypted raw: 73365F914A97CC383BEFA61F3E4DD000DF723F1778BE2A31BB5030D4D014171D
base64 code: 637A5A666B5571587A44673737365966506B3351414E39795078643476696F7875314177314E41554678303D
base64 string: czZfkUqXzDg776YfPk3QAN9yPxd4vioxu1Aw1NAUFx0=
Decrypted string: ANBOB.COM你好
PL/SQL procedure successfully completed.
SQL> DECLARE
encrypted_data RAW(2000);
num_key_bytes NUMBER := 256/8; -- key length 256 bits (32 bytes)
encryption_key RAW (32); -- stores 256-bit encryption key
encryption_type PLS_INTEGER := DBMS_CRYPTO.aes_cbc_pkcs5; --AES
input_data VARCHAR2(2000) := 'ANBOB.COM你好';
output_string VARCHAR2 (200);
decrypted_raw RAW (2000); -- stores decrypted binary text
BEGIN
encryption_key := utl_raw.cast_to_raw(lpad('a',num_key_bytes,'x'));
encrypted_data := DBMS_CRYPTO.encrypt(
-- src => UTL_RAW.cast_to_raw(input_data),
src => UTL_I18N.STRING_TO_RAW (input_data, 'ZHS16GBK'),
typ => encryption_type, -- AES encryption
key => encryption_key
);
DBMS_OUTPUT.put_line('Encrypted data: ' || encrypted_data);
DBMS_OUTPUT.PUT_LINE ('base64 string: ' ||utl_raw.cast_to_varchar2(utl_encode.base64_encode(encrypted_data)));
decrypted_raw := DBMS_CRYPTO.DECRYPT
(
src => encrypted_data,
typ => encryption_type,
key => encryption_key
);
output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'ZHS16GBK');
DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string);
END;
/
Encrypted data: 68C5437A15672F176D5477B138403FE7
base64 string: aMVDehVnLxdtVHexOEA/5w==
Decrypted string: ANBOB.COM你好
PL/SQL procedure successfully completed.
PostgreSQL
在PostgreSQL中可以使用encrypt function(外部扩展 pgcrypto extension). 它可能不是100%的替代品，但也许已经足够接近了。Postgres EDB, 也有对应的package，见dbms_crypto文档.
D:\postgresql\pgsql\bin>psql -d postgres
psql (17rc1)
输入 "help" 来获取帮助信息.
postgres=# CREATE EXTENSION pgcrypto
postgres=# \dx+ pgcrypto
对象用于扩展 "pgcrypto"
对象描述
---------------------------------------------------
函数 armor(bytea)
函数 armor(bytea,text[],text[])
函数 crypt(text,text)
函数 dearmor(text)
函数 decrypt(bytea,bytea,text)
函数 decrypt_iv(bytea,bytea,bytea,text)
函数 digest(bytea,text)
函数 digest(text,text)
函数 encrypt(bytea,bytea,text)
函数 encrypt_iv(bytea,bytea,bytea,text)
函数 gen_random_bytes(integer)
函数 gen_salt(text)
函数 gen_salt(text,integer)
函数 hmac(bytea,bytea,text)
函数 hmac(text,text,text)
函数 pgp_armor_headers(text)
函数 pgp_key_id(bytea)
函数 pgp_pub_decrypt(bytea,bytea)
函数 pgp_pub_decrypt(bytea,bytea,text)
函数 pgp_pub_decrypt(bytea,bytea,text,text)
函数 pgp_pub_decrypt_bytea(bytea,bytea)
函数 pgp_pub_decrypt_bytea(bytea,bytea,text)
函数 pgp_pub_decrypt_bytea(bytea,bytea,text,text)
函数 pgp_pub_encrypt(text,bytea)
函数 pgp_pub_encrypt(text,bytea,text)
函数 pgp_pub_encrypt_bytea(bytea,bytea)
函数 pgp_pub_encrypt_bytea(bytea,bytea,text)
函数 pgp_sym_decrypt(bytea,text)
函数 pgp_sym_decrypt(bytea,text,text)
函数 pgp_sym_decrypt_bytea(bytea,text)
函数 pgp_sym_decrypt_bytea(bytea,text,text)
函数 pgp_sym_encrypt(text,text)
函数 pgp_sym_encrypt(text,text,text)
函数 pgp_sym_encrypt_bytea(bytea,text)
函数 pgp_sym_encrypt_bytea(bytea,text,text)
函数 public.gen_random_uuid()
(36 行记录)
postgres=# select upper(encode(encrypt('ANBOB.COM你好', rpad('1234',128/8)::bytea, 'aes'),'HEX')) VAL;
val
----------------------------------
8D261CB345B3558D9A4F100B8D0CF7BD
(1 行记录)
postgres=# select convert_from(decrypt('\x8D261CB345B3558D9A4F100B8D0CF7BD',rpad('1234',128/8)::bytea,'aes'),'SQL_ASCII') VAL;
val
---------------
ANBOB.COM你好
(1 行记录)
openGauss
而openGauss顾中有自带的函数。环境opengauss V6
openGauss-# \df gs_encrypt
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
------------+------------+------------------+---------------------+--------+------------+------------+---------
pg_catalog | gs_encrypt | text | text, text, text | normal | f | f | f
(1 row)
openGauss-# \df gs_encrypt_aes128
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
------------+-------------------+------------------+---------------------+--------+------------+------------+---------
pg_catalog | gs_encrypt_aes128 | text | text, text | normal | f | f | f
(1 row)
openGauss-# \df gs_decrypt
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
------------+------------+------------------+---------------------+--------+------------+------------+---------
pg_catalog | gs_decrypt | text | text, text, text | normal | f | f | f
(1 row)
openGauss-# \df gs_decrypt_aes128
List of functions
Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind
------------+-------------------+------------------+---------------------+--------+------------+------------+---------
pg_catalog | gs_decrypt_aes128 | text | text, text | normal | f | f | f
(1 row)
-- 加密
openGauss=# SELECT gs_encrypt('anbob.com','Key_1234','sm4');
gs_encrypt
--------------------------------------
VXS2rQ/uGFRFA517/z24yaBrqoUQqbxZQg==
(1 row)
openGauss=# SELECT gs_encrypt('anbob.com','Key_1234','aes128');
gs_encrypt
----------------------------------------------------------------------------------------------
T2zzXeO4uAna9K8Vv6IkZ2CP3PrutgbhVL4E1HXukccUl9pN1++j0N9oqLqqYtBKfWQHXBatO+u2E39ukI+hugP0iD0=
(1 row)
openGauss=# SELECT gs_encrypt_aes128('anbob.com','Key_1234');
gs_encrypt_aes128
----------------------------------------------------------------------------------------------
T2zzXeO4uAna9K8Vv6IkZ7UfH0fbCXmgKrN/OU3VqqP7qwdhHYW7n8CY/kIf9XVgFqRo4t+Kj0ZMo8ZbrXrwUBHFkug=
(1 row)
openGauss=# select lengthb(gs_encrypt('anbob.com','Key_1234','aes128'));
lengthb
---------
92
(1 row)
-- 解密
openGauss=# select gs_decrypt('T2zzXeO4uAna9K8Vv6IkZ7UfH0fbCXmgKrN/OU3VqqP7qwdhHYW7n8CY/kIf9XVgFqRo4t+Kj0ZMo8ZbrXrwUBHFkug=','Key_1234','aes128');
gs_decrypt
------------
anbob.com
(1 row)
openGauss=# SELECT gs_decrypt_aes128('T2zzXeO4uAna9K8Vv6IkZ7UfH0fbCXmgKrN/OU3VqqP7qwdhHYW7n8CY/kIf9XVgFqRo4t+Kj0ZMo8ZbrXrwUBHFkug=','Asdf1234'); -- 如果key错误
ERROR: decrypt the cipher text failed!
CONTEXT: referenced column: gs_decrypt_aes128
openGauss=# SELECT gs_decrypt_aes128('T2zzXeO4uAna9K8Vv6IkZ7UfH0fbCXmgKrN/OU3VqqP7qwdhHYW7n8CY/kIf9XVgFqRo4t+Kj0ZMo8ZbrXrwUBHFkug=','Key_1234');
gs_decrypt_aes128
-------------------
anbob.com
(1 row)
openGauss=# select gs_decrypt('VXS2rQ/uGFRFA517/z24yaBrqoUQqbxZQg==','Key_1234','sm4');
gs_decrypt
------------
anbob.com
(1 row)
小结：
在Oracle 加密码包返回的是raw，所以一般还base64编码再to_varchar2, 但在openGauss中直接返回的就是差不多转换后的String.所以可以直接使用。