CREATE OR REPLACE PACKAGE encrypt_paswd as
FUNCTION encrypt_val( p_val IN VARCHAR2 ) RETURN RAW;
FUNCTION decrypt_val( p_val IN RAW ) RETURN VARCHAR2 ;
end;
/
CREATE OR REPLACE PACKAGE BODY encrypt_paswd
AS
G_CHARACTER_SET VARCHAR2(10) := 'AL32UTF8';
G_STRING VARCHAR2(32) := '12345678901234567890123456789012';
G_KEY RAW(250) := utl_i18n.string_to_raw
( data => G_STRING,
dst_charset => G_CHARACTER_SET );
G_ENCRYPTION_TYPE PLS_INTEGER := dbms_crypto.encrypt_aes256
+ dbms_crypto.chain_cbc
+ dbms_crypto.pad_pkcs5;
------------------------------------------------------------------------
--Encrypt a password
--Salt the password
------------------------------------------------------------------------
FUNCTION encrypt_val( p_val IN VARCHAR2 ) RETURN RAW
IS
l_val RAW(32) := UTL_I18N.STRING_TO_RAW( p_val, G_CHARACTER_SET );
l_encrypted RAW(32);
BEGIN
l_val := utl_i18n.string_to_raw
( data => p_val,
dst_charset => G_CHARACTER_SET );
l_encrypted := dbms_crypto.encrypt
( src => l_val,
typ => G_ENCRYPTION_TYPE,
key => G_KEY );
RETURN l_encrypted;
END encrypt_val;
FUNCTION decrypt_val( p_val IN RAW ) RETURN VARCHAR2
IS
l_decrypted RAW(32);
l_decrypted_string VARCHAR2(32);
l_user VARCHAR2(32);
BEGIN
SELECT user
INTO l_user
FROM dual;
-- if l_user = 'ADMIN' -- you can restrict usage of decrypt to certain db users only.
--then
l_decrypted := dbms_crypto.decrypt
( src => p_val,
typ => G_ENCRYPTION_TYPE,
key => G_KEY );
l_decrypted_string := utl_i18n.raw_to_char
( data => l_decrypted,
src_charset => G_CHARACTER_SET );
RETURN l_decrypted_string;
-- else
-- RAISE_APPLICATION_ERROR(-20101, 'You are not authorized to use this function - decrypt_val()');
--end if;
--RETURN 'Unknown';
END decrypt_val;
END encrypt_paswd;
/
