TDE – Transparent Data Encryption – is the Oracle solution for protecting data at rest. This refers, protecting data that is stored in one file or one disk. This data will be encrypted. Only after the database is open and the wallet password is given, you can query the data and see it.
Recently I’ve been working more with ExaCC and migrating databases from on-premises to the Cloud. Took quite some days to get into TDE and sometimes confusing terms used in the documentation and on the web.
This post summarizes all the concepts to have in mind when working with Oracle encrypted databases, which is the default in the Cloud and Exadata systems.
Warning
Having TDE in place, requires good management of encryption keys and backup of wallets. If the key is lost, the access to the data is lost.
Wallet / Keystore
Wallet is the container – usually a file – where the keys are stored. It can also be called keystore. The wallet is protected by a password that allows to read and write into it.
Alternative to a wallet file, can be a Oracle Key Vault or OCI Vault or external service via OCI KMS (Key Management Service).
The wallet file name looks like ewallet.p12
Auto-login wallet
Auto-login wallet is another file, which can open the Wallet in read-only mode without password.
The auto-login wallet file name looks like cwallet.sso
It can be LOCAL, which means it will work only in the current host. This increases security. On VM Cluster / RAC implementations, LOCAL is not used, as the wallet is shared among several hosts.
Note that to use SET KEY or IMPORT KEY commands, you need to first close the Auto-login wallet and open the Wallet with password. Or you can use the command with FORCE KEYSTORE IDENTIFIED BY "wallet_password" WITH BACKUP
Masterkey
Masterkey is the key that encrypt the data. It is auto-generated by the SET KEY or CREATE KEY sub-commands.
There is one masterkey per container: one for CDB$ROOT and one for each PDB. The masterkey as a ‘key_id’ which is unique.
A masterkey can also have user defined ‘tag’ to help identification.
When opening a wallet, all stored master keys become available to decrypt the data.
United and Isolated modes
United mode means a unique wallet for the whole CDB. Advantages are that there is only one wallet to take care of. But requires exporting/importing keys for moving one unique PDB around.
Isolated mode means that each PDB has its own wallet. This wallet is created in a directory <WALLET_ROOT>/pdb_guid/tde . It is good to use isolated mode when there is a big split between infrastructure DBA and application DBA – wallet passwords can be different; or if one PDB masterkey is stored in a different type of vault.
OCI does not support Isolated mode (using cloud tooling).
Database parameters
| WALLET_ROOT | Static parameter. Parent directory for the wallets of the CDB. Wallet will be in <WALLET_ROOT>/tde when using united mode.In OCI it is in ACFS, usually /var/opt/oracle/dbaas_acfs/<DB_NAME>/wallet_rootSuggestion for single instance is $ORACLE_BASE/admin/<DB_UNIQUE_NAME>/wallet |
| TABLESPACE_ENCRYPTION | Static parameter. Should be set to AUTO_ENABLE (default in OCI) to activate TDE locally. Can be set to DECRYPT_ONLY when using Hybrid Dataguard. ENCRYPT_NEW_TABLESPACES parameter not needed anymore (19.16+). |
| TDE_CONFIGURATION | Dynamic parameter. Set to KEYSTORE_CONFIGURATION=FILE when using a file based wallet. Otherwise, KEYSTORE_CONFIGURATION=OKV for Oracle key vault. |
Database views
V$ENCRYPTION_WALLET shows the wallet status information for each container. Typical query:
select CON_ID, WRL_TYPE, WALLET_TYPE, KEYSTORE_MODE, STATUS
from V$ENCRYPTION_WALLET;
When KEYSTORE_MODE is UNITED, means it uses the same file.
Do/cumentation 19c for V$ENCRYTION_WALLET (23ai)
V$ENCRYPTION_KEYS shows information about master keys available in the currently open wallet. It allows knowing which keys are missing, when moving a PDB to another CDB. Typical query:
select CON_ID, KEY_ID, TAG, CREATION_TIME
from V$ENCRYPTION_KEYS;
Documentation 19c for V$ENCRYPTION_KEYS (23ai)
V$DATABASE_KEY_INFO shows information about master keys in use for at least SYSTEM, UNDO and TEMP tablespaces, based on database controlfile. Typical query:
select CON_ID, MASTERKEYID, MASTERKEY_ACTIVATED
from V$DATABASE_KEY_INFO;
Documentation 19c for V$DATABASE_KEY_INFO (23ai)
It is possible to join V$ENCRYPTION_KEYS and V$DATABASE_KEY_INFO using:
select V$DATABASE_KEY_INFO.con_id, key_id, masterkeyid, masterkey_activated, creation_time
from V$ENCRYPTION_KEYS
left outer join V$DATABASE_KEY_INFO
on (replace(utl_raw.cast_to_varchar2(utl_encode.base64_encode('01' || MASTERKEYID)),'=','AAAAAAAAAAAAAAAAAAAAAAAAAAAAA') = key_id);
Export & import masterkeys
When unplugging a PDB, or during dataguard creation, you might need to copy some masterkeys from one wallet to another. To do that you use:
From CDB$ROOT:
Use queries above to get the key_id you need to export and then:ADMINISTER KEY MANAGEMENT EXPORT KEYS WITH SECRET "export_password" TO '/home/oracle/tdekeys.exp' FORCE KEYSTORE IDENTIFIED BY "wallet_password" WITH IDENTIFIER IN 'ADoxnJ0uH08cv7xkz83ovwsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA', 'ATz3CoyKE/yv3cNT5CWCXUAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
From PDB, you export all masterkeys:ALTER SESSION SET CONTAINER=<pdb_name>;
ADMINISTER KEY MANAGEMENT EXPORT KEYS WITH SECRET "export_password" TO '/home/oracle/tdekeys_pdb.exp' FORCE KEYSTORE IDENTIFIED BY "wallet_password;
Copy the file to the destination host and then proceed to the import:ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "export_password" FROM '/home/oracle/tdekeys.exp' FORCE KEYSTORE IDENTIFIED BY "wallet_password" WITH BACKUP;
Hybrid Dataguard
When migrating to the Cloud, you need to encrypt the data. However when you do not have Advanced Security Option on premises, you cannot do before the migration. With 19.16+ is possible to have Hybrid Dataguard, which allows one side of the dataguard setup to stay unencrypted. This also permits to reduce the migration downtime.
More information on the next post how to setup Hybrid Dataguard.