This blog post is a short summary of my attempt to configure Transparent Data Encryption (TDE) in a single-instance Oracle 19c Container Database (CDB). Reading the documentation I noticed that the way I used to configure TDE changed, there are two new initialisation parameters to be used since SQLNET.ENCRYPTION_WALLET_LOCATION has been deprecated. They are:

wallet_root
tde_configuration

This article assumes you have prior knowledge of TDE, if not I suggest you head over to the Advanced Security Guide for Oracle 19c. I won’t be so bold as to make the claim my configuration is perfect or even 100% correct; it does seem to work though. Please make sure that you get sign-off from your security team once you completed your TDE configuration.

If you are interested in Transparent Data Encryption please ensure your database is appropriately licensed to use the feature!

Reference

I used the following chapters in the Oracle Advanced Security Guide as reference:

Chapter 3: Configuring Transparent Data Encryption
Chapter 5: Managing Keystores and TDE Master Encryption Keys in United Mode

Current Environment

Key properties of the environment I’m creating in the lab:

I’ll use a software keystore
Oracle Restart 19c Enterprise Edition
Separation of duties in place in an Oracle Restart configuration
I use a Container Database with a single PDB (pdb1). The database has just been created by dbca
Configuration of “united mode” where all PDBs share the same keystore with the CDB$ROOT
I would like to use an auto-login keystore, trading security for convenience

I would have liked to use a local auto login keystore, however I’m planning on writing another article discussing Data Guard, and I don’t think a local auto-login would have worked as – per the docs – you can only open it on the node it was created.

In united mode, you create the keystore and TDE master encryption key for CDB and PDBs that reside in the same keystore. This might not be a good choice for you, make sure your implementation is consistent with your security team’s requirements.

Configure location and keystore type

This step is significantly different from previous releases, and the main new thing in 19c from what I understand.

[oracle@dgnode1 ~]$ mkdir /u01/app/oracle/admin/SITEA/wallets || echo "the directory exists, check your configuration"

Connect to CDB$ROOT and set wallet_location and tde_configuration

[oracle@dgnode1 ~]$ sq

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 16 09:14:05 2021
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> show parameter wallet_root

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
wallet_root                          string

SQL> alter system set wallet_root='/u01/app/oracle/admin/SITEA/wallets' scope=spfile;

System altered.

Unfortunately it is necessary to bounce the instance as wallet_root is a static parameter. You have to set wallet_root before you can change tde_configuration. So it’s time to bounce the database now. After it’s up, the finishing touches can be applied.

SQL> alter system set tde_configuration="keystore_configuration=file" scope=both;

System altered.

SQL> select name, value from v$parameter
  2  where name in ('wallet_root', 'tde_configuration');

NAME                 VALUE
-------------------- ------------------------------------------------------
wallet_root          /u01/app/oracle/admin/SITEA/wallets
tde_configuration    keystore_configuration=file

To avoid bouncing the instance you could have set the parameters via dbca when creating the database.

Create the software keystore

I was surprised when I noticed that Oracle appended “/tde” to the location specified by wallet_root. According to the documentation this is correct.

SQL> select wrl_parameter, status, wallet_type, keystore_mode, con_id from v$encryption_wallet
  2  /

WRL_PARAMETER                                      STATUS                         WALLET_TYPE          KEYSTORE     CON_ID
-------------------------------------------------- ------------------------------ -------------------- -------- ----------
/u01/app/oracle/admin/SITEA/wallets/tde/           NOT_AVAILABLE                  UNKNOWN              NONE              1
                                                   NOT_AVAILABLE                  UNKNOWN              UNITED            2
                                                   NOT_AVAILABLE                  UNKNOWN              UNITED            3

The value of NONE in KEYSTORE_MODE for CON_ID 1 is expected, as is the absence of wallet locations for the PDBs. CDB1 (the root) owns the keystore for all PDBs.

Let’s create a local auto-login software keystore. This might not be the right keystore type for you, make sure to read the documentation about keystore types and their pros and cons.

Create the necessary password-protected keystore

Before creating the auto login keystore, a password protected keystore has to be created. This is what I did:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show parameter wallet_root

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------------
wallet_root                          string      /u01/app/oracle/admin/SITEA/wallets

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO

SQL> set verify off

SQL> administer key management create keystore identified by &password;

keystore altered.

SQL> select con_id, wrl_parameter, status, wallet_type, keystore_mode from v$encryption_wallet;

    CON_ID WRL_PARAMETER                                      STATUS                         WALLET_TYPE          KEYSTORE
---------- -------------------------------------------------- ------------------------------ -------------------- --------
         1 /u01/app/oracle/admin/SITEA/wallets/tde/           CLOSED                         UNKNOWN              NONE
         2                                                    CLOSED                         UNKNOWN              UNITED
         3                                                    CLOSED                         UNKNOWN              UNITED

The keystore is created, but it’s closed. Here I had to make a choice whether to keep the keystore password protected or transforming it to an auto login keystore. The higher degree of security in the first case is offset by the necessity to enter the keystore password when starting the database. In my lab environment I wanted to be able to start the database without manual intervention, so an auto login password it is. As you read earlier I would have preferred a local auto login keystore but since I will create a physical standby for the database. The Advanced Security Guide doesn’t provide specifics about the keystore type to be used in Data Guard.

Deciding between an auto login keystore and a password protected keystore isn’t a decision for the DBA to make. This is one for the security team!

Straight forward, but the keystore location cannot be ommitted this time. The docs instruct us to use wrl_location as shown in v$encryption_wallet. And yes, please use $WALLET_ROOT/tde for this.

SQL> administer key management create auto_login keystore
  2  from keystore '/u01/app/oracle/admin/SITEA/wallets/tde'
  3  identified by &password;

keystore altered.

SQL> select con_id, wrl_parameter, status, wallet_type, keystore_mode from v$encryption_wallet;

    CON_ID WRL_PARAMETER                                      STATUS                         WALLET_TYPE          KEYSTORE
---------- -------------------------------------------------- ------------------------------ -------------------- --------
         1 /u01/app/oracle/admin/SITEA/wallets/tde/           OPEN_NO_MASTER_KEY             AUTOLOGIN            NONE
         2                                                    OPEN_NO_MASTER_KEY             AUTOLOGIN            UNITED
         3                                                    OPEN_NO_MASTER_KEY             AUTOLOGIN            UNITED

This command created the following files:

SQL> !ls -laR /u01/app/oracle/admin/SITEA/wallets
/u01/app/oracle/admin/SITEA/wallets:
total 0
drwxr-xr-x. 3 oracle oinstall 17 Feb 17 13:16 .
drwxr-x---. 7 oracle oinstall 79 Feb 17 13:12 ..
drwxr-x---. 2 oracle asmadmin 44 Feb 17 13:16 tde

/u01/app/oracle/admin/SITEA/wallets/tde:
total 8
drwxr-x---. 2 oracle asmadmin   44 Feb 17 13:16 .
drwxr-xr-x. 3 oracle oinstall   17 Feb 17 13:16 ..
-rw-------. 1 oracle asmadmin 2600 Feb 17 13:16 cwallet.sso
-rw-------. 1 oracle asmadmin 2555 Feb 17 13:16 ewallet.p12

Since this is an auto-login password store it is not necessary to open it. A master key is missing though, it needs to be created.

Create the TDE master encryption key

Important: before running this command ensure all the PDBs in your CDB are open read-write!

SQL> administer key management set key force keystore identified by &password with backup container=all;

keystore altered.

It was necessary to use the “force keystore” option due to this error:

SQL> administer key management set key identified by &password with backup container = all;
administer key management set key identified by ... with backup
*
ERROR at line 1:
ORA-28417: password-based keystore is not open

When clearly it was open! The keystore status column read OPEN_NO_MASTER_KEY, see above.

It has also been necessary to specify container = all or otherwise the PDB wouldn’t have been assigned the key. This was the output of my first attempt, before I specified container=all:

SQL> administer key management set key force keystore identified by &password with backup;

keystore altered.

SQL> select con_id, wrl_parameter, status, wallet_type, keystore_mode from v$encryption_wallet;

    CON_ID WRL_PARAMETER                                 STATUS                         WALLET_TYPE          KEYSTORE
---------- --------------------------------------------- ------------------------------ -------------------- --------
         1 /u01/app/oracle/admin/SITEA/wallets/tde/      OPEN                           LOCAL_AUTOLOGIN      NONE
         2                                               OPEN                           LOCAL_AUTOLOGIN      UNITED
         3                                               OPEN_NO_MASTER_KEY             LOCAL_AUTOLOGIN      UNITED

Note how CON_ID 3 (my PDB) doesn’t have a master key. According to the documentation it shouldn’t be necessary to specify container = all when using unified mode, but I guess it is.

Validating the configuration

The documentation provides a query to check if the master key is enabled:

SQL> select con_id, masterkey_activated from v$database_key_info;

    CON_ID MAS
---------- ---
         1 YES
         2 NO
         3 YES

So both my CDB$ROOT and PDB1 have the master key activated. I found the query against v$encryption_wallet useful, too.

SQL> select con_id, wrl_parameter, status, wallet_type, keystore_mode from v$encryption_wallet;

    CON_ID WRL_PARAMETER                                      STATUS                         WALLET_TYPE          KEYSTORE
---------- -------------------------------------------------- ------------------------------ -------------------- --------
         1 /u01/app/oracle/admin/SITEA/wallets/tde/           OPEN                           AUTOLOGIN            NONE
         2                                                    OPEN                           AUTOLOGIN            UNITED
         3                                                    OPEN                           AUTOLOGIN            UNITED

The autologin wallet is now open and ready for use.

Encrypt data

Connect to the PDB and create an encrypted tablespace using the default encryption algorithm. Be advised you can specify which encryption algorithm to use in the create tablespace command. I’m just mucking around with the feature and thus am perfectly happy with the default.

SQL> alter session set container = pdb1;

Session altered.

SQL> create tablespace soe_tbs datafile size 2g autoextend on next 1g maxsize 5g 
  2  encryption encrypt;

Tablespace created.

SQL> select ets.ts#, ets.encryptionalg, ets.key_version, ets.status, ts.name
  2  from v$encrypted_tablespaces ets join v$tablespace ts on (ets.ts# = ts.ts#);

       TS# ENCRYPT KEY_VERSION STATUS     NAME
---------- ------- ----------- ---------- ------------------------------
         6 AES128            0 NORMAL     SOE_TBS

Now let’s load some data… As always, I use Swingbench for this purpose. I’m pointing oewizard to the existing (TDE) tablespace; that’s an important aspect!

martin@client:~/java/swingbench/bin$ 
>./oewizard -cl -create ... -scale 1 -tc 4 -ts SOE_TBS -u soe

While the data creation job is running you can see the number of blocks encrypted increase on my encrypted tablespace:

SQL> select ets.ts#, ets.encryptionalg, ets.key_version, ets.status, 
  2     ets.blocks_encrypted, ets.blocks_decrypted, ts.name
  3    from v$encrypted_tablespaces ets 
  4     join v$tablespace ts on (ets.ts# = ts.ts#);

       TS# ENCRYPT KEY_VERSION STATUS     BLOCKS_ENCRYPTED BLOCKS_DECRYPTED NAME
---------- ------- ----------- ---------- ---------------- ---------------- ------------------------------
         6 AES128            0 NORMAL                28587                0 SOE_TBS

Similarly, you can see the number of blocks decrypted increase when you query data from the SOE schema.

13:26:06 SQL> select ets.ts#, ets.encryptionalg, ets.key_version, 
  2     ets.status, ets.blocks_encrypted, ets.blocks_decrypted, ts.name
  3      from v$encrypted_tablespaces ets 
  4      join v$tablespace ts on (ets.ts# = ts.ts#);

       TS# ENCRYPT KEY_VERSION STATUS     BLOCKS_ENCRYPTED BLOCKS_DECRYPTED NAME
---------- ------- ----------- ---------- ---------------- ---------------- ------------------------------
         6 AES128            0 NORMAL                69901                0 SOE_TBS

13:26:07 SQL> /

       TS# ENCRYPT KEY_VERSION STATUS     BLOCKS_ENCRYPTED BLOCKS_DECRYPTED NAME
---------- ------- ----------- ---------- ---------------- ---------------- ------------------------------
         6 AES128            0 NORMAL               155725           191459 SOE_TBS

13:26:57 SQL>

You can also notice blocks decrypted. As I haven’t run charbench yet I assume this is caused by the table scans when building the indexes.

Summary

This concludes the article on implementing TDE for an Oracle single-instance database. So far a lot of complexities have been omitted: no RAC, no Data Guard, no additional features requiring wallets. I am planning on extending this article to Data Guard setups, and eventually discuss a RAC setup.

Martins Blog

Trying to explain complex things in simple terms

New initialisation parameters for Transparent Data Encryption beginning in 19c

Reference

Current Environment

Configure location and keystore type

Create the software keystore

Create the necessary password-protected keystore

Create the TDE master encryption key

Validating the configuration

Encrypt data

Summary

Reference

Current Environment

Configure location and keystore type

Create the software keystore

Create the necessary password-protected keystore

Convert to auto-login keystore

Create the TDE master encryption key

Validating the configuration

Encrypt data

Summary