Secure data at rest on Amazon RDS Custom for Oracle with TDE – Part 1: non-CDB environments

In this first post of this two part series, we show you the options available to set up security using Transparent Data Encryption (TDE) to protect data at rest for an Oracle non-multi-tenant database running in Amazon Relational Database Service (Amazon RDS) Custom for Oracle. The second post will focus on the TDE implementation for the multi-tenant Oracle database. Also, if you need to implement TDE with Amazon RDS for Oracle then you can refer this documentation.

In this series, we also assess the impact of maintenance operations to the Oracle TDE configuration and important considerations that you may need to factor in.

Due to compliance requirements and increasing security threats, security of the database layer has become more important than ever before. Encrypting data at rest and database backups is an effective way of enforcing strong security controls. Amazon RDS Custom for Oracle can natively encrypt the underlying storage as well as 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 can help make sure that other operating system users trying to look at Oracle datafiles from outside the database can only see encrypted information. Additionally, the data encrypted in TDE columns and tablespaces are also encrypted in the redo log and archive log data streams. TDE can also be used to verify that Oracle datafiles contain encrypted data when copied or backed up using user-managed means.

What is Oracle Transparent Data Encryption?

Transparent Data Encryption is part of the Oracle Advanced Security Option (ASO) and is used to encrypt sensitive data stored in tables and tablespaces. ASO also includes Data Redaction, a feature that allows masking data on the fly when the data is returned from queries. Oracle Advanced Security is only available with Oracle Database Enterprise Edition (EE) and is not available with Oracle Database Standard Edition 2 (SE2). Additionally, ASO requires a separate Oracle license.

The main benefits of using TDE include the following:

• TDE helps address security-related regulatory compliance issues beyond those that can be handled by the native RDS Custom for Oracle storage encryption.
• No code or application changes are needed to handle the encrypted data; the database manages the data encryption and decryption transparently.
• As a security administrator, you can be assured that sensitive data is encrypted in the event that the storage media or datafiles are stolen.
• Oracle Database automates TDE primary encryption key and keystore management operations; the user or application doesn’t need to manage TDE primary encryption keys.

With TDE, after the data is encrypted, it’s transparently decrypted by the database engine for authorized users and applications when they access it. To prevent unauthorized decryption, TDE uses a two-layer encryption key mechanism, where the layer-one key, called the primary key, is stored in a security module external to the database (the keystore). The keystore can be a password-protected file, the wallet, or a hardware security module (HSM). For this post, we only focus on the wallet keystore. The layer-two keys can be stored in the data dictionary to implement column-level TDE, as shown in the following figure:

Alternatively, they can be stored in the header of the tablespace datafiles to implement tablespace-level TDE.

The layer-two encryption keys are encrypted and decrypted using the TDE primary key, which promotes a higher level of protection.

TDE also helps secure the backups by protecting backup data stored on media, in the event that the storage media is stolen.

Solution overview

Amazon RDS Custom for Oracle automates the setup, operation, and scaling of databases in the cloud while granting access to the database and underlying operating system to configure settings, install patches, and enable native features to adhere to the dependent application’s requirements. Amazon RDS Custom allows privileged access to the Amazon Elastic Compute Cloud (Amazon EC2) machine that runs the Oracle database, meaning that root access to the Linux operating system and SYS privileged access to the database are allowed.

In contrast to Amazon RDS for Oracle, with Amazon RDS Custom for Oracle, the customer has access to the underlying operating system and database, which can be customized. Amazon RDS Custom brings the benefits of Amazon RDS to customers who can’t easily move to a fully managed service because of customizations that are required with third-party, legacy, and packaged business applications. Examples of possible customizations are:

• Installing a custom database and OS patches or third-party packages and modules
• Configuring specific database settings not allowed in Amazon RDS for Oracle

Amazon RDS Custom introduces a new concept of a support perimeter. The database instance is periodically health-checked to verify that it’s working as expected and that Amazon RDS automation isn’t broken. This means that if a specific change breaks the automation, the instance is marked as out of perimeter (the state changes to Unsupported Configuration) and the automation stops working. As a consequence, no more automated backups are run, the monitoring is stopped, support from AWS is suspended, and no operations are allowed until the issue is manually resolved by the customer, eventually reverting back the change. This is another reason why we recommend testing any kind of change in a test environment before implementing it in production.

With all this said, the Oracle TDE standard configuration procedure needs to be slightly adapted when it comes to implementing it on an RDS Custom for Oracle database instance. You have to make sure you persist the TDE configuration to survive the lifecycle management of the database instance, meaning the planned maintenances such as database or OS patches or the unplanned failures.

Prerequisites

Before starting, it’s assumed that you have deployed an RDS Custom for Oracle 19c database instance and that you have access to the Linux OS through SSH or AWS Systems Manager Session Manager. For instructions, refer to Creating and connecting to a DB instance for Amazon RDS Custom for Oracle.

The steps mentioned in this post and the syntax used is valid for Oracle database versions starting from version 12c and newer.

Implement TDE on Amazon RDS Custom for Oracle

This section contains the steps to implement TDE on Amazon RDS Custom for Oracle version 18c and above—the WALLET_ROOT and TDE_CONFIGURATION database initialization parameters aren’t available in previous versions of the database. Therefore, from database release 18c onwards, it’s strongly recommended to set the WALLET_ROOT static initialization parameter and TDE_CONFIGURATION dynamic initialization parameter in the database to designate the location and type of keystores that you plan to use. The database locates this keystore by first checking the WALLET_ROOT setting. If this setting has not been created, then the database checks the sqlnet.ora file.

Regarding the configuration parameters we’ve mentioned, the following is some useful information you need to know before you set them up:

• WALLET_ROOT – This parameter specifies the top directory for different software keystores (such as TDE, Oracle Enterprise User Security, or TLS). For TDE, the directory for automated discovery is WALLET_ROOT/tde.
• TDE_CONFIGURATION – This parameter specifies the type of keystore (software keystore, Oracle Key Vault, or hardware security module). After you set the type of keystore using TDE_CONFIGURATION, when you create the keystore, Oracle Database creates a directory within the WALLET_ROOT location for the keystore type. For example, if you set TDE_CONFIGURATION to FILE, then Oracle Database creates a TDE wallet in WALLET_ROOT/tde. In this post, we set the parameter TDE_CONFIGURATION to FILE (software keystore).

Before you begin, take manual snapshot backup of the database instance and pause the automation so you can restore the database if something goes wrong or if the instance falls into the Unsupported configuration status and you can’t successfully revert back to when all of the steps were run. For instructions, refer to Pausing and resuming RDS Custom DB instance.

Configure the software keystore

A software keystore is a container that stores the TDE primary encryption key. You must configure the keystore location by setting WALLET_ROOT and TDE_CONFIGURATION, and then restart the database instance for it to take effect.

There are three different types of software keystores:

• Password-protected software keystores
• Auto-login software keystores
• Local auto-login software keystores

In addition to the software keystores, keystores can be externally managed using third-party hardware security modules or Oracle Key Vault. AWS also offers AWS CloudHSM.

In this post, we focus primarily on local auto-login software keystores. Note the following differences between auto-login and local auto-login software keystores:

• Auto-login software keystores – These are protected by a system-generated password and don’t need to be explicitly opened by database administrators manually. They are automatically opened by the database when they are accessed and can be used across different systems. The auto-login software keystore is recommended for environments where extra security is not required (environments where it’s explicitly required that wallets are manually opened). Auto-login software keystores are ideal for high availability and disaster recovery scenarios (such as active/passive configurations or Oracle Data Guard standby), where an unattended event, such as a failure, leads to a secondary node being activated.
• Local auto-login software keystores – These are auto-login software keystores that are local to the machine on which they are created. Local auto-login keystores can’t be opened on a machine other than the one on which they are created. This type of keystore is typically used for scenarios where extra security is required (to limit the use of the auto-login for that machine). For this reason, local auto-login wallets can’t be used in Oracle RAC-enabled databases, where only shared wallets are supported.

A password-protected software keystore is a software keystore with no auto-login capabilities. This software keystore requires extra work to manually open the wallet using a password in cases when the database is started or restarted. This includes point-in-time restores, creation of read replicas, database instance reboots, and more.

In this post, we include the steps for the local auto-login use case. If you want to use the auto-login option without the LOCAL attribute, remove the LOCAL keyword from the commands. For example, the following code uses the LOCAL option:

SQL> administer key management create LOCAL auto_login keystore from keystore '**' ... ;

The detailed implementation steps as below

Step 1 – Configure the TDE by setting the initialization 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 and backup of the spfile before the change 
-- Note: the target directory should exist

SQL> create pfile='/rdsdbdata/db/ORCLPM_A/pfilebackup/initORCLPM_200322.ora' from spfile;

File created.

-- Note: the database unique name used above “ORCLPM_A” can be retrieved from the
-- database configuration as shown below:

SQL> select db_unique_name from v$database;

Create a wallet directory and verify that it is created in the /rdsdbdata file system. This helps to make sure that future minor and major version upgrades of the Oracle software don’t overwrite the TDE configuration, possibly causing data loss if you don’t have a valid and recent backup of the wallet file.

Set the WALLET_ROOT static initialization parameter for the wallet directory. The TDE wallet will be created in the WALLET_ROOT/tde subdirectory. For example, for a database instance named orcl_A, we use the following code:

-- Create the wallet directory in the filesystem
-- Note: the selected filesystem is /rdsdbdata and ORCLPM_A is the value of the db_unique_name parameter of the database

$ mkdir -p /rdsdbdata/db/ORCLPM_A/wallet

-- We are setting the Static parameter in spfile and taking a DB restart (connected as 'SYS as sysdba')

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

System altered.

Step 2 – Pause the automation framework and restart the database to configure the wallet keystore

-- Restart the database

$ sqlplus '/as sysdba'
SQL> shutdown immediate;
SQL> startup;


-- Check the configuration after the restart:

SQL> show parameter wallet_root
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
wallet_root                          string      /rdsdbdata/db/ORCLPM_A/wallet

-- We will be setting the keystore_type to FILE to configure the keystore

SQL> alter system set TDE_CONFIGURATION='KEYSTORE_CONFIGURATION=FILE' scope=both;

System altered.

SQL> show parameter tde_configuration


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
tde_configuration                    string      KEYSTORE_CONFIGURATION=FILE

Step 3 – 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 <passwd>;

keystore altered.

-- OS Verification:

$ ls -lrt /rdsdbdata/db/ORCLPM_A/wallet/
-rw------- 1 rdsdb rdsdb 2555 Mar 20 23:04 ewallet.p12 <-- the wallet file

-- Enable auto-login for the keystore

SQL> administer key management create LOCAL auto_login keystore from keystore '/rdsdbdata/db/ORCLPM_A/wallet/tde/' identified by <passwd>;

keystore altered.

$ ls -lrt /rdsdbdata/db/ORCLPM_A/wallet/tde/
-rw------- 1 rdsdb rdsdb 2555 Mar 20 23:04 ewallet.p12
-rw------- 1 rdsdb rdsdb 2600 Mar 20 23:09 cwallet.sso <-- the local auto-login file

SQL> col wrl_parameter format a50
SQL> select WRL_TYPE,WRL_PARAMETER,STATUS,WALLET_TYPE from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER
-------------------- --------------------------------------------------
STATUS                         WALLET_TYPE
------------------------------ --------------------
FILE                 /rdsdbdata/db/ORCLPM_A/wallet/tde/
OPEN_NO_MASTER_KEY             LOCAL_AUTOLOGIN

-- Note: The wallet contains no master key yet (OPEN_NO_MASTER_KEY)

After you run this statement, the cwallet.sso file is created in the keystore location. The ewallet.p12 file is the password-protected wallet. Make sure you don’t remove the PKCS#12 wallet (ewallet.p12 file) after you create the auto-login keystore (.sso file) because it’s needed to regenerate or rekey the TDE primary encryption key in the future.

Step 4 – Open the software keystore and create the primary key

The software keystore needs to be opened before we can proceed with storing primary keys. The status OPEN_NO_MASTER_KEY means the primary key has not been created yet in the keystore.

We can manually open the keystore by issuing the ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN statement, or include the FORCE KEYSTORE clause in the ADMINISTER KEY MANAGEMENT statement that is used to perform the operation. FORCE KEYSTORE temporarily opens the keystore for the duration of the operation, and when the operation is complete, the keystore is closed again. FORCE KEYSTORE is useful for situations when the database is heavily loaded. See the following code:

-- Create the new master key

SQL> administer key management set key using tag '19C-TDEIMPL' force keystore identified by <passwd> with backup using 'TDE-BKP';

keystore altered.

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

FORCE KEYSTORE automatically opens the keystore before setting the 
TDE master encryption key. The FORCE KEYSTORE clause also switches over to 
opening the password-protected software keystore when an auto-login keystore 
is configured and is currently open
===============================================================================

$ ls -lrt /rdsdbdata/db/ORCLPM_A/wallet/tde/
-rw------- 1 rdsdb rdsdb 2555 Mar 20 23:23 ewallet_2023032023235516_TDE-BKP.p12 <-- a backup of the wallet was created using "with backup" clause
-rw------- 1 rdsdb rdsdb 4171 Mar 20 23:23 ewallet.p12
-rw------- 1 rdsdb rdsdb 4216 Mar 20 23:23 cwallet.sso

-- Check the wallet status:

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

WRL_TYPE             WRL_PARAMETER
-------------------- --------------------------------------------------
STATUS                         WALLET_TYPE          
------------------------------ -------------------- 
FILE                 /rdsdbdata/db/ORCLPM_A/wallet/tde/
OPEN                           LOCAL_AUTOLOGIN

-- Note: Now the wallet is OPEN, and contains a valid master key, see the output below

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

KEY_ID
------------------------------------------------------
TAG
-------------
KEYSTORE_TYPE       CREATION_TIME
------------------- --------------------------------------
AfyrLC/8xE9tv9FpsubpapYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
19C-TDEIMPL
SOFTWARE KEYSTORE   20-MAR-23 11.23.55.267519 PM +00:00

The steps listed in this section are the basic steps to implement the TDE in a non-CDB (container database) architecture. The steps needed for a CDB architecture are pretty similar, but with some additional steps—the detailed procedure to enable TDE on a CDB architecture is in the second post of this series.

Step 5 – Resume the Amazon RDS Custom automation

With TDE configured, you first need to resume the automation. The following screenshot shows how to resume automation on the Amazon RDS console.

Then you should verify that the Amazon RDS automation is not broken. This means that the database instance should be in the Available status on the Amazon RDS console.

Step 6 – Verify TDE setup and create encrypted objects and tablespaces

-- 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.

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

SQL> show parameter encrypt

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

SQL> alter system set encrypt_new_tablespaces='ALWAYS';

System altered.

-- Check that a new tablespace is encrypted by default:

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 to verify that the newly created tablespace 
-- was automatically encrypted by the engine:

$ adrci

adrci> show homepath
ADR Homes:
diag/rdbms/ORCLPM/ORCLPM_A

adrci> set homepath diag/rdbms/ORCLPM/ORCLPM_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

We also recommend pausing the automation and manually restarting the database to check that the wallet auto-login works as expected (it’s automatically reopened by the database after the restart) with encrypted objects being accessible after restart (select * from <table>). Don’t forget to resume the automation at the end.

Maintain the wallet and rotate the primary key

In this section, we discuss the various operations you can perform as part of ongoing wallet maintenance like rekeying the key, changing the keystore password and taking regular backups of the wallet.

Rekey the primary key

It’s highly recommended to periodically rekey the primary key in order to have a higher level of protection:

-- The command to re-key the master key (with backup)

SQL> administer key management set key identified by "******" with backup;
 
keystore altered.
 
SQL> select key_id, activation_time from v$encryption_keys;
 
KEY_ID
----------------------------------------------
ACTIVATION_TIME
-------------------------------------
AZfqjdHbU/mv2CcsZaX2+PAAAAAAAAAAAAAAAAZZZZZZZ <-- the newly activated master key
31-JAN-23 01.06.54.096659 PM +00:00
 
AZVjL6kJZU8mv8QbjGHx24YAAAAAAAAAAAAAAAABBBBBB
31-JAN-23 12.47.26.899319 PM +00:00

Change the keystore password

For similar security reasons, it’s highly recommended to periodically change the password of the wallet file:

-- The command to change the wallet password (with backup):

SQL> administer key management alter keystore password identified by "**oldpwd**" set "**newpwd**" with backup using 'change';
 
keystore altered.
 
-- check from the OS:

$ ls -lrt /rdsdbdata/db/ORCLPM_A/wallet/tde/
total 28
-rw------- 1 rdsdb rdsdb 2555 Jan 31 12:45 ewallet_2023013112451134.p12
-rw------- 1 rdsdb rdsdb 3803 Jan 31 12:47 ewallet_2023013112472680.p12
-rw------- 1 rdsdb rdsdb 3995 Jan 31 13:06 ewallet_2023013113065400.p12
-rw------- 1 rdsdb rdsdb 5259 Jan 31 13:19 ewallet_2023013113192219_change.p12 <-- note the suffix "change"
-rw------- 1 rdsdb rdsdb 5259 Jan 31 13:19 ewallet.p12

Take regular backups of the keystore

During Amazon RDS automated backups or manual snapshots, the entire database instance is backed up, including the wallet. Therefore, it’s not strictly necessary to have a backup of the wallet outside from the database instance; it can be restored by restoring the database instance. However, you can have your backup strategy for the wallet and copy it in a secure location outside from the database instance every time you modify it. A recommended option would be to copy it in Amazon Simple Storage Service (Amazon S3) using AWS Command Line Interface (AWS CLI). The following code shows how to back up the keystore using the SQL commands provided by the database engine:

-- Command to backup the software keystore

SQL> ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE USING 'tde19c' IDENTIFIED BY "*****" to '/rdsdbbin/oracle/wallet';
 
keystore altered.
 

SQL> !ls -lrt
total 36
-rw------- 1 rdsdb rdsdb 2555 Jan 31 12:45 ewallet_2023013112451134.p12
-rw------- 1 rdsdb rdsdb 3803 Jan 31 12:47 ewallet_2023013112472680.p12
-rw------- 1 rdsdb rdsdb 3995 Jan 31 13:06 ewallet_2023013113065400.p12
-rw------- 1 rdsdb rdsdb 5259 Jan 31 13:19 ewallet_2023013113192219_change.p12
-rw------- 1 rdsdb rdsdb 5259 Jan 31 13:20 ewallet_2023013113204227_tde19c.p12
-rw------- 1 rdsdb rdsdb 5259 Jan 31 13:20 ewallet.p12

Now the wallet backup file can be copied to Amazon S3 using AWS CLI. For more information, refer to Batch Upload Files to Amazon S3 Using the AWS CLI.

Potential impacts on TDE implementation

Database instance lifecycle management can potentially impact the TDE configuration if TDE is not implemented using the steps suggested. Also, the local or non-local configuration of the auto-login file opens up additional considerations.

The following table summarizes the different scenarios in which we tested an already-created TDE configuration.

Conclusion

In this post, we described how you can implement Oracle TDE in an RDS Custom for Oracle database instance, which involves specific steps to verify that the database instance is back in the support perimeter and being monitored for high availability.

In the second post of this series, we take a deeper dive into implementing TDE in a multi-tenant environment in Amazon RDS Custom for Oracle. 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.