[Oracle] 암호화 패키지 생성하기

안녕하세요. yeTi입니다.

오늘은 오라클에서 제공하는 DBMS_CRYPTO 패키지를 활용하여 암호화하는 패키지를 만들어보겠습니다.

1. dba 계정으로 로그인 후 다음 스크립트 실행

SQL> @/home/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/dbmsobtk.sql
SQL> @/home/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/prvtobtk.plb

2. 권한 부여

SQL> GRANT execute on dbms_crypto to public;
SQL> GRANT execute ON dbms_obfuscation_toolkit TO public;

3. 암호화를 사용한 계정으로 로그인 후 패키지 생성

CREATE OR REPLACE PACKAGE CryptIT AS
 FUNCTION encrypt( input_string IN VARCHAR2 ) RETURN VARCHAR2;

 FUNCTION decrypt( encrypted_raw IN VARCHAR2 ) RETURN VARCHAR2;
END CryptIT;
/

create or replace PACKAGE BODY CryptIT
IS
        FUNCTION encrypt(input_string IN VARCHAR2)
                RETURN VARCHAR2
        IS
                return_base256 VARCHAR2(256);
                encrypted_raw RAW (2000);      -- encryption raw type date
                key_bytes_raw RAW (32);        -- encryption key (32raw => 32byte => 256bit)
                encryption_type PLS_INTEGER := -- encryption
                DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
        BEGIN
                IF input_string       IS NOT NULL THEN
                        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');
                        encrypted_raw := DBMS_CRYPTO.ENCRYPT ( src => UTL_I18N.STRING_TO_RAW(input_string, 'AL32UTF8'), typ => encryption_type, KEY => key_bytes_raw );
                        -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
                        return_base256 := UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(encrypted_raw));
                END IF;
                RETURN return_base256;
        END encrypt;

        FUNCTION decrypt(encrypted_raw IN VARCHAR2)
                RETURN VARCHAR2
        IS
                output_string VARCHAR2 (200);
                decrypted_raw RAW (2000);      -- decryption raw type date
                key_bytes_raw RAW (32);        -- 256bit decryption key
                encryption_type PLS_INTEGER := -- decryption
                DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
        BEGIN
                IF encrypted_raw      IS NOT NULL THEN
                        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');
                        decrypted_raw := DBMS_CRYPTO.DECRYPT (
                        -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
                        src => UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(encrypted_raw)), typ => encryption_type, KEY => key_bytes_raw );
                        output_string := UTL_I18N.RAW_TO_CHAR(decrypted_raw, 'AL32UTF8');
                END IF;
                RETURN output_string;
        END decrypt;
END CryptIT;
/

4. 정상적으로 동작하는지 확인

SELECT CryptIT.encrypt('value')

FROM DUAL