Oracle TDE part I : Configuring the Encryption

By Mouhamadou Diaw

Oracle gives us some tools to secure our data in transit and in rest.

Oracle Transparent Data Encryption will allow us to encrypt data in rest. Oracle TDE need The Oracle Advanced Security License

Oracle TDE allow to do encryption at 2 levels

-Tablespace Level

-Table column level

In this first part of this blog, we will see how we can set up the TDE in a multitenant database. Documentation can be found here

In a multitenant environment we have two modes for configuring TDE

-The united mode where the keystore is managed at CDB$ROOT.

-The isolated mode which allow PDBs to independently create and manage their own keystore. An isolated mode PDB can have its own keystore, independent of the keystore of the CDB root.

In this blog we are using united mode

–Configuring initialization related parameters

Create first a directory for to store the TDE master encryption key

oracle@svoradl02:~]$ mkdir -p /u01/app/oracle/admin/CDB19P/tde_wallet

oracle@svoradl02:/u01/app/oracle/admin/CDB19P/tde_wallet/ [CDB19P] ls -ld /u01/app/oracle/admin/CDB19P/tde_wallet
drwxr-xr-x. 2 oracle oinstall 6 Feb 22 09:33 /u01/app/oracle/admin/CDB19P/tde_wallet
oracle@svoradl02:/u01/app/oracle/admin/CDB19P/tde_wallet/ [CDB19P]

On cdb$root change the parameter wallet_root to point to this directory

SQL> select name from v$database;

NAME
---------
CDB19P

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>


SQL> alter system set wallet_root='/u01/app/oracle/admin/CDB19P/tde_wallet' scope=spfile;

System altered.

SQL>

Bounce the database

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

Total System Global Area 6442450048 bytes
Fixed Size                  9178240 bytes
Variable Size            1644167168 bytes
Database Buffers         4781506560 bytes
Redo Buffers                7598080 bytes
Database mounted.
Database opened.

Verify the value of the parameter wallet_root

SQL> show parameter wallet_root

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
wallet_root                          string      /u01/app/oracle/admin/CDB19P/
                                                 tde_wallet
SQL>

As we will use a file, let’s set the parameter TDE_CONFIGURATION to FILE

SQL> ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=both;

System altered.

SQL> show parameter tde_configuration

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
tde_configuration                    string      KEYSTORE_CONFIGURATION=FILE
SQL>

At this moment we can validate that the keystore_mode is UNITED for the PDBS

SQL> SELECT CON_ID, KEYSTORE_MODE FROM V$ENCRYPTION_WALLET;

    CON_ID KEYSTORE
---------- --------
         1 NONE
         2 UNITED
         3 UNITED
         5 UNITED

-Create the Software Keystore

The software keystore will be in the CDB root but the master keys from this keystore will be  available for the PDBs that have their keystore in united mode.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY secret1;

keystore altered.

SQL>

If there is no error a sub-directory tde will be created and will contain following file

SQL> host ls -l /u01/app/oracle/admin/CDB19P/tde_wallet/tde
total 4
-rw-------. 1 oracle oinstall 2555 Feb 22 09:49 ewallet.p12

SQL>

-Create a password-protected software keystore

Then create a password for the software keystore

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/CDB19P/tde_wallet'  IDENTIFIED BY secret1;

keystore altered.

SQL>

-Create the auto-login or local auto-login

The keystore should be opened every time the database is opened. To automatically open the keystore at database startup, we can create an auto-login file

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/CDB19P/tde_wallet/tde' IDENTIFIED BY secret1;

keystore altered.

A .sso file should exist now

SQL> host ls -l /u01/app/oracle/admin/CDB19P/tde_wallet/tde
total 8
-rw-------. 1 oracle oinstall 2600 Feb 22 09:53 cwallet.sso
-rw-------. 1 oracle oinstall 2555 Feb 22 09:49 ewallet.p12

SQL>

-Opening the Software Keystore

The software keystore should be open in cdb$root and in any concerned PDB

– on  cdb$root

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

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

keystore altered.

– on the PDB

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         5 PDB1CLONE                      MOUNTED
SQL>


SQL> alter session set container=PDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL>


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

keystore altered.

SQL>

-Setting the TDE Master Encryption Key

– On  both CDB$ROOT and PDB1 run following command

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>  administer key management set key FORCE KEYSTORE identified by secret1 with backup;

keystore altered.

SQL>

Validate that the master key is activated allowing us to use TDE

SQL> SELECT CON_ID,MASTERKEY_ACTIVATED FROM V$DATABASE_KEY_INFO;

Conclusion   

TDE is now activated. In the second part we will see how we can encrypt data