8 Administering United Mode

Administering united mode means managing the keystores, master encryption keys, and general Transparent Database Encryption (TDE) functionality.

• Administering Keystores and 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.
• Administering Transparent Data Encryption in United Mode
  You can perform general administrative tasks with Transparent Data Encryption in united mode.

8.1 Administering Keystores and 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 TDE wallet or an external keystore only in the CDB root.
• Backing Up a Password-Protected TDE Wallet in United Mode
  The BACKUP KEYSTORE clause of the ADMINISTER KEY MANAGEMENT statement backs up a password-protected TDE wallet.
• Closing Keystores in United Mode
  You can close both TDE wallet and external keystores in united mode, unless the system tablespace is encrypted.
• Creating TDE Master Encryption Keys for Later Use in United Mode
  You can create a TDE master encryption key that can be activated at a later date.
• 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.
• Activating TDE Master Encryption Keys in United Mode
  After you activate a TDE master encryption key, it can be used.
• Creating User-Defined TDE Master Encryption Keys
  You can create a user-defined TDE master encryption key outside the database by generating a TDE master encryption key ID.
• 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.
• 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.
• 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.
• Moving TDE Master Encryption Keys into a New Keystore in United Mode
  You can move an existing TDE master encryption key into a new keystore from an existing password-protected keystore.
• 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.
• 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.

8.1.1 Changing the Keystore Password in United Mode

You can change the password of either a TDE wallet or an external keystore only in the CDB root.

• Changing the Password-Protected TDE Wallet Password in United Mode
  To change the password of a password-protected TDE wallet in united mode, you must use the ADMINISTER KEY MANAGEMENT statement in the CDB root.
• Changing the Password of an External Keystore in United Mode
  To change the password of an external keystore, you must close the external keystore and then change the password from the external keystore management interface.

8.1.1.1 Changing the Password-Protected TDE Wallet Password in United Mode

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

You can change this password at any time, as per the security policies, compliance guidelines, and other security requirements of your site. As part of the command to change the password, you will be forced to specify the WITH BACKUP clause, and thus forced to make a backup of the current TDE wallet. During the password change operation, Transparent Data Encryption operations such as encryption and decryption will continue to work normally. You can change this password at any time. You should change this password if you think it was compromised.

1. Connect to the CDB root as a common 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_TDE_wallet_password SET new_TDE_wallet_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_TDE_wallet_password is the current password that you want to change.

   • new_TDE_wallet_password is the new password.

   • 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 'before_password_change';
   
   keystore altered.

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

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

8.1.1.2 Changing the Password of an External Keystore in United Mode

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

1. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
2. Close the external keystore.
   • Close the connection to the external key manager:

     ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE 
     IDENTIFIED BY "external_key_manager_password"|EXTERNAL STORE 
     CONTAINER = ALL;
     

   • If the keystore was auto-opened by the database, then close the connection to the external key manager as follows:

     ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE 
     CONTAINER = ALL;
     

3. Change the Oracle Key Vault password.

   WALLET_ROOT/okv/bin/okvutil changepwd -t wallet -l WALLET_ROOT/okv/ssl

4. Update the Oracle Key Vault password that enables an auto-open Oracle Key Vault keystore to use the new password that you set in step 3.

   ADMINISTER KEY MANAGEMENT UPDATE SECRET 'new_Oracle_Key_Vault_password' 
   FOR CLIENT 'OKV-PASSWORD' TO [LOCAL] AUTO-LOGIN KEYSTORE 'WALLET_ROOT/tde';

5. Update the Oracle Key Vault password that enables IDENTIFIED BY EXTERNAL STORE (IBES) to the new password that you set in step 3.

   ADMINISTER KEY MANAGEMENT UPDATE SECRET 'new_Oracle_Key_Vault_password' 
   FOR CLIENT 'OKV-PASSWORD' TO [LOCAL] AUTO-LOGIN KEYSTORE 'WALLET_ROOT/tde_seps';

6. Open the connection to Oracle Key Vault.

   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN 
   IDENTIFIED BY EXTERNAL STORE;

8.1.2 Backing Up a Password-Protected TDE Wallet in United Mode

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

1. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
2. Back up the keystore by using the following syntax:

   ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE 
   [USING 'backup_identifier'] 
   [FORCE KEYSTORE] 
   IDENTIFIED BY [EXTERNAL STORE | TDE_wallet_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 TDE wallet in the same location as the source keystore.

   ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE 
   USING 'hr.emp_keystore' 
   FORCE KEYSTORE 
   IDENTIFIED BY 
   TDE_wallet_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 TDE wallet backup location.

8.1.3 Closing Keystores in United Mode

You can close both TDE wallet and external keystores in united mode, unless the system tablespace is encrypted.

• About Closing Keystores
  After you open a keystore, it remains open until you shut down the database instance.
• Closing a TDE Wallet in United Mode
  You can close password-protected TDE wallets, auto-login TDE wallets, and local auto-login TDE wallets in united mode.
• Closing an External Keystore in United Mode
  To close an external keystore, you must use the ADMINISTER KEY MANAGEMENT statement with the SET KEYSTORE CLOSE clause.

8.1.3.1 About Closing Keystores

After you open a keystore, it remains open until you shut down the database instance.

When you restart the database instance, then auto-login and local auto-login TDE wallets automatically open when required (that is, when the TDE master encryption key must be accessed). However, TDE wallet password-based and external keystores do not automatically open. You must manually open them again before you can use them.

When you close a TDE wallet or an external keystore, you disable all of the encryption and decryption operations on the database. Hence, a database user or application cannot perform any operation involving encrypted data until the TDE wallet or keystore is reopened.

When you re-open a TDE wallet keystore after closing it, its contents are reloaded back into the database. Thus, if the contents had been modified (such as during a migration), the database will have the latest TDE wallet or keystore contents.

When you run the ALTER PLUGGABLE DATABASE CLOSE statement or the SHUTDOWN command for a PDB, a keystore in the OPEN state for the PDB remains open until a user who has the SYSKM administrative privilege manually closes it with the ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE statement.

You can check if a TDE wallet or keystore is closed by querying the STATUS column of the V$ENCRYPTION_WALLET view.

The following data operations will fail if the TDE wallet or keystore is not accessible:

• SELECT data from an encrypted column

• INSERT data into on an encrypted column

• CREATE a table with encrypted columns

• CREATE an encrypted tablespace

8.1.3.2 Closing a TDE Wallet in United Mode

You can close password-protected TDE wallets, auto-login TDE wallets, and local auto-login TDE wallets in united mode.

In the case of an auto-login TDE wallet, 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 TDE wallet to a password-protected TDE wallet: you change the configuration to stop using the auto-login TDE wallet (by moving the auto-login TDE wallet to another location where it cannot be automatically opened), and then closing the auto-login TDE wallet.

1. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
2. Use the ADMINISTER KEY MANAGEMENT statement to close the TDE wallet.
   • For a password-protected TDE wallet, use the following syntax if you are in the CDB root:

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

     Use the SET clause to close the TDE wallet without force. If there is a dependent TDE wallet that is open (for example, an isolated mode PDB TDE wallet and you are trying to close the CDB root TDE wallet), 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 TDE wallet during the close operation. The STATUS column of the V$ENCRYPTION_WALLET view shows if a TDE wallet 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 TDE wallet, use this syntax if you are in the CDB root:

     ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE
     [CONTAINER =  ALL | CURRENT];

Closing a TDE wallet disables all of the encryption and decryption operations. Any attempt to encrypt or decrypt data or access encrypted data results in an error.

8.1.3.3 Closing an External Keystore in United Mode

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

1. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
   For example:

   CONNECT c##sec_admin AS SYSKM
   Enter password: password

2. Use the ADMINISTER KEY MANAGEMENT statement to close the external keystore.

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

   For example:

   ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE 
   IDENTIFIED BY "external_key_manager_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.

8.1.4 Creating TDE Master Encryption Keys for Later Use in United Mode

You can create a TDE master encryption key that can be activated at a later date.

• About Creating a TDE Master Encryption Key for Later Use
  The CREATE KEY clause of the ADMINISTER KEY MANAGEMENT statement can create a TDE master encryption key to be activated at a later date.
• 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.

8.1.4.1 About Creating a TDE Master Encryption Key for Later Use

The CREATE KEY clause of the ADMINISTER KEY MANAGEMENT statement can create a TDE master encryption key to be activated at a later date.

You then can activate this key on the same database or export it to another database and activate it there.

This method of TDE master encryption key creation is useful in a multitenant environment when you must re-create the TDE master encryption keys. The CREATE KEY clause enables you to use a single SQL statement to generate a new TDE master encryption key for all of the PDBs within a multitenant environment. The creation time of the new TDE master encryption key is later than the activation of the TDE master encryption key that is currently in use. Hence, the creation time can serve as a reminder to all of the PDBs to activate the most recently created TDE master encryption key as soon as possible.

8.1.4.2 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. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
   For example:

   CONNECT c##sec_admin AS SYSKM
   Enter password: password

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.

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

   • 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 mandatory keystore password that you used when you created the original keystore. It is case sensitive.

   • WITH BACKUP backs up the TDE master encryption key in the same location as the key, as identified by the WRL_PARAMETER column of the V$ENCRYPTION_WALLET view. To find the key locations for all of the database instances, query the GV$ENCRYPTION_WALLET view.

     You must back up password-based software keystores. You do not need to back up auto-login or local auto-login software keystores. Optionally, include the USING backup_identifier clause to add a description of the backup. Enclose backup_identifier in single quotation marks (' ').

   • 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;

8.1.5 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 8-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 run 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 8-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.

8.1.6 Activating TDE Master Encryption Keys in United Mode

After you activate a TDE master encryption key, it can be used.

• About Activating TDE Master Encryption Keys
  You can activate a previously created or imported TDE master encryption key by using the USE KEY clause of ADMINISTER KEY MANAGEMENT.
• 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.
• Example: Activating a TDE Master Encryption Key
  You can use the ADMINISTER KEY MANAGEMENT SQL statement to activate a TDE master encryption key.

8.1.6.1 About Activating TDE Master Encryption Keys

You can activate a previously created or imported TDE master encryption key by using the USE KEY clause of ADMINISTER KEY MANAGEMENT.

After you activate the master encryption key, it is used to encrypt all data encryption keys in your database. The key will be used to protect all of the column keys and all of the tablespace encryption keys. If you have deployed a logical standby database, then you must export the TDE master encryption keys after recreating them, and then import them into the standby database. You can have the TDE master encryption key in use on both the primary and the standby databases. To do so, you must activate the TDE master encryption key after you import it to the logical standby database.

8.1.6.2 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. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
   For example:

   CONNECT c##sec_admin AS SYSKM
   Enter password: password

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'];
   

   In this specification:

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

   • WITH BACKUP backs up the wallet in the same location as original wallet, as identified by WALLET_ROOT/tde. To find the key locations for all of the database instances, query the V$ENCRYPTION_WALLET or GV$ENCRYPTION_WALLET view.

     The WITH BACKUP clause is mandatory for all ADMINISTER KEY MANAGEMENT statements that modify the wallet. Optionally, include the USING backup_identifier clause to add a description of the backup. Enclose backup_identifier in single quotation marks (' ').

   For example:

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

8.1.6.3 Example: Activating a TDE Master Encryption Key

You can use the ADMINISTER KEY MANAGEMENT SQL statement to activate a TDE master encryption key.

Example 8-2 shows how to activate a previously imported TDE master encryption key and then update its tag. This key is activated with the current database time stamp and time zone.

Example 8-2 Activating a TDE Master Encryption Key

ADMINISTER KEY MANAGEMENT USE KEY 
'ARaHD762tUkkvyLgPzAi6hMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' 
USING TAG 'quarter:second;description:Activate Key on standby' 
IDENTIFIED BY password WITH BACKUP;

keystore altered.

In this version of the same operation, the FORCE KEYSTORE clause is added in the event that the auto-login keystore is in use, or if the keystore is closed. The password of the keystore is stored externally, so the EXTERNAL STORE setting is used for the IDENTIFIED BY clause.

ADMINISTER KEY MANAGEMENT USE KEY 
'ARaHD762tUkkvyLgPzAi6hMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' 
USING TAG 'quarter:second;description:Activate Key on standby' 
FORCE KEYSTORE IDENTIFIED BY EXTERNAL STORE WITH BACKUP;

keystore altered.

8.1.7 Creating User-Defined TDE Master Encryption Keys

You can create a user-defined TDE master encryption key outside the database by generating a TDE master encryption key ID.

• About User-Defined TDE Master Encryption Keys
  A TDE master encryption key that is outside the database has its own user-generated ID, which tracks the use of the TDE master encryption key.
• 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.

8.1.7.1 About User-Defined TDE Master Encryption Keys

A TDE master encryption key that is outside the database has its own user-generated ID, which tracks the use of the TDE master encryption key.

You can use the ADMINISTER KEY MANAGEMENT to create and set user-defined TDE master encryption key IDs. After you generate the TDE master encryption key, you can bring this key into the database. Optionally, you can specify the TDE master encryption key ID in various ADMINISTER KEY MANAGEMENT statements.

This type of configuration benefits Oracle Fusion SaaS Cloud environments in that it enables you to generate a TDE master encryption key this complies with your site’s requirements. This key that you generate supports the current encryption algorithms and can be used for TDE wallets.

After you generate the TDE master encryption key ID, you can encrypt your data as you normally would.

The TDE master encryption key and its corresponding ID will not be captured by any auditing logs.

8.1.7.2 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. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
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]
   IDENTIFIED BY [EXTERNAL STORE | keystore_password] 
   [WITH BACKUP [USING 'backup_identifier']];

   In this specification:

   • SET | CREATE : Enter SET if you want to create the master and activate the TDE master encryption key now, or select CREATE if you want to create the key for later use, without activating it yet. For SET KEY and USE KEY, you must be connected to the database that is supposed to use that key (CDB root container, united or isolated PDB). For CREATE KEY, you can be connected any united PDB, or the CDB root container, or the specific isolated PDB that is supposed to use that key later.

   • 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:mk|mkid clause but include the mk value, 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.

   The following example creates a TDE master encryption key without the key-ID is when a database administrator performs that operation:

   First, generate the TDE master encryption key. For example, using OpenSSL:

   $ openssl rand -hex 32
   f24ce8cdae39742b5da7293da206fbd05a28b1e44c4781f801768cfdc3dbb6e2

   Set (create and activate) this ARIA master key with the following ADMINISTER KEY MANAGEMENT statement:

   ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY 
   'f24ce8cdae39742b5da7293da206fbd05a28b1e44c4781f801768cfdc3dbb6e2' 
   USING ALGORITHM 'ARIA256'
   FORCE KEYSTORE
   IDENTIFIED BY EXTERNAL STORE
   WITH BACKUP USING 'backup_identifier';

   The next example, where TDE master encryption key and key-ID are generated outside of the database, shows how to support separation of duties. A key administrator inserts the key with a key_ID into the wallet by using the ADMINISTER KEY MANAGEMENT CREATE [ENCRYPTION] KEY statement. Then, the key administrator sends the key_ID to the database administrator, who then activates the key by using the ADMINISTER KEY MANAGEMENT USE KEY clause.

   1. Create the key-ID, converting characters to upper case. For example, using OpenSSL:

      $ openssl rand 16 | xxd -u -p
      D20765EB721AF44D054B30FE87F8E49A

   2. Create the TDE master encryption key. For example, using OpenSSL:

      $ openssl rand -hex 32
      5a089c8ea6ee21cba774f61e58d102665df4a979a34757836b3d066a3eb3db11

   3. Create (but do not activate) a default AES256 master encryption key by inserting both the random key-ID and the key itself, separated by a :, into the wallet.

      ADMINISTER KEY MANAGEMENT CREATE ENCRYPTION KEY
      'D20765EB721AF44D054B30FE87F8E49A:5a089c8ea6ee21cba774f61e58d102665df4a979a34757836b3d066a3eb3db11'
      IDENTIFIED BY [EXTERNAL STORE | TDE_wallet_password]
      WITH BACKUP USING 'backup_identifier' WITH BACKUP;

   4. The database administrator can retrieve the BASE64 key-ID from the database with the following SELECT statement:

      SELECT ' ADMINISTER KEY MANAGEMENT USE KEY '''||KEY_ID||''' 
      USING TAG '''||SYS_CONTEXT('USERENV', 'CON_NAME')||' '||TO_CHAR (SYS_EXTRACT_UTC (SYSTIMESTAMP), 
      'YYYY-MM-DD HH24:MI:SS"Z"')||''' 
      FORCE KEYSTORE 
      IDENTIFIED BY EXTERNAL STORE WITH BACKUP;
      ' AS "USE KEY COMMAND" FROM DUAL, V$ENCRYPTION_KEYS WHERE TAG IS NULL;

8.1.8 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. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
   For example:

   CONNECT c##sec_admin AS SYSKM
   Enter password: password

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.
   

8.1.9 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'));

8.1.10 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. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
   For example:

   CONNECT c##sec_admin AS SYSKM
   Enter password: password

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.

8.1.11 Moving TDE Master Encryption Keys into a New Keystore in United Mode

You can move an existing TDE master encryption key into a new keystore from an existing password-protected keystore.

• About Moving TDE Master Encryption Keys into a New Keystore
  You can move an unused (and safely archived) TDE master encryption key into a new keystore.
• 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.

8.1.11.1 About Moving TDE Master Encryption Keys into a New Keystore

You can move an unused (and safely archived) TDE master encryption key into a new keystore.

Use great caution when you decide to run ADMINISTER KEY MANAGEMENT MOVE KEYS. Even though this statement will not move an active master encryption key, it can still affect keys that are necessary for a range of database features. If you have deleted a key, then the data that was encrypted by that key is rendered permanently inaccessible (equivalent to deleting the data) for these features to be used. See Related Topics at the end of this topic for more information about features that are affected by deleted keystores.

Therefore, before you move the keystore, it is very important that you safely archive it. Delete the keystore only after a period of time has passed, to ensure that the keystore is no longer really useful.

To move a TDE master encryption key into a new keystore, you use the ADMINISTER KEY MANAGEMENT MOVE KEYS statement. This statement does not move the active TDE master encryption key (that is, the key that is currently in use at the time that ADMINISTER KEY MANAGEMENT MOVE KEYS is issued) because the database is currently using it.

If you mistakenly use the ADMINISTER KEY MANAGEMENT MOVE KEYS statement instead of ADMINISTER KEY MANAGEMENT MERGE KEYSTORE when you are configuring a new TDE keystore (for example, when you are changing the TDE keystore configuration from one where the TDE wallet is located in the operating system's file system to one where the TDE wallet is located in Oracle Automatic Storage Management (Oracle ASM)), then the following symptoms may help you to identify the TDE misconfiguration that was introduced by the use of the wrong key management command:

• When you open the TDE keystore that was the target of the earlier ADMINISTER KEY MANAGEMENT MOVE KEYS operation, an ORA-28374: typed master key not found in wallet error is seen, because the active TDE master encryption key was not moved to that keystore.
• The value shown in the STATUS column of the V$ENCRYPTION_WALLET view is OPEN_NO_MASTER_KEY after you open the new keystore. The OPEN_NO_MASTER_KEY status is expected, because the new TDE keystore that was mistakenly populated by means of the ADMINISTER KEY MANAGEMENT MOVE KEYS statement does not contain the active TDE master encryption key.

8.1.11.2 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 keystores. Before you move the keystore, ensure that it is safely archived. After you move the encryption key to a new keystore, and when you are sure that the old keystore is no longer needed, you then can delete the old keystore.

1. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
   For example:

   CONNECT c##sec_admin AS SYSKM
   Enter password: password

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-19 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.

   • keystore1_password is the password for the keystore from which the new keystore is moved.

   • FORCE temporarily opens the keystore for this operation.

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

   • subquery can be used to find the exact key identifier that you want.

   • backup_identifier is an optional description of the backup. Enclose backup_identifier in single quotation marks (' ').

   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 do the following:
   1. Back up the .p12 file containing the keystore that you want to delete.
   2. Manually delete the .p12 file containing the keystore.
   To find the location of the keystore, open the keystore, and then query the WRL_PARAMETER column of the V$ENCRYPTION_WALLET view.

8.1.12 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. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
   For example:

   CONNECT c##sec_admin AS SYSKM
   Enter password: password

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.

8.1.13 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. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
   For example:

   CONNECT c##sec_admin AS SYSKM
   Enter password: password

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.

8.2 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
  You can clone or relocate encrypted PDBs within the same container database, or across container databases.
• 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.
• Managing Cloned PDBs with Encrypted Data in United Mode
  In united mode, you can clone a PDB that has encrypted data in a CDB.
• 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.
• 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.

8.2.1 Moving PDBs from One CDB to Another in United Mode

You can clone or relocate encrypted PDBs within the same container database, or across container databases.

If you are trying to move a non-CDB or a PDB in which the SYSTEM, SYSAUX, UNDO, or TEMP tablespace is encrypted, and using the manual export or import of keys, then you must first import the keys for the non-CDB or PDB in the target database's CDB$ROOT before you create the PDB. Import of the keys are again required inside the PDB to associate the keys to the PDB.

• Clone or relocate the non-CDB or PDB using the following syntax:

  CREATE|RELOCATE PLUGGABLE DATABASE database_name KEYSTORE 
  IDENTIFIED BY EXTERNAL STORE|target_keystore_password [NO REKEY];

8.2.2 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 file or an archive file.
• 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 create a master encryption key for the PDB.
• Unplugging a PDB That Has Master Encryption Keys Stored in an External Keystore in United Mode
  You can unplug a PDB from one CDB that has been configured with an external keystore and then plug it into another CDB also configured with an external keystore.
• Plugging a PDB That Has Master Encryption Keys Stored in an External Keystore in United Mode
  The ADMINISTER KEY MANAGEMENT statement can import a TDE master encryption key from an external keystore to a PDB that has been moved to another CDB.

8.2.2.1 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 file or an archive file.

You can check if a PDB has been unplugged by querying the STATUS column of the DBA_PDBS data dictionary view.

1. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
   For example:

   CONNECT c##sec_admin AS SYSKM
   Enter password: password

2. Query the V$ENCRYPTION_WALLET dynamic view to ensure that the keystore is open.
3. Use the ENCRYPT USING transport_secret clause in the ALTER PLUGGABLE DATABASE statement when you unplug the PDB.
   This process extracts the master encryption keys that belong to that PDB from the open wallet, and encrypts those keys with the transport_secret clause.
   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; 

8.2.2.2 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 create a master encryption key for the PDB.

After you plug the PDB into the target CDB, and you must create a master encryption key that is unique to this plugged-in PDB. This is because the plugged-in PDB initially uses the key that was extracted from the wallet of the source 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. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
2. 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 XML 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 an XML file:

     CREATE PLUGGABLE DATABASE CDB1_PDB2 
     USING '/tmp/cdb1_pdb2.xml' 
     KEYSTORE IDENTIFIED BY EXTERNAL STORE|TDE_wallet_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' 
     KEYSTORE IDENTIFIED BY EXTERNAL STORE|TDE_wallet_password
     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 TDE wallet password of the target TDE wallet by including the KEYSTORE IDENTIFIED BY TDE_wallet_password clause in the CREATE PLUGGABLE DATABASE ... FROM SQL statement. You must provide this password even if the target database is using an auto-login TDE wallet. You can find if the source database has encrypted data or a TDE master encryption key set in the TDE wallet by querying the V$ENCRYPTION_KEYS dynamic view

3. Open the PDB.

   ALTER PLUGGABLE DATABASE pdb_name OPEN;

4. Open the TDE wallet in the CDB root by using one of the following methods:
   • If the TDE wallet of the CDB is not open, open it for the container and all open PDBs by using the following syntax:

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

   • If the TDE wallet of the CDB is open, connect to the plugged-in PDB and then open the keystore by using the following syntax:

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

5. Optionally, open the keystore in the PDB.
6. In the plugged-in PDB, set the TDE master encryption key for the PDB by using the following syntax:

   ADMINISTER KEY MANAGEMENT SET KEY
   [FORCE KEYSTORE]
   IDENTIFIED BY EXTERNAL STORE|TDE_wallet_password
   WITH BACKUP [USING 'backup_identifier'];

8.2.2.3 Unplugging a PDB That Has Master Encryption Keys Stored in an External Keystore in United Mode

You can unplug a PDB from one CDB that has been configured with an external keystore and then plug it into another CDB also configured with an external 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 external keystore that was used at the source CDB to the external keystore that is in use at the destination CDB.

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

8.2.2.4 Plugging a PDB That Has Master Encryption Keys Stored in an External Keystore in United Mode

The ADMINISTER KEY MANAGEMENT statement can import a TDE master encryption key from an external 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 external 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 external keystore that has been configured with the source CDB are available in the external keystore of the destination CDB.
3. Connect to the plugged PDB as a user who was granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

   For example:

   CONNECT sec_admin@pdb_name AS SYSKM
   Enter password: password
   Connected.
   

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

   For example:

   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN 
   IDENTIFIED BY keystore_passsword;

5. Import the external keystore master encryption key into the PDB.

   ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS 
   WITH SECRET "OKV" FROM 'OKV' 
   IDENTIFIED BY keystore_password; 

6. Restart the PDB.

   ALTER PLUGGABLE DATABASE PDB1 CLOSE;
   ALTER PLUGGABLE DATABASE PDB1 OPEN;

8.2.3 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.
• 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.
• Remotely Clone an Encrypted PDB in United Mode
  The CREATE PLUGGABLE DATABASE statement with the KEYSTORE IDENTIFIED BY clause can remotely clone a PDB that has encrypted data.
• Relocating an Encrypted PDB in United Mode
  The CREATE PLUGGABLE DATABASE statement with the KEYSTORE IDENTIFIED BY clause can relocate a PDB with encrypted data across CDBs.

8.2.3.1 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.

8.2.3.2 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. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
   For example:

   CONNECT c##sec_admin AS SYSKM
   Enter password: password

2. Query the STATUS column of the V$ENCRYPTION_WALLET dynamic view to ensure that the keystore is open in the CDB root.
3. Log in to the PDB as a user who has been granted the ADMINISTER KEY MANAGEMENT and the CREATE PLUGGABLE DATABASE privileges.
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 EXTERNAL STORE|keystore_password [NO REKEY];

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

   By default, during a PDB clone or relocate operation, the data encryption keys are rekeyed, which implies a re-encryption of all encrypted tablespaces. This rekey operation can increase the time it takes to clone or relocate a large PDB. With the optional NO REKEY clause, the data encryption keys are not renewed, and encrypted tablespaces are not re-encrypted.

   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 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.

8.2.3.3 Remotely Clone an Encrypted PDB in United Mode

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

1. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
   For example:

   CONNECT c##sec_admin AS SYSKM
   Enter password: password

2. Query the STATUS column of the V$ENCRYPTION_WALLET dynamic view to ensure that the keystore is open in the CDB root.
3. In this root container of the target database, create a database link that connects to the root container of the source CDB.

   CREATE DATABASE LINK clone_link 
   CONNECT TO C##REMOTE_CLONE_USER 
   IDENTIFIED BY C##REMOTE_CLONE_USER_PASSWORD 
   USING '//source_ip_address:port/DB_NAME';

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 cbd_1_pdb1@clone_link 
   FILE_NAME_CONVERT=('cdb1_pdb1', 'pdb3/cdb1_pdb3') KEYSTORE 
   KEYSTORE IDENTIFIED BY EXTERNAL STORE|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.

8.2.3.4 Relocating an Encrypted PDB in United Mode

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

1. Connect to the CDB root as a common user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
   For example:

   CONNECT c##sec_admin AS SYSKM
   Enter password: password

2. Query the STATUS column of the V$ENCRYPTION_WALLET dynamic view to ensure that the keystore is open in the CDB root.
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.
4. Use the CREATE PLUGGABLE DATABASE statement with the KEYSTORE IDENTIFIED BY clause to relocate the PDB.

   For example:

   CREATE PLUGGABLE DATABASE cdb1_pdb3 
   FROM cdb1_pdb1@clone_link RELOCATE [AVAILABILITY MAX] 
   [FILE_NAME_CONVERT=('cdb1_pdb1', 'pdb3/cdb1_pdb3')] 
   KEYSTORE IDENTIFIED BY EXTERNAL STORE|keystore_password;

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

   After you have relocated the PDB, the encrypted data is still accessible because the master encryption key of the source PDB is copied over to the destination PDB. The relocated PDB is a copy of the source PDB, but it will eventually follow its own course and have its own data and security policies. Therefore, you should rekey the master encrytion key of the cloned PDB.
5. Connect to the PDB as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
6. Rekey the master encryption key of the relocated PDB.

   For example:

   ADMINISTER KEY MANAGEMENT SET KEY 
   [FORCE KEYSTORE]
   IDENTIFIED BY EXTERNAL STORE|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.

8.2.4 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 TDE wallet or external keystore in the PDB to enable the Transparent Data Encryption operations to proceed. (Auto-login and local auto-login TDE wallets 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 password-protected TDE wallet or an external keystore in an individual PDB, you must open the wallet or keystore in the CDB root.

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

• If the keystore is a password-protected TDE wallet that uses an external store for passwords, then replace the password in the IDENTIFIED BY clause with EXTERNAL STORE.

• Before you can set a TDE master encryption key in an individual PDB, you must set the key in the CDB root. Oracle highly recommends that you include the USING TAG clause when you set keys in PDBs. For example:

  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 
  WITH BACKUP CONTAINER = CURRENT;' AS "SET KEY COMMAND" FROM DUAL;

  Including the USING TAG clause enables you to quickly and easily identify the keys that belong to a certain PDB, and when they were created.

• Auto-login and local auto-login TDE wallets 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 open PDB that is configured in united mode. Keystores for any PDBs that are configured in isolated mode are not opened.

8.2.5 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 an external 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 8-3 shows how to create this function.

Example 8-3 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;