采用Oracle的dbms_obfuscation_toolkit的加密和解密详解

采用Oracle的dbms_obfuscation_toolkit的加密和解密详解.

前一两天研究了一下Oracle的加密算法,结合自己的实践经历和 Oracle9i Supplied PL/SQL Packages and Types Reference. 加密(encrypt)解密(decrypt)是采用 Oracle DBMS_OBFUSCATION_TOOLKIT package.
利用这个包,我们可以对数据进行DES,Triple DES或者MD5加密.
DESGETKEY   -- 产生密钥,用于DES算法
   DES3GETKEY  -- 产生密钥,用于Triple DES算法
   DESENCRYPT  -- 用DES算法加密数据
   DESDECRYPT  -- 用DES算法解密数据
   DES3ENCRYPT -- 用Triple DES算法加密数据
   DES3DECRYPT -- 用DES算法解密数据
   MD5         -- 用MD5算法加密数据


Triple DES (3DES) is a far stronger cipher than DES; the resulting ciphertext (encrypted data) is much harder to break using an exhaustive search: 2**112 or 2**168 attempts instead of 2**56 attempts  这是怎么样的一个概念呢? 以现在的计算机计算能力来说吧,
uppose you build a computer capable of making 1000 attempts each second. How long would it take to exhaust 2 to the 56 (256) attempts?   it will go supernova many billions of years before you'll finish.
下面看看对字符串: password 加密的过程:

DECLARE
input_string        VARCHAR2(16) := 'password';
key_string          VARCHAR2(8)  := 'oracle9i';
  
encrypted_string    VARCHAR2(2048);
decrypted_string    VARCHAR2(2048);

error_in_input_buffer_length EXCEPTION;

PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
   INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
    '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES ***';

BEGIN
   dbms_output.put_line('> ========= BEGIN TEST =========');
   dbms_output.put_line('> Input string                 : ' ||
input_string);
   --BEGIN <-- ignore this, typo in Oracle's documentation
      dbms_obfuscation_toolkit.DESEncrypt(
                   input_string => input_string,
                   key_string => key_string,
                   encrypted_string => encrypted_string );
      dbms_output.put_line('> Encrypted string             : ' ||
                   encrypted_string);
-- Add DESDecrypt as shown, change raw to key_string
      dbms_obfuscation_toolkit.DESDecrypt(
                   input_string => encrypted_string,
                   key_string => key_string,
                   decrypted_string => decrypted_string);
      dbms_output.put_line('> Decrypted output             : ' ||
                   decrypted_string);
      dbms_output.put_line('>  ');     
      if input_string =
                   decrypted_string THEN
         dbms_output.put_line('> DES Encryption and Decryption successful');
      END IF;
EXCEPTION
     
   WHEN error_in_input_buffer_length THEN
      dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
END;


运行的结果:
> ========= BEGIN TEST =========
> Input string                 : password
> Encrypted string             : .]%.‡—I
> Decrypted output             : password
> 
> DES Encryption and Decryption successful


这里的encrypted string不同的sql/plus版本是不同的结果的,因为字符集不同,这里必段要注意:加密的字符串(input_string)必须是8的倍数哦,其实加密后的字符串也是8的倍数,如果不是的话,结果就是:
> ========= BEGIN TEST =========
> Input string                 : passwo1rd
> *** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES ***


再来看一个例子,采用raw和string的加密比较:
DECLARE
   input_string   VARCHAR2(16) := 'tigertigertigert';
   raw_input      RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
   key_string     VARCHAR2(8)  := 'scottsco';
   raw_key        RAW(128) := UTL_RAW.CAST_TO_RAW(key_string);
   encrypted_raw               RAW(2048);
   encrypted_string            VARCHAR2(2048);
   decrypted_raw               RAW(2048);
   decrypted_string            VARCHAR2(2048);
   error_in_input_buffer_length EXCEPTION;

   PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
   INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
    '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING
EXCEPTION ***';
   double_encrypt_not_permitted EXCEPTION;
   PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233);
   DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
    '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';

-- 1. Begin testing raw data encryption and decryption
   BEGIN
   dbms_output.put_line('> ========= BEGIN TEST RAW DATA =========');
   dbms_output.put_line('> Raw input                        : ' ||
                 UTL_RAW.CAST_TO_VARCHAR2(raw_input));
   BEGIN
      dbms_obfuscation_toolkit.DESEncrypt(input => raw_input,
               key => raw_key, encrypted_data => encrypted_raw );
      dbms_output.put_line('> encrypted hex value              : ' ||
               rawtohex(encrypted_raw));
      dbms_obfuscation_toolkit.DESDecrypt(input => encrypted_raw,
               key => raw_key, decrypted_data => decrypted_raw);
      dbms_output.put_line('> Decrypted raw output             : ' ||
                    UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw));
      dbms_output.put_line('>  ');     
      if UTL_RAW.CAST_TO_VARCHAR2(raw_input) =
                    UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw) THEN
         dbms_output.put_line('> Raw DES Encyption and Decryption successful');
      END if;
   EXCEPTION
      WHEN error_in_input_buffer_length THEN
             dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
   END;
   dbms_output.put_line('>  ');


-- 2. Begin testing string data encryption and decryption
   dbms_output.put_line('> ========= BEGIN TEST STRING DATA =========');

   BEGIN
      dbms_output.put_line('> input string                     : '
                           || input_string);
      dbms_obfuscation_toolkit.DESEncrypt(
               input_string => input_string,
               key_string => key_string,
               encrypted_string => encrypted_string );
      dbms_output.put_line('> encrypted hex value              : ' ||
                   rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_string)));
      dbms_obfuscation_toolkit.DESDecrypt(
               input_string => encrypted_string,
               key_string => key_string,
               decrypted_string => decrypted_string );
      dbms_output.put_line('> decrypted string output          : ' ||
                 decrypted_string);
      if input_string = decrypted_string THEN
         dbms_output.put_line('> String DES Encyption and Decryption successful');
      END if;
   EXCEPTION
      WHEN error_in_input_buffer_length THEN
             dbms_output.put_line(' ' || INPUT_BUFFER_LENGTH_ERR_MSG);
   END;
   dbms_output.put_line('>  ');
END;


运行的结果如下:
> ========= BEGIN TEST RAW DATA =========
> Raw input                        : tigertigertigert
> encrypted hex value              : 5AAB8C0D278AD75CA1968790D00FD75A
> Decrypted raw output             : tigertigertigert
> 
> Raw DES Encyption and Decryption successful
> 
> ========= BEGIN TEST STRING DATA =========
> input string                     : tigertigertigert
> encrypted hex value              : 5AAB8C0D278AD75CA1968790D00FD75A
> decrypted string output          : tigertigertigert
> String DES Encyption and Decryption successful
>

其实处理方法和前一个例子是一样的.


在存储过程中的实现:
create table cc_table(
empno varchar2(64),
cc_no varchar2(64)
)

;

insert into cc_table values('password','password');

select * from cc_table
1   password    password

存储过程如下:
CREATE OR REPLACE PROCEDURE decrypt_cc_no
  (empno varchar2)
  IS
 
   key_string          VARCHAR2(8)  := 'scottsco';
    
   encrypted_string    VARCHAR2(2048);
   decrypted_string    VARCHAR2(2048);
  
  BEGIN
    select cc_no into encrypted_string
    from cc_table
    where empno = empno;
 
    dbms_obfuscation_toolkit.DESDecrypt(
                     input_string => encrypted_string,
                     key_string => key_string,
                     decrypted_string => decrypted_string);
            
    update cc_table
    set cc_no = decrypted_string
    where empno = empno;         
      commit;           
  END;



select * from cc_table;
1   password    騄鐸#卙p

通过了.


再来看看一个采用des3(triple) 的加密过程:
先准备好表和数据:
create table TEST_NIEGC
(
  USERID    VARCHAR2(100),
  PASSWORD  VARCHAR2(100),
  ENCRYPTED VARCHAR2(100)
);

insert into test_niegc values('1','niegc','user1234');
insert into test_niegc values('2','ngc','user1234');
commit;


创建包PG_ENCRYPT_DECRYPT_NIEGC
create or replace package PG_ENCRYPT_DECRYPT_NIEGC is
iKey varchar2(8):='oracle9i';
function GEN_RAW_KEY  ( iKey in varchar2) return raw;
function DECRYPT_3KEY_MODE(iValue in raw,iMode in pls_integer)return varchar2;
function ENCRYPT_3KEY_MODE(iValue in varchar2,iMode in pls_integer)return raw;
end;
/


create or replace package body PG_ENCRYPT_DECRYPT_NIEGC is
function GEN_RAW_KEY  ( iKey in varchar2) 
return raw
as
rawkey raw(240) := '';
begin
for i in 1..length(iKey)
loop   
rawkey := rawkey||hextoraw(to_char(ascii(substr(iKey, i, 1))));
end loop;
return rawkey;
end;
/* Creating function DECRYPT_3KEY_MODE*/
FUNCTION DECRYPT_3KEY_MODE  ( iValue in raw, iMode in pls_integer)
return varchar2
as
vDecrypted varchar2(4000);
rawkey raw(240) := '';
begin
rawkey := GEN_RAW_KEY(iKey);-- decrypt input string
vDecrypted := dbms_obfuscation_toolkit.des3decrypt (UTL_RAW.CAST_TO_VARCHAR2(iValue), key_string => rawkey, which => iMode);
return vDecrypted;
end;
/*Creating function ENCRYPT_3KEY_MODE*/
FUNCTION ENCRYPT_3KEY_MODE  ( iValue in varchar2,  iMode in pls_integer)
return raw
as
vEncrypted varchar2(4000);
vEncryptedRaw Raw(2048);
rawkey raw(240) := '';
begin
rawkey := GEN_RAW_KEY(iKey);
-- encrypt input string
vEncrypted := dbms_obfuscation_toolkit.DES3Encrypt(iValue,key_string => rawkey,which => iMode);
-- convert to raw as out
--vEncrypted := 'aaaaaaaaa';
vEncryptedRaw := UTL_RAW.CAST_TO_RAW(vEncrypted);
return vEncryptedRaw;
end;
end;
/


在sql/plus执行:
update test_niegc a set a.encrypted=pg_encrypt_decrypt_niegc.ENCRYPT_3KEY_MODE(a.encrypted,1);
commit;

select * from test_niegc
1   niegc    69EF3A211A0F2C32
2   ngc    69EF3A211A0F2C32

update test_niegc a set a.encrypted=pg_encrypt_decrypt_niegc.DECRYPT_3KEY_MODE(a.encrypted,1);
select * from test_niegc;

1   1   niegc    user1234
2   2   ngc    user1234


是DES算法和Triple DES算法的特征之一. 输入长度必须是8的倍数, 而输出也是8的倍数,所以我们的字段长度也是8的倍数. 如果输入不是8的倍数,会报错的哦. 这里的密钥: iKey varchar2(8):='oracle9i'; 就是了.所以如果人家知道了密钥当然可以解密了.可以采用把这个package 再加密,这样人家就比较难看到密钥了.