下面我們來(lái)看看oracle中通過(guò)DBMS_CRYPTO包對(duì)表敏感字段進(jìn)行加密方法，有需要了解的朋友可一起來(lái)看看吧.

在安全越來(lái)越重視的近體，我們不少時(shí)候需要對(duì)數(shù)據(jù)庫(kù)中的某個(gè)表的敏感列數(shù)據(jù)(銀行卡,身份證號(hào)碼,金額等)進(jìn)行加密，方式數(shù)據(jù)泄密,在11.2.0.4中可以通過(guò)dbms_crypto包方式實(shí)現(xiàn),增加oracle的加密效率,本文提供處理思路,其他可以根據(jù)需求盡情發(fā)揮

數(shù)據(jù)庫(kù)版本

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>創(chuàng)建加密函數(shù)</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.

測(cè)試加密函數(shù)

SQL> select f_Encrypt_number('wwww.111cn.net') from dual;

F_ENCRYPT_NUMBER('WWWW.111cn.net')

--------------------------------------------------------------------------------

003CB89CB77F6644C93AE2CF7810B0E3E3B10B8C60B54058

創(chuàng)建解密函數(shù)

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.

測(cè)試解密函數(shù)

SQL> select f_decrypt_number('003CB89CB77F6644C93AE2CF7810B0E3E3B10B8C60B54058') from dual;

F_DECRYPT_NUMBER('003CB89CB77F6644C93AE2CF7810B0E3E3B10B8C60B54058')

--------------------------------------------------------------------------------

wwww.111cn.net

創(chuàng)建表綜合測(cè)試

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$ 20DD92762F199436

29 C_COBJ# C_COBJ# A0CB43E2EA6BA889

3 I_OBJ# I_OBJ# F2DE1B9C8A39AA3D

25 PROXY_ROLE_DATA$ PROXY_ROLE_DATA$ 62B99C02EBD4B250311E4490207FEF18CBD8CD8FBA1BFD81

41 I_IND1 I_IND1 3F4C3C186F8E2F52

54 I_CDEF2 I_CDEF2 CA23D202802BD3AC

9 rows selected.

更多信息請(qǐng)查看IT技術(shù)專欄