Oracle Transparent Data Encryption

TDE makes sure that the data is stored in encrypted form and when the data is accessed, it automatically decrypt it and it all happens transparently so it does not require even a single line of code at the application or database end.

 setup Transparent Data Encryption:

 First of all we need to define wallet location. Open your sqlnet.ora file and define the wallet location which can be any directory at the database server.

$ cd /d01/apps/oracle/network/admin
 $ mkdir tde_wallet
 $ vi sqlnet.ora

ENCRYPTION_WALLET_LOCATION =
 (SOURCE=
   (METHOD=file)
     (METHOD_DATA=
       (DIRECTORY=/d01/apps/oracle/network/admin/tde_wallet)))

Create a Secure Wallet to hold the Master Encryption Key:

$ sqlplus / as sysdba
SQL> alter system set encryption key authenticated by "ImOracle";       
 System altered.

Data with and without TDE:

create tablespace ts_tde datafile '/d01/apps/oradata/oraxpo/ts_tde01.dbf' size 20m autoextend on next 5m
extent management local segment space management auto;

create user tde_test identified by test  default tablespace ts_tde quota unlimited on ts_tde;
grant connect,resource to tde_test;

 We have a user named tde_test with a default tablespace ts_tde whose datafile.

conn tde_test/test
create table tde (sensitive_data varchar2(50));
insert into tde values ('This is very sensitive data');
commit;

 select * from tde;
  SENSITIVE_DATA
--------------------------------------------------
This is very sensitive data


SQL> conn / as sysdba
Connected.

 Flush the buffer_cache so that blocks in DB Buffer cache go to the datafile.

SQL> alter system flush buffer_cache;
 System altered.

 Now we create a table with a column encrypted transparently:

 $ sqlplus tde_test/test

 SQL> drop table tde purge;
 Table dropped.

 There are 4 encryption algorithms available for TDE.

   3DES168

   AES128

   AES192

   AES256

  AES192 is the default.

create table tde (sensitive_data varchar2(50) encrypt using '3DES168');
insert into tde values ('This is very sensitive data');
commit;


select * from tde;
 SENSITIVE_DATA
-------------------------------
This is very sensitive data

As you see the data entered in an encrypted column is accessible in clear text.

And that is why this encryption is called Transparent Data Encryption.

SQL> conn / as sysdba
Connected.

SQL> alter system flush buffer_cache;
 System altered.

SQL> select table_name , column_name , ENCRYPTION_ALG , SALT from dba_encrypted_columns;

TABLE_NAME     COLUMN_NAME       ENCRYPTION_ALG                SAL
-------------- ----------------- ----------------------------- ---
TDE            SENSITIVE_DATA    3 Key Triple DES 168 bits key YES


DBA_ENCRYPTED_COLUMNS gives you a list of all encrypted columns in the database.

Open and Close the wallet:

 $ sqlplus / as sysdba

 The database was restarted so the wallet is closed at this moment.

 Any query on encrypted columns will throw an error.

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

 Let's open the wallet and notice you will need a password to open the wallet.

SQL> alter system set encryption wallet open authenticated by "ImOracle";
System altered.


SQL> select * from tde_test.tde;
  
SENSITIVE_DATA
--------------------------------------------------
This is very sensitive

  This is how we close the wallet.
SQL> alter system set encryption wallet close;
 System altered.

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