Using Oracle's TDE attribute encryption

Oracle database transparent Data encryption (TDE), introduced as part of Oracle's Advanced Security options (Version 10.2 and later), selectively enables sensitive database data that remains in the underlying data files of the database and all downstream file components, such as online redo logs, Archive redo logs and database backups) for encryption. The basic goal of TDE is to protect sensitive data found in these original operating system files, to prevent malicious people from accessing the disk or to back up tapes, and then to try to restore the database or scan data in the original operating system files, such as personally identifiable information or credit card information.

As part of my consulting practice, I have implemented TDE many times. However, before one of the most recent contracts, I have been using TDE to encrypt new columns in an existing table or columns that belong to a new table. Using TDE in both cases is very simple because the target column is empty and therefore does not involve significant risk due to a lack of data and existing application dependencies.

My recent experience in implementing TDE is different. I helped a large company encrypt an existing column in a table that has more than 1 million rows. There is also a mission-critical application that relies on columns, so you can imagine that there are a lot of important things to consider before you start working. After searching the Internet for similar scenarios that provide experience, I found only a few good resources to help me.

This article outlines the lessons I've learned in the process of encrypting existing data by using TDE. If you try to use TDE for existing column data, I hope the information provided here will help you to do similar work quickly and efficiently.

Determine possible limits

When researching a customer's system, the first thing I do is look up the data model features associated with the target column that will prevent us from encrypting the columns, or look for items that might have a negative impact on the existing operation. The study includes finding column indexes and integrity constraints.

As the Oracle document explicitly declares, you need to know a lot of restrictions when you want to encrypt a column that has an index. Oracle does not allow encryption of columns with bitmap indexes, which is not closely related to our situation. However, the destination column has more than one common (B-tree) index. Although Oracle allows for the encryption of columns with normal indexes, Oracle prohibits "salt processing" encryption of indexed columns. Salt processing increases the security of duplicate data by adding random strings to the data before encryption, so it is more difficult for thieves to crack encrypted data using pattern-matching recognition techniques. All in all, after this initial analysis, we have a situation where we can encrypt the columns but not salt.

After parsing the column index, I could have done so, but the next question I wanted to answer was "is it appropriate to use these indexes?" "My thinking process is this: if the index is not used, then I will delete it, reducing the overhead necessary to maintain the index entries, especially given the additional burden of encryption." To determine if the index is useful, I use the index monitoring feature of the Oracle database. I found that the index was actually in use, so we had to continue to maintain it.

Next, I looked at whether the target column was involved in the referential integrity constraint. Because each table has its own encryption key, Oracle does not allow you to encrypt using the columns involved in the TDE foreign key relationship. In our case, the target column is not involved in the referential integrity constraint.