[DB] Crypto(암호화)

권한부여

암호화하는 PL/SQK 소스

--PL/SQL(절차적 언어로써의 질의어)
--User Defined Function(사용자 정의 함수)
/*
1. CREATE OR REPLACE FUNCTION 사용자정의함수명(바인드변수...)
2. 반환값이 있음. 일반 오라클 내장함수
   ( ex) LENGTH, SUBSTR, INSTR, TO_CHAR...)처럼 사용할 수 있음
3. 반환할 데이터 타입을 RETURN으로 선언해야 함
4. 실행영역에서도 RETURN문이 있어야 함
*/
/
SET SERVEROUTPUT ON;
/
--P_EMP_NUM : 바인드 변수(IN/OUT) => 파라미터를 받거나 보내거나..
CREATE OR REPLACE 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;
/

--자동차번호를 통해 고객의 명을 알고자 할때..
--33너1122
--EQUI JOIN
SELECT *
FROM   CAR A, CUS B
WHERE  A.CUS_NUM = B.CUS_NUM
AND    A.CAR_NUM = '33너1122';

--INNER JOIN(ANSI표준)
SELECT B.CUS_NM
FROM   CAR A INNER JOIN CUS B ON(A.CUS_NUM = B.CUS_NUM)
WHERE  A.CAR_NUM = '33너1122';
/
CREATE OR REPLACE FUNCTION FN_GET_CUS_NM(P_CAR_NUM IN VARCHAR2)
 RETURN VARCHAR2
IS
    --REFERENCE변수
    --VARCHAR2(100)
    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;
/
SELECT CAR_NUM, MK, PY, DRI_DIST, FN_GET_CUS_NM(CAR_NUM) CUS_NM FROM CAR;
/
/*
Package
1. 꾸러미, 포장한 상품이라는 의미
2. 업무상 관련 있는 것을 하나로 묶어서 사용
3. 여러 변수, Cursor, Function, Procedure, Exception을 묶어서 캡슐화함
4. 선언부와 본문 이렇게 두 부분으로 나뉨
5. 선언부는 패키지에 포함될 변수, Procedure, function 등을 선언함
*/
/
--선언부
CREATE OR REPLACE PACKAGE PKG_GET_NM
IS
    FUNCTION FN_MEMNAME(P_EMP_NUM IN VARCHAR2) RETURN VARCHAR2;
    FUNCTION FN_GET_CUS_NM(P_CAR_NUM IN VARCHAR2) RETURN VARCHAR2;
END;
/
--본문
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   EMP;

--기존 비밀번호를 암호화 처리
UPDATE EMP
SET    PWD = PKG_CRYPTO.ENCRYPT(PWD);

--로그인
SELECT *
FROM   EMP
WHERE  EMP_NUM = '202108001' 
AND    PWD = PKG_CRYPTO.ENCRYPT('1234');

SELECT *
FROM   CUS;

INSERT INTO CUS(CUS_NUM, CUS_NM, ADDR1, PNE, PWD, ZIPCODE)
VALUES(
    (SELECT NVL(MAX(CUS_NUM),0)+1 FROM CUS),
    '이순신',
    '대전광역시 중구',
    '010-111-2222',
    PKG_CRYPTO.ENCRYPT('java'),
    '11223'
);

UPDATE CUS
SET    PWD = PKG_CRYPTO.ENCRYPT(PWD)
WHERE  CUS_NUM < 4;

SELECT *
FROM MEMBER;

--MEMBER 테이블의 PASSWORD 컬럼의 데이터를 암호화
--상관관계서브쿼리 + EXIST UPDATE로 처리


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