Using Oracle TDE to Create an Encrypted Tablespace

...and how to set up software keystore for a Real Application Clusters (RAC) database that’s using Automatic Storage Management (ASM) and then create an encrypted tablespace.

Please note: This requires minimum database version 11.2.0.1, Enterprise Edition. The following steps were completed on a 12.1.0.2 Enterprise Edition RAC multitenant database. TDE requires Oracle Advanced Security, which is an extra-cost license.

What is Oracle Transparent Data Encryption (TDE)?  

Oracle TDE allows administrators to encrypt sensitive data (i.e. Personally Identifiable Information or PII) by protecting it from unauthorized access via encryption key if storage media, backups, or datafiles are stolen.

What can you encrypt with TDE?  

You can encrypt tablespaces and/or table columns. One reason to encrypt a tablespace is that it allows for index range scans while column encryption does not. Additionally, all data stored in the tablespace are encrypted, precluding administrators from having to identify all columns requiring encryption. Tablespace encryption also allows for bulk encryption and caching performance.

Additional detail for tablespace encryption vs. column encryption can be found on Oracle’s website.

Using Keystores

There are 2 types of keystores: 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

Configure Keystores

Now we will configure software keystore on a RAC database with ASM disk and create an encrypted tablespace. (Maybe it goes without saying, but please note: this is demonstration/informational only. Use at your own risk, no warranty implied. And ALWAYS TEST IN TEST/DEVELOPMENT ENVIRONMENT!)

1) Backup the sqlnet.ora file.

2) Update the sqlnet.ora file with the location of the wallet.

3) Open sqlnet.ora file and add the following lines.

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/ACFS/oracle/wallet/$ORACLE_UNQNAME/)))

Update DIRECTORY= your path. Oracle best practice is to create the wallet on your ASM Cluster Files system (ACFS) that is shared across all nodes in the cluster. The $ORACLE_UNQNAME should be associated with an ORACLE_SID with each database having its own directory path so that each wallet can be opened/closed with each database without impacting other databases.

4)  Save your changes and close sqlnet.ora.

5)  Log in to sqlplus into the (container CDB$ROOT) database as a user that has administer key management or syskm privilege.

• ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/ACFS/oracle/wallet/$ORACLE_UNQNAME/' IDENTIFIED BY “password”;
  • The double quotes (“) are needed around the password if you use special characters.
  • Update /ACFS/oracle/wallet to your chosen wallet location.
• Perform the following only if you want to create auto_login keystore.
• ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/ACFS/oracle/wallet/$ORACLE_UNQNAME/' IDENTIFIED BY “password”;
• Exit from sqlplus
• If you used ACFS disk to store your wallet you can now type ls -l on /ACFS/oracle/wallet/$ORACLE_UNQNAME/ to verify ewallet.p12 and cwallet.sso exist (cwallet.sso will only exist if you performed the auto login step above).  cwallet.sso is your auto login file and ewallet.p12 is the password based wallet.
• Perform the following step while connected to sqlplus if you skipped the step to create auto_login.
• ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "password" CONTAINER = ALL;
  • Use CONTAINER=ALL to OPEN the keystore in all pluggable databases (pdbs) in the container database (cdb) in a multitenant environment.
• Next set the master encryption key.  If multitenant environment, this will need to be performed on each pdb you are enabling encryption in while connected through sqlplus.
• ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY “password” WITH BACKUP;

Be sure to review Oracle documentation and your system requirements before modifying the COMPATIBLE parameter.

Before you can create an encrypted tablespace, per oracle documentation, COMPATIBLE initialization parameter must be set to a minimum of 11.1.0.0 (Your database version at a minimum must be 11.1.0.x to set the COMPATIBLE parameter to 11.1.0.0). However, to use available TDE features set COMPATIBLE to 11.2.0.0 as a minimum.

Please keep in mind with 12.2, you can alter a tablespace or table to use TDE online, meaning no more downtime for maintenance. Your compatibility settings will need to be set appropriately to take advantage of these capabilities.

Also be aware: if COMPATIBLE is set to 11.1.0.0 and you set COMPATIBLE TO 11.2.0.0, this is irreversible after restarting the database. 

If COMPATIBLE is already 11.2.0.0 or higher, you will have use of all tablespace encryption features besides online conversion of tables/tablespaces.

SHOW PARAMETER COMPATIBLE  -- To verify correct compatible setting

Create an encrypted tablespace.

CREATE TABLESPACE ENCRYPTED1
DATAFILE '/ACFS' SIZE 100M
        ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT);

Verify the tablespace is created with encryption:

SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES WHERE TABLESPACE_NAME=’ENCRYPTED1’;

You have enabled TDE and created an encrypted tablespace. You may now begin moving tables or creating tables/indexes in the encrypted tablespace, as need be.

Interested in learning more? I'd suggest the following resources:

• Oracle Advanced Security Transparent Data Encryption FAQs
• Database Upgrade Guide

Hope both the advice and warnings were helpful! If I can expand on this in future blog posts, feel free to send suggestions for questions and issues you're addressing.