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

An Oracle Data Guard primary database and secondary secondary database can share both a software keystore and a hardware security module.

About Using Transparent Data Encryption 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 you reset the TDE master encryption key in the primary database, then you must copy the keystore from the primary database that contains the TDE master encryption key to each standby database.

Note the following:

  • Re-key operations with wallet-based TDE will cause the Managed Recovery Process (MRP) on the standby databases to fail because the new TDE master encryption key is not yet available. In order to circumvent this problem, use the ADMINISTER KEY MANAGEMENT CREATE KEY statement on the primary database to insert new TDE master encryption keys into the wallet. Copy the wallet to the standby databases, and then execute a ADMINISTER KEY MANAGEMENT USE KEY statement on the primary.

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

Configuring TDE and Oracle Key Vault in an Oracle Data Guard Environment

You can configure Oracle Data Guard in a multitenant environment so that it can work with TDE and Oracle Key Vault.

The following scenario shows the configuration with Oracle Key Vault in a single-instance, multitenant Oracle Data Guard environment with one physical standby database. The version for the primary and standby databases must be release 19.6 or later. To complete this procedure, you must perform each step in the sequence shown. After you complete the procedure, Oracle Data Guard will use Oracle Key Vault for TDE key management exclusively, and there will be no TDE wallet on your database servers. Oracle recommends that you monitor the alert logs of both primary and standby databases.
  1. On both the primary and standby databases, execute the opatch lspatches command to check the patch release.
    $ORACLE_HOME/OPatch/opatch lspatches

    Output similar to the following appears:

    31281355;Database Release Update : 19.7.0.0.200414 (30869156)
    29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
  2. Download the Oracle Key Vault deployment script that the Oracle Key Vault administrators prepared to enable database administrators to automatically register their Oracle databases with Oracle Key Vault.
    Oracle Key Vault Administrator's Guide has an example of how to create a script to automatically enroll Oracle databases as endpoints. The deployment scripts reside on a shared file system from which database administrators can download. There are two different versions of these deployment scripts. The primary.zip file is for the primary database, and the secondary.zip file is for all standby databases. You can use these scripts for an Oracle Data Guard or an Oracle RAC environment.
    Another component that the Oracle Key Vault administrators prepare and add to the deployment script is a configuration file that contains all details for the deployment scripts to connect to Oracle Key Vault.
  3. Copy the two deployment scripts (primary.zip and secondary.zip) that an Oracle Key Vault administrator created for database administrators to download from a shared location.
    1. Copy the primary.zip file to the primary database.
      $ scp user@ip_address:/path/to/file/primary.zip .
    2. Copy the secondary.zip file to the standby database.
      $ scp user@ip_address:/path/to/file/secondary.zip .
  4. On their respective servers, extract the zip files.
    $ unzip primary.zip
    
    $ unzip secondary.zip
  5. Execute the primary-run-me.sh and secondary-run-me.sh scripts, which contain the commands for the RESTful API to execute in Oracle Key Vault.
    The Oracle Key Vault RESTful services will execute these commands in order to register this database in Oracle Key Vault with unique wallet and endpoint names.
    1. Primary database: For example:
      $ ./primary-run-me.sh 
      
      create_wallet -w $ORACLE_SID
      create_endpoint -e $ORACLE_SID_on_short_host_name -t ORACLE_DB -q LINUX64 -d "long_host_name, 192.0.2.101"
      set_default_wallet -e $ORACLE_SID_on_short_host_name -w $ORACLE_SID
      provision -v default_password -e $ORACLE_SID_on_short_host_name -y Oracle_Key_Vault_installation_directory
    2. Standby database: For example:
      $ ./secondary-run-me.sh 
      
      create_endpoint -e $ORACLE_SID_on_short_host_name -t ORACLE_DB -q LINUX64 -d "long_host_name, 192.0.2.102"
      set_default_wallet -e $ORACLE_SID_on_short_host_name -w $ORACLE_SID
      provision -v default_password -e $ORACLE_SID_on_short_host_name -y Oracle_Key_Vault_installation_directory
  6. Create the following directories on the primary database and the standby database.
    For example:
    $ mkdir -pv /u01/opt/oracle/product/okv 
    $ mkdir -pv /u01/opt/oracle/product/tde 
    $ mkdir -pv /u01/opt/oracle/product/tde_seps

    In this specification:

    • The /u01/opt/oracle/product directory will be defined as WALLET_ROOT in a later step.
    • /u01/opt/oracle/product/okv is the installation directory for the Oracle Key Vault client software. Depending on how the TDE_CONFIGURATION parameter is set, the Oracle Database will look for the Oracle Key Vault client software in wallet_root/okv.
    • /u01/opt/oracle/product/tde will store an auto-login wallet, which only contains the future Oracle Key Vault password, enabling an auto-login Oracle Key Vault configuration. Depending on how TDE_CONFIGURATION is set, the Oracle Database will look for the TDE wallet or an auto-open wallet for Oracle Key Vault, in wallet_root/tde.
    • /u01/opt/oracle/product/tde_seps will store an auto-login wallet, which only contains the future Oracle Key Vault password. This will hide the Oracle Key Vault password from the SQL*Plus command line and potentially from the database administrator to enforce separation of duties between Oracle database administrators and Oracle Key Vault administrators.
  7. Execute the RESTful API on the primary database first, because the deployment script on the standby databases depends on the presence of the shared virtual wallet in Oracle Key Vault that the script on the primary database creates.
    $ java -jar okvrestservices.jar -c config.ini

    The following message should appear:

    The endpoint software for Oracle Key Vault installed successfully
  8. On the primary and standby databases, execute the root.sh script to deploy the PKCS#11 library.
    # /u01/opt/oracle/product/okv/bin/root.sh

    The following output should appear:

    Creating directory: /opt/oracle/extapi/64/hsm/oracle/1.0.0/
    Copying PKCS library to /opt/oracle/extapi/64/hsm/oracle/1.0.0/
    Setting PKCS library file permissions
  9. Execute the okvutil changepwd command to change the password for the wallet that you installed, starting from the primary database and then to the standby.
    Because all database administrators downloaded the same deployment script, all databases have the same password into Oracle Key Vault. This step enables each database to have a unique password.
    $ /u01/opt/oracle/product/okv/bin/okvutil changepwd -t wallet -l /u01/opt/oracle/product/okv/ssl/
    
    Enter wallet password: default_password
    Enter new wallet password: Oracle_Key_Vault_password
    Confirm new wallet password: Oracle_Key_Vault_password
    Wallet password changed successfully
  10. On the primary and standby databases, execute the following statements.
    1. Execute the following statement to add the Oracle Key Vault password as a secret into an auto-open wallet to replace the Oracle Key Vault password in the SQL*Plus command line with EXTERNAL STORE.
      ADMINISTER KEY MANAGEMENT ADD SECRET 'Oracle_Key_Vault_password' 
      FOR CLIENT 'OKV_PASSWORD' 
      TO LOCAL AUTO_LOGIN KEYSTORE '/u01/opt/oracle/product/tde_seps';
      
    2. Execute the following statement to add the Oracle Key Vault password as a secret into an auto-open wallet to enable auto-open Oracle Key Vault.
      ADMINISTER KEY MANAGEMENT ADD SECRET 'Oracle_Key_Vault_password' 
      FOR CLIENT 'HSM_PASSWORD' 
      TO LOCAL AUTO_LOGIN KEYSTORE '/u01/opt/oracle/product/tde';
      
    3. Configure the primary and standby databases to always encrypt new tablespaces:
      ALTER SYSTEM SET ENCRYPT_NEW_TABLESPACES = ALWAYS SCOPE = BOTH;
    4. Optionally, if patch 30398099 is installed, then change the database default algorithm from AES128 to either AES192 or AES256.
      Note that the following parameter is preceded by an underscore.
      ALTER SYSTEM SET "_tablespace_encryption_default_algorithm" = 'AES256' SCOPE = BOTH;
      
    5. In the primary and standby databases, define the WALLET_ROOT static initialization parameter:
      ALTER SYSTEM SET WALLET_ROOT = '/u01/opt/oracle/product' SCOPE = SPFILE;
    6. Restart the primary and standby databases so that the preceding ALTER SYSTEM SET WALLET_ROOT statement takes effect.
    7. After the database restarts, configure TDE to use Oracle Key Vault as the first keystore and the auto-open wallet in WALLET_ROOT/tde as the secondary keystore.
      Execute the following statement in both the primary and standby databases:
      ALTER SYSTEM SET TDE_CONFIGURATION = "KEYSTORE_CONFIGURATION=OKV|FILE" SCOPE = BOTH;
      
  11. In the primary database, create your first TDE master encryption keys in Oracle Key Vault.
    Check the alert.log of the standby database. The managed recovery process (MRP) should not be stopped, since the standby database finds the correct master key in the shared virtual wallet in Oracle Key Vault.
    1. Primary root container: Set the first master encryption key.

      For all ADMINISTER KEY MANAGEMENT statements that do not change the TDE configuration, the password will be replaced by EXTERNAL STORE. This enables separation of duties between the database administrators and the Oracle Key Vault administrators because the Oracle Key Vault administrators do not need to share the Oracle Key Vault password with the database administrators.

      sqlplus sys as syskm
      Enter password: password
      
      ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY EXTERNAL STORE 
      CONTAINER = CURRENT;
    2. All primary PDBs: Set the first, tagged, master key for each open PDB. The benefit of tagging the PDB keys is that they can later be easily identified to belong to a certain PDB.
      SELECT ' ADMINISTER KEY MANAGEMENT SET KEY 
      USING TAG '''||SYS_CONTEXT('USERENV', 'CON_NAME')||' '|| 
      TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS')||''' 
      IDENTIFIED BY EXTERNAL STORE;' "SET KEY COMMAND" FROM DUAL;
    3. Execute the generated output of this SELECT statement.
      For example:
      ADMINISTER KEY MANAGEMENT SET KEY 
      USING TAG 'pdb_name date time' 
      IDENTIFIED BY EXTERNAL STORE;
  12. Perform the following steps in the root container.
    1. Optionally, encrypt the USERS tablespace in the root container. While technically possible, you should not encrypt the SYSTEM, SYSAUX, TEMP, and UNDO tablespaces of the root container.
      For example:
      ALTER TABLESPACE USERS ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
    2. Observe the alert.log of the standby database to confirm that the USERS tablespace there is also encrypted.
    3. As a user with the SYSKM administrative privilege, encrypt the data dictionary with the AES256 algorithm.
      ALTER DATABASE DICTIONARY ENCRYPT CREDENTIALS CONTAINER = CURRENT;
  13. Optionally, encrypt the USERS, SYSTEM, and SYSAUX tablespaces in the PDBs.
    Encrypting the TEMP and UNDO tablespaces is optional because data from encrypted tablespaces is tracked and automatically encrypted before being written into TEMP or UNDO.
    ALTER TABLESPACE USERS ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
    
    ALTER TABLESPACE SYSTEM ENCRYPTION ONLINE ENCRYPT;
    
    ALTER TABLESPACE SYSAUX ENCRYPTION ONLINE USING 'AES256' ENCRYPT;

    The SYSTEM tablespace can only be encrypted with the database default algorithm, which is AES128 unless it has been changed after you applied patch 30398099. If you do not have this patch and want to encrypt the SYSTEM tablespace with AES256, you must rekey the SYSTEM tablespace to use AES256. For example:

    ALTER TABLESPACE SYSTEM ENCRYPTION ONLINE USING 'AES256' REKEY;

    Observe the alert.log of the standby database to confirm the encryption and rekey operations are applied there as well.

  14. Create a tablespace and table in the primary database PDB.
    When you create the tablespace in the primary database even though there are no encryption keywords in that statement. It will be encrypted with AES128 by default unless the database default algorithm has been changed after patch 30398099 has been applied. Observe the alert.log of the standby database to confirm the encrypted tablespace is created there as well.
    CREATE TABLESPACE protected DATAFILE SIZE 50M;
    
    CREATE TABLE SYSTEM.TEST TABLESPACE protected 
    AS SELECT * FROM DBA_OBJECTS;
  15. Confirm that you can select from the table that is stored in an encrypted tablespace.
    SELECT COUNT(*), OWNER FROM SYSTEM.TEST 
    GROUP BY OWNER 
    ORDER BY 1 DESC;
  16. On the standby database, execute the following query to list the encrypted tablespaces for the root container, all PDBs, and the encryption algorithm.
    SELECT C.NAME AS PDB_NAME, T.NAME AS TBS_NAME, E.ENCRYPTIONALG AS ALG 
    FROM V$TABLESPACE T, V$ENCRYPTED_TABLESPACES E, V$CONTAINERS C 
    WHERE E.TS# = T.TS# AND E.CON_ID = T.CON_ID AND E.CON_ID = C.CON_ID ORDER BY E.CON_ID, T.NAME;
    
    PDB_NAME        TBS_NAME             ALG     
    --------------- -------------------- ------- 
    CDB$ROOT        USERS                AES256  
    FINPDB          PROTECTED01          AES256 
    FINPDB          SYSAUX               AES256  
    FINPDB          SYSTEM               AES256  
    FINPDB          USERS                AES256  
    
  17. Optionally, validate the configuration.
    1. Perform an Oracle Data Guard switchover between the primary and standby databases.
      Perform the following steps in the new primary database.
    2. Select from the encrypted table in your PDB.
      Because there is an auto-open connection into Oracle Key Vault, the following query does not require that you enter the Oracle Key Vault password.
      SELECT COUNT(*), OWNER FROM SYSTEM.TEST 
      GROUP BY OWNER 
      ORDER BY 1 DESC;
      
      24 rows selected.
    3. Rekey the PDB.
      SELECT ' ADMINISTER KEY MANAGEMENT SET KEY 
      USING TAG '''||SYS_CONTEXT('USERENV', 'CON_NAME')||' '|| 
      TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS')||''' 
      IDENTIFIED BY EXTERNAL STORE;' "REKEY COMMAND" FROM DUAL;
    4. Execute the generated output of this SELECT statement.
      For example:
      ADMINISTER KEY MANAGEMENT SET KEY 
      USING TAG 'pdb_name date time' 
      IDENTIFIED BY EXTERNAL STORE;
    5. In the root container, as a user who has the SYSKM administrative privilege, rekey the data dictionary.
      ALTER DATABASE DICTIONARY REKEY CREDENTIALS CONTAINER = CURRENT;
    6. Perform another Oracle Data Guard switchover.
    7. Select from the encrypted table in your PDB.
      Because there is an auto-open connection into Oracle Key Vault, the following query does not require that you enter the Oracle Key Vault password.
      SELECT COUNT(*), OWNER FROM SYSTEM.TEST 
      GROUP BY OWNER 
      ORDER BY 1 DESC;
      
      24 rows selected.
    8. Rekey the PDB.
      SELECT ' ADMINISTER KEY MANAGEMENT SET KEY 
      USING TAG '''||SYS_CONTEXT('USERENV', 'CON_NAME')||' '|| 
      TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS')||''' 
      IDENTIFIED BY EXTERNAL STORE;' "REKEY COMMAND" FROM DUAL;
    9. Execute the generated output of this SELECT statement.
      For example:
      ADMINISTER KEY MANAGEMENT SET KEY 
      USING TAG 'pdb_name date time' 
      IDENTIFIED BY EXTERNAL STORE;

How Transparent Data Encryption Works with Oracle Real Application Clusters

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 Real Application Clusters (Oracle RAC) nodes to share a software keystore.

A TDE configuration with Oracle Key Vault or a PKCS11-compatible hardware security module uses a network connection from each instance of the database to the external key manager. In Oracle Key Vault, you must create one endpoint for each instance of the Oracle RAC-enabled database, and one virtual wallet for each Oracle RAC-enabled database. Then, make that virtual wallet the default wallet of all endpoints that belong to that database. In an Oracle RAC-enabled Data Guard configuration, all instances (primary and all standby databases) share that one virtual wallet. With this configuration, set key and re-key operations are completely transparent because all participating instances are automatically synchronized. 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.

Oracle does not support the use of individual TDE wallets for each Oracle RAC node. Instead, use shared wallets for TDE in the Oracle RAC environment. This enables all of the instances to access the same shared software keystore. If your site uses Oracle Automatic Storage Management Cluster File System (Oracle ACFS), then this is the preferred location for a shared wallet. Directly sharing the wallet in Oracle Automatic Storage Management (Oracle ASM) (for example, +DATA/$ORACLE_UNQNAME/WALLETS) is an alternative if Oracle ACFS is not available.

Keystore operations (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. Similarly, when a TDE master encryption key rekey operation takes place, the new key becomes available to each of the 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 WALLET_ROOT static system parameter for all of the Oracle RAC instances point to the same shared software keystore location, as follows:

ALTER SYSTEM SET WALLET_ROOT = '+DATA/$ORACLE_UNQNAME/WALLETS' SCOPE = SPFILE SID ='*'; 

ALTER SYSTEM SET TDE_CONFIGURATION = "KEYSTORE_CONFIGURATION=FILE" SCOPE = BOTH SID = '*';

Note:

  • If you have the ENCRYPTION_WALLET_LOCATION parameter set, then be aware this parameter is deprecated. Oracle recommends that you use the WALLET_ROOT static initialization parameter and TDE_CONFIGURATION dynamic initialization parameter instead.
  • Storing TDE master encryption keys in individual wallets per Oracle Real Application Clusters (Oracle RAC) instance is not supported. As an alternative, use Oracle Key Vault for centralized key management across your on-premises or Cloud-based database deployments, or Oracle Automatic Storage Management (Oracle ASM), or Oracle ASM Cluster File System (Oracle ACFS) to provide local shared wallets.

Configuring TDE in Oracle Real Application Clusters for Oracle Key Vault

You can configure TDE in Oracle Real Application Clusters (Oracle RAC) on Oracle Exadata Cloud at Customer (ExaCC) and other servers for centralized key management provided by Oracle Key Vault.

The following scenario assumes that you have a multitenant two-node Oracle RAC configuration. In this procedure, you must complete the following steps in the order shown. After you have completed this procedure, the Oracle RAC environment will exclusively use Oracle Key Vault for key management for Transparent Data Encryption. This procedure assumes that you have installed the January 2020 (19.6) or later upgrade for Oracle Database release 19c.
Before you begin, monitor the alert logs of your running Oracle RAC database. The Java version that is included in the default Oracle Database release 19c installation can be used to install the Oracle Key Vault client with the RESTful services. The provision command of the RESTful API requires a symbolic link in /usr/bin/java that points to the Java home in the Oracle database. For example:
# ln -sv $ORACLE_HOME/jdk/jre/java /usr/bin/java
  1. Download the Oracle Key Vault deployment script that the Oracle Key Vault administrators prepared to enable database administrators to automatically register their Oracle databases with Oracle Key Vault.
    Oracle Key Vault Administrator's Guide has an example of how to create a script to automatically enroll Oracle databases as endpoints. The deployment scripts reside on a shared file system from with database administrators can download. There are two different versions of these deployment scripts. One script is for only the first node (which is called lead node in this procedure) and the other script is for all other nodes (which are called secondary nodes in this procedure). You can use these scripts for an Oracle RAC or an Oracle Data Guard environment.
    Another component that the Oracle Key Vault administrators prepare and add to the deployment script is a configuration file that contains all details for the deployment scripts to connect to Oracle Key Vault.
  2. Copy the two deployment scripts (primary.zip and secondary.zip) that an Oracle Key Vault administrator created for database administrators to download from a shared location.
    1. Copy the primary.zip file to the lead node.
      $ scp user@ip_address:/path/to/file/primary.zip .
    2. Copy the secondary.zip file to each secondary node.
      $ scp user@ip_address:/path/to/file/secondary.zip .
  3. Extract the zip files.
    1. On the lead node: Extract the primary.zip file.
      $ unzip primary.zip
    2. On the secondary nodes: Extract the secondary.zip file.
      $ unzip secondary.zip
  4. Execute the primary-run-me.sh and secondary-run-me.sh scripts, which contain the commands for the RESTful API to execute in Oracle Key Vault.
    The Oracle Key Vault RESTful services will execute these commands in order to register this database in Oracle Key Vault with unique wallet and endpoint names.
    1. On the lead node: This script creates a shared wallet (for the lead and all secondary nodes) and an endpoint in Oracle Key Vault, associates this endpoint for the lead node with the shared wallet, and downloads and installs the Oracle Key Vault client into an existing installation directory. With the WALLET_ROOT configuration, this directory is wallet_root/okv.
      $ more primary-run-me.sh 
      #!/bin/bash
      export EP_NAME=${ORACLE_SID^^}_on_${HOSTNAME/.*}
      cat > /home/oracle/script.txt << EOF
      create_wallet -w $ORACLE_UNQNAME
      create_endpoint -e $EP_NAME -t ORACLE_DB -q LINUX64 -d "$HOSTNAME, $(hostname -i)"
      set_default_wallet -e $EP_NAME -w $ORACLE_UNQNAME
      provision -v default_password -e $EP_NAME -y Oracle_Key_Vault_installation_directory
      EOF
      more script.txt
      

      Output similar to the following appears:

      create_wallet -w database_name
      create_endpoint -e instance_name_on_short_host_name -t ORACLE_DB -q LINUX64 -d "full_host_name, node_IP_address"
      set_default_wallet -e instance_name_on_short_host_name -w database_name
      provision -v default_password -e instance_name_on_short_host_name -y Oracle_Key_Vault_installation_directory
    2. Secondary nodes: This script only creates an endpoint for the secondary nodes, associates the endpoint of the secondary nodes with the shared wallet, and downloads and installs the Oracle Key Vault client into the existing installation directory on each secondary node.
      $ more secondary-run-me.sh 
      #!/bin/bash
      export EP_NAME=${ORACLE_SID^^}_on_${HOSTNAME/.*}
      cat > /home/oracle/script.txt << EOF
      create_endpoint -e $EP_NAME -t ORACLE_DB -q LINUX64 -d "$HOSTNAME, $(hostname -i)"
      set_default_wallet -e $EP_NAME -w $ORACLE_UNQNAME
      provision -v default_password -e $EP_NAME -y Oracle_Key_Vault_installation_directory
      EOF
      more script.txt
      

      Output similar to the following appears:

      create_endpoint -e instance_name_on_short_host_name -t ORACLE_DB -q LINUX64 -d "full_host_name, node_IP_address"
      set_default_wallet -e instance_name_on_short_host_name -w database_name
      provision -v default_password -e instance_name_on_short_host_name -y Oracle_Key_Vault_installation_directory
  5. Create the following directories on all nodes:
    For example:
    $ mkdir -pv /u01/opt/oracle/product/okv 
    $ mkdir -pv /u01/opt/oracle/product/tde 
    $ mkdir -pv /u01/opt/oracle/product/tde_seps

    In this specification:

    • The /u01/opt/oracle/product directory will be defined as WALLET_ROOT in a later step.
    • /u01/opt/oracle/product/okv is the installation directory for the Oracle Key Vault client software. Depending on how the TDE_CONFIGURATION parameter is set, the Oracle Database will look for the Oracle Key Vault client software in wallet_root/okv.
    • /u01/opt/oracle/product/tde will store an auto-login wallet, which only contains the future Oracle Key Vault password, enabling an auto-login Oracle Key Vault configuration. Depending on how TDE_CONFIGURATION is set, the Oracle Database will look for the TDE wallet or an auto-open wallet for Oracle Key Vault, in wallet_root/tde.
    • /u01/opt/oracle/product/tde_seps will store an auto-login wallet, which only contains the future Oracle Key Vault password. This will hide the Oracle Key Vault password from the SQL*Plus command line and potentially from the database administrator to enforce separation of duties between Oracle database administrators and Oracle Key Vault administrators.
  6. Execute the RESTful API on the lead node first, because all secondary nodes depend on the presence of the shared wallet in Oracle Key Vault that the lead node creates.
    $ java -jar okvrestservices.jar -c config.ini

    Output similar to the following should appear:

    [Line 1 OK] [CREATE WALLET] [3E48990A-82A0-48BC-ACEC-FF80CB380D38]
    [Line 2 OK] [CREATE ENDPOINT] [6FA40F80-558C-456A-84E3-25AE73B245DD]
    [Line 3 OK] [SET DEFAULT WALLET] [FINRAC1_on_rac19a:FINRAC]
    [Line 4 OK] [GET ENROLLMENT TOKEN] [FINRAC1_on_rac19a]
    The endpoint software for Oracle Key Vault installed successfully.
    [Line 4 OK] [PROVISION] 
    [Line 4 OK] [CLEANUP] 

    Make a note of the UUID of the wallet, which appears in Line 1 and is in bold.

    After you execute this command on the lead node, execute it on all secondary nodes.

  7. After successful installation of the Oracle Key Vault client, execute the root.sh script to install the PKCS library on all nodes.
    # Oracle_Key_Vault_installation_directory/bin/root.sh

    The following output should appear:

    Creating directory: /opt/oracle/extapi/64/hsm/oracle/1.0.0/
    Copying PKCS library to /opt/oracle/extapi/64/hsm/oracle/1.0.0/
    Setting PKCS library file permissions
  8. Execute the Oracle Key Vault okvutil changepwd command on all nodes to change the password for the Oracle Key Vault client that you installed.
    Because all database administrators downloaded the same deployment script, all databases have the same password into Oracle Key Vault. This step enables each database to have a unique password.
    $ /u01/opt/oracle/product/okv/bin/okvutil changepwd -t wallet -l /u01/opt/oracle/product/okv/ssl/
    
    Enter wallet password: default_password
    Enter new wallet password: Oracle_Key_Vault_password
    Confirm new wallet password: Oracle_Key_Vault_password
    Wallet password changed successfully
  9. On all nodes, add the Oracle Key Vault password into a local auto-login wallet to hide the newly changed password from database administrators.
    sqlplus c##sec_admin as syskm
    Enter password: password
    
    ADMINISTER KEY MANAGEMENT ADD SECRET 'Oracle_Key_Vault_password' 
    FOR CLIENT 'OKV_PASSWORD' 
    TO LOCAL AUTO_LOGIN KEYSTORE '/u01/opt/oracle/product/tde_seps';
    
  10. In the root container, execute the ALTER SYSTEM statement to set the static WALLET_ROOT parameter to configure the encryption wallet location for all instances:
    CONNECT AS SYSDBA
    
    ALTER SYSTEM SET WALLET_ROOT = '/u01/opt/oracle/product/' 
    SCOPE = SPFILE SID = '*';
  11. Restart the database.
    Execute this command on any node.
    $ srvctl stop database -db database_name -o immediate 
    $ srvctl start database -db database_name
  12. Check if the wallet has been replicated across the Oracle Key Vault cluster nodes:
    $ java -jar okvrestservices.jar -c /u01/opt/oracle/product/okv/conf/okvclient.ora 
    -j /home/oracle -u restadmin -r check_object_status -b WALLET 
    -x 3E48990A-82A0-48BC-ACEC-FF80CB380D38

    In this specification:

    • -c /u01/opt/oracle/product/okv/conf/okvclient.ora enables the RESTful API to use a complete configuration file (okvclient.ora) to leverage Oracle Key Vault's clustering capabilities.
    • -j /home/oracle is the corresponding entry for the client_wallet entry in the config.ini file.
    • -u restadmin is the corresponding entry for the usr entry in the config.ini file.
    • -r check_object_status checks the status of an object in Oracle Key Vault.
    • -b WALLET is the object type that is being checked with -r check_object_status.
    • -x 3E48990A-82A0-48BC-ACEC-FF80CB380D38 is the generated UUID that appeared when the wallet was created in Oracle Key Vault in Step 6.

    Output similar to the following should appear. If the status is ACTIVE, then continue. If the status is PENDING, then wait until the status is ACTIVE.

    [Line 0 OK] [CHECK OBJECT STATUS] [ACTIVE]
  13. In the root container, use the ALTER SYSTEM statement to set the dynamic TDE_CONFIGURATION parameter.
    For example:
    ALTER SYSTEM SET TDE_CONFIGURATION = "KEYSTORE_CONFIGURATION=OKV" 
    SCOPE = BOTH SID = '*';
  14. Optionally, define the database default encryption algorithm after applying Oracle patch 30398099.
    By default, Oracle Database applies the AES128 algorithm to encryption clauses that do not specify an encryption algorithm. Patch 30398099 allows you to choose from the AES128, AES192, and AES256 encryption algorithms. If you have applied this patch, then you can execute the following command to set the encryption clause:
    ALTER SYSTEM SET "_TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM" = 'encryption_algorithm' 
    SCOPE = BOTH SID = '*';
    
  15. In the root container, open the keystore, which opens the connection to Oracle Key Vault for the root container and all open PDBs.
    Note that the Oracle Key Vault password has been replaced in all subsequent ADMINISTER KEY MANAGEMENT commands with EXTERNAL STORE, because the database automatically retrieves the Oracle Key Vault password from the local auto-login wallet that you created in Step 9.
    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN
    IDENTIFIED BY EXTERNAL STORE 
    CONTAINER = ALL;
  16. In the root container, set the master encryption key.
    ADMINISTER KEY MANAGEMENT SET KEY 
    IDENTIFIED BY EXTERNAL STORE 
    CONTAINER = CURRENT;
  17. Create and activate a tagged master encryption key in all PDBs in this container.
    The benefit of adding tagged master encryption keys to PDBs is that it enables you to easily identify keys that belong to a certain PDB.
    1. Connect to each PDB and execute the following SELECT statement to create an ADMINISTER KEY MANAGEMENT command that contains the PDB name and time stamp as a tag for the PDB's master encryption key.
      CONNECT sec_admin@pdb_name AS SYSKM
      Enter password: password
      
      SELECT ' ADMINISTER KEY MANAGEMENT SET KEY 
      USING TAG '''||SYS_CONTEXT('USERENV', 'CON_NAME')||' '|| 
      TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS')||''' 
      IDENTIFIED BY EXTERNAL STORE;' "SET KEY COMMAND" FROM DUAL;
    2. Execute the generated output of this SELECT statement.
      For example:
      ADMINISTER KEY MANAGEMENT SET KEY 
      USING TAG 'pdb_name date time' 
      IDENTIFIED BY EXTERNAL STORE;
  18. On all nodes, add the Oracle Key Vault password into an auto-login wallet to enable auto-login connection into Oracle Key Vault.
    This step is mandatory in Oracle RAC. Having an auto-login connection into Oracle Key Vault is especially important when Oracle RAC nodes are automatically restarted (for example, while applying quarterly release upgrades using the opatchauto patch tool).
    ADMINISTER KEY MANAGEMENT ADD SECRET 'Oracle_Key_Vault_password' 
    FOR CLIENT 'HSM_PASSWORD' 
    TO LOCAL AUTO_LOGIN KEYSTORE '/u01/opt/oracle/product/tde';
    
  19. In the root container, execute the ALTER SYSTEM statement to change the TDE_CONFIGURATION parameter.
    For example:
    ALTER SYSTEM SET TDE_CONFIGURATION = "KEYSTORE_CONFIGURATION=OKV|FILE" 
    SCOPE = BOTH SID = '*';
  20. Change the default behavior of the database to always encrypt new tablespaces with the AES128 algorithm (or the algorithm that you specified in Step 14), even if the CREATE TABLESPACE command does not contain the encryption clauses.
    Execute this statement once on any node:
    ALTER SYSTEM SET ENCRYPT_NEW_TABLESPACES = ALWAYS SCOPE = BOTH SID = '*';
  21. From the root, encrypt sensitive credential data with AES256 for database links in the SYS.LINK$ and SYS.SCHEDULER$_CREDENTIAL system tables.
    This command requires the SYSKM administrative privilege:
    sqlplus c##sec_admin as syskm
    Enter password: password
    
    ALTER DATABASE DICTIONARY ENCRYPT CREDENTIALS;
  22. Log in to the PDB and create a tablespace.
    For example, to create a tablespace named protected:
    CREATE TABLESPACE protected DATAFILE SIZE 50M;
  23. Confirm that the tablespace is encrypted even though the encryption clauses were omitted.
    SELECT C.NAME AS pdb_name, T.NAME AS tablespace_name, E.ENCRYPTIONALG AS ALG 
    FROM V$TABLESPACE T, V$ENCRYPTED_TABLESPACES E, V$CONTAINERS C 
    WHERE E.TS# = T.TS# AND E.CON_ID = T.CON_ID AND E.CON_ID = C.CON_ID 
    ORDER BY E.CON_ID, T.NAME;
    
  24. Create a table in the encrypted tablespace that you just created.
    For example:
    CREATE TABLE SYSTEM.test TABLESPACE protected 
    AS SELECT * FROM DBA_OBJECTS;
  25. Select from this table to confirm that you can read encrypted data:
    SELECT COUNT(*), OWNER FROM SYSTEM.test 
    GROUP BY OWNER 
    ORDER BY 1 DESC;
  26. In the PDBs, encrypt the existing tablespaces.
    Optionally, encrypt the SYSTEM, SYSAUX, and USERS tablespaces. If you omit the encryption algorithm, then the default algorithm (AES128, or the algorithm that you specified in Step 14) is applied.
    ALTER TABLESPACE tablespace_name ENCRYPTION ONLINE ENCRYPT;
  27. Optionally, validate the configuration.
    1. Confirm that the auto-login for Oracle Key Vault is working.
      You can test this by restarting the database, logging into the PDB, and then selecting from the encrypted table. To restart the database:
      $ srvctl stop database -db database_name -o immediate
      $ srvctl start database -db database_name

      After logging in to the PDB, select from the SYSTEM.test table.

      SELECT COUNT(*), OWNER FROM SYSTEM.test 
      GROUP BY OWNER 
      ORDER BY 1 DESC;
    2. Confirm that the master encryption key re-key operations in all open PDBs are successful.
      First, as a user who has the SYSKM administrative privilege, execute the following SELECT statement to create an ADMINISTER KEY MANAGEMENT command that contains the PDB name and time stamp.
      SELECT ' ADMINISTER KEY MANAGEMENT SET KEY 
      USING TAG '''||SYS_CONTEXT('USERENV', 'CON_NAME')||' '|| 
      TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS')||''' 
      FORCE KEYSTORE IDENTIFIED BY EXTERNAL STORE;' "RE-KEY COMMAND" FROM DUAL;

      Next, execute the generated output of this SELECT statement.

      ADMINISTER KEY MANAGEMENT SET KEY 
      USING TAG 'pdb_name date time' 
      FORCE KEYSTORE IDENTIFIED BY EXTERNAL STORE;
    3. From the root container, re-key previously encrypted sensitive credential data in the SYS.LINK$ and SYS.SCHEDULER$_CREDENTIAL system tables.
      This command requires the SYSKM administrative privilege:
      ALTER DATABASE DICTIONARY REKEY CREDENTIALS;
    4. Drop the protected tablespace and its table, test.
      DROP TABLESPACE protected
      INCLUDING CONTENTS AND DATAFILES;

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 use of keystores in a multidatabase environment, use one of the following options:

    • Option 1: Specify the keystore location by individually setting the WALLET_ROOT static initialization parameter and the TDE_CONFIGURATION dynamic initialization parameter (its KEYSTORE_CONFIGURATION attribute set to FILE) for each CDB (or standalone database). You must set the KEYSTORE_CONFIGURATION attribute to FILE in order for the WALLET_ROOT parameter to work.

      For example:

      WALLET_ROOT = $ORACLE_BASE/admin/db_unique_name
      TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE"
    • Option 2: If WALLET_ROOT and TDE_CONFIGURATION are not set, and if the databases share the same Oracle home, then ensure that the WALLET_LOCATION and ENCRYPTION_WALLET_LOCATION parameters in sqlnet.ora are not set. By default, sqlnet.ora is located in the $ORACLE_BASE/network/admin directory (if $ORACLE_BASE is set) or in the $ORACLE_HOME/network/admin directory.

      This enables Oracle Database to use the keystore that is located in either the $ORACLE_BASE/admin/db_unique_name/wallet (assuming $ORACLE_BASE is set) or the $ORACLE_HOME/admin/db_unique_name/wallet directory.

    • 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. However, be aware that the ENCRYPTION_WALLET_LOCATION parameter in sqlnet.ora is deprecated, starting with release 19c, in favor of the WALLET_ROOT and TDE_CONFIGURATION initialization parameters.

Caution:

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