Encrypt key column data using the Dbms_crypto package

We can do this in the application layer for the commonly used columns in the business system, and can be implemented at the database level.
Self-validating the use of the Dbms_crypto package to encapsulate functions to implement key column encryption.

1. Database version

Sql>Select* fromv$version; BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release10.2.0.4.0-PRODPL/sql Release10.2.0.4.0-Productioncore10.2.0.4.0Productiontns forLinux:version10.2.0.4.0-Productionnlsrtl Version10.2.0.4.0-Production

2. Creating cryptographic functions

Sql> Conn/ asSysdbaconnected.sql>Create or Replace function Fun_enable_crypto (v_str varchar2,2V_key VARCHAR2default 'ABCDEFGH')  3  returnRaw4   is  5--v_str_raw Raw ( -):=Utl_raw.cast_to_raw (V_STR); 6V_str_raw Raw ( -): =utl_i18n.string_to_raw (V_str,'Al32utf8'); 7V_key_raw Raw ( -): =utl_i18n.string_to_raw (V_key,'Al32utf8'); 8Result_crypto Raw ( -); 9beginTenResult_crypto:=dbms_crypto.encrypt (src=>V_str_raw, OneTyp=>Dbms_crypto. DES_CBC_PKCS5, AKey=>V_key_raw);  -  returnResult_crypto; -end; the/Function created.

3. Create a decryption function

Sql>Create or Replace function Fun_disable_crypto (Encrypto raw,2V_key VARCHAR2default 'ABCDEFGH')  3  returnvarchar24   is  5V_key_raw Raw ( -): =utl_i18n.string_to_raw (V_key,'Al32utf8'); 6Result_crypto Raw ( -); 7begin8Result_crypto:=dbms_crypto.decrypt (src =Encrypto,9Typ =Dbms_crypto. DES_CBC_PKCS5,TenKey=>V_key_raw);  One  returnUtl_i18n.raw_to_char (Result_crypto,'Al32utf8');  Aend; -/Function created.

3. Verifying encryption and decryption

Sql>SelectFun_enable_crypto ('Haidian, Beijing, China.') asCrypto_info fromdual; Crypto_info--------------------------------------------------------------------------------60e37c977b92e7c4f36fef2bfec55494cf57a8db9f10feb0sql>SelectFun_disable_crypto ('60e37c977b92e7c4f36fef2bfec55494cf57a8db9f10feb0') asRel_info fromdual; Rel_info--------------------------------------------------------------------------------Haidian, Beijing, China.

--Customize a key

Sql>SelectFun_enable_crypto ('Haidian, Beijing, China.','1DRQDSE5NW') asCrypto_info fromdual; Crypto_info--------------------------------------------------------------------------------D0fd950eaf47b20e331500444fd8d1090ecb7ef519750bafsql>SelectFun_disable_crypto ('D0fd950eaf47b20e331500444fd8d1090ecb7ef519750baf','1DRQDSE5NW')  2   asRel_info fromdual; Rel_info--------------------------------------------------------------------------------Haidian, Beijing, China.

Reference:

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

Encrypt key column data using the Dbms_crypto package