4.3. Encryption in Oracle Database 10gStarting with Oracle Database 10g Release 1, Oracle provides the DBMS_CRYPTO package for encryption . In this section, I'll show how to generate keys and encrypt data using this new package. First, though, let's look at the differences between the DBMS_CRYPTO and the DBMS_OBFUSCATION_TOOLKIT packages. The DBMS_OBFUSCATION_TOOLKIT package remains available in Oracle Database 10g, although Oracle recommends that you start using the new package as the capabilities of the old package are limited compared with those of DBMS_CRYPTO. 4.3.1. Differences Between DBMS_CRYPTO and DBMS_OBFUSCATION_TOOLKITThere are a number of key differences between the DBMS_CRYPTO and DBMS_OBFUSCATION_TOOLKIT packages, summarized below:
4.3.2. Generating KeysAs mentioned earlier, the DBMS_OBFUSCATION_TOOLKIT function used to generate an encryption key, DES3GETKEY, is not available in the DBMS_CRYPTO package. A new function, RANDOMBYTES, takes its place. If you want to use my get_key function in Oracle Database 10g, you will need to change this function to use RANDOMBYTES instead. You need to be aware of the following differences when switching key generation methods:
Let's see how I need to change my get_key function to accommodate these changes. /* File on web: get_key_2.sql */ CREATE OR REPLACE FUNCTION get_key (p_length IN PLS_INTEGER) RETURN RAW IS l_ret RAW (4000); BEGIN l_ret := dbms_crypto.randombytes (p_length); RETURN l_ret; END; / Note that there is no which parameter, either. In addition, I have specified the length of the key to be generated, which is important during encryption.
The RANDOMBYTES function is a very simple one, and you may decide that you do not need a wrapper function to simplify it further. However, you may still want to wrap the function inside get_key for the following reasons.
In addition to generating keys in RAW via the RANDOMBYTES function, DBMS_CRYPTO can be used to produce numbers and binary integers . The RANDOMINTEGER function generates a binary integer key, as shown in this code segment: l_ret := DBMS_CRYPTO.randominteger; The RANDOMNUMBER function generates a key of the integer datatype with a length of 2128 as follows: l_ret := DBMS_CRYPTO.randomnumber; You may be wondering why we need an integer and a binary integer when the encryption relies on the RAW datatype only. They are not needed, strictly speaking, for encryption, but may be useful in generating pseudo-random numbers for other processing. 4.3.3. Encrypting DataAfter the key is generated, I have to encrypt my data. I do that with the ENCRYPT program in the DBMS_CRYPTO package. As with its DBMS_OBFUSCATION_TOOLKIT encryption cousin, ENCRYPT is overloaded; it provides both function and procedure variants. But in contrast to DBMS_OBFUSCATION_TOOLKIT, this overloading has a purpose in DBMS_CRYPTO. The function variant accepts only a RAW datatype as an input value, while the procedure variant accepts only CLOB and BLOB as input values. Let's look at the simplest case of RAW encryption in the ENCRYPT function . Here is the declaration of the function: DBMS_CRYPTO.encrypt( src in raw, typ in pls_integer, key in raw, iv in raw default null) return raw; You should already be familiar with three of these parameters:
The second parameter, typ, however, is new and requires a more detailed explanation. 4.3.3.1. Specifying the encryption typeThe DBMS_OBFUSCATION_TOOLKIT and DBMS_CRYPTO packages differ in how they allow you to select the type of encryption. DBMS_OBFUSCATION_TOOLKIT provides specific functions (and corresponding procedures) for each algorithmfor example, DESENCRYPT for DES and DES3ENCRYPT for Triple DES. DBMS_CRYPTO, on the other hand, provides only a single function, and the encryption type is specified via a parameter. Table 4-3 shows the algorithms available in the encryption process and their corresponding constants. You specify the desired constant in the form PackageName.Constant.Name; to select Triple DES, for example, you would use the constant DBMS_CRYPTO. ENCRYPT_3DES. Note that the AES variants and RC4 are not supported by the older package.
You specify the desired type of encryption by selecting the correct value for the typ parameter; however, this value is only a part of the value of the parameter, which encodes other information as well, as the next section describes. 4.3.3.2. Specifying chainingWhile encrypting data, each block to be encrypted can be either independently encrypted or chained with previous blocks to produce a more cryptographically secure system. The latter produces a more secure encrypted value. You choose the desired chaining method by selecting the appropriate constant from Table 4-4 and providing it as part of the typ parameterfor example, DBMS_CRYPTO.CHAIN_OFB.
4.3.3.3. Specifying paddingRemember that in block ciphering the data must be in units of blocks. What if the data is not an exact multiple? With DBMS_OBFUSCATION_TOOLKIT, you have to pad the data explicitly to make its length a multiple. However, this practice is not considered cryptographically secure. DBMS_CRYPTO allows you to specify the type of padding; most organizations use the PKCS#5 method. You choose the desired padding method by selecting the appropriate constant from Table 4-5 and providing it as part of the typ parameterfor example, DBMS_CRYPTO.PAD_PKCS5.
4.3.3.4. Combining options in the typ parameterNow let's see how to put these various options together. Suppose that you want to select these encryption options:
You can set the typ parameter as follows to express this combination of different settingsa rather lengthy string of values: typ => DBMS_CRYPTO.pad_zero + DBMS_CRYPTO.encrypt_aes128 + DBMS_CRYPTO.chain_cfb Using the same principle, you can specify any combination of options to the ENCRYPT function. Here is a typical complete call to the function: DECLARE l_enc RAW(2000); l_in RAW(2000); l_key RAW(2000); BEGIN l_enc := DBMS_CRYPTO.encrypt (src => l_in, KEY => l_key, typ => DBMS_CRYPTO.pad_zero + DBMS_CRYPTO.encrypt_aes128 + DBMS_CRYPTO.chain_cfb ); END; To make things more convenient, the package provides two constants with a predefined combination of values for these three parameters. Table 4-6 shows these constants and the set of encryption, padding, and chaining options they represent.
Assuming that I still want to specify the DES algorithm, PKCS#5 padding, and cipher block chaining (CBC), I would use the combination constant as follows. DECLARE l_enc RAW(2000); l_in RAW(2000); l_key RAW(2000); BEGIN l_enc := DBMS_CRYPTO.encrypt (src => l_in, KEY => l_key, typ => DBMS_CRYPTO.des_cbc_pkcs5 ); END; / Now let's rewrite the original function to encrypt a value as follows. CREATE OR REPLACE FUNCTION get_enc_val ( p_in_val IN RAW, p_key IN RAW, p_iv IN RAW := NULL ) RETURN RAW IS l_enc_val RAW (4000); BEGIN l_enc_val := DBMS_CRYPTO.encrypt (src => p_in_val, KEY => p_key, iv => p_iv, typ => DBMS_CRYPTO.encrypt_aes128 + DBMS_CRYPTO.chain_cbc + DBMS_CRYPTO.pad_pkcs5 ); RETURN l_enc_val; END; / 4.3.3.5. Handling and converting RAW dataThis function accepts the input values in RAW and assumes that you want to use the 128-bit AES encryption algorithm, PKCS#5 padding, and Cipher Block Chaining. In real-world applications, these assumptions may be too constraining. For instance, the input values are usually in VARCHAR2 or some numeric datatype, not RAW. Let's make the function more generic by having it accept VARCHAR2 instead of RAW. Because the ENCRYPT function requires RAW input, that means I will have to convert my original input to RAW. I can do this as follows: l_in := UTL_I18N.string_to_raw (p_in_val, 'AL32UTF8'); You may recall that earlier in the chapter I used the built-in UTL_RAW package to convert from VARCHAR to RAW. But here I am using the function UTL_I18N. STRING_TO_RAW, rather than UTL_RAW.CAST_TO_RAW, to do that conversion. Why? The ENCRYPT function requires the input to be RAW, but it also requires a specific character setAL32UTF8, which may not be the character set of the database. So I actually have to perform two conversions:
The CAST_TO_RAW function can't perform the character set conversion, but the STRING_TO_RAW function in the built-in package UTL_i18n can handle both.
ENCRYPT also returns a RAW datatype, which may not be convenient to store in the database or easy to manipulate. I can convert the value from RAW to VARCHAR2 as follows: l_enc_val := rawtohex(l_enc_val); 4.3.3.6. Specifying the encryption algorithmRecall from the earlier discussion that the choice of algorithm depends on several factorsfor example, upgrading from Oracle9i Database to Oracle Database 10g. If the source or destination of the encrypted data is in Oracle9i Database, you will not have access to the DBMS_CRYPTO package. Instead, you have to use DBMS_OBFUSCATION_TOOLKIT, which does not offer the AES algorithms. So even though AES algorithms are more secure and efficient, you will have no choice but to use something else, such as DES. For additional security, you might want to use 3DES (but be aware that it is slower than DES). In many cases you may need to choose different algorithms to satisfy different conditions, while the other two modifierspadding and chainingwill remain the same. Unfortunately, the ENCRYPT function does not allow you to define the type of encryption algorithm directly; it must be passed as a parameter along with other modifiers (e.g., padding and chaining). You can accomplish this yourself, however, by using a new parameter (p_algorithm) included in my user-defined generic encryption package. That parameter will accept only the following values, indicating the types of algorithms supported by DBMS_CRYPTO:
The passed value is then appended to the term "ENCRYPT_" and passed to the ENCRYPT function. The following code does just that: l_enc_algo := CASE p_algorithm WHEN 'DES' THEN DBMS_CRYPTO.encrypt_des WHEN '3DES_2KEY' THEN DBMS_CRYPTO.encrypt_3des_2key WHEN '3DES' THEN DBMS_CRYPTO.encrypt_3des WHEN 'AES128' THEN DBMS_CRYPTO.encrypt_aes128 WHEN 'AES192' THEN DBMS_CRYPTO.encrypt_aes192 WHEN 'AES256' THEN DBMS_CRYPTO.encrypt_aes256 WHEN 'RC4' THEN DBMS_CRYPTO.encrypt_rc4 END; 4.3.3.7. Putting it togetherPutting everything together, the get_enc_val function now looks like this: /* File on web: get_enc_val_6.sql */ CREATE OR REPLACE FUNCTION get_enc_val ( p_in_val IN VARCHAR2, p_key IN VARCHAR2, p_algorithm IN VARCHAR2 := 'AES128', p_iv IN VARCHAR2 := NULL ) RETURN VARCHAR2 IS l_enc_val RAW (4000); l_enc_algo PLS_INTEGER; l_in RAW (4000); l_iv RAW (4000); l_key RAW (4000); l_ret VARCHAR2 (4000); BEGIN l_enc_algo := CASE p_algorithm WHEN 'DES' THEN DBMS_CRYPTO.encrypt_des WHEN '3DES_2KEY' THEN DBMS_CRYPTO.encrypt_3des_2key WHEN '3DES' THEN DBMS_CRYPTO.encrypt_3des WHEN 'AES128' THEN DBMS_CRYPTO.encrypt_aes128 WHEN 'AES192' THEN DBMS_CRYPTO.encrypt_aes192 WHEN 'AES256' THEN dbms_crypto.encrypt_aes256 WHEN 'RC4' THEN DBMS_CRYPTO.encrypt_rc4 END; l_in := utl_i18n.string_to_raw (p_in_val, 'AL32UTF8'); l_iv := utl_i18n.string_to_raw (p_iv, 'AL32UTF8'); l_key := utl_i18n.string_to_raw (p_key, 'AL32UTF8'); l_enc_val := DBMS_CRYPTO.encrypt (src => l_in, KEY => l_key, iv => l_iv, typ => l_enc_algo + DBMS_CRYPTO.chain_cbc + DBMS_CRYPTO.pad_pkcs5 ); l_ret := RAWTOHEX (l_enc_val); RETURN l_ret; END; After this function is created, let's test it. SQL> SELECT get_enc_val ('Test','1234567890123456') 2> FROM dual 3> / GET_ENC_VAL('TEST','1234567890123456') -------------------------------------- 2137F30B29BE026DFE7D61A194BC34DD That's it; we have just built a generic encryption function that can optionally take the encryption algorithm and an initialization vector (IV). It assumes PKCS#5 padding and ECB chaining, which are common practice. If these encryption characteristics meet your requirements, this program could become your wrapper function to perform everyday encryption. 4.3.4. Decrypting DataOn the other side of the coin is the decryption process, which decodes the encrypted string using the same key used originally for encryption. Let's write a new function for decryption, called get_dec_val, using the DBMS_CRYPTO package, as follows. /* File on web: get_dec_val_2.sql */ CREATE OR REPLACE FUNCTION get_dec_val ( p_in_val IN VARCHAR2, p_key IN VARCHAR2, p_algorithm IN VARCHAR2 := 'AES128', p_iv IN VARCHAR2 := NULL ) RETURN VARCHAR2 IS l_dec_val RAW (4000); l_enc_algo PLS_INTEGER; l_in RAW (4000); l_iv RAW (4000); l_key RAW (4000); l_ret VARCHAR2 (4000); BEGIN l_enc_algo := CASE p_algorithm WHEN 'DES' THEN DBMS_CRYPTO.encrypt_des WHEN '3DES_2KEY' THEN DBMS_CRYPTO.encrypt_3des_2key WHEN '3DES' THEN DBMS_CRYPTO.encrypt_3des WHEN 'AES128' THEN DBMS_CRYPTO.encrypt_aes128 WHEN 'AES192' THEN DBMS_CRYPTO.encrypt_aes192 WHEN 'AES256' THEN DBMS_CRYPTO.encrypt_aes256 WHEN 'RC4' THEN DBMS_CRYPTO.encrypt_rc4 END; l_in := hextoraw(p_in_val); l_iv := utl_i18n.string_to_raw (p_iv, 'AL32UTF8'); l_key := utl_i18n.string_to_raw (p_key, 'AL32UTF8'); l_dec_val := DBMS_CRYPTO.decrypt (src => l_in, KEY => l_key, iv => l_iv, typ => l_enc_algo + DBMS_CRYPTO.chain_cbc + DBMS_CRYPTO.pad_pkcs5 ); l_ret := utl_i18n.raw_to_char (l_dec_val, 'AL32UTF8'); RETURN l_ret; END; Let's test this function. To decrypt the value encrypted earlier, I can use: SQL> SELECT get_dec_val ('2137F30B29BE026DFE7D61A194BC34DD', '1234567890123456') 2> FROM DUAL 3> / GET_DEC_VAL('2137F30B29BE026DFE7D61A194BC34DD','1234567890123456') --------------------------------------------------------------------------- Test There it is; I just got back my original value. Note that I am using the same key employed to encrypt earlier. When you are decrypting an encrypted value, you must use exactly the same key, algorithm, padding method, and chaining method used during encryption. You might consider using get_dec_val as a generic program to decrypt encrypted values. For simplicity, ease of management, and security, I suggest that you place this set of encryption and decryption functions inside a package of your own construction. Before closing this section, let me mention an important concept. In the previous two examples, I used VARCHAR2 input and output values. Recall, however, that encryption and decryption is done inside the database as RAW, so we had to convert the data and key from RAW to VARCHAR2 and then back to RAW again. Although doing this simplified our presentation, it may not be acceptable in some cases. See the sidebar "When Should You Use Raw Encryption?" earlier in the chapter. |