Section 4.3. Encryption in Oracle Database 10g

4.3. Encryption in Oracle Database 10g

Starting 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_TOOLKIT

There are a number of key differences between the DBMS_CRYPTO and DBMS_OBFUSCATION_TOOLKIT packages, summarized below:

Advanced Encryption Standard: The DES and DES3 algorithms have been showing signs of age, and many organizations are now using a more secure symmetric encryption algorithm, the Advanced Encryption Standard (AES). The DBMS_OBFUSCATION_TOOLKIT package does not support encryption using this newer standard, but DBMS_CRYPTO does.
Stream ciphering: Encryption can be performed on a block of data at a time via a process known as block ciphering . This method is the most common and the easiest to implement. However, some systems may not have the luxury of getting data in uniform chunksfor example, encrypted content relayed through the public media or other outlets. In such cases, the content must be encrypted, as it comes in, via a process known as stream ciphering. The DBMS_OBFUSCATION_TOOLKIT package does not support stream ciphering, but DBMS_CRYPTO does.
Secure Hash Algorithm: The DBMS_OBFUSCATION_TOOLKIT package supports only the Message Digest (MD5) function for cryptographic hashing, not more modern and secure algorithms such as the Secure Hash Algorithm 1 (SHA-1) provided by DBMS_CRYPTO.
Message Authentication Code: The use of a Message Authentication Code (MAC) allows the creation of a hashed value of the message to be transmitted; that value may then be compared with the value calculated on the message at the other end to ensure the integrity of that message. This process is similar to hashing except that a key must be supplied (as with encryption) to create the hash value. The DBMS_OBFUSCATION_TOOLKIT package does not support the creation of a MAC, but DBMS_CRYPTO does.
Large objects: The DBMS_OBFUSCATION_TOOLKIT package does not support large objects (LOBs) in their native format, but DBMS_CRYPTO does. Encryption of LOBs with the older package requires that they be converted first to RAW format using the built-in UTL_RAW package . This could create some difficulty in writing applications.

In certain circumstances, you may still want to use the DBMS_OBFUSCATION_TOOLKIT package even if you are running Oracle Database 10g. For example, if you are deploying an application in both Oracle9i Database and Oracle Database 10g, you will have to use the older package because both Oracle versions support it. Similarly, if you are encrypting data in Oracle Database 10g that may be decrypted in Oracle9i Database, you will again have to use DBMS_OBFUSCATION_TOOLKIT.

4.3.2. Generating Keys

As 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:

In the DBMS_OBFUSCATION_TOOLKIT, the DES3GETKEY function could generate a key with a datatype of either VARCHAR2 or RAW. In DBMS_CRYPTO, all encryptions involving VARCHAR2 are done through RAW, so a VARCHAR2 key is not useful, and the RANDOMBYTES function returns only a RAW key.
In DBMS_CRYPTO, you do not need to specify the seed as you do in the DBMS_OBFUSCATION_TOOLKIT. The function gets the seed from the parameter SQLNET.CRYPTO_SEED inside the file SQLNET.ORA. This parameter, therefore, must have a valid value of any combination of characters between 10 and 70 bytes long. Here is an example setting of the parameter:
```
     SQLNET.CRYPTO_SEED = weipcfwe0cu0we98c0wedcpoweqdufd2d2df2dk2d2d23fv43098fpi wef02uc2ecw1x982jd23d908d
```

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 package DBMS_CRYPTO may not have been granted to PUBLIC by default, or there may not be a public synonym for it. If you want all developers to be able to use DBMS_CRYPTO, make sure you have a public synonym and proper grants in place. You can execute the following statements as SYS to accomplish this.

     GRANT EXECUTE ON dbms_crypto TO PUBLIC;     CREATE PUBLIC SYNONYM dbms_crypto FOR sys.dbms_crypto;

Note that if the public synonym already exists, the statement will fail, but that will not cause any problems in your database.

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.

If your existing code uses the get_key function, you would have to create a function to ensure backward compatibility anyway.
It takes fewer characters to type "get_key", which can enhance code readability.
Uniformity usually helps in developing quality code, so wrapping may be beneficial for that reason alone.

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 2¹²⁸ 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 Data

After 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:

src: The input value to be encrypted
key: The encryption key
iv: The initialization vector.

The second parameter, typ, however, is new and requires a more detailed explanation.

4.3.3.1. Specifying the encryption type

The 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.

Table 4-3. Types of encryption in DBMS_CRYPTO
Constant	Description	Effective key length
ENCRYPT_DES	Data Encryption Standard (DES)	56
ENCRYPT_3DES_2KEY	Modified Triple Data Encryption Standard (3DES); operates on a block 3 times with 2 keys	112
ENCRYPT_3DES	Triple Data Encryption Standard (3DES); operates on a block 3 times	156
ENCRYPT_AES128	Advanced Encryption Standard	128
ENCRYPT_AES192	Advanced Encryption Standard	192
ENCRYPT_AES256	Advanced Encryption Standard	256
ENCRYPT_RC4	Streaming cipher (the only one)

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 chaining

While 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.

Table 4-4. Types of chaining in DBMS_CRYPTO
Constant	Description
CHAIN_CBC	Cipher Block Chaining format
CHAIN_ECB	Electronic Code Book format
CHAIN_CFB	Cipher Feedback format
CHAIN_OFB	Output Feedback format

4.3.3.3. Specifying padding

Remember 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.

Table 4-5. Types of padding in DBMS_CRYPTO
Constant	Description
PAD_PKCS5	Padding with Public Key Cryptography System #5
PAD_ZERO	Padding with zeros
PAD_NONE	No padding is done; the data must be an exact multiple of the block size to be encrypted (a multiple of eight)

4.3.3.4. Combining options in the typ parameter

Now let's see how to put these various options together. Suppose that you want to select these encryption options:

Padding method: Pad with zeros (PAD_ZERO)
Encryption algorithm: 128-bit key Advanced Encryption Standard (ENCRYPT_AES128)
Chaining method: Block chaining via Cipher Feedback (CHAIN_CFB)

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.

Table 4-6. DBMS_CRYPTO constants with predefined sets for the typ parameter
Constant	Encryption	Padding	Block chaining
DES_CBC_PKCS5	ENCRYPT_DES	PAD_PKCS5	CHAIN_CBC
DES3_CBC_PKCS5	ENCRYPT_3DES	PAD_PKCS5	CHAIN_CBC

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 data

This 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:

From the current database character set to the character set AL32UTF8
From VARCHAR2 to RAW

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.

The UTL_i18n package is provided as part of Oracle's Globalization Support and is used to perform globalization (or internationalization, which is often shortened to "i18n"; the name is made up of the starting letter "i," the ending letter "n," and the 18 letters in between). You can find out more about PL/SQL and internationalization in Chapter 24 of Oracle PL/SQL Programming, 4^th edition.

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 algorithm

Recall 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:

DES

3DES_2KEY

3DES

AES128

AES192

AES256

RC4

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 together

Putting 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 Data

On 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.