Oracle TDE Tablespace Encryption Proof of Concept

TDE transparently encrypts data at rest in Oracle Databases. It stops unauthorized attempts from the operating system to access database data stored in files, without impacting how applications access the data using SQL. TDE can encrypt entire application tablespaces or specific sensitive columns. This is a POC to Check, if TDE Tablespace Encryption is really encrypting my data?

A. Create two Tablespace, one is encrypted another one is normal. After that find the associated datafiles on the file system.

CREATE TABLESPACE ENC_TEST DATAFILE '/staging/datafile/enctest.dbf' SIZE 50M ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);
CREATE TABLESPACE NONENC_TEST DATAFILE '/staging/datafile/nonenctest.dbf' SIZE 50M;

select TABLESPACE_NAME, ENCRYPTED from dba_tablespaces;
select TABLESPACE_NAME, FILE_NAME from dba_data_files where TABLESPACE_NAME in ('ENC_TEST','NONENC_TEST');

B. Create a test table in the unencrypted NONENC_TEST tablespace and put a known string value in it. Repeat the same but now put a table in the encrypted ENC_TEST tablespace.

create table enctest1(data varchar2(100)) tablespace NONENC_TEST;
insert into enctest1 values ('DataIsTheNewOil');

create table enctest2(data varchar2(100)) tablespace ENC_TEST;
insert into enctest2 values ('DataIsTheNewOil');

C. Now use the OS ‘strings’ command to determine whether the string value inserted in the table is ‘visible’.

$ strings /staging/datafile/nonenctest.dbf |grep Data
DataIsTheNewOil

$ strings /staging/datafile/enctest.dbf |grep Data
$<nodata>

Result: Encrypted Tablespace’s value can’t be seen by OS user as compare to non-encrypted one, which confirms that data at rest in encrypted table/tablespace is protected.

Drop the tables after testing.

DROP TABLESPACE ENC_TEST INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE NONENC_TEST INCLUDING CONTENTS AND DATAFILES;

Thanks for reading!