Transparent Data Encryption (TDE) provides mechanism to encrypt the data stored in the OS data files. TDE enables the encryption of data at the storage level to prevent data tempering from outside of the database.

New commands has been introduced in oracle 12c for enabling Transperant data encryption.ADMINISTER KEY MANAGEMENT will replace the previous commands like ALTER SYSTEM SET ENCRYPTION WALLET and Wallet is known as keystore in 12c.

 

 

Lets see how to configure TDE.
 
1. Create a wallet/keystore location.
 


mkdir -p /media/sf_stuff/WALLET

2. update the wallet/keystore location in sqlnet.ora. It should look like.

cat  $ORACLE_HOME/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)(METHOD_DATA =
    (DIRECTORY = /media/sf_stuff/WALLET)))

 

Create keystore:

 

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/media/sf_stuff/WALLET/' IDENTIFIED BY walletpass#123;

keystore altered.

SQL> host ls /media/sf_stuff/WALLET/
ewallet.p12

 

Now open the keystore:

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY walletpass#123;

keystore altered.

Now activate the key:

 


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

no rows selected

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

keystore altered.

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

    CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
	 0 AS6cSkI4u09zv9+RRWMrX2QAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
	

SQL> SET LINESIZE 200
COLUMN wrl_parameter FORMAT A50
SELECT * FROM v$encryption_wallet;
SQL> SQL> 
WRL_TYPE	     WRL_PARAMETER					STATUS	       WALLET_TYPE	    WALLET_OR FULLY_BAC     CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------
FILE		     /media/sf_stuff/WALLET/				OPEN	       PASSWORD 	    SINGLE    NO		 0

 

 create a encrypted a tablespace 

 


SQL> CREATE TABLESPACE TEST_ENCRY 
datafile '/home/oracle/app/oracle/oradata/cdb1/testencry.dbf' size 2G 
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);  2    3    4  

Tablespace created.


SQL> create table emp_ency(
empno   Number(3),
Name     varchar(10)
) tablespace TEST_ENCRY;  2    3    4  

Table created.



SQL> select tablespace_name,encrypted from dba_tablespaces where tablespace_name='TEST_ENCRY';

TABLESPACE_NAME 	       ENC
------------------------------ ---
TEST_ENCRY		       YES

 

Create a table with encrypted column:

SQL> CREATE TABLE employee (
     first_name VARCHAR2(128),
     last_name VARCHAR2(128),
     empID NUMBER,
     salary NUMBER(6) ENCRYPT
);  2    3    4    5    6  

Table created.



SQL> select owner,table_name,column_name,encryption_alg from dba_encrypted_columns where table_name='EMPLOYEE';

OWNER	   TABLE_NAME	COLUMN_NAME  ENCRYPTION_A
---------- ------------ ------------ ------------
RAJ	   EMPLOYEE	SALARY	     AES 192 bits
				      key

ENABLE AUTOLOGIN :

 


SQL> SELECT * FROM v$encryption_wallet;

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

 

                  Here the wallet_type is PASSWORD , i.e every time we restart the database, we need to open the key/wallet explicitly. To avoid this, we can enable auto login ,so that next time when db gets restart, it will open the wallet automatically.

 


SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/media/sf_stuff/WALLET/' IDENTIFIED BY walletpass#123;

keystore altered.

SQL>  SELECT * FROM v$encryption_wallet;

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

SQL> 
SQL> startup force
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size		    2929936 bytes
Variable Size		  570428144 bytes
Database Buffers	  260046848 bytes
Redo Buffers		    5455872 bytes
Database mounted.
Database opened.
SQL> SELECT * FROM v$encryption_wallet;


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

 

we can see the wallet opened automatically and the wallet_type has been changed from PASSWORD TO AUTOLOGIN.

 

For multi-tenant database:
 

In a multi-tenant database (CDB), the Keystore has to be be created in the ROOT container (CDB$ROOT).
This single Keystore will be shared by all the associated PDBs as well as the CDB$ROOT container.

So for this we need to use CONTAINER=ALL clause to open and activate the keystore  in all pdbs.
 

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY walletpass#123 CONTAINER=ALL;

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY walletpass#123 WITH BACKUP CONTAINER=ALL;

 

NOTE: To create a keystore user should have either ADMINISTER KEY MANAGEMENT or SYSKM privilege.

Related dictionary tables for TDE:

 


----What tables contain TDE encrypted columns?
sql> select table_name, column_name from dba_encrypted_columns;

-------What tables are stored in TDE encrypted tablespaces?
sql> select a.table_name, a.tablespace_name from dba_tables a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name and b.encrypted = 'YES';

-------What indexes are stored in TDE encrypted tablespaces?
sql> select a.index_name, a.tablespace_name from dba_indexes a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name and b.encrypted = 'YES' and index_name not like 'SYS_IL%';

------- getting key/wallet details:
SQL> SELECT * FROM v$encryption_wallet;

SQL> SELECT con_id, key_id FROM v$encryption_keys;