4 Managing the Keystore and the TDE Master Encryption Key

You can modify and manage settings for the keystore and TDE master encryption key, and store secrets used by Oracle Database and store Oracle GoldenGate secrets in a keystore.

Topics:

Managing the Keystore

You can perform maintenance activities on keystores such as changing passwords, backing up keystores, merging keystores, moving keystores, handling keystores on ASM, and closing keystores.

Topics:

Changing the Password of a Password-Based Software Keystore

Oracle Database enables you to easily change password-based software keystore passwords.

Topics:

About Changing the Password of a Password-Based Software Keystore

You can only change (rotate) the password for password-based software keystores.

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 keystore. 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 may want to change this password if you think it was compromised.

Changing the Password-Based Software Keystore Password

To change the password of a password-based software keystore, you must use the ADMINISTER KEY MANAGEMENT statement.

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

    In a multitenant environment, log in to the root. For example:

    sqlplus c##sec_admin as syskm
    Enter password: password
    Connected.
    
  2. Run the following SQL statement:

    ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD IDENTIFIED BY
    old_password SET new_password [WITH BACKUP [USING 'backup_identifier']];
    

    In this specification:

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

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

    • WITH BACKUP creates a backup of the current keystore before the password is changed. You must include this clause.

    • backup_identifier specifies an optional identifier string for the backup that is created. The backup_identifier is added to the name of the backup file. Enclose backup_identifier in single quotation marks (' '). This identifier is appended to the named keystore file (for example, ewallet_time_stamp_emp_key_pwd_change.p12).

    The following example backs up the current keystore and then changes the password for the keystore:

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

Changing the Password of a Hardware Keystore

To change the password of a hardware keystore, you must use the ADMINISTER KEY MANAGEMENT statement.

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

    In a multitenant environment, log in to the root. For example:

    sqlplus c##sec_admin as syskm
    Enter password: password
    Connected.
    
  2. Close the hardware keystore.

    For example:

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

    See "Closing a Hardware Keystore".

  3. From the hardware security module management interface, create a new hardware security module password.

  4. In SQL*Plus, open the hardware keystore.

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

    See "Step 3: Open the Software Keystore".

Backing Up Password-Based Software Keystores

When you back up a password-based software keystore, you optionally can create a backup identifier string to describe the type of backup.

Topics:

About Backing Up Password-Based Software Keystores

You must back up password-based software keystores, as per the security policy and requirements of your site.

A backup of the keystore contains all of the keys contained in the original keystore. Oracle Database prefixes the backup keystore with the creation time stamp (UTC). If you provide an identifier string, then this string is inserted between the time stamp and keystore name.

After you complete the backup operation, the keys in the original keystore are marked as "backed up". You can check the status of keys querying the V$ENCRYPTION_WALLET data dictionary view.

You cannot back up auto-login or local auto-login software keystores. No new keys can be added to them directly through the ADMINISTER KEY MANAGEMENT statement operations. The information in these keystores is only read and hence there is no need for a backup.

If you have not yet backed up the keystore, then you can include the BACKUP clause in the ADMINISTER KEY MANAGEMENT statement when you create the TDE master encryption key. This both backs up the keystore and creates the TDE master encryption key. ("Step 4: Set the Software TDE Master Encryption Key" shows an example of how to accomplish this.)

Creating a Backup Identifier String for the Backup Keystore

The backup file name of a software password keystore is derived from the name of the password-based software keystore.

Oracle Database prefixes the software keystore password file name with the file creation time stamp in UTC format. If you provide an identifier string, then this string is inserted between the time stamp and keystore name.

  • To create a backup identifier string for a backup keystore, use the ADMINISTER KEY MANAGEMENT SQL statement with the BACKUP KEYSTORE clause, with the following syntax:

    ewallet_creation-time-stamp-in-UTC_user-defined-string.p12
    

    When you create the backup identifier (user_defined_string), use the operating system file naming convention. For example, in UNIX systems, you may want to ensure that this setting does not have spaces.

Example 4-1 shows the creation of a backup keystore that uses a bug number as the user-identified string, and how the resultant keystore appears in the file system.

Example 4-1 Creating a Backup Identifier String for a Backup Keystore

ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'BUG12966094' IDENTIFIED BY keystore_password;

Resultant keystore file:

ewallet_2013041513244657_BUG12966094.p12

How the V$ENCRYPTION_WALLET View Interprets Backup Operations

In the V$ENCRYPTION_WALLET view, the BACKUP column indicates if a copy of the keystore had been created with the WITH BACKUP clause of the ADMINISTER KEY MANAGEMENT statement or the ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE statement.

When you modify a key or a secret, the modifications that you make do not exist in the previously backed-up copy, because you make a copy and then modify the key itself. Because there is no copy of the modification in the previous keystores, the BACKUP column is set to NO, even if the BACKUP had been set to YES previously. Hence, if the BACKUP column is YES, then after you perform an operation that requires a backup, such as adding a custom attribute tag, the BACKUP column value changes to NO.

Backing Up a Password-Based Software Keystore

To back up a password-based software keystore, you must use the ADMINISTER KEY MANAGEMENT statement with the BACKUP KEYSTORE clause.

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

    In a multitenant environment, log in to the root. For example:

    sqlplus c##sec_admin as syskm
    Enter password: password
    Connected.
    
  2. Run the following SQL statement:

    ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE [USING 'backup_identifier'] 
    IDENTIFIED BY 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).

    • software_keystore_password is the password for the keystore.

    • keystore_location is the path at which the backup keystore is stored. 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 (' ').

    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' IDENTIFIED BY 
    password TO '/etc/ORACLE/KEYSTORE/DB1/';
    
    keystore altered.
    

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

Backups of the Hardware Keystore

You cannot use Oracle Database to back up hardware keystores.

See your HSM vendor instructions for information about backing up keys for hardware keystores.

Merging Software Keystores

You can merge software keystores in a variety of ways, such as merging two keystores to create a third keystore, merging one keystore into an existing keystore, or merging an auto-login software keystore into a password-based software keystore.

Topics:

About Merging Software Keystores

You can merge any combination of the software keystores. However, the merged keystore must be a password-based software keystore, and it can have a password that is different from the constituent keystores.

To use the merged keystore, you must explicitly open the merged keystore after you create it, even if one of the constituent keystores was already open before the merge.

Whether a common key from two source keystores is added or overwritten to a merged keystore depends on how you write the ADMINISTER KEY MANAGEMENT merge statement. For example, if you merge Keystore 1 and Keystore 2 to create Keystore 3, then the key in Keystore 1 is added to Keystore 3. If you merge Keystore 1 into Keystore 2, then the common key in Keystore 2 is not overwritten.

The ADMINISTER KEY MANAGEMENT merge statement has no bearing on the configured keystore that is in use. However, the merged keystore can be used as the new configured database keystore if you want. Remember that you must reopen the keystore if you are using the newly created keystore as the keystore for the database at the location configured by the sqlnet.ora file.

Merging Two Software Keystores into a Third New Keystore

You can merge two software keystores into a third new keystore, so that the two existing keystores are not changed.

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

    In a multitenant environment, log in to the root. For example:

    sqlplus c##sec_admin as syskm
    Enter password: password
    Connected.
    
  2. Run the following SQL statement:
    ADMINISTER KEY MANAGEMENT MERGE KEYSTORE 'keystore1_location' 
    [IDENTIFIED BY software_keystore1_password] AND KEYSTORE 'keystore2_location' 
    [IDENTIFIED BY software_keystore2_password] 
    INTO NEW KEYSTORE 'keystore3_location' 
    IDENTIFIED BY software_keystore3_password;
    

    In this specification:

    • keystore1_location is the directory location of the first keystore, which will be left unchanged after the merge. Enclose this path in single quotation marks (' ').

    • The IDENTIFIED BY clause is required for the first keystore if it is a password-based keystore. software_keystore1_password is the current password for the first keystore.

    • keystore2_location is the directory location of the second keystore. Enclose this path in single quotation marks (' ').

    • The IDENTIFIED BY clause is required for the second keystore if it is a password-based keystore. software_keystore2_password is the current password for the second keystore.

    • keystore3_location specifies the directory location of the new, merged keystore. Enclose this path in single quotation marks (' '). If there is already an existing keystore at this location, the command exits with an error.

    • software_keystore3_password is the new password for the merged keystore.

    The following example merges an auto-login software keystore with a password-based keystore to create a merged password-based keystore at a new location:

    ADMINISTER KEY MANAGEMENT MERGE KEYSTORE '/etc/ORACLE/KEYSTORE/DB1' 
    AND KEYSTORE '/etc/ORACLE/KEYSTORE/DB2' 
    IDENTIFIED BY existing_password_for_keystore_2 
    INTO NEW KEYSTORE '/etc/ORACLE/KEYSTORE/DB3' 
    IDENTIFIED BY new_password_for_keystore_3;
    
    keystore altered.
    

Merging One Software Keystore into an Existing Software Keystore

You can use the ADMINISTER KEY MANAGEMENT statement with the MERGE KEYSTORE clause to merge one software keystore into another existing software keystore.

  • To perform this type of merge, follow the steps in "Merging Two Software Keystores into a Third New Keystore" but use the following SQL statement:

    ADMINISTER KEY MANAGEMENT MERGE KEYSTORE 'keystore1_location' 
    [IDENTIFIED BY software_keystore1_password] 
    INTO EXISTING KEYSTORE 'keystore2_location' 
    IDENTIFIED BY software_keystore2_password 
    [WITH BACKUP [USING 'backup_identifier]];
    

    In this specification:

    • keystore1_location is the directory location of the first keystore, which will be left unchanged after the merge. Enclose this path in single quotation marks (' ').

    • The IDENTIFIED BY clause is required for the first keystore if it is a password-based keystore. software_keystore1_password is the password for the first keystore.

    • keystore2_location is the directory location of the second keystore into which the first keystore is to be merged. Enclose this path in single quotation marks (' ').

    • software_keystore2_password is the password for the second keystore.

    • WITH BACKUP creates a backup of the software keystore. Optionally, you can use the USING clause to add a brief description of the backup. Enclose this description in single quotation marks (' '). This identifier is appended to the named keystore file (for example, ewallet_time-stamp_emp_key_backup.p12, with emp_key_backup being the backup identifier). Follow the file naming conventions that your operating system uses.

The resultant keystore after the merge operation is always a password-based keystore.

Merging an Auto-Login Software Keystore into an Existing Password-Based Software Keystore

You can merge an auto-login software keystore into an existing password-based software keystore.

  • Use the ADMINISTER KEY MANAGEMENT MERGE KEYSTORE SQL statement to merge an auto-login software keystore into an existing password-based software keystore.

Example 4-2 shows how to merge an auto-login software keystore into a password-based software keystore. It also creates a backup of the second keystore before creating the merged keystore.

Example 4-2 Merging a Software Auto-Login Keystore into a Password Keystore

ADMINISTER KEY MANAGEMENT MERGE KEYSTORE '/etc/ORACLE/KEYSTORE/DB1' 
INTO EXISTING KEYSTORE '/etc/ORACLE/KEYSTORE/DB2' 
IDENTIFIED BY password WITH BACKUP;

In this specification:

  • MERGE KEYSTORE must specify the auto-login keystore.

  • EXISTING KEYSTORE refers to the password keystore.

Reversing a Software Keystore Merge Operation

You cannot directly reverse a keystore merge operation.

When you merge a keystore into an existing keystore (rather than creating a new one), you must include the WITH BACKUP clause in the ADMINISTER KEY MANAGEMENT statement to create a backup of this existing keystore. Later on, if you decide that you must reverse the merge, you can replace the merged software keystore with the one that you backed up.

In other words, suppose you want merge Keystore A into Keystore B. By using the WITH BACKUP clause, you create a backup for Keystore B before the merge operation begins. (The original Keystore A is still intact.) To reverse the merge operation, revert to the backup that you made of Keystore B.

  • Use the ADMINISTER KEY MANAGEMENT MERGE KEYSTORE SQL statement to perform merge operations.

    • For example, to perform a merge operation into an existing keystore:

      ADMINISTER KEY MANAGEMENT MERGE KEYSTORE '/etc/ORACLE/KEYSTORE/DB1' 
      INTO EXISTING KEYSTORE '/etc/ORACLE/KEYSTORE/DB2' 
      IDENTIFIED BY password WITH BACKUP USING "merge1";
      

      Replace the new keystore with the backup keystore, which in this case would be named ewallet_time-stamp_merge1.p12.

    • To merge an auto-login keystore into a password-based keystore, use the ADMINISTER KEY MANAGEMENT MERGE KEYSTORE SQL statement.

Moving a Software Keystore to a New Location

To move a software keystore to a new location, you must back up and close the keystore, edit the sqlnet.ora file, and then physically move the keystore to the new location.

If you are using Oracle Key Vault, then you can configure a TDE direct connection where Key Vault directly manages the TDE master keys. In this case, you will never need to manually move the keystore to a new location. See Oracle Key Vault Administrator's Guide for more information about using a TDE direct connection.

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

    In a multitenant environment, log in to the root or to the pluggable database (PDB). For example, to log in to a PDB called hrpdb:

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

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

  2. Make a backup copy of the software keystore.

    See "Backing Up Password-Based Software Keystores".

  3. Close the software keystore.

    For example:

    ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE; -- For an auto-login software keystore
    
    ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY software_keystore_password; -- For a password-based software keystore
    
  4. Exit the database session.

    For example, if you are logged in to SQL*Plus:

    EXIT
    
  5. Back up and then manually edit the sqlnet.ora file to point to the new location where you want to move the keystore.

    See the "Step 1: Set the Software Keystore Location in the sqlnet.ora File" for more information.

  6. Use the operating system move command (such as mv) to move the keystore with all of its keys to the new directory location.

Migrating a Software Password Keystore to a Hardware Keystore and Vice Versa

You can migrate password-based software keystores to hardware keystores, and vice versa.

Topics:

Migrating from a Password-Based Software Keystore to a Hardware Keystore

Step 1: Convert the Software Keystore to Open with the Hardware Keystore

Tools such as Oracle Data Pump and Oracle Recovery Manager require access to the old software keystore to perform decryption and encryption operations on data that was exported or backed up using the software keystore.

  • Use the ADMINISTER KEY MANAGEMENT SQL statement to convert a software keystore to a open with a hardware keystore.

    • To set the software keystore password as that of the hardware keystore, use the following syntax:

      ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD 
      IDENTIFIED BY software_keystore_password 
      SET "user_id:password" WITH BACKUP [USING 'backup_identifier'];
      

      In this specification:

      • software_keystore_password is the same password that you used when creating the software keystore.

      • user_id:password is the new software keystore password which is the same as the password of the HSM.

      • WITH BACKUP creates a backup of the software keystore. Optionally, you can use the USING clause to add a brief description of the backup. Enclose this description in single quotation marks (' '). This identifier is appended to the named keystore file (for example, ewallet_time-stamp_emp_key_backup.p12, with emp_key_backup being the backup identifier). Follow the file naming conventions that your operating system uses.

    • To create an auto-login keystore for a software keystore, use the following syntax:

      ADMINISTER KEY MANAGEMENT CREATE [LOCAL] AUTO_LOGIN KEYSTORE 
      FROM KEYSTORE 'keystore_location' 
      IDENTIFIED BY software_keystore_password;
      

      In this specification:

      • LOCAL enables you to create a local auto-login software keystore. Otherwise, omit this clause if you want the keystore to be accessible by other computers.

      • keystore_location is the path to the keystore directory location of the keystore that is configured in the sqlnet.ora file.

      • software_keystore_password is the existing password of the configured software keystore.

Step 2: Configure sqlnet.ora for the Migration of the Password-Based Software Keystore

After keystore migration, you are ready to open both the software and hardware keystore operations to enable access to keys created in the software keystore when required.

For the software keystore to open with the hardware keystore, either the software keystore must have the same password as the hardware keystore, or alternatively, you can create an auto-login keystore for the software keystore.

If you are migrating from a software keystore to a hardware keystore, then you must edit the sqlnet.ora file to use the METHOD=HSM setting.

  • Use the following format in the sqlnet.ora file:

    ENCRYPTION_WALLET_LOCATION=
      (SOURCE=(METHOD=HSM)(METHOD_DATA=
        (DIRECTORY=path_to_keystore)))
    

    path_to_software_keystore is the path to the previously configured software keystore. Having both HSM and the DIRECTORY location in the ENCRYPTION_WALLET_LOCATION parameter indicates that you switched between using the software keystore and the hardware keystore in the past, and it also enables you to switch back easily in the future.

Note:

If a DIRECTORY value is present in the ENCRYPTION_WALLET_LOCATION parameter setting, then ensure that you do not delete it.

Although hardware keystores do not require a DIRECTORY value, Oracle Database uses this value to locate your software keystore when you migrate to and from a hardware security module.

Example 4-3 shows how to edit the sqlnet.ora file to format a software keystore to hardware security module-based keystore or the reverse:

Example 4-3 Sample ENCRYPTION_WALLET_LOCATION Entries

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=(METHOD=HSM)(METHOD_DATA=
    (DIRECTORY=/app/wallet)))
Step 3: Perform the Hardware Keystore Migration

You can use the ADMINISTER KEY MANAGEMENT SQL statement to perform a hardware keystore migration.

To migrate from the software keystore to hardware keystore, you must use the MIGRATE USING keystore_password clause in the ADMINISTER KEY MANAGEMENT SET KEY SQL statement to decrypt the existing TDE table keys and the tablespace encryption keys with the TDE master encryption key in the software keystore and then reencrypt them with the newly created TDE master encryption key in the hardware keystore.

After you complete the migration, you do not need to restart the database, nor do you need to manually re-open the hardware keystore. The migration process automatically reloads the keystore keys in memory.

  • Use the following syntax when you run the ADMINISTER KEY MANAGEMENT SQL statement for migration:

    ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY "user_id:password" MIGRATE USING software_keystore_password [WITH BACKUP [USING 'backup_identifier']];
    

    In this specification:

    • user_id:password is the user ID and password that was created in Step 3 under "Step 2: Configure the Hardware Security Module" (in Configuring Transparent Data Encryption). Enclose this setting in double quotation marks (" ") and separate user_id and password with a colon (:).

    • software_keystore_password is the same password that you used when creating the software keystore or that you have changed to in "Step 1: Convert the Software Keystore to Open with the Hardware Keystore".

    • USING enables you to add a brief description of the backup. Enclose this description in single quotation marks (' '). This identifier is appended to the named keystore file (for example, ewallet_time-stamp_emp_key_backup.p12, with emp_key_backup being the backup identifier). Follow the file naming conventions that your operating system uses.

Note:

If the database contains columns encrypted with a public key, then the columns are decrypted and reencrypted with an AES symmetric key generated by HSM-based Transparent Data Encryption.

Migrating Back from a Hardware Keystore to a Password-Based Software Keystore

About Migrating Back from a Hardware Keystore

If you want to switch from using a hardware keystore solution to a software keystore, then you can use reverse migration of the keystore.

After you complete the switch, keep the hardware security module, in case earlier backup files rely on the TDE master encryption keys in the hardware security module.

If you had originally migrated from the software keystore to the hardware security module and reconfigured the software keystore as described in "Migration of a Previously Configured TDE Master Encryption Key", then you already have an existing keystore with the same password as the HSM password. Reverse migration configures this keystore to act as the new software keystore with a new password. If your existing keystore is an auto-login software keystore and you have the password-based software keystore for this auto-login keystore, then use the password-based keystore. If the password-based keystore is not available, then merge the auto-login keystore into a newly created empty password-based keystore, and use the newly create password-based keystore.

If you do not have an existing keystore, then you must specify a keystore location in the sqlnet.ora file using the ENCRYPTION_WALLET_LOCATION parameter. When you perform the reverse migration, migrate to the previous keystore so that you do not lose the keys.

Step 1: Configure sqlnet.ora for the Reverse Migration

First, you must edit the sqlnet.ora file.

  • Set the following configuration in the sqlnet.ora file:

    ENCRYPTION_WALLET_LOCATION=
      (SOURCE=(METHOD=FILE)(METHOD_DATA=
        (DIRECTORY=path_to_keystore)))
    

    Replace path_to_keystore with the directory location of the destination keystore.

Step 2: Configuring the Keystore for the Reverse for the Reverse Migration

To perform a reverse migration on a keystore, you can use the ADMINISTER KEY MANAGEMENT statement with the SET ENCRYPTION KEY and REVERSE MIGRATE clauses.

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

    In a multitenant environment, log in to the root. For example:

    sqlplus c##sec_admin as syskm
    Enter password: password
    Connected.
    
  2. Run the following SQL statement:

    ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY software_keystore_password REVERSE MIGRATE USING "user_id:password" [WITH BACKUP [USING 'backup_identifier']]; 
    

    In this specification:

    • software_keystore_password is the password for the existing keystore or the new keystore.

    • user_id:password is the user ID and password that was created in Step 3 in "Step 2: Configure the Hardware Security Module" (in Configuring Transparent Data Encryption). If the pre-hardware security module software keystore is the new keystore, then you must ensure that it has the same password as the user_id:password before issuing the reverse migration command. Enclose this setting in double quotation marks (" ").

    • WITH BACKUP creates a backup of the software keystore. Optionally, you can include the USING clause to add a brief description of the backup. Enclose this description in single quotation marks (' '). This identifier is appended to the named keystore file (for example, ewallet_time-stamp_emp_key_backup.p12, with emp_key_backup being the backup identifier). Follow the file naming conventions that your operating system uses.

    For example:

    ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY IDENTIFIED BY password REVERSE MIGRATE USING "psmith:password" WITH BACKUP;
    
    keystore altered.
    
  3. Optionally, change the keystore password.

    See "Changing the Password of a Password-Based Software Keystore" for more information.

Step 3: Configuring the Hardware Keystore to Open with the Software Keystore

After you complete the migration, you do not need to restart the database, nor do you need to manually re-open the software keystore. The migration process automatically reloads the keystore keys in memory.

The hardware keystore may still be required after reverse migration because the old keys are likely to have been used for encrypted backups or by tools such as Oracle Data Pump and Oracle Recovery Manager. You should cache the hardware keystore credentials in the keystore so that the HSM can be opened with the software keystore. See "Configuring Auto-Login Hardware Security Modules" for more information about how to store the HSM credential in a migrated keystore.

Keystore Order After a Migration

After you perform a migration, keystores can be either primary or secondary in their order.

The WALLET_ORDER column of the V$ENCRYPTION_WALLET dynamic view describes whether a keystore is primary (that is, it holds the current TDE master encryption key) or if it is secondary (it holds the previous TDE master encryption key). The WRL_TYPE column describes the type of locator for the keystore (for example, FILE for the sqlnet.ora file). The WALLET_ORDER column shows SINGLE if two keystores are not configured together and no migration was ever performed previously.

Table 4-1 describes how the keystore order works after you perform a migration.


Table 4-1 Keystore Order After a Migration

Type of Migration Done WRL_TYPE WALLET_ORDER Description

Migration of software keystore to HSM

HSM

FILE

PRIMARY

SECONDARY

Both the HSM and software keystore are configured. The TDE master encryption key can be either in the HSM or the software keystore.

The TDE master encryption key is first searched in the HSM.

If the TDE master encryption key is not in the primary keystore (HSM), then it will be searched for in the software keystore.

All of the new TDE master encryption keys will be created in the primary keystore (in this case, the HSM).

Reverse migration of HSM to software keystore

FILE

HSM

PRIMARY

SECONDARY

Both the HSM and software keystore are configured. The TDE master encryption key can be either in the HSM or the software keystore.

The TDE master encryption key is first searched for in the software keystore.

If the TDE master encryption key is not present in the primary (that is, software) keystore, then it will be searched for in the HSM.

All of the new TDE master encryption keys will be created in the primary keystore (in this case, the software keystore).


Migration of Keystores to and from Oracle Key Vault

You can use Oracle Key Vault to migrate both software and hardware keystores to and from Oracle Key Vault. This enables you to manage the keystores centrally, and then share the keystores as necessary with other TDE-enabled databases in your enterprise.

Oracle Key Vault enables you to upload a keystore to a container called a virtual wallet, and then create a new virtual wallet from the contents of previously uploaded Oracle keystores. For example, suppose you previously uploaded a keystore that contains 5 keys. You can create a new virtual wallet that consists of only 3 of these keys. You then can download this keystore to another TDE-enabled database. This process does not modify the original keystore.

In addition to Oracle keystores, Oracle Key Vault enables you to securely share other security objects, such as credential files and Java keystores, across the enterprise. It prevents the loss of keys and keystores due to forgotten passwords or accidentally deleted keystores. You can use Oracle Key Vault with products other than TDE: Oracle Real Application Security (Oracle RAC), Oracle Active Data Guard, and Oracle GoldenGate. Oracle Key Vault facilitates the movement of encrypted data using Oracle Data Pump and Oracle Transportable Tablespaces.

Closing a Keystore

You can manually close software and hardware keystores.

Topics:

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 software keystores automatically open when required (that is, when the TDE master encryption key must be accessed). However, software password-based and hardware keystores do not automatically open. You must manually open them again before you can use them.

When you close a software or hardware 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 keystore is reopened.

When you re-open a keystore after closing it, the keystore 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 keystore contents.

You can check the status of a keystore, whether it is open or closed, by querying the STATUS column of the V$ENCRYPTION_WALLET view.

The following data operations will fail if the 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

Closing a Software Keystore

You can manually close password-based software keystores, auto-login software keystores, and local auto-login software keystores.

In the case of an auto-login keystore, which opens automatically when it is accessed, manually close it if you moved it to a new location. You do this if you are changing your configuration from an auto-login keystore to a password-based keystore: you move out the auto-login keystore, and then close the auto-login keystore.

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

    In a multitenant environment, you must close the keystore first in the root. Afterward, all keystores in the PDBs will close as well. For example, to log in to the root:

    sqlplus sec_admin as syskm
    Enter password: password
    Connected.
    

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

  2. Run the ADMINISTER KEY MANAGEMENT SQL statement.

    • For a password-based software keystore, use the following syntax:

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

      In this specification:

      • software_keystore_password is the password of the user who created the keystore.

      • CONTAINER is for use in a multitenant environment. Enter ALL to close the keystore in all of the PDBs in this multitenant container database (CDB), or CURRENT for the current PDB. If you run this ADMINISTER KEY MANAGEMENT statement in the root, then all of the keystores on all of the PDBs will close, irrespective of whether CONTAINER is set to ALL or to CURRENT.

    • For an auto-login or local auto-login software keystore, use the following SQL statement:

      ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE;
      

      You do not need to specify a password for this statement.

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

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

  1. Log into the database instance as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

    In a multitenant environment, log in to the root. For example:

    sqlplus c##sec_admin as syskm
    Enter password: password
    Connected.
    
  2. Run the following SQL statement:

    ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "user_id:password" [CONTAINER = ALL | CURRENT];
    

    In this specification:

    • user_id:password is the user ID and password that was created in Step 3 in "Step 2: Configure the Hardware Security Module". Enclose this setting in double quotation marks (" ") and separate user_id and password with a colon (:).

    • CONTAINER is for use in a multitenant environment. Enter ALL to close the keystore in all of the PDBs in this CDB, or CURRENT for the current PDB. If you run this ADMINISTER KEY MANAGEMENT statement in the root, then all of the keystores on all of the PDBs will close, irrespective of whether CONTAINER is set to ALL or to CURRENT.

    For example:

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

Using a Software Keystore That Resides on Automatic Storage Management Volumes

You can store a software keystore on an Automatic Storage Management (ASM) disk group.

  • Edit the sqlnet.ora file to use the location of an ASM disk group specified using the ASM file naming convention when you configure the DIRECTORY setting in the ENCRYPTION_WALLET_LOCATION setting. That is, you must use the plus sign (+) notation for the ASM file name.

For example:

ENCRYPTION_WALLET_LOCATION=
      (SOURCE=(METHOD=FILE)(METHOD_DATA=
        (DIRECTORY=+disk1/mydb/wallet)))

If you must move or merge software keystores between a regular file system and an ASM file system, then you can use the same keystore merge statements described in "Merging Software Keystores".

To manage keystores in an ASM environment, you can use the ASMCMD utility.

Backup and Recovery of Encrypted Data

For software keystores, you cannot access encrypted data without the TDE master encryption key.

Because the TDE master encryption key is stored in the keystore, you should periodically back up the software keystore in a secure location. You must back up a copy of the keystore whenever you set a new TDE master encryption key or perform any operation that writes to the keystore.

Do not back up the software keystore in the same location as the encrypted data. Back up the software keystore separately. This is especially true when you use the auto-login keystore, which does not require a password to open. In case the backup tape is lost, a malicious user should not be able to get both the encrypted data and the keystore.

Oracle Recovery Manager (Oracle RMAN) does not back up the software keystore as part of the database backup. When using a media manager such as Oracle Secure Backup with Oracle RMAN, Oracle Secure Backup automatically excludes auto-open keystores (the cwallet.sso files). However, it does not automatically exclude encryption keystores (the ewallet.p12 files). It is a good practice to add the following exclude data set statement to your Oracle Secure Backup configuration:

exclude name *.p12

This setting instructs Oracle Secure Backup to exclude the encryption keystore from the backup set.

If you lose the software keystore that stores the TDE master encryption key, then you can restore access to encrypted data by copying the backed-up version of the keystore to the appropriate location. If you archived the restored keystore after the last time that you reset the TDE master encryption key, then you do not need to take any additional action.

If the restored software keystore does not contain the most recent TDE master encryption key, then you can recover old data up to the point when the TDE master encryption key was reset by rolling back the state of the database to that point in time. All of the modifications to encrypted columns after the TDE master encryption key was reset are lost.

See Also:

Oracle Database Backup and Recovery User's Guide for information about recovering a database

Deletion of Keystores

Do not delete keystores, particularly if a keystore is in use. You can find if a keystore is in use by querying the WRL_PARAMETER column of the V$ENCRYPTION_WALLET view after you open the keystore.

The reason you should not delete a keystore is because the keystore contains a list of all of the keys that were used for the database. Deleting the keystore deletes these keys, and could result in the loss of encrypted data. Even if you decrypted all of the data in your database, you still should not delete the keystore, because the TDE master encryption key in the keystore is also used for other Oracle Database features, such as off-lined tablespaces, Oracle Recovery Manager, and Oracle Secure Backup.

The exception is in the case of software auto-login (or auto-login local) keystores. If you do not want to use this type of keystore, then ideally you should move it to a secure directory. Only delete an auto-login keystore if you are sure that it comes from a specific password-based software keystore and that this keystore is available. The keystore should be available and known.

Managing the TDE Master Encryption Key

You can manage the TDE master encryption key in several ways, such as creating it for future use, activating it, working with the master key attributes, setting and resetting it in the keystore, and exporting and importing it.

Topics:

Creating TDE Master Encryption Keys for Later Use

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

Topics:

About Creating a TDE Master Encryption Key for Later Use

You can create a TDE master encryption key that can be activated at a later date by using the CREATE KEY clause of the ADMINISTER KEY MANAGEMENT SQL statement. 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.

Creating a TDE Master Encryption Key for Later Use

To create a TDE master encryption key for use later on, you must open the keystore and then use the ADMINSTER KEY MANAGEMENT statement with the CREATE KEY clause.

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

    In a multitenant environment, log in to the root. For example:

    sqlplus c##sec_admin as syskm
    Enter password: password
    Connected.
    
  2. Ensure that the keystore is open.

    You can query the STATUS column of the V$ENCRYPTION_WALLET view to find if the keystore is open. If you find that you must open the keystore, then see the following sections:

  3. Run the following SQL statement:

    ADMINISTER KEY MANAGEMENT CREATE KEY [USING TAG 'tag'] IDENTIFIED BY keystore_password [WITH BACKUP [USING 'backup_identifier']] [CONTAINER = (ALL|CURRENT)];
    

    In this specification:

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

    • 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 is for use in a multitenant environment. Enter ALL to set the encryption key in all of the PDBs in this CDB, or CURRENT for the current PDB.

  4. If necessary, activate the TDE master encryption key.

    See "Activation of TDE Master Encryption Keys".

Example: Creating a TDE Master Encryption Key in a Single Database

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

Example 4-4 shows how to create a TDE master encryption key in a single database. After you run this statement, a TDE master encryption key with the tag definition is created in the keystore for that database. You can query the TAG column of the V$ENCRYPTION_KEYS view for the identifier of the newly created key. You can query the CREATION_TIME column to find the most recently created key, which would be the key that you created from this statement. You can export this key to another database if you want or activate it locally later on, as described in "Activation of TDE Master Encryption Keys".

Example 4-4 Creating a TDE Master Encryption Key in a Single Database

ADMINISTER KEY MANAGEMENT CREATE KEY USING TAG
'source:admin@source;target:db1@target' 
IDENTIFIED BY password WITH BACKUP;

keystore altered.

Example: Creating a TDE Master Encryption Key in All PDBs

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

Example 4-5 shows how to create a TDE master encryption key in all of the PDBs in a multitenant environment. After you run this statement, a TDE 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 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 4-5 Creating a TDE Master Encryption Key in All of the PDBs

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

Activation of TDE Master Encryption Keys

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

Topics:

About Activating TDE Master Encryption Keys

You can activate a previously created or imported TDE master encryption key by using the ACTIVATE KEY clause of the ADMINSTER KEY MANAGEMENT SQL statement.

After you activate the key, it is available for use. 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.

Activating a TDE Master Encryption Key

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

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

    In a multitenant environment, log in to the root. For example:

    sqlplus c##sec_admin as syskm
    Enter password: password
    Connected.
    
  2. Ensure that the keystore is open.

    You can query the STATUS column of the V$ENCRYPTION_WALLET view to find if the keystore is open. If you find that you must open the keystore, see the following sections:

  3. Query the KEY_ID column of the V$ENCRYPTION_KEYS view to find the key identifier.

    For example:

    SELECT KEY_ID FROM V$ENCRYPTION_KEYS; 
    
    KEY_ID
    ----------------------------------------------------
    ARaHD762tUkkvyLgPzAi6hMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    
  4. Run the following SQL statement:

    ADMINISTER KEY MANAGEMENT USE KEY 'key_identifier' [USING TAG 'tag'] 
    IDENTIFIED BY keystore_password [WITH BACKUP [USING 'backup_identifier']];
    

    In this specification:

    • key_identifier is the key identifier that you find from querying the KEY_ID column of the V$ENCRYPTION_KEYS view. Enclose this setting in single quotation marks (' ').

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

    • keystore_password is the mandatory keystore password that you used when you created the original keystore.

    • 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 is for use in a multitenant environment. Enter ALL to set the encryption key in all of the PDBs in this CDB, or CURRENT for the current PDB.

Example: Activating a TDE Master Encryption Key

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

Example 4-6 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 4-6 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.

TDE Master Encryption Key Attribute Management

Master encryption key attributes store information about the TDE master encryption key.

Topics:

TDE Master Encryption Key Attributes

Master encryption key attributes include detailed information about the TDE master encryption key.

The information contains the following types:

  • Key time stamp information: Internal security policies and compliance policies usually determine the key rotation frequency. You should expire keys when they reach the end of their lifetimes and then generate new keys. Time stamp attributes such as key creation time and activation time help you to determine the key age accurately, and automate key generation.

    The V$ENCRYPTION_KEYS view includes columns such as CREATION_TIME and ACTIVATION_TIME. See Oracle Database Reference for a complete description of the V$ENCRYPTION_KEYS view.

  • Key owner information: Key owner attributes help you to determine the user who created or activated the key. These attributes can be important for security, auditing, and tracking purposes. Key owner attributes also include key use information, such as whether the key is used for standalone TDE operations or used in a multitenant environment.

    The V$ENCRYPTION_KEYS view includes columns such as CREATOR, CREATOR_ID, USER, USER_ID, and KEY_USE.

  • Key source information: Keys often must be moved between databases for operations such as import-export operations and Data Guard-related operations. Key source attributes enable you to track the origin of each key. You can track whether a key was created locally or imported, and the database name and instance number of the database that created the key. In a multitenant environment, you can track the PDB where the key was created.

    The V$ENCRYPTION_KEYS view includes columns such as CREATOR_DBNAME, CREATOR_DBID, CREATOR_INSTANCE_NAME, CREATOR_INSTANCE_NUMBER, CREATOR_PDBNAME, and so on.

  • Key usage information: Key usage information determines the database or PDB where the key is being used. It also helps determine whether a key is in active use or not.

    The V$ENCRYPTION_KEYS view includes columns such as ACTIVATING_DBNAME, ACTIVATING_DBID, ACTIVATING_INSTANCE_NAME, ACTIVATING_PDBNAME, and so on.

  • User-defined information and other information: When creating a key, you can tag it with information using the TAG option. Each key contains important information such as whether or not it has been backed up.

    The V$ENCRYPTION_KEYS view includes columns such as KEY_ID, TAG, and other miscellaneous columns, for example BACKED_UP.

Finding the TDE Master Encryption Key That Is in Use

A TDE master encryption key that is in use is the key that was activated most recently for the database. In a multitenant environment, the master key in use of the PDB is the one that was activated most recently for that PDB.

  • To find the master key, query the V$ENCRYPTION_KEYS dynamic view.

    • To find the master key in use in a non-CDB:

      SELECT KEY_ID 
      FROM V$ENCRYPTION_KEYS 
      WHERE ACTIVATION_TIME = (SELECT MAX(ACTIVATION_TIME) 
                               FROM V$ENCRYPTION_KEYS
                               WHERE ACTIVATING_DBID = (SELECT DBID FROM V$DATABASE));
      
    • To find the master key in use in a CDB:

      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 Custom TDE Master Encryption Key Attributes for Reporting Purposes

Custom TDE master encryption key attributes enable you to defined attributes that are specific to your needs.

Topics:

About Creating Custom Attribute Tags

Attribute tags enable you to monitor specific activities users perform, such as accessing a particular terminal ID.

By default, Oracle Database defines a set of attributes that describe various characteristics of the TDE master encryption keys that you create, such as the creation time, database in which the TDE master encryption key is used, and so on. These attributes are captured by the V$ENCRYPTION_KEY dynamic view.

You can create custom attributes that can be captured by the TAG column of the V$ENCRYPTION_KEYS dynamic view. This enables you to define behaviors that you may want to monitor, such as users who perform activities on encryption keys. The tag can encompass multiple attributes, such as session IDs from a specific terminal.

Creating a Custom Attribute Tag

To create a custom attribute tag, you must use the ADMINISTER KEY MANAGEMENT statement with the SET TAG clause.

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

    In a multitenant environment, log in to the root. For example:

    sqlplus c##sec_admin as syskm
    Enter password: password
    Connected.
    
  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 tag as follows:

    ADMINISTER KEY MANAGEMENT SET TAG 'tag' FOR 'master_key_identifier' 
    IDENTIFIED BY 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.

    • keystore_password is the password that was used to create the 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. For a full list of predefined parameters for the USERENV namespace in the SYS_CONTEXT function, see Oracle Database SQL Language Reference.

After you create the tag for a TDE master encryption key, its name should appear in the TAG column of the V$ENCRYPTION_KEYS view for that TDE master encryption key. If you create a tag for the secret, then the tag appears in the SECRET_TAG column of the V$CLIENT_SECRETS view. If you create a secret with a tag, then the tag appears in the SECRET_TAG column of the V$CLIENT_SECRETS view.

See Also:

"Storing Oracle GoldenGate Secrets in a Keystore" for information about creating secrets

Setting and Resetting the TDE Master Encryption Key in the Keystore

You can set and reset the TDE master encryption key for both software keystores and hardware keystores.

Topics:

About Setting and Resetting the TDE Master Encryption Key in the Keystore

You can set and reset the TDE master encryption key for both software password-based and hardware keystores.

The TDE master encryption key is stored in an external security module (keystore), and it is used to protect the TDE table keys and tablespace encryption keys. By default, the TDE master encryption key is a system-generated random value created by Transparent Data Encryption (TDE).

Use the ADMINISTER KEY MANAGEMENT statement to set or reset (REKEY) the TDE master encryption key.

Before you can encrypt or decrypt database columns or tablespaces, you must generate a TDE master encryption key. Oracle Database uses the same TDE master encryption key for both TDE column encryption and TDE tablespace encryption. The following sections explain how to create a basic TDE master encryption key:

Creating and Backing Up a TDE Master Encryption Key and Applying a Tag to It

To create and back up a TDE master encryption key and then apply a tag to it, you must use the ADMINISTER KEY MANAGEMENT statement with the SET ENCRYPTION KEY, IDENTIFIED BY, and WITH BACKUP clauses.

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

    In a multitenant environment, log in to the root or to the PDB. For example:

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

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

  2. Run the following SQL statement:

    ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY [USING TAG 'tag'] 
    IDENTIFIED BY keystore_password WITH BACKUP 
    [USING 'backup_identifier'] [CONTAINER = ALL | CURRENT];
    

    In this specification:

    • tag is the tag that you want to create. Enclose this tag in single quotation marks (' '). (See "Creating Custom TDE Master Encryption Key Attributes for Reporting Purposes" for more information about tags.)

    • keystore_password is either software_keystore_password or user_id:password. The user_id:password setting is the hardware keystore user ID and password that was created in Step 3 under "Step 2: Configure the Hardware Security Module". As with software passwords, it is case sensitive. You must enclose the password string in double quotation marks (" "). Separate user_id and password with a colon (:).

    • WITH BACKUP backs the TDE master encryption key up in the same location as the key, as identified by 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.

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

    • CONTAINER is for use in a multitenant environment. Enter ALL to set the encryption key in all of the PDBs in this CDB, or CURRENT for the current PDB.

    For example:

    ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY USING TAG 'backups" 
    IDENTIFIED BY password WITH BACKUP USING 'hr.emp_key_backup';
    
    keystore altered.
    

Oracle Database uses the keystore in the keystore location specified by the ENCRYPTION_WALLET_LOCATION parameter in the sqlnet.ora file to store the TDE master encryption key. See "About the Keystore Location in the sqlnet.ora File" for information about how the ENCRYPTION_WALLET_LOCATION parameter works in the sqlnet.ora file.

About Resetting or Rotating (REKEY) the TDE Master Encryption Key

Oracle Database uses a unified master encryption key for both TDE column encryption and TDE tablespace encryption.

When you reset (or REKEY) the TDE master encryption key for TDE column encryption, the master encryption key for TDE tablespace encryption also is reset. Reset or regenerate the master encryption key only if it was compromised or as per the security policies of the organization. This process deactivates the previous TDE master encryption key.

You cannot change the TDE master encryption key or reset a TDE master encryption key for an auto-login keystore. Because auto-login keystores do not have a password, an administrator or a privileged user can change the keys without the knowledge of the security officer. However, if both the auto-login and the password-based keystores are present in the configured location (as set in the sqlnet.ora file), then when you reset the TDE master encryption key, a TDE master encryption key is added to both the auto-login and password-based keystores. If the auto-login keystore is in use in a location that is different from that of the password-based keystore, then you must re-create the auto-login keystore.

Note:

You cannot add new information to auto-login keystores separately.

Resetting or Rotating (REKEY) the TDE Master Encryption Key

To reset (or rotate, rekey) a TDE master encryption key, you must open the keystore and then use the ADMINISTER KEY MANAGEMENT statement with the SET ENCRYPTION KEY clause.

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

    In a multitenant environment, log in to the root or to the PDB. For example, to log in to a PDB called hrpdb:

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

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

  2. Ensure that the keystore is open.

    Query the STATUS column of the V$ENCRYPTION_WALLET view to find if the keystore is open. If the keystore is closed, then see the following sections for information about opening it:

  3. If you are resetting 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.

  4. Rotate the TDE master encryption key by using the following statement:

    ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY [USING TAG 'tag'] IDENTIFIED BY 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 (' ').

    • keystore_password is the mandatory keystore password that you created when you created the keystore in "Step 2: Create the Software Keystore".

    • WITH BACKUP creates a backup of the keystore. You must use this option for password-based and hardware keystores. Optionally, you can use the USING clause to add a brief description of the backup. Enclose this description in single quotation marks (' '). This identifier is appended to the named keystore file (for example, ewallet_time-stamp_emp_key_backup.p12). Follow the file naming conventions that your operating system uses.

    • CONTAINER is for use in a multitenant environment. Enter ALL to open the keystore in all of the PDBs in this CDB, or CURRENT for the current PDB.

    For example:

    ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY password WITH BACKUP USING 'emp_key_backup';
    
    keystore altered.
    

For better security and to meet compliance regulations, periodically rotate (change) the TDE master encryption key. This process deactivates the previous TDE master encryption key, creates a new TDE master encryption key, and then activates it. You can check the keys that were created recently by querying the CREATION_TIME column in the V$ENCRYPTION_KEYS view. To find the keys that were activated recently, query the ACTIVATION_TIME column in the V$ENCRYPTION_KEYS view.

Exporting and Importing the TDE Master Encryption Key

You can export and import the TDE master encryption key in a variety ways, to suite the needs of other Oracle features, such as a multitenant environment or Oracle Data Guard.

Topics:

About Exporting and Importing the TDE Master Encryption Key

Oracle Database features such as transportable tablespaces and Oracle Data Pump move data that is possibly encrypted between databases.

In addition, CDBs contain PDBs that can be plugged in or unplugged. These are some common scenarios in which you can choose to export and import TDE master encryption keys to move them between source and target keystores. For Data Guard (Logical Standby), you must copy the keystore that is in the primary database to the standby database. Instead of merging the primary database keystore with the standby database, you can export the TDE master encryption key that is in use and then import it to the standby database. Moving transportable tablespaces that are encrypted between databases requires that you export the TDE master encryption key at the source database and then import it into the target database.

About Exporting TDE Master Encryption Keys

You can execute the ADMINISTER KEY MANAGEMENT EXPORT SQL statement to export a TDE master encryption key or a set of keys from a keystore, and subsequently import it into another keystore.

A TDE master encryption key is exported together with its key identifier and key attributes. The exported keys are protected with a password (secret) in the export file.

You can specify the TDE master encryption keys to be exported by using the WITH IDENTIFIER clause of the ADMINSITER KEY MANAGENT EXPORT statement. To export the TDE master encryption keys, you can either specify their key identifiers as a comma-separated list, or you can specify a query that enumerates their key identifiers. Be aware that Oracle Database executes the query determining the key identifiers within the current user's rights and not with definer's rights.

If you omit the WITH IDENTIFER clause, then all of the TDE master encryption keys of the database are exported.

In a consolidated database, you can export the keys from within a PDB for a PDB to be unplugged. In this scenario, you can only use the WITH IDENTIFIER clause in the root and not in a PDB. See "Exporting and Importing TDE Master Encryption Keys for a PDB" for information about exporting keys in a PDB.

To export a set of TDE master encryption keys:

See Also:

"Exporting and Importing TDE Master Encryption Keys for a PDB" for an example of using this statement in a multitenant environment

Exporting a TDE Master Encryption Key

To export a TDE master encryption key, you must open the keystore and then use the ADMINISTER KEY MANAGEMENT statement with the EXPORT [ENCRYPTION] KEYS WITH SECRET clause.

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

    In a multitenant environment, log in to the root. For example:

    sqlplus c##sec_admin as syskm
    Enter password: password
    Connected.
    
  2. If necessary, open the keystore.

    See "Step 3: Open the Software Keystore" for information about opening a keystore.

  3. Run the following SQL statement to export a set of TDE master encryption keys:

    ADMINISTER KEY MANAGEMENT EXPORT [ENCRYPTION] KEYS 
    WITH SECRET "export_secret" 
    TO 'file_path' IDENTIFIED BY software_keystore_password
       [WITH IDENTIFIER IN 'key_id1', 'key_id2', 'key_idn' | (SQL_query)];
    

    In this specification:

    • export_secret is a password that you can specify to encrypt the export the file that contains the exported keys. Enclose this secret in double quotation marks (" "), or you can omit the quotation marks if the secret has no spaces.

    • file_path is the complete path and name of the file to which the keys must be exported. Enclose this path in single quotation marks (' ').

    • software_keystore_password is the password of the keystore containing the keys.

    • key_id1, key_id2, key_idn is a string of one or more TDE master encryption key identifiers for the TDE master encryption key being exported. Separate each key identifier with a comma and enclose each of these key identifiers in single quotation marks (' '). To find a list of TDE master encryption key identifiers, query the KEY_ID column of the V$ENCRYPTION_KEYS dynamic view.

    • SQL_query is a query that fetches a list of the TDE master encryption key identifiers. It should return only one column which contains the TDE master encryption key identifiers. This query is executed with current user rights.

Example: Exporting a TDE Master Encryption Key by Using a Subquery

You can use the ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS SQL statement to export a TDE master encryption key by using a subquery.

Example 4-8 shows how to export TDE master encryption keys whose identifiers are fetched by a query to a file called export.exp. The TDE master encryption keys in the file are encrypted using the secret my_secret. The SELECT statement finds the identifiers for the TDE master encryption keys to be exported.

Be aware that in a multitenant environment, the WITH IDENTIFIER clause is not supported when you try to import or export keys inside a PDB. It is only permitted in the root. See "Exporting and Importing TDE Master Encryption Keys for a PDB" for information about exporting keys in a PDB.

Example 4-7 Exporting a List of TDE Master Encryption Key Identifiers to a File

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "my_secret" 
TO '/TDE/export.exp' IDENTIFIED BY password 
WITH IDENTIFIER IN 'AdoxnJ0uH08cv7xkz83ovwsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA',
'AW5z3CoyKE/yv3cNT5CWCXUAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';

keystore altered.

Example: Exporting a List of TDE Master Encryption Key Identifiers to a File

You can use the ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET SQL statement to export a list of TDE master encryption key identifiers to a file.

Example 4-7 shows how to export TDE master encryption keys by specifying their identifiers as a list, to a file called export.exp. Master encryption keys in the file are encrypted using the secret my_secret. The identifiers of the TDE master encryption key to be exported are provided as a comma-separated list.

Example 4-8 Exporting TDE Master Encryption Key Identifiers by Using a Subquery

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "my_secret" 
TO '/etc/TDE/export.exp' IDENTIFIED BY password 
WITH IDENTIFIER IN (SELECT KEY_ID FROM V$ENCRYPTION_KEYS WHERE ROWNUM <3);

keystore altered.

Example: Exporting All TDE Master Encryption Keys of the Database

You can use the ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS SQL statement to export all TDE master encryption keys of a database.

Example 4-9 shows how to export all of the TDE master encryption keys of the database to a file called export.exp. The TDE master encryption keys in the file are encrypted using the secret my_secret.

Example 4-9 Exporting All of the TDE Master Encryption Keys of the Database

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "my_secret" TO
'/etc/TDE/export.exp' IDENTIFIED BY password;

keystore altered.

About Importing TDE Master Encryption Keys

You can execute the ADMINISTER KEY MANAGEMENT IMPORT statement to import exported TDE master encryption keys from a key export file into a target keystore.

You cannot re-import TDE master encryption keys that have already been imported.

In a consolidated database, you can import the keys from within a PDB for a PDB to be plugged. See "Exporting and Importing TDE Master Encryption Keys for a PDB" for information about exporting keys in a PDB.

Importing a TDE Master Encryption Key

To import a TDE master encryption key, you must open the keystore and then use the ADMINISTER KEY MANAGEMENT statement with the IMPORT [ENCRYPTION] KEYS WITH SECRET clause.

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

    In a multitenant environment, log in to the root. The following command logs user c##sec_admin into the root.

    sqlplus c##sec_admin as syskm
    Enter password: password
    Connected.
    
  2. If necessary, open the keystore.

    See "Step 3: Open the Software Keystore" for information about opening a keystore.

  3. Run the following SQL statement:

    ADMINISTER KEY MANAGEMENT IMPORT [ENCRYPTION] KEYS WITH SECRET "import_secret"  FROM 'file_name' | FROM 'file_name' IDENTIFIED BY software_keystore_password [WITH BACKUP [USING 'backup_identifier']];
    

    In this specification:

    • import_secret is the same password that was used to encrypt the keys during the export operation. Enclose this secret in double quotation marks (" "), or you can omit the quotation marks if the secret has no spaces.

    • file_name is the complete path and name of the file from which the keys need to be imported. Enclose this setting in single quotation marks (' ').

    • software_keystore_password is the password of the software keystore where the keys are being imported.

    • WITH BACKUP must be used in case the target keystore was not backed up before the import operation. backup_identifier is an optional string that you can provide to identify the keystore backup. Enclose this setting in single quotation marks (' ').

Example: Importing a TDE Master Encryption Key

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

Example 4-10 shows how to import the TDE master encryption key identifiers that are stored in the file export.exp and encrypted with the secret my_secret.

Example 4-10 Importing TDE Master Encryption Key Identifiers from an Export File

ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "my_secret" 
FROM '/etc/TDE/export.exp' IDENTIFIED BY password WITH BACKUP;

keystore altered.

How Keystore Merge Differs from TDE Master Encryption Key Export or Import

The keystore merge operation differs from the TDE master encryption key export and import operations.

Even though both the ADMINISTER KEY MANAGEMENT MERGE statement and the ADMINISTER KEY MANAGEMENT EXPORT and IMPORT statements eventually move the TDE master encryption keys from one keystore to the next, there are differences in how these two statements function.

  • The MERGE statement merges two keystores whereas the EXPORT and IMPORT statements export the keys to a file or import the keys from a file. The keystore is different from the export file, and the two cannot be used interchangeably. The export file is not a keystore and cannot be configured to be used with a database as a keystore. Similarly, the IMPORT statement cannot extract the TDE master encryption keys from the keystore.

  • The MERGE statement merges all of the TDE master encryption keys of the specified keystores where as the EXPORT and IMPORT statements can be selective.

  • The EXPORT and IMPORT statements require the user to provide both a location (filepath) and the file name of the export file, whereas the MERGE statement only takes in the location of the keystores.

  • The file name of the keystores is fixed and is determined by the MERGE operation and can be either ewallet.p12 or cwallet.sso. The file names for the export files used in the EXPORT the IMPORT statements are specified by the user.

  • The keystores on Automatic Storage Management (ASM) disk groups or regular file systems can be merged with MERGE statements. The export files used in the EXPORT and the IMPORT statements can only be a regular operating system file and cannot be located on an ASM disk group.

  • The keystores merged using the MERGE statement do not need to be configured or in use with the database. The EXPORT statement can only export the keys from a keystore that is configured and in use with the database and is also open when the export is done. The IMPORT statement can only import the keys into a keystore that is open, configured, and in use with the database.

  • The MERGE statement never modifies the metadata associated with the TDE master encryption keys. The EXPORT and IMPORT operations can modify the metadata of the TDE master encryption keys when required, such as during a PDB plug operation.

Management of TDE Master Encryption Keys Using Oracle Key Vault

You can use Oracle Key Vault to manage and share TDE master encryption keys across an enterprise.

Oracle Key Vault securely stores the keys in a central repository, along with other security objects such as credential files and Java keystores, and enables you to share these objects with other TDE-enabled databases.

See Also:

Storing Secrets Used by Oracle Database

Secrets are data that support internal Oracle Database features and enable external clients such as Oracle GoldenGate to be integrated into the database.

Topics:

About Storing Oracle Database Secrets in a Keystore

You can use keystores to store secrets that support internal Oracle Database features and to enable the integration of external clients such as Oracle GoldenGate.

The secret key must be a string adhering to Oracle identifier rules. You can add, update, or delete a client secret in an existing keystore. The Oracle GoldenGate Extract process must have data encryption keys to decrypt the data that is in data files and in REDO or UNDO logs. Keys are encrypted with shared secrets when you share the keys between an Oracle database and an Oracle GoldenGate client. The software keystore stores the shared secrets.

Depending on your site's requirements, you may require automated open keystore operations even when a hardware security module is configured. For this reason, the hardware security module password can be stored in a software auto-login keystore, which enables the auto-login capability for the hardware security module. The Oracle Database side can also store the credentials for the database to log in to an external storage server in the software keystore.

You can store Oracle Database secrets in both software keystores and hardware keystores:

  • Software keystores: You can store secrets in software password-based, auto-login, and local auto-login software keystores. If you want to store secrets in an auto-login (or auto-login local) keystore, then note the following:

    • If the software auto-login keystore is in the same location as its corresponding password-based software keystore, then the secrets are added automatically.

    • If the software auto-login keystore is in a different location from its corresponding password-based software keystore, then you must create the auto-login keystore again from the password-based keystore, and keep the two keystores in synchronization.

  • Hardware keystores: You can store secrets in standard hardware security modules.

Storage of Oracle Database Secrets in a Software Keystore

To configure an Oracle Database secret in a software keystore, you must use the ADMINISTER KEY MANAGEMENT ADD SECRET|UPDATE SECRET|DELETE CLIENT SQL statement to add secrets, update secrets, and delete secrets from a keystore.

As with all of the ADMINISTER KEY MANAGEMENT statements, you must have the ADMINISTER KEY MANAGEMENT or the SYSKM administrative privilege. In a multitenant environment, run the statement in the root.

  • Adding a secret: Use the following syntax:

    ADMINISTER KEY MANAGEMENT
    ADD SECRET 'secret' FOR CLIENT 'client_identifier' [USING TAG 'tag']
    IDENTIFIED BY keystore_password [WITH BACKUP [USING 'backup_identifier'];
    
  • Updating a secret: Use the following syntax:

    ADMINISTER KEY MANAGEMENT
    UPDATE SECRET 'secret' FOR CLIENT 'client_identifier' [USING TAG 'tag']
    IDENTIFIED BY keystore_password [WITH BACKUP [USING 'backup_identifier'];
    
  • Deleting a secret: Use the following syntax:

    ADMINISTER KEY MANAGEMENT
    DELETE SECRET FOR CLIENT 'client_identifier' 
    IDENTIFIED BY keystore_password [WITH BACKUP [USING 'backup_identifier'];
    

In all of these statements, the specification is as follows:

  • secret is the client secret key to be stored, updated, or deleted. Enclose this setting in single quotation marks (' ') or omit the quotation marks if the secret has no spaces.

  • client_identifier is an alphanumeric string used to identify the secret key. client_identifier does not have a default value. Enclose this setting in single quotation marks (' ').

  • tag is an optional, user-defined description for the secret key to be stored. You can use tag with the ADD and UPDATE operations. Enclose this setting in single quotation marks (' '). This tag appears in the SECRET_TAG column of the V$CLIENT_SECRETS view. See "Creating Custom TDE Master Encryption Key Attributes for Reporting Purposes" for more information about tags.

  • keystore_password is the password for the keystore.

  • WITH BACKUP is required in case the keystore was not backed up before the ADD, UPDATE, or DELETE operation. backup_identifier is an optional user-defined description for the backup. Enclose backup_identifier in single quotation marks (' ').

Example: Adding an HSM Password to a Software Keystore

You can use the ADMINISTER KEY MANAGEMENT ADD SECRET SQL statement to add an HSM password to a software keystore.

Example 4-11 shows how to add a hardware security module (HSM) password as a secret to a software keystore.

Example 4-11 Adding an Oracle Database Secret to a Software Keystore

ADMINISTER KEY MANAGEMENT 
ADD SECRET 'psmith:password' FOR CLIENT 'HSM_PASSWORD' 
USING TAG 'HSM credentials' IDENTIFIED BY password WITH BACKUP;

Example: Changing an HSM Password That Is Stored as a Secret in a Software Keystore

You can use the ADMINISTER KEY MANAGEMENT UPDATE SECRET SQL statement to change an HSM password that is stored as a secret in a software keystore.

Example 4-12 shows how to change an HSM password that is stored as a secret in a software keystore.

Example 4-12 Changing an Oracle Database Secret to a Software Keystore

ADMINISTER KEY MANAGEMENT
UPDATE SECRET admin_password FOR CLIENT 'admin@myhost' 
USING TAG 'new_host_credentials' IDENTIFIED BY software_keytore_password;

Example: Deleting an HSM Password That Is Stored as a Secret in a Software Keystore

You can use the ADMINISTER KEY MANAGEMENT DELETE SECRET SQL statement to delete HSM passwords that are stored as secrets in a software keystore.

Example 4-13 shows how to delete an HSM password that is stored as a secret in the software keystore.

Example 4-13 Deleting an Oracle Database Secret in a Software Keystore

ADMINISTER KEY MANAGEMENT 
DELETE SECRET FOR CLIENT 'HSM_PASSWORD' 
IDENTIFIED BY password WITH BACKUP;

Storage of Oracle Database Secrets in a Hardware Keystore

To store an Oracle Database secret in a hardware keystore, you must use the ADMINISTER KEY MANAGEMENT ADD SECRET|UPDATE SECRET|DELETE CLIENT SQL statement to add secrets, update secrets, and delete secrets from the keystore.

As with all ADMINISTER KEY MANAGEMENT statements, you must have the ADMINISTER KEY MANAGEMENT or the SYSKM administrative privilege. In a multitenant environment, run the statement in the root.

Note:

Before you attempt to add a secret to a hardware security module, ensure that it has PDCS#11 data object support.

  • Adding a secret: Use the following syntax:

    ADMINISTER KEY MANAGEMENT ADD SECRET 'secret' 
    FOR CLIENT 'client_identifier' [USING TAG 'tag'] 
    IDENTIFIED BY "user_id:password";
    
  • Updating a secret: Use the following syntax:

    ADMINISTER KEY MANAGEMENT UPDATE SECRET 'secret' 
    FOR CLIENT 'client_identifier' [USING TAG 'tag'] 
    IDENTIFIED BY "user_id:password";
    
  • Deleting a secret: Use the following syntax:

    ADMINISTER KEY MANAGEMENT DELETE SECRET FOR CLIENT 'client_identifier' 
    IDENTIFIED BY "user_id:password";
    

In all of these statements, the specification as follows:

  • secret is the client secret key to be stored, updated, or deleted. Enclose this setting in double quotation marks (' ') or omit the quotation marks if the secret has no spaces.

  • client_identifier is an alphanumeric string used to identify the secret key. client_identifier does not have a default value. Enclose this setting in single quotation marks (' ').

  • tag is an optional, user-defined description for the secret key to be stored. You can use tag with the ADD and UPDATE operations. Enclose this setting in single quotation marks (' '). This tag appears in the SECRET_TAG column of the V$CLIENT_SECRETS view. See "Creating Custom TDE Master Encryption Key Attributes for Reporting Purposes" for more information about tags.

  • user_id:password is the password for the hardware keystore. Separate the user_id and the password with a colon, and enclose this setting in double quotation marks (" ").

Example: Adding an Oracle Database Secret to a Hardware Keystore

You can use the ADMINISTER KEY MANAGEMENT ADD SECRET SQL statement to add an Oracle Database secret to a hardware keystore.

Example 4-14 shows how to add a password for a user to a hardware keystore.

Example 4-14 Adding an Oracle Database Secret to a Hardware Keystore

ADMINISTER KEY MANAGEMENT ADD SECRET 'password' 
FOR CLIENT 'admin@myhost' USING TAG 'myhost admin credentials' 
IDENTIFIED BY "psmith:password";

Example: Changing an Oracle Database Secret in a Hardware Keystore

You can use the ADMINISTER KEY MANAGEMENT MANAGEMENT UPDATE SECRET SQL statement to change an Oracle Database secret in a hardware keystore.

Example 4-15 shows how to change a password that is stored as a secret in a hardware keystore.

Example 4-15 Changing an Oracle Database Secret in a Hardware Keystore

ADMINISTER KEY MANAGEMENT MANAGEMENT UPDATE SECRET 'password2' 
FOR CLIENT 'admin@myhost' USING TAG 'New host credentials' 
IDENTIFIED BY "psmith:password";

Example: Deleting an Oracle Database Secret in a Hardware Keystore

You can use the ADMINISTER KEY MANAGEMENT DELETE SECRET FOR CLIENT SQL statement to delete an Oracle Database secret that is in a hardware keystore.

Example 4-16 shows how to delete a hardware security module password that is stored as a secret in the hardware keystore.

Example 4-16 Deleting an Oracle Database Secret in a Hardware Keystore

ADMINISTER KEY MANAGEMENT DELETE SECRET FOR CLIENT 'admin@myhost' 
IDENTIFIED BY "psmith:password";

Configuring Auto-Login Hardware Security Modules

A hardware security module can be configured to use the auto-login capability.

Topics:

About Configuring Auto-Login Hardware Security Modules

An auto-login hardware security module stores the hardware security module credentials in an auto-login keystore.

This configuration reduces the security of the system as a whole. However, this configuration does support unmanned or automated operations and is useful in deployments where automatic re-login of the hardware security module is necessary.

To enable the auto-login capability for a hardware security module, you must store the hardware security module credentials in the hardware keystore.

Configuring an Auto-Login Hardware Security Module

To configure an auto-login hardware security module, you must use the ADMINISTER KEY MANAGEMENT statement to perform the configuration, and then modify the sql.net.ora file.

  1. Ensure that you configured the TDE hardware keystore.

    See "Configuring a Hardware Keystore" for more information.

  2. Close the hardware security module if it is open. (You can check the status of whether a keystore is open or closed by querying the STATUS column of the V$ENCRYPTION_WALLET view.)

    For example:

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

    See "Closing a Hardware Keystore" for more information.

  3. If you have not migrated from a software keystore, then create the software keystore with the hardware keystore password in the appropriate location (for example, /etc/ORACLE/WALLETS/orcl).

    For example:

    ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/orcl' 
    IDENTIFIED BY "psmith:password";
    
  4. If you have migrated and are using an auto-login software keystore in a specific location (for example, /etc/ORACLE/WALLETS/HSM), then create the software password keystore with the hardware keystore password from the auto-login keystore.

    For example:

    ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/etc/ORACLE/WALLETS/orcl' IDENTIFIED BY "psmith:password";
    
    ADMINISTER KEY MANAGEMENT 
    MERGE KEYSTORE '/etc/ORACLE/WALLETS/HSM' -- Example keystore path
    INTO EXISTING KEYSTORE '/etc/ORACLE/WALLETS/HSM' -- Example keystore location
    IDENTIFIED BY "psmith:password" WITH BACKUP;
    

    The location of the keystore for the ADMINISTER KEY MANAGEMENT merge statement does not need to be the location of the keystore in use.

  5. Reconfigure the sqlnet.ora file and add the keystore location of the software keystore created in Step 3 or Step 4 to the DIRECTORY setting of the ENCRYPTION_WALLET_LOCATION setting.

    For example:

    ENCRYPTION_WALLET_LOCATION=
     (SOURCE=(METHOD=FILE)(METHOD_DATA=
       (DIRECTORY=/etc/ORACLE/WALLETS/orcl)))
    

    See "About the Keystore Location in the sqlnet.ora File" for more information about how Oracle Database finds the keystore location.

  6. Open the software keystore.

    For example:

    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY software_keystore_password;
    
  7. Add or update the secret in the software keystore.

    The secret is the hardware security module password and the client is the HSM_PASSWORD. HSM_PASSWORD is an Oracle-defined client name that is used to represent the HSM password as a secret in the software keystore.

    For example:

    ADMINISTER KEY MANAGEMENT ADD SECRET 'user_id:password' 
    FOR CLIENT 'HSM_PASSWORD' IDENTIFIED BY "software_keystore_password" 
    WITH BACKUP;
    
    
  8. Close the software keystore.

    For example:

    ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY software_keystore_password;
    
  9. Create (or re-create) the auto-login keystore.

    ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE 
    FROM KEYSTORE '/etc/ORACLE/WALLETS/orcl/hsm' -- Keystore location
    IDENTIFIED BY software_keystore_password; 
    
  10. Update the sqlnet.ora file to use the hardware security module location.

    For example:

    ENCRYPTION_WALLET_LOCATION=
      (SOURCE=(METHOD=HSM)(METHOD_DATA=
        (DIRECTORY=/etc/ORACLE/WALLETS/orcl)))
    

At this stage, the next time that a TDE operation executes, the hardware security module auto-login keystore opens automatically.

Storing Oracle GoldenGate Secrets in a Keystore

You can store Oracle GoldenGate secrets in Transparent Data Encryption keystores.

Topics:

See Also:

Oracle Key Vault Administrator's Guide about how to use TDE with Oracle GoldenGate in an Oracle Key Vault environment

About Storing Oracle GoldenGate Secrets in Keystores

You can use the keystore to store secret keys for tools and external clients such as Oracle GoldenGate.

The secret key must be a string adhering to Oracle identifier rules. You can add, update, or delete a client secret in an existing keystore. This section describes how to capture Transparent Data Encryption encrypted data in the Oracle GoldenGate Extract (Extract) process using classic capture mode.

TDE support when Extract is in classic capture mode requires the exchange of the following keys:

  • TDE support for Oracle GoldenGate in the classic capture mode of the Extract process requires that an Oracle database and the Extract process share the secret to encrypt sensitive information being exchanged. The shared secret is stored securely in the Oracle database and Oracle GoldenGate domains. The shared secret is stored in the software keystore or the HSM as the database secret.

  • The decryption key is a password known as the shared secret that is stored securely in the Oracle database and Oracle GoldenGate domains. Only a party that has possession of the shared secret can decrypt the table and redo log keys.

After you configure the shared secret, Oracle GoldenGate Extract uses the shared secret to decrypt the data. Oracle GoldenGate Extract does not handle the TDE master encryption key itself, nor is it aware of the keystore password. The TDE master encryption key and password remain within the Oracle database configuration.

Oracle GoldenGate Extract only writes the decrypted data to the Oracle GoldenGate trail file, which Oracle GoldenGate persists during transit. You can protect this file using your site's operating system standard security protocols, as well as the Oracle GoldenGate AES encryption options. Oracle GoldenGate does not write the encrypted data to a discard file (specified with the DISCARDFILE parameter). The word ENCRYPTED will be written to any discard file that is in use.

Oracle GoldenGate does require that the keystore be open when processing encrypted data. There is no performance effect of Oracle GoldenGate feature on the TDE operations.

Requirements for Capturing TDE in Oracle GoldenGate Extract Classic Capture Mode

You should ensure that you meet the requirements for Oracle GoldenGate Extract to support Transparent Data Encryption capture.

The requirements are as follows:

  • To maintain high security standards, ensure that the Oracle GoldenGate Extract process runs as part of the Oracle user (the user that runs the Oracle database). That way, the keys are protected in memory by the same privileges as the Oracle user.

  • Run the Oracle GoldenGate Extract process on the same computer as the Oracle database installation.

Configuring Transparent Data Encryption Keystore Support for Oracle GoldenGate

To configure Transparent Data Encryption keystore support for Oracle GoldenGate, you must decide on a shared secret for the keystore, configure the Oracle database, store the shared secret in the keystore, and then set the shared secret in the extract process.

Topics:

Step 1: Decide on a Shared Secret for the Keystore

You should not share this password with any user other than trusted administrators who are responsible for configuring Transparent Data Encryption to work with Oracle GoldenGate Extract.

  • Decide on a shared secret (that is, a password) that meets or exceeds Oracle Database password standards.

See Also:

Oracle Database Security Guide for guidelines on creating secure passwords

Step 2: Configure Oracle Database for TDE Support for Oracle GoldenGate

To configure Oracle Database for TDE support for Oracle GoldGate, you must install the DBMS_INTERNAL_CLKM PL/SQL package and then grant the EXECUTE privilege to the user who will use this package.

  1. Log in to the database instance as user SYS with the SYSDBA administrative privilege.

    For example

    sqlplus sys as sysdba
    Enter password: password
    Connected.
    
  2. In a multitenant environment, connect to the appropriate PDB.

    For example:

    CONNECT SYS@hrpdb AS SYSDBA
    Enter password: password
    

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

  3. Load the Oracle Database-supplied DBMS_INTERNAL_CLKM PL/SQL package.

    For example:

    @?/app/oracle/product/12.1/rdbms/admin/prvtclkm.plb
    

    The prvtclkm.plb file also enables Oracle GoldenGate to extract encrypted data from an Oracle database.

  4. Grant the EXECUTE privilege on the DBMS_INTERNAL_CLKM PL/SQL package to the Oracle GoldenGate Extract database user.

    For example:

    GRANT EXECUTE ON DBMS_INTERNAL_CLKM TO psmith;
    

    This procedure enables the Oracle database and Oracle GoldenGate Extract to exchange information.

  5. Exit SQL*Plus.

Step 3: Store the TDE GoldenGate Shared Secret in the Keystore

To store a TDE GoldenGate shared secret in a keystore, you must use the ADMINISTER KEY MANAGEMENT statement with the ADD SECRET and WITH CLIENT clauses.

  1. Ensure that you have configured the TDE software or hardware keystore.

    See the following sections for more information:

  2. Set the Oracle GoldenGate-Transparent Data Encryption key in the keystore.

    The syntax is as follows:

    ADMINISTER KEY MANAGEMENT ADD|UPDATE|DELETE SECRET 'secret' 
    FOR CLIENT 'secret_identifier' [USING TAG 'tag'] 
    IDENTIFIED BY keystore_password [WITH BACKUP [USING 'backup_identifier']];
    

    In this specification:

    • secret is the client secret key to be stored, updated, or deleted. Enclose this setting in single quotation marks ('  ').

    • secret_identifier is an alphanumeric string used to identify the secret key. secret_identifier does not have a default value. Enclose this setting in single quotation marks (' ').

    • tag is an optional, user-defined description for the secret key to be stored. tag can be used with the ADD and UPDATE operations. Enclose this setting in single quotation marks (' '). This tag appears in the SECRET_TAG column of the V$CLIENT_SECRETS view. See "Creating Custom TDE Master Encryption Key Attributes for Reporting Purposes" for more information about tags.

    • keystore_password is the password for the keystore that is configured.

    • WITH BACKUP is required in case the keystore was not backed up before the ADD, UPDATE or DELETE operation. backup_identifier is an optional user-defined description for the backup. Enclose backup_identifier in single quotation marks (' ').

    The following example adds a secret key to the keystore and creates a backup in the same directory as the keystore:

    ADMINISTER KEY MANAGEMENT ADD SECRET 'some_secret' 
    FOR CLIENT 'ORACLE_GG' USING TAG 'GoldenGate Secret' 
    IDENTIFIED BY password WITH BACKUP USING 'GG backup';
    
  3. Verify the entry that you just created.

    For example:

    SELECT CLIENT, SECRET_TAG FROM V$CLIENT_SECRETS WHERE CLIENT = 'ORACLEGG';
    
    CLIENT   SECRET_TAG
    -------- ------------------------------------------
    ORACLEGG some_secret
    
  4. Switch the log files.

    CONNECT / AS SYSDBA
    
    ALTER SYSTEM SWITCH LOGFILE;
    

    See Oracle Database Administrator's Guide for more information about switching log files.

See Also:

"How Transparent Data Encryption Works with Oracle Real Application Clusters" if you are having problems using this procedure in an Oracle RAC environment

Step 4: Set the TDE Oracle GoldenGate Shared Secret in the Extract Process

To set the TDE Oracle GoldenGate shared secret in the extract process, you must use the GoldenGate Software Command Interface (GGSCI) utility.

  1. Start the GGSCI utility.

    For example:

    ggsci
    
  2. In the GGSCI utility, run the ENCRYPT PASSWORD command to encrypt the shared secret so that it is obfuscated within the Oracle GoldenGate Extract parameter file.

    ENCRYPT PASSWORD shared_secret algorithm ENCRYPTKEY keyname
    

    In this specification:

    • shared_secret is the clear-text shared secret that you created in "Step 1: Decide on a Shared Secret for the Keystore". This setting is case sensitive.

    • algorithm is one of the following values to specify AES encryption:

      • AES128

      • AES192

      • AES256

    • keyname is the logical name of the encryption key in the ENCKEYS lookup file. Oracle GoldenGate uses this name to look up the actual key in the ENCKEYS file.

    For example:

    ENCRYPT PASSWORD password AES256 ENCRYPTKEY mykey1
    
  3. In the Oracle GoldenGate Extract parameter file, set the DBOPTIONS parameter with the DECRYPTPASSWORD option.

    As input, supply the encrypted shared secret and the Oracle GoldenGate-generated or user-defined decryption key.

    DBOPTIONS DECRYPTPASSWORD shared_secret algorithm ENCRYPTKEY keyname
    

    In this specification:

    • shared_secret is the clear-text shared secret that you created in "Step 1: Decide on a Shared Secret for the Keystore". This setting is case sensitive.

    • algorithm is one of the following values to specify AES encryption:

      • AES128

      • AES192

      • AES256

    • keyname is the logical name of the encryption key in the ENCKEYS lookup file.

      For example:

      DBOPTIONS DECRYPTPASSWORD AACAAAAAAAAAAAIALCKDZIRHOJBHOJUH AES256 ENCRYPTKEY mykey1