oracle中通過DBMS

 下面我們來看看oracle中通過DBMS_CRYPTO包對表敏感字段進行加密方法，有需要了解的朋友可一起來看看吧.

在安全越來越重視的近體，我們不少時候需要對數據庫中的某個表的敏感列數據(銀行卡,身份證號碼,金額等)進行加密，方式數據洩密,在11.2.0.4中可以通過dbms_crypto包方式實現,增加oracle的加密效率,本文提供處理思路,其他可以根據需求盡情發揮
數據庫版本
SQL> select * from v$version;
 
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
[/shell]
<strong>創建加密函數</strong>
1
SQL> create or replace function f_Encrypt_number(number_in in varchar2) return raw is
  2  number_in_raw RAW(128):=UTL_I18N.STRING_TO_RAW(number_in,'AL32UTF8');
  3  key_number number(32):=32432432343243279898;
  4  key_raw RAW(128):=UTL_RAW.cast_from_number(key_number);
  5  encrypted_raw RAW(128);
  6  begin
  7  encrypted_raw:=dbms_crypto.Encrypt(src=>number_in_raw,typ=>DBMS_CRYPTO.DES_CBC_PKCS5,key=>key_raw);
  8  return encrypted_raw;
  9  end;
 10  /
 
Function created.
測試加密函數
SQL> select f_Encrypt_number('wwww.111cn.net') from dual;
 
F_ENCRYPT_NUMBER('WWWW.111cn.net')
--------------------------------------------------------------------------------
003CB89CB77F6644C93AE2CF7810B0E3E3B10B8C60B54058
創建解密函數
SQL> create or replace function f_decrypt_number (encrypted_raw IN RAW)
  2  return varchar2 is
  3  decrypted_raw raw(48);
  4  key_number number(32):=32432432343243279898;
  5  key_raw RAW(128):=UTL_RAW.cast_from_number(key_number);
  6  begin
  7  decrypted_raw := DBMS_CRYPTO.DECRYPT
  8  (
  9  src => encrypted_raw,
 10  typ => DBMS_CRYPTO.DES_CBC_PKCS5,
 11  key => key_raw
 12  );
 13  return UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
 14  END;
 15  /
 
Function created.
測試解密函數
SQL> select f_decrypt_number('003CB89CB77F6644C93AE2CF7810B0E3E3B10B8C60B54058') from dual;
 
F_DECRYPT_NUMBER('003CB89CB77F6644C93AE2CF7810B0E3E3B10B8C60B54058')
--------------------------------------------------------------------------------
wwww.111cn.net
創建表綜合測試
SQL> create table xifenfei_crypto
  2  (id number, name varchar2(20),en_name raw(128)) ;
 
Table created.
 
SQL> insert into xifenfei_crypto (id,name) select object_id,object_name from dba_objects where rownum<10;
 
9 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from xifenfei_crypto;
 
        ID NAME                 EN_NAME
---------- -------------------- ------------------------------
        20 ICOL$
        46 I_USER1
        28 CON$
        15 UNDO$
        29 C_COBJ#
         3 I_OBJ#
        25 PROXY_ROLE_DATA$
        41 I_IND1
        54 I_CDEF2
 
9 rows selected.
 
SQL> update xifenfei_crypto set en_name=f_Encrypt_number(name);
 
9 rows updated.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from xifenfei_crypto;
 
        ID NAME                 EN_NAME
---------- -------------------- --------------------------------------------------
        20 ICOL$                FE17B031331839A9
        46 I_USER1              FEF96765B1E2C53C
        28 CON$                 0283FCE900ACED5C
        15 UNDO$                20DD92762F199436
        29 C_COBJ#              A0CB43E2EA6BA889
         3 I_OBJ#               F2DE1B9C8A39AA3D
        25 PROXY_ROLE_DATA$     62B99C02EBD4B250311E4490207FEF18CBD8CD8FBA1BFD81
        41 I_IND1               3F4C3C186F8E2F52
        54 I_CDEF2              CA23D202802BD3AC
 
9 rows selected.
 
SQL> select id,name,f_decrypt_number(EN_NAME) de_name,en_name from  xifenfei_crypto;
 
        ID NAME                 DE_NAME                        EN_NAME
---------- -------------------- ------------------------------ --------------------------------------------------
        20 ICOL$                ICOL$                          FE17B031331839A9
        46 I_USER1              I_USER1                        FEF96765B1E2C53C
        28 CON$                 CON$                           0283FCE900ACED5C
        15 UNDO$                UNDO$   &nb