Oracle 암/복호화 (DBMS_CRYPTO) 스크립트
GRANT EXECUTE ON DBMS_OBFUSCATION_TOOLKIT TO system;
GRANT EXECUTE ON DBMS_CRYPTO TO system;
create or replace function TYS_Encrypt (input_string VARCHAR2) RETURN RAW
IS
-- select DBMS_CRYPTO.RANDOMBYTES (num_key_bytes) from dual; --> key_bytes_raw
encrypted_raw RAW (2000); -- stores encrypted binary text
key_bytes_raw RAW (32) := HEXTORAW('6E266456572A3E4C79D677BAC5144A645DF911CE07F18AEFF57C3E8560AE1672'); --암호화 키
encryption_type PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5; --암호화 알고리즘
BEGIN
encrypted_raw := DBMS_CRYPTO.ENCRYPT
(src => UTL_I18N.STRING_TO_RAW (input_string, 'AL32UTF8'),
typ => encryption_type,
key => key_bytes_raw);
return encrypted_raw;
END;
/
[PACKAGE]
CREATE OR REPLACE PACKAGE SCOTT.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 SCOTT.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;
/
[Funtion]
create 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;
/
create 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;
/
'1. IT Story > Scripts' 카테고리의 다른 글
| Oracle Golden Gate 사전 점검 스크립트 (0) | 2019.03.02 |
|---|---|
| Oracle Session_cached_cursors, Open_cursors 점검 스크립트 (0) | 2019.02.27 |
| Oracle Invalid Compile 스크립트 (0) | 2019.02.26 |
| Oracle Session별 Memory 사용량 확인 (0) | 2019.02.23 |
| DB PROFILE/BASH_PROFILE 적용 스크립트 (0) | 2019.02.19 |
| ORACLE SCN HELTH CHECK (0) | 2019.02.17 |
| Mysql DB 기본 점검 스크립트 (0) | 2019.02.09 |
| Oracle DBMS_SCHEDULER 백업 설정 스크립트 (0) | 2019.02.06 |
운명을바꾸는자
IT와 함께 살아가는 삶
