데이터베이스
[Oracle] 암호화(PL/SQL)하기
꼬방™
2012. 1. 10. 14:47
오라클의 PL/SQL를 활용하여 기본적인 데이터베이스 암호화를 하기 위한 함수 및 사용 방법
[ 사용법 ]
SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW ('1234nextguide'))) FROM dual; SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW ('MTIzNG5leHRndWlkZQ=='))) FROM dual; SELECT UTL_ENCODE.TEXT_ENCODE('1234nextguide', 'WE8ISO8859P1', 1) FROM dual; SELECT UTL_ENCODE.TEXT_DECODE('MTIzNG5leHRndWlkZQ==', 'WE8ISO8859P1', 1) FROM dual; [ 함수만들기 ]
/*------------------------------------------------------------------------------ -- 개체명 : B2C -- 생성일 : -- 최종수정일 : -- 상태 : VALID ------------------------------------------------------------------------------*/ CREATE OR REPLACE FUNCTION B2C(B IN BLOB) RETURN CLOB TYPECASTS BLOB TO CLOB (BINARY CONVERSION) IS pos PLS_INTEGER := 1; buffer VARCHAR2( 32767 ); res CLOB; lob_len PLS_INTEGER := DBMS_LOB.getLength(b); BEGIN DBMS_LOB.createTemporary(res, TRUE); DBMS_LOB.OPEN(res, DBMS_LOB.LOB_ReadWrite); LOOP buffer := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(b, 16000, pos)); IF LENGTH( buffer ) > 0 THEN DBMS_LOB.writeAppend( res, LENGTH( buffer ), buffer ); END IF; pos := pos + 16000; EXIT WHEN pos > lob_len; END LOOP; RETURN res; -- res is OPEN here END b2c; *------------------------------------------------------------------------------ -- 개체명 : C2B -- 생성일 : -- 최종수정일 : -- 상태 : VALID ------------------------------------------------------------------------------*/ CREATE OR REPLACE FUNCTION C2B(C IN CLOB) RETURN BLOB -- TYPECASTS CLOB TO BLOB (BINARY CONVERSION) IS pos PLS_INTEGER := 1; buffer RAW( 32767 ); res BLOB; lob_len PLS_INTEGER := DBMS_LOB.getLength(c); BEGIN DBMS_LOB.createTemporary(res, TRUE); DBMS_LOB.OPEN(res, DBMS_LOB.LOB_ReadWrite); LOOP buffer := UTL_RAW.CAST_TO_RAW(DBMS_LOB.SUBSTR(c, 16000, pos)); IF UTL_RAW.LENGTH(buffer) > 0 THEN DBMS_LOB.writeAppend(res, UTL_RAW.LENGTH(buffer), buffer); END IF; pos := pos + 16000; EXIT WHEN pos > lob_len; END LOOP; RETURN res; -- res is OPEN here END c2b; *------------------------------------------------------------------------------ -- 개체명 : BASE64_B2C -- 생성일 : -- 최종수정일 : -- 상태 : VALID ------------------------------------------------------------------------------*/ CREATE OR REPLACE FUNCTION BASE64_B2C(B IN BLOB) RETURN CLOB IS v_buffer_size INTEGER := 4096; v_buffer_raw RAW(4096); v_offset INTEGER DEFAULT 1; v_clob CLOB; BEGIN DBMS_LOB.createTemporary(v_clob, FALSE, DBMS_LOB.CALL); LOOP BEGIN DBMS_LOB.READ(b, v_buffer_size, v_offset, v_buffer_raw); EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; v_offset := v_offset + v_buffer_size; DBMS_LOB.APPEND(v_clob, to_clob(utl_raw.cast_to_varchar2(utl_encode.base64_encode(v_buffer_raw)))); END LOOP; RETURN v_clob; END BASE64_B2C; /*------------------------------------------------------------------------------ -- 개체명 : BASE64_C2B -- 생성일 : -- 최종수정일 : -- 상태 : VALID ------------------------------------------------------------------------------*/ CREATE OR REPLACE FUNCTION BASE64_C2B(C IN CLOB) RETURN BLOB IS v_blob BLOB; v_offset INTEGER; v_buffer_varchar VARCHAR2(4096); v_buffer_raw RAW(4096); v_buffer_size BINARY_INTEGER := 4096; BEGIN IF c IS NULL THEN RETURN NULL; END IF; DBMS_LOB.createTemporary(v_blob, TRUE); v_offset := 1; LOOP BEGIN DBMS_LOB.READ(c, v_buffer_size, v_offset, v_buffer_varchar); EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; v_buffer_raw := UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(v_buffer_varchar)); dbms_lob.writeAppend(v_blob, UTL_RAW.LENGTH(v_buffer_raw), v_buffer_raw); v_offset := v_offset + v_buffer_size; END LOOP; RETURN v_blob; END BASE64_C2B; [출처]