6 Using Transparent Data Encryption with Other Oracle Features

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

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

Oracle Data Pump enables you to encrypt entire dump sets, as opposed to encrypting just Transparent Data Encryption columns.

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

Password: password_for_hr

For software keystores, Oracle Database enables Oracle Real Application Clusters (Oracle RAC) nodes to share the keystore. Hardware security modules use a network connection for each database instance.

This eliminates the need to manually copy and synchronize the software keystore across all of the nodes. 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 hardware security modules, use a network connection for each database instance. Thus, all database instances have access to the hardware security module.

Keystore operations that must be performed or synchronized on all of the instances, such as opening or closing the keystore or rekeying can be performed on any one Oracle RAC instance. The synchronization operation applies to all of the other Oracle RAC instances in the cluster. This means that when you open and close a keystore for one instance, then it opens and closes for all of the Oracle RAC instances. Similarly, a TDE master encryption key rekey operation that you perform on one database instance applies to all of the database 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.

If you are not using a shared file system to store the software keystore, then you must copy the keystore to all of the nodes after a TDE master encryption key rekey.

You can use Transparent Data Encryption for both columns and tablespaces in a multitenant environment.

Note the following:

• The keystore that you create resides in the host multitenant environment, not within any particular PDB. Multiple PDBs can access a single keystore while running on this host. Each PDB that uses encryption has a Transparent Data Encryption TDE master encryption key stored in this keystore.

• Each PDB has its own TDE master encryption key. You must manage the TDE master encryption key for each PDB from within the PDB only, using the PDB-specific key management ADMINISTER KEY MANAGEMENT statements. From the root or a PDB, you can query the appropriate views to find information about the TDE master encryption keys of the PDBs in a CDB. For example, the PDBID column of the V$ENCYRYPTION_KEYS view indicates the PDBs to which a TDE master encryption key belongs.

• You can manage the Transparent Data Encryption TDE master encryption keys independently within the keystore for each PDB. You can rotate the TDE master encryption keys for each PDB individually. See "Exporting and Importing the TDE Master Encryption Key" for more information.

• You perform most of the keystore operations from the root. Keystore operations such as rotating a keystore password, merging keystores, and so on must be performed in the root. There are a few key management operations that you can perform within a PDB, such as opening, closing, resetting, and creating keys. The operations can also be performed for all of the PDBs from the root. Where applicable, the ADMINISTER KEY MANAGEMENT statement has the CONTAINER clause. Setting CONTAINER=ALL performs the action on all of the PDBs.

  See the following sections for more information:

  • "Operations That Must Be Performed in Root"

  • "Operations That Can Be Performed in Root or in a PDB"

• If you plan to move a PDB that uses Transparent Data Encryption to a new host computer, then you must move its TDE master encryption key as well. To move the TDE master encryption key from one host computer to another, use the procedures described in "Exporting and Importing the TDE Master Encryption Key".

You must perform specific ADMINISTER KEY MANAGEMENT keystore operations only in the root.

These operations are as follows:

• Creating password-based software keystores, using the ADMINISTER KEY MANAGEMENT CREATE KEYSTORE statement

• Creating auto-login software keystores, using the ADMINISTER KEY MANAGEMENT CREATE [LOCAL] AUTO_LOGIN KEYSTORE FROM KEYSTORE statement

• Changing the software keystore password, using the ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD statement

• Merging software keystores, using the ADMINISTER KEY MANAGEMENT MERGE KEYSTORE statement

• Backing up software keystores, using the ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE keystore

• Migrating from a software keystore to a hardware keystore, using the ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY... MIGRATE USING statement

• Reverse migrating from a hardware security module to a software keystore, using the ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY... REVERSE MIGRATE statement

• Adding, updating, and deleting secrets, using the ADMINISTER KEY MANAGEMENT ADD|UPDATE|DELETE SECRET statement

You can perform the some keystore operations in either the root or a PDB.

These operations are as follows:

• Opening keystores, using the ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN statement

• Closing keystores, using the ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE statement

You can perform the following key management operations either in the root or a PDB:

• Creating a tag for the TDE master encryption key, using the ADMINISTER KEY MANAGEMENT SET TAG statement

• Creating a TDE master encryption key, using the ADMINISTER KEY MANAGEMENT CREATE KEY statement

• Resetting or rotating the TDE master encryption key, using the ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY statement

• Activating a TDE master encryption key, using the ADMINISTER KEY MANAGEMENT USE KEY statement

• Exporting TDE master encryption keys, using the ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS statement

• Importing TDE master encryption keys, using the ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS statement

To export or import TDE master encryption keys for a PDB, you use the ADMINISTER KEY MANAGEMENT EXPORT and ADMINISTER KEY MANAGEMENT IMPORT statements.

Topics:

• About Exporting and Importing TDE Master Encryption Keys for a PDB

• Exporting or Importing a TDE Master Encryption Key for a PDB

• Example: Exporting a TDE Master Encryption Key from a PDB

• Example: Importing a TDE Master Encryption Key into a PDB

You can add or remove PDBs that have encrypted data to and from a CDB.

Topics:

• Exporting a TDE Master Encryption Key from a PDB

• Plugging a PDB That Has Encrypted Data into a CDB

You should be aware of how keystore open and close operations work in a multitenant environment.

For each PDB in a multitenant environment, you must explicitly open the password-based software keystore or hardware keystore in the PDB to enable the Transparent Data Encryption operations to proceed. (Auto-login and local auto-login software keystores open automatically.) Closing a keystore on a PDB blocks all of the Transparent Data Encryption operations on that PDB.

In a CDB, the open and close keystore operations in a PDB depends on the open and close status of the keystore in the root.

Note the following:

• Before you can manually open a software password-based or hardware keystore in an individual PDB, you must open the keystore in the root.

• Before you can set a TDE master encryption key in an individual PDB, you must set the key in the root.

• Auto-login and local auto-login software keystores open automatically. You do not need to manually open these from the root first, or from the PDB.

• If you close a keystore in the root, then the keystores in the dependent PDBs also close. A keystore close operation in the root is the equivalent of performing a keystore close operation with the CONTAINER clause set to ALL.

• If you open a keystore in the root and set the CONTAINER clause to ALL, then the keystores in the dependent PDBs also open.

The V$ENCRYPTION_WALLET view displays the status of the keystore in a PDB, whether it is open, closed, uses a software or hardware keystore, and so on. You can create a convenience function that uses this view to find the status for keystores in all of the PDBs in a CDB.

CREATE OR REPLACE PROCEDURE all_pdb_v$encryption_wallet
IS
    err_occ            BOOLEAN;
    curr_pdb           VARCHAR2(30);
    pdb_name           VARCHAR2(30);
    wrl_type           VARCHAR2(20);
    status             VARCHAR2(30);
    wallet_type        VARCHAR2(20);
    wallet_order       VARCHAR2(12);
    fully_backed_up    VARCHAR2(15);
    wrl_parameter      VARCHAR2(4000);
    cursor sel_pdbs IS SELECT NAME FROM V$CONTAINERS
                       WHERE NAME <> 'PDB$SEED' order by con_id desc;
  BEGIN
 
    -- Store the original PDB name
    SELECT sys_context('userenv', 'con_name') INTO curr_pdb FROM DUAL;
    IF curr_pdb <> 'CDB$ROOT' THEN
      dbms_output.put_line('Operation valid in ROOT only');
    END IF;
 
    err_occ := FALSE;
    dbms_output.put_line('---');
    dbms_output.put_line('PDB_NAME                       WRL_TYPE STATUS                        ');
    dbms_output.put_line('------------------------------ -------- ------------------------------');
    dbms_output.put_line('WALLET_TYPE          WALLET_ORDER FULLY_BACKED_UP');
    dbms_output.put_line('-------------------- ------------ ---------------');
    dbms_output.put_line('WRL_PARAMETER');
    dbms_output.put_line('--------------------------------------------------------------------------');
    FOR pdbinfo IN sel_pdbs LOOP
 
      pdb_name := DBMS_ASSERT.ENQUOTE_NAME(pdbinfo.name, FALSE);
      EXECUTE IMMEDIATE 'ALTER SESSION SET CONTAINER = ' || pdb_name;
 
      BEGIN
        pdb_name := rpad(substr(pdb_name,1,30), 30, ' ');
        EXECUTE IMMEDIATE 'SELECT wrl_type from V$ENCRYPTION_WALLET' into wrl_type; 
        wrl_type := rpad(substr(wrl_type,1,8), 8, ' ');
        EXECUTE IMMEDIATE 'SELECT status from V$ENCRYPTION_WALLET' into status;
        status := rpad(substr(status,1,30), 30, ' ');
        EXECUTE IMMEDIATE 'SELECT wallet_type from V$ENCRYPTION_WALLET' into wallet_type;
        wallet_type := rpad(substr(wallet_type,1,20), 20, ' ');
        EXECUTE IMMEDIATE 'SELECT wallet_order from V$ENCRYPTION_WALLET' into wallet_order;        
        wallet_order := rpad(substr(wallet_order,1,9), 12, ' ');
        EXECUTE IMMEDIATE 'SELECT fully_backed_up from V$ENCRYPTION_WALLET' into fully_backed_up;
        fully_backed_up := rpad(substr(fully_backed_up,1,9), 15, ' ');
        EXECUTE IMMEDIATE 'SELECT wrl_parameter from V$ENCRYPTION_WALLET' into wrl_parameter;
        wrl_parameter := rpad(substr(wrl_parameter,1,79), 79, ' ');
        dbms_output.put_line(pdb_name || ' ' || wrl_type || ' ' || status);
        dbms_output.put_line(wallet_type || ' ' || wallet_order || ' ' || fully_backed_up);
        dbms_output.put_line(wrl_parameter);
 
      EXCEPTION
        WHEN OTHERS THEN
        err_occ := TRUE;
      END;
    END LOOP;
 
    IF err_occ = TRUE THEN
       dbms_output.put_line('One or more PDB resulted in an error');
    END IF;
  END;
.
/
set serveroutput on
exec all_pdb_v$encryption_wallet;