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

CHOOSE TDE COLUMN ENCRYPTION IF …:CHOOSE TDE TABLESPACE ENCRYPTION IF …:
Location of sensitive information is knownLocation of sensitive information is unknown
Less than 5% of all application columns are encryption candidates.Most of the application data is deemed sensitive, or multiple national and international security and privacy mandates apply to your industry
Data type and length is supported by TDE column encryptionNot all data types that hold sensitive information are supported by TDE column encryption
Encryption candidates are not foreign-key columnsEncryption candidates are foreign key columns
Indexes over encryption candidates are normal B-tree indexesIndexes of encryption candidates are functional indexes
Application does not perform range scans over encrypted dataApplication searches for ranges of sensitive data
Increase in storage by 1 to 52 bytes per encrypted valueNo storage increase acceptable
Performance impact depends on percentage of encrypted columns; how often the encrypted values are selected or updated, the size of encrypted data, and other variables.Constant performance impact below 10%

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

It is not possible to remove TDE once implemented.

Even if there are no encrypted objects in the database, the TDE wallet has to be present in the wallet location.It does not cause any harm.

Also, the metadata related to TDE keys cannot be cleaned up from the dictionary manually.

Deleting the TDE wallet will not disable TDE. Once the TDE wallet is configured, the wallet should never be deleted or recreated.

Recreating the wallet using any parameters is not supported.

Oracle Support/Development team will not help in resolving any issues arising due to such operations. 

If the wallet or its password is lost, then there is no way to recover the encrypted data.There is no back door, the database has to be recreated.

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 …