首页 » ORACLE 9i-23ai, PostgreSQL/GaussDB » Migrate oracle to openGauss： dbms_crypto.encrypt /decrypt functions

Migrate oracle to openGauss： dbms_crypto.encrypt /decrypt functions

在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.所以可以直接使用。

打赏

目前这篇文章还没有评论(Rss)

我要评论

上一篇：

下一篇：