5 Managing Keystores and TDE Master Encryption Keys in United Mode

United mode enables you to create a common keystore for the CDB and the PDBs for which the keystore is in united mode.

The keys for the CDB and the PDBs reside in the common keystore.

About Managing Keystores and TDE Master Encryption Keys in United Mode

In united mode, you create the keystore and TDE master encryption key for CDB and PDBs that reside in the same keystore.

The keys for PDBs having keystore in united mode, can be created from CDB root or from the PDB.

This design enables you to have one keystore to manage the entire CDB environment, enabling the PDBs to share this keystore, but you can customize the behavior of this keystore in the individual united mode PDBs. For example, in a united mode PDB, you can configure a TDE master encryption key for the PDB in the united keystore that you created in the CDB root, open the keystore locally, and close the keystore locally. In order to perform these actions, the keystore in the CDB root must be open.

Before you configure your environment to use united mode or isolated mode, all the PDBs in the CDB environment are considered to be in united mode.

To use united mode, you must follow these general steps:

  1. In the CDB root, configure the database to use united mode by setting the WALLET_ROOT and TDE_CONFIGURATION parameters.

    The WALLET_ROOT parameter sets the location for the wallet directory and the TDE_CONFIGURATION parameter sets the type of keystore to use.

  2. Restart the database so that these settings take effect.

  3. In the CDB root, create the keystore, open the keystore, and then create the TDE master encryption key.

  4. In each united mode PDB, perform TDE master encryption key tasks as needed, such as opening the keystore locally in the united mode PDB and creating the TDE master encryption key for the PDB. Remember that the keystore is managed by the CDB root, but must contain a TDE master encryption key that is specific to the PDB for the PDB to be able to use TDE.

When you run ADMINISTER KEY MANAGEMENT statements in united mode from the CDB root, if the statement accepts the CONTAINER clause, and if you set it to ALL, then the statement applies only to the CDB root and its associated united mode PDBs. Any PDB that is in isolated mode is not affected.

Operations That Are Allowed in United Mode

Many ADMINISTER KEY MANAGEMENT operations performed in the CDB root apply to keystores and encryption keys in the united mode PDB.

Available United Mode-Related Operations in a CDB Root

Table 5-1 describes the ADMINISTER KEY MANAGEMENT operations that you can perform in the CDB root.

Table 5-1 ADMINISTER KEY MANAGEMENT United Mode Operations in a CDB Root

Operation Syntax United Mode Notes

Creating a keystore

ADMINISTER KEY MANAGEMENT
CREATE KEYSTORE 
['keystore_location']
IDENTIFIED BY keystore_password;

After you create the keystore in the CDB root, by default it is available in the united mode PDBs. Do not include the CONTAINER clause.

Opening a keystore

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

In this operation, the EXTERNAL STORE clause uses the password in the SSO wallet located in the tde_seps directory under the per-PDB WALLET_ROOT location.

Changing a keystore password

ADMINISTER KEY MANAGEMENT 
ALTER KEYSTORE PASSWORD
IDENTIFIED BY old_keystore_password
SET new_keystore_passwordWITH BACKUP 
[USING 'backup_identifier'];

Do not include the CONTAINER clause.

Backing up a keystore

ADMINISTER KEY MANAGEMENT
BACKUP KEYSTORE 
[USING 'backup_identifier']
[FORCE KEYSTORE]
IDENTIFIED BY [EXTERNAL STORE | keystore_password]
[TO 'keystore_location'];

Do not include the CONTAINER clause.

Closing a keystore without force

ADMINISTER KEY MANAGEMENT 
SET KEYSTORE CLOSE
[IDENTIFIED BY [EXTERNAL STORE | keystore_password]]
[CONTAINER = ALL | CURRENT];

If an isolated mode PDB keystore is open, then this statement raises an ORA-46692 cannot close wallet error.

Closing a keystore with force

ADMINISTER KEY MANAGEMENT 
FORCE KEYSTORE CLOSE
[IDENTIFIED BY [EXTERNAL STORE | keystore_password]]
[CONTAINER = ALL | CURRENT];

This operation allows the keystore to be closed in the CDB root when an isolated keystore is open.

Creating and activating a new TDE master encryption key (rekeying)

ADMINISTER KEY MANAGEMENT 
SET [ENCRYPTION] KEY 
[FORCE KEYSTORE]
[USING TAG 'tag_name']
IDENTIFIED BY [EXTERNAL STORE | keystore_password]
[WITH BACKUP 
[USING 'backup_identifier']]
[CONTAINER = ALL | CURRENT]

-

Creating a user-defined TDE master encryption key for either now (SET) or later on (CREATE)

ADMINISTER KEY MANAGEMENT [SET | CREATE] [ENCRYPTION] KEY
'mkid:mk | mk' 
[USING ALGORITHM 'algorithm'] 
[FORCE KEYSTORE]
[USING TAG 'tag_name']
IDENTIFIED BY [EXTERNAL STORE | keystore_password]
 [WITH BACKUP [USING 'backup_identifier']]
[CONTAINER = CURRENT];

-

Activating an existing TDE master encryption key

ADMINISTER KEY MANAGEMENT 
USE [ENCRYPTION] KEY 'key_id 
[USING TAG 'tag']
IDENTIFIED BY [EXTERNAL STORE | keystore_password]
WITH BACKUP 
[USING 'backup_identifier'];

Do not include the CONTAINER clause.

Tagging a TDE master encryption key

ADMINISTER KEY MANAGEMENT 
SET TAG 'tag' FOR 'key_id'
IDENTIFIED BY [EXTERNAL STORE | keystore_password ]
WITH BACKUP 
[USING 'backup_identifier'];

Do not include the CONTAINER clause.

Moving a TDE master encryption key to a new keystore

ADMINISTER KEY MANAGEMENT 
MOVE [ENCRYPTION] KEYS
TO NEW KEYSTORE 'keystore_location1'
IDENTIFIED BY keystore1_password
FROM [FORCE] KEYSTORE
IDENTIFIED BY keystore_password
[WITH IDENTIFIER IN
{ 'key_id' [, 'key_id' ]... | ( subquery ) } ]
[WITH BACKUP 
[USING 'backup_identifier'];

You can only move the master encryption key to a keystore that is within the same container (for example, between keystores in the CDB root or between keystores in the same PDB). You cannot move the master encryption key from a keystore in the CDB root to a keystore in a PDB, and vice versa.

Do not include the CONTAINER clause.

Available Operations in a United Mode PDB

Table 5-2 describes the ADMINISTER KEY MANAGEMENT operations that you can perform in a united mode PDB.

Table 5-2 ADMINISTER KEY MANAGEMENT United Mode PDB Operations

Operation Syntax United Mode Notes

Opening a keystore

ADMINISTER KEY MANAGEMENT 
SET KEYSTORE OPEN
IDENTIFIED BY
[EXTERNAL STORE | keystore_password]
[CONTAINER = CURRENT];

In this operation, the EXTERNAL_STORE clause uses the password in the Secure Sockets Layer (SSL) wallet. This wallet is located in the tde_seps directory in the WALLET_ROOT location.

Closing a keystore without force

ADMINISTER KEY MANAGEMENT 
SET KEYSTORE CLOSE
[IDENTIFIED BY keystore_password]
[CONTAINER = CURRENT];

-

Closing a keystore with force

ADMINISTER KEY MANAGEMENT 
FORCE KEYSTORE CLOSE
IDENTIFIED BY
[EXTERNAL STORE | keystore_password]
[CONTAINER = CURRENT];

-

Creating and activating a new TDE master encryption key (rekeying or rotating)

ADMINISTER KEY MANAGEMENT 
SET [ENCRYPTION] KEY 
[FORCE KEYSTORE]
[USING TAG 'tag_name']
IDENTIFIED BY [EXTERNAL STORE | keystore_password] 
[WITH BACKUP 
[USING 'backup_identifier']]
[CONTAINER = CURRENT];

-

Creating a user-defined TDE master encryption key for use either now (SET) or later on (CREATE)

ADMINISTER KEY MANAGEMENT SET | CREATE [ENCRYPTION] KEY
'mkid:mk | mk' 
[USING ALGORITHM 'algorithm'] 
[FORCE KEYSTORE]
IDENTIFIED BY [EXTERNAL STORE | keystore_password] 
[WITH BACKUP [USING 'backup_identifier']]
[CONTAINER = CURRENT];

-

Activating an existing TDE master encryption key

ADMINISTER KEY MANAGEMENT 
USE [ENCRYPTION] KEY 'key_id' 
[USING TAG 'tag']
IDENTIFIED BY
[EXTERNAL STORE | keystore_password] 
[WITH BACKUP 
[USING 'backup_identifier']];

Do not include the CONTAINER clause.

Tagging a TDE master encryption key

ADMINISTER KEY MANAGEMENT 
SET TAG 'tag' FOR 'key_id'
[FORCE KEYSTORE]
IDENTIFIED BY [EXTERNAL STORE | keystore_password]
[WITH BACKUP 
[USING 'backup_identifier']];

Do not include the CONTAINER clause.

Moving an encryption key to a new keystore

ADMINISTER KEY MANAGEMENT 
MOVE [ENCRYPTION] KEYS
TO NEW KEYSTORE 'keystore_location1'
IDENTIFIED BY keystore1_password
FROM [FORCE] KEYSTORE
IDENTIFIED BY keystore_password
[WITH IDENTIFIER IN
{ 'key_id' [, 'key_id' ]... | ( subquery ) } ]
[WITH BACKUP 
[USING 'backup_identifier']];

Do not include the CONTAINER clause.

Moving a key from a united mode keystore in the CDB root to an isolated mode keystore in a PDB

ADMINISTER KEY MANAGEMENT 
ISOLATE KEYSTORE
IDENTIFIED BY isolated_keystore_password
FROM ROOT KEYSTORE
[FORCE KEYSTORE]
IDENTIFIED BY
[EXTERNAL STORE | united_keystore_password]
[WITH BACKUP [USING backup_id]];

Do not include the CONTAINER clause.

Using the FORCE clause when a clone of a PDB is using the TDE master encryption key that is being isolated; then coping (rather than moving) the TDE master encryption keys from the keystore that is in the CDB root into the isolated mode keystore of the PDB

ADMINISTER KEY MANAGEMENT 
[FORCE] ISOLATE KEYSTORE
IDENTIFIED BY isolated_keystore_password
FROM ROOT KEYSTORE
[FORCE KEYSTORE]
IDENTIFIED BY
[EXTERNAL STORE | united_keystore_password]
[WITH BACKUP [USING backup_id]];

-

Operations That Are Not Allowed in a United Mode PDB

ADMINISTER KEY MANAGEMENT operations that are not allowed in a united mode PDB can be performed in the CDB root.

These operations are as follows:

  • Keystore operations:

    • Performing merge operations on keystores

    • Exporting a keystore

    • Importing a keystore

    • Migrating a keystore

    • Reverse-migrating a keystore

    • Moving the keys of a keystore that is in the CDB root into the keystores of a PDB

    • Moving the keys from a PDB into a united mode keystore that is in the CDB root

  • Encryption key operations:

    • Using the CONTAINER = ALL clause to create a new TDE master encryption key for later user in each pluggable database (PDB)

  • Client secret operations:

    • Adding client secrets

    • Updating client secrets

    • Deleting client secrets

Configuring the Keystore Location and Type for United Mode

For united mode, you can configure the keystore location and type by using only parameters or a combination of parameters and the ALTER SYSTEM statement.

Configuring United Mode by Editing the Initialization Parameter File

You can configure united mode by setting both the WALLET_ROOT and TDE_CONFIGURATION parameters in the initialization parameter file.

  1. Log in to the server where the CDB root of the Oracle database resides.
  2. If necessary, create a wallet directory.
    Typically, the wallet directory is located in the $ORACLE_BASE/admin/db_unique_name directory, and it is named wallet. Preferably, this directory should be empty.
  3. Edit the initialization parameter file, which by default is located in the $ORACLE_HOME/dbs directory, to include the following parameters:
    • WALLET_ROOT, to point to the location of the wallet directory.

      For example, for a database named orcl:

      wallet_root=$ORACLE_BASE/admin/orcl/wallet
    • TDE_CONFIGURATION, to specify one of the following keystore types:
      • FILE specifies a software keystore.

      • OKV specifies an Oracle Key Vault keystore.

      • HSM specifies a hardware security module (HSM) keystore.

      For example, to specify the TDE keystore type:

      tde_configuration="keystore_configuration=file"
  4. Log in to the CDB root as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
    For example:
    sqlplus sec_admin as syskm
    Enter password: password
  5. Check the configuration settings.
    • For the WALLET_ROOT parameter:
      SHOW PARAMETER WALLET_ROOT

      The VALUE column of the output should show the absolute path location of the wallet directory.

    • For the TDE_CONFIGURATION parameter:
      SHOW PARAMETER TDE_CONFIGURATION

      The VALUE column should show the keystore type, prepended with KEYSTORE_CONFIGURATION=.

    If the values do not appear, then try restarting your database with the STARTUP command pointing to the location of the initialization parameter file where you added these settings. For example:
    STARTUP PFILE = /u01/oracle/dbs/init.ora
  6. Confirm that the TDE_CONFIGURATION parameter was set correctly.
    SELECT CON_ID, KEYSTORE_MODE FROM V$ENCRYPTION_WALLET;
    The output should be similar to the following:
        CON_ID KEYSTORE
    ---------- --------
             1 NONE
             2 UNITED
             3 UNITED
             4 UNITED
             5 UNITED
    
    The CDB root (CON_ID 1) will always be in the NONE state, and at this stage, the remaining CON_IDs should be set to UNITED. PDBs can be either UNITED or ISOLATED, depending on how you configure them. When you query the V$ENCRYPTION_WALLET view, if the ORA-46691: The value of the KEYSTORE_CONFIGURATION attribute is invalid error appears, then check the initialization parameter file where you added this setting.
After you configure united mode, you can create keystores and master encryption keys, and when these are configured, you can encrypt data.

Configuring United Mode with the Initialization Parameter File and ALTER SYSTEM

If your environment relies on server parameter files (spfile) or parameter files (pfile), then you can set TDE_CONFIGURATION using ALTER SYSTEM with SCOPE.

With this method, you still set the WALLET_ROOT static initialization parameter in the initialization parameter file.
  1. Log in to the server where the CDB root of the Oracle database resides.
  2. If necessary, create a wallet directory.
    Typically, the wallet directory is located in the $ORACLE_BASE/admin/db_unique_name directory, and it is named wallet. Preferably, this directory should be empty.
  3. Edit the initialization parameter file to include the WALLET_ROOT static initialization parameter for the wallet directory.
    By default, the initialization parameter file is located in the $ORACLE_HOME/dbs directory.
    For example, for a database instance named orcl:
    wallet_root=$ORACLE_BASE/admin/orcl/wallet
  4. Log in to the CDB root as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
    For example:
    sqlplus sec_admin as syskm
    Enter password: password
  5. Restart the database.
    SHUTDOWN IMMEDIATE
    STARTUP

    To start the database by pointing to the location of the initialization file where you added the WALLET_ROOT setting, issue a STARTUP command similar to the following:

    STARTUP PFILE = /u01/oracle/dbs/init.ora
  6. Check the WALLET_ROOT setting.
    SHOW PARAMETER WALLET_ROOT

    The VALUE column of the output should show the absolute path location of the wallet directory.

  7. Set the TDE_CONFIGURATION dynamic initialization parameter to specify the keystore type, using the following syntax:
    ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=keystore_type" 
    SCOPE=scope_type;

    In this specification:

    • keystore_type can be one of the following settings for united mode:

      • FILE configures a TDE keystore.

      • OKV configures an Oracle Key Vault keystore.

      • HSM configures a hardware security module (HSM) keystore.

    • scope_type sets the type of scope (for example, both, memory, spfile, pfile.

    For example, to configure a TDE keystore if the parameter file (pfile) is in use, set scope to memory:

    ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" 
    SCOPE=memory;

    To configure a TDE keystore if the server parameter file (spfile) is in use, set scope to both:

    ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" 
    SCOPE=both;
  8. Check the TDE_CONFIGURATION parameter setting.
    SHOW PARAMETER TDE_CONFIGURATION
    

    The VALUE column should show the keystore type, prepended with KEYSTORE_CONFIGURATION=.

  9. Confirm that the TDE_CONFIGURATION parameter was set correctly.
    SELECT CON_ID, KEYSTORE_MODE FROM V$ENCRYPTION_WALLET;
    The output should be similar to the following:
        CON_ID KEYSTORE
    ---------- --------
             1 NONE
             2 UNITED
             3 UNITED
             4 UNITED
             5 UNITED
    
    The CDB root (CON_ID 1) will always be in the NONE state, and at this stage, the remaining CON_IDs should be set to UNITED. PDBs can be either UNITED or ISOLATED, depending on how you configure them. When you query the V$ENCRYPTION_WALLET view, if the ORA-46691: The value of the KEYSTORE_CONFIGURATION attribute is invalid error appears, then check the initialization parameter file where you added this setting.
After you configure united mode, you can create keystores and master encryption keys, and when these are configured, you can encrypt data.

Configuring a Software Keystore for Use in United Mode

In united mode, the software keystore resides in the CDB root but the master keys from this keystore are available for the PDBs that have their keystore in united mode.

About Configuring a Software Keystore in United Mode

In united mode, the keystore that you create in the CDB root will be accessible by the united mode PDBs.

In general, to configure a united mode software keystore after you have enabled united mode, you create and open the keystore in the CDB root, and then create a master encryption key for this keystore. Afterward, you can begin to encrypt data for tables and tablespaces that will be accessible throughout the CDB environment.

The V$ENCRYPTION_WALLET dynamic view describes the status and location of the keystore. For example, the following query shows the open-closed status and the keystore location of the CDB root keystore (CON_ID 1) and its associated united mode PDBs. The WRL_PARAMETER column shows the CDB root keystore location being in the $ORACLE_BASE/wallet/tde directory.

SELECT CON_ID, STATUS, WRL_PARAMETER FROM V$ENCRYPTION_WALLET;

CON_ID STATUS WRL_PARAMETER
------ ------ -----------------------------
     1 OPEN   /app/oracle/wallet/tde/
     2 CLOSED 
     3 OPEN 
     4 OPEN
     5 OPEN  

In this output, there is no keystore path listed for the other PDBs in this CDB because these PDBs use the keystore in the CDB root. If any of these PDBs are isolated and you create a keystore in the isolated mode PDB, then when you perform this query, the WRL_PARAMETER column will show the keystore path for the isolated mode PDB.

You can create a secure external store for the software keystore. This feature enables you to hide the password from the operating system: it removes the need for storing clear-text keystore passwords in scripts or other tools that can access the database without user intervention, such as overnight batch scripts. The location for this keystore is set by the EXTERNAL_KEYSTORE_CREDENTIAL_LOCATION initialization parameter. In a multitenant environment, different PDBs can access this external store location when you run the ADMINISTER KEY MANAGEMENT statement using the IDENTIFIED BY EXTERNAL STORE clause. This way, you can centrally locate the password and then update it only once in the external store.

Step 1: Create the Software Keystore

In united mode, you must create the keystore in the CDB root.

After you create this keystore in the CDB root, it becomes available in any united mode PDB, but not in any isolated mode PDBs.
  1. Log in to the CDB root as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
    For example:
    sqlplus c##sec_admin as syskm
    Enter password: password
  2. Run the ADMINISTER KEY MANAGEMENT SQL statement to create the keystore using the following syntax:
    ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 
    ['keystore_location'] 
    IDENTIFIED BY software_keystore_password;
    

    In this specification:

    • keystore_location is the path to the keystore directory location of the password-protected keystore for which you want to create the auto-login keystore. If the path that is set by the WALLET_ROOT parameter is the path that you want to use, then you can omit the keystore_location setting.

      If you specify the keystore_location, then enclose it in single quotation marks (' '). To find the default location, you can query the WRL_PARAMETER column of the V$ENCRYPTION_WALLET view. (If the keystore was not created in the default location, then the STATUS column of the V$ENCRYPTION_WALLET view is NOT_AVAILABLE.)

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

    For example, to create the keystore in the default location, assuming that WALLET_ROOT has been set:

    ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 
    IDENTIFIED BY password;
    
    keystore altered.
    
After you complete these steps, the ewallet.p12 file, which contains the keystore, appears in the designated keystore location. For example, if you had set the WALLET_ROOT parameter to $ORACLE_BASE/wallet and the TDE_CONFIGURATION parameter to FILE (for TDE, which creates a tde directory in the wallet root location), then the keystore will be created in the $ORACLE_BASE/wallet/tde directory. The name of the keystore is ewallet.p12.

Step 2: Open the Software Keystore in a United Mode PDB

To open a software keystore in united mode, you must use the ADMINISTER KEY MANAGEMENT statement with the SET KEYSTORE OPEN clause.

  1. Log in to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
    For example:
    sqlplus c##sec_admin as syskm
    Enter password: password
  2. Open the keystore in the CDB root.
    For example:
    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN 
    IDENTIFIED BY password;
    
    keystore altered.

    If the database instance is configured using the EXTERNAL_KEYSTORE_CREDENTIAL_LOCATION instance initialization parameter and has a keystore at that location containing the credentials of the password-protected keystore, and you want to switch over from using an auto-login keystore to using the password-protected keystore with these credentials, you must include the FORCE KEYSTORE clause and the IDENTIFIED BY EXTERNAL STORE clause in the ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN statement, as follows:

    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN 
    FORCE KEYSTORE 
    IDENTIFIED BY EXTERNAL STORE;
    
    keystore altered.

    If the WALLET_ROOT parameter has been set, then Oracle Database finds the external store by searching in this path in the CDB root: WALLET_ROOT/tde_seps.

  3. Ensure that the PDB in which you want to open the keystore is in READ WRITE mode.
    For example:
    SHOW PDBS
    
    CON_ID CON_NAME   OPEN MODE    RESTRICTED
    ------ ---------- ------------ -----------
    2      PDB$SEED   READ ONLY    NO
    3      CDB1_PDB1  READ WRITE   NO

    If any PDB has an OPEN MODE value that is different from READ WRITE, then run the following statement to open the PDB, which will set it to READ WRITE mode:

    ALTER PLUGGABLE DATABASE CDB1_PDB1 OPEN;

    Now the keystore can be opened in both the CDB root and the PDB.

  4. Connect to the PDB.
  5. Run the ADMINISTER KEY MANAGEMENT statement to open the keystore.
    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN 
    IDENTIFIED BY password;
    keystore altered.

    To switch over to opening the password-protected software keystore when an auto-login keystore is configured and is currently open, specify the FORCE KEYSTORE clause as follows.

    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN 
    FORCE KEYSTORE 
    IDENTIFIED BY EXTERNAL STORE;
    keystore altered.

    FORCE KEYSTORE is also useful for databases that are heavily loaded. The IDENTIFIED BY EXTERNAL STORE clause is included in the statement because the keystore credentials exist in an external store. This enables the password-protected keystore to be opened without specifying the keystore password within the statement itself.

    If the WALLET_ROOT parameter has been set, then Oracle Database finds the external store by searching in this path: WALLET_ROOT/PDB_GUID/tde_seps.

  6. Confirm that the keystore is open.
    SELECT STATUS FROM V$ENCRYPTION_WALLET;

Step 3: Set the TDE Master Encryption Key in the Software Keystore in United Mode

To set the TDE master encryption key in the keystore when the PDB is configured in united mode, use the ADMINISTER KEY MANAGEMENT statement with the SET KEY clause.

  1. Log in to the CDB root or to the PDB that is configured for united mode as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
  2. Ensure that the database is open in READ WRITE mode.
    To find the status, run the show pdbs command.
  3. Run the ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY statement to create or rekey the TDE master encryption key in the keystore.
    For example, if the keystore is password-protected and open, and you want to create or rekey the TDE master encryption key in the current container:
    ADMINISTER KEY MANAGEMENT SET KEY 
    IDENTIFIED BY keystore_password 
    WITH BACKUP USING 'emp_key_backup';
    
    keystore altered.

    If the keystore is closed:

    ADMINISTER KEY MANAGEMENT SET KEY 
    FORCE KEYSTORE
    IDENTIFIED BY keystore_password 
    WITH BACKUP USING 'emp_key_backup';
    
    keystore altered.

    In this specification:

    • FORCE KEYSTORE should be included if the keystore is closed. This automatically opens the keystore before setting the TDE master encryption key. The FORCE KEYSTORE clause also switches over to opening the password-protected software keystore when an auto-login keystore is configured and is currently open.

    • IDENTIFIED BY specifies the keystore password. Alternatively, if the keystore password is in an external store, you can use the IDENTIFIED BY EXTERNAL STORE clause.

  4. Confirm that the TDE master encryption key is set.
    SELECT MASTERKEY_ACTIVATED FROM V$DATABASE_KEY_INFO;

    The output should be YES.

Step 4: Encrypt Your Data in United Mode

Now that you have completed the keystore configuration and the PDB is configured in united mode, you can begin to encrypt data in the PDB.

Configuring a Hardware Keystore in United Mode

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

About Configuring a Hardware Keystore in United Mode

You must create a hardware keystore in a PDB using isolated mode but you can perform other tasks in united mode.

To configure a hardware keystore for a PDB in isolated mode, you first must set the WALLET_ROOT parameter. This is necessary for two reasons: first, to have support for migrating to a software keystore in the future, and second, because the configuration file for Oracle Key Vault is retrieved from a location under WALLET_ROOT. Afterwards, you must set the KEYSTORE_CONFIGURATION attribute of the TDE_CONFIGURATION parameter to HSM or OKV, open the configured hardware keystore, and then set the TDE master encryption key for the PDB. After you complete these tasks, you can begin to encrypt data in the PDB.

How you specify the IDENTIFIED BY clause when you run the ADMINISTER KEY MANAGEMENT statement depends on the type of hardware keystore. For a hardware security module (HSM), you use the following syntax:

IDENTIFIED BY "user_name:password"

For an Oracle Key Vault keystore, you can omit the user_name and colon (but keep the quotation marks):

IDENTIFIED BY "password"

Step 1: Configure the United Mode Hardware Security Module

To configure a third-party hardware security module, you must copy the PKCS#11 library to the correct location and follow your vendor's instructions

Step 2: Open the Hardware Keystore in a United Mode PDB

To open a hardware keystore in united mode, you must use the ADMINISTER KEY MANAGEMENT statement with the SET KEYSTORE OPEN clause.

  1. Log in to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
    For example:
    sqlplus c##sec_admin as syskm
    Enter password: password
  2. Open the keystore in the CDB root by using the following syntax.
    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN 
    IDENTIFIED BY "hardware_keystore_credentials";
    

    The type of hardware keystore determines how you specify the hardware keystore password. For hardware security modules, you must use the user_name:password syntax. For example:

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

    For an Oracle Key Vault keystore, you can only provide the password. No user name is allowed in the IDENTIFIED BY clause. Enclose the password in double quotation marks.

    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN 
    IDENTIFIED BY "password";
  3. Ensure that the PDB in which you want to open the keystore is in READ WRITE mode.
    For example:
    SHOW PDBS
    
    CON_ID CON_NAME   OPEN MODE    RESTRICTED
    ------ ---------- ------------ -----------
    2      PDB$SEED   READ ONLY    NO
    3      CDB1_PDB1  READ WRITE   NO

    If any PDB has an OPEN MODE value that is different from READ WRITE, then run the following statement to open the PDB, which will set it to READ WRITE mode:

    ALTER PLUGGABLE DATABASE CDB1_PDB1 OPEN;

    Now the keystore can be opened in both the CDB root and the PDB.

  4. Connect to the PDB and run the ADMINISTER KEY MANAGEMENT statement to open the keystore.
    For example, for an HSM:
    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN 
    IDENTIFIED BY "psmith:password";
    
    keystore altered.

    You can include the FORCE KEYSTORE clause if there is a chance that the CDB root keystore has been closed, or the database is heavily loaded.

  5. Confirm that the keystore is open.
    SELECT STATUS FROM V$ENCRYPTION_WALLET;
  6. Repeat this procedure each time you restart the database instance.

Step 3: Set the TDE Master Encryption Key in the Hardware Keystore in United Mode

To set the TDE master encryption key in the keystore when the PDB is configured in united mode, use the ADMINISTER KEY MANAGEMENT statement with the SET KEY clause.

  1. Log in to the CDB root or to the PDB that is configured for united mode as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
  2. Ensure that the database is open in READ WRITE mode.
    To find the status, run the SHOW PDBS command.
  3. Run the ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY statement to create or rekey the TDE master encryption key in the keystore.
    ADMINISTER KEY MANAGEMENT SET KEY 
    [USING TAG 'tag'] 
    [FORCE KEYSTORE] 
    IDENTIFIED BY [EXTERNAL STORE | "hardware_keystore_credentials"];
    

    In this specification:

    • FORCE KEYSTORE temporarily opens the password-protected keystore for this operation. You must open the keystore for this operation.

    • hardware_keystore_credentials refers to the credentials for either an HSM or an Oracle Key Vault hardware keystore. For an HSM, specify the credentials using this format, enclosed in quotation marks and separating the components with a colon: user_name:password, with user_name being the user who created the HSM and password being this user’s password. For Oracle Key Vault, enter only the password of the user who created the keystore. Enclose this password with quotation marks.

    For example:

    ADMINISTER KEY MANAGEMENT SET KEY 
    FORCE KEYSTORE 
    IDENTIFIED BY "psmith:password";
    
    keystore altered.
  4. Confirm that the TDE master encryption key is set.
    SELECT MASTERKEY_ACTIVATED FROM V$DATABASE_KEY_INFO;

    The output should be YES.

Step 4: Encrypt Your Data in United Mode

Now that you have completed the keystore configuration and the PDB is configured in united mode, you can begin to encrypt data in the PDB.

Administering Keystores and TDE Master Encryption Keys in United Mode

After you configure a keystore and master encryption key for use in united mode, you can perform tasks such as rekeying TDE master encryption keys.

Changing the Keystore Password in United Mode

You can change the password of either a software keystore or a hardware keystores only in the CDB root.

Changing the Password-Protected Software Keystore Password in United Mode

To change the password of a password-protected software keystore in united mode, you must use the ADMINISTER KEY MANAGEMENT statement in the CDB root.

You cannot change keystore passwords from a united mode PDB.
  1. Log in to the CDB root as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

  2. Use the following syntax to change the password for the keystore:

    ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD 
    [FORCE KEYSTORE] 
    IDENTIFIED BY
    old_keystore_password SET new_keystore_password 
    [WITH BACKUP [USING 'backup_identifier']];
    

    In this specification:

    • FORCE KEYSTORE temporarily opens the password-protected keystore for this operation if the keystore is closed if an auto-login keystore is configured and is currently open, or if a password-protected keystore is configured and is currently closed.

    • old_password is the current keystore password that you want to change.

    • new_password is the new password that you set for the keystore.

    • You do not need to include the CONTAINER clause because the password can only be changed locally, in the CDB root.

    The following example creates a backup of the keystore and then changes the password:

    ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD 
    IDENTIFIED BY
    old_password SET new_password 
    WITH BACKUP USING 'pwd_change';
    
    keystore altered.

    This example performs the same operation but uses the FORCE KEYSTORE clause in case the auto-login software keystore is in use or the password-protected software keystore is closed.

    ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD
    FORCE KEYSTORE 
    IDENTIFIED BY
    old_password SET new_password 
    WITH BACKUP USING 'pwd_change';
    
    keystore altered.
Changing the Password of a Hardware Keystore in United Mode

To change the password of a hardware keystore, you must close the hardware keystore and then change the password from the hardware keystore management interface.

You cannot change keystore passwords from a united mode PDB.
  1. Log in to the CDB root as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

  2. Close the hardware keystore.

    For example, for an HSM:

    ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE 
    IDENTIFIED BY "psmith:password";
    

    For a keystore whose password is stored externally:

    ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE 
    IDENTIFIED BY EXTERNAL STORE;
  3. From the hardware security module management interface, create a new hardware security module password.

  4. Update the credentials of the HSM in the external store to use "user_name:password".

    Currently, the external store contains the old HSM credentials, which would no longer work.

    For example:

    ADMINISTER KEY MANAGEMENT
    UPDATE SECRET 'user_name:password' 
    FOR CLIENT 'TDE_WALLET' 
    TO LOCAL AUTO_LOGIN KEYSTORE '/etc/ORACLE/WALLETS/orcl/external_store';
  5. In SQL*Plus, open the hardware keystore.

    For example, for an HSM:

    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN 
    IDENTIFIED BY "psmith:new_password";
    

    For a hardware keystore whose password is stored externally:

    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN 
    IDENTIFIED BY EXTERNAL STORE;

Backing Up a Password-Protected Software Keystore in United Mode

The BACKUP KEYSTORE clause of the ADMINISTER KEY MANAGEMENT statement backs up a password-protected software keystore.

  1. Log in to the CDB root as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

    For example:

    sqlplus c##sec_admin@hrpdb as syskm
    Enter password: password
    Connected.
    
  2. Back up the keystore by using the following syntax:

    ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE 
    [USING 'backup_identifier'] 
    [FORCE KEYSTORE] 
    IDENTIFIED BY [EXTERNAL STORE | software_keystore_password] 
    [TO 'keystore_location'];
    

    In this specification:

    • USING backup_identifier is an optional string that you can provide to identify the backup. Enclose this identifier in single quotation marks (' '). This identifier is appended to the named keystore file (for example, ewallet_time-stamp_emp_key_backup.p12).

    • FORCE KEYSTORE temporarily opens the password-protected keystore for this operation. You must open the keystore for this operation.

    • IDENTIFIED BY is required for the BACKUP KEYSTORE operation on a password-protected keystore because although the backup is simply a copy of the existing keystore, the status of the TDE master encryption key in the password-protected keystore must be set to BACKED UP and for this change the keystore password is required.

    • keystore_location is the path at which the backup keystore is stored. This setting is restricted to the PDB when the PDB lockdown profile EXTERNAL_FILE_ACCESS setting is blocked in the PDB or when the PATH_PREFIX variable was not set when the PDB was created. If you do not specify the keystore_location, then the backup is created in the same directory as the original keystore. Enclose this location in single quotation marks (' ').

    • You do not need to include the CONTAINER clause because the keystore can only be backup up locally, in the CDB root.

    The following example backs up a software keystore in the same location as the source keystore.

    ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE 
    USING 'hr.emp_keystore' 
    FORCE KEYSTORE 
    IDENTIFIED BY 
    software_keystore_password ;
    
    keystore altered.
    

    In the following version, the password for the keystore is external, so the EXTERNAL STORE clause is used.

    ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE 
    USING 'hr.emp_keystore' 
    FORCE KEYSTORE 
    IDENTIFIED BY EXTERNAL STORE;

    After you run this statement, an ewallet_identifier.p12 file (for example, ewallet_time-stamp_hr.emp_keystore.p12) appears in the keystore backup location.

Closing Keystores in United Mode

You can close both software and hardware keystores in united mode, unless the system tablespace is encrypted.

Closing a Software Keystore in United Mode

You can close password-protected keystores, auto-login keystores, and local auto-login software keystores in united mode.

In the case of an auto-login keystore, which opens automatically when it is accessed, you must first move it to a new location where it cannot be automatically opened, then you must manually close it. You must do this if you are changing your configuration from an auto-login keystore to a password-protected keystore: you change the configuration to stop using the auto-login keystore (by moving the auto-login keystore to another location where it cannot be automatically opened), and then closing the auto-login keystore.
  1. Log in to the CDB root or the united mode PDB as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
  2. Close the software keystore.
    • For a password-protected software keystore, use the following syntax if you are in the CDB root:
      ADMINISTER KEY MANAGEMENT SET | FORCE KEYSTORE CLOSE 
      [IDENTIFIED BY [EXTERNAL STORE | software_keystore_password]]
      [CONTAINER = ALL | CURRENT];

      Use the SET clause to close the keystore without force. If there is a dependent keystore that is open (for example, an isolated mode PDB keystore and you are trying to close the CDB root keystore), then an ORA-46692 cannot close wallet error appears. If this happens, then use the FORCE clause instead of SET to temporarily close the dependent keystore during the close operation. The STATUS column of the V$ENCRYPTION_WALLET view shows if a keystore is open.

      If you are in the united mode PDB, then either omit the CONTAINER clause or set it to CURRENT.

    • For an auto-login or local auto-login software keystore, use this syntax if you are in the CDB root:
      ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE
      [CONTAINER =  ALL | CURRENT];
Closing a keystore disables all of the encryption and decryption operations. Any attempt to encrypt or decrypt data or access encrypted data results in an error.
Closing a Hardware Keystore in United Mode

To close a hardware keystore, you must use the ADMINISTER KEY MANAGEMENT statement with the SET KEYSTORE CLOSE clause.

  1. Log in to the CDB root or the united mode PDB as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
  2. Close the hardware keystore by using the following syntax:
    ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE 
    IDENTIFIED BY [EXTERNAL STORE | "hardware_keystore_credentials"]
    [CONTAINER = ALL | CURRENT];

    For example, for an HSM:

    ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE 
    IDENTIFIED BY "psmith:password"
    CONTAINER = ALL;
    If an ORA-46692 cannot close wallet error appears, then check if any isolated mode keystores are open. To find the status of a keystore, query the STATUS column of the V$ENCRYPTION_WALLET view.
Closing a keystore disables all of the encryption and decryption operations. Any attempt to encrypt or decrypt data or access encrypted data results in an error.

Creating a User-Defined TDE Master Encryption Key in United Mode

To create a user-defined TDE master encryption key, use the ADMINISTER KEY MANAGEMENT statement with the SET | CREATE [ENCRYPTION] KEY clause.

  1. Log in to the CDB root a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
    For example:
    sqlplus c##sec_admin as syskm
    Enter password: password
  2. Create the user-defined TDE master encryption key by using the following syntax:
    ADMINISTER KEY MANAGEMENT SET | CREATE [ENCRYPTION] KEY
    'mkid:mk | mk' 
    [USING ALGORITHM 'algorithm'] 
    [FORCE KEYSTORE]
    [USING TAG 'tag_name']
    IDENTIFIED BY [EXTERNAL STORE | keystore_password] 
    [WITH BACKUP [USING 'backup_identifier']]
    [CONTAINER = CURRENT];

    In this specification:

    • SET | CREATE : Enter SET if you want to create the master and activate the TDE master encryption key now, or enter CREATE if you want to create the key for later use, without activating it yet.

    • mkid and mk:

      • mkid, the TDE master encryption key ID, is a 16–byte hex-encoded value that you can specify or have Oracle Database generate.

      • mk, the TDE master encryption key, is a hex-encoded value that you can specify or have Oracle Database generate, either 32 bytes (for the for AES256, ARIA256, and GOST256 algorithms) or 16 bytes (for the SEED128 algorithm).

      If you omit the mkid value but include the mk, then Oracle Database generates the mkid for the mk.

      If you omit the entire mkid:mk|mkid clause, then Oracle Database generates these values for you.

    • USING ALGORITHM: Specify one of the following supported algorithms:

      • AES256

      • ARIA256

      • SEED128

      • GOST256

      If you omit the algorithm, then the default, AES256, is used.

    • FORCE KEYSTORE temporarily opens the password-protected keystore for this operation. You must open the keystore for this operation.

    • CONTAINER: If you include this clause, then set it to CURRENT. (CURRENT is the default.)

    The following example includes a user-created TDE master encryption key but no TDE master encryption key ID, so that the TDE master encryption key is generated:

    ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY 
    '3D432109DF88967A541967062A6F4E460E892318E307F017BA048707B402493C' 
    USING ALGORITHM 'GOST256'
    FORCE KEYSTORE
    IDENTIFIED BY keystore_password;

    The next example creates user-defined keys for both the master encryption ID and the TDE master encryption key. It omits the algorithm specification, so the default algorithm AES256 is used.

    ADMINISTER KEY MANAGEMENT CREATE ENCRYPTION KEY 
    '10203040506070801112131415161718:3D432109DF88967A541967062A6F4E460E892318E307F017BA048707B402493C' 
    IDENTIFIED BY keystore_password
    CONTAINER = CURRENT;

Example: Creating a Master Encryption Key in All PDBs

You can use the ADMINISTER KEY MANAGEMENT CREATE KEY USING TAG statement to create a TDE master encryption key in all PDBs.

Example 5-1 shows how to create a master encryption key in all of the PDBs in a multitenant environment. It uses the FORCE KEYSTORE clause in the event that the auto-login keystore in the CDB root is open. The password is stored externally, so the EXTERNAL STORE setting is used for the IDENTIFIED BY clause. After you execute this statement, a master encryption key is created in each PDB. You can find the identifiers for these keys as follows:

  • Log in to the PDB and then query the TAG column of the V$ENCRYPTION_KEYS view.

  • Log in to the CDB root and then query the INST_ID and TAG columns of the GV$ENCRYPTION_KEYS view.

You also can check the CREATION_TIME column of these views to find the most recently created key, which would be the key that you created from this statement. After you create the keys, you can individually activate the keys in each of the PDBs.

Example 5-1 Creating a Master Encryption Key in All of the PDBs

ADMINISTER KEY MANAGEMENT CREATE KEY USING TAG 
'scope:all pdbs;description:Create Key for ALL PDBS' 
FORCE KEYSTORE IDENTIFIED BY EXTERNAL STORE 
WITH BACKUP
CONTAINER = ALL;
 
keystore altered.

Creating a TDE Master Encryption Key for Later Use in United Mode

A keystore must be opened before you can create a TDE master encryption key for use later on in united mode.

  1. Log in to the CDB root or the united mode PDB as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
  2. Create the TDE master encryption key by using the following syntax:
    ADMINISTER KEY MANAGEMENT CREATE [ENCRYPTION] KEY 
    [FORCE KEYSTORE]
    [USING TAG 'tag_name']
    IDENTIFIED BY [EXTERNAL STORE | keystore_password] 
    [WITH BACKUP [USING 'backup_identifier']]
    [CONTAINER = ALL | CURRENT];
    

    In this specification:

    • FORCE KEYSTORE temporarily opens the password-protected keystore for this operation. You must open the keystore for this operation.

    • CONTAINER: In the CDB root, set CONTAINER to either ALL or CURRENT. In a PDB, set it to CURRENT. In both cases, omitting CONTAINER defaults to CURRENT.

    For example:

    ADMINISTER KEY MANAGEMENT CREATE KEY 
    FORCE KEYSTORE 
    IDENTIFIED BY keystore_password
    WITH BACKUP
    CONTAINER = CURRENT;
  3. If necessary, activate the TDE master encryption key.
    1. Find the key ID.
      SELECT KEY_ID FROM V$ENCRYPTION_KEYS; 
      
      KEY_ID
      ----------------------------------------------------
      AWsHwVYC2U+Nv3RVphn/yAIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    2. Use this key ID to activate the key.
      ADMINISTER KEY MANAGEMENT USE KEY 
      'AWsHwVYC2U+Nv3RVphn/yAIAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' 
      USING TAG 'quarter:second;description:Activate Key on standby' 
      IDENTIFIED BY password 
      WITH BACKUP;

Activating a TDE Master Encryption Key in United Mode

To activate a TDE master encryption key in united mode, you must open the keystore and use ADMINISTER KEY MANAGEMENT with the USE KEY clause.

  1. Log in to the CDB root or the united mode PDB as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

  2. Query the ORIGIN and KEY_ID columns of the V$ENCRYPTION_KEYS view to find the key identifier.

    For example:

    SELECT ORIGIN, KEY_ID FROM V$ENCRYPTION_KEYS; 
    
    ORIGIN  KEY_ID
    ------  ----------------------------------------------
    LOCAL   ARaHD762tUkkvyLgPzAi6hMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    
  3. Use this key identifier to activate the TDE master encryption key by using the following syntax:

    ADMINISTER KEY MANAGEMENT USE KEY 'key_identifier_from_V$ENCRYPTION_KEYS' 
    [USING TAG 'tag'] 
    [FORCE KEYSTORE] 
    IDENTIFIED BY [EXTERNAL STORE | keystore_password] 
    WITH BACKUP [USING 'backup_identifier']
    [CONTAINER = ALL | CURRENT];
    

    In this specification:

    • FORCE KEYSTORE temporarily opens the password-protected keystore for this operation. You must open the keystore for this operation.

    • CONTAINER: In the CDB root, set CONTAINER to either ALL or CURRENT. In a PDB, omit the CONTAINER clause. In both cases, omitting CONTAINER defaults to CURRENT.

    For example:

    ADMINISTER KEY MANAGEMENT USE KEY 'ARaHD762tUkkvyLgPzAi6hMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' 
    FORCE KEYSTORE 
    IDENTIFIED BY EXTERNAL STORE
    WITH BACKUP
    CONTAINER = ALL;

Rekeying the TDE Master Encryption Key in United Mode

You can use the ADMINISTER KEY MANAGEMENT statement with the SET KEY clause to rekey a TDE master encryption key.

  1. Log in to the CDB root or the united mode PDB as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

  2. If you are rekeying the TDE master encryption key for a keystore that has auto login enabled, then ensure that both the auto login keystore, identified by the .sso file, and the encryption keystore, identified by the .p12 file, are present.

    You can find the location of these files by querying the WRL_PARAMETER column of the V$ENCRYPTION_WALLET view. To find the WRL_PARAMETER values for all of the database instances, query the GV$ENCRYPTION_WALLET view.

  3. Rekey the TDE master encryption key by using the following syntax:

    ADMINISTER KEY MANAGEMENT SET [ENCRYPTION] KEY 
    [FORCE KEYSTORE]
    [USING TAG 'tag_name'] 
    IDENTIFIED BY [EXTERNAL STORE | keystore_password] 
    [WITH BACKUP [USING 'backup_identifier']]
    [CONTAINER = ALL | CURRENT];
    

    In this specification:

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

    • FORCE KEYSTORE temporarily opens the password-protected keystore for this operation. You must open the keystore for this operation.

    • IDENTIFIED BY can be one of the following settings:

      • EXTERNAL STORE uses the keystore password stored in the external store to perform the keystore operation.

      • keystore_password is the password that was created for this keystore.

    • CONTAINER: In the CDB root, set CONTAINER to either ALL or CURRENT. In a PDB, set it to CURRENT. In both cases, omitting CONTAINER defaults to CURRENT.

    For example:

    ADMINISTER KEY MANAGEMENT SET KEY 
    FORCE KEYSTORE 
    IDENTIFIED BY keystore_password 
    WITH BACKUP USING 'emp_key_backup'
    CONTAINER = CURRENT;
    
    keystore altered.
    

Finding the TDE Master Encryption Key That Is in Use in United Mode

A TDE master encryption key that is in use is the key that was activated most recently for the database.

In united mode, the TDE master encryption key in use of the PDB is the one that was activated most recently for that PDB.
  • To find the TDE master encryption key that is in use, query the V$ENCRYPTION_KEYS dynamic view.
    For example:
    SELECT KEY_ID 
    FROM V$ENCRYPTION_KEYS 
    WHERE ACTIVATION_TIME = (SELECT MAX(ACTIVATION_TIME) 
           FROM V$ENCRYPTION_KEYS
           WHERE ACTIVATING_PDBID = SYS_CONTEXT('USERENV', 'CON_ID'));

Creating a Custom Attribute Tag in United Mode

To create a custom attribute tag in united mode, you must use the SET TAG clause of the ADMINISTER KEY MANAGEMENT statement.

  1. Log in to the CDB root or the united mode PDB as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

  2. If necessary, query the TAG column of the V$ENCRYPTION_KEY dynamic view to find a listing of existing tags for the TDE master encryption keys.

    When you create a new tag for a TDE master encryption key, it overwrites the existing tag for that TDE master encryption key.

  3. Create the custom attribute tag by using the following syntax:

    ADMINISTER KEY MANAGEMENT SET TAG 'tag' 
    FOR 'master_key_identifier' 
    [FORCE KEYSTORE] 
    IDENTIFIED BY [EXTERNAL STORE | keystore_password] 
    WITH BACKUP [USING 'backup_identifier'];
    

    In this specification

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

    • master_key_identifier identifies the TDE master encryption key for which the tag is set. To find a list of TDE master encryption key identifiers, query the KEY_ID column of the V$ENCRYPTION_KEYS dynamic view.

    • FORCE KEYSTORE temporarily opens the password-protected keystore for this operation. You must open the keystore for this operation.

    • IDENTIFIED BY can be one of the following settings:

      • EXTERNAL STORE uses the keystore password stored in the external store to perform the keystore operation.

      • keystore_password is the password that was created for this keystore.

    • backup_identifier defines the tag values. Enclose this setting in single quotation marks (' ') and separate each value with a colon.

    For example, to create a tag that uses two values, one to capture a specific session ID and the second to capture a specific terminal ID:

    ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY 
    USING TAG 'sessionid=3205062574:terminal=xcvt' 
    IDENTIFIED BY keystore_password 
    WITH BACKUP;
    
    keystore altered.
    

    Both the session ID (3205062574) and terminal ID (xcvt) can derive their values by using either the SYS_CONTEXT function with the USERENV namespace, or by using the USERENV function.

Moving a TDE Master Encryption Key into a New Keystore in United Mode

In united mode, you can move an existing TDE master encryption key into a new keystore from an existing software password keystore.

This feature enables you to delete unused keys. After you move the key to a new keystore, you then can delete the old keystore.
  1. Log in to the CDB root or the united mode PDB as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
  2. Query the KEY_ID column of the V$ENCRYPTION_KEYS view to find the key identifier of the keystore to which you want to move the keys.
    For example:
    SELECT CREATION_TIME, KEY_ID FROM V$ENCRYPTION_KEYS; 
    
    CREATION TIME
    ----------------------------------------------------
    22-SEP-17 08.55.12.956170 PM +00:00
    
    KEY_ID
    ----------------------------------------------------
    ARaHD762tUkkvyLgPzAi6hMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
  3. Move the key into a new keystore by using the following syntax:
    ADMINISTER KEY MANAGEMENT 
    MOVE [ENCRYPTION] KEYS
    TO NEW KEYSTORE 'keystore_location1'
    IDENTIFIED BY keystore1_password
    FROM [FORCE] KEYSTORE
    IDENTIFIED BY keystore_password
    [WITH IDENTIFIER IN
    { 'key_identififier' [, 'key_identifier' ]... | ( subquery ) } ]
    [WITH BACKUP [USING 'backup_identifier']];

    In this specification:

    • keystore_location1 is the path to the wallet directory that will store the new keystore .p12 file. By default, this directory is in $ORACLE_BASE/admin/db_unique_name/wallet.

    • FORCE temporarily opens the keystore for this operation.

    • keystore_password is the password for the keystore from which the key is moving.

    For example:

    ADMINISTER KEY MANAGEMENT MOVE KEYS 
    TO NEW KEYSTORE '$ORACLE_BASE/admin/orcl/wallet' 
    IDENTIFIED BY keystore_password 
    FROM FORCE KEYSTORE 
    IDENTIFIED BY keystore_password 
    WITH IDENTIFIER IN 
    (SELECT KEY_ID FROM V$ENCRYPTION_KEYS WHERE ROWNUM < 2)
    WITH BACKUP;
  4. To delete the old keystore, go to the wallet directory and then manually delete the .p12 file containing the keystore.
    To find the location of the keystore, open the keystores, and then query the WRL_PARAMETER column of the V$ENCRYPTION_WALLET view.

Automatically Removing Inactive TDE Master Encryption Keys in United Mode

In united mode, the REMOVE_INACTIVE_STANDBY_TDE_MASTER_KEY initialization parameter can configure the automatic removal of inactive TDE master encryption keys.

  1. Log in to the server where the CDB root or the united mode PDB of the Oracle standby database resides.
  2. Locate the initialization parameter file for the database.
    By default, the initialization parameter fileis located in the $ORACLE_HOME/dbs directory.
  3. Edit the initialization parameter file to include the REMOVE_INACTIVE_STANDBY_TDE_MASTER_KEY initialization parameter.
    For example:
    remove_inactive_standby_tde_master_key = true

    Setting this parameter to TRUE enables the automatic removal of inactive TDE master encryption keys; setting it to FALSE disables the automatic removal.

Isolating a Pluggable Database Keystore

Isolating a PDB keystore moves the master encryption key from the CDB root keystore into an isolated mode keystore in the a PDB.

This process enables the keystore to be managed as a separate keystore in isolated mode. This way, an administrator who has been locally granted the ADMINISTER KEY MANAGEMENT privilege for the PDB can manage the keystore.
  1. Log in to the united mode PDB as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
    For example:
    sqlplus sec_admin@hrpdb as syskm
    Enter password: password
    Connected.
    
  2. Move the keys from the keystore of the CDB root into the isolated mode keystore of the PDB by using the following syntax:
    ADMINISTER KEY MANAGEMENT [FORCE] ISOLATE KEYSTORE
    IDENTIFIED BY isolated_keystore_password
    FROM ROOT KEYSTORE
    [FORCE KEYSTORE]
    IDENTIFIED BY
    [EXTERNAL STORE | united_keystore_password]
    [WITH BACKUP [USING backup_id]];

    In this specification:

    • FORCE is used when a clone of the PDB is using the master encryption key that is being isolated. The ADMINISTER KEY MANAGEMENT statement then copies (rather than moves) the keys from the wallet of the CDB root into the isolated mode PDB.

    • FORCE KEYSTORE temporarily opens the password-protected keystore for this operation if an auto-login keystore is open (and in use) or if the keystore is closed.

    • united_keystore_password: Knowledge of this password does not enable the user who performs the ISOLATE KEYSTORE operation privileges to perform ADMINISTER KEY MANAGEMENT UNITE KEYSTORE operations on the CDB root. This password is the same as the keystore password in the CDB root.

    After the keystore of a CDB root has been united with that of a PDB, all of the previously active (historical) master encryption keys that were associated with the CDB are moved to the keystore of the PDB.
  3. Confirm that the united mode PDB is now an isolated mode PDB.
    SELECT KEYSTORE_MODE FROM V$ENCRYPTION_WALLET;

    The output should be ISOLATED.

After the united mode PDB has been converted to an isolated mode PDB, you can change the password of the keystore.

Administering Transparent Data Encryption in United Mode

You can perform general administrative tasks with Transparent Data Encryption in united mode.

Moving PDBs from One CDB to Another in United Mode

In united mode, you can automatically move a PDB from one CDB to another (for example, for load balancing or adding new functionality).

If the PDB has TDE-encrypted tables or tablespaces, then you can set the ONE_STEP_PLUGIN_FOR_PDB_WITH_TDE dynamic system parameter to TRUE to enable the PDB to include the TDE keys in the PDB move operation. This parameter avoids you having to manually provide a keystore password when you import the TDE keys into the PDB after it has moved to a different CDB. When ONE_STEP_PLUGIN_FOR_PDB_WITH_TDE is set to TRUE, the database caches the keystore password in memory, obfuscated at the system level, and then uses it for the import operation. The default for ONE_STEP_PLUGIN_FOR_PDB_WITH_TDE is FALSE.
  1. Log in to the PDB as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
  2. Set the ONE_STEP_PLUGIN_FOR_PDB_WITH_TDE dynamic initialization parameter TRUE.
    For example:
    ALTER SYSTEM SET ONE_STEP_PLUGIN_FOR_PDB_WITH_TDE = TRUE;
  3. Move (relocate) the PDB.

Unplugging and Plugging a PDB with Encrypted Data in a CDB in United Mode

In united mode, for a PDB that has encrypted data, you can plug it into a CDB. Conversely, you can unplug this PDB from the CDB.

Unplugging a PDB That Has Encrypted Data in United Mode

In united mode, you can unplug a PDB with encrypted data and export it into an XML metadata file or an archive file.

The database that is unplugged contains data files and other associated files. You can check if a PDB has been unplugged by querying the STATUS column of the DBA_PDBS data dictionary view.
  1. In the CDB root, query the V$ENCRYPTION_WALLET dynamic view to ensure that the keystore is open.
  2. Use the ENCRYPT USING transport_secret clause in the ALTER PLUGGABLE DATABASE statement when you unplug the PDB.
    You must use this clause if the PDB has encrypted data. Otherwise, an ORA-46680: master keys of the container database must be exported error is returned.
    • For example, to export the PDB data into an XML file:
      ALTER PLUGGABLE DATABASE CDB1_PDB2 
      UNPLUG INTO '/tmp/cdb1_pdb2.xml' 
      ENCRYPT USING transport_secret;
    • To export the PDB data into an archive file:
      ALTER PLUGGABLE DATABASE CDB1_PDB2 
      UNPLUG INTO '/tmp/cdb1_pdb2.pdb' 
      ENCRYPT USING transport_secret; 
Plugging a PDB That Has Encrypted Data into a CDB in United Mode

To plug a PDB that has encrypted data into a CDB, you first plug in the PDB and then you can set the keystore in the PDB.

When you plug an unplugged PDB into another CDB, the key version is set to 0 because this operation invalidates the history of the previous keys. You can check the key version by querying the KEY_VERSION column of the V$ENCRYPTED_TABLESPACES dynamic view. Similarly, if a control file is lost and recreated, then the previous history of the keys is reset to 0. You can check if a PDB has already been plugged in by querying the STATUS column of the DBA_PDBS data dictionary view.
  1. From the CDB root, create the PDB by plugging the unplugged PDB into the CDB.

    To perform this operation for united mode, include the DECRYPT USING transport_secret clause.

    You must use this clause if the metadata or archive file for the PDB has encrypted data. Otherwise, an ORA-46680: master keys of the container database must be exported error is returned.

    • For example, if you had exported the PDB data into a metadata XML file:

      CREATE PLUGGABLE DATABASE CDB1_PDB2 
      USING '/tmp/cdb1_pdb2.xml' 
      NOCOPY KEYSTORE 
      IDENTIFIED BY password 
      DECRYPT USING transport_secret;
    • If you had exported the PDB into an archive file:

      CREATE PLUGGABLE DATABASE CDB1_PDB2 
      USING '/tmp/cdb1_pdb2.pdb' 
      DECRYPT USING transport_secret;

    During the open operation of the PDB after the plug operation, Oracle Database determines if the PDB has encrypted data. If so, it opens the PDB in the RESTRICTED mode.

    If you want to create the PDB by cloning another PDB or from a non-CDB, and if the source database has encrypted data or a TDE master encryption key that has been set, then you must provide the keystore password by including the keystore identified by keystore_password clause in the CREATE PLUGGABLE DATABASE ... FROM SQL statement. You must provide this password even if the source database is using an auto-login software keystore. You can find if the source database has encrypted data or a TDE master encryption key set in the keystore by querying the V$ENCRYPTION_KEYS dynamic view

  2. Open the PDB.

    For example:

    ALTER PLUGGABLE DATABASE CDB1_PDB2 OPEN;
  3. Open the keystore in the CDB root.

    For example:

    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN 
    IDENTIFIED BY password;

    Optionally, open the keystore in the PDB.

  4. In either the CDB root or the PDB (if the keystore is open in the PDB), set the TDE master encryption key for the PDB.

    For example:

    ADMINISTER KEY MANAGEMENT SET KEY 
    IDENTIFIED BY keystore_password 
    WITH BACKUP USING 'emp_key_backup';
Unplugging a PDB That Has Master Encryption Keys Stored in a Hardware Keystore in United Mode

You can unplug a PDB from one CDB that has been configured with an HSM and then plug it into another CDB also configured with an hardware keystore.

  1. Unplug the PDB.
    You can check if a PDB has already been unplugged by querying the STATUS column of the DBA_PDBS data dictionary view.
  2. Move the master encryption keys of the unplugged PDB in the hardware keystore that was used at the source CDB to the hardware keystore that is in use at the destination CDB.

    Refer to the documentation for the hardware keystore for information about moving master encryption keys between hardware keystores.

Plugging a PDB That Has Master Encryption Keys Stored in a Hardware Keystore in United Mode

The ADMINISTER KEY MANAGEMENT statement can import a TDE master encryption key from a hardware keystore to a PDB that has been moved to another CDB.

  1. Plug the unplugged PDB into the destination CDB that has been configured with the hardware keystore.
    You can check if a PDB has already been plugged in by querying the STATUS column of the DBA_PDBS data dictionary view.
    After the plug-in operation, the PDB that has been plugged in will be in restricted mode.
  2. Ensure that the master encryption keys from the hardware keystore that has been configured with the source CDB are available in the hardware keystore of the destination CDB.
  3. Log in to the plugged PDB as a user who was granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

    For example:

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

    Contact your SYSDBA administrator for the correct PDB. To check the current container, run the SHOW CON_NAME command.

  4. Open the master encryption key of the plugged PDB.

    For example, for a PDB called PDB1:

    ALTER SESSION SET CONTAINER = PDB1;
    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN 
    IDENTIFIED BY keystore_passsword;
  5. Import the hardware keystore master encryption key into the PDB.
    ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS 
    WITH SECRET "HSM" FROM 'HSM' 
    IDENTIFIED BY keystore_password; 
  6. Restart the PDB.
    ALTER PLUGGABLE DATABASE PDB1 CLOSE;
    ALTER PLUGGABLE DATABASE PDB1 OPEN;

Managing Cloned PDBs with Encrypted Data in United Mode

In united mode, you can clone a PDB that has encrypted data in a CDB.

About Managing Cloned PDBs That Have Encrypted Data in United Mode

When you clone a PDB, you must make the master encryption key of the source PDB available to cloned PDB.

This allows a cloned PDB to operate on the encrypted data. To perform the clone, you do not need to export and import the keys because Oracle Database transports the keys for you even if the cloned PDB is in a remote CDB. However, you will need to provide the keystore password of the CDB where you are creating the clone.

If the PDBs have encrypted data, then you can perform remote clone operations on PDBs between CDBs, and relocate PDBs across CDBs.

Cloning a PDB with Encrypted Data in a CDB in United Mode

The CREATE PLUGGABLE DATABASE statement with the KEYSTORE IDENTIFIED BY clause can clone a PDB that has encrypted data.

  1. In the CDB root, query the STATUS column of the V$ENCRYPTION_WALLET dynamic view to ensure that the keystore is open in the CDB root.
  2. Log in to the PDB as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
  3. Use the CREATE PLUGGABLE DATABASE statement with the KEYSTORE IDENTIFIED BY clause to clone the PDB.

    For example:

    CREATE PLUGGABLE DATABASE cdb1_pdb3 
    FROM cdb1_pdb1 
    FILE_NAME_CONVERT=('cdb1_pdb1', 'pdb3/cdb1_pdb3') KEYSTORE 
    IDENTIFIED BY keystore_password;

    Replace keystore_password with the password of the keystore of the CDB where the cdb1_pdb3 clone is created.

    After you create the cloned PDB, encrypted data is still accessible by the clone using the master encryption key of the original PDB. After a PDB is cloned, there may be user data in the encrypted tablespaces. This encrypted data is still accessible because the master encryption key of the source PDB is copied over to the destination PDB. Because the clone is a copy of the source PDB but will eventually follow its own course and have its own data and security policies, you should rekey the master encrytion key of the cloned PDB.
  4. Rekey the master encryption key of the cloned PDB.

    For example:

    ADMINISTER KEY MANAGEMENT SET KEY 
    FORCE KEYSTORE
    IDENTIFIED BY keystore_password 
    WITH BACKUP USING 'emp_key_backup';

    In this example, FORCE KEYSTORE is included because the keystore must be open during the rekey operation.

    Before you rekey the master encryption key of the cloned PDB, the clone can still use master encryption keys that belong to the original PDB. However, these master encryption keys do not appear in the cloned PDB V$ dynamic views. Rekeying the master encryption key ensures that the cloned PDB uses its own unique keys, which will be viewable in the V$ views.
Performing a Remote Clone of PDB with Encrypted Data Between Two CDBs in United Mode

The CREATE PLUGGABLE DATABASE statement with the KEYSTORE IDENTIFIED BY clause can remotely clone a PDB that has encrypted data.

  1. In the CDB root, query the STATUS column of the V$ENCRYPTION_WALLET dynamic view to ensure that the keystore is open in the CDB root.
  2. Log in to the PDB as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
  3. Create a database link for the PDB that you want to clone remotely.
    Use the CREATE DATABASE LINK SQL statement to create the database link. You must create the database link by following the database link prerequisites that are required for cloning a remote PDB or a non-CDB.
  4. Use the CREATE PLUGGABLE DATABASE statement with the KEYSTORE IDENTIFIED BY clause to perform the clone of the PDB.

    For example:

    CREATE PLUGGABLE DATABASE cdb1_pdb3 
    FROM cdb1_pdb1 
    FILE_NAME_CONVERT=('cdb1_pdb1', 'pdb3/cdb1_pdb3') KEYSTORE 
    IDENTIFIED BY keystore_password;

    Replace keystore_password with the password of the keystore of the CDB where the cdb1_pdb3 clone is created.

    After you create the cloned PDB, encrypted data is still accessible by the clone using the master encryption key of the original PDB. After a PDB is cloned, there may be user data in the encrypted tablespaces. This encrypted data is still accessible because the master encryption key of the source PDB is copied over to the destination PDB. Because the clone is a copy of the source PDB but will eventually follow its own course and have its own data and security policies, you should rekey the master encrytion key of the cloned PDB.
  5. Rekey the master encryption key of the remotely cloned PDB.

    For example:

    ADMINISTER KEY MANAGEMENT SET KEY 
    FORCE KEYSTORE
    IDENTIFIED BY keystore_password 
    WITH BACKUP USING 'emp_key_backup';

    In this example, FORCE KEYSTORE is included because the keystore must be open during the rekey operation.

    Before you rekey the master encryption key of the cloned PDB, the clone can still use master encryption keys that belong to the original PDB. However, these master encryption keys do not appear in the cloned PDB V$ dynamic views. Rekeying the master encryption key ensures that the cloned PDB uses its own unique keys, which will be viewable in the V$ views.
Relocating Across CDBs a Cloned PDB with Encrypted Data in United Mode

The CREATE PLUGGABLE DATABASE statement with the KEYSTORE IDENTIFIED BY clause can relocate across CDBs a cloned PDB that has encrypted data.

  1. In the CDB root, query the STATUS column of the V$ENCRYPTION_WALLET dynamic view to ensure that the keystore is open in the CDB root.
  2. Log in to the PDB as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
  3. Create a database link for the PDB that you want to clone.
    Use the CREATE DATABASE LINK SQL statement to create the database link. You must create the database link by following the database link prerequisites that are required for cloning a remote PDB or a non-CDB.
  4. Use the CREATE PLUGGABLE DATABASE statement with the KEYSTORE IDENTIFIED BY clause to clone the PDB.

    For example:

    CREATE PLUGGABLE DATABASE cdb1_pdb3 
    FROM cdb1_pdb1 
    FILE_NAME_CONVERT=('cdb1_pdb1', 'pdb3/cdb1_pdb3') KEYSTORE 
    IDENTIFIED BY keystore_password;

    Replace keystore_password with the password of the keystore of the CDB where the cdb1_pdb3 clone is created.

    After you create the cloned PDB, encrypted data is still accessible by the clone using the master encryption key of the original PDB. After a PDB is cloned, there may be user data in the encrypted tablespaces. This encrypted data is still accessible because the master encryption key of the source PDB is copied over to the destination PDB. Because the clone is a copy of the source PDB but will eventually follow its own course and have its own data and security policies, you should rekey the master encrytion key of the cloned PDB.
  5. Use the ALTER PLUGGABLE DATABASE statement to perform the relocation operation.
    For example:
    ALTER PLUGGABLE DATABASE cdb1_pdb3
    OPEN RELOCATE TO 'need instance name example';
  6. Rekey the master encryption key of the remotely cloned PDB.

    For example:

    ADMINISTER KEY MANAGEMENT SET KEY 
    FORCE KEYSTORE
    IDENTIFIED BY keystore_password 
    WITH BACKUP USING 'emp_key_backup';

    In this example, FORCE KEYSTORE is included because the keystore must be open during the rekey operation.

    Before you rekey the master encryption key of the cloned PDB, the clone can still use master encryption keys that belong to the original PDB. However, these master encryption keys do not appear in the cloned PDB V$ dynamic views. Rekeying the master encryption key ensures that the cloned PDB uses its own unique keys, which will be viewable in the V$ views.

How Keystore Open and Close Operations Work in United Mode

You should be aware of how keystore open and close operations work in united mode.

For each PDB in united mode, you must explicitly open the password-protected 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.

The open and close keystore operations in a PDB depend on the open and close status of the keystore in the CDB root.

Note the following:

  • You can create a separate keystore password for each PDB in united mode.

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

  • If an auto-login keystore is in use, or if the keystore is closed, then include the FORCE KEYSTORE clause in the ADMINISTER KEY MANAGEMENT statement when you open or close the keystore.

  • If the keystore is a password-protected software keystore that uses an external store for passwords, then set the IDENTIFIED BY clause to EXTERNAL STORE.

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

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

  • If you close the keystore in the CDB 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 perform an ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN statement in the CDB root and set the CONTAINER clause to ALL, then the keystore will only be opened in each PDB that is configured in united mode, and any PDB that is configured in isolated mode is not opened.

Finding the Keystore Status for All of the PDBs in United Mode

You can create a convenience function that uses the V$ENCRYPTION_WALLET view to find the status for keystores in all PDBs in a CDB.

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.

  • To create a function that uses theV$ENCRYPTION_WALLET view to find the keystore status, use the CREATE PROCEDURE PL/SQL statement.

Example 5-2 shows how to create this function.

Example 5-2 Function to Find the Keystore Status of 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;