Planning Migration

Planning for Database Migration

Migrating from 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. For a complete database integration, this process should be transparent to all applications that interact with the database; there is no need to make changes to applications that access the sensitive information. If integrating at the application level, then application might require some changes.

Whichever method of integration is chosen for implementation, always consider what data to encrypt, how to encrypt it, and 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 Oracle supports the encryption of single-byte, multi-byte, and Unicode data.

Supported Data Types

Column Encryption Guidelines

The ability to encrypt a column depends on the relationship between the column and its table. 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.

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

   Note

   During decryption, the primary keys, which were dropped at the time of migration, are not created back. Manually recreate primary keys, if needed.

   • Primary keys referenced in another table: To encrypt the primary keys referenced in another table, the reference must be dropped first. Manually recreate the primary keys and the reference to preserve the conditions established by the primary keys referred in another table.

   • Foreign key: To encrypt a foreign key, manually drop the constraints prior to data migration. After migration, re-establish the constraints.

   • Indexed columns: Indexed 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 to be encrypted are not part of a join. If encrypting a column that is part of a join, 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 IVs 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.

   • 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 the column to be migrated contains NULL values, those values remain decrypted in the resulting encrypted column. When a database query yields a NULL value, no cryptographic operation is required, so CDP does not interact with the CipherTrust Manager for that query.

     Note

     • When migration is performed on a column, which contains NULL values, the number of migrated records is equal to the number of total records in the column.

     • When decryption or key rotation is performed on a migrated column, which contains NULL values, the number of decrypted or key rotated records is less than the number of total records in the migrated column. The migrated records containing NULL values are not included the decrypted or key rotated record count.

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

   • Encrypted columns: Columns that are currently encrypted cannot be encrypted.

Length of the Plaintext Column

The following table describes the conversion of Character family plaintext data types migrated using non-versioned keys:

The following table describes the conversion of data types migrated using versioned keys based on specified algorithm(s):

When versioned keys are used for migration, all ranges of data not mentioned in the above table are converted into RAW irrespective of the algorithm used.

How to Encrypt the Data

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

• which algorithm to use

• what mode to use

• how to apply initialization vectors

• whether to use padding

• replacement values

• how much space the resulting data requires

Choosing an Encryption Algorithm

CDP for Oracle supports two types of encryption:

Applying Initialization Vectors

When using an algorithm in CBC mode, an Initialization Vector (IV) can be applied at the field-level or at the column-level. When applying IVs at the field-level, a unique IV is used for the encryption of each field. In this case, a new column is added to the table. When IV is applied at the column-level, there is only one IV per column, and that IV is stored in a separate metadata table.

It may be preferred to apply IVs at the field-level when encrypting values that might be identical. For example, when encrypting names, if two people in the table have the same name, those names encrypt to the same value if the same IV is applied for both encrypt operations. If, however, a different IV is applied for the two encrypt operations, then the encrypt operations yield different results. Field-level encryption offers an even higher level of security because the IVs are different for every value being encrypted.

However, be aware that applying IVs at the field-level might cause a performance disadvantage when doing an exact search. When IVs are applied at the field-level, the search value can’t be encrypted, which means that all rows being searched through must be decrypted. When encrypting values that are unique, like credit card numbers or social security numbers, it is recommended to apply IVs at the column-level.

Options for the IV field of the Column Properties section are as follows:

• user-specified IV for column: Supply a single IV for an entire column.

• random IV for column: The Key Manager provides one randomly generated IV that is used to encrypt all values in a column.

• random IV for each field: The Key Manager provides a randomly generated IV for each value in a column. With this option, the Key Manager adds a column for the IV to the base table.

Padding

The CipherTrust Manager appliance automatically selects a padding scheme before data is migrated. One of two methods are used:

• PKCS5 Padding: This scheme might be used when encrypting with symmetric keys (AES, DES, and ARIA).

• No Padding: This scheme is used when the column’s data type is CHAR or CHARACTER and the column’s original width is an exact multiple of the encryption block size.

Replacement Values

If a database user attempts to access encrypted data to which they do not have decryption permission, the system returns an error message. The content of those permission-related errors can be specified using the replacement values feature.

The replacement value is used only if the user has no decryption permissions. If an error results from the user’s authorization policy (i.e., the decryption request occurs outside the usage period, or exceeds the maximum operations per hour,) the actual error is returned. This is done for two reasons. First, returning a policy violation error acts as an alert of a possible attack. Second, returning this error ensures that the same query always yields the same results. If a query could return either the replacement value or legitimate plaintext, without any way of indicating which was returned, data in the client application could be corrupted.

Use the Decryption Behavior for Users with Insufficient Permissions field on the Column Properties section to specify that the system returns a specific replacement value, a NULL value, or the original standard error.

By using the replacement value together with the default user mapping, it can be ensured that all unauthorized database users receive the same message when attempting to access the encrypted data.

• A user with select, update, and delete privileges can delete data from a migrated table based on the error replacement values.

• Replacement values are not returned if a query yields a NULL value. When a query results in a NULL value, no cryptographic process is required, so the CDP does not interact with the NAE Server and the replacement values feature is not activated.

• For large object data types, CDP does not support the user-defined error replacement value. Standard error and Null value replacement are supported.

• If the Error Replacement feature is configured, then the Error Replacement Value will be returned only for the unauthorized users.

• The Error Replacement feature will not work if the Key Manager is unreachable.

Space Requirements Considerations

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

• The algorithm used to encrypt data

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

• Whether the table needs an identity column

Managing 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

The data type used to store the ciphertext varies by data type. CDP for for Oracle stores ciphertext as RAW. However, for large objects and VARCHAR2 of size 2000-4000 and NVARCHAR2 of size 1000-2000 data types, BLOB is used in place of RAW for encrypted values.

Non-versioned Keys

The following table describes the conversion of data types migrated using non-versioned keys:

Versioned Keys

The following table describes the conversion of data types migrated using versioned keys based on specified algorithm(s):

When versioned keys are used for migration, all ranges of data not mentioned in the above table are converted into RAW irrespective of the algorithm used.

Identity Columns

In Oracle databases, the ING_ROW_ID column is added to the base table. This column enables various batch operations that can be performed by the CipherTrust Manager.

Size of Encrypted Data

Encrypted data is predictably larger than plaintext data, so it is important to make sure that enough table space 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 is 56 bytes for DES and DES-EDE; for AES/SEED/ARIA, the new column is 64 bytes.

If the length of the original column is a multiple of the block size, then the new column is 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 is 64 bytes long.

In addition, if a random IV for each field is used, then the table has 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.

A new column is created and the original column is set to NULL. The new column is 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.