Your suggested change has been received. Thank you.

close

Suggest A Change

https://thales.na.market.dpondemand.io/docs/dpod/services/kmo….

Thales Logo

All

  • All
back

Tasks

Encrypt a column

search

Please Note:

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

  1. Ensure that you have the following permissions:

    • Create Table

    • Create View

    • Alter Schema

  2. Server role as public.

  3. Database role as db_datareader and db_datawriter.

  4. CDP for SQL Server is installed. Refer to Quick Start for details.

  5. The pdbctl utility is installed.

Steps

  1. Create a database connection on the CipherTrust Manager. For detailed instructions, refer to Creating a SQL Server Database Connection.

  2. Generate 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.

  3. Create a user mapping. Tables cannot be migrated until a user mapping is added. You can create user mapping using any of these options:

    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 as Database User Login, and you want to use key1 for migration, then the database user sa must be mapped to the owner of key1 for successful migration.

  4. Back up your database.

  5. Configure the properties file. For the complete list of parameters, refer to Configuration Parameters.

  6. Give public access to the master database to all users who access sensitive data for encryption and decryption.

  7. Select the table and configure column-level encryption using any of the following options.

    Note

    You can also set encryption parameters for multiple columns. Refer Set encryption parameters for multiple columns for details.

  8. Connect to pdbctl utility using the adddb command.

  9. Encrypt the table using any of the following options.

    • pdbctl utility. Run following command:

          ./pdbctl migrate -a <database_alias> -t <table_name>

    • CipherTrust Manager UI (available on CipherTrust Manager 2.11.1 and higher versions. Refer to Encrypt a Column for details.)

Flags description

The following table describes the flags and parameters associated with this command:

FlagData TypeDescription
-bintBatch 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.
-astringDatabase alias associated with the database user.
-hFlag to view help for migrate command.
-istringInsert trigger name. This is an optional parameter.
-nstringNew table name. This is an optional parameter.
-tstringTable name that contains the columns to encrypt.
-ustringUpdated trigger name. This is an optional parameter.
-- verbosePrint verbose logs.
-vstringView 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

On this page