TDE (Transparent Data Encryption)

Implementing Transparent Data Encryption (TDE) on Oracle 12c Standalone Database

This document provides a step-by-step guide to implementing Transparent Data Encryption (TDE) on an Oracle 12c standalone database. TDE helps protect sensitive data at rest by encrypting datafiles.

Prerequisites

• Oracle Database 12c installed and running.

• Appropriate OS user (e.g., oracle) with permissions to create directories and modify Oracle configuration files.

• Familiarity with SQL*Plus and basic OS commands.

Implementation Steps

1. Create Wallet Directory

Create a dedicated directory on the file system to store the TDE wallet (keystore). This directory should have restricted permissions.

mkdir -p /test/WALLET

Note: Ensure the oracle OS user has appropriate read and write permissions to this directory.

2. Modify sqlnet.ora

Update the sqlnet.ora file to specify the location of the encryption wallet. This file is typically located in $ORACLE_HOME/network/admin/.

Add the following lines to sqlnet.ora:

ENCRYPTION_WALLET_LOCATION =
  (SOURCE = (METHOD = FILE)(METHOD_DATA =
    (DIRECTORY = /test/WALLET)))

Important: Ensure this sqlnet.ora file is in the correct network/admin directory accessible by your database instance. If you have a separate Grid Infrastructure Home (Grid_Home), you might need to copy sqlnet.ora from the Grid Home's network/admin to the database home's network/admin if it's managed externally, or ensure the path is consistent.

3. Create Keystore (Wallet)

In Oracle 12c, the standard and recommended way to create a TDE keystore is using the ADMINISTER KEY MANAGEMENT command from SQL*Plus.

Connect to SQL*Plus as SYSDBA:

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/test/WALLET/' IDENTIFIED BY "YOUR_WALLET_PASSWORD";

Replace "YOUR_WALLET_PASSWORD" with a strong password for your TDE wallet. Remember this password, as it's needed to open the wallet manually.

Verify File Creation: After executing the command, check the wallet directory. You should see an ewallet.p12 (or ewallet.p01 in older versions/configurations) file created.

ls -ltr /test/WALLET/

Example Output:

total 4
-rw------- 1 oracle asmadmin 2555 May 27 10:30 ewallet.p12

4. Open Keystore

Before you can activate keys or encrypt data, the keystore must be opened.

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "YOUR_WALLET_PASSWORD";

Note: If you copied sqlnet.ora from a Grid Home, ensure it's in the correct database network/admin path before attempting to open the keystore.

5. Activate the Master Encryption Key

Once the keystore is open, you can activate the master encryption key. This command generates a new master key and sets it as the active key for TDE operations.

First, check current key status (should show no keys initially):

SET LINESIZE 100
SELECT con_id, key_id FROM v$encryption_keys;

Example Output:

no rows selected

Now, activate the key:

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "YOUR_WALLET_PASSWORD" WITH BACKUP;

Example Output:

keystore altered.
```WITH BACKUP` option creates a backup of the current keystore before generating a new key, which is good practice.

Verify the new master key:
```sql
SELECT con_id, key_id FROM v$encryption_keys;

Example Output:

  CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
         0 TTTTTTTTTTTTTTTTGfeeeeeeeeeeeeeeee

The KEY_ID will be a unique identifier for your master encryption key.

6. Enable Autologin for the Keystore (Recommended)

By default, the wallet is password-protected. This means you would need to manually open the wallet with the password every time the database restarts. To avoid this, enable autologin.

First, check the current wallet status:

column WRL_PARAMETER format a30
column WRL_TYPE format a10
column STATUS format a10

SELECT * FROM v$encryption_wallet;

Example Output (before autologin):

WRL_TYPE   WRL_PARAMETER               STATUS     WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
---------- ------------------------------ ---------- -------------------- --------- --------- ----------
FILE       /test/WALLET/                  OPEN       PASSWORD             SINGLE    NO               0

Notice WALLET_TYPE is PASSWORD.

Now, enable autologin:

ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/test/WALLET/' IDENTIFIED BY "YOUR_WALLET_PASSWORD";

This command creates a cwallet.sso file in the wallet directory.

Restart the database to confirm the autologin functionality.

After restart, check the wallet status again:

ls -ltr /test/WALLET/

Example Output:

total 8
-rw------- 1 oracle asmadmin 2555 May 27 10:30 ewallet.p12
-rw------- 1 oracle asmadmin 2580 May 27 10:35 cwallet.sso

Now, query v$encryption_wallet:

SELECT * FROM v$encryption_wallet;

Example Output (after autologin):

WRL_TYPE   WRL_PARAMETER               STATUS     WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
---------- ------------------------------ ---------- -------------------- --------- --------- ----------
FILE       /test/WALLET/                  OPEN       AUTOLOGIN            SINGLE    NO               0

The WALLET_TYPE should now be AUTOLOGIN.

7. Create Encrypted Tablespace

Now that TDE is configured and the master key is active, you can create encrypted tablespaces. Any data stored in these tablespaces will be automatically encrypted by TDE.

CREATE TABLESPACE TDE_TESTDATA
  DATAFILE '+DATA_DG' SIZE 200M AUTOEXTEND ON NEXT 100M MAXSIZE 8192M
  LOGGING
  ONLINE
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
  BLOCKSIZE 8K
  SEGMENT SPACE MANAGEMENT AUTO
  ENCRYPTION USING 'AES256' -- Specify the encryption algorithm
  DEFAULT STORAGE(ENCRYPT)  -- Data in this tablespace will be encrypted by default
  FLASHBACK ON;

Replace +DATA_DG with your actual ASM disk group or file system path.

Once the encrypted tablespace is created, you can create tables or move existing tables and indexes into it as usual. Oracle will handle the encryption and decryption transparently.