아래 원문을 정리한 것.
http://www.dbasupport.com/oracle/ora9i/DBMS_OBFUSCATION_TOOLKIT.shtml
이전에 이미 이 패키지에 대한 글을 적은 적이 있는데(이 게시판의 첫번째 글로 있을 것이다)
복습삼아 또 정리해봤다.
DB에 저장된 비밀번호, 주민등록번호, 신용카드번호등의 필드를 암호화하여 저장하는데 적용될 수 있을 것이다.
아래에서는 원문을 수정한 부분도 있지만, 전반적으로 동일하게 했다.
아래는 Encryption/Decryption 예를 보인 Anonymous PL/SQL블럭이다.
SQL> set serveroutput on
SQL> DECLARE
2 input_string VARCHAR2(32) := 'dbakorea.pe.kr주인장 강명규 白手'; -- 비밀번호, 주민등록번호등
3 key_string VARCHAR2(8) := '적수공권';
4
5 encrypted_string VARCHAR2(2048);
6 decrypted_string VARCHAR2(2048);
7
8 error_in_input_buffer_length EXCEPTION;
9
10 PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
11 INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
12 '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES ***';
13
14 BEGIN
15 dbms_output.put_line('> ========= BEGIN TEST =========');
16 dbms_output.put_line('> Input string : ' || input_string);
17 --BEGIN <-- ignore this, typo in Oracle's documentation
18 dbms_obfuscation_toolkit.DESEncrypt(
19 input_string => input_string,
20 key_string => key_string,
21 encrypted_string => encrypted_string );
22 dbms_output.put_line('> Encrypted string : ' || encrypted_string);
23 -- Add DESDecrypt as shown, change raw to key_string
24 dbms_obfuscation_toolkit.DESDecrypt(
25 input_string => encrypted_string,
26 key_string => key_string,
27 decrypted_string => decrypted_string);
28 dbms_output.put_line('> Decrypted output : ' || decrypted_string);
29 dbms_output.put_line('> ');
30 if input_string = decrypted_string THEN
31 dbms_output.put_line('> DES Encryption and Decryption successful');
32 END IF;
33 EXCEPTION
34
35 WHEN error_in_input_buffer_length THEN
36 dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
37 END;
38 /
> ========= BEGIN TEST =========
> Input string : dbakorea.pe.kr주인장 강명규 白手
> Encrypted string : [aS] > Decrypted output : dbakorea.pe.kr주인장 강명규 白手
>
> DES Encryption and Decryption successful
PL/SQL procedure successfully completed.
위와 동일한 예이지만, 알아볼 수 없는 암호화된 문자열대신 16진수로 보여준다.
SQL> DECLARE
2 input_string VARCHAR2(32) := 'dbakorea.pe.kr주인장 강명규 白手';
3 raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
4 key_string VARCHAR2(8) := '적수공권';
5 raw_key RAW(128) := UTL_RAW.CAST_TO_RAW(key_string);
6 encrypted_raw RAW(2048);
7 encrypted_string VARCHAR2(2048);
8 decrypted_raw RAW(2048);
9 decrypted_string VARCHAR2(2048);
10 error_in_input_buffer_length EXCEPTION;
11
12 PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
13 INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
14 '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING
15 EXCEPTION ***';
16 double_encrypt_not_permitted EXCEPTION;
17 PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233);
18 DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
19 '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';
20
21 -- 1. Begin testing raw data encryption and decryption
22 BEGIN
23 dbms_output.put_line('> ========= BEGIN TEST RAW DATA =========');
24 dbms_output.put_line('> Raw input : ' ||
25 UTL_RAW.CAST_TO_VARCHAR2(raw_input));
26 BEGIN
27 dbms_obfuscation_toolkit.DESEncrypt(input => raw_input,
28 key => raw_key, encrypted_data => encrypted_raw );
29 dbms_output.put_line('> encrypted hex value : ' ||
30 rawtohex(encrypted_raw));
31 dbms_obfuscation_toolkit.DESDecrypt(input => encrypted_raw,
32 key => raw_key, decrypted_data => decrypted_raw);
33 dbms_output.put_line('> Decrypted raw output : ' ||
34 UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw));
35 dbms_output.put_line('> ');
36 if UTL_RAW.CAST_TO_VARCHAR2(raw_input) =
37 UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw) THEN
38 dbms_output.put_line('> Raw DES Encyption and Decryption successful');
39 END if;
40 EXCEPTION
41 WHEN error_in_input_buffer_length THEN
42 dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
43 END;
44 dbms_output.put_line('> ');
45
46
47 -- 2. Begin testing string data encryption and decryption
48 dbms_output.put_line('> ========= BEGIN TEST STRING DATA =========');
49
50 BEGIN
51 dbms_output.put_line('> input string : '
52 || input_string);
53 dbms_obfuscation_toolkit.DESEncrypt(
54 input_string => input_string,
55 key_string => key_string,
56 encrypted_string => encrypted_string );
57 dbms_output.put_line('> encrypted hex value : ' ||
58 rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_string)));
59 dbms_obfuscation_toolkit.DESDecrypt(
60 input_string => encrypted_string,
61 key_string => key_string,
62 decrypted_string => decrypted_string );
63 dbms_output.put_line('> decrypted string output : ' ||
64 decrypted_string);
65 if input_string = decrypted_string THEN
66 dbms_output.put_line('> String DES Encyption and Decryption successful');
67 END if;
68 EXCEPTION
69 WHEN error_in_input_buffer_length THEN
70 dbms_output.put_line(' ' || INPUT_BUFFER_LENGTH_ERR_MSG);
71 END;
72 dbms_output.put_line('> ');
73 END;
74 /
> ========= BEGIN TEST RAW DATA =========
> Raw input : dbakorea.pe.kr주인장 강명규 白手
> encrypted hex value : 5B61535D3C6C014D4441FDF2F696B45212A24C576DA0E209ADCA53A770CA9D42
> Decrypted raw output : dbakorea.pe.kr주인장 강명규 白手
>
> Raw DES Encyption and Decryption successful
>
> ========= BEGIN TEST STRING DATA =========
> input string : dbakorea.pe.kr주인장 강명규 白手
> encrypted hex value : 5B61535D3C6C014D4441FDF2F696B45212A24C576DA0E209ADCA53A770CA9D42
> decrypted string output : dbakorea.pe.kr주인장 강명규 白手
> String DES Encyption and Decryption successful
>
PL/SQL procedure successfully completed.
SQL> create table cc_table (empno integer, cc_no char(16));
Table created.
SQL> insert into cc_table values(4569, '4323445698329120');
1 row created.
SQL> select * from cc_table;
EMPNO CC_NO
---------- ----------------
4569 4323445698329120
empno를 가진 row의 cc_no필드값을 암호화시키는 프로시저
SQL> CREATE or REPLACE PROCEDURE encrypt_cc_no (empno number)
2 IS
3 input_string VARCHAR2(16);
4 key_string VARCHAR2(8) := '내맘대로';
5 encrypted_string VARCHAR2(2048);
6 BEGIN
7 select cc_no into input_string from cc_table where empno = empno;
8
9 dbms_obfuscation_toolkit.DESEncrypt(
10 input_string => input_string,
11 key_string => key_string,
12 encrypted_string => encrypted_string);
13
14 update cc_table set cc_no = encrypted_string where empno = empno;
15 END;
16 /
Procedure created.
SQL> exec encrypt_cc_no(4569);
PL/SQL procedure successfully completed.
SQL> select * from cc_table;
EMPNO CC_NO
---------- ----------------
4569 dM$9v?X2;x?W
empno를 가진 row의 cc_no필드값을 복호화시키는 프로시저
SQL> CREATE or REPLACE PROCEDURE decrypt_cc_no (empno number)
2 IS
3 key_string VARCHAR2(8) := '내맘대로';
4 encrypted_string VARCHAR2(2048);
5 decrypted_string VARCHAR2(2048);
6
7 BEGIN
8 select cc_no into encrypted_string from cc_table where empno = empno;
9
10 dbms_obfuscation_toolkit.DESDecrypt(
11 input_string => encrypted_string,
12 key_string => key_string,
13 decrypted_string => decrypted_string);
14
15 update cc_table set cc_no = decrypted_string where empno = empno;
16
17 END;
18 /
Procedure created.
SQL> exec decrypt_cc_no(4569);
PL/SQL procedure successfully completed.
SQL> select * from cc_table;
EMPNO CC_NO
---------- ----------------
4569 4323445698329120
SQL>
|
This article comes from dbakorea.pe.kr (Leave this line as is)
|
