Decrypt a column
When you decrypt a column:
The column containing the ciphertext is copied to a temporary table along with the identity column and the IV column if there is one.
The ciphertext is sent to the pdbctl utility along with the IV for decryption.
The pdbctl utility returns the plaintext data to the database, and the plaintext is inserted into the original column. Remember that when the data was originally encrypted, another column was created and the original column was set to NULL after deleting the old data.
After the plaintext data is inserted into the column, any new columns that were created during the migration are dropped. These might include the column that held the ciphertext, the IV column, and the identity column.
Important Notes
Although decryption restores the plaintext data and drops the columns created during data encryption, it does not restore any constraints lost during encryption. If you want to return a database to its true pre-migration state, restore a backup.
The Database User Login that you specified while configuring the database connection to CipherTrust Manager must be mapped to the owner of the key that was used for encryption/key rotation. If this is not the case, the decryption stops with an error.
When data is encrypted using a versioned key, the resulting ciphertext contains information in its header indicating which version of the key was used. This header is 3-byte long. During decryption or verification process, the pdbctl utility parses this information and applies the correct key version. There is no need to specify the key version. While using versioned keys, to decrypt the data, at least one version of the key must be Active. If the version of the key is retired key, an exception is thrown.
Prerequisite
The pdbctl utility is installed.
Steps
To unencrypt columns of a table, run the following command:
./pdbctl unmigrate <col1 col2 ...> -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 unmigrated. 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 unmigrate command. | |
| -t | string | Table name to be decrypted. |
| --verbose | Print verbose logs. |
Note
When performing unencryption of large data types with batch size > 1, the following message is displayed:
Unencrypting 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 decrypts the column CITY in the table CUSTOMERS associated with alias demo:
./pdbctl unmigrate -a demo -t CUSTOMERS CITY
The output shows the status of the operation:
Processing...
: Job Id 135 |
Status :success
To check the encryption parameters for the columns in the table CUSTOMERS, use the listcolumns command.
./pdbctl listcolumns -a demo -t CUSTOMERS
The output is:
---------------------------------------------------------------
Column name: CUSTOMER_ID
Column type: NUMBER
Column width: 10
Column key: aes256
Column Algorithm: AES
Column Migrated: false
Column IV: 8D74E7CC0E659F2D8A8BC417750856FE
---------------------------------------------------------------
Tip
After decrypting a table, it is recommended to remove the un-needed ciphertext. Refer to Delete data after encryption/decryption for details.
