Encrypt a column
Encrypting a column involves converting column’s plaintext values to ciphertext and building views and triggers so that applications can seamlessly interact with the encrypted data. The data encryption is an interaction between the database table, CDP, pdbctl utility, and CipherTrust Manager. Refer to Encryption Flow for details.
CDP allows online table encryption. You do not need to take the tables offline during the encryption process. You can select, insert, update, and delete records from the database tables while the encryption is in progress. The views and triggers are automatically created during the encryption process. After the views and triggers are created, you can delete and recreate them, if required.
Prerequisites
Ensure that you have the following permissions :
Create Table
Alter Table
Drop Table
Create Index
Create View
Drop View
Create Trigger
Drop Trigger
CDP for DB2 is installed. Refer to Quick Start for details.
The pdbctl utility is installed. Refer to the pdbctl utility documentation for details.
Steps
Create a database connection. Use any of the options to add a database connection:
Using CipherTrust Manager UI. Refer to Creating a DB2 Database Connection for details.
pdbctl utility. Refer to pdbctl utility documentation for details.
Create Keys on CipherTrust Manager. Refer to Creating Keys for details. The key can be either versioned or non-versioned. When using a versioned key:
you can encrypt only with the active versions of that key.
you must create an instance of a the default version or a specific version of a key.
Add a user mapping. Tables cannot be migrated until a user mapping is added. You can create user mapping using any of these options:
CiphetTrust Manager UI. Refer to Managing User Mappings for details.
pdbctl utility. Refer to the pdbctl utility documentation for details.
Note
The Database User Login that you specified while configuring the database connection to CipherTrust Manager must be mapped to the owner of the key being used for migration. If this is not the case, the migration will not complete successfully. For example, if you specify
sa
asDatabase User Login
, and you want to usekey1
for migration, then the database usersa
must be mapped to the owner ofkey1
for successful migration.Select the table and configure column-level encryption. Use any of the options to configure column-level encryption properties.
Using CipherTrust Manager UI. Refer to Managing Tables for details.
pdbctl utility. Refer to Configure column-level encryption settings for details.
Encrypt a table using the following command:
./pdbctl migrate -a <database_alias> -t <table_name>
Flags description
The following table describes the flags and parameters associated with this command:
Flag | Data Type | Description |
---|---|---|
-b | int | Batch size to be migrated. The value must be an integer. This is an optional parameter. Default values: > 1 - For large data types > 1000 - For other data types. |
-a | string | Database alias associated with the database user. |
-h | Flag to view help for migrate command. | |
-i | string | Insert trigger name. This is an optional parameter. |
-n | string | New table name. This is an optional parameter. |
-t | string | Table name that contains the columns to encrypt. |
-u | string | Updated trigger name. This is an optional parameter. |
-- verbose | Print verbose logs. | |
-v | string | View name. This is an optional parameter. |
Note
When performing migration of large data types with batch size > 1, the following message is displayed:
Encrypting large datatype column(s) with batch size greater than 1 fails if it contains any data greater than 3936
Continue only if the data length is ≤ 3936, otherwise, use the default batch size.
Example
The following sample command performs the data migration for the table CUSTOMERS
as per the set encryption parameters along with the status of the operation:
./pdbctl migrate -a demo -t CUSTOMERS
The output shows the status of the operation:
Job ID: 133
Processing...
Job Id 133 | Status : success
Data encryption errors
If the data migration ends in error, you can perform the following operations, as required:
Restore job
Drops the columns created during the encryption. This returns the table to its pre-encryption state.
To restore a job, run the following command:
./pdbctl restorejob -a <database_alias> -t <table_name> -j <jobid>
Resume job
Continues the process from where it ended. You may want to select this option after you have remedied the source of the error.
To resume a job, run the following command:
./pdbctl restorejob -a <database_alias> -t <table_name> -j <jobid> -- resume
Cancel job
Cancels the data migration process.
To cancel a job, run the following command:
./pdbctl restorejob -a <database_alias> -t <table_name> -j <jobid> -- cancel