오라클 특정 컬럼 암호화 하기

 

--암호화 함수

CREATE OR REPLACE FUNCTION f_Encrypt (p_Input_str  in varchar2) RETURN VARCHAR2
AS
 v_Input_len number := ROUND(LENGTH(p_Input_str)/8+0.5)*8;
 v_Encrypted_str varchar2(2000) := null;
 v_Key varchar2(16) := 'abcdefgh12345678';
BEGIN
   DBMS_OBFUSCATION_TOOLKIT.DESENcrypt(input_string => RPAD(p_Input_str,
                                       v_Input_len),
                                       key_string => v_Key,
                                       encrypted_string =>v_Encrypted_str);

   RETURN v_Encrypted_str;
END;
/

-- 복호화 함수
CREATE OR REPLACE FUNCTION f_Decrypt (p_Encrypted_str in varchar2) RETURN VARCHAR2
AS
 v_Key varchar2(16) := 'abcdefgh12345678';
 v_Decrypted_str varchar2(2000);
BEGIN
   DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(input_string => p_Encrypted_str, 
                                     key_string => v_Key,
                                     decrypted_string => v_Decrypted_str);

   RETURN trim(v_Decrypted_str);
END;
/

 

 

CREATE TABLE ENC_TEST (ID VARCHAR2(30), PWD VARCHAR2(30));

-- 암호화 입력
INSERT INTO ENC_TEST (ID, PWD) VALUES ('AAA', f_Encrypt('11223344AABB'));
INSERT INTO ENC_TEST (ID, PWD) VALUES ('BBB', f_Encrypt('QWER1232'));
COMMIT;

SELECT * FROM ENC_TEST;

ID                             PWD
------------------------------ ------------------------------
AAA                            쮎틃6X奚~?@
BBB                            돐?&戱MY疥[

-- 복호화
SELECT ID, f_Decrypt(PWD) FROM ENC_TEST;

ID                             PWD
------------------------------ ------------------------------
AAA                            11223344AABB
BBB                            QWER1232

* 출처 : http://database.sarang.net

[출처] 오라클 특정 컬럼 암호화 하기 (바람여행 공간) |작성자 바람여행