Skip Headers
Oracle® Database Advanced Security Administrator's Guide
11g Release 1 (11.1)

B28530-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

3 Securing Stored Data Using Transparent Data Encryption

Transparent data encryption enables you to encrypt sensitive data, such as credit card numbers, stored in table columns. Encrypted data is transparently decrypted for a database user who has access to the data. Transparent data encryption helps protect data stored on media in the event that the storage media or data file gets stolen.

This chapter is divided into the following topics:

3.1 About Transparent Data Encryption

Oracle Database 11g uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system data files where data is stored. To protect these data files, Oracle Database provides transparent data encryption. Transparent data encryption encrypts sensitive table data stored in data files. To prevent unauthorized decryption, transparent data encryption stores the encryption keys in a security module external to the database.

This section contains the following topics:

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.2.1 Restrictions on Using Transparent Data Encryption

You cannot use transparent data encryption to encrypt columns used in foreign key constraints. This is because every table has a unique column encryption key.

Transparent data 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 transparent data encryption. Do not use transparent data encryption with the following database features:

  • Index types other than B-tree

  • Range scan search through an index

  • External large objects (BFILE)

  • Materialized View Logs

  • Synchronous Change Data Capture

  • Transportable Tablespaces

  • Original import/export utilities

Note:

Oracle Database 10g release 2 (10.2) did not support large object (LOB) data types such as BLOB and CLOB. Oracle Database 11g supports internal large object data types such as BLOB and CLOB. However, you cannot encrypt external LOBs (BFILE).

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

Transparent data encryption 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.

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

This illustration is described in the text.

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 Using Transparent Data Encryption

The following steps discuss using transparent data encryption:

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.

Note:

Oracle Database 11g Release 1 (11.1) ensures higher security by protecting data in temporary tablespaces during operations such as JOIN and SORT. The data in temporary tablespaces stays encrypted during these operations.

If you were already using transparent data encryption in Oracle Database 10g release 2 (10.2), and you have upgraded to Oracle Database 11g Release 1 (11.1), then Oracle recommends that you set the compatibility for your database to 11.0.0 and reset the master key. This enables the enhanced security features provided by Oracle Database 11g Release 1 (11.1) transparent data encryption. It also creates a tablespace master encryption key. Refer to "Setting and Resetting the Master Encryption Key" for details on resetting the master encryption key.

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.1.1 Specifying a Wallet Location for Transparent Data Encryption

The external security module stores encryption keys in the Oracle wallet specified in the sqlnet.ora configuration file. The WALLET_LOCATION parameter is used to specify the Oracle wallet location in the sqlnet.ora file.

If you wish to use a wallet specifically for transparent data encryption, then you must specify a second 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 transparent data encyrption.

See Also:

"Sample sqlnet.ora File"for an example of the syntax used to set this parameter

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

See Also:

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

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.

3.2.2.1 Setting the Master Encryption Key

Before you can encrypt or decrypt database columns, you must generate or set a master encryption key. The master encryption key is used to encrypt the column encryption keys, which are used to encrypt table columns.

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.

    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 transparent data encryption 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 (" ").

See Also:

Oracle Database SQL Reference for the rules related to supplying passwords

If no wallet exists, then this command creates a new one at the wallet location specified in the sqlnet.ora parameter file. If no wallet location is specified in the sqlnet.ora file, then the default database wallet location is used. If an existing auto login wallet is present at the expected wallet location, then a new wallet is not created.

3.2.2.2 Resetting the Master Encryption Key

Reset/Regenerate the master encryption key only if it has been compromised. 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 3-1 shows a sample usage of this command.

Example 3-1 Setting or Resetting the Master Encryption Key To Use a PKI-Based Private Key

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

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.

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 (" ").

Note:

The password to open the wallet is the password that you specify for creating the master encryption key. This is discussed under "Setting the Master Encryption Key" .

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.

Example 3-2 Opening the External Security Module Wallet with ALTER SYSTEM

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:

3.2.4.1 Creating a Table with an Encrypted Column

By default, transparent data encryption uses the AES encryption algorithm with a 192-bit key length (AES192). If you encrypt a table column without specifying an algorithm, the column will be encrypted using the AES192 algorithm.

Example 3-3 creates a new table with an encrypted column. The column is encrypted using the default algorithm (AES192).

Example 3-3 Creating a New Table with an Encrypted Column Using the Default Algorithm (AES192)

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

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

By default, transparent data encryption adds salt to cleartext before encrypting it. This makes it harder for attackers to steal data through a brute force attack. However, if you plan to index the encrypted column, you must use NO SALT.

Transparent data encryption also enables you to specify a nondefault encryption algorithm. You can choose from one of the following algorithms:

  • 3DES168

  • AES128

  • AES192 (default)

  • AES256

Example 3-4 shows how to specify the NO SALT parameter with the SQL ENCRYPT clause (empID NUMBER ENCRYPT NO SALT). It also shows the syntax for specifying a different encryption algorithm (salary NUMBER(6) ENCRYPT USING '3DES168'). Note that the string which specifies the algorithm must be enclosed in single quotation marks (' ').

Example 3-4 Creating a New Table with an Encrypted Column Using 3DES168 and NO SALT

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

3.2.4.3 Creating an Encrypted Column in an External Table

The external table feature enables you to access data in external sources as if the data were in a database table. External tables can be updated using the ORACLE_DATAPUMP access driver.

See Also:

Oracle Database Concepts for discussions on Schema Objects and Tables.

To encrypt specific columns in an external table, use the ENCRYPT clause when defining those columns. A randomly generated key is used to encrypt the columns.

If you plan to move your external table to a new location, then you cannot use a randomly generated key to encrypt the columns. This is because the randomly generated key will not be available at the new location.

For such scenarios, you should specify a password while encrypting the columns. After you move the data, you can use the same password to regenerate the key required to access encrypted column data at the new location.

Table partition exchange also requires a password-based column encryption key.

Example 3-5 creates an external table using a password to create the column encryption key.

Example 3-5 Creating a New External Table with a Password-Generated Column Encryption Key

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;

See Also:

Oracle Database SQL Reference about CREATE TABLE, ENCRYPT, and the rules for passwords.

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:

3.2.5.1 Adding an Encrypted Column to an Existing Table

To add an encrypted column to an existing table, you use the ALTER TABLE ADD command, specifying the new column with the ENCRYPT clause. Example 3-6 adds an encrypted column, ssn, to an existing table, called employee.

Example 3-6 Adding an Encrypted Column to an Existing Table

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

The ssn column is encrypted with the default AES192 algorithm. Salt is added to the data, by default.

You can choose to encrypt the column using a different algorithm. You can also specify NO SALT, if you wish to index the column.

3.2.5.2 Encrypting an Unencrypted Column

To encrypt an unencrypted column, use the ALTER TABLE MODIFY command, specifying the unencrypted column with the ENCRYPT clause. Example 3-7 encrypts the first_name column in the employee table.

Example 3-7 Encrypting an Unencrypted Column

ALTER TABLE employee MODIFY (first_name ENCRYPT);

The first_name column is encrypted with the default AES192 algorithm. Salt is added to the data, by default.

You can choose to encrypt the column using a different algorithm. You can also specify NO SALT, if you wish to index the column.

3.2.5.3 Disabling Encryption on a Column

You may want to disable encryption for reasons of compatibility or performance. To disable column encryption, use the ALTER TABLE MODIFY command with the DECRYPT clause. Example 3-8 decrypts the first_name column in the employee table.

Example 3-8 Turning Off Column Encryption

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.

Example 3-9 Creating Index on a Column 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);

Note:

You cannot create an index on a column that has been encrypted with salt. If you try to do this, an error (ORA-28338) is raised.

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 removes the one common 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.

Example 3-10 Adding Salt to an Encrypted Column

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.

Example 3-11 Removing Salt from an Encrypted Column

ALTER TABLE employee MODIFY (first_name ENCRYPT NO SALT);

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

The NOMAC parameter enables you to skip the integrity check performed by transparent data encryption. This saves 20 bytes of disk space per encrypted value. If the number of rows and encrypted columns in the table is large, then this adds up to a significant amount of disk space.

The NOMAC parameter also reduces the performance overheads associated with transparent data encryption. Using the NOMAC parameter causes the integrity check to be skipped during encryption and decryption operations. This saves processing cycles and leads to faster performance.

Note:

Transparent data encryption uses the SHA-1 integrity algorithm by default. All encrypted columns in a table must use the same integrity algorithm. If you already have a table column using the SHA-1 algorithm, then you cannot use the NOMAC parameter to encrypt another column in the same table.

If you wish to skip integrity checks for a table, then you must use the NOMAC parameter when encrypting a column in the table for the first time. All subsequent columns that you encrypt in the table implicitly use the NOMAC parameter.

Example 3-12 creates a table with an encrypted column. The empID column is encrypted using the NOMAC parameter.

Example 3-12 Using the NOMAC parameter in a CREATE TABLE statement

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

Example 3-13 encrypts the salary column in the employee table using the NOMAC parameter.

Example 3-13 Using the NOMAC parameter in an ALTER TABLE statement

ALTER TABLE employee MODIFY (salary ENCRYPT 'NOMAC');

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-14 regenerates the column encryption key for the employee table.

Example 3-14 Changing the Encryption Key on Tables Containing Encrypted Columns

ALTER TABLE employee REKEY;

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

Example 3-15 Changing the Encryption Key and Algorithm on Tables Containing Encrypted Columns

ALTER TABLE employee REKEY USING '3DES168';

3.3 Managing Transparent Data Encryption

This section contains these topics:

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.1.1 Specifying a Separate Wallet for Transparent Data Encryption

By default, transparent data encryption uses the default database wallet or the wallet specified by the WALLET_LOCATION parameter in the sqlnet.ora configuration file. However, as this wallet can be shared by other Oracle components, Oracle recommends that you use a separate wallet for storing master encryption keys used by transparent data encryption. To designate a separate wallet, set the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file to point to the wallet used exclusively by transparent data encryption.

See Also:

"Sample sqlnet.ora File"for an example of the syntax used to set this parameter

When determining which wallet to use, transparent data encryption 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 transparent data encryption looks for a wallet at the default database location.

3.3.1.2 Using an Auto Login Wallet

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

Transparent data encryption uses an auto login wallet only if it is available at the correct 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.

See Also:

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

3.3.1.3 Creating Wallets

When you create the master encryption key using the ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY password command, transparent data encryption 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 the mkwallet command-line utility and Oracle Wallet Manager to create wallets. These are full-featured tools that allow you to create wallets and to view and modify their content.

See Also:

Chapter 9, "Using Oracle Wallet Manager" for more information about Oracle Wallet Manager

3.3.1.4 Using Transparent Data Encryption in a Multi-Database Environment

If there are multiple Oracle databases installed on the same server (for example, databases sharing the same Oracle binary but using different data files), then each database must access its own Transparent Data Encryption wallet. Wallets are not designed to be shared between databases. By design, there must be one wallet per database. You cannot use the same wallet for more than one database.

To configure the sqlnet.ora file for a multi-database environment, use one of the following options:

  1. If the databases share the same Oracle home, then keep the sqlnet.ora file in the default location, which is in the ORACLE_HOME/network/admin directory.

    In this case, it is ideal to use the default location. Ensure that the sqlnet.ora file has no WALLET_LOCATION or ENCRYPTION_WALLET_LOCATION entries. Transparent Data Encryption accesses the wallet from the default sqlnet.ora location if these two entries are not in the sqlnet.ora file.

  2. If Option 1 is not feasible for your site, then you can specify the wallet location based on an environment variable setting, such as ORACLE_SID. For example:

    ENCRYPTION_WALLET_LOCATION =
     (SOURCE =
      (METHOD = FILE)
       (METHOD_DATA =
        (DIRECTORY = /home/oracle/wallet/$ORACLE_SID)
    
  3. If Options 1 and 2 are not feasible, then use separate sqlnet.ora files, one for each database. Ensure that you have correctly set the TNS_ADMIN environment variable to point to the correct database configuration. See SQL*Plus User's Guide and Reference for more information and examples of setting the TNS_ADMIN variable.

Caution:

Using a wallet from another database can cause partial or complete data loss.

3.3.2 Backup and Recovery of Master Encryption Keys

This section contains the following topics:

3.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 do 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 loose 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.

3.3.2.2 Backup and Recovery of PKI Key Pair

Transparent data 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. Transparent data encryption 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 the mkwallet command line utility or 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:

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.

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 TABLES=employee_data DIRECTORY=dpump_dir
    DUMPFILE=dpcd2be1.dmp ENCRYPTION=ENCRYPTED_COLUMNS_ONLY
    ENCRYPTION_PASSWORD=PWD2encrypt
    
    Password: password_for_hr
    
  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 TABLES=employee_data DIRECTORY=dpump_dir DUMPFILE=dpcd2be1.dmp ENCRYPTION_PASSWORD=PWD2encrypt
    
    Password: password_for_hr
    

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 DIRECTORY=dpump_dir1 DUMPFILE=hr_enc.dmp
ENCRYPTION=all ENCRYPTION_PASSWORD=PWD2encrypt
ENCRYPTION_ALGORITHM=AES256 ENCRYPTION_MODE=dual

Password: password_for_hr

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.

See Also:

3.3.4 Performance and Storage Overheads

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

3.3.4.1 Performance Overheads

Transparent data 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, column 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 transparent data 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, 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.

3.3.4.2 Storage Overheads

Encrypted data needs more storage space than clear text data. Each encrypted value is associated with a 20 byte integrity check. In addition, transparent data encryption pads out encrypted value to 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. Also, if data has been encrypted with salt, then each encrypted value requires an additional 16 bytes of storage.

To summarize, encrypting a single column would require between 32 and 48 bytes of additional storage for each row, on average.

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.

See Also:

Appendix C in the Oracle Data Guard Concepts and Administration Guide for more information about the use of transparent data encryption with logical standby databases

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.

See Also:

"Materialized View Concepts and Architecture" in the Oracle Database Advanced Replication Guide for more information on materialized views

3.3.7 Compression and Data Deduplication of Encrypted Data

After you have encrypted data, you cannot compress it. Any compression that you need to perform must take place before you encrypt the data. In Transparent Data Encryption column encryption, because much less data is encrypted, the post-encryption compression functions are not affected as much as with Transparent Data Encryption tablespace encryption.

You can use the following solutions to handle compression and data deduplication of encrypted data:

  • To compress database tables before the encryption takes place, use the Advanced Compression Option of Oracle Recovery Manager.

  • If you have many copies of very similar or even identical data stored on a single disk, then consider using storage-based data deduplication. To use data deduplication, use the Oracle SecureFiles LOB deduplication functionality on individual tables.

3.3.8 Transparent Data Encryption with Oracle Call Interface

Row shipping cannot be used with Oracle Call Interface (OCI), because the key to make the row usable is not available at the receipt-point.

3.3.9 Transparent Data Encryption in a Multi-Database Environment

If there are multiple Oracle databases installed on the same server (for example, databases sharing the same Oracle binary but using different data files), then each database must access its own Transparent Data Encryption keystore. Wallets are not designed to be shared between databases. By design, there must be one wallet per database. You cannot use the same wallet for more than one database.

To configure the sqlnet.ora file for a multi-database environment, use one of the following options:

  1. If the databases share the same Oracle home, then keep the sqlnet.ora file in the default location, which is in the ORACLE_HOME/network/admin directory.

    In this case, it is ideal to use the default location. Ensure that the sqlnet.ora file has no WALLET_LOCATION or ENCRYPTION_WALLET_LOCATION entries. Transparent Data Encryption accesses the wallet from the default sqlnet.ora location if these two entries are not in the sqlnet.ora file.

  2. If Option 1 is not feasible for your site, then you can specify the wallet location based on an environment variable setting, such as ORACLE_SID. For example:

    ENCRYPTION_WALLET_LOCATION =
     (SOURCE =
      (METHOD = FILE)
       (METHOD_DATA =
        (DIRECTORY = /home/oracle/wallet/$ORACLE_SID)
    
  3. If Options 1 and 2 are not feasible, then use separate sqlnet.ora files, one for each database. Ensure that the TNS_ADMIN environment variable is correctly set to point to the correct database configuration. See SQL*Plus User's Guide and Reference for more information and examples of setting the TNS_ADMIN variable.

Caution:

Using a keystore from another database can cause partial or complete data loss.

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

See Also:

Oracle Database Reference for a full description of these data dictionary views.

3.4 About Tablespace Encryption

Tablespace encryption enables you to encrypt an entire tablespace. All objects created in the encrypted tablespace are automatically encrypted. 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.

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

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

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

Tablespace encryption uses the transparent data encryption architecture to transparently encrypt (and decrypt) tablespaces. The tablespace encryption master key is stored in the same Oracle wallet that is used to store the transparent data encryption master key. This tablespace encryption master key is used to encrypt the tablespace encryption key, which in turn is used to encrypt and decrypt data in the tablespace.

The encrypted data is protected during operations like JOIN and SORT. This means that the data is safe when it is moved to temporary tablespaces. Data in undo and redo logs is also protected.

Tablespace encryption also allows index range scans on data in encrypted tablespaces. This is not possible with column-based transparent data encryption.

3.5 Using Tablespace Encryption

In order to use tablespace encryption, you must be running Oracle Database 11g release 1 (11.1). If you have upgraded from an earlier release, the compatibility for the database must have been set to 11.0.0 or higher.

Note:

Advancing the database compatibility, using the COMPATIBLE initialization parameter, is an irreversible change.

Use the following steps to implement tablespace encryption:

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/)))

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.

Note:

You cannot re-create (REKEY) tablespace encryption keys.

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

Note:

The security administrator needs to open the Oracle wallet after starting the Oracle instance. A restart of the Oracle instance requires the security administrator to open the wallet again.

When using Oracle Real Application Clusters (Oracle 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. .

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.

Note:

  • The ENCRYPTION keyword in the permanent_tablespace_clause is used to specify the encryption algorithm. The ENCRYPT keyword in the storage_clause actually encrypts the tablespace.

  • For security reasons, a tablespace cannot be encrypted with the NO SALT option.

See Also:

Oracle Database SQL Reference Guide for the CREATE TABLESPACE command syntax.

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

Example 3-16 Creating an Encrypted Tablespace

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

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

Example 3-17 Creating an Encrypted Tablespace

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

See Also:

Oracle Database Reference for a full description of these data dictionary views.

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.

See Also:

Oracle Database SQL Reference for more details on the CREATE TABLE and ALTER TABLE commands.

3.6 About Hardware Security Modules

A hardware security module (HSM) is a physical device that provides secure storage for encryption keys. It also provides secure computational space (memory) to perform encryption and decryption operations. HSM is a more secure alternative to the Oracle wallet.

Transparent data encryption can use HSM to provide enhanced security for sensitive data. An HSM is used to store the master encryption key used for transparent data encryption. The key is secure from unauthorized access attempts as the HSM is a physical device and not an operating system file. All encryption and decryption operations that use the master encryption key are performed inside the HSM. This means that the master encryption key is never exposed in insecure memory.

3.7 Using Hardware Security Modules

Using HSM involves an initial setup of the HSM device. You also need to configure transparent data encryption to use HSM. Once the initial setup is done, HSM can be used just like an Oracle software wallet. The following steps discuss configuring and using hardware security modules:

  1. Set the ENCRYPTION_WALLET_LOCATION Parameter in the sqlnet.ora File

  2. Copy the PKCS#11 Library to Its Correct Path

  3. Set Up the HSM

  4. Generate a Master Encryption Key for HSM-Based Encryption

  5. Reconfigure the Software Wallet (Optional)

  6. Ensure that the HSM Is Accessible

  7. Encrypt and Decrypt Data

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.

Note:

Only one PKCS#11 library is supported at a time. If you wish to use an HSM from a new vendor, then you should replace the PKCS#11 library from the earlier vendor with the library from the new vendor.

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.

Note:

The HSM is set up by the security administrator who is responsible for managing transparent data encryption.

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 (" ").

Note:

The user_Id and password are not created automatically. You must set these up using the HSM management interface before issuing the ALTER SYSTEM SET ENCRYPTION KEY command. This is different from the procedure used for an Oracle wallet. An Oracle wallet requires no prior setup before issuing the ALTER SYSTEM SET ENCRYPTION KEY command.

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.

Note:

If the database contains columns encrypted with a public key, then the columns are decrypted and reencrypted with an AES symmetric key generated by HSM-based transparent data encryption.

If you are already using tablespace encryption and not using HSM, then tablespace encryption continues to use the same software wallet for all encryption and decryption operations. The existing tablespace master encryption key cannot be migrated to the HSM.

Note:

A tablespace master encryption key is created in the HSM only if you have not created a tablespace master encryption key in the software wallet.

3.7.5 Reconfigure the Software Wallet (Optional)

This step is applicable if you are already using the software wallet for tablespace encryption. 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. 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.

    See Also:

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

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 (" ")

Note:

Access to the HSM needs to reenabled every time the database instance is restarted.

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 Getting Started with Transparent Data Encryption and Tablespace Encryption

This section uses a tutorial approach to help you get started with transparent data encryption and tablespace encryption. We illustrate the following tasks using sample scenarios:

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

3.8.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=/u01/app/oracle/product/11.1.0/db_1/)))

Save the changes and close the file.

Note:

You can choose any directory for the encrypted wallet, but the path should not point to the standard obfuscated wallet (cwallet.sso) created during the database installation.

3.8.1.2 Create the Master Encryption Key

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

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

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 transparent data encryption is created/re-created.

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

Note:

  • The master encryption key should only be created once, unless you want to reencrypt your data with a new encryption key.

  • Only users with the ALTER SYSTEM privilege can create a master encryption key or open the wallet.

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

ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY "welcome1";

Note:

The password used with the preceding command is the same that you used to create the master encryption key. This becomes the password to open the wallet and make the master encryption key accessible.

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.9 Troubleshooting Transparent Data Encryption

This section lists common error messages that you may encounter while configuring and using transparent data encryption. It also lists the common causes of these error messages and possible solutions for them.

ORA-28330: encryption is not allowed for this data type
Cause: Data type was not supported for column encryption.
Action: None
ORA-28331: encrypted column size too long for its data type
Cause: column was encrypted and for VARCHAR2, the length specified was > 3932; for CHAR, the length specified was > 1932; for NVARCHAR2, the length specified was > 1966; for NCHAR, the length specified was > 966;
Action: Reduce the column size.
ORA-28332: cannot have more than one password for the encryption key
Cause: More than one password was specified in the user command.
Action: None
ORA-28333: column is not encrypted
Cause: An attempt was made to rekey or decrypt an unencrypted column.
Action: None
ORA-28334: column is already encrypted
Cause: An attempt was made to encrypt an encrypted column.
Action: None
ORA-28335: referenced or referencing FK constraint column cannot be encrypted
Cause: encrypted columns were involved in the referential constraint
Action: None
ORA-28336: cannot encrypt SYS owned objects
Cause: An attempt was made to encrypt columns in a table owned by SYS.
Action: None
ORA-28337: the specified index may not be defined on an encrypted column
Cause: Index column was either a functional, domain, or join index.
Action: None
ORA-28338: cannot encrypt indexed column(s) with salt
Cause: An attempt was made to encrypt index column with salt.
Action: Alter the table and specify column encrypting without salt.
ORA-28339: missing or invalid encryption algorithm
Cause: Encryption algorithm was missing or invalid in the user command.
Action: Must specify a valid algorithm.
ORA-28340: a different encryption algorithm has been chosen for the table
Cause: Existing encrypted columns were associated with a different algorithm.
Action: No need to specify an algorithm, or specify the same one for the existing encrypted columns.
ORA-28341: cannot encrypt constraint column(s) with salt
Cause: An attempt was made to encrypt constraint columns with salt.
Action: Encrypt the constraint columns without salt.
ORA-28342: integrity check fails on column key
Cause: Encryption metadata may have been improperly altered.
Action: None
ORA-28343: fails to encrypt data
Cause: data or encryption metadata may have been improperly altered or the security module may not have been properly setup
Action: None
ORA-28344: fails to decrypt data
Cause: data or encryption metadata may have been improperly altered or the security module may not have been properly setup
Action: None
ORA-28345: cannot downgrade because there exists encrypted column
Cause: An attempt was made to downgrade when there was an encrypted column in the system.
Action: Decrypt these columns before attempting to downgrade.
ORA-28346: an encrypted column cannot serve as a partitioning column
Cause: An attempt was made to encrypt a partitioning key column or create partitioning index with encrypted columns.
Action: The column must be decrypted.
ORA-28347: encryption properties mismatch
Cause: An attempt was made to issue an ALTER TABLE EXCHANGE PARTITION | SUBPARTITION command, but encryption properties were mismatched.
Action: Make sure encrytion algorithms and columns keys are identical. The corresponding columns must be encrypted on both tables with the same salt and non-salt flavor.
ORA-28348: index defined on the specified column cannot be encrypted
Cause: An attempt was made to encrypt a column which is in a functional index, domain index, or join index.
Action: drop the index
ORA-28349: cannot encrypt the specified column recorded in the materialized view log
Cause: An attempt was made to encrypt a column which is already recorded in the materialized view log.
Action: drop the materialized view log
ORA-28350: cannot encrypt the specified column recorded in CDC synchronized change table
Cause: An attempt was made to encrypt a column which is already recorded in CDC synchronized change table.
Action: drop the synchronized change table
ORA-28351: cannot encrypt the column of a cluster key
Cause: An attempt was made to encrypt a column of the cluster key. A column of the cluster key in a clustered table cannot be encrypted.
Action: None
ORA-28353: failed to open wallet
Cause: The database was unable to open the security module wallet due to an incorrect wallet path or password It is also possible that a wallet has not been created. Type mkwallet from command line for instructions.
Action: Execute the command again using the correct wallet password or verfying a wallet exists in the specified directory. If necessary, create a new wallet and initialize it.
ORA-28354: wallet already open
Cause: The security module wallet has already been opened.
Action: None
ORA-28356: invalid open wallet syntax
Cause: The command to open the wallet contained improper spelling or syntax.
Action: If attempting to open the wallet, verify the spelling and syntax and execute the command again.
ORA-28357: password required to open the wallet
Cause: A password was not provided when executing the open wallet command.
Action: Retry the command with a valid password.
ORA-28358: improper set key syntax
Cause: The command to set the master key contained improper spelling or syntax.
Action: If attempting to set the master key for Transparent Database Encryption, verify the spelling and syntax and execute the command again.
ORA-28359: invalid certificate identifier
Cause: The certificate specified did not exist in the wallet.
Action: Query the V$WALLET fixed view to find the proper certificate identifier for certificate to be used.
ORA-28361: master key not yet set
Cause: The master key for the instance was not set.
Action: Execute the ALTER SYSTEM SET KEY command to set a master key for the database instance.
ORA-28362: master key not found
Cause: The required master key required could not be located. This may be casued by the use of an invalid or incorrect wallet.
Action: Check wallet location parameters to see if they specify the correct wallet. Also, verify that an SSO wallet is not being used when an encrypted wallet is intended.
ORA-28363: buffer provided not large enough for output
Cause: A provided output buffer is too small to contain the output.
Action: Check the size of the output buffer to make sure it is initialized to the proper size.
ORA-28364: invalid wallet operation
Cause: The command to operate the wallet contained improper spelling or syntax.
Action: Verify the spelling and syntax and execute the command again.
ORA-28365: wallet is not open
Cause: The security module wallet has not been opened.
Action: Open the wallet.
ORA-28366: invalid database encryption operation
Cause: The command for database encryption contained improper spelling or syntax.
Action: Verify the spelling and syntax and execute the command again.
ORA-28367: wallet does not exist
Cause: The Oracle wallet has not been created or the wallet location parameters in sqlnet.ora specifies an invalid wallet path.
Action: Verify that the WALLET_LOCATION or the ENCRYPTION_WALLET_LOCATION parameter is correct and that a valid wallet exists in the path specified.
ORA-28368: cannot auto-create wallet
Cause: The database failed to auto create an Oracle wallet. The Oracle process may not have proper file permissions or a wallet may already exist.
Action: Confirm that proper directory permissions are granted to the Oracle user and that neither an encrypted or obfuscated wallet exists in the specified wallet location and try again.
ORA-28369: cannot add files to encryption-ready tablespace when offline
Cause: You attempted to add files to an encryption-ready tablespace when all the files in the tablespace were offline.
Action: Bring the tablespace online and try again
ORA-28370: ENCRYPT storage option not allowed
Cause: You attempted to specify the ENCRYPT storage option. This option may only be specified during CREATE TABLESPACE.
Action: Remove this option and retry the statement.
ORA-28371: ENCRYPTION clause and/or ENCRYPT storage option not allowed
Cause: You attempted to specify the ENCRYPTION clause or ENCRYPT storage option for creating TEMP or UNDO tablespaces.
Action: Remove these options and retry the statement.
ORA-28372: missing ENCRYPT storage option for encrypted tablespace
Cause: You attempted to specify ENCRYPTION property for CREATE TABLESPACE without specifying ENCRYPT storage option to encrypt the tablepsace.
Action: Add ENCRYPT storage option and retry the statement.
ORA-28373: missing ENCRYPTION clause for encrypted tablespace
Cause: You attempted to specify storage option ENCRYPT in CREATE TABLESPACE without specifying ENCRYPTION property to encrypt the tablepsace.
Action: Add ENCRYPTION clause and retry the statement.
ORA-28374: typed master key not found in wallet
Cause: You attempted to access encrypted tablespace or redo logs with a typed master key not existing in the wallet.
Action: Copy the correct Oracle Wallet from the instance where the tablespace was created.
ORA-28375: cannot perform cross-endianism conversion on encrypted tablespace
Cause: You attempted to perform cross-endianism conversion on encrypted tablespace.
Action: Cross-endianism conversion on encrypted tablespace is not supported.
ORA-28376: cannot find PKCS11 library
Cause: The HSM vendor"s library cannot be found.
Action: Place the HSM vendor"s library in the following directory structure: For Unix like system: /opt/oracle/extapi/[32,64]/hsm/{VENDOR}/{VERSION}/lib<apiname>.<ext> For Windows systems: %SYSTEM_DRIVE%\oracle\extapi\[32,64]\hsm\{VENDOR}\{VERSION}\lib<apin// ame>.<ext> [32, 64] - refers to 32bit or 64bit binary. {VENDOR} - The name of the vendor supplying the library. {VERSION} - Version of the library, perferably in num#.num#.num# for// mat.
ORA-28377: No need to migrate from wallet to HSM
Cause: There are either no encrypted columns or all column keys are already encrypted with the HSM master key.
Action: No action required.
ORA-28378: Wallet not open after setting the Master Key
Cause: The Master Key has been set or reset. However, wallet could not be reopened successfully.
Action: Reopen the wallet.

3.10 Transparent Data Encryption Reference Information

This section includes the following topics:

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.

See Also:

Table 3-1 lists the supported encryption algorithms.

Table 3-1 Supported Encryption Algorithms for Transparent Data Encryption

Algorithm Key Size Parameter Name

Triple DES (Data Encryption Standard)

168 bits

3DES168

AES (Advanced Encryption Standard)

128 bits

AES128

AES

192 bits (default)

AES192

AES

256 bits

AES256


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.

Table 3-2 Maximum Allowable Size for Data Types

Data Type Maximum Size

CHAR

1932 bytes

VARCHAR2

3932 bytes

NVARCHAR2

1966 bytes

NCHAR

966 bytes


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.

Table 3-3 Transparent Data Encryption SQL Commands Quick Reference

Task SQL Command

Add encrypted column to existing table

ALTER TABLE table_name ADD (column_name datatype ENCRYPT);

Create table and encrypt column

CREATE TABLE table_name (column_name datatype ENCRYPT);

Encrypt unencrypted existing column

ALTER TABLE table_name MODIFY (column_name ENCRYPT);

Master encryption key: set or reset

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY password;

Master encryption key: set or reset to use PKI certificate

ALTER SYSTEM SET ENCRYPTION KEY certificate_ID IDENTIFIED BY password;

Wallet: open to access master encryption key

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY password;