개발자의 끄적끄적

[oracle] 암호화 / DBMS_CRYPTO 패키지를 이용하여 데이터 암,복호화[펌] 본문

개발/sql

[oracle] 암호화 / DBMS_CRYPTO 패키지를 이용하여 데이터 암,복호화[펌]

효벨 2020. 8. 17. 02:00
728x90
반응형

[oracle] 암호화 / DBMS_CRYPTO 패키지를 이용하여 데이터 암,복호화[펌]

 

오라클 10g 이상에서 사용할  있는 DBMS_CRYPTO 패키지를 이용하여 암호화 하는 방법

 

1. DBMS_CRYPTO 패키지 생성

설치 되어 있지 않을 경우 다음의 SQL 수행해서 패키지를 생성한다.

 

/$ORACLE_HOME/rdbms/admin/dbmsobtk.sql

/$ORACLE_HOME/rdbms/admin/prvtobtk.plb

 

2. SYS유저에서 사용할 패키지를 생성 .

 

테스트에서 사용  암호  값은 '12345678' 사용한다.

 

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

-- 에러 발생시에 error code  message 받기 위한 변수 지정.

    SQLERRMSG   VARCHAR2(255);

    SQLERRCDE   NUMBER;

 

    FUNCTION encrypt (input_string IN VARCHAR2 )

     RETURN RAW

    IS

   

        key_data_raw        RAW(64);

        converted_raw       RAW(64);

        encrypted_raw       RAW(64);

 

    BEGIN

 

        -- 들어온 data  암호 키를 각각 RAW  변환한다.

        converted_raw := UTL_I18N.STRING_TO_RAW(input_string , 'AL32UTF8');

        key_data_raw  := UTL_I18N.STRING_TO_RAW(  '12345678' , 'AL32UTF8');

 

        -- DBMS_CRYPTO.ENCRYPT  암호화 하여 encrypted_raw  저장.

        encrypted_raw := DBMS_CRYPTO.ENCRYPT(  src => converted_raw ,

                                               typ => DBMS_CRYPTO.DES_CBC_PKCS5 , -- typ 부분만 변경하면 원하는 알고리즘을 사용할  있다. key value byte   다르니 확인해야 한다.

                                               key => key_data_raw ,

                                               iv =>  NULL);

       

        RETURN encrypted_raw;

    END encrypt;

 

    FUNCTION decrypt (input_string IN VARCHAR2 )

     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(  '12345678' , 'AL32UTF8');

       

        decrypted_raw := DBMS_CRYPTO.DECRYPT( src => input_string ,

                                              typ => DBMS_CRYPTO.DES_CBC_PKCS5 ,

                                              key => key_data_raw ,

                                              iv =>  NULL);

 

        -- DBMS_CRYPTO.DECRYPT 수행 결과 나온 복호화  raw data varchar2 변환하면 !

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

 

        RETURN converted_string;

    END decrypt ;

END pkg_crypto;

/

 

3. 사용할 유저에게 실행 권한 부여

기본적으로 DBMS_CRYPTO 패키지 권한  부여하면 되지만, 혹시   경우  개의 패키지에 대한 사용 권한을 준다.

 

grant execute on pkg_crypto to soe;

 

4. 테스트

패키지가 정상적으로 생성되었는지 테스트

 

SQL> select sys.pkg_crypto.encrypt ( 'test' ) from dual ;

A04B686B118AF67B

 

SQL> select sys.pkg_crypto.decrypt ( 'A04B686B118AF67B' ) from dual ;

test

 

SQL> create table test_crypto (id int , pwd varchar2(64)) ;

 

SQL> insert into test_crypto values (1, sys.pkg_crypto.encrypt('password1') ) ;

SQL> insert into test_crypto values (2, sys.pkg_crypto.encrypt('password2') ) ;

SQL> commit ;

 

SQL> select * from test_crypto ;

1        8A65E0E80532B5FADACA597658B8E8E0

2        8A65E0E80532B5FA6635EBCA6EB4D195

 

SQL> select id , sys.pkg_crypto.decrypt(pwd) from test_crypto ;

1        password1

2        password2

 

 

5. 패키지 소스 암호화 (WRAP)

패키지 바디 부분을 SQL 파일로 저장한 후에, 해당 SQL파일을 오라클의 WRAP 명령을 이용하여 소스를 암호화 한다.

암호화된 소스를 이용하여 패키지를 생성한다.

 

패키지 바디 부분을 pkg_crypto.sql 파일로 만든 후에, WRAP 명령을 이용하여 소스 암호화를 진행 한다.

 

D:\app\bstar\product\11.2.0\client_2\BIN>wrap iname=d:\tmp\pkg_crypto.sql oname=d:\tmp\pkg_crypto.plb

 

PL/SQL Wrapper: Release 11.2.0.1.0- Production on  7  17 11:27:35 2018

Copyright (c) 1993, 2009, Oracle.  All rights reserved.

 

Processing d:\tmp\pkg_crypto.sql to d:\tmp\pkg_crypto.plb

 

d:\tmp\pkg_crypto.plb 파일을 실행 시켜도 되고, 메모장으로 열어서 사용하는 툴에 붙여 넣어서 수행 시켜도 .

SQL> select text from dba_source where name = 'PKG_CRYPTO';

 

PACKAGE pkg_crypto

IS

    FUNCTION encrypt ( input_string IN VARCHAR2 ) RETURN RAW;

    FUNCTION decrypt ( input_string IN VARCHAR2 ) RETURN VARCHAR2;

END pkg_crypto;

 

PACKAGE BODY pkg_crypto wrapped

a000000

34e

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

b

569 237

ohqkYyNGzuK44ZjD/dc7zE6LBD4wg5VeACDWfC/NR2SlOfdRWqio8BgJ+rTLDeZ0KhuUtwfL

pHsh5UXSmrE5hH7n/MYlulcFuxz+/3JEolt108hdUznBsR865PlC+TBOESlCZ+k6dfP/0AHl

m7ZdffubfOeMEW+6xue2jQP8dS8cEnnuvOBaUG77FS9kmfvhlxjyhQm4lwlnk65byQ4mpm7j

GILd7l4DK2J9rhLoBmcn9GupCftFAI05Ew3eYFuXMfS4NUsCqzdZDLE1ssWoPgFg+nUzSFvF

96FFaMLpCoAYcU9Tq8HdBzHF2Ns/HrqqvzJZx/uPlmo9e4NoSHOonHhr6S2BSS6PXVSXOfeW

dCG489cqwDaf/h2Nxx6WEONFUFoTb7aNG1pvW8Ng5SfDjDYeq4D+lAQA3onCzKeT6/y2hsuA

IJnvdL8FCN3PdeQlz1W8zWexpkBqPyhKvj+RXuuxlTfRnM2voRmdrRch6sSNazdobfnUJOL4

tbN6GdLtMV5ecSORI6U3gkDu9v0wuFojLTjBxLTrCgbIq5AI1x9AOIKC

 



출처: https://bstar36.tistory.com/364 [멋지게 놀아라]

반응형
Comments