Planning Encryption

Encrypting columns with plaintext data to columns with encrypted data is an important task that requires planning and collaboration between the database administrator and the security engineer. The database encryption process should be transparent to all the applications that interact with the databases. You should not have to make changes to applications that access your sensitive information. If you are integrating at the application level, then you might have to make some changes to the application. Refer to the following

When planning for database encryption, you must consider:

• Type of data to encrypt

• How to encrypt

• How to manage the encrypted data

Selecting the Data to Encrypt

When selecting the data to be encrypted, you must consider the character type, data type, column properties, the length of the plaintext column, and actual length of the row.

Supported Character Types

CDP for DB2 supports the encryption of:

• Single-byte

• Multi-byte

• Unicode data

Supported Data Types

The following table lists the supported data types for DB2 that can be migrated. Data types that do not appear in this list cannot be encrypted.

• XML data type can’t be encrypted.

• If an XML data type is present in a table being migrated, the following error appears:
  Columns of data type XML are not supported in a view that is the target of an INSTEAD OF trigger.

• For DECIMAL data type, the maximum scale/precision combination supported is 31/30.

Column Encryption Guidelines

Before applying encryption properties on columns, note the following:

1. During encryption, some columns in a table cannot be encrypted. Below is a list of roles that columns can play and their effect on encryption.

   • Identity column – Cannot be encrypted.

   • Primary key – Primary keys are dropped during encryption. You must manually recreate primary keys if you want to preserve the conditions established by the primary keys. If the primary key is not referenced in a foreign key constraint, you should verify that the key is not referenced implicitly as a foreign key before encrypting. During unencryption, the primary keys, which were dropped at the time of encryption, are not created back. You must manually recreate primary keys, if needed.

   • Primary keys referenced in another table – To encrypt the primary keys referenced in another table, you must first drop the reference. You must manually recreate the primary keys and the reference if you want to preserve the conditions established by the primary keys referred in another table.

   • Foreign key – To encrypt a foreign key, you must manually drop the constraints prior to data encryption. After encryption, you can re-establish them.

   • Indexed columns – Index columns can be encrypted, however, the sort order of the encrypted data will not be consistent with the sort order of the plaintext data.

2. The constraints placed on your columns must be evaluated, as these may affect the data encryption process. The following list provides types of constraints and describes their effect on encryption.

   • Joins – Make sure that the columns you are encrypting are not part of a join. If you are encrypting a column that is part of a join, you should encrypt both columns.

   • Unique constraints – When encrypting a column with a unique constraint, that constraint is dropped during the data encryption process. If you want to retain the unique constraint after encryption, you should manually recreate the unique constraint. You cannot use field-level IVs on a column with unique constraints. Instead, you should choose one IV for the entire column.

   • Check constraint – To encrypt a column with a check constraint, you must drop the check constraint.

   • Default values – Columns with a default value assigned to them cannot be encrypted. This is because the default constraint adds plaintext data to the column. Applications accessing that data then try to decrypt plaintext data, yielding unexpected results.

   • NULL values – NULL values are not encrypted by CDP. If a migrated column contains NULL values, those values remain unencrypted in the resulting encrypted column. When a database query yields a NULL value, no cryptographic process is required, so CDP does not interact with the CipherTrust Manager for that query.

   • Columns referenced in non-CDP triggers on the table – These columns cannot be encrypted. If you attempt to encrypt such columns, the system returns: "Before executing this operation, you must delete non-CDP trigger (<trigger_name>) defined for this table."

   Note

   Columns that are currently encrypted cannot be encrypted.

Length of the Plaintext Column

CDP for DB2 Server uses the VARCHAR FOR BIT DATA data type to store ciphertext for standard encryption. These VARCHAR FOR BIT DATA columns cannot exceed 4000 bytes. If the padding process would create a ciphertext larger than 4000 bytes, encryption is not allowed.

Using AES, CDP does not allow the encryption of plaintext values larger than 3999 bytes.

The following table illustrates how padding affects column length when encryption is performed with AES keys.

Using DES/DES-EDE, CDP does not allow the encryption of plaintext values larger than 3999 bytes.

The following table illustrates how padding affects column length when encryption is performed with DES/DESEDE keys.

How to Encrypt Data

When deciding how to encrypt your data, you must consider:

• which type of encryption to perform

• which algorithm to use

• what mode to use

• how to apply initialization vectors

• whether to use padding

• select replacement values

• determine how much space the resulting data requires.

Choosing Encryption Type

CDP for DB2 supports two types of encryption:

• Standard Encryption

• Format Preserving Encryption (FPE)

Space Requirements Considerations

Consider the following things when allotting space for an encrypted solution:

• Algorithm to use to encrypt data

• Whether to use field–level or column–level IVs

• Whether the table needs an identity column

Managing the Encrypted Data

Prior to migrating the database, it is important to consider how the encrypted data will be managed. The sections below discuss ciphertext data types, identity columns, space requirements, and changes to the original column.

Ciphertext Data Types

CDP for DB2 Server stores ciphertext as “VARCHAR FOR BIT DATA”.

Identity Columns

If a usable index already exists, CDP uses it. Otherwise, an identity column (called ING_ROW_ID) is added to the base table.

Size of Encrypted Data

Encrypted data is predictably larger than plaintext data. So it is important to make sure that enough tablespace is available for the operation being performed (data migration, unencryption, or key rotation). During the data migration process, a new column is created. The original column might have a length of 50 bytes; the new column, however, has a different length. The length of the new column depends on which algorithm is used to encrypt the data.

For columns encrypted with symmetric keys (AES, DES-EDE, ARIA, and DES), the new column size increases to the next multiple of the block size. Remember that DES and DES-EDE use block sizes of 8 bytes, and AES/SEED/ARIA uses a block size of 16 bytes. So, if the original column was 50 bytes, then the new column will be 56 bytes for DES and DES-EDE; for AES/SEED/ARIA, the new column will be 64 bytes.

If the length of the original column is a multiple of the block size, then the new column will be the original column size plus the block size. For example, if a column contained a 56-byte string, and that string was encrypted with a DES-EDE key, then the ciphertext would be 64 bytes long.

In addition, if a random IV for each field is used, then the table will have another column of either 8 bytes (DES and DES-EDE) or 16 bytes (AES).

Consider a scenario where a 50-byte column is to be encrypted with a DES-EDE key. There are 10,000 rows in the column, and a unique IV for each value is to be used.

The original column is set to null and a new column is created. The new column will be 56 bytes in length. Before encryption, the column required 500,000 bytes. After encryption, the column requires 560,000 bytes. Additionally, a new column is created to hold the IVs. The IVs are 8 bytes each, so an additional 80,000 bytes of space is required for the IVs.

To execute encryption on column, refer to the Encrypt a column article.