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.

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.

# 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. Patch 30398099 has been integrated into the April 2021 RU for Oracle Database release 19c (19.11), so this functionality is available without extra patches. Note that the following parameter is preceded by an underscore. Note that the following parameter is preceded by an underscore.

    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;

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.

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.

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

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.

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