Section 4.2. Encryption in Oracle9i Database
4.2. Encryption in Oracle9i DatabaseLet's start our detailed discussion of encryption of Oracle data by looking at the DBMS_OBFUSCATION_TOOLKIT package. Although Oracle now recommends that you use the newer DBMS_CRYPTO package, most organizations have not yet converted their applications to use the new capabilities, so it makes sense to begin with the older package.
4.2.1. Encrypting DataIt's time to see encryption in action in the Oracle database. I'll show a simple example and then explain the details. Let's assume here that you are trying to get the encrypted value of the string "SHHH..TOP SECRET". The following snippet of code does the trick, invoking the DES3ENCRYPT program in the DBMS_OBSFUSCATION_TOOLKIT package: 1 DECLARE 2 l_enc_val VARCHAR2 (200); 3 BEGIN 4 DBMS_OBFUSCATION_TOOLKIT.des3encrypt 5 (input_string => 'SHHH..TOP SECRET', 6 key_string => 'ABCDEFGHIJKLMNOP', 7 encrypted_string => l_enc_val 8 ); 9 DBMS_OUTPUT.put_line ('Encrypted Value = ' || l_enc_val); 10 END;The output is: Encrypted Value = ¿jVªå¬F.(e)?«?0 Line 6 specifies the key string used to encrypt the value, a key that is 16 characters long. The encrypted value is a VARCHAR2 value but is full of control characters. This type of output may not be useful in real-life applications, especially if you want to store it, print it, or tell someone what it is; we may have to make it more manageable by changing it into readable characters. Note, however, that sometimes you don't want to convert the values from or to the RAW datatype; see the sidebar "When Should You Use Raw Encryption?" later in this chapter. Our first task is to convert the value to the RAW datatype using the built-in package UTL_RAW. l_enc_val := utl_raw.cast_to_raw(l_enc_val); Next, I convert this to hexadecimal using the function RAWTOHEX to make it easier to manipulate: l_enc_val := rawtohex(utl_raw.cast_to_raw(l_enc_val)); This will change my PL/SQL block to: DECLARE l_enc_val VARCHAR2 (200); BEGIN DBMS_OBFUSCATION_TOOLKIT.des3encrypt (input_string => 'SHHH..TOP SECRET', key_string => 'ABCDEFGHIJKLMNOP', encrypted_string => l_enc_val ); l_enc_val := RAWTOHEX (UTL_RAW.cast_to_raw (l_enc_val)); DBMS_OUTPUT.put_line ('Encrypted Value = ' || l_enc_val); END;The output is: Encrypted Value = A86A56A6EE92462E28652903ECAEC730 The output is now a hexadecimal string, easily stored and manipulated in VARCHAR2 fields of tables. You could also convert the output to a number for even easier numeric manipulation, but you are generally better off using only hexadecimal values as characters, as they convey meaning as encrypted data while numbers do not. l_enc_val := to_number('A86A56A6EE92462E28652903ECAEC730', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') 223862444271805716712258987042708309808Using the encryption programs in the DBMS_OBFUSCATION_TOOLKIT package as a base, I'll build some wrapper functions around them to make things easier and more flexible to use.
/* File on web: get_enc_val_1.sql */ CREATE OR REPLACE FUNCTION get_enc_val (p_in_val IN VARCHAR2, p_key IN VARCHAR2) RETURN VARCHAR2 IS l_enc_val VARCHAR2 (200); BEGIN l_enc_val := DBMS_OBFUSCATION_TOOLKIT.des3encrypt (input_string => p_in_val, key_string => p_key ); l_enc_val := RAWTOHEX (UTL_RAW.cast_to_raw (l_enc_val)); RETURN l_enc_val; END; Using this function from the earlier example, I get the desired results. SQL> SET SERVEROUTPUT ON SQL> DECLARE 2> v_enc VARCHAR2 (200); 3> BEGIN 4> v_enc := get_enc_val ('SHHH..TOP SECRET', 'ABCDEFGHIJKLMNOP'); 5> DBMS_OUTPUT.put_line ('Encrypted value = ' || v_enc); 6> END; 7> / Encrypted value = A86A56A6EE92462E28652903ECAEC730 PL/SQL procedure successfully completed.Note that the actual value produced on your system could be different, as a result of character set differences; this is a very important concept that I'll explain later in this chapter. You can use this encryption function in a variety of waysinserting data into encrypted columns, passing encrypted data to other functions or procedures, and much more. Before going further, let's test this function by passing it different input. In the earlier example, we used a specific string to encrypt"SHHH..TOP SECRET". Here we will use a different value to encrypt: DECLARE v_enc VARCHAR2 (200); BEGIN v_enc := get_enc_val ('A DIFFERENT VALUE', 'ABCDEFGHIJKLMNOP'); DBMS_OUTPUT.put_line ('Encrypted value = ' || v_enc); END; /Uh-oh. This time it throws an error immediately. DECLARE * ERROR at line 1: ORA-28232: invalid input length for obfuscation toolkit ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 0 ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 216 ORA-06512: at "SCOTT.GET_ENC_VAL", line 10 ORA-06512: at line 4 What went wrong here? The only thing that changed was the input string: the first one I passed was 16 characters long, but the second one was 17 characters. It turns out that the input to the function DES3ENCRYPT must be an exact multiple of eight characters; if it is not, the exception will trigger an ORA-28232 error. In this type of encryption, known as block ciphering , the encryption routines work on a block of characters at a time (where a block is eight characters long). If the length of the input value is not a multiple of eight, the string has to be padded to make it so, as discussed earlier in the "Padding and Chaining" section. I can easily modify the input string to make it such inside the function, as follows: /* File on web: get_enc_val_2.sql */ CREATE OR REPLACE FUNCTION get_enc_val (p_in_val IN VARCHAR2, p_key IN VARCHAR2) RETURN VARCHAR2 IS l_enc_val VARCHAR2 (200); l_in_val VARCHAR2 (200); BEGIN l_in_val := RPAD (p_in_val, (8 * ROUND (LENGTH (p_in_val) / 8, 0) + 8)); l_enc_val := DBMS_OBFUSCATION_TOOLKIT.des3encrypt (input_string => l_in_val, key_string => p_key ); l_enc_val := RAWTOHEX (UTL_RAW.cast_to_raw (l_enc_val)); RETURN l_enc_val; END; / The only difference here is that I have modified the input string to right-pad it with blank spaces to make it a multiple of eight bytes. If you use this modified function, you can pass strings of any length to be encrypted.
4.2.2. Specifying an Initialization VectorThe encryption described in the previous section works very well for most situations. However some intruders are still one step ahead of us. One of the code-cracking tools (also known as cryptoanalysis ) they employ is to check the header information of the encrypted data to identify a pattern. To prevent this, you can add a non-data-related random value to the beginning of your actual data. This is a bit like creating some very simple encrypted data of your own. For example, if your actual data is 12345678, you could affix a random value, say 6675, before it to make it 667512345678, which can then be encrypted. The header information then contains some value related to 6675, not the actual data. When decrypting , you need to make sure to remove these random characters. The random characters prefixed to the data are known as the initialization vector (IV). In the DBMS_OBFUSCATION_TOOLKIT, you specify this initialization vector in the DES3ENCRYPT function as an additional parameter called iv_string. Because the IV is prefixed to the actual data, the length of the combined string, not just the data, must be a multiple of eight. Let's modify our encryption function to accept this parameter and make the length a multiple of eight. /* File on web get_enc_val_3.sql */ CREATE OR REPLACE FUNCTION get_enc_val ( p_in_val IN VARCHAR2, p_key IN VARCHAR2, p_iv IN VARCHAR2 := NULL ) RETURN VARCHAR2 IS l_enc_val VARCHAR2 (200); l_in_val VARCHAR2 (200); l_iv VARCHAR2 (200); BEGIN l_in_val := RPAD (p_in_val, (8 * ROUND (LENGTH (p_in_val) / 8, 0) + 8)); l_iv := RPAD (p_iv, (8 * ROUND (LENGTH (p_iv) / 8, 0) + 8)); l_enc_val := DBMS_OBFUSCATION_TOOLKIT.des3encrypt (input_string => l_in_val, key_string => p_key, iv_string => l_iv ); l_enc_val := RAWTOHEX (UTL_RAW.cast_to_raw (l_enc_val)); RETURN l_enc_val; END; 4.2.3. Decrypting DataSo far, I have focused on encrypting the data; let's see how to decrypt it to get back the original value with the DES3DECRYPT function . In the following PL/SQL block, I will create an encrypted value from cleartext and then decrypt it. DECLARE l_enc_val VARCHAR2 (2000); l_dec_val VARCHAR2 (2000) := 'Clear Text Data'; l_key VARCHAR2 (2000) := 'ABCDEFGHIJKLMNOP'; BEGIN l_enc_val := get_enc_val (l_dec_val, l_key, '12345678'); l_dec_val := DBMS_OBFUSCATION_TOOLKIT.des3decrypt (input_string => UTL_RAW.cast_to_varchar2 (HEXTORAW (l_enc_val) ), key_string => l_key ); DBMS_OUTPUT.put_line ('Decrypted Value = ' || l_dec_val); END; /The output is: Decrypted Value = s}?2+¬xt Data PL/SQL procedure successfully completed. Wait! The decrypted value is different from the input given. What went wrong? Note the parameters to the DES3DECRYPT function. Have you supplied the IV to it? Because an IV was specified during the encryption process, it must be specified during decryption, as well. Let's rewrite the block with the IV value of 12345678: DECLARE l_enc_val VARCHAR2 (2000); l_dec_val VARCHAR2 (2000) := 'Clear Text Data'; l_key VARCHAR2 (2000) := 'ABCDEFGHIJKLMNOP'; BEGIN l_enc_val := get_enc_val (l_dec_val, l_key, '12345678'); l_dec_val := DBMS_OBFUSCATION_TOOLKIT.des3decrypt (input_string => UTL_RAW.cast_to_varchar2 (HEXTORAW (l_enc_val) ), key_string => l_key, iv_string => '12345678' ); DBMS_OUTPUT.put_line ('Decrypted Value = ' || l_dec_val); END; /The output is as expected: Decrypted Value = Clear Text Data PL/SQL procedure successfully completed.
In a way, IV acts as a key or a part of the key, but it can't be relied on as a key as such. Why? Consider the following code. DECLARE l_enc_val VARCHAR2 (2000); l_dec_val VARCHAR2 (2000) := 'Clear Text Data'; l_key VARCHAR2 (2000) := 'ABCDEFGHIJKLMNOP'; BEGIN l_enc_val := get_enc_val (l_dec_val, l_key, '12345678'); l_dec_val := DBMS_OBFUSCATION_TOOLKIT.des3decrypt (input_string => UTL_RAW.cast_to_varchar2 (HEXTORAW (l_enc_val) ), key_string => l_key, iv_string => '1234567X' ); DBMS_OUTPUT.put_line ('Decrypted Value = ' || l_dec_val); END; /The output is: Decrypted Value = Clear T?xt Data PL/SQL procedure successfully completed. The IV parameter is 12345678 during encryption but 1234567X during decryption; only the eighth character has changed. That's because the decrypted value is not exactly the same as the input value; the eighth character is a nonprintable one instead of the letter e. Although the returned data is not exactly the same, it might be easier to guess by supplying random values for the initialization vector, a procedure known as a brute-force attack. Because IVs are typically shorter than keys, that guess may take less time, so you should not rely on the IV as a key.
4.2.4. Encrypting RAW DataWe've talked a bit about the use of RAW data. Here we'll explore how you can encrypt data whose datatype is RAW by taking advantage of the fact that within the DBMS_OBFUSCATION_TOOLKIT package, the DES3ENCRYPT and DES3DECRYPT programs are overloaded. That means that they have several variants. Each has a procedure format in which exactly the same parameters are passed as input parameters and the return value is passed back to the user using an OUT parameter named either encrypted_string or decrypted_string (depending on whether you are encrypting or decrypting). The functions and procedures are also overloaded to accommodate the RAW datatype for the parameters. You will use these variants if you need to manipulate raw data such as large objects (LOBs). It is certainly possible to convert RAW values as shown here when doing encryption and decryption: /* File on web: enc_raw.sql */ CREATE OR REPLACE FUNCTION enc_raw ( p_in_val IN VARCHAR2, p_key IN VARCHAR2, p_iv IN VARCHAR2 ) RETURN VARCHAR2 IS l_enc_val RAW (200); l_in_val RAW (200); l_iv RAW (200); BEGIN l_in_val := UTL_RAW.cast_to_raw (RPAD (p_in_val, (8 * ROUND (LENGTH (p_in_val) / 8, 0) + 8 ) ) ); l_iv := UTL_RAW.cast_to_raw (RPAD (p_iv, (8 * ROUND (LENGTH (p_iv) / 8, 0) + 8))); l_enc_val := DBMS_OBFUSCATION_TOOLKIT.des3encrypt (input => l_in_val, KEY => p_key, iv => l_iv ); RETURN RAWTOHEX (UTL_RAW.cast_to_raw (l_enc_val)); END; / However, the additional processing required for conversion between the RAW and VARCHAR2 datatypes might actually hurt performance, rather than help it. In my tests, this version for VARCHAR2 and NUMBER datatypes underperformed the plain string version by about 50%. Because encryption is a CPU-intensive process, this measurement may vary widely based on the host system. However, the general rule of thumb is to avoid this raw manipulation if possible if your data is primarily character-based and you use only one type of character set. 4.2.5. Performing Multi-Pass EncryptionEarlier in this chapter, in the "Encryption Algorithms" section, I mentioned that the DES standard had been enhanced so that content could pass through the process two
or three times, leading to the name Triple DES, or DES3. Oracle's implementation of DES3 through the function DES3ENCRYPT uses the two-pass scheme by default. However, you can instruct the function to use three passes via a new parameter called which. The default value, 0, indicates only two passes, and 1 indicates three. Performing three passes, of course, provides stronger encryption. To use the three-pass scheme, you must use a key of at least 24 bytes, instead of the 16-byte one we've been using up until now. I can change the original function as follows to allow the user to specify the two-pass or three-pass process. /* File on web: get_enc_val_4.sql */ CREATE OR REPLACE FUNCTION get_enc_val ( p_in_val IN VARCHAR2, p_key IN VARCHAR2, p_iv IN VARCHAR2, p_which IN NUMBER := 0 ) RETURN VARCHAR2 IS l_enc_val VARCHAR2 (200); l_in_val VARCHAR2 (200); l_iv VARCHAR2 (200); BEGIN l_in_val := RPAD (p_in_val, (8 * ROUND (LENGTH (p_in_val) / 8, 0) + 8)); l_iv := RPAD (p_iv, (8 * ROUND (LENGTH (p_iv) / 8, 0) + 8)); l_enc_val := DBMS_OBFUSCATION_TOOLKIT.des3encrypt (input_string => l_in_val, key_string => p_key, iv_string => l_iv, which => p_which ); l_enc_val := RAWTOHEX (UTL_RAW.cast_to_raw (l_enc_val)); RETURN l_enc_val; END; / Changing the number of passes during encryption also means that, during decryption three passes, instead of two, must be used. During the decryption process, I must explicitly set the which parameter to 1. DECLARE l_enc_val VARCHAR2 (2000); l_dec_val VARCHAR2 (2000) := 'Clear Text Data'; l_key VARCHAR2 (2000) := 'ABCDEFGHIJKLMNOPQRSTUVWXY'; BEGIN l_enc_val := get_enc_val (l_dec_val, l_key, '12345678', 1); l_dec_val := DBMS_OBFUSCATION_TOOLKIT.des3decrypt (input_string => UTL_RAW.cast_to_varchar2 (HEXTORAW (l_enc_val) ), key_string => l_key, iv_string => '12345678', which => 1 ); DBMS_OUTPUT.put_line ('Decrypted Value = ' || l_dec_val); END; /The length of the key is now 24 bytes, the minimum required for the three-pass encryption process. 4.2.6. Putting It TogetherNow that you've learned about the components of the encryption process, let's put it all together to build our own unified tool. I'll modify our old faithful get_enc_val function as follows. /* File on web: get_enc_val_5.sql */ CREATE OR REPLACE FUNCTION get_enc_val ( p_in_val IN VARCHAR2, p_key IN VARCHAR2, p_iv IN VARCHAR2 := NULL, p_which IN NUMBER := 0 ) RETURN VARCHAR2 IS l_enc_val VARCHAR2 (200); l_in_val VARCHAR2 (200); l_iv VARCHAR2 (200); BEGIN IF p_which = 0 THEN IF LENGTH (p_key) < 16 THEN raise_application_error (-20001, 'Key length less than 16 for two-pass scheme' ); END IF; ELSIF p_which = 1 THEN IF LENGTH (p_key) < 24 THEN raise_application_error (-20002, 'Key length less than 24 for three-pass scheme' ); END IF; ELSE raise_application_error (-20003, 'Incorrect value of which ' || p_which || '; must be 0 or 1' ); END IF; l_in_val := RPAD (p_in_val, (8 * ROUND (LENGTH (p_in_val) / 8, 0) + 8)); l_iv := RPAD (p_iv, (8 * ROUND (LENGTH (p_iv) / 8, 0) + 8)); l_enc_val := DBMS_OBFUSCATION_TOOLKIT.des3encrypt (input_string => l_in_val, key_string => p_key, iv_string => l_iv, which => p_which ); l_enc_val := RAWTOHEX (UTL_RAW.cast_to_raw (l_enc_val)); RETURN l_enc_val; END; / I'll also build a similar function for decryption, named get_dec_val, as follows. /* File on web: get_dec_val_1.sql */ CREATE OR REPLACE FUNCTION get_dec_val ( p_in_val VARCHAR2, p_key VARCHAR2, p_iv VARCHAR2 := NULL, p_which NUMBER := 0 ) RETURN VARCHAR2 IS l_dec_val VARCHAR2 (2000); l_iv VARCHAR2 (2000); BEGIN IF p_which = 0 THEN IF LENGTH (p_key) < 16 THEN raise_application_error (-20001, 'Key length less than 16 for two-pass scheme' ); END IF; ELSIF p_which = 1 THEN IF LENGTH (p_key) < 24 THEN raise_application_error (-20002, 'Key length less than 24 for three-pass scheme' ); END IF; ELSE raise_application_error (-20003, 'Incorrect value of which ' || p_which || '; must be 0 or 1' ); END IF; l_iv := RPAD (p_iv, (8 * ROUND (LENGTH (p_iv) / 8, 0) + 8)); l_dec_val := DBMS_OBFUSCATION_TOOLKIT.des3decrypt (input_string => UTL_RAW.cast_to_varchar2 (HEXTORAW (p_in_val) ), key_string => p_key, iv_string => l_iv, which => p_which ); RETURN RTRIM (l_dec_val); END; / Note that I have padded the IV parameter to make its length a multiple of eight. Remember that during encryption I padded the input value to make its length a multiple of eight; after decryption, I have to remove those added blank spaces, as we did in the return string of the above function.
4.2.7. Generating KeysIt should be apparent from the discussion so far that the weakest link in the chain is the encryption key. To successfully decrypt the encrypted data, the key is literally thatthe key, and to protect the encryption, you must make that key very difficult to guess. In the examples we have presented so far, we have used a 16-byte key for DES3 two-pass encryption and a 24-byte key for DES3 three-pass encryption. There are two important points to remember about using a proper encryption key:
4.2.7.1. Using DES3GETKEYThe DBMS_OBFUSCATION_TOOLKIT provides a function, DES3GETKEY (and as usual, a procedure, and both formats are overloaded with multiple datatypes) that allows you to generate a cryptographically acceptable key. The function needs a seed value as a starting point to generate a random value that can be used as a key.
You can call the function as follows: l_ret := DBMS_OBFUSCATION_TOOLKIT.des3getkey ( seed_string => l_seed ); The value of l_seed must be a random string of 80 characters; a longer value is accepted, but only 80 characters will be used. The value returned is a VARCHAR2, stored in the variable l_ret. Because the seed length must be 80 characters, let's use a simple scheme to generate the value. Note that this is not the actual key; it is just the seed. (For a detailed discussion of seeds, see Chapter 7.) l_seed varchar2(2000) := '1234567890'|| '1234567890'|| '1234567890'|| '1234567890'|| '1234567890'|| '1234567890'|| '1234567890'|| '1234567890' This function DES3GETKEY returns a value in binary format, which probably should be converted to a usable type such as VARCHAR2, so I can modify the returned key as follows: l_ret := rawtohex(utl_raw.cast_to_raw(l_ret)); This converts the key to RAW and then to a hexadecimal value. One more parameterwhichis used to specify either the two-pass or three-pass method. Putting everything together, my function to generate the key looks like this. /* File on web: get_key_1.sql */ 1 CREATE OR REPLACE FUNCTION get_key ( 2 p_seed VARCHAR2 := '1234567890' 3 || '1234567890' 4 || '1234567890' 5 || '1234567890' 6 || '1234567890' 7 || '1234567890' 8 || '1234567890' 9 || '1234567890', 10 p_which NUMBER := 0 11 ) 12 RETURN VARCHAR2 13 IS 14 l_seed VARCHAR2 (80); 15 l_ret VARCHAR2 (2000); 16 BEGIN 17 l_seed := RPAD (p_seed, 80); 18 l_ret := 19 DBMS_OBFUSCATION_TOOLKIT.des3getkey (seed_string => l_seed, 20 which => p_which 21 ); 22 l_ret := RAWTOHEX (UTL_RAW.cast_to_raw (l_ret)); 23 RETURN l_ret; 24* END; The following table explains the significant elements of this code.
This function will return a cryptographically random value each time it is called. Let's see how it works. BEGIN DBMS_OUTPUT.put_line ('Key=' || get_key); DBMS_OUTPUT.put_line ('Key=' || get_key); DBMS_OUTPUT.put_line ('Key=' || get_key); DBMS_OUTPUT.put_line ('Key=' || get_key); DBMS_OUTPUT.put_line ('Key=' || get_key); END;The output is: Key=4992D7CCC6B9428F11D7EC612E728C02 Key=4DB67B0610E3EB2EB6B7B6B39DC4DB13 Key=4DC1F80A3FE4FC266A667CE2A11E25C9 Key=111768ECC7E6F0C5DFAD6B9B0C146C9A Key=75FE17395B8209FC578C41B26E22CBC7 Note how the key generated is different in each case, even though the seed is the same. The actual output may be different when you run this; this is supposed to be random. 4.2.7.2. Using the key in encryptionUsing the functions developed above, I can satisfactorily encrypt sensitive data. Let's see a very simple example. DECLARE l_key VARCHAR2 (80); l_enc VARCHAR2 (2000); BEGIN l_key := get_key; l_enc := get_enc_val ('Input Value', l_key); DBMS_OUTPUT.put_line ('Key = ' || l_key || ' Encrypted Value = ' || l_enc); END; /The output is: Key = 3DA5335923D784F21B0C27B61496D1AD Encrypted Value = 076A5703A745D03934B56F7500C1DCB4
Let's decrypt the encrypted value using the same key. DECLARE l_key VARCHAR2 (80) := '3DA5335923D784F21B0C27B61496D1AD'; l_enc VARCHAR2 (2000) := '076A5703A745D03934B56F7500C1DCB4'; l_dec VARCHAR2 (2000); BEGIN l_dec := get_dec_val (l_enc, l_key); DBMS_OUTPUT.put_line ('Decrypted Value = ' || l_dec); END; /The output is: Decrypted Value = Input Value Using the functions get_key, get_enc_val, and get_dec_val we can build a complete encryption system, as you'll see in the next section. 4.2.8. A Practical Encryption ExampleHow can we use our newfound knowledge of encryption in a system that we might encounter in the real world? Let's examine a table named ACCOUNTS. The table looks like this. SQL> DESC accounts Name Null? Type ----------------------------------------- -------- ------------------- ACCOUNT_NO NOT NULL NUMBER BALANCE NUMBER ACCOUNT_NAME VARCHAR2(200) I want to protect the data by encrypting the columns BALANCE and ACCOUNT_NAME. As I've said many times, the most important element is the key, and it must be an appropriate one. I can generate a key, use it to encrypt the column value, and then store the key and the encrypted value somewhere to be retrieved later. How exactly can I do this? I have a few choices:
4.2.9. Storing the KeysStoring the keys is the most crucial part of the encryption exercise. If you don't do this properly, the whole point of safeguarding data by encrypting it becomes moot. There are a variety of storage options:
|
