oracle数据库字段加密解密

第一步，使用sys账号登录，创建加密解密函数

CREATE OR REPLACE FUNCTION f_Encrypt_number (number_in IN VARCHAR2) 
RETURN RAW IS number_in_raw RAW (128) := UTL_I18N.STRING_TO_RAW (number_in, 'AL32UTF8') ; 
key_number NUMBER (32) := 32432432343243279898 ; 
key_raw RAW (128) := UTL_RAW.cast_from_number (key_number) ; encrypted_raw RAW (128) ;
BEGIN
	encrypted_raw := dbms_crypto. ENCRYPT (
		src => number_in_raw,
		typ => DBMS_CRYPTO.DES_CBC_PKCS5,
		KEY => key_raw
	) ; RETURN encrypted_raw ;
END ;


CREATE OR REPLACE FUNCTION f_decrypt_number (encrypted_raw IN RAW) 
RETURN VARCHAR2 IS decrypted_raw RAW (48) ; key_number NUMBER (32) := 32432432343243279898 ; 
key_raw RAW (128) := UTL_RAW.cast_from_number (key_number) ;
BEGIN
	decrypted_raw := DBMS_CRYPTO. DECRYPT (
		src => encrypted_raw,
		typ => DBMS_CRYPTO.DES_CBC_PKCS5,
		KEY => key_raw
	) ; RETURN UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8') ;
END ;

第二步测试
 

select f_Encrypt_number('shower') from dual;

select f_decrypt_number('8B7DB353024D2EA4') from dual;

第三步授权

grant execute on sys.f_Encrypt_number to udmdb;
grant execute on sys.f_decrypt_number to udmdb;
commit;

第四步登录udmdb用户，执行加密解密函数

加密:UPDATE T_SGAJ_SZHJRKJBXX2016_1 SET F_GMSFHM= sys.f_Encrypt_number(F_GMSFHM);
commit;
解密:select sys.f_decrypt_number(F_GMSFHM) from T_SGAJ_SZHJRKJBXX2016_1;