Protect Your Data with Transparent Data Encryption

Photo by TheDigitalArtist

Transparent Data Encryption (TDE) is a powerful database feature that allows developers and administrators to very quickly, persist data encrypted at-rest. Applications do not require explicit coding to encrypt data for storage. TDE manages encryption during storage, and decryption when data is read, transparently and automatically.

TDE can also be used during backups. In the typical “tape falls off the back of the truck“ scenario, malicious actors would not be able to decipher the data on the lost tapes without the crucial encryption key stored in the Oracle Wallet. Assuming of course, the wallet was not backed up to the same tape.

This enterprise-grade feature is found in the Oracle Advanced Security Database Option. That requires user to be licensed for database enterprise edition. However, with Oracle Database 18c Express Edition (18cXE), Oracle has made it free and accessible by everyone!

This post was specially written for Oracle Application Express (APEX) developers who are new to this technology. It provides a simple workflow for creating an encrypted tablespaces. Since APEX application live on the database, placing the parsing schema on a TDE-enabled tablespace, automatically protects the entire application. Hopefully this will encourage you to use it for protecting Personally Identifiable Information (PII) or any other sensitive and confidential data.

If you are trying out 18cXE using the Docker image Martin D’Souza and I worked on, access the database container using the following command:

$ docker exec -it -u oracle oracledb bash

Typically, the wallets would be located (WALLET_ROOT) in $ORACLE_BASE/admin/$ORACLE_SID/wallet. However, when running the DB as a Docker container, developers are encouraged to map /opt/oracle/oradata to a directory on the host machine. That way, the container can be destroyed without losing your data files, configuration files and now Oracle Wallet(s).

As the oracle user, create the directory /opt/oracle/oradata/wallets

mkdir -p /opt/oracle/oradata/wallets

Login to the database as SYSDBA:

$ . oraenv
ORACLE_SID = [oracle] ? XE
The Oracle base has been set to /opt/oracle
$ sqlplus / as sysdba

Check that WALLET_ROOT hasn’t already been set:

SQL> show parameter wallet_root

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
wallet_root			     string

Set the wallet_root location, then restart the database:

SQL> alter system set wallet_root='/opt/oracle/oradata/wallets' scope=spfile;

System altered.

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

Total System Global Area 1610609928 bytes
Fixed Size		    8897800 bytes
Variable Size		  805306368 bytes
Database Buffers	  788529152 bytes
Redo Buffers		    7876608 bytes
Database mounted.
Database opened.

Check that the TDE_CONFIGURATION hasn’t already been set:

SQL> show parameter tde_configuration;

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

Set the TDE_CONFIGURATION:

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

System altered.

Create a wallet in /opt/oracle/oradata/wallets/tde:

SQL> administer key management create keystore identified by "superSecret111";

keystore altered.

Open the keystore for CDB$ROOT and XEPDB1 containers:

SQL> administer key management set keystore open identified by "superSecret111" container = all;

keystore altered.

Set the master key for CDB$ROOT and XEPDB1 containers:

SQL> administer key management set key identified by "superSecret111" with backup container = all;

keystore altered.

Check that the keystores (except the seed container) are all open and master keys set:

SQL> column name format a30
SQL> select c.name, w.status
  2  from v$encryption_wallet w, v$containers c
  3  where c.con_id = w.con_id;

NAME			       STATUS
------------------------------ ------------------------------
CDB$ROOT		       OPEN
PDB$SEED		       CLOSED
XEPDB1			       OPEN

In the PDB, create a tablespace, a user with the encrypted tablespace as default and grant the necessary roles. Supported encryption algorithms are listed here. Below is a sample script for creating an encrypted tablespace:

create tablespace secure_ts
  datafile '/opt/oracle/oradata/XE/XEPDB1/secure_ts01.dbf'
  size 10M autoextend on next 5M
  encryption using 'AES256' encrypt
;

create user secure_user identified by secure_user
  default tablespace secure_ts
  quota unlimited on secure_ts
;

grant connect to secure_user;
grant resource to secure_user;

Login to the PDB as the user secure_user and create simple database objects and insert some records, for example:

create table sample(id number);

insert into sample values (1);

commit;

select * from sample;

Restart the database, reconnect as secure_user and query the table again.

SQL> alter session set container = xepdb1;

Session altered.

SQL> select * from secure_user.sample;
select * from secure_user.sample
                          *
ERROR at line 1:
ORA-28365: wallet is not open

A quick check on the wallet status should explain why:

SQL> alter session set container = cdb$root;

Session altered.

SQL> select c.name, w.status
  2  from v$encryption_wallet w, v$containers c
  3  where c.con_id = w.con_id;

NAME			       STATUS
------------------------------ ------------------------------
CDB$ROOT		       CLOSED
PDB$SEED		       CLOSED
XEPDB1			       CLOSED

The wallet is no longer open after the database restarted. We will need to make this happen automatically. We do that by converting the keystore into an auto_login keystore:

SQL> administer key management create auto_login keystore from keystore '/opt/oracle/oradata/wallets/tde' identified by "superSecret111";

keystore altered;

IMPORTANT NOTES

• There are many security considerations that require thought before roling out TDE.
  • One of them is about keeping the keys safe.In this post, the keystore was made to auto_login, which really isn’t as safe as insisting, with the local keyword, that the wallet only opens on one host. I did not use this in the example as it is intended to work with Docker. Making the wallet set to allow automatic wallet opening could cause data loss if the container is dropped then restored.
  • Hardware-based key management systems are preferred and safer. They should be considered if budget and resource permits.
• The keystore and TDE master encryption keys are managed in United Mode. I had intended to use the Isolated Mode, but apparently, that might not be supported in XE.

SQL> alter system set tde_configuration="keystore_configuration=file" scope=both;
alter system set tde_configuration="keystore_configuration=file" scope=both
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-12754: Feature 'Per-PDB TDE keystore' is disabled due to missing capability
'Runtime Environment'.

• Last but not least, keep your wallets saved/safe in a separate secure location. DO NOT loose them. Failing to protect them will cause irreversible, catastrophic loss of data.

Enjoy Oracle Advanced Security!