【笔记记录】Oracle使用加密和解密

1、加密函数

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 ;

2、解密函数

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 ;

3、验证SQL

select f_Encrypt_number(‘shower’) from dual;
select f_decrypt_number(‘8B7DB353024D2EA4’) from dual;

–授权
grant execute on sys.f_Encrypt_number to testuser;
grant execute on sys.f_decrypt_number to testuser;
commit