Oracle TDE part II : Encrypting Data

By Mouhamadou Diaw

In the first part of this blog, we activate TDE in the database, in this part we will see how we can encrypt data. I am using Oracle 19c for the tests

As specified before TDE will allow encryption at 2 levels

-Tablespace level

-Table column level

TDE will encrypt your data in rest at filesystem level.

Tablespace level

A tablespace can be encrypted during its creation.

SQL>  create tablespace TBS_ENC datafile  size 10m autoextend on maxsize 3G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ENCRYPTION DEFAULT STORAGE (ENCRYPT);

Tablespace created.

SQL>

We can specify the encryption algorithm to use, otherwise the default will be used

You can use dba_tablespaces, v$encrypted_tablespaces for information about encrypted tablespaces

SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces;

TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO
MYTABAA                        NO
PRODTAB                         NO
TBS_ENC                        YES

8 rows selected.

It’s also possible to do an online encryption for existing tablespaces

SQL> alter tablespace PRODTAB encryption online  encrypt;

Tablespace altered.

The parameter ENCRYPT_NEW_TABLESPACES will allow future tablespaces to be encrypted.

Column table level

We can encrypt a column in at the  table with creation

SQL> create table edge.test_encrypt (id number, name varchar2(30), salary number encrypt);

Table created.

SQL>

We can add an encrypted column to an existing table

SQL> alter table edge.test_encrypt add (idcard varchar(12) encrypt);

Table altered.

We can transform a non-encrypted column to encrypted one

SQL> alter table edge.test_encrypt modify (name  encrypt);

Table altered.

SQL>

To get information about encrypted column the dba_encrypted_columns can be queried.

SQL> select TABLE_NAME,COLUMN_NAME,ENCRYPTION_ALG from dba_encrypted_columns where OWNER='EDGE';

TABLE_NAME      COLUMN_NAME     ENCRYPTION_ALG
--------------- --------------- -----------------
TEST_ENCRYPT    NAME            AES 192 bits key
TEST_ENCRYPT    SALARY          AES 192 bits key
TEST_ENCRYPT    IDCARD          AES 192 bits key

SQL>

Note that you can also specify the encryption algorithm if you don’t want to use the default one.

Questions that can be asked

-Column vs tablespace encryption

One question may be which encryption level I have to use. There is really no fix answer, it’s depends of your requirements. In the following document we have some guidelines

In any case we should know that TDE column encryption has some limitations. Check the documentation

-Can we remove TDE once activated in the DB

Have a look in this document

The response is definitevely NO

In this document Is it Possible to Remove/Disable TDE? (Doc ID 2488898.1) we have following lines

Conclusion

We have seen how TDE can help for encrypting data. Just remember that TDE need Oracle Advanced Security. As disabling TDE is not supported, all daily procedures should be tested before implementation. This include RMAN backup and restore, RMAN duplicate , Data Guard operation …