Configuring Oracle Databases

Before data can be migrated, CipherTrust Database Protection for Oracle must be installed on the database server and the database must be added to the CipherTrust Manager GUI.

This section provides information on operations that can be performed on the CipherTrust Manager for the CDP for the Oracle client.

Managing Database Connection

This section provides instructions on how to configure a connection between an Oracle database and the CipherTrust Manager. It also describes how to view, edit, and delete an existing database connection.

Creating an Oracle Database Connection

To create an Oracle database connection:

1. Log on to the CipherTrust Manager GUI.

2. Click Database Protection to open the application. The Databases screen displays the list of existing database connections, if any.

   

3. Click + Database and select Oracle from the available options. The Oracle Database Configuration page is displayed.

4. Enter the Connection Information details in the respective fields.

   

   The following table lists the parameters that are required when creating or managing an Oracle database connection.

   Item	Description
   Name (Alias)	Enter a name for the connection information. This field uniquely identifies a database connection.
   Host	Enter the IP address of the database server.
   Port	Enter the port on which the database server is listening for connections. Default values: • 1521: If a TCP based connection is used connect the CipherTrust Manager and Oracle. • 2484: If an SSL based connection is used to connect the CipherTrust Manager and Oracle.
   Database User Name	Enter the database login name that has permission to modify the tables to be migrated. This could be the owner of the database tables or a user with privileges to CREATE, MODIFY, and DROP views, tables, and triggers.
   Database User Password	Enter the password for the database user.
   MetaDatabase User Name	Displays the meta database user as INGRIAN. It cannot be changed. INGRIAN user is created as requirement before installing the CDP client for Oracle.
   MetaDatabase User Password	Enter the password for the meta database user.
   Enable Credentials Caching	Select to save the database credentials for multiple sessions. User authorization is not required to access table/column for a database in different sessions. Possible configurations: • Enabled: The user can directly access the table/column information for a database in multiple sessions. • Disabled: For each session, while accessing the table/column information for a database, user is prompted for database credentials. Once the credentials are validated successfully, further authorization is not required for that session. NOTE: The database authorization is session based. Authorization is mandatory for each session while accessing table/column for a database.
   Protocol	Select the protocol to be used to connect the CipherTrust Manager to an Oracle database. The supported protocols are: • TCP • SSL To create an SSL based connection: a. Upload the CA certificate to the CipherTrust Manager. For more information, refer to Uploading SSL Certificate to CipherTrust Manager. b. On the Oracle Database Configuration page, select the Protocol as SSL.
   Connect With	Select the mode of connection, either SID or Service Name.
   SID/Service Name	Enter the Oracle System ID or Service Name for the database.
   Enter Description	Enter description for the database. This field is optional.

5. Click Save. The newly added database appears on the Databases screen. The Status column reflects the status of the connection.

6. Click the refresh () icon on the screen if the status is not updated.

After the database is added, the next step is to create a user mapping. Refer to Managing User Mappings.

Editing Database Connections

To edit an existing database connection:

1. Log on to the CipherTrust Manager GUI.

2. Click Database Protection to open the application. The Databases screen displays the list of existing database connections, if any.

3. Click the overflow icon () corresponding to the desired database connection.

   

4. Click Edit Connection. The Connection Information screen is displayed in edit mode.

5. Edit the following fields/options as required.

   • Host

   • Port

   • Database User Name

   • Database User Password

   • MetaDatabase User Password

   • Enable Credential Caching

   • Protocol

   • SID/Service Name

   • Description

6. Click Save to effect the changes. The Status column on the Database screen reflects status of the connection.

7. Click the refresh icon () on the screen if the status is not updated.

Viewing Database Connections

To view the existing database connections:

1. Log on to the CipherTrust Manager GUI.

2. Click Database Protection to open the application. The Databases screen displays the list of existing database connections, if any.

   

Deleting Database Connections

To delete an existing database connection:

1. Log on to the CipherTrust Manager GUI.

2. Click Database Protection to open the application. The Databases screen displays the list of existing database connections, if any.

3. Click the overflow icon () corresponding to the desired database connection.

   

4. Click Delete.

The database connection is deleted. The Databases screen displays the available list of database connections.

Uploading SSL Certificate to CipherTrust Manager

To connect the CipherTrust Manager to an Oracle database using an SSL protocol, the CA certificate must be uploaded to the CipherTrust Manager. The CA certificate can be uploaded either using REST interface or the CipherTrust Manager's GUI. Once the CA certificate is successfully uploaded to the CipherTrust Manager, an SSL based connection can be established between the CipherTrust Manager and Oracle database.

Uploading an SSL Certificate Using REST Interface

To upload the Oracle database CA certificate using the REST interface from the API playground, perform the following steps:

1. Open the CipherTrust Manager URL in a browser. The login page is displayed.

2. On the login page, click the API & CLI Documentation link. The API playground is displayed in the right.

3. Click Authenticate.

4. Enter username, password, domain, and click POST. The default domain is root.

5. In the left pane, click CDP > Database.

6. Click Upload - post. The Upload - post section is displayed on the right.

7. In the body field, paste the CA certificate in the .pem format as shown below.

   {
       "name":"myCertCh",
       "cert":"-----BEGIN CERTIFICATE-----\nMIIBkDCB+gIBADANBgkqhkiG9w0BAQQFADARMQ8wDQYDVQQDEwZzZXJ2ZXIwHhcNMTkwNzMwMDQy\nODUyWhcNMjAwNzI5MDQyODUyWjARMQ8wDQYDVQQDEwZzZXJ2ZXIwgZ8wDQYJKoZIhvcNAQEBBQAD\ngY0AMIGJAoGBAMPJ9L0IanR5XxajKiLjEns2geFzYjCdOpIcqiHTH78D7N1nIm/XMWTbo7Bebi0V\nrirWM1LUakhHxUSV9L+nb1IMaAuk01/pmJaSbU8EPzcmujWoA66gKg2K0phExoT1RQ+QE+6pLBPl\nftSB8VLNiPcXx4GMQcUq9Kiw4CMmozvBAgMBAAEwDQYJKoZIhvcNAQEEBQADgYEAjxmFBnkkxp1K\n2gkDsfvz5Uq7bGvXwdO8M/j5Ryz812f4pzyCXbb8p4rkf9l27WJAJ6Ero9U07K7Rd8NqBJmReobq\nDcPQnp2y4dzsPI7/oMHfk69DkGIa4PHhEYcXWz37L33CBrxnASpqM6yc/thjARt73Ejs80foBkAf\nOG6//Cs=\n-----END CERTIFICATE-----"
   }
   

8. Click POST.

Uploading an SSL Certificate Using CipherTrust Manager's GUI

To upload the Oracle database CA certificate using the CipherTrust Manager's GUI:

1. Log on to the CipherTrust Manager GUI.

2. Click Database Protection to open the application.

3. Click CDP > Database Certificates.

4. Enter the Name and Certificate in .pem format as shown below:

   

5. Click Upload. A message is displayed on the Database Certificate screen showing the status of the upload. For example, if a certificate is successfully uploaded, following message is displayed:

   

Managing User Mappings

A user mapping is an association between a database user or a database role, and a local user on the CipherTrust Manager. CDP uses this user mapping to authenticate with the CipherTrust Manager before submitting any cryptographic requests.

When a database user sends a request to the CipherTrust Manager, CDP searches its list of the user mappings (contained in the ING_AUTHORIZED_USER table in the metadata database). If the database user appears on the list or is a member of a mapped database role, CDP includes the associated CipherTrust Manager user and password in the request. If those credentials are valid and the CipherTrust Manager user has access to the required key, then the crypto operation is performed. If the credentials are invalid or the CipherTrust Manager user does not have access to the key, the operation fails.

If a database user, who is a member of a database role mapped to a local user, is also mapped individually to a local user, the individual user mapping takes precedence.

If a database user belongs to multiple database roles, which are mapped to local users, the user inherits the access privileges of the local user mapped to the role that appears first in the alphabetic ascending order.

Some features may need to be enabled for all database users and database roles not otherwise listed on the List of users screen. To do this, the default mapping value should be associated with a specific CipherTrust Manager user. For example, a CipherTrust Manager user with access to global keys can be created, or a CipherTrust Manager user with access to no specific permissions can be created to enable the replacement value feature.

Default Mapping

The default mapping is a catch-all CipherTrust Manager user connected to the CipherTrust Manager when no user mapping exists for a database user. When there is no default mapping and an unmapped database user attempts to access sensitive data, CDP returns an error message and does not send the request to the CipherTrust Manager. It may be useful to create a default mapping to prevent CDP from automatically returning this error.

When this feature is enabled, instead of returning an error message, CDP connects to the CipherTrust Manager as the default CipherTrust Manager user. How the CipherTrust Manager then responds to requests depends on the CipherTrust Manager configuration. The CipherTrust Manager might return following:

• insufficient permissions

• NULL

• Pre–configured replacement value.

• Return encrypted value

(This behavior is configured on the Database Column Properties screen for the encrypted column.)

When the default mapping is assigned, the system creates an entry in the ING_AUTHORIZED_USER table with the user name, ING_DEFAULT_USER. For this reason, avoid using ING_DEFAULT_USER to represent a specific database user.

This section covers the following topics:

• Viewing/Adding User Mappings

• Deleting/Editing User Mapping

Viewing/Adding User Mappings

Viewing User Mappings

To view the list of existing user mappings for an Oracle database connection:

1. Log on to the CipherTrust Manager GUI.

2. Click Database Protection to open the application. The Databases screen displays the list of existing database connections, if any.

   

3. Click the overflow icon () corresponding to the desired database connection.

   

4. Click Manage User Mapping. The List of users screen is displayed. It displays the existing user mappings for an Oracle database connection.

   

Adding User Mapping

To add a new user mapping for an Oracle database connection:

1. On the List of users screen, click Map User. The Map User screen is displayed.

   

2. Enter the mapping details:

   Item	Description
   Database User	The database user or role that can be used to connect to the CipherTrust Manager. To create a default mapping, enter ING_DEFAULT_USER in this field. NOTE: The default mapping value applies to all the database users not otherwise listed on the List of users screen. Refer to Managing User Mappings for additional information on default user mapping.
   Local User	Enter the local user to which the database user is to be mapped. (The local user is a CipherTrust Manager user.)
   Local Password	Enter the password for the local user.

3. Click Save. The new user mapping appears on the List of users screen.

Adding User Mapping in Domain

To add a user mapping in a domain:

1. On the List of users screen, click Map User. The Map User screen is displayed.

2. Enter the mapping details. The local user name should include domain name (for example, my-domain||admin) as shown below:

   

3. Click Save. The new user mapping with domain name appears on the List of users screen.

   

Deleting/Editing User Mapping

To delete or edit an existing user mapping:

1. Log on to the CipherTrust Manager GUI.

2. Click Database Protection to open the application. The Databases screen displays the list of existing database connections, if any.

3. Click the overflow icon () corresponding to the desired database connection.

   

4. Click Manage User Mapping. The List of users screen is displayed. It displays the list of existing user mappings for an Oracle database connection.

   

5. Click the overflow icon () corresponding to the desired user mapping connection.

   

6. (Optional) Click Delete. Skip this step if you do not want to delete an existing user mapping.

7. Confirm the delete operation when prompted. The user mapping is deleted from the List of users screen.

8. Click the overflow icon () corresponding to the desired user mapping connection.

   

9. Click Edit. The Edit User Mapping screen is displayed.

   

10. Change the Local User and enter its password.

11. Click Save.

The user mapping is updated on the List of users screen.

Managing Tables

Once a connection between an Oracle database and the CipherTrust Manager is set up, and the user's role is defined, you can perform cryptographic operations on the table and the columns containing the plaintext values.

To perform cryptographic operations:

1. Add a database table

2. Set column-level encryption properties

Adding a Database Table

To add a database table:

1. Log on to the CipherTrust Manager GUI.

2. Click Database Protection to open the application. The Databases screen displays the list of existing database connections, if any.

   

3. Click the overflow icon () corresponding to the desired database connection.

   

4. Click Manage Tables. The List of tables screen is displayed.

   

5. Click Add Table. The Add Table screen is displayed.

   

   On the Add Table screen, use one of the options:

   a. Select the table for the owner used in add connection.

   b. Change Owner Name.

   • Enter Owner Name and click Change Owner.

   • Select table from the drop-down list and click Save.

   You can also manually enter the table name in the Select Table field, if it is not displayed in the drop-down list. The table contains the plaintext values to be encrypted.

   The List of columns screen is displayed. The screen displays the list of available columns in the table with the column details, such as data type, width, and attributes of the column.

   

Setting Column Level Encryption Properties

To perform migration of a column, set the column-level encryption properties. In this step, select the parameters that are required during the migration process.

To set the column-level encryption properties:

1. Select the column for which you want to set the column-level encryption properties. The Database Column Properties screen is displayed.

   

2. Enter the encryption properties for the column. The following table lists the parameters that are required when setting column-level encryption properties.

   Item	Description
   Name	Displays the new column name. The new column name will be in the format <column>_NEW. As part of the data migration process, the system creates a new column to hold the encrypted data. This field displays the original column. Once the column properties are saved here, do not modify the column name in the database. Discrepancies between the column name in the database and the encryption instructions cause an error. If such an error occurs during the migration, restore the original column name shown in the error message.
   Type	Data type of the column.
   Width	Width of the column, before and after encryption. NOTE: The selected algorithm impacts the column width for the encrypted data.
   Encryption Type	The type of encryption to be performed. The possible values are: • Standard: Encryption without retaining the format of the input data. • FPE: Encryption while retaining the format of the input data.
   Algorithm	Algorithm to be used for encryption. Possible values are: • TDES • ARIA • AES • SEED The CipherTrust Manager supports PKCS5Padding and NoPadding options. PKCS5Padding is used for all block ciphers (SEED, TDES, ARIA, and AES). NOTE: For FPE encryption type, only AES algorithm is applicable.
   Key	Key to be used for encryption and decryption. Only keys that apply the selected algorithm and to which the user has access are available. NOTE: The selected key must be available to the database users that perform the migration. The Key drop-down list displays both versioned and non-versioned keys. NOTE: For FPE, only non-versioned AES keys are used.
   Mode/Cardinality	For Standard encryption, the following options are available when AES, SEED, TDES, or ARIA algorithm is selected: • CBC • ECB It is recommended that you use block ciphers (SEED, TDES, ARIA, and AES) in the CBC mode unless there is a compelling reason to use the ECB mode. CBC is considered to be a more secure mode for a variety of reasons: • ECB’s biggest disadvantage is that for a given key, two identical plaintexts will correspond to an identical ciphertext; whereas, CBC uses the ciphertext of the previous block of plaintext as the initialization vector for the encryption of the next block of plaintext. This succeeds in guaranteeing that two identical plaintext blocks will not result in the same ciphertext. • CBC detects if blocks arrive out of order, which prevents a block switching attack. For format preserving encryption, the following option is available (only AES algorithm is supported for PFE): • CARD10: To encrypt data consisting of digits 0-9. Special characters, including space, if present in input data, are retained as it is. NOTE: For FPE, if the input data has characters other than digits 0-9 and special characters including space during migration, then the invalid input data error will be encountered. Also, if the table is already migrated, then inserting such data will return the invalid input data error.
   IV	Initialization Vector (IV) method. An IV is a sequence of random bytes appended to the front of the plaintext before encryption by a block cipher. The IV method is available only when using AES, SEED, ARIA or TDES algorithms. The IV is required for the CBC mode and not allowed for the ECB mode. Using an IV eliminates the possibility that the initial ciphertext block will be the same for any two encryption operations, which use the same key. IVs can be applied at the field-level or column-level. For standard encryption type, the IV you specify for operations that use TDES keys must be eight-byte; for AES keys, the IV must be sixteen-byte. If you specify an IV here, the IV must be specified in hexadecimal (base 16 encoded) characters. An eight-byte IV requires sixteen characters; a sixteen-byte IV requires thirty-two characters. For FPE encryption type, the IV you specify for operations must be hex encoded IV and should be s-integers (specified set of integers, 0-9) of length 112 characters. For example, an IV can be "01080506070..." of length 112 characters. This IV will be used for data length > 56. After encryption, this field displays the IV used.
   Padding	Padding mode to be used when encrypting the column. The default value is PKCS5Padding. NOTE:Not applicable for FPE encryption.
   FPE Encryption Format	The formats the user may use to determine the structure of the output. Following formats are supported for FPE: • NONE - No format is applied with FPE. In this case, complete plaintext will be considered as an input for crypto operation. • FIRST_SIX - This format allows the user to keep intact the first six digits of the plaintext input. So, after encryption, the first six digits of the output ciphertext will remain same as input plaintext and rest digits will be encrypted using FPE. • FIRST_SIX_LAST_FOUR - This format allows the user to keep intact the first six and last four digits of the plaintext input. So, after encryption the first six and last four digits of the output ciphertext will remain same as input plaintext and rest digits will be encrypted using FPE. • FIRST_TWO_LAST_FOUR - This format allows the user to keep intact the first two and last four digits of the plaintext input. So, after encryption the first two and last four digits of the output ciphertext will remain same as input plaintext and rest digits will be encrypted using FPE. • LAST_FOUR - This format allows the user to keep intact the last four digits of the plaintext input. So, after encryption the last four digits of the output ciphertext will remain same as input plaintext and rest digits will be encrypted using FPE. NOTE: The effective data length (excluding special characters) of input plaintext to be encrypted must be greater than the selected format. For example, for FIRST_SIX format, if the input plaintext is 12345678@#, then encryption will be performed on 78 after retaining the first six characters and the special characters @#. Suppose the input plaintext was 12345@#, then the encryption will not be performed on this, as the effective data to be encrypted is less than six even though the input plaintext has seven characters.
   Tweak Algorithm	The hashing algorithm to be applied to specified tweak data in case of FPE. Select from the available options: • NONE • SHA1 • SHA256 If NONE is selected in the FPE Encryption Format field, then NONE, SHA1, and SHA256 options are available for Tweak Algorithm. If any encryption format is selected, then only SHA1 and SHA256 options are available. Enter the tweak data for the selected Tweak Algorithm in the text field. Tweak data is an ASCII string of maximum 256 characters. It accepts any ASCII value for SHA1 and SHA256 and any valid hex encoded value for “NONE” like “1111111111111111”.
   Attributes	Displays the attributes applied to the column that is being configured for encryption, such as nullable. Some attributes, such as nullable, will not prevent the encryption of a column; other attributes, such as foreign key, will prevent encryption. Some attributes, such as primary key should be considered very carefully because it is possible that the key is referenced implicitly as a foreign key.
   Decryption Behavior of users with Insufficient Permissions	The value the system returns when a user with insufficient access permissions attempts to query the database. Available options are: • Return with "insufficient permissions" error - Unauthorized requests return an error. • Return NULL - Returns a NULL value when unauthorized requests are made in this column. The query executes without generating an error, and the value returned for the decrypted column is NULL. • Return replacement value - Selecting this option allows specifying the value that is returned when a user makes an unauthorized attempt to decrypt the data in this column. The specified value must be a valid value for the data type and length of the column being configured. A query by an unauthorized user will return successfully, and the specified value will be returned in place of the actual decrypted value. • Return encrypted value - Returns the encrypted value when a user makes an unauthorized attempt to decrypt the data in this column. This option is applicable for FPE encryption only. NOTE: 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 CDP does not interact with the CipherTrust Manager and the replacement values feature is not activated. NOTE: An invalid user is one that is mapped to any group which do not have permission on the key and also the user is not part of Key Admins group (it is a system defined group). For such an invalid user only, the error replacement behavior is applicable.

3. Click Save to save the column properties values. (Clicking Cancel returns you to the List of columns screen.)

Operations Using pdbctl Utility

Once the column-level encryption properties are set, you can perform migration using the pdbctl utility.

Migrating data using the pdbctl utility includes the following steps:

1. Adding a database connection.

   ./pdbctl adddb -a <demoalias> -u <database_user> -d <database_name> -p <database_password> -t Oracle -i <10.1.1.1> -m <meta_user_password> -o <port> -b <naeuser> -c <naepassword>
   

   Note

   Here, naeuser corresponds to the CipherTrust Manager local user. The user should have the encryption/decryption permission on the key used for configuring encryption properties for the column.

2. Migrating the data.

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

For more information on the pdbctl utility, refer to the pdbctl Utility User Guide.