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 bydbca
- 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!
Convert to auto-login keystore
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.