8 Using Transparent Data Encryption with Other Oracle Features

You can use Oracle Data Encryption with other Oracle features, such as Oracle Data Guard or Oracle Real Application Clusters.

How Transparent Data Encryption Works with Export and Import Operations

Oracle Data Pump can export and import tables that contain encrypted columns, as well as encrypt entire dump sets.

About Exporting and Importing Encrypted Data

You can use Oracle Data Pump to export and import tables that have encrypted columns.

For both software and hardware keystores, the following points are important when you must export 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.

When you use Oracle Data Pump to export and import tables containing encrypted columns, it uses the ENCRYPTION parameter to enable encryption of data in dump file sets. The ENCRYPTION parameter allows the following values:

  • ENCRYPTED_COLUMNS_ONLY: Writes encrypted columns to the dump file set in encrypted format

  • DATA_ONLY: Writes all of the data to the dump file set in encrypted format

  • METADATA_ONLY: Writes all of the metadata to the dump file set in encrypted format

  • ALL: Writes all of the data and metadata to the dump file set in encrypted format

  • NONE: Does not use encryption for dump file sets

Exporting and Importing Tables with Encrypted Columns

You can export and import tables with encrypted columns using the ENCRYPTION=ENCRYPTED_COLUMNS_ONLY setting.

  1. Ensure that the keystore is open before you attempt to export tables containing encrypted columns.

    In a multitenant environment, if you are exporting data in a pluggable database (PDB), then ensure that the wallet is open in the PDB. If you are exporting into the root, then ensure that the wallet is open in the root.

    To find if the keystore is open, query the STATUS column of the V$ENCRYPTION_WALLET view. If you must open the keystore, then run the following SQL statement:

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

    The software_keystore_password setting is the password for the keystore. The keystore must be open because the encrypted columns must be decrypted using the TDE table keys, which requires access to the TDE master encryption key. The columns are reencrypted using a password, before they are exported.

  2. Run the EXPDP command, using 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. The ENCRYPTION_PWD_PROMPT = YES setting enables you to prompt for the password interactively, which is a recommended security practice.

    expdp hr TABLES=employee_data DIRECTORY=dpump_dir
    DUMPFILE=dpcd2be1.dmp ENCRYPTION=ENCRYPTED_COLUMNS_ONLY
    ENCRYPTION_PWD_PROMPT = YES
    
    Password: password_for_hr
    
  3. To import the exported data into the target database, ensure that you specify the same password that you used for the export operation, as set by the ENCRYPTION_PASSWORD parameter.

    The password is used to decrypt the data. Data is reencrypted with the new TDE table keys generated in the target database. The target database must have the keystore open to access the TDE master encryption key. The following example imports the employee_data table:

    impdp hr TABLES=employee_data DIRECTORY=dpump_dir 
    DUMPFILE=dpcd2be1.dmp 
    ENCRYPTION_PWD_PROMPT = YES
    
    Password: password_for_hr

Using Oracle Data Pump to Encrypt Entire Dump Sets

Oracle Data Pump can encrypt entire dump sets, not just Transparent Data Encryption columns.

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

You can use the ENCRYPTION_MODE=TRANSPARENT setting to transparently encrypt the dump file set with the TDE master encryption key stored in the keystore. A password is not required in this case. The keystore must be present and open at the target database, and it must contain the TDE master encryption key from the source database for a successful decryption of column encryption metadata during an import operation.

The open keystore is also required to reencrypt column encryption metadata at the target database. If a keystore already exists on the target database, then you can export the current TDE master encryption key from the keystore of the source database and import it into the keystore of the target database.

  • Use the ENCRYPTION_MODE parameter to specify the encryption mode. ENCRYPTION_MODE=DUAL encrypts the dump set using the TDE master encryption key stored in the keystore and the password provided.

For example, to use dual encryption mode to export encrypted data:

expdp hr DIRECTORY=dpump_dir1 
DUMPFILE=hr_enc.dmp
ENCRYPTION=all 
ENCRYPTION_PASSWORD=encryption_password
ENCRYPTION_PWD_PROMPT=yes
ENCRYPTION_ALGORITHM=AES256 
ENCRYPTION_MODE=dual

Password: password_for_hr
Encryption Password: password_for_encryption

Using Oracle Data Pump with Encrypted Data Dictionary Data

Oracle Data Pump operations provide protections for encrypted passwords and other encrypted data.

When you enable the encryption of fixed-user database passwords in a source database, then an Oracle Data Pump export operation dump stores a known invalid password for the database link password. This password is in place instead of the encrypted password that the export operation extracts from the database. An ORA-39395: Warning: object <database link name> requires password reset after import warning message is displayed as a result. If you import data into an Oracle Database 18c or later database, then this same warning appears when the database link object with its invalid password is created in the target database. When this happens, you must reset the database link password, as follows:

ALTER DATABASE LINK database_link_name CONNECT TO schema_name IDENTIFIED BY password;

To find information about the database link, you can query the V$DBLINK dynamic view.

When the encryption of fixed-user database passwords has been disabled in a source database, then there are no changes to Data Pump. The obfuscated database link passwords are exported and imported as in previous releases.

In this case, Oracle recommends the following:

  • Set the ENCRYPTION_PASSWORD parameter on the expdp command so that you can further protect the obfuscated database link passwords.

  • Set the ENCRYPTION_PWD_PROMPT parameter to YES so that the password can be entered interactively from a prompt, instead of being echoed on the screen.

Both the ENCRYPTION_PASSWORD and the ENCRYPTION_PWD_PROMPT parameters are available in import operations. ENCRYPTION_PWD_PROMPT is only available with the expdp and impdp command-line clients, whereas ENCRYPTION_PASSWORD is available in both the command-line clients and the DBMS_DATAPUMP PL/SQL package.

During an import operation, whether the keystore is open or closed affects the behavior of whether or not an encryption password must be provided. If the keystore was open during the export operation and you provided an encryption password, then you do not need to provide the password during the import operation. If the keystore is closed during the export operation, then you must provide the password during the import operation.

How Transparent Data Encryption Works with Oracle Data Guard

For both software keystores and hardware keystores, Oracle Data Guard supports Transparent Data Encryption (TDE).

If the primary database uses TDE, then each standby database in a Data Guard configuration must have a copy of the encryption keystore from the primary database. If the primary database uses TDE, then each standby database in a Data Guard configuration must have an encryption keystore with the keystore from the primary database merged into it. If you reset the TDE master encryption key in the primary database, then you must merge the keystore on the primary database that contains the TDE master encryption key to each standby database.

Note the following:

  • Encrypted data in log files remains encrypted when data is transferred to the standby database. Encrypted data also stays encrypted during transit.

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

  • Materialized views work with TDE tablespace encryption. You can create both materialized views and materialized view logs in encrypted tablespaces. Materialized views also work with TDE column encryption.

How Transparent Data Encryption Works with Oracle Real Application Clusters

Oracle RAC nodes can share both a software keystore and a hardware security module.

About Using Transparent Data Encryption with Oracle Real Application Clusters

Oracle Database enables Oracle RAC nodes to share a software keystore.

A configuration with a hardware security module uses a network connection from each instance of the database to the shared hardware security module. This eliminates the need to manually copy the software keystore to each of the other nodes in the cluster. Oracle recommends that you create the software keystore on a shared file system. This enables all of the instances to access the same shared software keystore. If you configure Oracle RAC to use Automatic Storage Management (ASM), then store the keystore on the ASM disk group.

For a hardware security module based configuration, configure the database instance on each Oracle RAC node to connect to the shared hardware security module. Thus, all instances of the database have access to the hardware security module.

Keystore operations that must be performed on all of the instances (such as opening or closing the keystore, or rekeying the TDE master encryption key) can be issued on any one Oracle RAC instance. Internally, the Oracle database takes care of synchronizing the keystore context on each Oracle RAC node, so that the effect of the keystore operation is visible to all of the other Oracle RAC instances in the cluster. This means that when you open the keystore on one instance, then it also opens for each of the other Oracle RAC instances. Similarly, when a TDE master encryption key rekey operation takes place, the new key becomes available to each of the Oracle RAC instances. This means that when you open the keystore on one database instance, it opens for each of the other Oracle RAC instances. You can perform other keystore operations, such as exporting TDE master encryption keys, rotating the keystore password, merging keystores, or backing up keystores, from a single instance only.

When using a shared file system, ensure that the ENCRYPTION_WALLET_LOCATION or WALLET_LOCATION parameter setting in the sqlnet.ora file for all of the Oracle RAC instances point to the same shared software keystore location. You also must ensure security of the shared software keystore by assigning the appropriate directory permissions.

Using a Non-Shared File System to Store a Software Keystore in Oracle RAC

If you do not use a shared file system to store the software keystore, then you must copy the keystore to the associated nodes.

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

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

    sqlplus sec_admin@hrpdb as syskm
    Enter password: password
    Connected.
    
  2. Reset the TDE master encryption key on the first Oracle Real Application Clusters (Oracle RAC) node.

    For example, for column encryption:

    ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keystore_password WITH BACKUP USING 'emp_key_backup';
    
  3. Copy the keystore file with the new TDE master encryption key from the first node to all of the other nodes.

    To find the keystore file location, query the WRL_PARAMETER column in the V$ENCRYPTION_WALLET view. To find the WRL_PARAMETER settings for all of the database instances, query the GV$ENCRYPTION_WALLET view.

  4. Close and then reopen the keystore on any node.

    If you are using a multitenant container database (CDB), then run the following statement in the root:

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

Note:

Any keystore operation, such as opening or closing the keystore, performed on any one Oracle RAC instance applies to all other Oracle RAC instances. This is true even if you are not using a shared file system.

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

How Transparent Data Encryption Works with SecureFiles

SecureFiles, which stores LOBS, has three features: compression, deduplication, and encryption.

About Transparent Data Encryption and SecureFiles

SecureFiles encryption uses TDE to provide the encryption facility for LOBs.

When you create or alter tables, you can specify the SecureFiles encryption or LOB columns that must use the SecureFiles storage. You can enable the encryption for a LOB column by either using the current Transparent Data Encryption (TDE) syntax or by using the ENCRYPT clause as part of the LOB parameters for the LOB column. The DECRYPT option in the current syntax or the LOB parameters turn off encryption.

Example: Creating a SecureFiles LOB with a Specific Encryption Algorithm

The CREATE TABLE statement can create a SecureFiles LOB with encryption specified.

Example 8-1 shows how to create a SecureFiles LOB in a CREATE TABLE statement.

Example 8-1 Creating a SecureFiles LOB with a Specific Encryption Algorithm

CREATE TABLE table1 ( a BLOB ENCRYPT USING 'AES256')
    LOB(a) STORE AS SECUREFILE (
    CACHE
    );

Example: Creating a SecureFiles LOB with a Column Password Specified

The CREATE TABLE statement can create a SecureFiles LOB with a column password.

Example 8-2 shows an example of creating a SecureFiles LOB that uses password protections for the encrypted column.

All of the LOBS in the LOB column are encrypted with the same encryption specification.

Example 8-2 Creating a SecureFiles LOB with a Column Password Specified

CREATE TABLE table1 (a VARCHAR2(20), b BLOB)
    LOB(b) STORE AS SECUREFILE (
        CACHE
        ENCRYPT USING 'AES192' IDENTIFIED BY password
    );

How Transparent Data Encryption Works with Oracle Call Interface

Transparent Data Encryption does not have any effect on the operation of Oracle Call Interface (OCI).

For most practical purposes, TDE is transparent to OCI except for the row shipping feature. You cannot use the OCI row shipping feature with TDE because the key to make the row usable is not available at the receipt-point.

How Transparent Data Encryption Works with Editions

Transparent Data Encryption does not have any effect on the Editions feature of Oracle Database.

For most practical purposes, TDE is transparent to Editions. Tables are always noneditioned objects. TDE Column Encryption encrypts columns of the table. Editions are not affected by TDE tablespace encryption.

Configuring Transparent Data Encryption to Work in a Multidatabase Environment

Each Oracle database on the same server (such as databases sharing the same Oracle binary but using different data files) must access its own TDE keystore.

Keystores are not designed to be shared among databases. By design, there must be one keystore per database. You cannot use the same keystore for more than one database.

  • To configure the sqlnet.ora file for a multidatabase environment, use one of the following options:

    • Option 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 keystore from the default sqlnet.ora location if these two entries are not in the sqlnet.ora file.

    • Option 2: If Option 1 is not feasible for your site, then you can specify the keystore location as follows. The WALLET_ROOT initialization parameter is shared by all PDBs, but each CDB (or standalone database) must have its own separate WALLET_ROOT setting.

      For example:

      WALLET_ROOT = /home/oracle/db_unique_name/wallet/
       
    • Option 3: If Options 1 and 2 are not feasible, then use separate sqlnet.ora files, one for each database. Ensure that you correctly set the TNS_ADMIN environment variable to point to the correct database configuration.

Caution:

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