3 Transparent Data Encryption

3.1.1 Benefits of Using Transparent Data Encryption

Transparent data encryption enables simple and easy encryption for sensitive data in columns without requiring users or applications to manage the encryption key. This freedom can be extremely important when addressing, for example, regulatory compliance issues. No need to use views to decrypt data, because the data is transparently decrypted once a user has passed necessary access control checks. Security administrators have the assurance that the data on disk is encrypted, yet handling encrypted data becomes transparent to applications.

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.

Do not, however, use transparent data encryption with these database features:

• Index types other than B-tree

• Range scan search through an index

• Large object datatypes such as BLOB and CLOB

• Import/export through Data Pump

• Direct Path SQL*Loader

• Other database tools and utilities that directly access data files

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

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. The keys for all tables containing encrypted columns are encrypted with the database server master 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 key of the server 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 key in this way prevents its unauthorized use. In addition to storing the master key, the Oracle wallet is also used to generate encryption keys and perform encryption and decryption.

Figure 3-1 Transparent Data Encryption Overview

Using an external security module separates ordinary program functions from encryption operations, making it possible to divide duties between database administrators and security administrators. Security is enhanced because no single administrator is granted complete access to all data.

3.1.4 Overview of Basic Transparent Data Encryption Operations

To use 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

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.

A master key that has been set remains accessible to the database until the database instance is shutdown. To load the master key after the database is restarted, use the following command:

ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY password

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 WALLET CLOSE

The command disables access to the master key in the wallet and prevents access to data in the encrypted columns.

3.2.1 Enabling Transparent Data Encryption

Before transparent data encryption can be enabled, the compatibility level for the database must be 10.2. 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, for greater security, a separate wallet be used to store transparent data encryption master keys.

3.2.2 Opening the Encrypted Wallet for Database Access to Encryption Keys

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

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY password

where password is a string value specified in sqlnet.ora as the password to the wallet.

Once the wallet has been opened, it remains open until you shut down the database instance (or close it explicitly by issuing an 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-1 shows an example of each usage case.

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "U83j10LLt8v";
Wallet opened.
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "U83j10LLt8v";
ORA-XXXXX: Wallet already opened.
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "U93j10LLt8v";
ORA-XXXXX: Incorrect wallet password given.

3.2.3 Setting and Resetting the Master Key

The master key is stored in the external security module and is used to protect column encryption keys. By default, the master 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 key may result in greater performance degradation when accessing encrypted columns in the database.

To set or reset the master key, you use the same ALTER SYSTEM command. The following sections explain how and why to perform each of these tasks.

ALTER SYSTEM SET ENCRYPTION KEY certificate_ID IDENTIFIED BY password

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

3.2.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, causing repetition of text in the clear to appear different when encrypted. Salt thus removes one method attackers use to steal data, namely, matching patterns of encrypted text.

To add or remove salt from encrypted columns, you again use the ALTER TABLE MODIFY command with either the SALT or NO SALT parameter specified with the ENCRYPT clause. Example 3-3 and Example 3-4 illustrate such commands.

ALTER TABLE employee MODIFY (first_name ENCRYPT SALT);

ALTER TABLE employee MODIFY (first_name ENCRYPT NO SALT);

To remove salt from an encrypted column before indexing it, use the syntax shown in Example 3-4. This command also re-encrypts the data.

3.2.5 Creating Tables That Contain 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 That Uses the Default Algorithm

• Creating a Table with an Encrypted Column Using a Non-Default Algorithm and No Salt

• Creating an Encrypted Column on an External Table

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

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

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

3.2.6 Specifying Columns for Encryption 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 Encrypted Columns to Existing Tables

• Encrypting Unencrypted Columns

• Disabling Encryption on a Column

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

ALTER TABLE employee MODIFY (first_name ENCRYPT);

ALTER TABLE employee MODIFY (first_name DECRYPT);

3.2.7 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-11 shows how to create an index on a column that has been encrypted without salt.

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

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

Each table can have at most one encryption key for its columns. This key can be changed by using either the original encryption algorithm or a different algorithm specified in the REKEY command phrase. The examples in this section illustrate these capabilities. Good security practices include backing up the wallet before and after such changes.

ALTER TABLE employee REKEY;

ALTER TABLE employee REKEY USING '3DES168';

ALTER TABLE employee ENCRYPT USING 'AES256';

3.2.9 Supported Encryption and Integrity Algorithms

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

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

Table 3-1 lists the supported encryption algorithms.

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

3.2.10 Datatypes That Can Be Encrypted with the Transparent Data Encryption Feature

The following datatypes can be encrypted using this feature:

• 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

3.2.11 Quick Reference: Transparent Data Encryption SQL Commands

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

3.3.1 Oracle Wallet Management for Transparent Data Encryption

To store the master keys, transparent data encryption can use either the default database wallet shared by all Oracle components or a separate wallet. The wallet can be an autologin wallet that allows access to encrypted data without requiring a security administrator to explicitly open the wallet.

3.3.2 Backup and Recovery of Master Keys

This section contains the following topics:

• Backup and Recovery of Oracle Wallet

• Backup and Recovery of PKI Key Pair

ALTER SYSTEM SET ENCRYPTION KEY certificate_id IDENTIFIED BY wallet_password

3.3.3 Export and Import of Tables with Encrypted Columns

When exporting tables containing encrypted columns, it is important that:

• The sensitive data remain unintelligible during transport

• Authorized users can decrypt that data after it is imported at the destination

Because the key for decryption is local to the server where the tables originally reside, decryption at the destination will not be possible using that key. Consequently, prior to exporting, the administrator re-keys the table(s) with a password key, which he then securely provides to the destination administrator.

Upon import, the destination administrator specifies the same password. The affected columns being imported are decrypted, enabling the receiving server to immediately re-encrypt those columns with a local server key. They are then ready for standard authorized use in their new location.

3.3.4 Performance Effects of Transparent Data Encryption

This feature affects performance only when data is retrieved from or inserted into an encrypted column. No reduction of performance occurs for such operations on other columns, even in a table containing encrypted columns.

The total performance effect 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, including regulatory mandates.

Enabling transparent data encryption on columns in an existing table results in a full table update on all rows in the column as it encrypts all data stored within the column. This may cause the table to be inaccessible while encryption is being enabled. Using Online Redefinition to enable transparent data encryption allows the table to be available while it is being encrypted.

The redo log impact of a full table update on a large table should also be kept in mind. 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.

3.3.5 Security Considerations for Using Transparent Data Encryption

Access to encrypted columns can be:

• Public

• Controlled by standard authentication and authorization procedures and policies

• Controlled by individual users who can grant access to others

Security considerations for transparent data encryption operate within the broader arena of total system security. Security administrators 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 materials. Having separate wallets permits auto-login for other Oracle components but preserves password protection for the wallet enabling transparent data encryption.

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

3.3.6 Transparent Data Encryption and Replication in Distributed Environments

In general, the methods and protections vary with the choice of the tool by which replication is accomplished. SQL*Loader, Dataguard, and import/export are methods to enable transporting tables with encrypted columns. They rely on using a password created precisely to protect the data during transmission. When the data is reconstituted at the receipt-point, the columns marked as encrypted are immediately re-keyed with a master key local to the new table location.

When asynchronous SQL-based replication is used, information describing the update to be performed is encoded (but not encrypted) in a LOB and can remain in the deferred queue for some time. While in the deferred queue, the data is not protected. To minimize this risk, configure replication so that encrypted table information does not remain in the deferred queue for a long time.

3.3.7 Transparent Data Encryption with OCI

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

3.3.8 Transparent Data Encryption Data Dictionary Views

The following three 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.