
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:
1 | $ 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
1 | mkdir -p /opt/oracle/oradata/wallets |
Login to the database as SYSDBA
:
1 | $ . oraenv |
Check that WALLET_ROOT
hasn’t already been set:
1 | SQL> show parameter wallet_root |
Set the wallet_root
location, then restart the database:
1 | SQL> alter system set wallet_root='/opt/oracle/oradata/wallets' scope=spfile; |
Check that the TDE_CONFIGURATION
hasn’t already been set:
1 | SQL> show parameter tde_configuration; |
Set the TDE_CONFIGURATION
:
1 | SQL> alter system set tde_configuration="keystore_configuration=file" scope=both; |
Create a wallet in /opt/oracle/oradata/wallets/tde
:
1 | SQL> administer key management create keystore identified by "superSecret111"; |
Open the keystore for CDB$ROOT
and XEPDB1
containers:
1 | SQL> administer key management set keystore open identified by "superSecret111" container = all; |
Set the master key for CDB$ROOT
and XEPDB1
containers:
1 | SQL> administer key management set key identified by "superSecret111" with backup container = all; |
Check that the keystores (except the seed container) are all open and master keys set:
1 | SQL> column name format a30 |
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:
1 | create tablespace secure_ts |
Login to the PDB as the user secure_user
and create simple database objects and insert some records, for example:
1 | create table sample(id number); |
Restart the database, reconnect as secure_user
and query the table again.
1 | SQL> alter session set container = xepdb1; |
A quick check on the wallet status should explain why:
1 | SQL> alter session set container = cdb$root; |
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:
1 | SQL> administer key management create auto_login keystore from keystore '/opt/oracle/oradata/wallets/tde' identified by "superSecret111"; |
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 thelocal
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.
- One of them is about keeping the keys safe.In this post, the keystore was made to
- 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.
1 | SQL> alter system set tde_configuration="keystore_configuration=file" scope=both; |
- 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!