Enabling TDE Tablespace encryption in an Oracle 19c Instance

The purpose of this article is to list and document day-to-day tasks related to Oracle Transparent Data Encryption. I am writing this article in terms of “notes” for myself which you may find helpful. Let’s have a high-level overview of the TDE implementation in the Oracle Database.

What is Oracle Transparent Data Encryption (TDE)?

Oracle Database uses authentication, authorization, and auditing mechanisms to secure data in the database, but not in the operating system data files where data is stored. To protect these data files, Oracle Database provides Transparent Data Encryption (TDE). TDE encrypts sensitive data stored in data files. To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database, called a keystore. Transparent Data Encryption (TDE) enables you to encrypt sensitive data that you store in tables and tablespaces.

After the data is encrypted, this data is transparently decrypted for authorized users or applications when they access this data. TDE helps protect data stored on media (also called data at rest) in the event that the storage media or data file is stolen.

TDE Tablespace encryption

TDE tablespace encryption encrypts all of the data stored in an encrypted tablespace and the corresponding redo data. TDE tablespace encryption does not encrypt data that is stored outside of the tablespace.

All of the data in an encrypted tablespace is stored in an encrypted format on the disk. Data is transparently decrypted for an authorized user having the necessary privileges to view or modify the data. In the event that the data files on a disk or backup media are stolen, the data is not compromised.

TDE tablespace encryption uses the two-tiered, key-based architecture to transparently encrypt (and decrypt) tablespaces. The TDE master encryption key is stored in a security module (Oracle wallet, Oracle Key Vault, or Oracle Cloud Infrastructure key management system (KMS)). This TDE master encryption key is used to encrypt the TDE tablespace encryption key, which in turn is used to encrypt and decrypt data in the tablespace.

TDE Column Encryption

Transparent Data Encryption (TDE) column encryption protects confidential data, such as credit card and Social Security numbers, that is stored in table columns.

TDE column encryption uses the two-tiered key-based architecture to transparently encrypt and decrypt sensitive table columns. The TDE master encryption key is stored in an external keystore, which can be an Oracle wallet, Oracle Key Vault, or the Oracle Cloud Infrastructure key management system (KMS). This TDE master encryption key encrypts and decrypts the TDE table key, which in turn encrypts and decrypts data in the table column. When a table contains encrypted columns, TDE uses a single TDE table key regardless of the number of encrypted columns. Each TDE table key is individually encrypted with the TDE master encryption key.

Prerequisites for enabling TDE

Make sure you have an Advanced Security Option license which is an extra-cost license before proceeding. In OCI DBCS it is included by default.

In this exercise, we are considering the 19c Oracle Enterprise database without container databases. For assumptions, UATDB_STDY is the unique name for the standby database for UATDB_PRIM which is the unique name for the primary.

Using Keystores

There are 2 types of key stores: hardware security module (HSM) and software. For these purposes, we are going to use software keystore because it provides more flexibility and initially costs less to implement. Software keystores include three configuration types:

1. Password-based keystore
2. Auto-login keystore, and
3. Local auto-login keystore ( we are using this type in our exercise)

Configure Keystores

• Create TDE wallet Directory

mkdir "${ORACLE_BASE}/admin/${DB_UNIQUE_NAME}/wallet/tde"

• This will set some TDE-related DB parameters and create a TDE wallet/keystore and generate a master key as well and convert the wallet to an autologin wallet. Replace the wallet password, db_unique_name in the below statements.

# source DB environment

sqlplus -s "/as sysdba"<< EOF
---set parameters----
alter system set wallet_root=‘{{ oracle_base }}/admin/{{ db_unique_name| upper }}/wallet’ scope=spfile;
ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE = BOTH;

---create keystore---
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '{{ oracle_base }}/admin/{{ db_unique_name| upper }}/wallet/tde' IDENTIFIED BY {{ wallet_pass }};                                              

-----convert keystore to AUTOLOGIN----
ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '{{ oracle_base }}/admin/{{ db_unique_name| upper }}/wallet/tde' IDENTIFIED BY {{ wallet_pass }};

----open keystore----
administer key management set keystore open force keystore identified by {{ wallet_pass }} ;

-----Set master encryption key-----
ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE  IDENTIFIED BY {{ wallet_pass }} WITH BACKUP ;

------Close Wallet----------
administer key management set keystore close identified by  {{ wallet_pass }};
select con_id, wallet_type,wrl_parameter, status from v$encryption_wallet;
EOF

• You must set the compatible, wallet_root and TDE_CONFIGURATION initialization parameters on all instances of the database (RAC or standby nodes) before creating an encrypted tablespace.
• Copy the wallet directory to all nodes in case of Oracle RAC if the wallet is not created in an ASM disk group location.
• Copy the wallet to all standby nodes as well as any DR nodes.

Gather Information about the TDE keystore

set linesize 250 pagesize 250
column name format a40
column masterkeyid_base64 format a60
select  name,utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64  FROM (select t.name, RAWTOHEX(x.mkid) mkeyid from v$tablespace t, x$kcbtek x where t.ts#=x.ts#);
select con_id, wallet_type,wrl_parameter, status from v$encryption_wallet;
select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);

Encrypting New Tablespaces

Run the CREATE TABLESPACE the statement, using its encryption clauses.

CREATE TABLESPACE encrypt_ts
  DATAFILE '/data/..../encrypt_df.dbf' SIZE 1M
  ENCRYPTION USING 'AES256' ENCRYPT;

In this specification:

• ENCRYPTION USING ‘AES256' ENCRYPT specifies the encryption algorithm and the key length for the encryption. The ENCRYPT clause encrypts the tablespace. Enclose this setting in single quotation marks (‘ ‘). The key lengths are included in the names of the algorithms. If you do not specify an encryption algorithm, then the default encryption algorithm, AES128, is used.

Note: no separate effort is required on standby instance in case of creating new tablespace with tde encryption enabled

Encrypting Future Tablespaces

You can set the ENCRYPT_NEW_TABLESPACES database initialization parameter to automatically encrypt future tablespaces that you create.

• In SQL*Plus, enter the following ALTER SYSTEM statement:

ALTER SYSTEM SET ENCRYPT_NEW_TABLESPACES = value;

• In this specification, value can be:
• CLOUD_ONLY transparently encrypts the tablespace in the Cloud using the tablespace encryption default algorithm if you do not specify the ENCRYPTION clause of the CREATE TABLESPACE SQL statement. It applies only to an Oracle Cloud environment. If you create the tablespace on-premises, then it will follow the CREATE TABLESPACE statement specification that you enter. For example, if you omit the ENCRYPTION clause, then the tablespace is created unencrypted. If you include this clause and use a different algorithm, then the tablespace will use that algorithm. CLOUD_ONLY is the default.
• ALWAYS automatically encrypts the tablespace using the tablespace encryption default algorithm if you omit the ENCRYPTION clause of CREATE TABLESPACE, for both the Cloud and premises scenarios.
• If you do provide the ENCRYPTION clause, however, the algorithm that you specify takes precedence over the tablespace encryption default algorithm.
• DDL encrypts the tablespace using the specified setting of the ENCRYPTION clause of CREATE TABLESPACE, for both Oracle Cloud and on-premises environments.

ENCRYPT_NEW_TABLESPACES parameter specifies whether the new tablespaces to be created should be implicitly encrypted. The default algorithm is AES128.

We can set default TDE encryption algorithm (Only for 19c databases) by using an ‘_’ parameter:

SQL> alter system set "_tablespace_encryption_default_algorithm" = 'AES256' scope = both;

Note: these parameters should be set for all standby instances as well

This determines the encryption algorithm used on new tablespaces after setting:

 alter system set encrypt_new_tablespaces = ALWAYS scope = both;

as well as the encryption algorithm for the SYSTEM tablespace:

 alter tablespace SYSTEM encryption ONLINE encrypt;

Note: This parameter needs to be set *before* creating a TDE wallet, or *before* the first ‘set key’ operation when Oracle Key Vault is used, in order to be effective for the SYSTEM tablespace.

Encrypting existing Tablespaces

Say you have a Tablespace which was not encrypted when it was created and now has some data in it and we need to encrypt it using the TDE master key. We can use the below methods.

Encryption operation requires at least the same amount of space as the largest data file in the tablespace you are encrypting.

Turn off the transport and apply (if standby exists)

If you have a standby for this primary database, turn off the redo log transport and apply

#on Primary DB
dgmgrl /
edit database "UATDB_STDY" set state='apply-off';
edit database "UATDB_PRIM" set state='transport-off';

Offline Method

Shutdown the application that is using this database

Make sure the wallet is open and has autologin enabled on both nodes (on primary and standby) and has the same master keys on both sides.

Perform below on the Primary

#here USERS is the tablespace name
sqlplus "/as sysdba"
select con_id, wallet_type,wrl_parameter, status from v$encryption_wallet;
alter tablespace USERS offline normal;
ALTER TABLESPACE USERS ENCRYPTION offline USING 'AES256' ENCRYPT;
alter tablespace USERS online normal;

Gather information again to see if the Tablespace is encrypted now.

Online Method

Perform below on the Primary

#here online_test is the tablespace name
sqlplus "/as sysdba"
select con_id, wallet_type,wrl_parameter, status from v$encryption_wallet;
ALTER TABLESPACE USERS ENCRYPTION online USING 'AES256' ENCRYPT;

Enable TDE in Standby (if Standby Exists)

Copy the wallet files ewallet.p12, cwallet.sso from primary DB (/u01/app/oracle/admin/${DB_UNIQUE_NAME}/wallet/tde) to standby DB (/u01/app/oracle/admin/${DB_UNIQUE_NAME}/wallet/tde).

Check config On Standby

sqlplus "/as sysdba"

set linesize 250 pagesize 250
column name format a40
column masterkeyid_base64 format a60
select  name,utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64  FROM (select t.name, RAWTOHEX(x.mkid) mkeyid from v$tablespace t, x$kcbtek x where t.ts#=x.ts#);
select con_id, wallet_type,wrl_parameter, status from v$encryption_wallet;
select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);
exit

orapki wallet display -wallet /u01/app/oracle/admin/${DB_UNIQUE_NAME}/wallet/tde

Execute to enable TDE on Standby (if standby exists)

sqlplus "/as sysdba"

set linesize 250 pagesize 250
column name format a40

select con_id, wallet_type,wrl_parameter, status from v$encryption_wallet;

ALTER TABLESPACE USERS ENCRYPTION offline USING 'AES256' ENCRYPT;
exit

orapki wallet display -wallet /u01/app/oracle/admin/${DB_UNIQUE_NAME}/wallet/tde

Or Rebuild the standby.

Re-enable log sync

# On Primary

dgmgrl /
edit database "UATDB_STDY" set state='apply-on';
edit database "UATDB_PRIM" set state='transport-on';

Rekey Operation (rotating master key)

On Primary Node

• Turn off the transport and apply (if standby exists)

dgmgrl /
edit database "UATDB_STDY" set state='apply-off';
edit database "UATDB_PRIM" set state='transport-off';

• Rotate the master key

sqlplus "/as sysdba"
----Perform the key master key rotation using the administer key command----
ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY FORCE KEYSTORE IDENTIFIED BY <wallet_password> WITH BACKUP;
administer key management set keystore close identified by <wallet_password>;

column name format a40
column masterkeyid_base64 format a60
select  name,utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64  
FROM (select t.name, RAWTOHEX(x.mkid) mkeyid from v$tablespace t, x$kcbtek x where t.ts#=x.ts#);

• Check the new key

#/u01/app/oracle/admin/${DB_UNIQUE_NAME}/wallet/tde is the tde wallet location and wallet is autologin
orapki wallet display -wallet /u01/app/oracle/admin/${DB_UNIQUE_NAME}/wallet/tde

On Standby Node (if standby exists)

1. Take file backup of wallet files ewallet.p12 and cwallet.sso in standby DB.
2. Copy (overwrite) the wallet files ewallet.p12, cwallet.sso from primary DB to standby DB.
3. Shutdown Standby

sqlplus "/as sysdba"
shut immediate

4. Startup Standby

sqlplus "/as sysdba"
startup mount

Re-enable log sync

# On Primary
dgmgrl /
edit database "UATDB_STDY" set state='apply-on';
edit database "UATDB_PRIM" set state='transport-on';

Rekey On RAC

Keystore operations (such as opening or closing the keystore, or rekeying the TDE master encryption key) can be issued on any one Oracle RAC instance. Internally, the Oracle database takes care of synchronizing the keystore context on each Oracle RAC node, so that the effect of the keystore operation is visible to all of the other Oracle RAC instances in the cluster. Similarly, when a TDE master encryption key rekey operation takes place, the new key becomes available to each of the Oracle RAC instances. You can perform other keystore operations, such as exporting TDE master encryption keys, rotating the keystore password, merging keystores, or backing up keystores, from a single instance only.

Restrictions on Using Transparent Data Encryption Tablespace Encryption

You should be aware of restrictions on using Transparent Data Encryption when you encrypt a tablespace.

Note the following restrictions:

• Transparent Data Encryption (TDE) tablespace encryption encrypts or decrypts data during read and write operations, as opposed to TDE column encryption, which encrypts and decrypts data at the SQL layer. This means that most restrictions that apply to TDE column encryption, such as data type restrictions and index type restrictions, do not apply to TDE tablespace encryption.
• To perform import and export operations, use Oracle Data Pump.

Backups of Wallet

TDE wallet should be backed up once daily, and the wallet backup should be pushed to the secure storage account/bucket for the respective instance. TDE wallet should also be backed up once weekly along with Full File system backup.

Impact on Cloning and Patching

Cloning

• RMAN

If we are doing a clone using cold backup or using RMAN backup, we have to make sure that the wallet is copied from the source env to the target and that parameters are configured properly on the target env.

Once the DB is restored please make sure to rekey the wallet on the target side and delete the older master keys

• Expdp/Impdp

If you are using export/import for cloning data, you don’t need to worry about it. Data Pump can either export it encrypted or unencrypted, it is up to your expdp parameters. If you don’t specify an encryption_password, then the data is exported unencrypted (you may get a warning about this, but it will keep going). If you specify an encryption_password for expdp, then the data is now encrypted using this new password,

To import, simply import the dumpfile. If you specified an encryption_password on the expdp command, you need the same password on the impdp command. If you import this data into an encrypted tablespace, it will be encrypted, if you import into an unencrypted tablespace, then the data will be unencrypted. Make sure to delete the dump files from the servers after the clone is done.

Patching

There is no impact of TDE on patching.

Oracle TDE in Cloud PaaS

The above guide is true for on-prem environments. The Major cloud providers that provide Oracle DB as Service are Oracle (OCI) and AWS.

Some of the steps defined before won’t be required for the Databases in the cloud (PaaS DB service). Please review the Cloud provider’s documentation for that. For any Oracle instance running in a VM managed (Azure, OCI, or AWS) by you, the above steps are still valid.

AWS

Amazon RDS supports Oracle Transparent Data Encryption (TDE), a feature of the Oracle Advanced Security option available in Oracle Enterprise Edition. The TDE option is a permanent option that can’t be removed from an option group. You can’t disable TDE from a DB instance once that instance is associated with an option group with the Oracle TDE option. You can change the option group of a DB instance that is using the TDE option, but the option group associated with the DB instance must include the TDE option. You can also modify an option group that includes the TDE option by adding or removing other options.

Amazon RDS manages the Oracle Wallet and TDE master key for the DB instance. You do not need to set the encryption key using the command ALTER SYSTEM set encryption key.

Oracle Transparent Data Encryption

OCI

Oracle Database Cloud Service (DBCS) uses Oracle Transparent Data Encryption (TDE) to protect data at rest for its databases. Database Cloud Service (DBCS) integrates with the OCI Vault service. CMEK (customer-managed encryption keys) are supported for TDE encryption. Users have the option to continue keeping the TDE master encryption keys in Oracle-managed file-based encryption on the DB System or use the OCI vault service to store and manage the master encryption keys. The OCI Vault keys used for protecting databases are stored in a highly available, durable, and managed service.

OCI Vault Integration with Oracle Database Cloud Service

In the Nutshell

• The TDE wallet should have the same keys on all related nodes i.e. if we have a standby it should have the same wallet as Primary. If we have a DR node (in a different region) that should also have the same TDE wallet as of Primary.
• Be extra cautious when enabling TDE in RAC. As my mentor mentions it “RAC with TDE enabled is like a monkey with grenade. Dangerous and unpredictable”. Thats because of historic bugs related with RAC having TDE enabled.
• Use separate key stores/wallets for each environment.
• Don’t delete the TDE wallet unless you have already decrypted the instance and do not want to use TDE.
• Save your wallet password in a key vault.
• The actual performance impact on applications can vary.
• Keep wallets for TDE encryption keys and TLS certificates separate for easier management.

TDE column encryption versus TDE tablespace encryption

Oracle's recommendation is to use TDE tablespace encryption. TDE tablespace encryption has better, more consistent performance characteristics in most cases. Moreover, tablespace encryption in particular leverages hardware-based crypto acceleration where it is available, minimizing the performance impact even further to the ‘near-zero’ range.

Can you enable column encryption on a table in an already encrypted tablespace?

Yes, a hybrid setup is sometimes used. Please feel free to comment and share the scenarios in which that is used.

References

Transparent Data Encryption Frequently Asked Questions

Multitenant : Transparent Data Encryption (TDE) in Pluggable Databases (PDBs) in Oracle Database 12c…

Please read my other articles as well and share your feedback. If you like the content shared please like, comment, and subscribe for new articles.