이것은 mysql에서의 set password('') 와 비슷한 기능이다. 하지만 오라클은 DES 알고리즘으로 문자열을 암호화한다.
Decrypting Oracle's DBMS_OBFUSCATION_TOOLKIT By Steve Callan
To secure sensitive data, Oracle provides developers with the DBMS_OBFUSCATION_TOOLKIT. This supplied PL/SQL package, available in both the Standard and Enterprise editions, is the industrial strength version of the DBMS_RANDOM package. Upon reading Oracle's documentation about this package, you will need a decoder just to keep track of all the acronyms and to decrypt the errors in Oracle's sample code. However, once you see how easy it is to use, you will be able to take Oracle's code example and modify it for your own use.
If you have never worked or dealt with encryption, Oracle's documentation provides a short summary of encryption principles in the Oracle9i Supplied PL/SQL Packages and Types Reference documentation. Like many other things in Oracle, just because you can do something does not mean you should do it. Encryption is one of those features best used when used appropriately. What is not pointed out is this truism: what is created by man can be broken by man. How does this apply to Oracle's obfuscation toolkit? Let's look at the one of the Data Encryption Standard options.
Oracle states that the "Triple DES (3DES) is a far stronger cipher than DES; the resulting ciphertext (encrypted data) is much harder to break using an exhaustive search: 2**112 or 2**168 attempts instead of 2**56 attempts." What is the significance of these numbers? Suppose you build a computer capable of making 1000 attempts each second. How long would it take to exhaust 2 to the 56 (256) attempts? Before you get your calculator, take a wild guess. Did you guess something close to just over two million years? A very significant obstacle in launching a brute force attack against encrypted data is time. If you are dealing with 2 to the 168 attempts, your next biggest obstacle, after time itself, is the sun, because it will go supernova many billions of years before you'll finish.
Unlike port assignments, when it comes to encryption, the standards are much clearer, and in fact, are promulgated by the government. You can read more about the standards (plus get some background on encryption) at http://www.itl.nist.gov/fipspubs/fip46-2.htm.
Oracle's documentation
One example Oracle uses in its documentation may leave you feeling a bit mystified. Why is that? Because of the errors in the code, that's why. If you use the example shown in Chapter 14 of the Oracle9i Application Developer's Guide - Fundamentals, you will need to make the changes shown in the code below.
DECLARE
input_string VARCHAR2(16) := 'tigertigertigert';
key_string VARCHAR2(8) := 'scottsco';
encrypted_string VARCHAR2(2048);
decrypted_string VARCHAR2(2048);
error_in_input_buffer_length EXCEPTION;
PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
'*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES ***';
BEGIN
dbms_output.put_line('> ========= BEGIN TEST =========');
dbms_output.put_line('> Input string : ' ||
input_string);
--BEGIN <-- ignore this, typo in Oracle's documentation
dbms_obfuscation_toolkit.DESEncrypt(
input_string => input_string,
key_string => key_string,
encrypted_string => encrypted_string );
dbms_output.put_line('> Encrypted string : ' ||
encrypted_string);
-- Add DESDecrypt as shown, change raw to key_string
dbms_obfuscation_toolkit.DESDecrypt(
input_string => encrypted_string,
key_string => key_string,
decrypted_string => decrypted_string);
dbms_output.put_line('> Decrypted output : ' ||
decrypted_string);
dbms_output.put_line('> ');
if input_string =
decrypted_string THEN
dbms_output.put_line('> DES Encryption and Decryption successful');
END IF;
EXCEPTION
WHEN error_in_input_buffer_length THEN
dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
END;
Let's run this and see what the output is like.
The encrypted string portion may look different in your SQL*Plus session because some of the characters will appear as darkened rectangles (the ANSI equivalent of "I don't know how to display this character, so here's a black rectangle for you."). Once you get past the long package and subprogram names, using the toolkit is pretty simple. The example shown in Chapter 34 of the Oracle9i Supplied PL/SQL Packages and Types Reference actually works as is and produces the output shown below.
DECLARE
input_string VARCHAR2(16) := 'tigertigertigert';
raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
key_string VARCHAR2(8) := 'scottsco';
raw_key RAW(128) := UTL_RAW.CAST_TO_RAW(key_string);
encrypted_raw RAW(2048);
encrypted_string VARCHAR2(2048);
decrypted_raw RAW(2048);
decrypted_string VARCHAR2(2048);
error_in_input_buffer_length EXCEPTION;
PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
'*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING
EXCEPTION ***';
double_encrypt_not_permitted EXCEPTION;
PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233);
DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
'*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';
-- 1. Begin testing raw data encryption and decryption
BEGIN
dbms_output.put_line('> ========= BEGIN TEST RAW DATA =========');
dbms_output.put_line('> Raw input : ' ||
UTL_RAW.CAST_TO_VARCHAR2(raw_input));
BEGIN
dbms_obfuscation_toolkit.DESEncrypt(input => raw_input,
key => raw_key, encrypted_data => encrypted_raw );
dbms_output.put_line('> encrypted hex value : ' ||
rawtohex(encrypted_raw));
dbms_obfuscation_toolkit.DESDecrypt(input => encrypted_raw,
key => raw_key, decrypted_data => decrypted_raw);
dbms_output.put_line('> Decrypted raw output : ' ||
UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw));
dbms_output.put_line('> ');
if UTL_RAW.CAST_TO_VARCHAR2(raw_input) =
UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw) THEN
dbms_output.put_line('> Raw DES Encyption and Decryption successful');
END if;
EXCEPTION
WHEN error_in_input_buffer_length THEN
dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
END;
dbms_output.put_line('> ');
-- 2. Begin testing string data encryption and decryption
dbms_output.put_line('> ========= BEGIN TEST STRING DATA =========');
BEGIN
dbms_output.put_line('> input string : '
|| input_string);
dbms_obfuscation_toolkit.DESEncrypt(
input_string => input_string,
key_string => key_string,
encrypted_string => encrypted_string );
dbms_output.put_line('> encrypted hex value : ' ||
rawtohex(UTL_RAW.CAST_TO_RAW(encrypted_string)));
dbms_obfuscation_toolkit.DESDecrypt(
input_string => encrypted_string,
key_string => key_string,
decrypted_string => decrypted_string );
dbms_output.put_line('> decrypted string output : ' ||
decrypted_string);
if input_string = decrypted_string THEN
dbms_output.put_line('> String DES Encyption and Decryption successful');
END if;
EXCEPTION
WHEN error_in_input_buffer_length THEN
dbms_output.put_line(' ' || INPUT_BUFFER_LENGTH_ERR_MSG);
END;
dbms_output.put_line('> ');
END;
> ========= BEGIN TEST RAW DATA =========
> Raw input : tigertigertigert
> encrypted hex value : 5AAB8C0D278AD75CA1968790D00FD75A
> Decrypted raw output : tigertigertigert
>
> Raw DES Encyption and Decryption successful
>
> ========= BEGIN TEST STRING DATA =========
> input string : tigertigertigert
> encrypted hex value : 5AAB8C0D278AD75CA1968790D00FD75A
> decrypted string output: tigertigertigert
> String DES Encyption and Decryption successful
This does the same thing as the previous example, except that the output is shown using hexadecimal values (it's more "readable?").
Putting it to use
Let's take the code from Oracle and modify it for our use. We will not worry about the exception code, and we will take input from the user to encrypt a 16-digit credit card number.
Here is the shortened code, turned into a procedure, along with the sample data.
So far, so good. Can the credit number be unencrypted? Let's see.
We are good to go! The credit number was successfully restored to its unencrypted value.
In closing
Although the examples shown were quite simple (and many of the variable names stayed the same to help with comparing code), the power and ease of use of the DBMS_OBFUSCATION_TOOLKIT is readily seen. If you were charged with encrypting a table's worth of credit card numbers, you would probably use a cursor to gather all of the empno's and then use a cursor for loop to update the records in one fell swoop. I hope that this article has "decrypted" some of the mystery behind one of Oracle's more powerful features.
