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, always consider the character type, data type, column properties, and the length of the plaintext column.

Supported Character Types

CDP for DB2 supports:

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

Column Encryption Guidelines

Before applying encryption properties on columns, note the following:

1. The ability to encrypt a column depends on the relationship between the column and its table. Below is a list of roles that columns can play and their effect on encryption.

   • Identity column – Cannot be encrypted.

   • Partitioning key – The partitioning key itself cannot be encrypted. If the table contains a nullable partitioning key, no columns can be encrypted.

   • Primary key – Primary keys are dropped during migration. Manually recreate primary keys to preserve the conditions established by the primary keys. If the primary key is not referenced in a foreign key constraint, verify that the key is not referenced implicitly as a foreign key before encrypting.

   • Foreign key – To encrypt a foreign key, drop the constraints prior to data migration. After migration, reestablish the constraints.

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

   • Join constraints – Make sure that the columns to be encrypted are not part of a join constraint. If encrypting a column that is part of a join constraint, both columns should be encrypted.

   • Unique constraints – When encrypting a column with a unique constraint, that constraint is dropped during the data migration process. If the unique constraint is to be retained after encryption, manually recreate the unique constraint. Field-level IV can’t be used on a column with unique constraints. Instead, one IV should be chosen for the entire column.

   • Check constraint - To encrypt a column with a check constraint, drop the check constraint.

Additional rules apply to the following topics:

• 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 will then be trying to decrypt plaintext data, yielding unexpected results.

• NULL values – NULL values are not encrypted by SafeNet ProtectDB. 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 SafeNet ProtectDB does not interact with the key manager for that query.

Columns referenced in triggers on the table - These columns can be encrypted; however, the DBA should review all triggers that reference the column to ensure that the trigger functions as expected. No warning is given by the system when a column with a trigger on it is migrated.

Encrypted columns – Columns that are currently encrypted cannot be encrypted.

Length of the Plaintext Column

CDP for DB2 Server uses 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-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-EDE 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)

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), the new column size increases to the next multiple of the block size. Remember that DES-EDE uses block size 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-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-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.