How to enable Transparent Data Encryption (TDE) in Oracle database

TDE is an encryption mechanism present in Oracle database used to encrypt the data stored in a table column or tablespace. It protects the data stored on database files (DBF) by doing an encryption in case the file is stolen or hacked.

TDE supports any of the following encryption algorithms to encrypt the data. 

• 3DES168, AES128, AES192, AES256

TDE uses an encryption key to encrypt or decrypt the data. The encryption key is saved in a  Keystore which is saved external to the database.For example, the Keystore can be saved in a file. 

TDE supports two levels of encryption

• Columns Level Encryption: Encrypt the table column data
• Tablespace Level Encryption: Encrypt all the data in a tablespace

TDE supports SALT, a random data added to the value before the encryption happens. It strengthens the encryption.

In this post, I explained how to setup a password-based Transparent Data Encryption (TDE)  in Oracle database. The following are the series of steps required to complete the setup. 

1. Set a KeyStore local in SQLNET.ORA
2. Create a Password based KeyStore 
3. Open the KeyStore 
4. Set Master Encryption Key 
5. Encrypt your data 

Let’s begin to set up the Transparent Data Encryption

Pre-Requisite

Oracle Database 11.2.0 or greater installed

Step 1: Set KEYSTORE location in SQLNET.ORA

1.1) Go to ORACLE_HOME\NETWORK\ADMIN and open the SQLNET.ora in a notepad file C:\app\oracledb\product\12.1.0\dbhome_1\NETWORK\ADMIN\Sqlnet.ora

1.2) Create a directory anywhere on the database server  cd C:\app\oracledb\product\12.1.0\dbhome_1\ mkdir wallets

1.3) Add the below content in SQLNET.ORA file. Here we are defining a directory location for the KEYSTORE. Replace the directory path in the DIRECTORY parameter with the one you created.  ENCRYPTION_WALLET_LOCATION= (SOURCE= (METHOD=FILE) (METHOD_DATA= (DIRECTORY=C:\app\oracledb\product\12.1.0\dbhome_1\wallets)))

STEP 2: Create a Password-based KeyStore

2.1) Login as SYSDBA or a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege sqlplus sys/password as sysdba

2.2) Create a KEYSTORE ADMINISTER KEY MANAGEMENT CREATE KEYSTORE ‘C:\app\oracledb\product\12.1.0\dbhome_1\wallets’ IDENTIFIED BY passwod123;

• Replace <‘C:\app\oracledb\product\12.1.0\dbhome_1\wallets’> with the directory mentioned in SQLNET.ORA
• Replace <password123> with your password

Step 3: Open the KEYSTORE

3.1) Login as sysdba and Open the KEYSTORE ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY password123;

• Replace ‘Password123’ with the one you set in Step2

Step 4: Set Master Encryption Key

4.1) Set the Master Encryption Key using SET KEY clause ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY password123 WITH BACKUP USING ’emp_key_backup’;

• Replace ‘Password123’ with the one you set in Step2
• Replace ”emp_key_backup’ with your own description

Step5: Encrypt your Data

You can encrypt individual columns in a table or the entire tablespace. The Following algorithms are supported (3DES168, AES128, AES192, AES256)

5.1 Encrypt a Columns in Table

5.1.1) Create a table with an encrypted column.  create table job(title varchar2(128) encrypt);

Where:

The keyword encrypt tells the database toencrypts data that are stored in column ‘Title’. By default, It uses the encryption algorithm AES192. SAC and MAC are added by default

5.1.2) Create a column with an encryption algorithm and NO SALT. The Using keyword is used to set an algorithm.  create table emp(name varchar2(128) encrypt using ‘3DES168’, age NUMBER ENCRYPT NO SALT);

5.1.3) Encrypt an existing table column alter table employees modify (first_name ENCRYPT NO SALT);

(or) Alter table employees add (new_name varchar(40) ENCRYPT)

5.1.4) Change the Encryption key of an existing column alter table employees rekey using ‘3DES168’;

5.2) Encrypt Tablespace

You need to set the COMPATIBLE parameter greater than 11.1.2 before encrypting the tablespace. 

5.2.1) Set the COMPATIBLE initialization parameter

First, check the COMPATIBLE value SQLPLUS / as SYSDBA show parameter compatible;

If the compatible value is less than 11.2.0.0 then change the value greater than 11.2.0. Follow the steps below to change the parameter Sqlplus / as sysdba ALTER SYSTEM SET COMPATIBLE = ‘12.2.0’ SCOPE=SPFILE; Shutdown IMMEDIATE STARTUP

5.2.2) Create a tablespace with encryption. The syntax will be like this 

CREATE TABLESPACE my_tablespace DATAFILE ‘C:\app\oracledb\oradata\orcl\TABLESPACE_NAME.dbf’ SIZE 150M ENCRYPTION USING ‘AES256’ DEFAULT STORAGE (ENCRYPT);

Note: Encrypting existing tablespace is not supported.

That’s it. The transparent data encryption is completed now.