Transparent Data Encryption (TDE) is a solution to encrypt data so that only an authorized user can read it. One of the best practices to protect sensitive data such as credit card or SSN info is to use encryption, especially if the data resides in a potentially unprotected environment. Just like encryption, managing the decryption process is highly important as well. To prevent unauthorized decryption, TDE stores the encryption keys in a security module external to the database, called a keystore. In this blog post, we are going to explore how to configure TDE and manage different keystore configurations in a Multitenant environment.

Configuring the Keystore

Step 1: Configure the Software Keystore Location

TDE configuration requires a one-time setup using the WALLET_ROOT and TDE_CONFIGURATION parameters so that Oracle Database can retrieve the keystore. We need to:

  • Make sure we have a wallet directory (e.g. $ORACLE_BASE/admin/cdb1/wallet).
  • Add an entry in init.ora so that WALLET_ROOT points to the wallet directory:
WALLET_ROOT=$ORACLE_BASE/admin/cdb1/wallet
  • Set the TDE_CONFIGURATION parameter in CDB$Root:
SQL> ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE=BOTH;

System altered.

Step 2: Create the Software Keystore

Creating a Password-Protected Software Keystore

Creating a password-protected keystore is as easy as executing the following statement:

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY oracle19;

keystore altered.

Creating an Auto-Login Software Keystore

Another option for software keystores is the auto-login keystore, which has a system generated password. An auto-login keystore is created from a password-protected keystore. In other words, after creating a password-protected keystore as above, all we have to do is to run the following statement to create an auto-login keystore: 

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE
  2  FROM KEYSTORE '/u01/app/oracle/admin/cdb1/wallet/tde'
  3  IDENTIFIED BY oracle19;

keystore altered.

Step 3: Open the Keystore

We can open the keystore as follows:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle19;

keystore altered.

Let’s check the status of the keystore:

SQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;
STATUS
------------------------------
OPEN_NO_MASTER_KEY

Since we haven’t set the master encryption key yet, the status is OPEN_NO_MASTER_KEY instead of just OPEN.

Step 4: Set the TDE Master Encryption Key

We can set the master encryption key by executing the following statement:

SQL> ADMINISTER KEY MANAGEMENT SET KEY
  2  IDENTIFIED BY oracle19
  3  WITH BACKUP USING 'cdb1_key_backup';

keystore altered.

Let’s check the status of the keystore one more time:

SQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;
STATUS
------------------------------
OPEN

Now that we set the master encryption key, the status is now OPEN.

Step 5: Encrypt Your Data

We can now start encrypting our data. In order to show how our encrypted data is not accessible when the keystore is closed, I disabled the auto login keystore and just used password-protected keystore.

Let’s connect to PDB1 and create an encrypted table:

SQL> connect admin/oracle19@//localhost/pdb1

Connected.

SQL> create table t1 (c1 number encrypt);

Table created.

SQL> insert into t1 values (1234);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;
    C1
----------
      1234

Now, let’s close the wallet and try to run the same SELECT statement:

SQL> administer key management set keystore close identified by oracle19 container=all;

keystore altered.

SQL> select con_id, status from V$ENCRYPTION_WALLET;

    CON_ID STATUS
---------- ------------------------------
      1 CLOSED
      2 CLOSED
      3 CLOSED


SQL> connect admin/oracle19@//localhost/pdb1

Connected.

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