Configuring Encryption and Search on Encrypted Data

You use Siebel Tools to enable encryption for a column in a database table and to enable search on the encrypted column. You encrypt a column and its data by specifying values for certain parameters of the column in the database table.

You can also enable search on the encrypted data by creating an additional column (hash column) that stores the result of applying the SHA-2 algorithm to the plain text value of the encrypted data. Search can be case-sensitive or case-insensitive depending on how you configure search.

Note: For help with encrypting columns in database tables, contact your Oracle sales representative for Oracle Advanced Customer Services to request assistance.

This procedure explains how to encrypt data and, optionally, enable searching on the encrypted data. Before you begin, note the following:

The encrypted column, hash column, and the column that stores the index number to the key file must come from the same database table.
You can't encrypt a column that has a denormalized column, because this feature isn't supported.
For example, column NAME of account table S_ORG_EXT has a denormalized column in: S_ACCNT_POSTN.ACCOUNT_NAME.
The encrypted column and the hash column must be of type String (VARCHAR), while the column that stores the index number to the key file must be of type Integer.
For more information on requirements for data encryption, see Requirements for Data Encryption.

Attention: All encryption that's upgraded is upgraded to a minimum of 256 bits in Siebel CRM.

Encrypt a Column and Enable Search

Here's how to encrypt a column and enable search on the encrypted column in a database table:

Start Siebel Tools.
Select the column in the database table that contains the data you want to encrypt.
Add values to the following parameters of the column you selected in the previous step:
- Computation Expression. Specify the algorithm to encrypt data in the column as follows: Siebel Encrypt.AES[ColumnName]).
  For information on the Siebel AES encryption options, see Data Encryption.
- Encrypt Key Specifier. Specify the column that stores the index number to the key file.

To allow search on encrypted data, create another column with a name of your choice or with the following name format: C_HASH_NAME. Where Name is the name of the column you selected earlier in this procedure.

C_HASH_NAME stores the value that results from applying the SHA-2 algorithm to the plain text values of the column you selected earlier in this procedure.

The following table shows the syntax for several search scenarios.

Scenario	Enter these values
Encrypt data in column C_SSI using the AES algorithm	For Computation Expression, enter: `SiebelEncrypt.AES ([C_SSI])`. For Encrypt Key Specifier, specify the column that stores the index key for the key file. For example:`C_KeyIndex`.
To enable case-sensitive search on the data that you encrypt in column C_SSI, you create an additional column C_HASH_SSI	Enter the following syntax in the field for the Computation Expression of column C_HASH_SSI: `SiebelHash.SHA1 ([C_SSI])`.
To enable case-insensitive search on the data that you encrypt in column C_SSI, you create an additional column C_HASH_SSI	Enter the following syntax in the field for the Computation Expression of column C_HASH_SSI: `SiebelHash.SHA1CI ([C_SSI])`.

Scenario

Enter these values

Encrypt data in column C_SSI using the AES algorithm

For Computation Expression, enter: SiebelEncrypt.AES ([C_SSI]).

For Encrypt Key Specifier, specify the column that stores the index key for the key file. For example:C_KeyIndex.

To enable case-sensitive search on the data that you encrypt in column C_SSI, you create an additional column C_HASH_SSI

Enter the following syntax in the field for the Computation Expression of column C_HASH_SSI: SiebelHash.SHA1 ([C_SSI]).

To enable case-insensitive search on the data that you encrypt in column C_SSI, you create an additional column C_HASH_SSI

Enter the following syntax in the field for the Computation Expression of column C_HASH_SSI: SiebelHash.SHA1CI ([C_SSI]).

Now do one of the following:

If the column that you have enabled for encryption does not yet contain data, then there are no further steps to perform.
If the column that you have enabled for encryption does contain data, then proceed to the next step of this procedure.

If the database column that you have enabled for encryption previously contained data, then run the Encryption Upgrade utility (encryptupg.exe) to encrypt the existing data and, if applicable, to create searchable hash values for the data.
Encrypt existing data immediately after you configure a column for encryption. You can create searchable hash values for the column at a later time if you choose.

This step is mandatory for successful Siebel CRM upgrade, otherwise encrypted data with the old hash value will not be readable.
Update the repository and deliver the updates.

Note that tables can be published using the Apply/DDL button or the siebdev.exe utility in Siebel Tools (Windows environment). For more information on publishing tables, see Using Siebel Tools.