Encryption In-Depth

This section discusses many of the issues you need to understand for practicing effective data encryption.

Keys, Data, and IVs

It should be obvious that using a different encryption algorithm will yield different ciphertext. There is also a strong relationship between the encryption key, the data, and the IVs used within the same algorithm. As you go through the next examples, you will see this relationship. This is important because the relationship dictates how to effectively use the encryption keys based on the data and algorithms.

The following examples show the encrypted data as RAWs because it is easier to distinguish differences in the hexadecimal RAW output than the mangled character representation illustrated previously.

The first important principle is that changing the keys on the same data results in different ciphertext. If you encrypt the data with a key that changes for each record, then two records with the same values will result in different ciphertext:

sec_mgr@KNOX10g> COL data format a6    sec_mgr@KNOX10g> COL "Encrypted with Key1" format a34    sec_mgr@KNOX10g> COL "Encrypted with Key2" format a34    sec_mgr@KNOX10g> SELECT 'Data' DATA,    2          data_crypto.encrypt_char ('Data', 'Key1')    3                      "Encrypted with Key1",   4          data_crypto.encrypt_char ('Data', 'Key2')    5                      "Encrypted with Key2"   6   FROM DUAL;    DATA       Encrypted with Key1              Encrypted with Key2    ------   --------------------------------  -----------------------   Data      6F0FA69188F00D8DEE30F38783FA2BDF  11C843B0AF268E10F5C8B65071D2AEF4    

Notice the encrypted output (ciphertext) changed when the key changed. The ciphertext will also change if the data changes but the key stays the same:

sec_mgr@KNOX10g> -- changing data    sec_mgr@KNOX10g> COL "'Data1' key: 'TheSecretKey'" format a36    sec_mgr@KNOX10g> COL "'Data2' key: 'TheSecretKey'" format a36    sec_mgr@KNOX10g> SELECT data_crypto.encrypt_char ('Data1',    2                                 'TheSecretKey')   3                   "'Data1' key: 'TheSecretKey'",   4           data_crypto.encrypt_char ('Data2',   5                                  'TheSecretKey')    6                   "'Data2' key: 'TheSecretKey'"   7   FROM DUAL;    'Data1' key:  'TheSecretKey'          'Data2' key: 'TheSecretKey'  ------------------------------------ ----------------------------------   0F938AA8EE43E237403CB99D1DC546E6      4FD06CE09FFE899697B0DF47A8E1BBF7    

If you want to use the same key to encrypt the same data values, an initialization vector has to be added to the encryption call to ensure duplicate ciphertext is not produced:

sec_mgr@KNOX10g> -- Changing IVs allows different ciphertext for    sec_mgr@KNOX10g> -- the same data and the same key.  sec_mgr@KNOX10g> DECLARE       2    -- The encryption algorithm that will be used.    3    l_algorithm PLS_INTEGER      4      :=   dbms_crypto.encrypt_aes      5         + dbms_crypto.chain_cbc      6         + dbms_crypto.pad_pkcs5;     7     -- First initialization vector. Vector has to be      8    -- 16 bytes.     9    l_iv1         RAW (16)   10        := UTL_RAW.cast_to_raw (' 0123456789012345');   11    -- Second initialization vector. Vector has to be     12    -- 16 bytes.   13    l_iv2 RAW (16)   14      := UTL_RAW.cast_to_raw (' 1123456789012345');   15    -- The data that will be encrypted     16    l_data   VARCHAR2 (4)  := 'DATA';   17    -- The key that will be used for the encryption.   18    -- Key has to be 16 bytes    19    l_key    VARCHAR2 (16)  := 'TheEncryptionKey';   20  BEGIN     21    DBMS_OUTPUT.put_line    22      (   'Encrypted with IV1: '   23       || dbms_crypto.encrypt     24                      (UTL_RAW.cast_to_raw (l_data),   25                       l_algorithm,   26                       UTL_RAW.cast_to_raw (l_key),   27                       l_iv1));   28    DBMS_OUTPUT.put_line    29      (   'Encrypted with IV2: '   30       || dbms_crypto.encrypt     31                      (UTL_RAW.cast_to_raw (l_data),   32                       l_algorithm,   33                       UTL_RAW.cast_to_raw (l_key),   34                       l_iv2));   35   END;   36 /     Encrypted with IV1: C8EE5EDFB8A252F0A171E93C642754C6    Encrypted with IV2: 764E9898B28CA68D621096989D772694    PL/SQL procedure successfully completed.   

The bolded values in the IVs show that a 1-byte difference in the IV will completely change the resulting ciphertext. The most important point in this section is that you generally know the uniqueness of the data you wish to encrypt. If all the encryption inputs are the same—key, data, algorithm, and IV—then the ciphertext will also be the same. This may be undesirable. Consider the following example:

sec_mgr@KNOX10g> CREATE TABLE people       2     (ename VARCHAR2(12),    3      deptno VARCHAR2(16));    Table created.    sec_mgr@KNOX10g> INSERT INTO people       2    SELECT ename, deptno      3      FROM scott.emp;    14 rows created.    sec_mgr@KNOX10g> break on deptno    sec_mgr@KNOX10g> SELECT ename, deptno       2              FROM people       3              order by deptno, ename;    ENAME    DEPTNO    -------  --------   CLARK    10    KING    MILLER    ADAMS    20   FORD    JONES    SCOTT    SMITH    ALLEN    30    BLAKE    JAMES    MARTIN    TURNER    WARD    

If you wish to encrypt the DEPTNO column in this table, you might issue the following:

sec_mgr@KNOX10g> EXEC data_crypto.setkey('Deptno Key');    PL/SQL procedure successfully completed.    sec_mgr@KNOX10g> UPDATE people      2     SET deptno = data_crypto.encrypt (deptno);    14 rows updated.   

First set the encryption key to “Deptno Key.” This will maintain the same key for all encrypted updates. While the data was encrypted, the relationship and association of person to DEPTNO is still maintained. This is due to the fact that the same key was used for all records.

sec_mgr@KNOX10g> COL "Encrypted Deptno" format a32    sec_mgr@KNOX10g> BREAK on "Encrypted Deptno"  sec_mgr@KNOX10g> SELECT ename, UTL_RAW.cast_to_raw (deptno) "Encrypted Deptno"     2              FROM people      3              ORDER BY deptno, ename;    ENAME          Encrypted Deptno    ------------ --------------------   CLARK          2C2209E50EBA89F28E78E3DADF247E3D    KING    MILLER    ALLEN          2F044687D0D897871586A7ED400DD599    BLAKE    JAMES    MARTIN    TURNER    WARD   ADAMS          C580432E9DAC481E575A0ACB396A8B3A    FORD    JONES    SCOTT    SMITH       14 rows selected.   

If you wish to obscure the relationship, you have to use a different key for each row. In the following example, the records are encrypted using a unique key for each record:

sec_mgr@KNOX10g> TRUNCATE TABLE people;    Table truncated.    sec_mgr@KNOX10g> INSERT INTO people       2    SELECT ename, deptno       3       FROM scott.emp;    14 rows created.    sec_mgr@KNOX10g> UPDATE people       2     SET deptno = data_crypto.encrypt (deptno, ename);    14 rows updated.   

Since the ENAME is different for each row, two rows with the same DEPTNO will result in different encrypted values:

sec_mgr@KNOX10g> SELECT ename,     2           UTL_RAW.cast_to_raw (deptno) "Encrypted Deptno"    3         FROM people      4  ORDER BY deptno, ename;    ENAME       Encrypted Deptno    ---------- -----------------------------   JAMES       023C3C40FDECF587CFE9D51300EA66B8    TURNER      17A3A806CB7C9314CA83957FF0DAD5B7    KING        458DABDAFAB4235C402200435100E614    CLARK       67583C666C4C06DB78088998942D21EB    WARD        7CF35398CD84BFE22D12F9560653C920    FORD        82CA438DB5491AE23B9D46CD08149958    MILLER      9FADCF5BDEF03C926281FB02EEFEDC61    MARTIN      A14FF7184C9C2304596E40A7BE91C00A    SMITH       AC4A6BB34AC7EE7136108D6661CD5452    ADAMS       C01ED18F64343111A666017B53AD3C3D    JONES       C5BFFFFE0CC7CC936F958F9FB931BAB3    ALLEN       CB04A624191CB5EBF5F028C05A46E540    BLAKE       D6948B6A87B00F816C14E5DD071B4431    SCOTT       FD42777AAD3A82292304C97B94933DD8    14 rows selected.   

Storing Encrypted Data

There are a few important issues concerning the storage of encrypted data as character data (either VARCHAR2 or CLOB).

The practical use of storing the character data is in question. The encryption process completely mangles the data. Any letters or numbers no longer look like letters or numbers. Much of the data has become nonprintable. Some applications, publishing software, or terminals expecting printable characters may not enjoy the encrypted version.

When using the block encryption algorithms (all algorithms except RC4), storing the encrypted data as a VARCHAR2 or CLOB can create problems with translations between different character sets. VARCHAR2 and CLOB data is interpreted and translated between character sets when moving the data between databases. When doing this with encrypted data, the conversion process will rearrange the data. If this is done, the decryption process will not return the original data.

Caution 

Encrypted data stored as character data will not be successfully decrypted unless the encryption and decryption sessions have the same NLS character set.

The solution to this is to store the data either as a RAW or a BLOB. RAW and BLOB data types will not undergo character set translation. To support this, the DATA_CRYPTO package returns RAW data for encryption of character, number, and date data.

To illustrate the importance of this, consider the following example. A table is created that holds names and salaries. The salary will be encrypted and stored both as a VARCHAR2 and as a RAW.

sec_mgr@DAGGER> CONN sec_mgr/oracle10g@dagger  Connected.  sec_mgr@DAGGER> DROP TABLE people;    Table dropped.    sec_mgr@DAGGER> CREATE TABLE people     2    (NAME VARCHAR2(30),    3    salary VARCHAR2(16),    4    raw_salary RAW (32));    Table created.    sec_mgr@DAGGER> -- populate table  sec_mgr@DAGGER> INSERT INTO people     2              (NAME, salary)    3     SELECT ename, sal    4       FROM scott.emp    5      WHERE ROWNUM <= 3;    3 rows created.    sec_mgr@DAGGER> -- Encrypt salaries, use name as key  sec_mgr@DAGGER> -- Encrypt salary as RAW  sec_mgr@DAGGER> UPDATE people     2     SET raw_salary =     3            data_crypto.encrypt_char (salary, NAME);    3 rows updated.    sec_mgr@DAGGER> -- encrypt salary as VARCHAR2  sec_mgr@DAGGER> UPDATE people     2     SET salary = data_crypto.encrypt (salary, NAME);    3 rows updated.    sec_mgr@DAGGER> COMMIT ;    Commit complete.    sec_mgr@DAGGER> COL decrypted_salary format a16  sec_mgr@DAGGER> COL decrypted_raw_salary format a13  sec_mgr@DAGGER> -- Can decrypt both RAW and varchar2  sec_mgr@DAGGER> SELECT NAME,     2         data_crypto.decrypt_char (raw_salary, NAME)    3                              decrypted_raw_salary,    4         data_crypto.decrypt (salary, NAME)     5                              decrypted_salary    6   FROM people;    NAME          DECRYPTED_RAW       DECRYPTED_SALARY  ----------   ----------------     -------------     SMITH         800                 800  ALLEN         1600                1600  WARD          1250                1250   

Everything appears to be working until…a client connects to the database and their character set is different than that of the user who initially saved the VARCHAR2 data. When this occurs, the VARCHAR2 data will not decrypt. The other client in this example will be another database server accessing the data over a database link:

sec_mgr@KNOX10g> CREATE DATABASE LINK dagger USING 'dagger';    Database link created.    sec_mgr@KNOX10g> -- Can still decrypt RAW  sec_mgr@KNOX10g> COL decrypted_raw_salary format a13  sec_mgr@KNOX10g> SELECT NAME,     2         data_crypto.decrypt_char (raw_salary, NAME)    3                           decrypted_raw_salary    4   FROM people@dagger;    NAME       DECRYPTED_RAW  --------   ---------------------- SMITH      800  ALLEN      1600   WARD      1250    sec_mgr@KNOX10g> -- Cannot decrypt VARCHAR2  sec_mgr@KNOX10g> SELECT NAME,     2      data_crypto.decrypt (salary, NAME)    3                                 decrypted_salary    4   FROM people@dagger;        data_crypto.decrypt (salary, NAME)         *   ERROR at line 2:  ORA-28817: PL/SQL function returned an error.  ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 67  ORA-06512: at "SYS.DBMS_CRYPTO", line 41  ORA-06512: at "SEC_MGR.DATA_CRYPTO", line 318  ORA-06512: at "SEC_MGR.DATA_CRYPTO", line 354  ORA-06512: at line 1   

You can compare the character sets and see they are different:

sec_mgr@KNOX10g> -- Show character sets are different  sec_mgr@KNOX10g> col "Remote Character Set" format a30  sec_mgr@KNOX10g> col "Local Character Set" format a30  sec_mgr@KNOX10g> SELECT a.VALUE "Remote Character Set",    2            b.VALUE "Local Character Set"   3    FROM v$nls_parameters@dagger a,   4         v$nls_parameters b   5  WHERE a.parameter = 'NLS_CHARACTERSET'   6    AND b.parameter = 'NLS_CHARACTERSET';    Remote Character Set    Local Character Set  ----------------------  --------------------- WE8ISO8859P1             WE8MSWIN1252   

You would see the same results if you used CLOBs and BLOBs instead of VARCHAR2 and RAW. The DBMS_CRYPTO package returns RAWs and BLOBs for this reason and the DATA_ CRYPTO provides these functions to support these uses as well.

Note that the translation problem will occur for any insert or updates to encrypted data when the encrypting and decrypting character sets are not identical. Importing data that was exported in a different character set will also cause problems. The safest and best method for handling encrypted data is to store it as a RAW or BLOB. The only exception is when you can guarantee that the character sets will never differ.

Encrypted Data Sizes

Encrypting data with block ciphers does change the data size. Recall the DBMS_CRYPTO will automatically pad data for block ciphers. For the DES-based algorithms, the padding will be to the next highest 8-byte boundary. For the AES-based algorithms, the padding will be to the next highest 16-byte boundary.

The following example illustrates this fact. A table is created and populated. A helper procedure is also created to assist in populating the table. The table is populated by taking data records of six different sizes—1, 7, 8, 15, 16, and 32 bytes, respectively—and encrypting them with each of the algorithms. The data is specifically sized around the block boundaries. This will illustrate how a data element that is originally 8 bytes in length may have to be stored in a 16-byte column.

sec_mgr@KNOX10g> CREATE TABLE enc_data_sizes     2     (algorithm VARCHAR2(6),    3     dataval VARCHAR2(256),    4     enc_dataval VARCHAR2(256),    5     raw_enc_dataval RAW(256));    Table created.    sec_mgr@KNOX10g> -- Create a procedure which will be called  sec_mgr@KNOX10g> -- iteratively to perform the insert operation  sec_mgr@KNOX10g> CREATE OR REPLACE PROCEDURE insert_enc_record (     2    p_dataval IN VARCHAR2)    3  AS    4  BEGIN    5    INSERT INTO enc_data_sizes    6               (algorithm,    7                dataval,    8                enc_dataval,    9                raw_enc_dataval)   10        VALUES (data_crypto.get_algorithm,   11                p_dataval,   12                data_crypto.encrypt (p_dataval),   13                data_crypto.encrypt_char (p_dataval));   14  END;   15 /   Procedure created.  sec_mgr@KNOX10g> -- Populate table  sec_mgr@KNOX10g> DECLARE     2    l_dataval VARCHAR2 (32);    3   BEGIN    4    data_crypto.setkey ('thisIsTheEncryptKey');    5     6    FOR i IN 1 .. 32    7    LOOP    8     -- checking padding around block boundaries    9     IF (i IN (1, 7, 8, 15, 16, 32))   10     THEN   11      l_dataval := RPAD ('x', i, i);   12      data_crypto.set_algorithm (data_crypto.des);   13      insert_enc_record (l_dataval);   14      data_crypto.set_algorithm (data_crypto.des3);   15      insert_enc_record (l_dataval);   16      data_crypto.set_algorithm (data_crypto.aes);   17      insert_enc_record (l_dataval);   18      data_crypto.set_algorithm    19                                (data_crypto.aes192);   20     insert_enc_record (l_dataval);   21     data_crypto.set_algorithm   22                                (data_crypto.aes256);   23     insert_enc_record (l_dataval);   24     data_crypto.set_algorithm (data_crypto.rc4);   25     insert_enc_record (l_dataval);   26     END IF;   27   END LOOP;   28  END;   29 /    PL/SQL procedure successfully completed.  sec_mgr@KNOX10g> commit;   

You can now view the effect of the encryption on the original data sizes. The results are consistent regardless of the encrypting data’s type (VARCHAR2, RAW, CLOB or BLOB):

sec_mgr@KNOX10g> BREAK on algorithm  sec_mgr@KNOX10g> COL algorithm format a9  sec_mgr@KNOX10g> SELECT algorithm,     2            LENGTH (dataval) "Original Size",    3            LENGTH (enc_dataval) "Encrypted Size",    4            LENGTH (enc_dataval) - LENGTH (dataval) "Padded Bytes"    5       FROM enc_data_sizes    6    ORDER BY algorithm, "Original Size";    ALGORITHM  Original Size  Encrypted Size  Padded Bytes  --------- --------------  --------------  ------------- AES         1              16              15               7              16               9              8              16               8             15              16               1             16              32              16             32              48              16  AES192      1              16              15             7              16               9              8              16               8             15              16               1             16              32              16             32              48              16  AES256      1              16              15               7              16               9              8              16               8             15              16               1             16              32              16             32              48              16  DES         1               8               7               7               8               1              8              16               8              15              16               1             16              24               8             32              40               8  DES3        1               8               7               7               8               1              8              16               8             15              16               1             16              24               8             32              40               8  RC4         1               1               0               7               7               0              8               8               0             15              15               0             16              16               0             32              32               0   

The output shows for the AES algorithm, data up to 15 bytes will be padded to 16 bytes. The 1-byte string was padded with 15 bytes, and the 15-byte string was padded with 1 byte. This is consistent with each 16-byte block size—a 32-byte string is padded to 48 bytes.

For DES, the same is true except the block size is 8 and not 16. All data smaller than 8 bytes will be padded up to 8 bytes. Data of 8–15 bytes will be padded to 16 bytes, and so on.

The striking exception is the RC4 algorithm. RC4 is not a block cipher, but a stream cipher. This means the data is encrypted for each byte, and the bytes don’t have to be collected into blocks of 8 or 16 for the encryption process.