암호화와 복호화를 담당하는 이 두 함수를 이용하면 그 어떤 데이터라도 쉽게 암호화하여 데이터를 저장하고 다시 복호화 할 수 있을 것이다. 또한 함수 형태로 만들었으므로 어떤 테이블에 있는 어느 컬럼이라도 암호화와 복호화를 적용할 수 있다. 지금까지 작성한 MY_UTIL_PKG의 전체 소스를 정리하면 다음과 같다.
입력
CREATE OR REPLACE PACKAGE my_util_pkg IS
-- 1. 프로그램 소스 검색 프로시저
PROCEDURE program_search_prc (ps_src_text IN VARCHAR2);
-- 2. 객체검색 프로시저
PROCEDURE object_search_prc (ps_obj_name IN VARCHAR2);
-- 3. 테이블 Layout 출력
PROCEDURE table_layout_prc ( ps_table_name IN VARCHAR2);
-- 4. 컬럼 값을 세로로 출력
PROCEDURE print_col_value_prc ( ps_query IN VARCHAR2 );
-- 이메일 전송과 관련된 패키지 상수
pv_host VARCHAR2(10) := 'localhost'; -- SMTP 서버명
pn_port NUMBER := 25; -- 포트번호
pv_domain VARCHAR2(30) := 'hong.com'; -- 도메인명
pv_boundary VARCHAR2(50) := 'DIFOJSLKDWFEFO.WEFOWJFOWE'; -- boundary text
pv_directory VARCHAR2(50) := 'SMTP_FILE'; --파일이 있는 디렉토리명
-- 5. 이메일 전송
PROCEDURE email_send_prc ( ps_from IN VARCHAR2,
ps_to IN VARCHAR2,
ps_subject IN VARCHAR2,
ps_body IN VARCHAR2,
ps_content IN VARCHAR2 DEFAULT 'text/plain;',
ps_file_nm IN VARCHAR2
);
-- 6. 비밀번호 생성
FUNCTION fn_create_pass ( ps_input IN VARCHAR2,
ps_add IN VARCHAR2 )
RETURN RAW;
-- 7. 비밀번호 확인
FUNCTION fn_check_pass ( ps_input IN VARCHAR2,
ps_add IN VARCHAR2,
p_raw IN RAW )
RETURN VARCHAR2;
-- 8. 암호화 함수
FUNCTION fn_encrypt ( ps_input_string IN VARCHAR2 )
RETURN RAW;
-- 9. 복호화 함수
FUNCTION fn_decrypt ( prw_encrypt IN RAW )
RETURN VARCHAR2;
END my_util_pkg;
-- 패키지 본문
CREATE OR REPLACE PACKAGE BODY my_util_pkg IS
/* 1. 프로그램 소스 검색 프로시저 *************************************************************************************/
PROCEDURE program_search_prc (ps_src_text IN VARCHAR2)
IS
vs_search VARCHAR2(100);
vs_name VARCHAR2(1000);
BEGIN
-- 찾을 키워드 앞뒤에 '%'를 붙인다.
vs_search := '%' || NVL(ps_src_text, '%') || '%';
-- dba_source에서 입력된 키워드로 소스를 검색한다.
-- 입력 키워드가 대문자 혹은 소문자가 될 수 있으므로 UPPER, LOWER 함수를 이용해 검색한다.
FOR C_CUR IN ( SELECT name, type, line, text
FROM user_source
WHERE text like UPPER(vs_search)
OR text like LOWER(vs_search)
ORDER BY name, type, line
)
LOOP
-- 프로그램 이름과 줄번호를 가져와 출력한다.
vs_name := C_CUR.name || ' - ' || C_CUR.type || ' - ' || C_Cur.line || ' : ' || REPLACE(C_CUR.text, CHR(10), '');
DBMS_OUTPUT.PUT_LINE( vs_name);
END LOOP;
END program_search_prc;
/* 2. 객체검색 프로시저 *************************************************************************************************/
PROCEDURE object_search_prc (ps_obj_name IN VARCHAR2)
IS
vs_search VARCHAR2(100);
vs_name VARCHAR2(1000);
BEGIN
-- 찾을 키워드 앞뒤에 '%'를 붙인다.
vs_search := '%' || NVL(ps_obj_name, '%') || '%';
-- referenced_name 입력된 키워드로 참조객체를 검색한다.
-- user_dependencies에는 모두 대문자로 데이터가 들어가 있으므로 UPPER 함수를 이용해 검색한다.
FOR C_CUR IN ( SELECT name, type
FROM user_dependencies
WHERE referenced_name LIKE UPPER(vs_search)
ORDER BY name, type
)
LOOP
-- 프로그램 이름과 줄번호를 가져와 출력한다.
vs_name := C_CUR.name || ' - ' || C_CUR.type ;
DBMS_OUTPUT.PUT_LINE( vs_name);
END LOOP;
END object_search_prc;
/* 3. 테이블 Layout 출력 ***********************************************************************************************/
PROCEDURE table_layout_prc ( ps_table_name IN VARCHAR2)
IS
vs_table_name VARCHAR2(50) := UPPER(ps_table_name);
vs_owner VARCHAR2(50);
vs_columns VARCHAR2(300);
BEGIN
BEGIN
-- TABLE이 있는지 검색
SELECT OWNER
INTO vs_owner
FROM ALL_TABLES
WHERE TABLE_NAME = vs_table_name;
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(vs_table_name || '라는 테이블이 존재하지 않습니다');
RETURN;
END;
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('테이블: ' || vs_table_name || ' , 소유자 : ' || vs_owner);
DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------');
-- 컬럼정보 검색 및 출력
FOR C_CUR IN ( SELECT column_name, data_type, data_length, nullable, data_default
FROM ALL_TAB_COLS
WHERE table_name = vs_table_name
ORDER BY column_id
)
LOOP
-- 컬럼 정보를 출력한다. 줄을 맞춰 출력되도록 RPAD 함수를 사용한다.
vs_columns := RPAD(C_CUR.column_name, 20) || RPAD(C_CUR.data_type, 15) || RPAD(C_CUR.data_length, 5) || RPAD(C_CUR.nullable, 2) || RPAD(C_CUR.data_default, 10);
DBMS_OUTPUT.PUT_LINE( vs_columns);
END LOOP;
END table_layout_prc;
/* 4. 컬럼 값을 세로로 출력 *****************************************************************************************************************************************/
PROCEDURE print_col_value_prc ( ps_query IN VARCHAR2 )
IS
l_theCursor INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
l_columnValue VARCHAR2(4000);
l_status INTEGER;
l_descTbl DBMS_SQL.DESC_TAB;
l_colCnt NUMBER;
BEGIN
-- 쿼리구문이 p_query 매개변수에 들어오므로 이를 파싱한다.
DBMS_SQL.PARSE( l_theCursor, ps_query, DBMS_SQL.NATIVE );
-- DESCRIBE_COLUMN 프로시저 : 커서에 대한 컬럼정보를 DBMS_SQL.DESC_TAB 형 변수에 넣는다.
DBMS_SQL.DESCRIBE_COLUMNS ( l_theCursor, l_colCnt, l_descTbl );
-- 선택된 컬럼 개수만큼 루프를 돌며 DEFINE_COLUMN 프로시저를 호출해 컬럼을 정의한다.
FOR i IN 1..l_colCnt
LOOP
DBMS_SQL.DEFINE_COLUMN (l_theCursor, i, l_columnValue, 4000);
END LOOP;
-- 실행
l_status := DBMS_SQL.EXECUTE(l_theCursor);
WHILE ( DBMS_SQL.FETCH_ROWS (l_theCursor) > 0 )
LOOP
-- 컬럼 개수만큼 다시 루프를 돌면서 컬럼 값을 l_columnValue 변수에 담는다.
-- DBMS_SQL.DESC_TAB 형 변수인 l_descTbl.COL_NAME은 컬럼 명칭이 있고
-- l_columnValue에는 컬럼 값이 들어있다.
FOR i IN 1..l_colCnt
LOOP
DBMS_SQL.COLUMN_VALUE ( l_theCursor, i, l_columnValue );
DBMS_OUTPUT.PUT_LINE ( rpad( l_descTbl(i).COL_NAME, 30 ) || ': ' || l_columnValue );
END LOOP;
DBMS_OUTPUT.PUT_LINE( '-----------------' );
END LOOP;
DBMS_SQL.CLOSE_CURSOR (l_theCursor);
END print_col_value_prc;
/* 5. 이메일 전송 **************************************************************************************************************************/
PROCEDURE email_send_prc ( ps_from IN VARCHAR2, -- 보내는 사람
ps_to IN VARCHAR2, -- 받는 사람
ps_subject IN VARCHAR2, -- 제목
ps_body IN VARCHAR2, -- 본문
-- Content-Type
ps_content IN VARCHAR2 DEFAULT 'text/plain;',
ps_file_nm IN VARCHAR2 -- 첨부파일
)
IS
vc_con utl_smtp.connection;
v_bfile BFILE; -- 파일을 담을 변수
vn_bfile_size NUMBER := 0; -- 파일크기
v_temp_blob BLOB := EMPTY_BLOB; -- 파일을 옮겨담을 BLOB 타입 변수
vn_blob_size NUMBER := 0; -- BLOB 변수 크기
vn_amount NUMBER := 54; -- 54 단위로 파일을 잘라 메일에 붙이기 위함
v_tmp_raw RAW(54); -- 54 단위로 자른 파일내용이 담긴 RAW 타입변수
vn_pos NUMBER := 1; --파일 위치를 담는 변수
BEGIN
vc_con := UTL_SMTP.OPEN_CONNECTION(pv_host, pn_port);
UTL_SMTP.HELO(vc_con, pv_domain); -- HELO
UTL_SMTP.MAIL(vc_con, ps_from); -- 보내는사람
UTL_SMTP.RCPT(vc_con, ps_to); -- 받는사람
UTL_SMTP.OPEN_DATA(vc_con); -- 메일본문 작성 시작
UTL_SMTP.WRITE_DATA(vc_con,'MIME-Version: 1.0' || UTL_TCP.CRLF ); -- MIME 버전
UTL_SMTP.WRITE_DATA(vc_con,'Content-Type: multipart/mixed; boundary="' || pv_boundary || '"' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_RAW_DATA(vc_con, UTL_RAW.CAST_TO_RAW('From: ' || ps_from || UTL_TCP.CRLF) ); -- 보내는사람
UTL_SMTP.WRITE_RAW_DATA(vc_con, UTL_RAW.CAST_TO_RAW('To: ' || ps_to || UTL_TCP.CRLF) ); -- 받는사람
UTL_SMTP.WRITE_RAW_DATA(vc_con, UTL_RAW.CAST_TO_RAW('Subject: ' || ps_subject || UTL_TCP.CRLF) ); -- 제목
UTL_SMTP.WRITE_DATA(vc_con, UTL_TCP.CRLF ); -- 한 줄 띄우기
-- 메일 본문
UTL_SMTP.WRITE_DATA(vc_con, '--' || pv_boundary || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA(vc_con, 'Content-Type: ' || ps_content || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA(vc_con, 'charset=euc-kr' || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA(vc_con, UTL_TCP.CRLF );
UTL_SMTP.WRITE_RAW_DATA(vc_con, UTL_RAW.CAST_TO_RAW(ps_body || UTL_TCP.CRLF) );
UTL_SMTP.WRITE_DATA(vc_con, UTL_TCP.CRLF );
-- 첨부파일이 있다면 ...
IF ps_file_nm IS NOT NULL THEN
UTL_SMTP.WRITE_DATA(vc_con, '--' || pv_boundary || UTL_TCP.CRLF );
-- 파일의 Content-Type은 application/octet-stream
UTL_SMTP.WRITE_DATA(vc_con,'Content-Type: application/octet-stream; name="' || ps_file_nm || '"' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(vc_con,'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(vc_con,'Content-Disposition: attachment; filename="' || ps_file_nm || '"' || UTL_TCP.CRLF);
UTL_SMTP.WRITE_DATA(vc_con, UTL_TCP.CRLF);
-- 파일처리 시작
-- 파일을 읽어 BFILE 변수인 v_bfile에 담는다.
v_bfile := BFILENAME(pv_directory, ps_file_nm);
-- v_bfile 담은 파일을 읽기전용으로 연다.
DBMS_LOB.OPEN(v_bfile, DBMS_LOB.LOB_READONLY);
-- v_bfile에 담긴 파일의 크기를 가져온다.
vn_bfile_size := DBMS_LOB.GETLENGTH(v_bfile);
-- v_bfile를 BLOB 변수인 v_temp_blob에 담기 위해 초기화
DBMS_LOB.CREATETEMPORARY(v_temp_blob, TRUE);
-- v_bfile에 담긴 파일을 v_temp_blob 로 옮긴다.
DBMS_LOB.LOADFROMFILE(v_temp_blob, v_bfile, vn_bfile_size);
-- v_temp_blob의 크기를 구한다.
vn_blob_size := DBMS_LOB.GETLENGTH(v_temp_blob);
-- vn_pos 초기값은 1, v_temp_blob 크기보다 작은 경우 루프
WHILE vn_pos < vn_blob_size
LOOP
-- v_temp_blob에 담긴 파일을 vn_amount(54)씩 잘라 v_tmp_raw에 담는다.
DBMS_LOB.READ(v_temp_blob, vn_amount, vn_pos, v_tmp_raw);
-- 잘라낸 v_tmp_raw를 메일에 첨부한다.
UTL_SMTP.WRITE_RAW_DATA(vc_con, UTL_ENCODE.BASE64_ENCODE ( v_tmp_raw));
UTL_SMTP.WRITE_DATA(vc_con, UTL_TCP.CRLF );
v_tmp_raw := NULL;
vn_pos := vn_pos + vn_amount;
END LOOP;
DBMS_LOB.FREETEMPORARY(v_temp_blob); -- v_temp_blob 메모리 해제
DBMS_LOB.FILECLOSE(v_bfile); -- v_bfile 닫기
END IF; -- 첨부파일 처리 종료
-- 맨 마지막 boundary에는 앞과 뒤에 '--'를 반드시 붙여야 한다.
UTL_SMTP.WRITE_DATA(vc_con, '--' || pv_boundary || '--' || UTL_TCP.CRLF );
UTL_SMTP.CLOSE_DATA(vc_con); -- 메일 본문 작성 종료
UTL_SMTP.QUIT(vc_con); -- 메일 세션 종료
EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
dbms_output.put_line(sqlerrm);
UTL_SMTP.QUIT(vc_con);
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary e-mail issue - try again');
UTL_SMTP.QUIT(vc_con);
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Permanent Error Encountered.');
dbms_output.put_line(sqlerrm);
UTL_SMTP.QUIT(vc_con);
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
UTL_SMTP.QUIT(vc_con);
END email_send_prc;
/* 6. 비밀번호 생성 **************************************************************************************************************************/
FUNCTION fn_create_pass ( ps_input IN VARCHAR2,
ps_add IN VARCHAR2 )
RETURN RAW
IS
v_raw RAW(32747);
v_key_raw RAW(32747);
v_input_string VARCHAR2(100);
BEGIN
-- 키 값을 가진 ch19_wrap_pkg 패키지의 pv_key_string 상수를 가져와 RAW 타입으로 변환한다.
v_key_raw := UTL_RAW.CAST_TO_RAW(ch19_wrap_pkg.pv_key_string );
-- 좀 더 보안을 강화하기 위해 두 개의 입력 매개변수와 특수문자인 $%를 조합해
-- MAC 함수의 첫 번째 매개변수로 넘긴다.
v_input_string := ps_input || '$%' || ps_add;
-- MAC 함수를 사용해 입력 문자열을 RAW 타입으로 변환한다.
v_raw := DBMS_CRYPTO.MAC (src => UTL_RAW.CAST_TO_RAW(v_input_string)
,typ => DBMS_CRYPTO.HMAC_SH1
,key => v_key_raw);
RETURN v_raw;
END fn_create_pass;
/* 7. 비밀번호 확인 **************************************************************************************************************************/
FUNCTION fn_check_pass ( ps_input IN VARCHAR2,
ps_add IN VARCHAR2,
p_raw IN RAW )
RETURN VARCHAR2
IS
v_raw RAW(32747);
v_key_raw RAW(32747);
v_input_string VARCHAR2(100);
v_rtn VARCHAR2(10) := 'N';
BEGIN
-- 키 값을 가진 ch19_wrap_pkg 패키지의 pv_key_string 상수를 가져와 RAW 타입으로 변환한다.
v_key_raw := UTL_RAW.CAST_TO_RAW(ch19_wrap_pkg.pv_key_string );
-- 좀 더 보안을 강화하기 위해 두 개의 입력 매개변수와 특수문자인 $%를 조합해
-- MAC 함수의 첫 번째 매개변수로 넘긴다.
v_input_string := ps_input || '$%' || ps_add;
-- MAC 함수를 사용해 입력 문자열을 RAW 타입으로 변환한다.
v_raw := DBMS_CRYPTO.MAC (src => UTL_RAW.CAST_TO_RAW(v_input_string)
,typ => DBMS_CRYPTO.HMAC_SH1
,key => v_key_raw);
IF v_raw = p_raw THEN
v_rtn := 'Y';
ELSE
v_rtn := 'N';
END IF;
RETURN v_rtn;
END fn_check_pass;
/* 8. 암호화 함수 **************************************************************************************************************************/
FUNCTION fn_encrypt ( ps_input_string IN VARCHAR2 )
RETURN RAW
IS
encrypted_raw RAW(32747);
v_key_raw RAW(32747); -- 암호화 키
encryption_type PLS_INTEGER; -- 암호화 슈트
BEGIN
-- 암호화 키 값을 가져온다.
v_key_raw := ch19_wrap_pkg.key_bytes_raw;
-- 암호화 슈트 설정
encryption_type := DBMS_CRYPTO.ENCRYPT_AES256 + -- 256비트 키를 사용한 AES 암호화
DBMS_CRYPTO.CHAIN_CBC + -- CBC 모드
DBMS_CRYPTO.PAD_PKCS5; -- PKCS5로 이루어진 패딩
-- ENCRYPT 함수로 암호화를 한다. 매개변수로 들어온 문자열을 UTL_I18N.STRING_TO_RAW를 사용해 RAW 타입으로 변환한다.
encrypted_raw := DBMS_CRYPTO.ENCRYPT ( src => UTL_I18N.STRING_TO_RAW (ps_input_string, 'AL32UTF8'),
typ => encryption_type,
key => v_key_raw
);
RETURN encrypted_raw;
END fn_encrypt;
/* 9. 복호화 함수 **************************************************************************************************************************/
FUNCTION fn_decrypt ( prw_encrypt IN RAW )
RETURN VARCHAR2
IS
vs_return VARCHAR2(100);
v_key_raw RAW(32747); -- 암호화 키
encryption_type PLS_INTEGER; -- 암호화 슈트
decrypted_raw RAW (2000); -- 복호화 데이터
BEGIN
-- 암호화 키 값을 가져온다.
v_key_raw := ch19_wrap_pkg.key_bytes_raw;
-- 암호화 슈트 설정
encryption_type := DBMS_CRYPTO.ENCRYPT_AES256 + -- 256비트 키를 사용한 AES 암호화
DBMS_CRYPTO.CHAIN_CBC + -- CBC 모드
DBMS_CRYPTO.PAD_PKCS5; -- PKCS5로 이루어진 패딩
-- 매개변수로 들어온 RAW 타입 데이터를 다시 복호화 ( 암호화했던 키와 암호화 슈트는 동일하게 사용해야 한다. )
decrypted_raw := DBMS_CRYPTO.DECRYPT ( src => prw_encrypt,
typ => encryption_type,
key => v_key_raw
);
-- 복호화된 RAW 타입 데이터를 UTL_I18N.RAW_TO_CHAR를 사용해 다시 VARCHAR2로 변환
vs_return := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
RETURN vs_return;
END fn_decrypt;
END my_util_pkg;
지금까지 이 책에서 배운 내용을 토대로 유용하게 쓸 수 있는 몇 가지 프로그램을 만들었다. 독자분들도 평소에 이런 기능을 구현한 프로그램이 있었으면 좋겠다고 생각한 것이 있다면 실제로 구현해서 사용해 보길 바란다. 덧붙어 여기에서 만들었던 프로그램을 약간 수정해 응용하면 훨씬 더 사용하기 쉽고 유용한 프로그램을 만들 수 있을 것이다.