비밀번호 암호화/복호화

CREATE OR REPLACE PACKAGE BODY PKG_GET_NM
IS
    --첫번째 함수 BODY
    FUNCTION FN_MEMNAME(P_EMP_NUM IN VARCHAR2) RETURN VARCHAR2
    IS
    --SCALAR변수
        R_NM VARCHAR2(100); 
    BEGIN
        --PL/SQL에서의 SELECT에는 꼭 INTO문이 있어야 함
        SELECT NM INTO R_NM
        FROM   EMP
        WHERE  EMP_NUM = P_EMP_NUM;

        DBMS_OUTPUT.PUT_LINE('생성 완료!');

        RETURN R_NM;
    END FN_MEMNAME;

    --두번째 함수BODY
    FUNCTION FN_GET_CUS_NM(P_CAR_NUM IN VARCHAR2) RETURN VARCHAR2
    IS
        R_CUS_NM CUS.CUS_NM%TYPE;
    BEGIN
        SELECT B.CUS_NM INTO R_CUS_NM
        FROM   CAR A INNER JOIN CUS B ON(A.CUS_NUM = B.CUS_NUM)
        WHERE  A.CAR_NUM = P_CAR_NUM;

        RETURN R_CUS_NM;
    END FN_GET_CUS_NM;
END PKG_GET_NM;
/
SELECT CAR_NUM, MK, PY, DRI_DIST, PKG_GET_NM.FN_GET_CUS_NM(CAR_NUM) CUS_NM FROM CAR;

--오라클 자료형(데이터 타입)
/*
https://technet.tmaxsoft.com/upload/download/online/tibero/pver-20150504-000001/tibero_pkg/chap_dbms_obfuscation.html
* RAW
 - 2000Byte까지 저장
 - 이진(Binary : 0 1)데이터 저장
 - 문자형 -> 이진데이터로 저장 : STRING_TO_RAW
 - 이진데이터 -> 문자형으로 읽을때 : RAW_TO_CHAR

* DBMS_CRYPTO 
 - 암호화/복호화 패키지
 - 오라클 10g 이상에서 지원
 - 해시 알고리즘 제공 : 임의의 길이의 데이터를 고정길이의 해시값으로 변환
 - 데이터의 암호화 및 복호화를 위해 DES, AES 등의 다양한 알고리즘 지원

* DBMS_OBFUSCATION_TOOLKIT
 - 데이터를 암호화하고 복호화 하는 패키지.
 - 암호화 : 1234 -> ASLDFKJHSDAHFSIA
 - 복호화 : ASLDFKJHSDAHFSIA -> 1234

* 대칭키 = 비밀키 = 관용키
 - DES, IDEA, SKIPJACK 등
 - 암호화와 복호화를 동일한 키를 사용 <-> 암호화와 복호화 시 서로 다른 키를 사용하는 것은 비대칭키(공개키)
 - 보안이 뚫리면 끝
*/
--1. 선언부
CREATE OR REPLACE PACKAGE PKG_CRYPTO
IS
    --암호화 함수 정의
    FUNCTION ENCRYPT(INPUT_STRING IN VARCHAR2) RETURN RAW;
    --복호화 함수 정의
    FUNCTION DECRYPT(INPUT_STRING IN VARCHAR2) RETURN VARCHAR2;
END PKG_CRYPTO;
/
--2. 구현부
CREATE OR REPLACE PACKAGE BODY PKG_CRYPTO
IS
    --암호화 함수 구현
    FUNCTION ENCRYPT(INPUT_STRING IN VARCHAR2) RETURN RAW
    IS
        CONVERTED_RAW RAW(64);
        KEY_DATA_RAW RAW(64);
        ENCRYPTED_RAW RAW(64);
    BEGIN
        --대상 데이터 암호화
        CONVERTED_RAW := UTL_I18N.STRING_TO_RAW(INPUT_STRING, 'AL32UTF8');
        --비공개키 암호화
        KEY_DATA_RAW := UTL_I18N.STRING_TO_RAW('12345678', 'AL32UTF8');

        ENCRYPTED_RAW := DBMS_CRYPTO.ENCRYPT(SRC=>CONVERTED_RAW
            ,TYP=>DBMS_CRYPTO.DES_CBC_PKCS5
            ,KEY=>KEY_DATA_RAW
            ,IV=>NULL
            );
        RETURN ENCRYPTED_RAW;
    END ENCRYPT;

    --복호화 함수 구현
    FUNCTION DECRYPT(INPUT_STRING IN VARCHAR2) RETURN VARCHAR2
    IS
        KEY_DATA_RAW RAW(64);
        DECRYPTED_RAW VARCHAR2(64); --RAW형의 VARCHAR2
        CONVERTED_STRING VARCHAR2(64); --VARCHAR2
    BEGIN
        --INPUT_STRING (IN)바인드 변수는 이미 이진데이터형이므로 STRING_TO_RAW를 할 필요가 없음
        --비공개키 암호화(대칭키이므로 동일함)
        KEY_DATA_RAW := UTL_I18N.STRING_TO_RAW('12345678', 'AL32UTF8');
        --DECRYPT : RAW(이진데이터) -> STRING(문자데이터)
        DECRYPTED_RAW := DBMS_CRYPTO.DECRYPT(SRC=>INPUT_STRING
                    ,TYP=>DBMS_CRYPTO.DES_CBC_PKCS5
                    ,KEY=>KEY_DATA_RAW
                    ,IV=>NULL);
        --DBMS_CRYPTO.DECRYPT 결과(복호화) 의 RAW 데이터를 VARCHAR2로 변환
        CONVERTED_STRING := UTL_I18N.RAW_TO_CHAR(DECRYPTED_RAW, 'AL32UTF8');
        RETURN CONVERTED_STRING;
    END DECRYPT;
END PKG_CRYPTO;
/
--선언부
CREATE OR REPLACE PACKAGE PKG_CRYPTO
IS
    FUNCTION ENCRYPT(INPUT_STRING IN VARCHAR2) RETURN RAW;
    FUNCTION DECRYPT(INPUT_STRING IN VARCHAR2) RETURN VARCHAR2;
END PKG_CRYPTO;
/
CREATE OR REPLACE PACKAGE BODY PKG_CRYPTO
IS
    FUNCTION ENCRYPT(INPUT_STRING IN VARCHAR2) RETURN RAW
    IS
        CONVERTED_RAW RAW(64);
        KEY_DATA_RAW  RAW(64);
        ENCRYPTED_RAW RAW(64);
    BEGIN
        --암호화X, 문자를 RAW(바이너리)로 변환
        CONVERTED_RAW := UTL_I18N.STRING_TO_RAW(INPUT_STRING,'AL32UTF8');
        KEY_DATA_RAW := UTL_I18N.STRING_TO_RAW('12345678','AL32UTF8');
        --암호화o
        ENCRYPTED_RAW := DBMS_CRYPTO.ENCRYPT(
            SRC=>CONVERTED_RAW
            ,TYP=>DBMS_CRYPTO.DES_CBC_PKCS5
            ,KEY=>KEY_DATA_RAW
            ,IV=>NULL
        );
        RETURN ENCRYPTED_RAW;
    END ENCRYPT;

    FUNCTION DECRYPT(INPUT_STRING IN VARCHAR2) RETURN VARCHAR2
    IS
        --64RAW : 64Byte(512bit)
        KEY_DATA_RAW RAW(64);
        DECRYPTED_RAW RAW(64);
        CONVERTED_STRING VARCHAR2(64);
    BEGIN
        KEY_DATA_RAW := UTL_I18N.STRING_TO_RAW('12345678','AL32UTF8');
        --복호화o => RAW(바이너리)
        DECRYPTED_RAW := DBMS_CRYPTO.DECRYPT(
            SRC=>INPUT_STRING
            ,TYP=>DBMS_CRYPTO.DES_CBC_PKCS5
            ,KEY=>KEY_DATA_RAW
            ,IV=>NULL
        );
        --RAW->VARCHAR2
        CONVERTED_STRING := UTL_I18N.RAW_TO_CHAR(DECRYPTED_RAW,'AL32UTF8');
        RETURN CONVERTED_STRING;
    END DECRYPT;
END PKG_CRYPTO;
/

SELECT PKG_CRYPTO.ENCRYPT('test') FROM DUAL;
SELECT PKG_CRYPTO.DECRYPT('A04B686B118AF67B') FROM DUAL;

SELECT *
FROM MEMBER;

--MEMBER 테이블의 PASSWORD 컬럼의 데이터를 암호화
--상관관계 서브쿼리 + EXISTS UPDATE로 처리
UPDATE MEMBER A
SET A.PASSWORD =  (
    SELECT  PKG_CRYPTO.ENCRYPT(B.PASSWORD)--암호화된 password
    FROM MEMBER B
    WHERE B.MEMBERID = A.MEMBERID --**********
);
COMMIT;

--아이디, 비밀번호 인증
SELECT * FROM MEMBER
WHERE MEMBERID = 'a001'
AND PASSWORD = PKG_CRYPTO.ENCRYPT('asdfasdf');

SELECT PKG_CRYPTO.DECRYPT('04FC1AD4078C4BE0158F4B2AA46FF591') FROM DUAL;

암호화 완료!!