Column and Tablespace Encryption using Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE):

Oracle Transparent Data Encryption (TDE) enables the organizations to encrypt sensitive application data on storage media completely transparent to the application. TDE addresses encryption requirements associated with public and private privacy and security regulations such as PCI DSS. TDE column encryption was introduced in Oracle Database 10g Release 2, enabling encryption of table columns containing sensitive information. The TDE tablespace encryption and the support for hardware security modules (HSM) were introduced in Oracle Database 11gR1.

TDE is protecting the data at rest. It is encrypting the data in the datafiles so that in case they are obtained by other parties it will not be possible to access the clear text data.  TDE cannot be used to obfuscate the data for the users who have privileges to access the tables. In the databases where TDE is configured any user who has access on an encrypted table will be able to see the data in clear text because Oracle will transparently decrypt the data for any user having the necessary privileges.

Note:

Oracle TDE is available by default in Oracle RDBMS Enterprise Edition. To be able to use this it is necessary to purchase an Oracle Advanced Security license.

Set the ENCRYPTION_WALLET_LOCATION in $ORACLE_HOME/network/admin/sqlnet.ora

[oracle@localhost admin]$ view sqlnet.ora

ENCRYPTION_WALLET_LOCATION =

   (SOURCE =

     (METHOD = FILE)

     (METHOD_DATA =

       (DIRECTORY = /home/oracle/wallet1)

     )

   )

Create the Keystore (wallet):

SQL>

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY oracle;

SQL>

select * from V$ENCRYPTION_WALLET;

SQL>

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 289406976 bytes

Fixed Size          2923536 bytes

Variable Size       230687728 bytes

Database Buffers       50331648 bytes

Redo Buffers            5464064 bytes

Database mounted.

Database opened.

SQL>

select * from V$ENCRYPTION_WALLET;

Wallet must open after Database Reboot:

If we failed to open the wallet after database reboot then users/applications will not be able to see the data from the encrypted tablespace/columns until the wallet is open.

SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY oracle;

System altered.

SQL> select * from V$ENCRYPTION_WALLET;

In order to create an encrypted table/tablespace the master key should be activated:

SQL> select key_id from v$encryption_keys;

Test Tablespace Encryption:

Now we are going to test tablespace encryption by creating a sample table in encrypted tablespace “DATA” and also in non-encrypted in tablespace “USERS”.

Creating Encrypted tablespace “DATA”:

create tablespace data datafile '/u01/app/oracle/oradata/TEST/datafile/data2.dbf' size 10m autoextend off ENCRYPTION USING 'AES192'

DEFAULT STORAGE(ENCRYPT);

SQL> SELECT tablespace_name, encrypted, status FROM dba_tablespaces;

SQL> select TABLESPACE_NAME, FILE_NAME from dba_data_files

   where TABLESPACE_NAME in ('USERS','DATA');

 

Creating table “scott.test” in USERS tablespace:

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

  

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

SQL> Commit;

  

SQL> SELECT * FROM SCOTT.TEST;

  

Creating table “scott.test_encryption” in DATA(encrypted):

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

  

SQL> insert into scott.test_encryption values ('CHECK ENCRYPTION')

SQL> Commit;

  

SQL> SELECT * FROM SCOTT.TEST_ENCRYPTION;

SQL> alter system checkpoint;

     System CHECKPOINT altered.

Checking USERS tablespace datafile:

Checking datafile in USERS tablespace to check whether we are able to see the data we inserted (data in the table scott.test: “CHECK ENCRYPTION”) in USERS(un-encrypted) tablespace

[oracle@localhost admin]$ strings /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_doffypfx_.dbf | grep 'CHECK ENCRYPTION'

Output:

 CHECK ENCRYPTION

Note: In the above case, we are able to see the table’s data from Datafile.

Checking DATA(Encrypted) tablespace datafile:

Checking datafiles to see whether we are able to see the data we inserted (data in the table scott.test_encryption: “CHECK ENCRYPTION”) in DATA(Encrypted) tablespace.

[oracle@localhost admin]$ strings /u01/app/oracle/oradata/TEST/datafile/data2.dbf | grep 'CHECK ENCRYPTION'

--No Output--

[oracle@localhost admin]$  

Note: In the above case, we are unable to see the table’s data from Datafile.

Conclusion: From the above testing "Tablespace Encryption" we can conclude that we cannot see the data of the objects that are stored in the ENCRYPTED tablespace's datafiles.

Test Column Encryption:

Creating table “SCOTT.TEST_COLUMN_ENCRYPTION” in non-encrypted tablespace “TEST” with an ENCRYPTED column “SECRET_DATE”.

SQL> SELECT tablespace_name, encrypted, status FROM dba_tablespaces;

SQL> select TABLESPACE_NAME, FILE_NAME from dba_data_files

   where TABLESPACE_NAME in ('TEST');

 

Creating table with columns Name(non-encrypted) and Secret_date(encrypted):

SQL> Create table SCOTT.TEST_COLUMN_ENCRYPTION (

ID number,

name varchar2(20),

Secret_date varchar2(30) encrypt)

tablespace test;

SQL> select * from dba_encrypted_columns;

SQL> insert into SCOTT.TEST_COLUMN_ENCRYPTION values (1, 'pavan','This is secret');

SQL> insert into SCOTT.TEST_COLUMN_ENCRYPTION values (1, 'Krishna','This is top secret');

SQL> alter system checkpoint;

SQL> select * from SCOTT.TEST_COLUMN_ENCRYPTION;

Checking non-encrypted column’s data in datafile:

[oracle@localhost ~]$ strings /u01/app/oracle/oradata/TEST/datafile/test1.dbf | grep pavan

Output: 

pavan4

[oracle@localhost ~]$ strings /u01/app/oracle/oradata/TEST/datafile/test1.dbf | grep Krishna

Output:

KrishnaDFV

Note: In the above case, we are able to see the column data from Datafile.

Checking encrypted column’s data in datafile:

[oracle@localhost ~]$ strings /u01/app/oracle/oradata/TEST/datafile/test1.dbf | grep 'This is secret'

--No Output--

[oracle@localhost ~]$ strings

/u01/app/oracle/oradata/TEST/datafile/test1.dbf | grep 'This is top secret'

--No Output--

[oracle@localhost ~]$

Note: In the above case, we are unable to see the encrypted column data from Datafile.

Conclusion: From the above testing "Column Encryption" we can conclude that we cannot see the column's data that is stored in the ENCRYPTED columns.

How To Verify With A Simple Test If TDE Tablespace Encryption is really encrypting my data? (Doc ID 1495828.1)

Master Note For Transparent Data Encryption ( TDE ) (Doc ID 1228046.1)