使用dbms_crypto包加密关键列数据

       对于业务系统中常见的需要加密的列我们可以在应用层来实现，也可以在数据库层实现，
自己验证了一下使用dbms_crypto包来封装函数实现关键列的加密。

 

1.数据库版本

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

 

2.创建加密函数

SQL> conn /as sysdba
Connected.
SQL> create or replace function fun_enable_crypto(v_str varchar2,
  2  v_key varchar2 default 'abcdefgh')
  3  return raw
  4  is
  5  --v_str_raw raw(128):=utl_raw.cast_to_raw(v_str);
  6  v_str_raw raw(128):=utl_i18n.string_to_raw(v_str,'AL32UTF8');
  7  v_key_raw raw(128):=utl_i18n.string_to_raw(v_key,'AL32UTF8');
  8  result_crypto raw(128);
  9  begin
 10  result_crypto:=dbms_crypto.encrypt(src=>v_str_raw,
 11  typ=>dbms_crypto.DES_CBC_PKCS5,
 12  key=>v_key_raw);
 13  return result_crypto;
 14  end;
 15  /

Function created.

 

3.创建解密函数

SQL> create or replace function fun_disable_crypto(encrypto raw,
  2  v_key varchar2 default 'abcdefgh')
  3  return varchar2
  4  is
  5  v_key_raw raw(128):=utl_i18n.string_to_raw(v_key,'AL32UTF8');
  6  result_crypto raw(128);
  7  begin
  8  result_crypto:=dbms_crypto.decrypt(src => encrypto,
  9  typ => dbms_crypto.DES_CBC_PKCS5,
 10  key=>v_key_raw);
 11  return utl_i18n.raw_to_char(result_crypto,'AL32UTF8');
 12  end;
 13  /

Function created.

 

3.验证加密解密

SQL> select fun_enable_crypto('中国.北京.海淀') as crypto_info from dual;
 
CRYPTO_INFO
--------------------------------------------------------------------------------
60E37C977B92E7C4F36FEF2BFEC55494CF57A8DB9F10FEB0

SQL> select fun_disable_crypto('60E37C977B92E7C4F36FEF2BFEC55494CF57A8DB9F10FEB0') as rel_info from dual;
 
REL_INFO
--------------------------------------------------------------------------------
中国.北京.海淀

 

--自定义一个key

SQL> select fun_enable_crypto('中国.北京.海淀','1drQdse5Nw') as crypto_info from dual;
 
CRYPTO_INFO
--------------------------------------------------------------------------------
D0FD950EAF47B20E331500444FD8D1090ECB7EF519750BAF



SQL> select fun_disable_crypto('D0FD950EAF47B20E331500444FD8D1090ECB7EF519750BAF','1drQdse5Nw')
  2  as rel_info from dual;
 
REL_INFO
--------------------------------------------------------------------------------
中国.北京.海淀 

 

参考：

http://www.xifenfei.com/5724.html
http://blog.csdn.net/rznice/article/details/7402850
http://blog.163.com/donfang_jianping/blog/static/1364739512012113053323211/