Verify Transparent Data Encryption (TDE)

Create an encrypted tablespace :

CREATE TABLESPACE data DATAFILE '/u01/app/oracle/oradata/ORCL/data01.dbf' SIZE 10M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

First check which tablespaces are encrypted and which ones are not:

SQL> select TABLESPACE_NAME, ENCRYPTED from dba_tablespaces;

TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS NO
EXAMPLE NO
DATA YES

Then find the associated datafiles on the filesystem:

SQL> select TABLESPACE_NAME, FILE_NAME from dba_data_files where TABLESPACE_NAME in ('USERS','DATA');

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
/u01/oradata/v1120/users01.dbf

DATA
/u01/oradata/v1120/data01.dbf

Create a test table in the unencrypted USERS tablespace and put a known string value in it:

SQL> create table scott.test(data varchar2(100)) tablespace users;

Table created.

SQL> insert into scott.test values ('ABCDEFGHIJKLMNOPQRSTUVWZYZ');

1 row created.

SQL> commit;

Commit complete.


Repeat the same but now put a table in the encrypted tablespace (it incidently shows the wallet needs to be open to be able to store data in the encrypted tablespsace):

SQL> create table scott.test_encrypted(data varchar2(100)) tablespace data;

Table created.

SQL> insert into scott.test_encrypted values ('ABCDEFGHIJKLMNOPQRSTUVWZYZ');
insert into scott.test_encrypted values ('ABCDEFGHIJKLMNOPQRSTUVWZYZ')
  *
ERROR at line 1:
ORA-28365: wallet is not open

SQL> alter system set wallet open identified by "welcome1";
System altered.

SQL> insert into scott.test_encrypted values ('ABCDEFGHIJKLMNOPQRSTUVWZYZ');
1 row created.

SQL> commit;
Commit complete.

Make sure all changes are flushed to disk, otherwise the data may not be written do the datafiles when performing the subsequent check:

SQL> alter system checkpoint;

System altered.

Now use the OS 'strings' command to determine whether the string value inserted in the table is 'visible':

SQL> !strings /u01/oradata/v1120/users01.dbf | grep ABCDEF
ABCDEFGHIJKLMNOPQRSTUVWZYZ

SQL> !strings /u01/oradata/v1120/data01.dbf | grep ABCDEF

<no output>