DBMS_CRYPTO - bami74/me GitHub Wiki

GRANT EXECUTE ON DBMS_OBFUSCATION_TOOLKIT TO [유저명];
GRANT EXECUTE ON DBMS_CRYPTO TO [유저명];

CREATE OR REPLACE PACKAGE [유저명].CRYPTO_AES256
IS
/******************************************************************************
  암호화
 ******************************************************************************/
 FUNCTION ENC_AES ( input_string IN VARCHAR2
 ) RETURN VARCHAR2;

/******************************************************************************
  복호화
 ******************************************************************************/
 FUNCTION DEC_AES (  encrypted_raw IN VARCHAR2
 ) RETURN VARCHAR2;                        

END CRYPTO_AES256;
/

CREATE OR REPLACE PACKAGE BODY [유저명].CRYPTO_AES256
IS
/******************************************************************************
  암호화
 ******************************************************************************/
 FUNCTION ENC_AES ( input_string IN VARCHAR2
 ) RETURN VARCHAR2
 IS
   encrypted_raw      RAW (2000);             -- 암호화된 RAW타입 데이터
   key_bytes_raw      RAW (32);               -- 암호화 KEY (32RAW => 32Byte => 256bit)
   encryption_type    PLS_INTEGER :=          -- 암호화 알고리즘 선언
                            DBMS_CRYPTO.ENCRYPT_AES256
                          + DBMS_CRYPTO.CHAIN_CBC
                          + DBMS_CRYPTO.PAD_PKCS5;
 
    BEGIN
        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
            );

        -- 에러 방지를 위해 base64_encode로 인코딩 처리.. 
        -- ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
        RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(encrypted_raw));

    END ENC_AES;

/******************************************************************************
  복호화
 ******************************************************************************/
 FUNCTION DEC_AES (  encrypted_raw IN VARCHAR2
 ) RETURN VARCHAR2
 IS
   output_string      VARCHAR2 (200);         -- 복호화된 문자열
   decrypted_raw      RAW (2000);             -- 복호화된 raw타입 데이터
   key_bytes_raw      RAW (32);               -- 256bit 암호화 key
   encryption_type    PLS_INTEGER :=          -- 복호화 알고리즘 선언
                            DBMS_CRYPTO.ENCRYPT_AES256
                          + DBMS_CRYPTO.CHAIN_CBC
                          + DBMS_CRYPTO.PAD_PKCS5;
    BEGIN
        key_bytes_raw := UTL_I18N.STRING_TO_RAW('12345678901234567890123456789012', 'AL32UTF8');
        decrypted_raw := DBMS_CRYPTO.DECRYPT
            (
                -- 에러 방지를 위해 base64_decode로 인코딩 처리.. 
                -- 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');

       RETURN output_string;

    END DEC_AES;                        
END CRYPTO_AES256;
/

create or replace package crypto_aes256 is

  /******************************************************************************
   암호화
  ******************************************************************************/
  function enc_aes(input_string in varchar2) return varchar2;

  /******************************************************************************
   복호화
  ******************************************************************************/
  function dec_aes(encrypted_raw in varchar2) return varchar2;

end crypto_aes256;

create or replace package body crypto_aes256 is

  /********************************************************************************
  encryption
  ********************************************************************************/
  function enc_aes(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('1234.567.8900!@#$%^&*()_+=      ',
                                              '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 enc_aes;

  /********************************************************************************
  decryption
  ********************************************************************************/
  function dec_aes(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('1234.567.8900!@#$%^&*()_+=      ',
                                              '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 dec_aes;

end crypto_aes256;