| 标题 | oracle中通过DBMS_CRYPTO包对表敏感字段进行加密 |
| 范文 | 下面我们来看看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$ 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. |
| 随便看 |
|
在线学习网范文大全提供好词好句、学习总结、工作总结、演讲稿等写作素材及范文模板，是学习及工作的有利工具。