Step-by-Step: Implementing Oracle Transparent Data Encryption (TDE)

🧱 Step 1: Set Initialization Parameters

Set the wallet root and TDE configuration in the database:

ALTER SYSTEM SET WALLET_ROOT = '/u01/app/oracle/wallet' SCOPE = SPFILE;
ALTER SYSTEM SET TDE_CONFIGURATION = 'KEYSTORE_CONFIGURATION=FILE' SCOPE = SPFILE;

Example on Windows Platform:
SQL> ALTER SYSTEM SET WALLET_ROOT = 'C:\test1\wallets' SCOPE = SPFILE;
System altered.
SQL> ALTER SYSTEM SET TDE_CONFIGURATION = 'KEYSTORE_CONFIGURATION=FILE' SCOPE = SPFILE;
System altered.

Restart the database to apply changes:

SHUTDOWN IMMEDIATE;
STARTUP;

📁 Step 2: Create the Keystore (Wallet)

Create the wallet directory:

mkdir -p /u01/app/oracle/wallet/tde

Example of Windows:
mkdir C:\test1\wallets\tde

Create the wallet using SQL:

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/wallet/tde' IDENTIFIED BY MyWalletPass;

Example:
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE 'C:\test1\wallets\tde' IDENTIFIED BY MyWalletPass;
keystore altered.

🔓 Step 3: Open the Keystore

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY MyWalletPass;

Example:
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY MyWalletPass;
keystore altered.

🔑 Step 4: Set the Master Encryption Key

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY MyWalletPass WITH BACKUP;

Example:
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY MyWalletPass WITH BACKUP;
keystore altered.

📦 Step 5: Create an Encrypted Tablespace

CREATE TABLESPACE secure_tbs 
DATAFILE '/u01/app/oracle/oradata/ORCL/secure_tbs01.dbf' SIZE 100M 
ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

Example:
SQL> CREATE TABLESPACE secure_tbs datafile 'C:\ORACLEDB\ORADATA\ORCL\secure_tbs01.dbf' size 100M ENCRYPTION  using 'AES256' Default storage(encrypt);

Tablespace created.

📋 Step 6: Create a Table in the Encrypted Tablespace

CREATE TABLE sensitive_data (
  id NUMBER,
  ssn VARCHAR2(11)
) TABLESPACE secure_tbs;

Example:
SQL> CREATE TABLE sensitive_data (  id NUMBER,  ssn VARCHAR2(11)) TABLESPACE secure_tbs;
Table created.

🔍 Step 7: Verify Encryption

Check wallet status:

SELECT * FROM V$ENCRYPTION_WALLET;

SQL> column  wrl_parameter for a25
SQL> /

WRL_TYPE             WRL_PARAMETER             STATUS     WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC     CON_ID
-------------------- ------------------------- ---------- ----------- --------- -------- --------- ----------
FILE                 C:\TEST1\WALLETS\tde\     OPEN       PASSWORD    SINGLE    NONE     NO                 1
FILE                                           CLOSED     UNKNOWN     SINGLE    UNITED   UNDEFINED          2
FILE                                           CLOSED     UNKNOWN     SINGLE    UNITED   UNDEFINED          3

Check encryption keys:

SELECT * FROM V$ENCRYPTION_KEYS;

This setup ensures that data stored in the encrypted tablespace is protected at rest. You can also encrypt individual columns if needed.

Verify the table by closing the wallet

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY MyWalletPass;

keystore altered.

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