How Does a Database that Supports TDE Work In Practice?

Question

I'm very curious on how does TDE is implemented in practice. My goal is to get understanding on the usability constraint and possibility performance impact of applying such technique.

Thus I'm trying to understand how does the database perform search under such encryption scheme, since, in my understanding, performing encryption such as AES encryption requires the use of random IV, rendering query with where clause to be impossible unless a full database scan is done.

For example, such query:

select * from some_tbl where some_column = "some_plain_text"

would fail since the database no longer contains "some_plain_text" assuming some_column is encrypted. I have heard some techniques solve this issue by saving the hash on the database as well, and search is done based on the hash instead.

But it should only solve queries of exact match, but how about queries like this:

select * from some_tbl where some_column < 5

or queries that use the 'like' operator, and how about indexing?

Answer 1

TDE encrypts only data at rest. Data is always visible inside the running database (the encryption is transparent to the user or application). Data is decrypted automatically as it is read from disk into memory and encrypted as it is written back to disk. SQL queries perform normally, since search criteria or functions aren't applied until the data is in memory anyway. TDE only protects against theft of the storage media itself, or against theft of the physical data files; it does not protect against theft of data using normal SQL commands.

There is a performance impact to this functionality: it will take longer to perform disk I/O because you've added an extra layer of processing for the encryption or decryption. The scale of that impact will depend on your particular storage medium and the volume of data you're processing at any given time. For most people it is negligible, but every situation is unique.

Note that depending on how your datafiles are stored, file system encryption from the OS may not be available as an alternative (e.g. Oracle's Automatic Storage Management architecture). Transparent filesystem encryption only protects against theft of the storage device: if I have access to your filesystem by logging in to the server, I can view the file or copy the file off of your server and the OS will decrypt it automatically for me. Database-level TDE allows your data protection architecture to be OS-agnostic and prevents that kind of datafile theft.

The decision on whether or not to use TDE is often driven by legal or regulatory compliance requirements, and not by developer preference. Many cloud service providers enable some form of TDE by default.

Answer 2

From my understanding of TDE, it's only a encryption at rest, the decryption is done on the fly by the DB engine, so queries are written in a "normal" way.

Side note: at job-1 we tested TDE and finally goes back to FS encryption as the performance was downgraded by more than 10% while FS Encryption had 3 to 4% overhead.