8 Securing Stored Data Using Transparent Data Encryption

8.1.2.1 TDE Column Encryption

TDE column encryption is used to protect confidential data, such as credit card and social security numbers, stored in table columns. TDE column encryption uses the two-tiered, key-based architecture to transparently encrypt and decrypt sensitive table columns. The TDE master encryption key is stored in an external security module, which can be an Oracle wallet or Hardware Security Module (HSM). This master encryption key is used to encrypt the table key, which in turn is used to encrypt and decrypt data in the table column.

Figure 8-1shows an overview of the TDE column encryption process.

Figure 8-1 TDE Column Encryption Overview

Description of ''Figure 8-1 TDE Column Encryption Overview''

As shown in Figure 8-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 or Hardware Security Module (HSM), as described in this chapter. Storing the master encryption key in this way prevents its unauthorized use.

Using an external security module (wallet/HSM) 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.

When a table contains encrypted columns, a single table key is used regardless of the number of encrypted columns. The table keys for all tables 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.

8.1.2.2 TDE Tablespace Encryption

TDE tablespace encryption enables you to encrypt an entire tablespace. All objects created in the encrypted tablespace are automatically encrypted. TDE tablespace encryption is useful if you want to secure sensitive data in tables. You do not need to perform a granular analysis of each table column to determine the columns that need encryption.

In addition, TDE tablespace encryption takes advantage of bulk encryption and caching to provide enhanced performance. While the actual performance impact on applications can vary, the performance overhead is roughly estimated to be in between 5% and 8%.

TDE tablespace encryption is a good alternative to TDE column encryption if your tables contain sensitive data in multiple columns, or if you want to protect the entire table and not just individual columns.

TDE tablespace encryption encrypts all data that is stored in an encrypted tablespace and its corresponding redo data. This includes internal large objects (LOBs) such as BLOBs and CLOBs. TDE tablespace encryption does not encrypt data that is stored outside the tablespace. For example, BFILE data is not encrypted as it is stored outside the database. If you create a table with a BFILE column in an encrypted tablespace, then this particular column will not be encrypted. However, SecureFile LOBs are supported from Oracle Database 11g Release 1 (11.1).

All data in an encrypted tablespace is stored in encrypted format on the disk. Data is transparently decrypted for an authorized user having the necessary privileges to view or modify the data. A database user or application does not need to know if the data in a particular table is encrypted on the disk. In the event that the data files on a disk or backup media gets stolen, the data is not compromised.

TDE tablespace encryption uses the two-tiered, key-based architecture to transparently encrypt (and decrypt) tablespaces. The TDE master key is stored in an external security module (Oracle Wallet or HSM). This TDE master key is used to encrypt the TDE tablespace encryption key, which in turn is used to encrypt and decrypt data in the tablespace.

Figure 8-2 shows an overview of the TDE tablespace encryption process.

Figure 8-2 TDE Tablespace Encryption

TDE tablespace encryption also allows index range scans on data in encrypted tablespaces. This is not possible with TDE column encryption.

Oracle Database 11g Release 2 (11.2) implements the following enhancements to TDE tablespace encryption:

• A unified master encryption key is used for both TDE column encryption and TDE tablespace encryption.

• You can reset the unified master encryption key. This provides enhanced security and helps meet security and compliance requirements.

8.2.1.1 Specifying a Wallet Location for Transparent Data Encryption

If you wish to use a wallet specifically for TDE, then you must specify a wallet location in the sqlnet.ora file by using the ENCRYPTION_WALLET_LOCATION parameter. Oracle recommends that you use the ENCRYPTION_WALLET_LOCATION parameter to specify a wallet location for TDE.

8.2.1.2 Using Wallets with Automatic Login Enabled

The external security module can use wallets with the automatic login feature enabled. These wallets remain open all of the time. The security administrator does not have to reopen the wallet after a database instance has been restarted. If your environment does not require the extra security provided by a wallet that must be explicitly opened for use, then you may use an auto login wallet.

You can also choose to create a local auto login wallet. Local auto login wallets cannot be moved to another computer. They must be used on the host on which they are created.

8.2.2.1 Setting the Master Encryption Key

Before you can encrypt or decrypt database columns or tablespaces, you must generate a master encryption key. Oracle Database 11g Release 2 (11.2) uses the same master encryption key for both TDE column encryption and TDE tablespace encryption.

To set the master encryption key, use the following command:

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

where

• certificate_ID is an optional string containing the unique identifier of a certificate stored in the Oracle wallet. Use this parameter if you intend to use your PKI private key as your master encryption key. This parameter has no default setting. Enclose the certificate_ID in double quotation marks (" ").

  You can search for a certificate_ID by querying the V$WALLET fixed view when the wallet is open. Only certificates that can be used as master encryption keys by TDE are shown.

• password is the mandatory wallet password for the security module, with no default setting. It is case sensitive. Enclose the password string in double quotation marks (" ").

The wallet location specified by the ENCRYPTION_WALLET_LOCATION parameter, in the sqlnet.ora parameter file, is used to create the master encryption key. If the ENCRYPTION_WALLET_LOCATION parameter is not present in the sqlnet.ora file, then the WALLET_LOCATION value is used. A new wallet is created if one does not exist already.

If no wallet location is specified in the sqlnet.ora file, then the default database wallet location is used. The default database wallet location is ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet or ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet. Here, DB_UNIQUE_NAME is the unique name of the database specified in the initialization parameter file.

If an existing auto login wallet is present at the expected wallet location, then a new wallet is not created.

8.2.2.2 Resetting the Master Encryption Key

Reset/Regenerate the master encryption key only if it has been compromised or as per the security policies of the organization. You should back up the wallet before resetting the master encryption key.

Frequent master encryption key regeneration does not necessarily enhance system security. Security modules can store a large number of keys. However, this number is not unlimited. Frequent master encryption key regeneration can exhaust all available storage space.

To reset the master encryption key, use the SQL syntax as shown in "Setting the Master Encryption Key".

The ALTER SYSTEM SET ENCRYPTION KEY command is a data definition language (DDL) command requiring the ALTER SYSTEM privilege, and it automatically commits any pending transactions. Example 8-1 shows a sample usage of this command.

SQL> ALTER SYSTEM SET ENCRYPTION KEY "j23lm781098dhb345sm" IDENTIFIED BY "password";

Here, j23lm781098dhb345sm is the certificate ID and password is the wallet password.

For PKI-based keys, certificate revocation lists are not enforced as enforcing certificate revocation may lead to losing access to all encrypted information in the database. However, you cannot use the same certificate to create the master key again.

8.2.4.1 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

• Using the NOMAC Parameter to Save Disk Space and Improve Performance

• 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 employee (
     first_name VARCHAR2(128),
     last_name VARCHAR2(128),
     empID NUMBER ENCRYPT 'NOMAC' NO SALT ,
     salary NUMBER(6)
);

SQL> ALTER TABLE EMPLOYEE REKEY USING '3DES168' 'SHA-1';

Table altered.
SQL> ALTER TABLE EMPLOYEE REKEY USING '3DES168' 'NOMAC';

Table altered.

CREATE TABLE emp_ext (
    first_name,
    ....
    ssn 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;

8.2.4.2 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

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

SQL> ALTER TABLE employee MODIFY (first_name ENCRYPT);

SQL> ALTER TABLE employee MODIFY (first_name ENCRYPT 'NOMAC');

SQL> ALTER TABLE employee MODIFY (first_name DECRYPT);

8.2.4.3 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 8-12 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);

8.2.4.4 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. This ensures that the same plaintext data does not always translate to the same encrypted text. Salt removes the one common method attackers use to steal data, namely, matching patterns of encrypted text. Adding salt requires an additional 16 bytes of storage, per encrypted data value.

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

SQL> ALTER TABLE employee MODIFY (first_name ENCRYPT SALT);

Example 8-14 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.

SQL> ALTER TABLE employee MODIFY (first_name ENCRYPT NO SALT);

8.2.4.5 Changing the Encryption Key or Algorithm for Tables with Encrypted Columns

Each table can have only one table key for its columns. You can regenerate the table key with the ALTER TABLE command. You can also choose to use a different encryption algorithm for the new table key.

Example 8-15 regenerates the table key for the employee table.

SQL> ALTER TABLE employee REKEY;

Example 8-16 regenerates the table key for the employee table using the 3DES168 algorithm.

SQL> ALTER TABLE employee REKEY USING '3DES168';

8.2.4.6 Data Types That Can Be Encrypted with TDE Column 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

• LOBs (Internal LOBs and SECUREFILE LOBs Only)

• 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 8-1 shows the maximum allowable sizes for various data types.

8.2.4.7 Restrictions on Using TDE Column Encryption

TDE column encryption encrypts and decrypts data at the SQL layer. Oracle Database utilities and features that bypass the SQL layer cannot leverage the services provided by TDE column encryption. Do not use TDE column encryption with the following database features:

• Index types other than B-tree

• Range scan search through an index

• External large objects (BFILE)

• Synchronous Change Data Capture

• Transportable Tablespaces

• Original import/export utilities

In addition, you cannot use TDE column encryption to encrypt columns used in foreign key constraints.

Applications that need to use these unsupported features can use the DBMS_CRYPTO package for their encryption needs.

TDE protects data stored on disk/media. It does not protect data in transit. Use Oracle Advanced Security network encryption solutions discussed in Chapter 2, "Configuration and Administration Tools Overview"to encrypt data over the network.

8.2.5.1 Setting the Tablespace Master Encryption Key

Before you can encrypt or decrypt tablespaces, you must generate or set a master encryption key. The tablespace master encryption key is stored in an external security module and is used to encrypt the TDE tablespace encryption keys.

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=/app/wallet)))

Oracle Database 11g Release 2 (11.2) uses the same master encryption key for both TDE column encryption and TDE tablespace encryption. When you issue the ALTER SYSTEM SET ENCRYPTION KEY command, a unified master encryption key is created for both TDE column encryption and TDE tablespace encryption. Creating a master encryption key is discussed under "Setting the Master Encryption Key" .

If you were already using TDE in Oracle Database 10g release 2 (10.2), and have upgraded the database to 11g Release 2 (11.2), then you must reissue the ALTER SYSTEM SET ENCRYPTION KEY command to create a unified master encryption key.

If you were already using TDE tablespace encryption in Oracle Database 11g release 1 (11.1), and have upgraded the database to 11g release 2 (11.2), then you have separate master encryption keys for TDE column encryption and TDE tablespace encryption. You must create a unified master encryption key by reissuing the ALTER SYSTEM SET ENCRYPTION KEY command.

8.2.5.2 Opening 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 and Closing the Encrypted Wallet" .

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 WALLET OPEN 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.

8.2.5.3 Creating 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 8-17 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 8-18 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.

8.2.5.4 Restrictions on Using TDE Tablespace Encryption

TDE tablespace encryption encrypts/decrypts data during read/write operations, as opposed to TDE column encryption, which encrypts/decrypts data at the SQL layer. This means that most restrictions that apply to TDE column encryption, such as data type restrictions and index type restrictions, are not applicable to TDE tablespace encryption.

The following list includes the restrictions that apply to TDE tablespace encryption:

• External Large Objects (BFILEs) cannot be encrypted using TDE tablespace encryption. This is because these files reside outside the database.

• To perform import and export operations, use Oracle Data Pump.

8.2.6.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. Add the ENCRYPTION_WALLET_LOCATION parameter to the sqlnet.ora file, as follows:

   ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=HSM))
   

   If the ENCRYPTION_WALLET_LOCATION parameter is already present in the sqlnet.ora file, then change the METHOD value to HSM:

   ENCRYPTION_WALLET_LOCATION=
     (SOURCE=(METHOD=HSM)(METHOD_DATA=
       (DIRECTORY=/app/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.

3. Save and close the file.

8.2.6.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. This extension is .so on Unix.

8.2.6.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.

8.2.6.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 table keys inside the HSM.

Use the following command to create the master encryption key:

SQL> 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 table keys and reencrypts them with the newly created, HSM-based, master encryption key.

8.2.6.5 Reconfigure the Software Wallet (Optional)

This step is applicable if you have exported encrypted data or created encrypted backups using the software wallet. 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 or the orapki command-line utility to change the password for the software wallet. SQL*Plus cannot be used to change the wallet password.

  See Also:

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

• You can alternatively choose to use an auto login wallet. The auto login wallet is identified by a file with the .sso extension. 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.

  You can also choose to create a local auto login wallet. Local auto login wallets cannot be moved to another computer. They must be used on the host on which they are created.

  See Also:

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

  • "Creating, Viewing, and Modifying Wallets with orapki" for information about enabling auto login and local auto login using the orapki command-line utility

8.2.6.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:

SQL> ALTER SYSTEM SET ENCRYPTION 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 ENCRYPTION WALLET CLOSE IDENTIFIED BY "user_Id:password" command. This disables all encryption and decryption operations in the HSM. A database user or application 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

• CREATE an encrypted tablespace

8.2.6.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.

8.2.7.1 Using a Non-Shared File System to Store the Wallet

If you are not using a shared file system to store the wallet, then you need to copy the wallet to all nodes after a master key rekey. If you need to reset the master encryption key for the database, then use the following steps:

1. Reset the master encryption key on the first Oracle RAC node. Use the following command: See "Setting and Resetting the Master Encryption Key" for more information.

2. Copy the wallet with the new master encryption key from the first node to all other nodes.

3. Close and reopen the wallet on any one node. Use the following commands:

   SQL> ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY "password";
   SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password";
   

   Note:

   Any wallet operation, like opening or closing the wallet, performed on any one Oracle RAC instance is applicable for all other Oracle RAC instances. This is true even if you are not using a shared file system.

   All Oracle RAC nodes are now configured to use the new master encryption key.

8.3.1.1 Specifying a Separate Wallet for Transparent Data Encryption

When determining which wallet to use, TDE first attempts to use the wallet specified by the parameter ENCRYPTION_WALLET_LOCATION. If the parameter is not set, then it attempts to use the wallet specified by the parameter WALLET_LOCATION. If this fails as well, then TDE looks for a wallet at the default database location.

Oracle strongly recommends that you use a separate wallet for storing master encryption keys used by TDE. To designate a separate wallet, set the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file to point to the wallet used exclusively by TDE.

8.3.1.2 Using an Auto Login Wallet

You can create an auto login wallet with Oracle Wallet Manager or the orapki command-line utility. The auto login wallet allows convenient access to encrypted data across database instance restarts.

TDE uses an auto login wallet only if it is available at the correct location (ENCRYPTION_WALLET_LOCATION, WALLET_LOCATION, or default wallet location), and the SQL command to open an encrypted wallet has not already been executed. If an auto login wallet is being used, you must not use the ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password" command.

8.3.1.3 Creating Wallets

When you create the master encryption key using the ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "password" command, TDE checks to see if a wallet exists in the default or specified location. If no wallet exists, then a wallet is created automatically.

In addition to the SQL command, you can also use Oracle Wallet Manager to create wallets. Oracle Wallet Manager is a full-featured tool that allows you to create wallets and to view and modify their content.

You can also use the orapki command like utility to create wallets.

8.3.2.1 Backup and Recovery of Oracle Wallet

You cannot access any encrypted data without the master encryption key. As the master encryption key is stored in the Oracle wallet, the wallet should be periodically backed up in a secure location. You must back up a copy of the wallet whenever a new master encryption key is set.

The Oracle wallet should not be backed up with the encrypted data. The wallet should be backed up separately. This is especially true when using the auto login wallet, which does not require a password to open. In case the backup tape gets lost, a malicious user should not be able to get both the encrypted data and the wallet.

Recovery Manager (RMAN) does not back up the wallet as part of the database backup. When using a media manager like Oracle Secure Backup (OSB) with RMAN, OSB automatically excludes auto-open wallets (the cwallet.sso files). However, encryption wallets (the ewallet.p12 files) are not excluded automatically. It is a good practice to add the following exclude dataset statement to your OSB configuration:

exclude name *.p12

This instructs OSB to exclude the encryption wallet from the backup set.

If you lose the wallet that stores the master encryption key, you can restore access to encrypted data by copying the backed-up version of the wallet to the appropriate location. If the restored wallet was archived after the last time that the master encryption key was reset, then no additional action needs to be taken.

If the restored wallet does not contain the most recent master encryption key, then you can recover old data up to the point when the master encryption key was reset by rolling back the state of the database to that point in time. All modifications to encrypted columns after the master encryption key was reset are lost.

8.3.2.2 Backup and Recovery of PKI Key Pair

TDE column encryption supports the use of PKI asymmetric key pairs as master encryption keys. This enables it to leverage existing key backup, escrow, and recovery facilities from leading certificate authority vendors.

In current key escrow or recovery systems, the certificate authority with key recovery capabilities typically stores a version of the private key, or a piece of information that helps recover the private key. If the private key is lost, the user can recover the original key and certificate by contacting the certificate authority and initiating a key recovery process.

Typically, the key recovery process is automated and requires the user to present certain authenticating credentials to the certificate authority. TDE puts no restrictions on the key recovery process other than that the recovered key and its associated certificate be a PKCS#12 file that can be imported into an Oracle wallet. This requirement is consistent with the key recovery mechanisms of leading certificate authorities.

After obtaining the PKCS#12 file with the original certificate and private key, you need to create a new empty wallet in the same location as the previous wallet. To do this, you can use Oracle Wallet Manager. You can then import the PKCS#12 file into the wallet by using the same utility. You should choose a strong password to protect the wallet.

After the wallet has been created and the correct certificates imported, log onto the database and execute the following command at the SQL prompt to complete the recovery process:

SQL> ALTER SYSTEM SET ENCRYPTION KEY "certificate_id" IDENTIFIED BY "wallet_password"

To retrieve the certificate_id of the certificate in the wallet, query the V$WALLET fixed view after the wallet has been opened.

8.3.4.1 Performance Overheads

TDE tablespace encryption has small associated overheads. While the actual performance impact on applications can vary, it is roughly estimated to be in between 5% and 8%.

TDE column encryption affects performance only when data is retrieved from or inserted into an encrypted column. No reduction in performance occurs for operations involving unencrypted columns, even if these columns are in a table containing encrypted columns.

Accessing data in encrypted columns involves small overheads. The overhead associated with encrypting or decrypting a common attribute, such as credit card number, is estimated to be around 5%. This means that a SELECT operation (involves decryption) or an INSERT operation (involves encryption) would take roughly 5% more time than what it takes with clear text data.

The total performance overhead depends on the number of encrypted columns and their frequency of access. The columns most appropriate for encryption are those containing the most sensitive data.

Enabling encryption on an existing table results in a full table update like any other ALTER TABLE operation that modifies table characteristics. Administrators should keep in mind the potential performance and redo log impact on the database server before enabling encryption on a large existing table.

A table can temporarily become inaccessible for write operations while encryption is being enabled, table keys are being rekeyed, or the encryption algorithm is being changed. You can use online table redefinition to ensure that the table is available for write operations during such procedures.

If TDE column encryption is being enabled on a very large table, then the redo log size might need to be increased to accommodate the operation.

It has also been observed that encrypting an indexed column takes more time than encrypting a column without indexes. If you need to encrypt a column that has an index built on it, you can try dropping the index, encrypting the column with NO SALT, and then re-creating the index.

If you index an encrypted column, then the index is created on the encrypted values. When you query for a value in the encrypted column, Oracle transparently encrypts the value used in the SQL query. It then performs an index lookup using the encrypted value.

8.3.4.2 Storage Overheads

TDE tablespace encryption has no storage overheads. However, TDE column encryption has some associated storage overheads. Encrypted column data needs more storage space than clear text data. In addition, TDE pads out encrypted values to multiples of 16 bytes. This means that if a credit card number requires 9 bytes for storage, then an encrypted credit card value will require an additional 7 bytes.

Each encrypted value is also associated with a 20-byte integrity check. This is not applicable if you have encrypted columns using the NOMAC parameter. Also, if data has been encrypted with salt, then each encrypted value requires an additional 16 bytes of storage.

The maximum storage overhead for each encrypted value is 52 bytes.

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

Open the sqlnet.ora file located in $ORACLE_HOME/network/admin. Enter the following line at the end of the file:

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=(METHOD=FILE)(METHOD_DATA=
   (DIRECTORY=/app/wallet)))

Save the changes and close the file.

8.4.1.2 Create the Master Encryption Key

Next, we need to create the master encryption key, which is used to encrypt the table keys. Enter the following commands to create the master encryption key:

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "Easy2rem";

The preceding command achieves the following:

• If no encrypted wallet is present in the directory specified, an encrypted wallet is created (ewallet.p12), the wallet is opened, and the master encryption key for TDE is created/re-created.

• If an encrypted wallet is present in the directory specified, the wallet is opened, and the master encryption key for TDE is created/re-created.

8.4.1.3 Open the Oracle Wallet

Every time the database is shut down, the Oracle wallet is closed. You can also explicitly close the wallet.

You need to make sure that the Oracle wallet is open before you can perform any encryption or decryption operation. Use the following command to open the wallet containing the master encryption key:

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "Easy2rem";