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:
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