3 Transparent Data Encryption

3.1.1 Benefits of Using Transparent Data Encryption

Transparent data encryption has the following advantages:

• As a security administrator, you can be sure that sensitive data is safe in case the storage media or data file gets stolen.

• Implementing transparent data encryption helps you address security-related regulatory compliance issues.

• You do not need to create triggers or views to decrypt data. Data from tables is transparently decrypted for the database user.

• Database users need not be aware of the fact that the data they are accessing is stored in encrypted form. Data is transparently decrypted for the database users and does not require any action on their part.

• Applications need not be modified to handle encrypted data. Data encryption/decryption is managed by the database.

3.1.2 When to Use Transparent Data Encryption

Use transparent data encryption to protect confidential data, such as credit card and social security numbers, without having to manage key storage or create auxiliary tables, views, and triggers. An application that processes sensitive data can use this feature to provide strong data encryption with little or no change to the application.

3.1.3 How Transparent Data Encryption Works

Transparent data encryption is a key-based access control system. Even if the encrypted data is retrieved, it cannot be understood until authorized decryption occurs, which is automatic for users authorized to access the table.

When a table contains encrypted columns, a single key is used regardless of the number of encrypted columns. This key is called the column encryption key. The column encryption keys for all tables, containing encrypted columns, are encrypted with the database server master encryption key and stored in a dictionary table in the database. No keys are stored in the clear.

As shown in Figure 3-1, the master encryption key is stored in an external security module that is outside the database and accessible only to the security administrator. For this external security module, Oracle uses an Oracle wallet as described in this chapter. Storing the master encryption key in this way prevents its unauthorized use.

Figure 3-1 Transparent Data Encryption Overview

Using an external security module separates ordinary program functions from encryption operations, making it possible to divide duties between database administrators and security administrators. Security is enhanced because the wallet password can be unknown to the database administrator, requiring the security administrator to provide the password.

3.1.4 Overview of Basic Transparent Data Encryption Operations

To enable transparent data encryption, you must have the ALTER SYSTEM privilege and a valid password to the Oracle wallet. If an Oracle wallet does not exist, then a new one is created using the password specified in the SQL command.

To create a new master key and begin using transparent data encryption, issue the following command:

ALTER SYSTEM SET [ENCRYPTION] KEY IDENTIFIED BY password

Enclose the password in double quotation marks (" "). This command generates the database server master encryption key, which the server uses to encrypt the column encryption key for each table. No table columns in the database can be encrypted until the master key of the server has been set.

The master encryption key remains accessible to the database until the database instance is shutdown. To load the master encryption key after the database is restarted, use the following command:

ALTER SYSTEM SET [ENCRYPTION] WALLET OPEN IDENTIFIED BY password

Enclose the password in double quotation marks (" "). To create a new table with encrypted columns, use the CREATE TABLE command in the following form:

CREATE TABLE table_name ( column_name column_type ENCRYPT,....);

The ENCRYPT keyword against a column specifies that the column should be encrypted.

If an existing table has columns that require encryption, then use the ALTER TABLE command in the following form:

ALTER TABLE table_name MODIFY ( column_name column_type ENCRYPT,...);

The ENCRYPT keyword against a column specifies that the column should be encrypted.

To disable access to all encrypted columns in the database, use the following command:

ALTER SYSTEM SET [ENCRYPTION] WALLET CLOSE

The preceding command disables access to the master key in the wallet and prevents access to data in the encrypted columns. You need to open the wallet again, using the ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY password command, to reenable access to the master encryption key.

3.2.1 Enabling Transparent Data Encryption

Transparent data encryption was first introduced in Oracle Database 10g release 2 (10.2). To use this feature, you must be running Oracle Database 10g release 2 (10.2) or higher.

To start using transparent data encryption, the security administrator must create a wallet and set a master key. The wallet can be the default database wallet shared with other Oracle Database components, or a separate wallet specifically used by transparent data encryption. Oracle recommends that you use a separate wallet to store the master encryption key.

3.2.2 Setting and Resetting the Master Encryption Key

The master encryption key is stored in the Oracle wallet, and is used to protect column encryption keys. By default, the master encryption key is a random key generated by transparent data encryption. It can also be an existing key pair from a PKI certificate designated for encryption. To use transparent data encryption with PKI key pairs, the issuing certificate authority must be able to issue X.509v3 certificates with the key usage field marked for encryption.

Neither key type is more secure, but if you have already deployed PKI within your organization, then you can leverage such PKI services as key escrow and recovery. However, encryption using current PKI algorithms requires significantly more system resources than symmetric key encryption. Using a PKI key pair as a master encryption key may result in greater performance degradation when accessing encrypted columns in the database.

Use the ALTER SYSTEM command to set or reset the master encryption key. The following sections discuss setting and resetting the master encryption key.

SQL> ALTER SYSTEM SET [ENCRYPTION] KEY [certificate_ID] IDENTIFIED BY password

ALTER SYSTEM SET ENCRYPTION KEY "j23lm781098dhb345sm" IDENTIFIED BY "p3812dH9E";

3.2.3 Opening the Encrypted Wallet

The external security module stores the master encryption key in an Oracle wallet. The database must load the master encryption key into memory before it can encrypt or decrypt columns. Opening the wallet allows the database to access the master encryption key. Use the following ALTER SYSTEM command to explicitly open the wallet:

ALTER SYSTEM SET [ENCRYPTION] WALLET OPEN IDENTIFIED BY password

where password is the password to open the wallet. Enclose the password string in double quotation marks (" ").

Once the wallet has been opened, it remains open until you shut down the database instance, or close it explicitly by issuing the ALTER SYSTEM SET [ENCRYPTION] WALLET CLOSE command. When you restart the instance, you must issue the ALTER SYSTEM SET [ENCRYPTION] WALLET OPEN command again.

If the schema does not have the ALTER SYSTEM privilege, or the wallet is unavailable, or an incorrect password is given, then the command returns an error and exits. If the wallet is already open, the command returns an error and takes no action. Example 3-2 shows an example of each usage case.

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "U83j10LLt8v";
Wallet opened.

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "U83j10LLt8v";
ERROR at line 1:
ORA-28354: wallet already open 

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "U93j10LLt8v";
ERROR at line 1:
ORA-28353: failed to open wallet

3.2.4 Creating Tables with Encrypted Columns

To create relational tables with encrypted columns, specify the SQL ENCRYPT clause when you define database columns with the CREATE TABLE statement.

This section contains the following topics:

• Creating a Table with an Encrypted Column

• Creating a Table with an Encrypted Column Using a Nondefault Algorithm and No Salt

• Creating an Encrypted Column in an External Table

CREATE TABLE employee (
     first_name VARCHAR2(128),
     last_name VARCHAR2(128),
     empID NUMBER,
     salary NUMBER(6) ENCRYPT
);

CREATE TABLE employee (
     first_name VARCHAR2(128),
     last_name VARCHAR2(128),
     empID NUMBER ENCRYPT NO SALT,
     salary NUMBER(6) ENCRYPT USING '3DES168'
);

CREATE TABLE emp_ext (
     first_name,
     last_name,
     empID,
     salary,
     ssn ENCRYPT IDENTIFIED BY "xIcf3T9u"
)  ORGANIZATION EXTERNAL
   (
    TYPE ORACLE_DATAPUMP
    DEFAULT DIRECTORY "D_DIR"
    LOCATION('emp_ext.dat')
    )
    REJECT LIMIT UNLIMITED
as select * from employee;

3.2.5 Encrypting Columns in Existing Tables

To add an encrypted column to an existing table, or to encrypt or decrypt an existing column, you use the ALTER TABLE SQL command with the ADD or MODIFY clause.

This section contains the following topics:

• Adding an Encrypted Column to an Existing Table

• Encrypting an Unencrypted Column

• Disabling Encryption on a Column

ALTER TABLE employee ADD (ssn VARCHAR2(11) ENCRYPT);

ALTER TABLE employee MODIFY (first_name ENCRYPT);

ALTER TABLE employee MODIFY (first_name DECRYPT);

3.2.6 Creating an Index on an Encrypted Column

To create an index on an encrypted column, you use the standard CREATE INDEX command. The column being indexed must have been encrypted without salt. Example 3-9 shows how to create an index on a column that has been encrypted without salt.

CREATE TABLE employee (
   first_name VARCHAR2(128),
   last_name VARCHAR2(128),
   empID NUMBER ENCRYPT NO SALT,
   salary NUMBER(6) ENCRYPT USING '3DES168'
);
CREATE INDEX employee_idx on employee (empID);

3.2.7 Adding or Removing Salt from an Encrypted Column

Salt is a way to strengthen the security of encrypted data. It is a random string added to the data before it is encrypted, causing repetition of text in the clear to appear different when encrypted. Salt thus removes one method attackers use to steal data, namely, matching patterns of encrypted text.

To add or remove salt from encrypted columns, use the ALTER TABLE MODIFY command. Example 3-10 encrypts the first_name column using salt. If the first_name column was encrypted without salt earlier, then this command reencrypts it using salt.

ALTER TABLE employee MODIFY (first_name ENCRYPT SALT);

Example 3-11 removes salt from the first_name column. If you need to index a column that was encrypted using salt, then you can use this command to remove the salt before indexing.

ALTER TABLE employee MODIFY (first_name ENCRYPT NO SALT);

3.2.8 Changing the Encryption Key or Algorithm for Tables Containing Encrypted Columns

Each table can have only one column encryption key for it's columns. You can regenerate the column encryption key with the ALTER TABLE command. You can also choose to use a different encryption algorithm for the new column encryption key. It is a good security practice to back up the wallet before and after such changes.

Example 3-12 regenerates the column encryption key for the employee table.

ALTER TABLE employee REKEY;

Example 3-13 regenerates the column encryption key for the employee table using the 3DES168 algorithm.

ALTER TABLE employee REKEY USING '3DES168';

3.3.1 Oracle Wallet Management

Transparent data encryption stores the master encryption key in an Oracle wallet. Transparent data encryption can use the default database wallet shared by all Oracle components. It can also use a separate wallet for this purpose. The wallet can be an auto login wallet that allows access to encrypted data without requiring a security administrator to explicitly open the wallet.

3.3.2 Backup and Recovery of Master Encryption Keys

This section contains the following topics:

• Backup and Recovery of Oracle Wallet

• Backup and Recovery of PKI Key Pair

exclude name *.p12

ALTER SYSTEM SET [ENCRYPTION] KEY certificate_id IDENTIFIED BY wallet_password

3.3.3 Export and Import of Tables with Encrypted Columns

The following points are important when exporting tables containing encrypted columns:

• Sensitive data should remain unintelligible during transport

• Authorized users should be able to decrypt the data after it is imported at the destination

You can use the Oracle Data Pump utility to export and import tables containing encrypted columns. Oracle Data Pump makes use of the ENCRYPTION parameter to enable encryption of data in dump file sets. The ENCRYPTION parameter allows the following values:

• ENCRYPTED_COLUMNS_ONLY: Encrypted columns are written to the dump file set in encrypted format

• DATA_ONLY: All data is written to the dump file set in encrypted format

• METADATA_ONLY: All metadata is written to the dump file set in encrypted format

• ALL: All data and metadata is written to the dump file set in encrypted format

• NONE: Encryption is not used for dump file sets

The following steps discuss exporting and importing tables with encrypted columns using ENCRYPTION=ENCRYPTED_COLUMNS_ONLY:

1. You should ensure that the encryption wallet is open, before attempting to export tables containing encrypted columns. This is because the encrypted columns need to be decrypted using the column encryption keys, which in turn requires access to the master encryption key. The columns are reencrypted using a password, before they are exported.

2. Use the ENCRYPTION_PASSWORD parameter to specify a password that is used to encrypt column data in the export dump file set. The following example exports the employee_data table:

   expdp hr/password TABLES=employee_data DIRECTORY=dpump_dir
   DUMPFILE=dpcd2be1.dmp ENCRYPTION=ENCRYPTED_COLUMNS_ONLY
   ENCRYPTION_PASSWORD=PWD2encrypt
   

3. When importing data into the target database, you need to specify the same password. The password is used to decrypt the data. Data is reencrypted with the new column encryption keys generated in the target database. The target database must have the wallet open to access the master encryption key. The following example imports the employee_data table:

   impdp hr/password TABLES=employee_data DIRECTORY=dpump_dir DUMPFILE=dpcd2be1.dmp ENCRYPTION_PASSWORD=PWD2encrypt
   

Oracle Data Pump functionality has been enhanced in Oracle Database 11g Release 1 (11.1). You can encrypt entire dump sets, as opposed to encrypting just transparent data encryption columns. The ENCRYPTION_MODE parameter enables you to specify the encryption mode.

ENCRYPTION_MODE=DUAL encrypts the dump set using the master key stored in the wallet and the password provided. The following example uses dual encryption mode:

expdp hr/password DIRECTORY=dpump_dir1 DUMPFILE=hr_enc.dmp
ENCRYPTION=all ENCRYPTION_PASSWORD=PWD2encrypt
ENCRYPTION_ALGORITHM=AES256 ENCRYPTION_MODE=dual

While importing, you can use either the password or the wallet master key to decrypt the data. If the password is not supplied, then the master key in the wallet is used to decrypt the data. The wallet must be present, and open, at the target database. The open wallet is also required to reencrypt column encryption data at the target database.

You can use ENCRYPTION_MODE=TRANSPARENT to transparently encrypt the dump file set with the master encryption key stored in the wallet. A password is not required in this case. The wallet must be present, and open, at the target database, for successful decryption during import. The open wallet is also required to reencrypt column encryption data at the target database.

3.3.4 Performance and Storage Overheads

The overhead associated with transparent data encryption can be categorized into the following:

• Performance Overheads

• Storage Overheads

3.3.5 Security Considerations

Security considerations for transparent data encryption operate within the broader arena of total system security. As a security administrator, you must identify the levels of risk to be addressed and the degrees of sensitivity in data maintained by the site. Costs and benefits must be evaluated for the alternative methods of achieving acceptable protections. In many cases, it makes sense to have separate security administrators, separate wallets, and protected backup procedures for encrypted data. Having separate wallets permits auto-login for other Oracle components but preserves password protection for the transparent data encryption wallet.

Additional security considerations apply to normal database and network operations when using transparent data encryption. Encrypted column data stays encrypted in the data files, undo logs, redo logs, and the buffer cache of the system global area (SGA). However, data is decrypted during expression evaluation, making it possible for decrypted data to appear in the swap file on the disk. Privileged operating system users can potentially view this data.

Column values encrypted using transparent data encryption are stored in the data files in encrypted form. However, these data files may still contain some clear-text fragments, called ghost copies, left over by past data operations on the table. This is similar to finding data on the disk after a file has been deleted by the operating system.

Old clear-text fragments may be present for some time until the database overwrites the blocks containing such values. If privileged operating system users bypass the access controls of the database, they might be able to directly access these values in the data file holding the tablespace. You can use the following procedure to minimize this risk:

1. Create a new tablespace in a new data file. You can use the CREATE TABLESPACE statement.

2. Move the table containing encrypted columns to the new tablespace. You can use the ALTER TABLE.....MOVE statement. Repeat this step for all objects in the original tablespace.

3. Drop the original tablespace. You can use the DROP TABLESPACE tablespace INCLUDING CONTENTS KEEP DATAFILES statement. Oracle recommends that you securely delete data files using platform specific utilities.

4. Use platform and file system specific utilities to securely delete the old data file. Examples of such utilities include shred (on Linux) and sdelete (on Windows).

3.3.6 Replication in Distributed Environments

Oracle Data Guard supports transparent data encryption. If the primary database uses transparent data encryption, then each standby database in a Data Guard configuration must have a copy of the encryption wallet from the primary database. If you reset the master encryption key in the primary database, then the wallet containing the master encryption key needs to be copied to each standby database.

Transparent data encryption works with SQL*Loader direct path loads. The data loaded into encrypted columns is transparently encrypted during the direct path load.

You can encrypt columns in materialized views. However, materialized view logs cannot contain encrypted columns.

3.3.7 Transparent Data Encryption with OCI

Row shipping cannot be used, because the key to make the row usable is not available at the receipt-point.

3.3.8 Transparent Data Encryption Data Dictionary Views

The following data dictionary views maintain information about the encryption algorithms used to encrypt columns:

• DBA_ENCRYPTED_COLUMNS

• ALL_ENCRYPTED_COLUMNS

  Shows the algorithm used to encrypt columns for all tables that are accessible to a particular user.

• USER_ENCRYPTED_COLUMNS

  Shows the algorithm used to encrypt columns for all tables in a particular user's schema.

3.5.1 Set the Tablespace Master Encryption Key

Setting the tablespace master encryption key is a one-time activity. This creates the master encryption key for tablespace encryption. This key is stored in an external security module (Oracle wallet) and is used to encrypt the tablespace encryption keys.

Tablespace encrytion uses the same software wallet that is used by column-based transparent data encryption to store the master encryption key. Check to ensure that the ENCRYPTION_WALLET_LOCATION (or WALLET_LOCATION) parameter in the sqlnet.ora file points to the correct software wallet location. For example:

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=
   (DIRECTORY=/u01/app/oracle/product/11.1.0/db_1/)))

If you want to use tablespace encryption alongside features like hardware security modules (HSMs), then you must set the tablespace master encryption key before configuring the HSM. The tablespace master encryption key must be created before you create a master key in HSM mode. This is because the tablespace master encryption key cannot be created in the HSM.

When you create a master encryption key for transparent data encryption, a master encryption key for tablespace encryption also gets created. Creating a master encryption key is discussed under "Setting the Master Encryption Key" .

If you were already using transparent data encryption in Oracle Database 10g release 2 (10.2), and have upgraded the database and compatibility to 11g, then you need to reissue the ALTER SYSTEM SET ENCRYPTION KEY command to create a master encryption key for tablespace encryption.

When you issue the ALTER SYSTEM SET ENCRYPTION KEY command, it re-creates the standard transparent data encryption master key if one already exists, and creates a new tablespace master encryption key. If the tablespace master encryption key already exists, a new key is not created.

3.5.2 Open the Oracle Wallet

Before you can create an encrypted tablespace, the Oracle wallet containing the tablespace master encryption key must be open. The wallet must also be open before you can access data in an encrypted tablespace. Opening the Oracle wallet has been discussed under "Opening the Encrypted Wallet" .

When using Oracle Real Application Clusters (RAC) instances, the security administrator should ensure that the wallet is open for each instance. This is because any Oracle RAC instance might be required to perform encrypt and decrypt operations.

The security administrator also needs to open the wallet before performing database recovery operations. This is because background processes may require access to encrypted redo and undo logs. When performing database recovery, the wallet must be opened before opening the database. This is illustrated in the following statements:

SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY password;
SQL> ALTER DATABASE OPEN;

You can also choose to use auto login wallets, if your environment does not require the extra security provided by a wallet that needs to be explicitly opened. However, this is not the recommended practice.

3.5.3 Create an Encrypted Tablespace

The CREATE TABLESPACE command enables you to create an encrypted tablespace. The permanent_tablespace_clause enables you to choose the encryption algorithm and the key length for encryption. The ENCRYPT keyword in the storage_clause encrypts the tablespace. The following syntax illustrates this:

CREATE
   [ BIGFILE | SMALLFILE ]
   { permanent_tablespace_clause
   | temporary_tablespace_clause
   | undo_tablespace_clause
   } ;

Where,

permanent_tablespace_clause=
TABLESPACE tablespace
.........
ENCRYPTION [USING algorithm]
.........
storage_clause
.........

Where,

storage_clause=
.........
[ENCRYPT]
.........

Here:

algorithm can have one of the following values:

• 3DES168

• AES128

• AES192

• AES256

The key lengths are included in the names of the algorithms themselves. If no encryption algorithm is specified, the default encryption algorithm is used. The default encryption algorithm is AES128.

Example 3-14 creates a tablespace called securespace. The tablespace is encrypted using the 3DES algorithm. The key length is 168 bits.

CREATE TABLESPACE securespace
DATAFILE '/home/user/oradata/secure01.dbf'
SIZE 150M
ENCRYPTION USING '3DES168'
DEFAULT STORAGE(ENCRYPT);

Example 3-15 creates a tablespace called securespace2. As no encryption algorithm is specified, the default encryption algorithm (AES128) is used. The key length is 128 bits.

CREATE TABLESPACE securespace2
DATAFILE '/home/user/oradata/secure01.dbf'
SIZE 150M
ENCRYPTION
DEFAULT STORAGE(ENCRYPT);

The following data dictionary views maintain information about the encryption status of a tablespace. You can query these views to verify that a tablespace has been encrypted:

• DBA_TABLESPACES: The ENCRYPTED column indicates whether a tablespace is encrypted

• USER_TABLESPACES: The ENCRYPTED column indicates whether a tablespace is encrypted

You cannot encrypt an existing tablespace. However, you can import data into an encrypted tablespace using the Oracle Data Pump utility. You can also use SQL commands like CREATE TABLE...AS SELECT...or ALTER TABLE...MOVE... to move data into an encrypted tablespace. The CREATE TABLE...AS SELECT... command enables you to create a table from an existing table. The ALTER TABLE...MOVE... command enables you to move a table into the encrypted tablespace.

3.7.1 Set the ENCRYPTION_WALLET_LOCATION Parameter in the sqlnet.ora File

The ENCRYPTION_WALLET_LOCATION parameter specifies the location of the Oracle wallet. You need to change this parameter to reflect the fact that an HSM is to be used in place of the software wallet.

Use the following steps to set the ENCRYPTION_WALLET_LOCATION parameter:

1. Open the sqlnet.ora file. This file is located in the $ORACLE_HOME/network/admin directory.

2. Look for the ENCRYPTION_WALLET_LOCATION parameter. Change the METHOD value to HSM:

   ENCRYPTION_WALLET_LOCATION=
     (SOURCE=(METHOD=HSM)(METHOD_DATA=
       (DIRECTORY=/app/oracle/admin/SID1/wallet)))
   

   Note:

   If a DIRECTORY value is present in the ENCRYPTION_WALLET_LOCATION parameter, then make sure that you do not delete it. Although HSM does not require a DIRECTORY value, the value is used to locate your old software wallet when migrating to HSM-based transparent data encryption. Also, the DIRECTORY value might be required by tools, such as Recovery Manager (RMAN), to locate the software wallet.

   If the ENCRYPTION_WALLET_LOCATION parameter is not present in the sqlnet.ora file, then you need to add it. Add the following at the end of the sqlnet.ora file:

   ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=HSM))
   

3. Save and close the file.

3.7.2 Copy the PKCS#11 Library to Its Correct Path

Your HSM vendor supplies you with an associated PKCS#11 library. You should copy this library to the specified directory structure to ensure that the database is able to find this library. Use the following directory structures for UNIX and Windows respectively:

/opt/oracle/extapi/[32,64]/hsm/{VENDOR}/{VERSION}/libapiname.ext 

%SYSTEM_DRIVE%\oracle\extapi\[32,64]\hsm\{VENDOR}\{VERSION}\libapiname.ext

Here:

[32,64] specifies whether the supplied binary is 32-bits or 64-bits

VENDOR stands for the name of the vendor supplying the library

VERSION refers to the version of the library. This should preferably be in a format, number.number.number

apiname requires no special format. However, the apiname must be prefixed with the word lib, as illustrated in the syntax.

ext needs to be replaced by the extension of the library file.

3.7.3 Set Up the HSM

Your HSM vendor should have provided you the instructions to set up the HSM interface. Use your HSM management interface and the instructions provided by your vendor to set up the HSM. Create the user account and password that would be used by the database to interact with the HSM.

3.7.4 Generate a Master Encryption Key for HSM-Based Encryption

To start using HSM-based encryption, you need to create a master encryption key that will be stored inside the HSM. The master encryption key is used to encrypt or decrypt column encryption keys inside the HSM.

Use the following command to create the master encryption key:

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY user_Id:password [MIGRATE USING wallet_password]

Here:

user_Id is the user Id created for the database using the HSM management interface

password is the password created for the user Id using the HSM management interface. Enclose the user_Id:password string in double quotation marks (" ").

wallet_password is the password required to open an existing Oracle wallet on the file system. Enclose the wallet_password string in double quotation marks (" ").

If you are already using transparent data encryption and not using HSM, then you need to use the MIGRATE USING wallet_password clause in the preceding command. This decrypts the existing column encryption keys and reencrypts them with the newly created, HSM-based, master encryption key.

3.7.5 Reconfigure the Software Wallet (Optional)

This step is applicable if you want to use tablespace encryption along with the HSM. This step is also required if you have exported encrypted data or created encrypted backups using the software wallet.

Tablespace encryption uses the software wallet to access the tablespace master encryption key. In addition, tools like Oracle Data Pump and Recovery Manager require access to the old software wallet to perform decryption and encryption operations on data exported or backed up using the software wallet.

You can use either of the following approaches to reconfigure the software wallet:

• Change the wallet password to the HSM userId:password string. Here:

  user_Id is the user Id created for the database using the HSM management interface

  password is the password created for the user Id using the HSM management interface. Enclose the user_Id:password string in double quotation marks (" ").

  Use Oracle Wallet Manager to change the password for the software wallet.

  See Also:

  "Changing the Password" for more details on changing the wallet password

• You can alternatively choose to use an auto login wallet. Use an auto login wallet only if your environment does not require the extra security provided by a wallet that needs to be explicitly opened.

  See Also:

  "Using Auto Login" for information about enabling auto login using Oracle Wallet Manager

  The auto login wallet is identified by a file with the .sso extension. Remove the ewallet.p12 file from your wallet directory after configuring the auto login (.sso) wallet.

3.7.6 Ensure that the HSM Is Accessible

The security administrator must make sure that the HSM is accessible to the database before any encryption or decryption can be performed. This is analogous to opening the Oracle wallet. Use the following command to make the HSM accessible:

ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY user_Id:password

Here:

user_Id is the user Id created for the database using the HSM management interface

password is the password created for the user Id using the HSM management interface

Enclose the user_Id:password string in double quotation marks (" ")

The security administrator can disable access to the HSM using the ALTER SYSTEM SET WALLET CLOSE command. This disables all encryption and decryption operations in the HSM. A database user cannot perform any operation involving encrypted data until the wallet has been reopened. For example, the following operations will fail if the HSM is not accessible:

• SELECT data from an encrypted column

• INSERT data into on an encrypted column

• CREATE a table with encrypted column(s)

• ALTER the encryption properties of a column

3.7.7 Encrypt and Decrypt Data

HSM use is transparent to the end user. The commands to create a table with encrypted columns, access encrypted data, or decrypt data are the same regardless of whether the master encryption key resides in an Oracle wallet or HSM.

3.8.1 Prepare the Database for Transparent Data Encryption

In order to start using transparent data encryption, let us first prepare the database by specifying an Oracle wallet location and setting the master encryption key. The following steps prepare the database to use transparent data encryption:

1. Specify an Oracle Wallet Location in the sqlnet.ora File

2. Create the Master Encryption Key

3. Open the Oracle Wallet

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=
   (DIRECTORY=/u01/app/oracle/product/11.1.0/db_1/)))

SQL> ALTER SYSTEM SET KEY IDENTIFIED BY "welcome1";

ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "welcome1";

3.8.2 Create a Table with an Encrypted Column

We can now create tables with encrypted columns. Let us create a table called cust_payment_info. This table contains a column called credit_card_number. The credit_card_number column contains sensitive data, which we would like to encrypt. Use the following command to create the table:

CREATE TABLE cust_payment_info 
  (first_name VARCHAR2(11), 
  last_name VARCHAR2(10), 
  order_number NUMBER(5), 
  credit_card_number VARCHAR2(16) ENCRYPT NO SALT,
  active_card VARCHAR2(3));

The table is created in the default tablespace of the user that issues this command. The credit_card_number column is encrypted without SALT. All data entered for the credit_card_number column would be encrypted on disk. Any user with access to the credit_card_number data can view the decrypted data. A database user need not be aware if the contents of a particular column are encrypted on the disk.

You can now enter data into the table. The following example adds some sample data to the cust_payment_info table:

INSERT INTO cust_payment_info VALUES
  ('Jon', 'Oldfield', 10001, '5446959708812985','YES');
INSERT INTO cust_payment_info VALUES
  ('Chris', 'White', 10002, '5122358046082560','YES'); 
INSERT INTO cust_payment_info VALUES
  ('Alan', 'Squire', 10003, '5595968943757920','YES');
INSERT INTO cust_payment_info VALUES
  ('Mike', 'Anderson', 10004, '4929889576357400','YES');
INSERT INTO cust_payment_info VALUES
  ('Annie', 'Schmidt', 10005, '4556988708236902','YES');
INSERT INTO cust_payment_info VALUES
  ('Elliott', 'Meyer', 10006, '374366599711820','YES');
INSERT INTO cust_payment_info VALUES
  ('Celine', 'Smith', 10007, '4716898533036','YES');
INSERT INTO cust_payment_info VALUES
  ('Steve', 'Haslam', 10008, '340975900376858','YES');
INSERT INTO cust_payment_info VALUES
  ('Albert', 'Einstein', 10009, '310654305412389','YES');

All data entered into the credit_card_number column is stored on the disk in encrypted form.

3.8.3 Create an Index on an Encrypted Column

You can create an index on an encrypted column if it has been encrypted without salt. Let us create an index on the credit_card_number column. The following command creates an index on the credit_card_number column:

CREATE INDEX cust_payment_info_idx ON cust_payment_info (credit_card_number);

3.8.4 Alter a Table to Encrypt an Existing Column

You can use the ALTER TABLE command to alter an existing table. Let us alter a table called employees with no encrypted columns. The following command describes the employees table:

SQL> DESC employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FIRSTNAME                                          VARCHAR2(11)
 LASTNAME                                           VARCHAR2(10)
 EMP_SSN                                            VARCHAR2(9)
 DEPT                                               VARCHAR2(20)

The following command encrypts the emp_ssn column in the employees table:

SQL> ALTER TABLE employees MODIFY (emp_ssn VARCHAR2(9) ENCRYPT);

The following command describes the altered employees table:

SQL> DESC employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FIRSTNAME                                          VARCHAR2(11)
 LASTNAME                                           VARCHAR2(10)
 EMP_SSN                                            VARCHAR2(9) ENCRYPT
 DEPT                                               VARCHAR2(20)

All existing data in the emp_ssn column will now be encrypted on the disk. Data would be transparently decrypted for database users, who otherwise have access to the data.

3.8.5 Create an Encrypted Tablespace

Tablespace encryption enables you to encrypt an entire tablespace. All data stored in the tablespace is encrypted by default. Thus, if you create any table in an encrypted tablespace, it is encrypted by default. You do not need to perform a granular analysis of each table column to determine the columns that need encryption.

Let us create an encrypted tablespace to store encrypted tables. The following command creates an encrypted tablespace called securespace:

SQL> CREATE TABLESPACE securespace
  2  DATAFILE '/home/oracle/oracle3/product/11.1.0/db_1/secure01.dbf'
  3  SIZE 150M
  4  ENCRYPTION
  5  DEFAULT STORAGE(ENCRYPT);
Tablespace created.

3.8.6 Create a Table in an Encrypted Tablespace

If we create a table in an encrypted tablespace, then all data in the table is stored in encrypted form on the disk. The following command creates a table called, customer_info_payment in an encrypted tablespace called, securespace.

SQL> CREATE TABLE customer_payment_info
  2  (first_name VARCHAR2(11),
  3  last_name VARCHAR2(10),
  4  order_number NUMBER(5),
  5  credit_card_number VARCHAR2(16),
  6  active_card VARCHAR2(3))TABLESPACE securespace;
Table created.

3.10.1 Supported Encryption and Integrity Algorithms

By default, transparent data encryption uses the Advanced Encryption Standard with a 192-bit length cipher key (AES192). In addition, salt is added by default to cleartext before encryption unless specified otherwise. Note that salt cannot be added to indexed columns that you want to encrypt. For indexed columns, choose the NO SALT parameter for the SQL ENCRYPT clause.

You can change encryption algorithms and encryption keys on existing encrypted columns by setting a different algorithm with the SQL ENCRYPT clause.

Table 3-1 lists the supported encryption algorithms.

For integrity protection, the SHA-1 hashing algorithm is used.

3.10.2 Data Types That Can Be Encrypted with Transparent Data Encryption

The following data types can be encrypted using this feature:

• BINARY_DOUBLE

• BINARY_FLOAT

• CHAR

• DATE

• INTERVAL DAY TO SECOND

• INTERVAL YEAR TO MONTH

• NCHAR

• NUMBER

• NVARCHAR2

• RAW

• TIMESTAMP (includes TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE)

• VARCHAR2

You cannot encrypt a column if the encrypted column size becomes greater than the size allowed by the data type of the column. Table 3-2 shows the maximum allowable sizes for various data types.

3.10.3 Quick Reference: Transparent Data Encryption SQL Commands

Table 3-3 provides a summary of the SQL commands you can use to implement and manage transparent data encryption.