Skip Headers
Oracle® Database Advanced Security Guide
12c Release 1 (12.1)

E17729-18
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 Configuring Transparent Data Encryption

You can configure software or hardware keystores, for use on both individual table columns or entire tablespaces.

This chapter contains the following topics:

Configuring a Software Keystore

A software keystore is a container for the TDE master encryption key, and it resides in the software file system.

This section contains the following topics:

About Configuring a Software Keystore

A software keystore is a container that stores the Transparent Data Encryption master encryption key. Before you can configure the keystore, you first must define a location for it in the sqlnet.ora file. There is one keystore per database, and the database locates this keystore by checking the keystore location that you define in the sqlnet.ora file. You can create other keystores, such as copies of the keystore and export files that contain keys, depending on your needs. However, you must never remove or delete the keystore that you configured in the sqlnet.ora location, nor replace it with a different keystore.

After you configure the software keystore location in the sqlnet.ora file, you can log in to the database instance to create and open the keystore, and then set the TDE master encryption key. After you complete these steps, you can begin to encrypt data.

Step 1: Set the Software Keystore Location in the sqlnet.ora File

The first step you must take to configure a software keystore is to designate a location for it in the sqlnet.ora file.

This section contains the following topics:

About the Keystore Location in the sqlnet.ora File

Oracle Database checks the sqlnet.ora file for the directory location of the keystore, whether it is a software keystore or a hardware module security (HSM) keystore. Your first step is to edit the sqlnet.ora file to define a directory location for the keystore that you plan to create. Ensure that this directory exists beforehand. Preferably, this directory should be empty.

In a multitenant environment, the keystore location is set for the entire multitenant container database (CDB), not for individual pluggable databases (PDBs).

In the sqlnet.ora file, you must set the ENCRYPTION_WALLET_LOCATION parameter to specify the keystore location. When determining which keystore to use, Oracle Database searches for the keystore location in the following places, in this order:

  1. First, it attempts to use the keystore in the location specified by the parameter ENCRYPTION_WALLET_LOCATION in the sqlnet.ora file.

  2. If the ENCRYPTION_WALLET_LOCATION parameter is not set, then it attempts to use the keystore in the location that is specified by the parameter WALLET_LOCATION.

  3. If the WALLET_LOCATION parameter is also not set, then Oracle Database looks for a keystore at the default database location, which is ORACLE_BASE/admin/DB_UNIQUE_NAME/wallet or ORACLE_HOME/admin/DB_UNIQUE_NAME/wallet. (DB_UNIQUE_NAME is the unique name of the database specified in the initialization parameter file.) When the keystore location is not set in the sqlnet.ora file, then the V$ENCRYPTION_WALLET view displays the default location. You can check the location and status of the keystore in the V$ENCRYPTION_WALLET view.

By default, the sqlnet.ora file is located in the ORACLE_HOME/network/admin directory or in the location set by the TNS_ADMIN environment variable. Ensure that you have properly set the TNS_ADMIN environment variable to point to the correct sqlnet.ora file. See SQL*Plus User's Guide and Reference for more information and examples of setting the TNS_ADMIN environment variable.

See Also:

"Migrating a Software Password Keystore to a Hardware Keystore and Vice Versa" for information about how to configure the sqlnet.ora file for migration between these two keystore types

Configuring the sqlnet.ora File for a Software Keystore Location

To create a software keystore on a regular file system, use the following format when you edit the sqlnet.ora file:

ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=path_to_keystore)))

Example 3-1 shows how to configure a software keystore location in the sqlnet.ora file for a regular file system in which the database name is orcl.

Example 3-1 Configuring a Software Keystore Location on a Regular File System

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=
   (METHOD=FILE)
    (METHOD_DATA=
     (DIRECTORY=/etc/ORACLE/WALLETS/orcl)))

Example 3-2 shows how to configure a software keystore location when multiple databases share the sqlnet.ora file.

Example 3-2 Configuring a Software Keystore Location for Multiple Databases

ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=/etc/ORACLE/WALLETS/$ORACLE_SID/)))

If you want to create a software keystore location on an Oracle Automatic Storage Management (ASM) disk group, then use the following format:

ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=+ASM_file_path_of_the_diskgroup)))

Example 3-3 shows how to configure a software keystore location in the sqlnet.ora file for an Automatic Storage Management (ASM) file system.

Example 3-3 Configuring a Software Keystore Location on an ASM System

ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=FILE)
   (METHOD_DATA=
    (DIRECTORY=+disk1/mydb/wallet)))

Step 2: Create the Software Keystore

After you have specified a directory location for the software keystore, you can create the keystore.

This section contains the following topics:

About Creating Software Keystores

You can create password-based software keystores, auto-login software keystores, and local auto-login software keystores.

See Also:

"Types of Keystores" for more information about software keystores

Creating a Password-Based Software Keystore

A password-based software keystore requires a user password, which is used to protect the keys and credentials stored in the keystore.

  1. Ensure that you complete the procedure described in "Step 1: Set the Software Keystore Location in the sqlnet.ora File".

  2. Log in to the database instance as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

    In a multitenant environment, log in to the root. For example:

    sqlplus c##sec_admin as syskm
    Enter password: password
    Connected.
    
  3. Run the ADMINISTER KEY MANAGEMENT SQL statement to create the keystore.

    The syntax is as follows:

    ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'keystore_location' IDENTIFIED BY software_keystore_password;
    

    In this specification:

    • keystore_location is the path to the keystore directory location of the password-based keystore for which you want to create the auto-login keystore (for example, /etc/ORACLE/WALLETS/orcl). Enclose the keystore_location setting in single quotation marks (' '). To find this location, you can query the WRL_PARAMETER column of the V$ENCRYPTION_WALLET view. (If the keystore was not created in the default location, then the STATUS column of the V$ENCRYPTION_WALLET view is NOT_AVAILABLE.)

    • software_keystore_password is the password of the keystore that you, the security administrator, creates.

    For example, to create the keystore in the /etc/ORACLE/WALLETS/orcl directory:

    ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/orcl' IDENTIFIED BY password;
    
    keystore altered.
    

    After you run this statement, the ewallet.p12 file, which is the keystore, appears in the keystore location.

Creating an Auto-Login or Local Auto-Login Software Keystore

As an alternative to password-based keystores, you can create either an auto-login or local auto-login software keystore. Both of these keystores have system-generated passwords. They are also PKCS#12-based files. The auto-login software keystore can be opened from different computers from the computer where this keystore resides, but the local auto-login software keystore can only be opened from the computer on which it was created. Both the auto-login and local auto-login keystores are created from the password-based software keystores.

  1. Ensure that you complete the procedure described in "Step 1: Set the Software Keystore Location in the sqlnet.ora File".

  2. Log in to the database instance as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

    In a multitenant environment, log in to the root. For example:

    sqlplus c##sec_admin as syskm
    Enter password: password
    Connected.
    
  3. Create a password-based software keystore, as described in "Creating a Password-Based Software Keystore".

  4. Run the ADMINISTER KEY MANAGEMENT SQL statement to create the keystore.

    The syntax is as follows:

    ADMINISTER KEY MANAGEMENT CREATE [LOCAL] AUTO_LOGIN KEYSTORE FROM KEYSTORE 'keystore_location' IDENTIFIED BY software_keystore_password;
    

    In this specification:

    • LOCAL enables you to create a local auto-login software keystore. Otherwise, omit this clause if you want the keystore to be accessible by other computers.

    • keystore_location is the path to the directory location of the password-based keystore for which you want to create the auto-login keystore (for example, /etc/ORACLE/WALLETS/orcl). Enclose this setting in single quotation marks (' '). To find this location, query the WRL_PARAMETER column of the V$ENCRYPTION_WALLET view.

    • software_keystore_password is the password-based keystore for which you want to create the auto-login keystore.

    For example, to create an auto-login software keystore of the password-based keystore that is located in the/etc/ORACLE/WALLETS/orcl directory:

    ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/etc/ORACLE/WALLETS/orcl' IDENTIFIED BY password;
    
    keystore altered.
    

    After you run this statement, the cwallet.sso file appears in the keystore location. The ewallet.p12 file is the password-based wallet.

Note:

Do not remove the PKCS#12 wallet (ewallet.p12 file) after you create the auto login keystore (.sso file). You must have the PKCS#12 wallet to regenerate or rekey the TDE master encryption key in the future. By default, this file is located in the $ORACLE_HOME/admin/ORACLE_SID/wallet directory.

Transparent Data Encryption uses an auto login keystore only if it is available at the correct location (ENCRYPTION_WALLET_LOCATION, WALLET_LOCATION, or the default keystore location), and the SQL statement to open an encrypted keystore has not already been executed. (Note that auto-login keystores are encrypted, because they have system-generated passwords.)

Step 3: Open the Software Keystore

Depending on the type of keystore you create, you must manually open the keystore before you can use it.

This section contains the following topics:

About Opening Software Keystores

You must manually open a password-based software keystore before any TDE master encryption keys can be created or accessed in the keystore. You do not need to manually open auto-login or local auto-login software keystores. These keystore are automatically opened when it is required, that is, when an encryption operation must access the key. If necessary, you can explicitly close any of these types of keystores. You can check the status of whether a keystore is open, closed, open but with no master key, or open but with an unknown master key by querying the STATUS column of the V$ENCRYPTION_WALLET view.

After you open a keystore, it remains open until you manually close it. Each time you restart a database instance, you must manually open the password keystore to reenable encryption and decryption operations.

Opening a Software Keystore

  1. Ensure that you complete the procedure described in "Step 2: Create the Software Keystore".

  2. Log in to the database instance as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

    In a multitenant environment, you must open the keystore first in the root before you can open it in a PDB. For example, to log in to the root:

    sqlplus c##sec_admin as syskm
    Enter password: password
    Connected.
    

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

  3. Run the ADMINISTER KEY MANAGEMENT statement.

    Use the following syntax:

    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY software_keystore_password [CONTAINER = ALL | CURRENT];
    

    In this specification:

    • software_keystore_password is the same password that you used to create the keystore in "Step 2: Create the Software Keystore".

    • CONTAINER is for use in a multitenant environment. Enter ALL to set the keystore in all of the PDBs in this CDB, or CURRENT for the current PDB.

      For example:

      ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password;
      
      keystore altered.
      

Note that if the keystore is open but you have not created a TDE master encryption key yet (described next), the STATUS column of the V$ENCRYPTION_WALLET view reminds you with an OPEN_NO_MASTER_KEY status.

Step 4: Set the Software TDE Master Encryption Key

Once the keystore is open, you can set a TDE master encryption key for it.

This section contains the following topics:

About Setting the Software TDE Master Encryption Key

The TDE master encryption key is stored in the keystore. It protects the TDE table keys and tablespace encryption keys. By default, the TDE master encryption key is a key that Transparent Data Encryption (TDE) generates.

In a multitenant environment, you can create and manage the TDE master encryption key from either the root or the PDB.

Note:

You can create TDE master encryption keys for use later on, and then manually activate them. See "Creating a TDE Master Encryption Key for Later Use" for more information.

Setting the TDE Master Encryption Key in the Software Keystore

  1. For password software keystores, ensure that you complete the procedure described in "Step 3: Open the Software Keystore" to open the key.

    Auto-login or local auto-login software keys are opened automatically after you create them. Password-based software keystores must be open before you can set the TDE master encryption key. If the auto-login software keystore is open, then you must close it and open the password-based software keystore. If both the password-based keystore and auto-login keystores are present in the configured location and the password-based keystore is open, then the TDE master encryption key is automatically written to the auto-login keystore as well.

  2. Log in to the database instance as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

    In a multitenant environment, log in to the root or to the PDB. For example, to log in to a PDB:

    sqlplus sec_admin@hrpdb as syskm
    Enter password: password
    Connected.
    

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

  3. Ensure that the database is open in READ WRITE mode.

    You can set the TDE master encryption key if OPEN_MODE is set to READ WRITE. To find the status, for a non-multitenant environment, query the OPEN_MODE column of the V$DATABASE dynamic view. If you are using a multitenant environment, then query the V$PDBS view. (If you cannot access these views, then connect as SYSDBA and try the query again. In order to connect as SYSKM for this type of query, you must create a password file for it. See Oracle Database Administrator's Guide for more information.)

  4. Connect using the SYSKM administrative privilege and then run the ADMINISTER KEY MANAGEMENT SQL statement to set the software management key.

    ADMINISTER KEY MANAGEMENT SET KEY [USING TAG 'tag'] IDENTIFIED BY password [WITH BACKUP [USING 'backup_identifier']] [CONTAINER = ALL | CURRENT];
    

    In this specification:

    • tag is the associated attributes and information that you define. Enclose this setting in single quotation marks (' ').

    • password is the mandatory keystore password that you created when you created the keystore in "Step 2: Create the Software Keystore".

    • WITH BACKUP creates a backup of the keystore. You must use this option for password-based keystores. Optionally, you can use the USING clause to add a brief description of the backup. Enclose this description in single quotation marks (' '). This identifier is appended to the named keystore file (for example, ewallet_time_stamp_emp_key_backup.p12, with emp_key_backup being the backup identifier). Follow the file naming conventions that your operating system uses.

    • CONTAINER is for use in a multitenant environment. Enter ALL to set the key in all of the PDBs in this CDB, or CURRENT for the current PDB.

    For example:

    ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY password WITH BACKUP USING 'emp_key_backup';
    
    keystore altered.
    

Step 5: Encrypt Your Data

At this stage, the software keystore configuration is complete and you can begin to encrypt data. See the following sections:

Configuring a Hardware Keystore

A hardware keystore resides in a hardware security module (HSM), which is designed to store encryption keys.

This section contains the following topics:

About Configuring a Hardware Keystore

A hardware security module (HSM) is a physical device that provides secure storage for encryption keys. To configure a keystore for a hardware security module (hardware keystore), you must first include the keystore type in the sqlnet.ora file, configure and open the hardware keystore, and then set the hardware keystore TDE master encryption key. In short, there is one hardware keystore per database, and the database locates this keystore by checking the keystore type that you define in the sqlnet.ora file.

After you configure the hardware keystore, you are ready to begin encrypting your data.

Step 1: Set the Hardware Keystore Type in the sqlnet.ora File

Before you can configure a hardware keystore, you must enable the database to recognize that it is a hardware security module by editing the sqlnet.ora file. By default, this file is located in the ORACLE_HOME/network/admin directory or in the location set by the TNS_ADMIN environment variable.

Use the following setting in the sqlnet.ora file to define the hardware keystore type, which is HSM.

ENCRYPTION_WALLET_LOCATION=
 (SOURCE=
  (METHOD=HSM))

See Also:

Step 2: Configure the Hardware Security Module

  1. Ensure that you complete the procedure described in "Step 1: Set the Hardware Keystore Type in the sqlnet.ora File".

  2. Copy the PKCS#11 library to its correct path.

    Your hardware security module vendor should provide you with an associated PKCS#11 library. Only one PKCS#11 library is supported at a time. If you want to use an HSM from a new vendor, then you must replace the PKCS#11 library from the earlier vendor with the library from the new vendor.

    Copy this library to the appropriate location to ensure that Oracle Database can find this library:

    • UNIX systems: Use the following syntax to copy the library to this directory:

      /opt/oracle/extapi/[32,64]/hsm/{VENDOR}/{VERSION}/libapiname.ext 
      
    • Windows systems: Use the following syntax to copy the library to this directory:

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

    In this specification:

    • [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 the 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 must be replaced by the extension of the library file. This extension is .so on UNIX.

  3. Follow your vendor's instructions to set up the hardware security module.

    Use your hardware security module management interface and the instructions provided by your HSM vendor to set up the hardware security module. Create the user account and password that must be used by the database to interact with the hardware security module. This process creates and configures a hardware keystore that communicates with your Oracle database.

Step 3: Open the Hardware Keystore

After you have configured the hardware security module, you must open the hardware keystore before it can be used.

This section contains the following topics:

About Opening the Hardware Keystore

You must open the hardware keystore so that it is accessible to the database before you can perform any encryption or decryption. You can check the status of whether a keystore is open, closed, open but with no TDE master encryption key, or open but with an unknown master encryption key by querying the STATUS column of the V$ENCRYPTION_WALLET view.

Opening the Hardware Keystore

  1. Ensure that you complete the procedure described in "Step 2: Configure the Hardware Security Module".

  2. Log in to the database instance as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

    In a multitenant environment, you must open the keystore first in the root before you can open it in a PDB. For example, to log in to the root:

    sqlplus sec_admin as syskm
    Enter password: password
    Connected.
    

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

  3. Run the ADMINISTER KEY MANAGEMENT SQL statement using the following syntax:

    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "user_id:password" [CONTAINER = ALL | CURRENT];
    

    In this specification:

    • 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 (" ") and separate user_id and password with a colon (:).

    • CONTAINER is for use in a multitenant environment. Enter ALL to set the keystore in all of the PDBs in this CDB, or CURRENT for the current PDB.

    For example:

    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "psmith:password";
    
    keystore altered.
    
  4. Repeat this procedure each time you restart the database instance.

Step 4: Set the Hardware Keystore TDE Master Encryption Key

Once you have opened the hardware keystore, you are ready to set the hardware keystore TDE master encryption key.

This section contains the following topics:

About Setting the Hardware Keystore TDE Master Encryption Key

To start using hardware security module-based encryption, you must create a TDE master encryption key that is stored inside the hardware keystore. Oracle Database uses the TDE master encryption key to encrypt or decrypt TDE table keys or tablespace encryption keys inside the hardware security module.

If you have not previously configured a software keystore for Transparent Data Encryption, then follow the steps in "Setting a TDE Master Encryption Key if You Have Not Previously Configured One". If you have already configured a software keystore for TDE, then you must migrate it to the hardware security module, as described in "Migrating a Previously Configured TDE Master Encryption Key".

Setting a TDE Master Encryption Key if You Have Not Previously Configured One

Complete this step if you have not previously configured a software keystore for Transparent Data Encryption.

In a multitenant environment, you can create and manage the TDE master encryption key from either the root or the PDB.

Note:

You can create TDE master encryption keys for use later on, and then manually activate them. See "Creating a TDE Master Encryption Key for Later Use" for more information.

To set the TDE master encryption key for a hardware security module:

  1. Ensure that you complete the procedure described in"Step 3: Open the Hardware Keystore".

  2. Log in to the database instance as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

    In a multitenant environment, log in to the root or to the PDB. For example:

    sqlplus sec_admin@hrpdb as syskm
    Enter password: password
    Connected.
    

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

  3. Ensure that the database is open in READ WRITE mode.

    You can set the TDE master encryption key if OPEN_MODE is set to READ WRITE. To find the status, for a non-multitenant environment, query the OPEN_MODE column of the V$DATABASE dynamic view. If you are in a multitenant environment, then query the V$PDBS view. (If you cannot access these views, then connect as SYSDBA and try the query again. In order to connect as SYSKM for this type of query, you must create a password file for it. See Oracle Database Administrator's Guide for more information.)

  4. Run the following SQL statement:

    ADMINISTER KEY MANAGEMENT SET KEY [USING TAG 'tag'] IDENTIFIED BY "user_id:password" [CONTAINER = ALL | CURRENT];
    

    In this specification:

    • tag is the associated attributes and information that you define. Enclose this setting in single quotation marks (' ').

    • 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 (" ") and separate user_id and password with a colon (:).

    • CONTAINER is for use in a multitenant environment. Enter ALL to set the keystore in all of the PDBs in this CDB, or CURRENT for the current PDB.

    For example:

    ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "psmith:password";
    
    keystore altered.
    

Migrating a Previously Configured TDE Master Encryption Key

You must perform this step if you previously configured a software keystore. Tools such as Oracle Data Pump and Oracle Recovery Manager require access to the old software keystore to perform decryption and encryption operations on data exported or backed up using the software keystore. Migrate from the software to the hardware keystore, as described in "Migrating a Software Password Keystore to a Hardware Keystore and Vice Versa".

Step 5: Encrypt Your Data

At this stage, the software keystore configuration is complete and you can begin to encrypt data. See the following sections:

Encrypting Columns in Tables

You can use Transparent Data Encryption to encrypt individual columns in database tables.

This section contains the following topics:

About Encrypting Columns in Tables

You can encrypt individual columns in tables. Whether you choose to encrypt individual columns or entire tablespaces depends on the requirements described in the following two sections. Following these sections are various procedures for encrypting table columns.

Data Types That Can Be Encrypted with TDE Column Encryption

You can encrypt data columns that use the following data types:

  • BINARY_DOUBLE

  • BINARY_FLOAT

  • CHAR

  • DATE

  • INTERVAL DAY TO SECOND

  • INTERVAL YEAR TO MONTH

  • NCHAR

  • NUMBER

  • NVARCHAR2

  • RAW (legacy or extended)

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

  • VARCHAR2 (legacy or extended)

You cannot encrypt a column if the encrypted column size is greater than the size allowed by the data type of the column.

Table 3-1 shows the maximum allowable sizes for various data types.

Table 3-1 Maximum Allowable Size for Data Types

Data Type Maximum Size

CHAR

1932 bytes

VARCHAR2 (legacy)

3932 bytes

VARCHAR2 (extended)

32,699 bytes

NVARCHAR2 (legacy)

1966 bytes

NVARCHAR2 (extended)

16,15 bytes

NCHAR

966 bytes

RAW (extended)

32,699 bytes


Note:

TDE tablespace encryption does not have these data type restrictions. See "Encrypting Entire Tablespaces" for more information.

Restrictions on Using Transparent Data Encryption Column Encryption

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

  • Index types other than B-tree

  • Range scan search through an index

  • Synchronous change data capture

  • Transportable tablespaces

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

Applications that must use these unsupported features can use the DBMS_CRYPTO PL/SQL package for their encryption needs.

Transparent Data Encryption protects data stored on a disk or other media. It does not protect data in transit. Use the network encryption solutions discussed in Oracle Database Security Guide to encrypt data over the network.

Creating Tables with Encrypted Columns

You can create new tables that have encrypted columns. Oracle Database provides a selection of different algorithms that you can use to definite the encryption.

This section contains the following topics:

About 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 SQL statement.

Creating a Table with an Encrypted Column Using the Default Algorithm

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

TDE adds salt to plaintext before encrypting it. Adding salt makes it harder for attackers to steal data through a brute force attack. TDE also adds a Message Authentication Code (MAC) to the data for integrity checking. The SHA-1 integrity algorithm is used by default.

Note:

If there are multiple encrypted columns in a table, then all of these columns must use the same pair of encryption and integrity algorithms.

Salt is specified at the column level. This means that an encrypted column in a table can choose not to use salt irrespective of whether or not other encrypted columns in the table use salt.

Example 3-4 creates a new table with an encrypted column (salary). The column is encrypted using the default encryption algorithm (AES192). Salt and MAC are added by default. This example assumes that the wallet is open and a master key is set.

Example 3-4 Encrypting a Table Column Using the Default Algorithm (AES192)

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

Creating a Table with an Encrypted Column Using a Non-Default Algorithm

By default, TDE adds salt to plaintext before encrypting it. Adding salt makes it harder for attackers to steal data through a brute force attack. However, if you plan to index the encrypted column, then you must use the NO SALT parameter.

TDE also enables you to specify a non-default encryption algorithm. You can choose from one of the following algorithms:

  • 3DES168

  • AES128

  • AES192 (default)

  • AES256

Example 3-5 shows how to specify encryption settings for the empID and salary columns.

Example 3-5 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');

In this example:

  • The empID column is encrypted and does not use salt. Both the empID and salary columns will use the 3DES168 encryption algorithm, because all of the encrypted columns in a table must use the same encryption algorithm.

  • The salary column is encrypted using the 3DES168 encryption algorithm. Note that the string that specifies the algorithm must be enclosed in single quotation marks (' '). The salary column uses salt by default.

Using the NOMAC Parameter to Save Disk Space and Improve Performance

The NOMAC parameter in the CREATE TABLE and ALTER TABLE statements bypasses the integrity check performed by TDE. 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 can add up to a significant amount of disk space.

The NOMAC parameter also reduces the performance overhead associated with TDE. Using the NOMAC parameter bypasses the integrity check during encryption and decryption operations. This saves processing cycles and leads to faster performance.

Note:

TDE uses the SHA-1 integrity algorithm by default. All of the 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.

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

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

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

Example 3-7 shows how to change the integrity algorithm for encrypted columns in a table. The encryption algorithm is set to 3DES168 and the integrity algorithm is set to SHA-1. The second ALTER TABLE statement sets the integrity algorithm to NOMAC.

Example 3-7 Changing the Integrity Algorithm for a Table

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

ALTER TABLE EMPLOYEE REKEY USING '3DES168' 'NOMAC';

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.

To encrypt specific columns in an external table, use the ENCRYPT clause when you define those columns. A system-generated key encrypts the columns. For example, the following CREATE TABLE SQL statement encrypts the ssn column using the 3DES168 algorithm:

CREATE TABLE emp_ext (
    first_name,
    ....
    ssn ENCRYPT USING '3DES168',
    ....
...
...

If you plan to move an 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, 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 the encrypted column data at the new location.

Table partition exchange also requires a password-based TDE table key.

Example 3-8 creates an external table using a password to create the TDE table key.

Example 3-8 Creating a New External Table with a Password-Generated TDE Table Key

CREATE TABLE emp_ext (
     first_name,
     last_name,
     empID,
     salary,
     ssn ENCRYPT IDENTIFIED BY password
)  ORGANIZATION EXTERNAL
   (
    TYPE ORACLE_DATAPUMP
    DEFAULT DIRECTORY "D_DIR"
    LOCATION('emp_ext.dat')
    )
    REJECT LIMIT UNLIMITED
AS SELECT * FROM EMPLOYEE;

See Also:

Encrypting Columns in Existing Tables

You can encryption columns in existing tables. As with new tables, you have a choice of different algorithms to use to definite the encryption.

This section contains the following topics:

About 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 statement with the ADD or MODIFY clause.

Adding an Encrypted Column to an Existing Table

To add an encrypted column to an existing table, you must use the ALTER TABLE ADD statement, specifying the new column with the ENCRYPT clause.

Example 3-9 adds an encrypted column, ssn, to an existing table, called employee.

Example 3-9 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 and MAC are added by default.

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

Encrypting an Unencrypted Column

To encrypt an existing unencrypted column, use the ALTER TABLE MODIFY statement, specifying the unencrypted column with the ENCRYPT clause.

Example 3-10 encrypts the first_name column in the employee table.

Example 3-10 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 encrypt the column using a different algorithm. If you want to index a column, then you must specify NO SALT. You can also bypass integrity checks by using the NOMAC parameter.

Example 3-11 encrypts the first_name column in the employee table using the NOMAC parameter.

Example 3-11 Using the NOMAC Parameter in an ALTER TABLE Statement

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

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-12 decrypts the first_name column in the employee table.

Example 3-12 Turning Off Column Encryption

ALTER TABLE employee MODIFY (first_name DECRYPT);

Creating an Index on an Encrypted Column

To create an index on an encrypted column, use the standard CREATE INDEX statement. The column being indexed must be encrypted without salt. If the column is encrypted with salt, then the ORA-28338: cannot encrypt indexed column(s) with salt error is raised.

Example 3-13 shows how to create an index on a column that has been encrypted without salt.

Example 3-13 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);

Adding or Removing Salt from an Encrypted Column

Salt is a way to strengthen the security of encrypted data. It is a random string added to the data before it is encrypted. This ensures that the same plaintext data does not always translate to the same encrypted text. Salt removes the one common method that intruders use to steal data, namely, matching patterns of encrypted text. Adding salt requires an additional 16 bytes of storage per encrypted data value.

To add or remove salt from encrypted columns, use the ALTER TABLE MODIFY SQL statement.

Example 3-14 encrypts the first_name column using salt. If the first_name column was encrypted without salt earlier, then the ALTER TABLE MODIFY statement reencrypts it using salt.

Example 3-14 Adding Salt to an Encrypted Column

ALTER TABLE employee MODIFY (first_name ENCRYPT SALT);

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

Example 3-15 Removing Salt from an Encrypted Column

ALTER TABLE employee MODIFY (first_name ENCRYPT NO SALT);

Changing the Encryption Key or Algorithm for Tables Containing Encrypted Columns

Each table can have only one TDE table key for its columns. You can regenerate the TDE table key with the ALTER TABLE statement. This process generates a new key, decrypts the data in the table using the previous key, reencrypts the data using the new key, and then updates the table metadata with the new key information. You can also use a different encryption algorithm for the new TDE table key.

Example 3-16 regenerates the TDE table key for the employee table.

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

ALTER TABLE employee REKEY;

Example 3-17 regenerates the TDE table key for the employee table by using the 3DES168 algorithm.

Example 3-17 Changing an Encrypted Table Column Encryption Key and Algorithm

ALTER TABLE employee REKEY USING '3DES168';

Encrypting Entire Tablespaces

You can create a new tablespace and encryption it.

This section contains the following topics:

About Encrypting Entire Tablespaces

This section describes how to create a new tablespace and encrypt it. You cannot encrypt existing tablespaces. Before you decide to encrypt a tablespace, understand the restrictions described in the next section.

Restrictions on Using Transparent Data Encryption Tablespace Encryption

Note the following restrictions:

  • Transparent Data Encryption (TDE) tablespace encryption encrypts or decrypts data during read and write operations, as opposed to TDE column encryption, which encrypts and decrypts data at the SQL layer. This means that most restrictions that apply to TDE column encryption, such as data type restrictions and index type restrictions, do not apply to TDE tablespace encryption.

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

See Also:

Oracle Database Utilities for more information about Oracle Data Pump

Step 1: Set the COMPATIBLE Initialization Parameter for Tablespace Encryption

You must set the COMPATIBLE initialization parameter before creating an encrypted tablespace.

This section contains the following topics:

About Setting the COMPATIBLE Initialization Parameter for Tablespace Encryption

Before you can use the full set of tablespace encryption features, you must set the COMPATIBLE initialization parameter for the database to 11.2.0.0 or later. Setting the compatibility to 11.2.0.0 instead of 11.1.0.0 enables the following additional features:

  • The 11.2.0.0 setting enables the database to use any of the four supported algorithms for data encryption (3DES168, AES128, AES192, and AES256).

  • The 11.2.0.0 setting enables the migration of a key from a software keystore to a hardware keystore (ensure that the TDE master encryption key was configured for the hardware keystore)

  • The 11.2.0.0 setting enables resetting and rotating the TDE master encryption key

Be aware that once you set this parameter to 11.2.0.0, the change is irreversible. To use tablespace encryption, ensure that the compatibility setting is at the minimum, which is 11.1.0.0.

See Also:

Setting the COMPATIBLE Initialization Parameter for Tablespace Encryption

  1. Log in to the database instance.

    In a multitenant environment, log in to the PDB. For example:

    sqlplus sec_admin@hrpdb
    Enter password: password
    Connected.
    

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

  2. Check the current setting of the COMPATIBLE parameter.

    For example:

    SHOW PARAMETER COMPATIBLE
     
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    compatible                           string      12.0.0.0
    noncdbcompatible                     BOOLEAN     FALSE
    
  3. If you must change the COMPATIBLE parameter, then complete the remaining steps in this procedure.

    The value should be 11.2.0.0 or higher.

  4. Locate the initialization parameter file for the database instance.

    • UNIX systems: This file is in the ORACLE_HOME/dbs directory and is named initORACLE_SID.ora (for example, initmydb.ora).

    • Windows systems: This file is in the ORACLE_HOME\database directory and is named initORACLE_SID.ora (for example, initmydb.ora).

  5. In SQL*Plus, connect as a user who has the SYSDBA administrative privilege, and then shut down the database.

    For example:

    CONNECT /AS SYSDBA
    SHUTDOWN
    
  6. Edit the initialization parameter file to use the correct COMPATIBLE setting.

    For example:

    COMPATIBLE = 12.2.0.0
    
  7. In SQL*Plus, ensure that you are connected as a user who has the SYSDBA administrative privilege, and then start the database.

    For example:

    CONNECT /AS SYSDBA
    STARTUP
    

    If tablespace encryption is in use, then open the keystore at the database mount. The keystore must be open before you can access data in an encrypted tablespace.

    STARTUP MOUNT;
    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password;
    ALTER DATABASE OPEN;
    

Step 2: Set the Tablespace TDE Master Encryption Key

Ensure that you configure the TDE master encryption key, as described in the following sections:

Step 3: Create the Encrypted Tablespace

After you have set the COMPATIBLE initialization parameter, you are ready to create the encrypted tablespace.

This section contains the following topics:

About Creating Encrypted Tablespaces

To create an encrypted tablespace, you use the CREATE TABLESPACE SQL statement. You must have the CREATE TABLESPACE system privilege to create an encrypted tablespace.

You cannot change an existing tablespace to make it encrypted. You can, however, import data into an encrypted tablespace by using Oracle Data Pump. You can also use a SQL statement such as CREATE TABLE...AS SELECT...or ALTER TABLE...MOVE... to move data into an encrypted tablespace. The CREATE TABLE...AS SELECT... statement creates a table from an existing table. The ALTER TABLE...MOVE... statement moves a table into the encrypted tablespace.

For security reasons, you cannot encrypt a tablespace with the NO SALT option.

You can query the ENCRYPTED column of the DBA_TABLESPACES and USER_TABLESPACES data dictionary views to verify if a tablespace was encrypted.

See Also:

Oracle Database Reference for more information about these data dictionary views

Creating an Encrypted Tablespace

  1. Log in to the database instance as a user who has been granted the CREATE TABLESPACE system privilege.

    In a multitenant environment, log in to the PDB. For example:

    sqlplus sec_admin@hrpdb as syskm
    Enter password: password
    Connected.
    

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

  2. Run the CREATE TABLESPACE statement, using its encryption clauses.

    For example:

    CREATE TABLESPACE encrypt_ts
      DATAFILE '$ORACLE_HOME/dbs/encrypt_df.dbf' SIZE 1M
      ENCRYPTION USING 'AES256'
      DEFAULT STORAGE (ENCRYPT);
    

    In this specification:

    • ENCRYPTION USING 'AES256' specifies the encryption algorithm and the key length for the encryption. Enclose this setting in single quotation marks (' '). The key lengths are included in the names of the algorithms. If you do not specify an encryption algorithm, then the default encryption algorithm, AES128, is used. Choose from the following algorithms:

      • 3DES168

      • AES128

      • AES192

      • AES256

    • ENCRYPT in the DEFAULT STORAGE clause encrypts the tablespace.

See Also:

Oracle Database SQL Language Reference for the CREATE TABLESPACE statement syntax

Examples of Encrypted Tablespaces

Example 3-18 creates a tablespace called securespace_1 that is encrypted using the 3DES algorithm. The key length is 168 bits.

Example 3-18 Creating an Encrypted Tablespace

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

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

Example 3-19 Creating an Encrypted Tablespace

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

You cannot encrypt an existing tablespace.

Finding Information About Transparent Data Encryption Data

Table 3-2 describes dynamic and data dictionary views that you can query to find more information about Transparent Data Encryption data.

Table 3-2 Transparent Data Encryption Related Views

View Description

ALL_ENCRYPTED_COLUMNS

Displays encryption information about encrypted columns in the tables accessible to the current user

DBA_ENCRYPTED_COLUMNS

Displays encryption information for all of the encrypted columns in the database

USER_ENCRYPTED_COLUMNS

Displays encryption information for encrypted table columns in the current user's schema

V$CLIENT_SECRETS

Lists the properties of the strings (secrets) that were stored in the keystore for various features (clients).

In a multitenant environment, when you query this view in a PDB, then it displays information about keys that were created or activated for the current PDB. If you query this view in the root, then it displays this information about keys for all of the PDBs.

V$ENCRYPTED_TABLESPACES

Displays information about the tablespaces that are encrypted

V$ENCRYPTION_KEYS

Displays information about the TDE master encryption keys.

In a multitenant environment, when you query this view in a PDB, it displays information about keys that were created or activated for the current PDB. If you query this view in the root, it displays this information about keys for all of the PDBs.

V$ENCRYPTION_WALLET

Displays information on the status of the keystore and the keystore location for TDE

V$WALLET

Displays metadata information for a PKI certificate, which can be used as a master encryption key for TDE


See Also:

Oracle Database Reference for detailed information about these views