DBMS/오라클] 오라클 10g용db_crypto함수

--[EDIT BY SINU]

conn / as sysdba

create user sinu
identified by sinu;

grant resource, connect to sinu;
grant execute on DBMS_CRYPTO to sinu;

conn sinu/sinu

CREATE OR REPLACE PACKAGE pkg_crypto
IS
    FUNCTION encrypt (
        input_string        IN  VARCHAR2 ,
        key_data IN VARCHAR2 := '12345678'
    ) RETURN RAW;
   
    FUNCTION decrypt (
        input_string        IN  VARCHAR2 ,
        key_data IN VARCHAR2 := '12345678'
    ) RETURN VARCHAR2;

END pkg_crypto;
/


CREATE OR REPLACE PACKAGE BODY pkg_crypto
IS
    SQLERRMSG   VARCHAR2(255);
    SQLERRCDE   NUMBER;
   
    FUNCTION encrypt (input_string IN VARCHAR2 , key_data IN VARCHAR2 := '12345678')
     RETURN RAW
    IS
   
        key_data_raw        RAW(64);
        converted_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(key_data, '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 , key_data IN VARCHAR2 := '12345678')
     RETURN VARCHAR2
    IS
        converted_string    VARCHAR2(64);
        key_data_raw        RAW(64);
        decrypted_raw    VARCHAR2(64);

    BEGIN

        key_data_raw     := UTL_I18N.STRING_TO_RAW(key_data, 'AL32UTF8');
       
        decrypted_raw :=
            DBMS_CRYPTO.DECRYPT(
                 src => input_string ,
                 typ => DBMS_CRYPTO.DES_CBC_PKCS5 ,
                 key => key_data_raw ,
                 iv =>  NULL);

        converted_string := UTL_I18N.RAW_TO_CHAR(decrypted_raw, 'AL32UTF8');

        RETURN converted_string;

    END decrypt ;
END pkg_crypto;
/

col card_number format a64
create table card_info ( id number, card_number varchar2(64) ) ;

insert into card_info values ( 1 , pkg_crypto.encrypt('1234567812345678')) ;
insert into card_info values ( 2 , pkg_crypto.encrypt('12345678')) ;
commit;

select * from card_info ;
select id , pkg_crypto.decrypt(card_number) card_number
from card_info;

insert into card_info values ( 3 , pkg_crypto.encrypt('1234567812345678','00000000')) ;
select id , pkg_crypto.decrypt(card_number,'00000000') card_number from card_info where id = 3  ;

select * from card_info ;

drop package body pkg_crypto;
drop package pkg_crypto;
drop table card_info;

conn / as sysdba
drop user sinu cascade;

--[EDIT BY SINU]