Oracle迁移到Oceanbase加密数据注意事项

最近有个项目上oracle迁移到ocenabase，但是应用中使用Oracle的数据库内加密函数，数据库中存储的是加密数据，这里有两个注意事项，首先是对于加密函数是否兼容，其次是数据库内的加密数据，如何同步及同步到目标库后已加密码数据能否解密的问题。

2011年在搞oracle 10g时，当时测试也简单测试过<oracle10G 加密解密之dbms_crypto (一)>,<oracle 9i 的加密解密用法之dbms_obfuscation_toolkit(一)> , 主要是两个package  dbms_obfuscation_toolkit  和dbms_crypto, 主要是两类，一是生成加密哈希算法,如最常见的MD5，二是生成加密算法,如DES和AES。  目前Oceanbase V4版本中自带了dbms_crypto 用于兼容Oracle模式，下面测试两个值是否相同。

MD5 hash测试

-- Oracle
create or replace function obfuscation_md5(p_in varchar2) return varchar2
  is
  begin
   return RawToHex(UTL_RAW.CAST_TO_RAW(dbms_obfuscation_toolkit.MD5(input_string=>p_in)));
  end;
/

 
  
CREATE OR REPLACE FUNCTION CRYPTO_md5(p_string IN VARCHAR2)
RETURN VARCHAR2
IS
  v_hash RAW(2000);
BEGIN
  v_hash := DBMS_CRYPTO.HASH(
    src => UTL_I18N.STRING_TO_RAW(p_string, 'AL32UTF8'),
    typ => DBMS_CRYPTO.HASH_MD5
   );
    RETURN LOWER(RAWTOHEX(v_hash));
END CRYPTO_md5;
/

SQL> select obfuscation_md5('anbob.com') from dual;

obfuscation_md5('ANBOB.COM')
--------------------------------------------------------------  
354A5B219144A6B3833864596D87DE6A

SQL> SELECT CRYPTO_md5('anbob.com') from dual;

CRYPTO_md5('ANBOB.COM')
--------------------------------------------------------------  
354a5b219144a6b3833864596d87de6a

Note:
MD5是一种不可逆的hash算法，对于相同字符串的值生成的值是相同的，所以加密来说并不安全，可以通过MD5值反向碰撞解密。

-- Linux
$ echo -n anbob.com|md5sum |cut -d" " -f1
354a5b219144a6b3833864596d87de6a

Note：
在操作系统层使用md5sum一样也可以生成，值与数据库生成的相同

-- Oceanbase for Oracle mode
obclient(root@sys)[oceanbase]> select md5('anbob.com');
+----------------------------------+
| md5('anbob.com')                 |
+----------------------------------+
| 354a5b219144a6b3833864596d87de6a |
+----------------------------------+
1 row in set (0.006 sec)

obclient(SYS@orcl)[SYS]> CREATE OR REPLACE FUNCTION CRYPTO_md5(p_string IN VARCHAR2)
 RETURN VARCHAR2
 IS
   v_hash RAW(2000);
 BEGIN
   v_hash := DBMS_CRYPTO.HASH(
     src => UTL_I18N.STRING_TO_RAW(p_string, 'AL32UTF8'),
     typ => DBMS_CRYPTO.HASH_MD5
    );
     RETURN LOWER(RAWTOHEX(v_hash));
 END CRYPTO_md5;
 /
Query OK, 0 rows affected (0.875 sec)

obclient(SYS@orcl)[SYS]> SELECT CRYPTO_md5('anbob.com') from dual;
+----------------------------------+
| CRYPTO_md5('ANBOB.COM')        |
+----------------------------------+
| 354a5b219144a6b3833864596d87de6a |
+----------------------------------+
1 row in set (0.427 sec)

Note:
在Oceanbase中可以使用md5函数和DBMS_CRYPTO生成的是一样的。 所以如果只是md5值，在oracle还是ocenabase生成的值是相同的。

安全散列算法 (SHA-1) hash测试

CREATE OR REPLACE FUNCTION get_sh1_hash(p_string IN VARCHAR2)
RETURN VARCHAR2
IS
  v_hash RAW(2000);
BEGIN
  v_hash := DBMS_CRYPTO.HASH(
    src => UTL_I18N.STRING_TO_RAW(p_string, 'AL32UTF8'),
    typ => DBMS_CRYPTO.HASH_SH1 
   );
    RETURN LOWER(RAWTOHEX(v_hash));
END get_sh1_hash;
/
 
-- Oracle 
SQL> select get_sh1_hash('anbob.com') from dual;

GET_SH1_HASH('ANBOB.COM')
-------------------------------------------------------------------------  
826fddf9a33a45bf8d6968887a2565e06ea31389

-- Oceanbase for Oracle mode
obclient(SYS@orcl)[SYS]> select get_sh1_hash('anbob.com') from dual;
+------------------------------------------+
| GET_SH1_HASH('ANBOB.COM')                |
+------------------------------------------+
| 826fddf9a33a45bf8d6968887a2565e06ea31389 |
+------------------------------------------+
1 row in set (0.139 sec)

Note:
生成sh安全散列的值在oracle与ocenabase也是相同的。

加密背景

在 Oracle 数据库中，DBMS_OBFUSCATION_TOOLKIT 是 Oracle 早期提供的加密工具包，在较新版本中已被 DBMS_CRYPTO 取代。DBMS_CRYPTO 包提供了强大的加密功能。提供不同的加密算法，在Oceanbase中同样支持这个package, 加密算法支持DES\AES\SM. DBMS_CRYPTO 不直接支持 VARCHAR2 数据类型。在对 VARCHAR2 类型的数据执行加密操作之前，必须将其转换为统一数据库字符集 AL32UTF8，然后将其转换为 RAW 数据类型。完成这些转换后，可以使用 DBMS_CRYPTO 系统包对其进行加密。

加密算法需要明确指定三个关键组件，而不仅仅是基础算法（如 ENCRYPT_DES）。这是因为加密过程需要完整的参数组合才能正常工作。

Oracle 要求加密操作必须明确指定：
基础算法（如 ENCRYPT_DES）：定义核心加密方法（这里是 DES）。
分组模式（如 CHAIN_CBC）：定义如何分块处理数据。
填充方案（如 PAD_PKCS5）：定义如何填充不满足块大小的数据。

这三个部分通过 按位或（+） 组合成一个完整的加密参数。

-- 典型的安全组合：DES + CBC + PKCS5
l_mod NUMBER := DBMS_CRYPTO.ENCRYPT_DES 
              + DBMS_CRYPTO.CHAIN_CBC 
              + DBMS_CRYPTO.PAD_PKCS5;

DES 的块大小是 8 字节。如果数据长度不是 8 的倍数，最后一个块需要填充。
PKCS#5 填充 会添加字节，使总长度为块大小的整数倍。例如：
原始数据：’ABC’（3 字节）
填充后：’ABC\x05\x05\x05\x05\x05’（补 5 个 0x05，共 8 字节）

DBMS_OBFUSCATION_TOOLKIT 仅支持DES和MD5， DBMS_CRYPTO 支持更多算法，DBMS_OBFUSCATION_TOOLKIT 固定使用CBC模式和PKCS5填充，DBMS_CRYPTO 允许自定义这些参数

字符集问题：
对于多字节字符(如中文)，建议先转换为AL32UTF8编码
可以使用UTL_I18N.STRING_TO_RAW替代UTL_RAW.CAST_TO_RAW

obclient(SYS@orcl)[SYS]> select UTL_I18N.STRING_TO_RAW('anbob.com') from dual;
+-------------------------------------+
| UTL_I18N.STRING_TO_RAW('ANBOB.COM') |
+-------------------------------------+
| 616E626F622E636F6D                  |
+-------------------------------------+
 

obclient(SYS@orcl)[SYS]> select utl_raw.cast_to_raw('anbob.com') from dual;
+----------------------------------+
| UTL_RAW.CAST_TO_RAW('ANBOB.COM') |
+----------------------------------+
| 616E626F622E636F6D               |
+----------------------------------+

dbms_obfuscation_toolkit 加密、解密测试

oracle支持，oceanbase不支持。

create or replace function encrypt_obfuscation1(p_in varchar2,p_key varchar2) return raw
   is
      v_rtn varchar2(1000);
	  l_encrypted varchar2(1000);
   begin
       dbms_obfuscation_toolkit.desencrypt(input_string=>p_in,key_string=>p_key,ENCRYPTED_STRING=>l_encrypted);
	   return UTL_RAW.CAST_TO_RAW(l_encrypted);
   end;
  /

SQL> select encrypt_obfuscation1('12345a23','anbob.com') from dual;

ENCRYPT_OBFUSCATION('12345A23','ANBOB.COM')
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2F7D6C1D2B798562

SQL> select encrypt_obfuscation1('12345a2323','anbob.com') from dual;
select encrypt_obfuscation1('12345a2323','anbob.com') from dual
       *
ERROR at line 1:
ORA-28232: invalid input length for obfuscation toolkit
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 21
ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 115
ORA-06512: at "SYS.ENCRYPT_OBFUSCATION1", line 8

Note:
加密数据必须为8字节或倍数。

  create or replace function encrypt_obfuscation(p_in varchar2,p_key varchar2) return varchar2
   is
      v_in varchar2(255);
      v_rtn varchar2(1000);
   begin
      v_in := rpad(p_in,(trunc(length(p_in)/8)+1)*8,chr(0));
      return   UTL_RAW.CAST_TO_RAW(dbms_obfuscation_toolkit.desencrypt(input_string=>v_in,key_string=>p_key));
   end;
  /

SQL> select encrypt_obfuscation('12345a234','anbob.com') from dual;

ENCRYPT_OBFUSCATION('12345A234','ANBOB.COM')
---------------------------------------------------------  
2F7D6C1D2B79856235D3647FB7F129B3


create or replace function decrypt_obfuscation(p_in varchar2,p_key varchar2) return varchar2
  is
  v_in varchar2(2000);
  begin
     dbms_obfuscation_toolkit.DESDECRYPT(input_string => UTL_RAW.CAST_TO_varchar2(p_in),key_string => p_key,
    decrypted_string=> v_in);
    v_in := rtrim(v_in,chr(0));
    return v_in;
  end;
 /

SQL> select decrypt_obfuscation('2F7D6C1D2B79856235D3647FB7F129B3','anbob.com') from dual;

DECRYPT_OBFUSCATION('2F7D6C1D2B79856235D3647FB7F129B3','ANBOB.COM')
----------------------------------------------------------------------------  
12345a234

Note:
不是8字节的倍数，使用cha(0)补充。

dbms_crypto DES加密测试

DES 需要 8 字节密钥，DES 算法已被认为不够安全(56位有效密钥长度)，生产环境建议使用 AES(DBMS_CRYPTO.ENCRYPT_AES256)

-- ORACLE
SQL> r
  1  DECLARE
  2   l_credit_card_no VARCHAR2(19) := '1234-5678-9012-3456';
  3   l_ccn_raw RAW(128) := utl_raw.cast_to_raw(l_credit_card_no);
  4   l_key     RAW(128) := utl_raw.cast_to_raw('anbob.com');
  5
  6   l_encrypted_raw RAW(2048);
  7   l_decrypted_raw RAW(2048);
  8  BEGIN
  9    dbms_output.put_line('Original : ' || l_credit_card_no);
 10
 11     dbms_output.put_line('Key : ' || utl_raw.cast_to_varchar2(l_key));
 12
 13    l_encrypted_raw := dbms_crypto.encrypt(l_ccn_raw,
 14    dbms_crypto.des_cbc_pkcs5, l_key);
 15
 16    dbms_output.put_line('Encrypted : ' ||
 17    RAWTOHEX(utl_raw.cast_to_raw(l_encrypted_raw)));
 18
 19    l_decrypted_raw := dbms_crypto.decrypt(src => l_encrypted_raw,
 20    typ => dbms_crypto.des_cbc_pkcs5, key => l_key);
 21
 22    dbms_output.put_line('Decrypted : ' ||
 23    utl_raw.cast_to_varchar2(l_decrypted_raw));
 24* END;
Original : 1234-5678-9012-3456
Key : anbob.com
Encrypted : 373037393732333945383935463138364641344231353635454245423846413744453632453243444145433144383337
Decrypted : 1234-5678-9012-3456

PL/SQL procedure successfully completed.


-- Oceanbase for Oracle mode

obclient(SYS@orcl)[SYS]> set serveroutput on

obclient(SYS@orcl)[SYS]> DECLARE
    ->  l_credit_card_no VARCHAR2(19) := '1234-5678-9012-3456';
    ->  l_ccn_raw RAW(128) := utl_raw.cast_to_raw(l_credit_card_no);
    ->  l_key     RAW(128) := utl_raw.cast_to_raw('anbob.com');
    ->
    ->  l_encrypted_raw RAW(2048);
    ->  l_decrypted_raw RAW(2048);
    -> BEGIN
    ->   dbms_output.put_line('Original : ' || l_credit_card_no);
    ->
    ->    dbms_output.put_line('Key : ' || utl_raw.cast_to_varchar2(l_key));
    ->
    ->   l_encrypted_raw := dbms_crypto.encrypt(l_ccn_raw,
    ->   dbms_crypto.des_cbc_pkcs5, l_key);
    ->
    ->   dbms_output.put_line('Encrypted : ' ||
    ->   RAWTOHEX(utl_raw.cast_to_raw(l_encrypted_raw)));
    ->
    ->   l_decrypted_raw := dbms_crypto.decrypt(src => l_encrypted_raw,
    ->   typ => dbms_crypto.des_cbc_pkcs5, key => l_key);
    ->
    ->   dbms_output.put_line('Decrypted : ' ||
    ->   utl_raw.cast_to_varchar2(l_decrypted_raw));
    -> END;
    -> /
Query OK, 1 row affected (0.881 sec)

Original : 1234-5678-9012-3456
Key : anbob.com
Encrypted : 373037393732333945383935463138364641344231353635454245423846413744453632453243444145433144383337
Decrypted : 1234-5678-9012-3456

Note:
加密码使用相同秘钥生成的值也是一样的。

dbms_crypto DES加密 解密测试

CREATE OR REPLACE PACKAGE encrypt_decrypt
AS
   FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC;
   FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC;
END;
/

CREATE OR REPLACE PACKAGE BODY encrypt_decrypt
AS
     encryption_type    PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_DES
                                     + DBMS_CRYPTO.CHAIN_CBC
                                     + DBMS_CRYPTO.PAD_PKCS5;
     encryption_key     RAW (32) := UTL_RAW.cast_to_raw('anbob.com');


     FUNCTION encrypt (p_plainText VARCHAR2) RETURN RAW DETERMINISTIC
     IS
        encrypted_raw      RAW (2000);
     BEGIN
        encrypted_raw := DBMS_CRYPTO.ENCRYPT
        (
           src => UTL_RAW.CAST_TO_RAW (p_plainText),
           typ => encryption_type,
           key => encryption_key
        );
       RETURN encrypted_raw;
     END encrypt;

     FUNCTION decrypt (p_encryptedText RAW) RETURN VARCHAR2 DETERMINISTIC
     IS
        decrypted_raw      RAW (2000);
     BEGIN
        decrypted_raw := DBMS_CRYPTO.DECRYPT
        (
            src => p_encryptedText,
            typ => encryption_type,
            key => encryption_key
        );
        RETURN (UTL_RAW.CAST_TO_VARCHAR2 (decrypted_raw));
     END decrypt;
END;
/ 

-- Oracle
SQL> select encrypt_decrypt.encrypt('anbob') from dual;

ENCRYPT_DECRYPT.ENCRYPT('ANBOB')
----------------------------------------------------  
059F611EC2A5395D

SQL> select encrypt_decrypt.decrypt('059F611EC2A5395D') from dual;

ENCRYPT_DECRYPT.DECRYPT('059F611EC2A5395D')
----------------------------------------------------  
anbob


-- Oceanbase
obclient(SYS@orcl)[SYS]> select encrypt_decrypt.encrypt('anbob') from dual;
+----------------------------------+
| ENCRYPT_DECRYPT.ENCRYPT('ANBOB') |
+----------------------------------+
| 059F611EC2A5395D                 |
+----------------------------------+
1 row in set (0.258 sec)

obclient(SYS@orcl)[SYS]> select encrypt_decrypt.decrypt('059F611EC2A5395D') from dual;
+---------------------------------------------+
| ENCRYPT_DECRYPT.DECRYPT('059F611EC2A5395D') |
+---------------------------------------------+
| anbob                                       |
+---------------------------------------------+
1 row in set (0.017 sec)

Note:
加密解密的方法在Oracle和oceanbase中的PLSQL完全兼容，值也相同。

dbms_obfuscation_toolkit 加密dbms_crypto解密

 
-- dbms_obfuscation_toolkit
SQL>  select encrypt_obfuscation('12345678aa','anbob.com') from dual;

ENCRYPT_OBFUSCATION('12345678AA','ANBOB.COM')
---------------------------------------------------------------  
DF0126398E1CFEB38BC840B927C02789
 
 
-- DBMS_CRYPTO
SQL>  select encrypt_decrypt.encrypt('12345678aa') from dual;

ENCRYPT_DECRYPT.ENCRYPT('12345678AA')
------------------------------------------------------------  
DF0126398E1CFEB34C95F6536264F9BB


-- dbms_obfuscation_toolkit
select decrypt_obfuscation('DF0126398E1CFEB34C95F6536264F9BB','anbob.com') from dual;

DECRYPT_OBFUSCATION('DF0126398E1CFEB34C95F6536264F9BB','ANBOB.COM')
-----------------------------------------------------------------------------------  
12345678aa

-- DBMS_CRYPTO
select encrypt_decrypt.decrypt('DF0126398E1CFEB38BC840B927C02789') from dual;
 
ERROR at line 1:
ORA-28817: PL/SQL function returned an error.
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67
ORA-06512: at "SYS.DBMS_CRYPTO", line 44
ORA-06512: at "SYS.ENCRYPT_DECRYPT", line 26

Note:
两个方法生成的密文前半部分相同，但后半部分不同， DBMS_CRYPTO加密的dbms_obfuscation_toolkit可以解密，但是dbms_obfuscation_toolkit加密的DBMS_CRYPTO不可以解密。

总结

声明，我并不是安全专家，Oceanbase支持较新的DBMS_CRYPTO加密和解密，生成的MD5或sha hash是相同的，但是加密算法的值有不同，虽然我测试的dec加密在相同的秘钥下，部分密文相同，使用dbms_obfuscation_toolkit可以解密DBMS_CRYPTO的加密密文，但是不建议这种跨方法的加密解密。所以如果跨数据库如应用之前在oracle使用dbms_obfuscation_toolkit加密，但加密数据在同步到oceanbase前需要解密，再同步重新使用新方法加密。