This blog post is a short summary of my attempt to retrofit Transparent Data Encryption (TDE) into an existing Data Guard environment featuring a single-instance Oracle 19c Container Database (CDB) on a file system. Neither Oracle Restart nor Real Application Clusters are in use to keep the size of the article a little more manageable.
Setting the stage
Reading the documentation I noticed that the way I used to configure TDE changed, there are two new initialisation parameters to be used; SQLNET.ENCRYPTION_WALLET_LOCATION has been deprecated and should no longer be used. The 2 parameters in question are:
wallet_roottde_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 PoC.
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 8: Using Transparent Data Encryption with Other Oracle Features
- Chapter 5: Managing Keystores and TDE Master Encryption Keys in United Mode
- Chapter 3: Configuring Transparent Data Encryption
Current Environment
Key properties of the environment I’m creating in the lab:
- I’ll use a software keystore
- Oracle Database 19c Enterprise Edition, with the 19.20.0 RU applied
- I use a Container Database with 1 PDB (
pdb1). The single-instance database has just been created bydbcawithout TDE. Data Guard has already been configured - The database’s global database name is ORCL with db_unique_names SITEA and SITEB; SITEA is currently running in primary role. Each database’s ORACLE_SID matches its unique name
- 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
- All Pluggable Databases are open read-write all the time to reduce complexity
Note that 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. Here I create a new wallet_root directory on the file system for use with the database.
[oracle@dgnode1 ~]$ mkdir -p /u01/app/oracle/admin/${ORACLE_SID}/wallets ||
> echo "the directory exists, check your configuration"
Repeat this step for the standby database(s), adapt the path as needed.
Connect to CDB$ROOT and set wallet_location and tde_configuration in the server parameter file. Setting wallet_root is preferred over setting ENCRYPTION_WALLET_LOCATION in sqlnet.ora. The documentation reads:
Starting with Oracle Database release 19c, the
ENCRYPTION_WALLET_LOCATIONparameter is deprecated. Instead, use theWALLET_ROOTstatic initialization parameter and theTDE_CONFIGURATIONdynamic initialization parameter to configure the wallet location.WALLET_ROOTandTDE_CONFIGURATIONcan be used for a regular file system, multiple database access, and ASM
Additionally you should consider setting tablespace_encryption to auto_enable. That way you don’t miss encrypting new tablespaces.
[oracle@dgnode1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 31 14:05:17 2023
Version 19.20.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
SQL> alter system set tablespace_encryption=auto_enable scope=spfile;
System altered.
SQL> alter system set wallet_root='/u01/app/oracle/admin/$ORACLE_SID/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. Apply the finishing touches after you restarted the database during a convenient change window.
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
Ensure the initialisation parameters are set on every standby in your configuration in addition to the primary.
Create the software keystore
Once the initialisation parameters are set connect to the primary database and create a local auto-login keystore. Make sure you are connected to CDB$ROOT. I prefer to open all PDBs read-write, too.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> select database_role, name, db_unique_name from v$database;
DATABASE_ROLE NAME DB_UNIQUE_NAME
---------------- ------------------------------ ------------------------------
PRIMARY ORCL SITEA
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
Create the keystore next, at first it’s protected by a password. In the next step you’ll create a local auto-login keystore.
administer key management create keystore identified by &password;
The keystore is now present, as seen by v$encryption_wallet:
SQL> select wrl_parameter, status, wallet_type, keystore_mode, con_id from v$encryption_wallet;
WRL_PARAMETER STATUS WALLET_TYPE KEYSTORE CON_ID
-------------------------------------------- ------------------------------ -------------------- -------- ----------
/u01/app/oracle/admin/SITEA/wallets/tde/ CLOSED UNKNOWN NONE 1
CLOSED UNKNOWN UNITED 2
CLOSED 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. CON_ID 1 (aka CDB$ROOT) owns the keystore for all PDBs (aka united mode).
Note how 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 a local 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 (well you could store the password in an additional secure external password store, but that’s not in scope of this article). In my lab environment I wanted to be able to start the database without manual intervention, so a (local) auto login keystore it is.
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!
Create a local auto-login keystore on the primary
In this step the local auto-login keystore is created.
SQL> administer key management
create local auto_login keystore
from keystore identified by &password;
SQL> select wrl_parameter, status, wallet_type, keystore_mode, con_id from v$encryption_wallet;
WRL_PARAMETER STATUS WALLET_TYPE KEYSTORE CON_ID
-------------------------------------------- ------------------------------ -------------------- -------- ----------
/u01/app/oracle/admin/SITEA/wallets/tde/ OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN NONE 1
OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN UNITED 2
OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN UNITED 3
Since you created a local auto-login password store it is not necessary to open it. A master key is missing though.
Interlude: configuring an External Store for the keystore password
At this point you can decide to move the keystore password to a secure external password store (SEPS), rather than referencing it verbatim in the identified by password clause. There are many advantages of doing so, the least of which is not having to store the password in any scripts – a huge security gain.
I decided to take this approach, you are going to see the identified by external store clause from now on, instead of a password. Before you can crate the SEPS you must provide a location for it. By default it goes to wallet_root/tde_seps. Unlike wallet_root/tde Oracle won’t create this directory for you – it must be created first before you can create the actual SEPS.
SQL> !mkdir /u01/app/oracle/admin/${ORACLE_SID}/wallets/tde_seps
SQL> administer key management add secret '&password'
for client 'TDE_WALLET'
to local auto_login keystore '/u01/app/oracle/admin/${ORACLE_SID}/wallets/tde_seps'
Create the TDE master encryption key in CDB$ROOT
Make sure to connect to CDB$ROOT as SYSDBA or SYSKM to set the first key on the primary.
SQL> administer key management set key force keystore identified by external store with backup container=current;
You’ll notice that the key was created in CDB$ROOT and the seed-PDB:
SQL> select wrl_parameter, status, wallet_type, keystore_mode, con_id from v$encryption_wallet;
WRL_PARAMETER STATUS WALLET_TYPE KEYSTORE CON_ID
-------------------------------------------- ------------------------------ -------------------- -------- ----------
/u01/app/oracle/admin/SITEA/wallets/tde/ OPEN LOCAL_AUTOLOGIN NONE 1
OPEN LOCAL_AUTOLOGIN UNITED 2
OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN UNITED 3
Next it’s time to set keys in all PDBs. There is only one so that’s not too hard. The following command does that for me:
alter session set container = pdb1; show con_name administer key management set key using tag 'PDB1 2023-10-31 14:38:11Z' force keystore identified by external store with backup;
With all the PDBs dealt with it time to hop back to CDB$ROOT on the primary. Everything looks all right:
SQ> alter session set container = CDB$ROOT;
SQL> select wrl_parameter, status, wallet_type, keystore_mode, con_id from v$encryption_wallet;
WRL_PARAMETER STATUS WALLET_TYPE KEYSTORE CON_ID
-------------------------------------------- ------------------------------ -------------------- -------- ----------
/u01/app/oracle/admin/SITEA/wallets/tde/ OPEN LOCAL_AUTOLOGIN NONE 1
OPEN LOCAL_AUTOLOGIN UNITED 2
OPEN LOCAL_AUTOLOGIN UNITED 3
This concludes the setup on the primary database. Next the keystore (and secure external password store containing the keystore password if you have one) must be made available on the standby.
Configuring the standby database
The easiest way to transfer the keystore to the standby is to use scp or rsync. The local auto-login wallet is tied to the host and can’t be used on the standby. There is no point copying it over, the following rsync command makes sure it’s not transferred:
rsync -rvpt --exclude '*.sso' \ /u01/app/oracle/admin/SITEA/wallets/tde/ \ dgnode2:/u01/app/oracle/admin/SITEB/wallets/tde/
Remember that rsync is picky about trailing slashes :) A new local auto-login keystore must be created. Making sure I’m connected to the right system:
SQL> select database_role, name, db_unique_name, open_mode from v$database;
DATABASE_ROLE NAME DB_UNIQUE_NAME OPEN_MODE
---------------- --------- ------------------------------ --------------------
PHYSICAL STANDBY ORCL SITEB MOUNTED
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 PDB1 MOUNTED
SQL> select wrl_parameter, status, wallet_type, keystore_mode, con_id from v$encryption_wallet;
WRL_PARAMETER STATUS WALLET_TYPE KEYSTORE CON_ID
-------------------------------------------------- ------------------------------ -------------------- -------- ----------
/u01/app/oracle/admin/SITEB/wallets/tde/ CLOSED UNKNOWN NONE 1
Let’s create the local auto-login keystore:
administer key management create local auto_login keystore from keystore identified by &password;
And here it is:
SQL> select wrl_parameter, status, wallet_type, keystore_mode, con_id from v$encryption_wallet; WRL_PARAMETER STATUS WALLET_TYPE KEYSTORE CON_ID -------------------------------------------------- ------------------------------ -------------------- -------- ---------- /u01/app/oracle/admin/SITEB/wallets/tde/ OPEN LOCAL_AUTOLOGIN NONE 1
The standby should now be ready. Jump back to the primary to start encrypting data.
Online encryption of tablespaces on the primary
Consult your security team as to which tablespaces must be encrypted. For CDB$ROOT this typically involves the USERS tablespace if it is used. SYSTEM and SYSAUX can optionally be encrypted, too. Here’s an example how to encrypt the SYSTEM tablespace online using the AES256 algorithm. Connect to the primary using an account granted the SYSKM privilege and run the following command for example.
SQL> alter tablespace SYSTEM encryption online using 'AES256' encrypt;
Tablespace altered.
If you are transporting tablespaces you should spend some more time evaluating the algorithm; Oracle 19c defaults to AES128. I like AES256.
You can also encrypt the credentials of named user database links in the root container if needed:
alter database dictionary encrypt credentials container = current;
Repeat the online encryption operations – keeping an eye on space – for each PDB. You’d typically want to encrypt SYSTEM, SYSAUX, UNDO and any user-data tablespace. Maybe even TEMP. New tablespaces should automatically be encrypted, too, thanks to the correct setting of tablespace_encryption=auto_enable. You can influence the algorithm by setting _tablespace_encryption_default_algorithm. This parameter became an official init.ora parameter with 21c and later.
Ensure you TEST this procedure first before running these commands on systems you are about. Mainly for ETA but also space consumption and performance impact.
Verification of the encryption work
Once all the tablespaces in your PDBs and CDB$ROOT are encrypted it’s time for a quick check:
SELECT
c.name AS pdb_name,
t.name AS tbs_name,
e.encryptionalg AS alg,
e.status
FROM
v$tablespace t,
v$encrypted_tablespaces e,
v$containers c
WHERE
e.ts# = t.ts#
AND e.con_id = t.con_id
AND e.con_id = c.con_id
ORDER BY
e.con_id,
t.name
/
PDB_NAME TBS_NAME ALG STATUS
-------------------- ------------------------------ ------- ----------
CDB$ROOT SYSAUX AES256 NORMAL
CDB$ROOT SYSTEM AES256 NORMAL
CDB$ROOT UNDOTBS1 AES256 NORMAL
CDB$ROOT USERS AES256 NORMAL
PDB1 SYSAUX AES256 NORMAL
PDB1 SYSTEM AES256 NORMAL
PDB1 UNDOTBS1 AES256 NORMAL
PDB1 USERS AES256 NORMAL
8 rows selected.
Thanks to redo transport the standby database’s tablespaces are equally encrypted. Looking good!
Summary
There were a few steps required for me to enable TDE in Data Guard, but they didn’t impact the availability of my primary database. I like online encryption of systems while users are still connected and doing work.
Martins Blog 