DBMS_CRYPTO

The DBMS_CRYPTO package is new to Oracle Database 10g. This package replaces the DBMS_ OBFUSCATION_TOOLKIT which, while still available, has been deprecated. DBMS_CRYPTO adds several new encryption algorithms, hashing algorithms, and a key-hashed capability. It shouldn’t be overlooked that the package name is both easier to type and easier to pronounce.

There are two additional places to find information on the new package. Chapter 22 of the PL/SQL Packages and Type Reference 10 g Release 1 is a good starting resource that explains the organization of the package. As a reference source, however, it lacks code examples. In addition to the code in this chapter, actual code examples can be found in Chapter 16 of Developing Applications Using Data Encryption of the Oracle Database Security Guide 10 g Release 1.

The default privileges on DBMS_CRYPTO are secure. That is, the package has no explicit grants given to the DBA group or to PUBLIC. Before you can begin, you have to grant execute privileges on the package to your security manager. The Oracle Database Security Guide 10 g also strongly recommends that you revoke the PUBLIC execute privileges from the DBMS_ OBFUSCATION_TOOLKIT. If an attacker can’t execute the program to decrypt the data, then recovering the original text becomes even more difficult.

The DBMS_CRYPTO package is very robust. The application developer can choose from a potpourri of encryption algorithms, padding techniques, initialization vectors, and block cipher modifiers. Padding techniques are the methods used to ensure the data sizes are congruent with the algorithm being used. Many of the encryption algorithms require the data to be padded to an 8-byte boundary. An initialization vector (IV) is random data that is prepended to the actual data you want to encrypt. It adds security for data values that are of low cardinality (that is, they don’t have many differences) because the resulting ciphertext for two identical values that use different IVs will be different. This makes breaking the encryption more difficult. Block cipher modifiers allow you to specify how a block cipher algorithm is implemented. There are four block cipher modifiers: Electronic Code Book (ECB) will encrypt each block of data independently of the previous and subsequent blocks; Cipher Block Chaining (CBC) uses an exclusive-OR (XOR) of the previous data block with the current block before it is encrypted; Cipher Feedback (CFB) allows the data to be smaller than the block size (no padding is required); and Output Feedback (OFB) uses data from the previous block to fill the rightmost slots of the current block.

Don’t worry if you don’t understand the differences in algorithms, padding, or modifiers. You can strongly and securely encrypt the data using the recommended settings you see in the examples. For those die-hard cryptographers who understand the differences and have specific preferences, the DBMS_CRYPTO package will allow you to exploit the different padding, initialization vectors, and cipher modifiers.

Encryption Routines

DBMS_CRYPTO provides one encryption function and two encryption procedures. The function is based on the RAW data type. It accepts a key, data, and optional IV parameter of type RAW, and it also returns a RAW data type. The two encryption procedures are LOB oriented. One procedure accepts a BLOB as the data parameter; the other procedure accepts a CLOB as the data parameter. Both procedures expect a key and an optional IV of type RAW, and both procedures write the encrypted data to a BLOB data type.

At first, there seems to be something missing. What happened to support for VARCHAR2? It’s not an oversight. The VARCHAR2 data type is not directly supported. The encryption function can be easily used by translating the VARCHAR2 data types using the UTL_RAW package. The VARCHAR2 encrypted data can be corrupted if updated by two users or databases that are using different character sets—this is discussed in more detail in the “Storing Encrypted Data” section, later in the chapter. To encrypt any data type that is not a RAW, CLOB, or BLOB, you’ll have to perform data type translation.

DBMS_CRYPTO solves many of the challenges that existed with its predecessor. The encryption procedures are designed to handle all large objects. LOB support was missing from the DBMS_OBFUSCATION_TOOLKIT. In addition, DBMS_CRYPTO automatically pads all data when using the block encryption algorithms (or block ciphers). The block ciphers operate on 8-byte boundaries and require that the data fall evenly on the byte boundaries. For the data that does not fall on the boundary, padding has to be used to increase the data size. For example, the character string “123456789” has to be padded with an additional 7 characters to make its total length 16 characters, which is evenly divisible by 8. Prior to DBMS_CRYPTO, the developer had to manually pad the data.

DBMS_CRYPTO Simple Example

This first example illustrates how to invoke the encryption function for the DBMS_CRYPTO package:

sec_mgr@KNOX10g> -- DBMS_CRYPTO simple example  sec_mgr@KNOX10g> DECLARE     2     -- Variable for the encryption algorithm that will be used.    3     -- Using AES with 128-bit key size on a CBC modifier    4     -- and padded using the PKCS#5 padding standard    5     l_algorithm PLS_INTEGER    6       :=    dbms_crypto.encrypt_aes128    7           + dbms_crypto.chain_cbc    8           + dbms_crypto.pad_pkcs5;     9     -- Variable for the data that will be encrypted.   10     l_data    VARCHAR2 (4) := 'DATA';   11     -- Variable for the key that will be used for the encryption.   12     -- Key has to be 16 bytes for AES128 algorithm   13     l_key     VARCHAR2 (16) := 'TheEncryptionKey';   14     -- Initialization vector. Vector has to be   15     -- 16 bytes for AES128 algorithm.   16     l_iv      VARCHAR2 (16) := '0123456789012345';   17  BEGIN   18    DBMS_OUTPUT.put_line   19     ( 'Encrypted DATA: '   20      || UTL_RAW.cast_to_varchar2   21       (dbms_crypto.encrypt   22                 (UTL_RAW.cast_to_raw (l_data),   23                  l_algorithm,   24                  UTL_RAW.cast_to_raw (l_key),   25                  UTL_RAW.cast_to_raw (l_iv))));   26  END;   27 /   Encrypted DATA:  e ^ '  = 8. =  T^¯óRí    PL/SQL procedure successfully completed.   

The output from the encryption function was converted back to a string to illustrate that ‘DATA’ no longer looks like a valid string—it’s encrypted.

The encryption algorithm was set by defining a local variable that is the resulting summation of the algorithm, the block modifier, and the padding. This is discussed in more detail in the “Encryption Algorithms” section, later in the chapter.

The DBMS_CRYPTO package is complete in the sense that you can use it to encrypt practically all of the standard data types after you convert them to a RAW, CLOB, or BLOB.

While working with the procedures and functions, you may find that you have to repetitively perform data type conversions. You’ll notice in the preceding example that the data, the key, and the IV have to be converted to the RAW data type. This may or may not be a problem with your data. I find that it is easier to produce examples when the data, keys, and IVs are strings.

The encryption key size and the IV size can be 8, 16, 24, or 32 bytes and are dependent on the encryption algorithm that will be used. This example artificially creates the exact size key and IV. A key that is too short or too long will cause an error. An IV that is too short will cause an error, and only the first 8, 16, 24, or 32 bytes of the IV will be used based on the algorithm. Depending on how you generate your IV and key, you may have to truncate or pad those values in addition to converting them.

DATA_CRYPTO Package

For many of your practical uses, you may find it easier to create a package that can be used to cast your data types for you. To help illustrate how encryption can be used, the DATA_CRYPTO package presented here will act as a wrapper for the database’s DBMS_CRYPTO. The DATA_ CRYPTO package will make data encryption easier when performing SQL-based updates, and it will also simplify the example code. The complete package specifications and the implementation of the package body are given in Appendix B.

Based on the complexity of the example just shown, encryption and decryption functions for the character, date, and number data types will be provided. The DATA_CRYPTO functions will perform the data type casting. All functions accept the key as a character string. The package will ensure the key is the appropriate size for the given algorithm.

The functions are named ENCRYPT_<data type> and DECRYPT_<data type>; for example, ENCRYPT_CHAR is used for encrypting character data. The returning values for the encryption function and the input data type for the decryption function are RAW data types. You will see later in the “Storing Encrypted Data” section that storing encrypted data as a RAW is important for ensuring that you will be able to decrypt it later. There is an additional ENCRYPT and DECRYPT function that accepts character data and returns encrypted character data. These two complementary functions are used for illustrative purposes.

As mentioned, DBMS_CRYPTO provides encryption and decryption procedures for BLOBs and CLOBs. However, both procedures return BLOBs, and both are procedures as opposed to functions. The DATA_CRYPTO package will provide BLOB functions. There is also a function to return encrypted CLOB data, which is helpful in illustrating examples.

The IV isn’t used in efforts to help simplify the code examples. You’ll be able to understand how the encryption works without their use. You should note that IVs can make encrypting data with repeating values, which use repeating keys, more secure.

Encryption Algorithms

The DBMS_CRYPTO supports DES, triple-DES with two keys, triple-DES with three keys, AES with three different key sizes, and the RC4 algorithms. DBMS_CRYPTO determines the proper algorithm by its numeric value as opposed to a string value. The algorithm parameter is therefore a numeric data type. The base algorithm (for example, DES, 3DES, and AES) has a numeric value that is added to the numeric value for the block modifier (for example, CBC, CFB, ECB, and OFB) and the padding algorithms (for example, PKCS5, Zeros, ORCL, or None). The DATA_CRYPTO package will support the various algorithms but will be defaulted to the Oracle-recommended CBC block modifier and the Oracle-recommended PKCS5 data padding. The DATA_CRYPTO package uses global package variables and stores the computed numerical value for the algorithms:

-- Strongest AES at 256-bit key length (32-bytes)  g_aes256 CONSTANT PLS_INTEGER     :=   dbms_crypto.encrypt_aes256       + dbms_crypto.chain_cbc      + dbms_crypto.pad_pkcs5;   

These algorithms can be easily modified to accommodate a different block chaining algorithm or different padding algorithms by modifying this definition. To make it easy to pass these algorithms to procedures, a DATA_CRYPTO function is provided for each algorithm that returns the algorithms numeric value:

FUNCTION aes256     RETURN PLS_INTEGER  AS  BEGIN     RETURN g_aes256;  END;   

This allows you to easily pass the algorithm as a parameter to the DATA_CRYPTO.ENCRYPT functions from SQL statements:

sec_mgr@KNOX10g> COL Encrypted format a32  sec_mgr@KNOX10g> SELECT    2     data_crypto.encrypt ('Data', 'Key', data_crypto.aes256) encrypted   3     FROM DUAL;    ENCRYPTED  ----------------------- ¦lé+=&n-ç0?Ç+f±ß   

GET and SET functions

The DATA_CRYPTO package allows the user to set and modify the default encryption algorithm by calling getter and setter procedures. Recall the DBMS_CRYPTO package resolves the encryption algorithm by evaluating its numerical value. This user-friendly version translates the number to text, making testing and debugging easier:

-- Set encryption algorithm  -- Stores value in global package variable  PROCEDURE set_algorithm (p_enc_algorithm IN PLS_INTEGER);    -- return character representation of currently set algorithm  FUNCTION get_algorithm      RETURN VARCHAR2;   

Similarly, if you want to set the encryption key to be used in successive encryption and decryption operations, a set key procedure is provided. The key is stored in a package variable:

-- Stores key as a RAW in a global package variable  -- Key is sized based on algorithm passed or the globally  -- defined algorithm  PROCEDURE setkey (   p_key              IN   VARCHAR2,   p_enc_algorithm    IN   PLS_INTEGER DEFAULT NULL  );  

Key Sizing

The DBMS_CRYPTO function and its procedures accept RAW keys. The keys have to be the correct size for the algorithm being used. The key size varies from 8 bytes for DES to 32 bytes for AES256. The RC4 algorithm will take a key of any size. As you will soon see, one popular approach to key management is not storing the keys but rather (securely) computing the encryption keys. Therefore, it’s desirable to have a routine that ensures the key size is appropriate for the algorithm being used.

The examples used in this chapter will use string-based keys for the encryption and decryption parameter. The DBMS_CRYPTO requires the key to be a RAW, so a function is provided to convert the key to a RAW and adjust it to the appropriate size:

-- Return RAW key of appropriate size based on algorithm    -- If algorithm is null, the global algorithm will be used.  FUNCTION getkey (    p_key             IN   VARCHAR2 DEFAULT NULL,    p_enc_algorithm   IN   PLS_INTEGER DEFAULT NULL   )   RETURN RAW;   

This function will also provide utility if you want to directly invoke the DBMS_CRYPTO and ensure the key is sized appropriately as shown here:

sec_mgr@KNOX10g> SELECT dbms_crypto.encrypt     2                        (UTL_RAW.cast_to_raw ('data'),   3                         data_crypto.aes,   4                         data_crypto.getkey ( 'my encryption key'   5                                               || ' of some random length',   6                                               data_crypto.aes    7                                             )    8                        ) encrypted    9      FROM DUAL;    ENCRYPTED    ------------------------------   CF7723ECBFCFF059B2A62BC2DB9BA56F    

The DATA_CRYPTO.GETKEY function will use the left-most bytes of the key passed. Keys that are too large for the algorithm will be truncated. Keys that are too small will be right padded with binary zeros.

Note 

Two identical values encrypted with the same key result in the same ciphertext; therefore, careful attention to the encryption keys and their lengths is important to ensuring the data encryption is effectively implemented.

Keys that are too small or too large can result in a less secure implementation. (This is probably why Oracle does not automatically size the keys for you.) You’ll see an example of this in the “Weak Keys” section.

Weak Keys

The DBMS_CRYPTO package does not truncate or pad encryption keys in any way. This leaves the responsibility for creating the appropriately sized keys to the developer. There are a few important things to consider.

The DATA_CRYPTO.GETKEY is a convenience function. It pads or truncates keys for the relevant encryption algorithm. It uses the left-most bits for the truncation. It is therefore possible to provide two different keys that get truncated to the same encryption key. That is, if the key is too long for the algorithm, then its truncation may remove the uniqueness:

sec_mgr@KNOX10g> COL encrypted1 format a16  sec_mgr@KNOX10g> COL encrypted2 format a16  sec_mgr@KNOX10g> EXEC data_crypto.set_Algorithm(data_crypto.DES);    PL/SQL procedure successfully completed.    sec_mgr@KNOX10g> SELECT data_crypto.encrypt_char ('Data',    2                                                'ThisKeyIsOver8bytes')   3                                                            encrypted1,   4       data_crypto.encrypt_char ('Data',   5                                 'ThisKeyIsAlsoOver8bytes')    6                                                            encrypted2   7     FROM DUAL;    ENCRYPTED1            ENCRYPTED2  -------------------   --------------------- 0DCA01D7A8E26AAF      0DCA01D7A8E26AAF   

The DES algorithm requires an 8-byte key. The DATA_CRYPTO.GETKEY function truncates the key to the first 8 bytes. The two preceding keys result in the same key, which subsequently produces the same ciphertext.

The DATA_CRYPTO.GETKEY will also pad the encryption key to make it the appropriate length. The overall strength of the algorithm is influenced by the uniqueness and variations in all elements of the key. If you elect to use AES256, the key size is 32 bytes. If you use a key with one character (a key size of 1 byte), then the remaining key bytes (characters) will be the same—you will have 1 byte that you provided and 31 bytes of binary zeros. This could make it much easier for an attacker to guess your encryption key.

The DATA_CRYPTO.GETKEY is provided for convenience and as a reference. Understanding the implications of key size and thus overall strength is important to ensuring the encryption process will be effective.