AWS Database Blog

Secure data at rest on Amazon RDS Custom for Oracle with TDE – Part 2: Multi-tenant environments

In Part 1 of this series, we discussed implementing Oracle Transparent Database Encryption (TDE) in Amazon Relational Database Service (Amazon RDS) Custom for Oracle with the local auto-login option in a database with the multi-tenant option disabled. In this post, we focus on outlining the implementation steps of TDE in an Oracle database with the multi-tenant option enabled.

TDE is used to secure the database at rest and also encrypt the database backups. Due to compliance requirements and increasing security threats, security of the database layer has become more important than ever before. Encryption of data at rest and encrypting the backups of databases is an effective way of enforcing strong security controls. Amazon RDS Custom for Oracle natively encrypts the underlying storage as well as the Amazon RDS-managed backups. In some scenarios, you may wish to use Oracle Database managed encryption of the Oracle datafiles using Oracle Database TDE. Oracle TDE encrypts complete datafiles if tied to a tablespace or encrypts the column content if tied to a table. You can also use TDE to make sure that Oracle datafiles contain encrypted data when copied or backed up using user-managed means.

Solution overview

The multi-tenant architecture enables the Oracle database to function as a container database (CDB) with zero, one, or more pluggable databases (PDBs). The following diagram illustrates this configuration.

A container is a collection of schemas, objects, and related structures, and a CDB includes zero, one, or many customer-created PDBs and application containers. A PDB is a portable collection of schemas, schema objects, and non-schema objects that appears to an Oracle Net client as a separate database. The main purpose of PDBs is to consolidate PDBs into a single CDB to achieve economies of scale, while maintaining greater isolation between PDBs for improved security and governance. Oracle databases before Oracle Database version 12c are defined non-CDBs, and starting from version 12c, the non-CDB database mode is deprecated. With the general availability of the Oracle Database version 21c, the multi-tenant architecture is the only supported database mode.

Configure TDE in a multi-tenant architecture

When securing data at rest with Oracle TDE in a multi-tenant environment, there are two approaches for keystore management:

  • United mode – This is the default mode. It enables the creation of a common keystore for the CDB and PDBs. In united mode, the keys for PDBs can be created from the CDB root or from the PDB itself. This design allows you to have a single keystore to manage the entire database. The primary encryption keys for the CDB and the PDBs reside in the shared keystore.
  • Isolated Mode – In this mode, the pluggable database has its own keystore and its primary encryption keys can be managed from the PDB only. It’s possible to enable isolated mode in a specific PDB even if the CDB is configured for united mode. Similar to united mode, you must first configure a PDB to use isolated mode by setting the WALLET_ROOT and TDE_CONFIGURATION parameters.

Depending on your organizations requirement’s, you can use a mixture of united mode and isolated mode for the TDE configuration in a multi-tenant environment. The following table summarizes some differences between united and isolated mode PDB configuration.

Factor United Mode Isolated Mode
Ease of keystore management or administration Simple as a single keystore. Multiple keystores in CDB and PDB introduces management overhead.
Portability of PDB More complex compared to isolated mode because with united mode, keys are stored in a central location. Ease of portability because key management is self-managed within the PDB and the keystore is not shared.
Flexibility of PDB configuration PDBs in united mode have the same key store type. Isolated mode PDBs can have different types of keystore (for example, software keystore or external HSM).
Rekey Operations of PDB databases Slower because there are many databases together in united mode. Improves the performance of rekey operations in the PDB as compared to the rekey performance in united mode when there are a large number of encrypted PDBs.

Implement TDE in a pluggable database in united mode

The high-level steps to implement TDE in united mode are:

  1. In the CDB root, configure the database to use united mode by setting the WALLET_ROOT and TDE_CONFIGURATION parameters:
    1. WALLET_ROOT sets the location for the wallet directory.
    2. TDE_CONFIGURATION sets the type of keystore to use.
  2. Pause the automation framework and restart the database so that these settings take effect.
  3. In the CDB root, create and open the keystore, then create the TDE primary encryption key.
  4. In each PDB, perform TDE primary encryption key tasks such as opening the keystore locally in the PDB and creating the TDE primary encryption key for the PDB. Remember that the keystore is managed by the CDB root, but must contain a TDE primary encryption key that is specific to the PDB for the PDB to be able to use TDE.
  5. Convert the password-protected software keystore to auto-login.
  6. Restart the database for the configuration to take effect.
  7. Perform a backup of the keystore.

Let’s look at the implementation steps in more detail with code snippets:

  1. Configure the CDB root container to use united mode by setting the TDE parameters: 
    -- Connected as 'SYS as sysdba':
$ sqlplus '/as sysdba'

-- Note: you can retrieve the SYS password from the AWS Secrets Manager, please refer to this guide
-- We recommend taking manual snapshot backup of database  before the change

SQL> show pdbs

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

SQL> show con_name

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

-- Ensure that you create the directory for storing the keys
-- Note: 'RDSCDB_A' is the db_unique_name of the database (show parameter db_unique_name)

SQL> show con_name

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

--Create the directory at the OS Level with below command
mkdir -p /rdsdbdata/db/cdb/RDSCDB_A/wallet

SQL> alter system set wallet_root='/rdsdbdata/db/cdb/RDSCDB_A/wallet' scope=spfile;

System altered.
    SQL
  2. Pause the automation framework and restart the database to configure the wallet keystore: 
    -- Shutdown and startup the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 2.4964E+10 bytes
Fixed Size 13873328 bytes
Variable Size 1.2080E+10 bytes
Database Buffers 1.2818E+10 bytes
Redo Buffers 53231616 bytes
Database mounted.
Database opened.

SQL> show pdbs

CON_ID     CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           MOUNTED    NO

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs

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


SQL> show con_name

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

-- check the configuration of the wallet_root parameter

SQL> show parameter wallet_root

NAME        TYPE        VALUE
----------- ----------- -------------------------------------
wallet_root string      /rdsdbdata/db/cdb/RDSCDB_A/wallet/tde/

SQL> show parameter tde_config

NAME              TYPE        VALUE
----------------- ----------- ------------------------------
tde_configuration string

-- set the tde_configuration parameter to configure a wallet keystore

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

System altered.

SQL> show parameter tde_configuration

NAME                  TYPE       VALUE
--------------------- ---------- ------------------------------
tde_configuration     string     keystore_configuration=file
    SQL
  3. Resume the automation and create a password-protected software keystore: 
    -- Note: Use the below command to create password protected Keystore. Once Keystore is created you will notice oracle created ewallet.p12 inside wallet_root location.

SQL> administer key management create keystore identified by '****';

keystore altered.

-- check from OS:
$ ls -lrt /rdsdbdata/db/cdb/RDSCDB_A/wallet/tde/
-rw------- 1 rdsdb rdsdb 2555 Feb 3 14:31 ewallet.p12

SQL> select con_id, wrl_type, keystore_mode, WRL_PARAMETER, status from v$encryption_Wallet;

CON_ID WRL_TYPE KEYSTORE_MODE   WRL_PARAMETER                       STATUS
------ -------- --------------- ----------------------------------- -------
     1 FILE     NONE            /rdsdbdata/db/RDSCDB_A/wallet/tde/  CLOSED
     2 FILE     UNITED                                              CLOSED
     3 FILE     UNITED                                              CLOSED

-- Note: Running the SQL command ADMINISTER KEY MANAGEMENT in united mode from the CDB root with the CONTAINER clause set to ALL does not affect any PDB that is in isolated mode, the statement applies only to the CDB root and its associated united mode PDBs.

-- Open the Keystore: After Keystore is created it will be in closed status:

SQL> administer key management set keystore open identified by '****' container=all;

keystore altered.

SQL> select con_id, wrl_type, keystore_mode, status from v$encryption_Wallet;

CON_ID WRL_TYPE  KEYSTORE    STATUS
------ --------- ----------- --------------------
     1 FILE      NONE        OPEN_NO_MASTER_KEY
     2 FILE      UNITED      OPEN_NO_MASTER_KEY
     3 FILE      UNITED      OPEN_NO_MASTER_KEY

-- Note: That status shows OPEN_NO_MASTER_KEY as we did not create the master key yet.

-- Set the TDE Master Encryption Key for CDB$ROOT & PDBs:

-- Use the below command to create master key use options like tag and with backup to give the tag to your master key and to take immediate backup of your Keystore. 

SQL> show con_name

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

SQL> administer key management set key using tag 'master key' identified by '*****' with backup using 'masterbackup';

keystore altered.

-- Once the master key is created status will change from OPEN_NO_MASTER_KEY to OPEN:

SQL> select con_id,wrl_type,keystore_mode,status from v$encryption_Wallet;

CON_ID WRL_TYPE    KEYSTORE_MODE    STATUS
------ ---------   ---------------- -------------------
1       FILE       NONE             OPEN
2       FILE       UNITED           OPEN
3       FILE       UNITED           OPEN_NO_MASTER_KEY

-- Note: the master key is separate for CDB$ROOT and each PDBs. 

SQL> col TAG for a50
SQL> col CREATION_TIME for a50
SQL> col ACTIVATION_TIME for a50
SQL> select key_id, creation_time, activation_time, tag from v$encryption_keys;

KEY_ID                                                CREATION_TIME                            ACTIVATION_TIME                          TAG
----------------------------------------------------- ---------------------------------------- ---------------------------------------- ------------------
ASTy157YdE/Dv+dGU/ZPVBEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA  03-FEB-23 03.00.37.102322 PM +00:00      03-FEB-23 03.00.37.102324 PM +00:00      master key


-- check from OS:

$ ls -lrt /rdsdbdata/db/RDSCDB_A/wallet/tde/
-rw------- 1 rdsdb rdsdb 2555 Feb 3 15:00 ewallet_2023020315003700_masterbackup.p12
-rw------- 1 rdsdb rdsdb 4171 Feb 3 15:00 ewallet.p12

SQL> show pdbs

CON_ID     CON_NAME         OPEN MODE  RESTRICTED
---------- ---------------- ---------- ----------
2          PDB$SEED         READ ONLY  NO
3          ORCL             READ WRITE NO
    SQL
  4. Complete the TDE configuration for the pluggable database: 
    -- move to the PDB setting the container parameter at session level:

SQL> alter session set container=ORCL;

Session altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER  STATUS              WALLET_TYPE  WALLET_ORDER  KEYSTORE_MODE  FULLY_BACKED_UP  CON_ID
-------- -------------- ------------------- ------------ ------------- -------------- ---------------- -------
FILE                    OPEN_NO_MASTER_KEY  PASSWORD     SINGLE        UNITED         UNDEFINED        3

SQL> select * from v$encryption_keys;

no rows selected

SQL> administer key management set key using tag 'PDB KEY' identified by '***' with backup using 'backupmasterafterpdb';

keystore altered.

SQL> select key_id, creation_time, activation_time, tag from v$encryption_keys;

KEY_ID                                                CREATION_TIME                        ACTIVATION_TIME                      TAG
----------------------------------------------------- ------------------------------------ ------------------------------------ ---------
AaiiAvCQJE/Dv7hLOSOcZ28AAAAAAAAAAAAAAAAAAAAAAAAAAAAA  03-FEB-23 03.12.22.638438 PM +00:00  03-FEB-23 03.12.22.638440 PM +00:00  PDB KEY

-- check from OS:

$ ls -lrt /rdsdbdata/db/RDSCDB_A/wallet/tde/
-rw------- 1 rdsdb rdsdb 2555 Feb 3 15:00 ewallet_2023020315003700_masterbackup.p12
-rw------- 1 rdsdb rdsdb 5611 Feb 3 15:12 ewallet_2023020315122250_backupmasterafterpdb.p12
-rw------- 1 rdsdb rdsdb 7259 Feb 3 15:12 ewallet.p12

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select * from v$ENCRYPTION_WALLET;

WRL_TYPE  WRL_PARAMETER                       STATUS  WALLET_TYPE  WALLET_ORDER  KEYSTORE_MODE  FULLY_BACKED_UP  COND_ID
--------- ----------------------------------- ------- ------------ ------------- -------------- ---------------- --------
FILE      /rdsdbdata/db/RDSCDB_A/wallet/tde/  OPEN    PASSWORD     SINGLE        NONE           NO               1
FILE                                          OPEN    PASSWORD     SINGLE        UNITED         NO               2
FILE                                          OPEN    PASSWORD     SINGLE        UNITED         NO               3

SQL> SELECT KEY_ID, creation_time, activation_time, tag FROM V$ENCRYPTION_KEYS;

KEY_ID                                                CREATION_TIME                       ACTIVATION_TIME                          TAG.         CON_ID
----------------------------------------------------- ----------------------------------- ---------------------------------------- ------------ -------
ASTy157YdE/Dv+dGU/ZPVBEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA  03-FEB-23 03.00.37.102322 PM +00:00 03-FEB-23 03.00.37.102324 PM +00:00      master key   1
AY05SMaFV09Hv/QjhZP798sAAAAAAAAAAAAAAAAAAAAAAAAAAAAA  03-FEB-23 03.12.22.377188 PM +00:00 03-FEB-23 03.12.22.377191 PM +00:00      PDB KEY      2
AaiiAvCQJE/Dv7hLOSOcZ28AAAAAAAAAAAAAAAAAAAAAAAAAAAAA  03-FEB-23 03.12.22.638438 PM +00:00 03-FEB-23 03.12.22.638440 PM +00:00      PDB KEY      3

-- Check the contents of the Alert log:

$ adrci

adrci> show homepath
ADR Homes:
diag/rdbms/RDSCDB/RDSCDB_A

adrci> set homepath diag/rdbms/RDSCDB/RDSCDB_A
adrci> show alert -tail 50

[…]
2023-02-03T15:05:36.588230+00:00
ARC3 (PID:30032): Archived Log entry 369 added for T-1.S-388 ID 0xcb50ed4b LAD:1
2023-02-03T15:10:37.563693+00:00
Thread 1 advanced to log sequence 390 (LGWR switch), current SCN: 1703354
Current log# 2 seq# 390 mem# 0: /rdsdbdata/db/cdb/RDSCDB_A/onlinelog/o1_mf_2_kt6qz6p3_.log
2023-02-03T15:10:37.577050+00:00
ARC0 (PID:30024): Archived Log entry 370 added for T-1.S-389 ID 0xcb50ed4b LAD:1
2023-02-03T15:12:22.761807+00:00
ORCL(3):Creating new database key for new master key and wallet
ORCL(3):Creating new database key with the new master key
ORCL(3):New database key and new master key created successfully
2023-02-03T15:15:38.554838+00:00
Thread 1 advanced to log sequence 391 (LGWR switch), current SCN: 1704680
Current log# 4 seq# 391 mem# 0: /rdsdbdata/db/cdb/RDSCDB_A/onlinelog/o1_mf_4_kt6qz83m_.log
2023-02-03T15:15:38.570242+00:00
ARC1 (PID:30028): Archived Log entry 371 added for T-1.S-390 ID 0xcb50ed4b LAD:1
    SQL
  5. Convert the password-protected software keystore to auto-login: 
    Note: We recommend to convert Keystore to Autologin so Keystore is open by itself without waiting for manual Keystore open operation 
and encrypted data is available immediately after restart to users.

SQL> show con_name

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

SQL> administer key management create auto_login keystore from keystore identified by '***';

keystore altered.

SQL> !ls -lrt /rdsdbdata/db/RDSCDB_A/wallet/tde/
-rw------- 1 rdsdb rdsdb 2555 Feb 3 15:00 ewallet_2023020315003700_masterbackup.p12
-rw------- 1 rdsdb rdsdb 5611 Feb 3 15:12 ewallet_2023020315122250_backupmasterafterpdb.p12
-rw------- 1 rdsdb rdsdb 7259 Feb 3 15:12 ewallet.p12
-rw------- 1 rdsdb rdsdb 7304 Feb 3 15:21 cwallet.sso
    SQL
  6. Pause the automation framework and restart the database for the configuration to take effect: 
    -- shutdown and startup the database:
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 2.4964E+10 bytes
Fixed Size 13873328 bytes
Variable Size 1.2080E+10 bytes
Database Buffers 1.2818E+10 bytes
Redo Buffers 53231616 bytes
Database mounted.
Database opened.

SQL> show pdbs

CON_ID     CON_NAME   OPEN MODE  RESTRICTED
---------- ---------- ---------- -----------
2          PDB$SEED   READ ONLY  NO
3          ORCL       MOUNTED

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> set pages 1000 lines 200
SQL> select * from v$encryption_wallet;

WRL_TYPE WRL_PARAMETER                       STATUS  WALLET_TYPE   WALLET_ORDER KEYSTORE_MODE FULLY_BACKED_UP CON_ID
-------- ----------------------------------- ------- ------------- ------------ ------------- --------------- ----------
FILE     /rdsdbdata/db/RDSCDB_A/wallet/tde/  OPEN    AUTOLOGIN     SINGLE       NONE          NO              1
FILE                                         OPEN    AUTOLOGIN     SINGLE       UNITED        NO              2
FILE                                         OPEN    AUTOLOGIN     SINGLE       UNITED        NO              3

SQL> show pdbs

CON_ID    CON_NAME   OPEN MODE  RESTRICTED
--------- ---------- ---------- ----------
2         PDB$SEED   READ ONLY  NO
3         ORCL       READ WRITE NO
    SQL
  7. Perform a backup of the keystore: 
    Note: Once all the above changes are done take a full backup of Keystore. Once the backup is taken column FULLY_BACKED_UP will change to YES.

SQL> administer key management backup keystore using 'WalletBackupFULL' force keystore identified by '****';

keystore altered.

SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER                       STATUS  WALLET_TYPE   WALLET_ORDER KEYSTORE_MODE FULLY_BACKED_UP CON_ID
-------- ----------------------------------- ------- ------------- ------------ ------------- --------------- ----------
FILE     /rdsdbdata/db/RDSCDB_A/wallet/tde/  OPEN    AUTOLOGIN     SINGLE       NONE          YES             1
FILE                                         OPEN    AUTOLOGIN     SINGLE       UNITED        YES             2
FILE                                         OPEN    AUTOLOGIN     SINGLE       UNITED        YES             3
    SQL

Implement TDE in a pluggable database in isolated mode

The high-level steps to implement TDE in isolated mode are the following:

  1. In the CDB root, configure the database to use united mode by setting the WALLET_ROOT and TDE_CONFIGURATION parameters:
    1. WALLET_ROOT sets the location for the wallet directory.
    2. TDE_CONFIGURATION sets the type of keystore to use.
  2. Pause the automation framework and restart the database so that these settings take effect.
  3. In the CDB root, create and open the keystore, then create the TDE primary encryption key.
  4. In the PDB, configure the parameter TDE_CONFIGURATION parameters:
    1. WALLET_ROOT is a prerequisite; it sets the location for the wallet directory.
    2. TDE_CONFIGURATION sets the type of keystore to use.
  5. Create and open the PDB keystore, then create the TDE primary encryption key.
  6. Configure the auto-login option for the PDB keystore.

Let’s explore the implementation steps and code in more detail. The code for Steps 1–3 is the same as in the previous section.

  1. Configure the CDB root as detailed in the previous section.
  2. Pause the automation framework as shown in the previous section.
  3. Create the keystore and TDE primary key in the CDB root as shown in the previous section.
  4. Configure the keystore of the PDB in isolated mode: 
    -- Connected as 'SYS as sysdba':
$ sqlplus '/as sysdba'

-- Note: you can retrieve the SYS password from the AWS Secrets Manager, please refer to this guide
-- We recommend taking manual snapshot backup of database  before the change

SQL> show pdbs

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

SQL> ALTER SESSION SET CONTAINER=PDB1;

Session altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER   STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BACCON_ID
-------------------- --------------- ------------------------------ -------------------- --------- -------- --------- ----------
FILE                                 OPEN_NO_MASTER_KEY             PASSWORD             SINGLE    UNITED   UNDEFINED 3

SQL> select * from v$encryption_keys;

no rows selected

SQL> ALTER SYSTEM SET TDE_CONFIGURATION='KEYSTORE_CONFIGURATION=FILE' SCOPE=BOTH;

System altered.

SQL> select name, guid from v$containers;

NAME     GUID
-------- ------------------------------------
TDEBLG   FF94C94F1DBB1806E053391B1FAC7965

SQL> select WRL_PARAMETER, status, KEYSTORE_MODE from v$encryption_wallet;

WRL_PARAMETER                                                                    STATUS                 KEYSTORE
-------------------------------------------------------------------------------- ---------------------- ------------
/rdsdbdata/db/cdb/RDSCDB_A/wallet/FF94C94F1DBB1806E053391B1FAC7965/tde/          OPEN_NO_MASTER_KEY     ISOLATED
    SQL
  5. Create and open the keystore and create a TDE encryption key for the PDB: 
    -- Note: the WALLET_ROOT parameter is a pre-requisite and it is required to be configured with a path that is in the /rdsdbdata file system,
-- because this will makes sure that any future minor and major version upgrades of the Oracle software will not overwrite the TDE configuration,
-- possibly causing data loss if you don’t have a valid and recent backup of the wallet file.

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY '*****';

System altered.
Note: In Isolated Mode you can use a different password for the PDB keystore.

-- Open the keystore:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN FORCE KEYSTORE IDENTIFIED BY '*****';

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY '*****' WITH BACKUP;

keystore altered.

SQL> select WRL_PARAMETER, status, KEYSTORE_MODE from v$encryption_wallet;

WRL_PARAMETER                                                                    STATUS                 KEYSTORE
-------------------------------------------------------------------------------- ---------------------- ------------
/rdsdbdata/db/cdb/RDSCDB_A/wallet/FF94C94F1DBB1806E053391B1FAC7965/tde/          OPEN                   ISOLATED

SQL> select * from v$encryption_wallet;
 
WRL_TYPE   WRL_PARAMETER                                                             STATUS   WALLET_TYPE      WALLET_ORDER  KEYSTORE_MODE  FULLY_BACKED_UP  CON_ID
---------- ------------------------------------------------------------------------- -------- ---------------- ------------- -------------- ---------------- -------
FILE       /rdsdbdata/db/cdb/RDSCDB_A/wallet/tde/                                    OPEN     AUTOLOGIN        SINGLE        NONE           NO               1
FILE                                                                                 OPEN     AUTOLOGIN        SINGLE        UNITED         NO               2
FILE       /rdsdbdata/db/cdb/RDSCDB_A/wallet/FF94C94F1DBB1806E053391B1FAC7965/tde/   OPEN     LOCAL_AUTOLOGIN  SINGLE        ISOLATED       NO               3
    SQL
  6. Configure the auto-login option for the PDB keystore: 
    SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY ******;

keystore altered.

-- Note: The PDB keystore is now placed in a subfolder of WALLET_ROOT matching the PDB GUID. 
-- You find similar files, ewallet.p12 and cwallet.sso in the dedicated folder for the isloated PDB keystore:

$ pwd
/rdsdbdata/db/cdb/RDSCDB_A/wallet/*******pdb-guid******/tde
-rw-------. 1 rdsdb rdsdb 2120 Feb 03 09:37 cwallet.sso
-rw-------. 1 rdsdb rdsdb 2059 Feb 03 09:35 ewallet.p12
    SQL

Verify TDE and Amazon RDS Custom automation

You can proceed with creating encrypted columns and tablespaces in the database to validate that the TDE implementation works as expected. Additionally, pause the automation and restart the database to check if the wallet auto-login works as expected, with encrypted objects being accessible after restart. See the following code:

-- connected to the PDB, create a table with an encrypted column:

SQL> create table demotab (empid number encrypt using 'AES256');

Table created.

-- create an encrypted tablespace:

SQL> create tablespace DEMOENCRYPT datafile size 10M encryption using 'AES256' default storage(encrypt);

Tablespace created.

SQL> select name from v$datafile;

NAME
-----------------------------------------------------------------------------------
[...]
/rdsdbdata/db/ORCL_A/*******pdb-guid******/datafile/o1_mf_demoencr_kxl4goo1_.dbf
[...]

-- Also you can set the below parameter to ensure that new tablespaces being created as encrypted by default 

SQL> sho parameter encrypt_new_tablespaces

NAME                      TYPE        VALUE
------------------------- ----------- ------------
encrypt_new_tablespaces   string      CLOUD_ONLY

SQL> alter system set encrypt_new_tablespaces='ALWAYS';

System altered.

-- create a new encrypted tablespace without specifying the encryption clauses:

SQL> create tablespace TBLSNEW datafile size 10M;

Tablespace created.

SQL> select t.name, e.encryptionalg, e.encryptedts, e.STATUS from v$encrypted_tablespaces e, v$tablespace t where t.ts#=e.ts#(+);

NAME         ENCRYPTIONALG  ENCRYPTEDTS  STATUS
------------ -------------- ------------ ----------
TBLSNEW      AES128         YES          NORMAL

-- check the alert log of the database:

$ adrci

adrci> show homepath
ADR Homes:
diag/rdbms/RDSCDB/RDSCDB_A

adrci> set homepath diag/rdbms/RDSCDB/RDSCDB_A
adrci> show alert -tail 10

[…]
ALTER SYSTEM SET encrypt_new_tablespaces='ALWAYS' SCOPE=BOTH;
2023-02-03T15:38:22.893327+00:00
create tablespace TBLSNEW datafile size 10M
2023-02-03T15:38:22.893398+00:00
Force tablespace TBLSNEW to be encrypted
Completed: create tablespace TBLSNEW datafile size 10M
SQL

All of the other operations related to rekeying the primary key, changing the password of the keystore, and taking regular backups of the wallet and auto-login file are the same as in Part 1 of this series.

In Part 1, we listed some maintenance scenarios applicable to Amazon RDS Custom for Oracle and described their impact on the implemented TDE. The same set of scenarios and considerations still apply in the case of the multi-tenant database architecture described in this post.

Conclusion

In this post, we described how you can implement Oracle TDE in Amazon RDS Custom for Oracle with the multi-tenant option enabled. We discussed the steps to enable both united and isolated modes, and how the benefits of following the procedure described in this post are a better and tested integration of Oracle TDE implementation with the Amazon RDS Custom for Oracle service. The database instance configured with the procedure described in this post will be more reliable and robust during its entire lifecycle, considering both planned maintenance activities, such as minor version patching or database restore and unplanned failures. We strongly recommend you to follow the detailed steps described in this post as they can be considered best practice. We welcome your comments.

About the Authors

Domenico di Salvia is a Senior Database Specialist Solutions Architect at AWS. In his role, Domenico works with customers in the EMEA region to provide guidance and technical assistance on database projects, helping them improve the value of their solutions when using or migrating to AWS, designing scalable, secure, performant, sustainable, cost-effective, and robust database architectures in the AWS Cloud.

Prasad Matkar is Database Specialist Solutions Architect at AWS based in the EMEA region. With a focus on relational database engines, he provides technical assistance to customers in migrating and modernizing their database workloads to AWS. He has over 16 years of experience working with Oracle databases.