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:
The detailed implementation steps as below
Step 1 – Configure the TDE by setting the initialization parameters
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:
Step 2 – Pause the automation framework and restart the database to configure the wallet keystore
Step 3 – Create a password-protected software keystore
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:
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
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:
Change the keystore password
For similar security reasons, it’s highly recommended to periodically change the password of the wallet file:
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:
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.
Scenario
Keystore Set to Local Auto-Login
Keystore Set to Auto-Login
CEV Change or Patching Activity
Observations: No impact on existing TDE configuration after DB minor version upgrade.
Actions: No manual intervention needed.
Database restore (PITR)
Observations: TDE parameters setup not present in the restored database. But TDE files do exist in the folder locations.
Actions: TDE parameters need to be set up and only the auto-login (.sso) files need to be recreated.
Observations: TDE parameters setup not present in the restored database. But TDE files do exist in the folder locations.
Actions: TDE parameters need to be set up again to open the wallet.
Read Replicas
Observations: Wallet configuration is present on the standby host but the wallet is in closed state. Recovery of the read replica continues without any impact.
Actions: Auto-login file needs to be recreated on the standby host.
Observations: No impact on TDE configuration; database is in read-only state with wallet open.
Actions: No manual intervention needed.
Flashback of Primary
Observations: No impact to the TDE setup after flashback or OPEN RESETLOGS operations.
Actions: No manual intervention needed.
Automatic scaling of Compute
Observations: Wallet is in closed state after compute scaling completes.
Actions: Auto-login file needs to be recreated on new compute host in the same directory.
Observations: No impact to TDE setup and wallet automatically opens after database is restarted.
Actions: No manual intervention needed.
Storage Auto scaling
Observations: Online activity is not impacted.
Actions: No manual intervention needed.
Wallet file is not accessible or lost
Observations: Database is open and running and objects in encrypted tablespaces are inaccessible.
Actions: You must restore wallet file from backup or create the wallet file with known password.
Autologin file is not accessible or lost
Observations: Database is open and running and objects in encrypted tablespaces are inaccessible.
Actions: You must restore autologin file from backup or create new file with known password.
DataGuard configuration with Manual standby
Observations: Standby database in a configuration must have a copy of the encryption keystore from the primary database for the recovery to progress.
Actions:
If there is a reset of the TDE primary encryption key in the primary database, then you must copy the keystore from the primary database that contains the TDE primary encryption key to the standby database.
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.
Read MoreAWS Database Blog
