TDE (Transparent Data Encryption)

Steps to implement TDE on Oracle 12c standalone DB.

1. Make Wallet directory

mkdir -p /test/WALLET

2. Modify sqlnet.ora

in "/network/admin"

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

copy this sqlnet.ora to /network/admin"

3. Create Keystore using mkstore (optional)

ORACLE_HOME/oracle_common/bin/mkstore -wrl <wallet_location> -createCredential <db_connect_string> <username> 

3. Create Keystore (12c specific, syntax diff for previous versions)

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

Check if following file is created

$ ls -ltr /test/WALLET/
total 4
-rw------- 1 oracle asmadmin 2555 Mar 28 09:34 ewallet.p01


4. open Keystore

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY ######;

Make sure you have copied sqlnet.ora from grid home to oracle db home

5. Now activate the key

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

no rows selected

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY ###### WITH BACKUP;

keystore altered.

SQL> SELECT con_id, key_id FROM v$encryption_keys;

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

SQL>


6. Enable Autologin

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


SQL> SELECT * FROM v$encryption_wallet;

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

SQL>


We can see that wallet type is "PASSWORD". That means everytime we restart database, we need to open wallet with password.

To avoid it, we can enable Autologin

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

Then restart database and check again. This time WALLET_TYPE should be AUTOLOGIN.

You should see 2 files in wallet location as below

-rw------- 1 oracle asmadmin 3995 Mar 28 09:41 ewallet.p01
-rw------- 1 oracle asmadmin 4040 Mar 29 12:16 cwallet.sso

If we check now, WALLET_TYPE will be AUTOLOGIN


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




7. Create encrypted tablespace

CREATE TABLESPACE TDE_TESTDATAFILE 
  '+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'
DEFAULT STORAGE(ENCRYPT)
FLASHBACK ON;

Now you can move tables/indexes to this tablespace as usual.