Transparent Data Encryption for Databases

If you are creating a software application that needs to store and retrieve data, then most probably you have to use Databases. Unless it is not an in-memory database, Database store data on the disk as a file. Your application may be running on a remote server and database also reside on a remote server. Your application may collect sensitive data such as passwords, bank account data, and health data etc. Imagine a hacker somehow login into your database server and stolen your database file. Now, Unfortunately Hacker able access to all of your information by reading that file. So, How could you overcome this problem?

Encryption comes to Rescue

Encryption commonly used in day to day network communication to hide data from the third party. Encryption scrambles your data by a given key. Only the person who knows the right key can read the real message. Encryption could be either symmetric or asymmetric. In this case, we just need symmetric encryption since the user does not need to generate a shared secret.

The simplest solution to overcome this problem is by storing data encrypted rather storing as a flat file. But, encrypting the whole file is not a practical solution since its need to decrypt whole database file to execute each of the SQL queries. It is a huge performance drawback. Instead of encrypting the whole file we can use pages to encrypt database data as blocks. Pages are the smallest unit of transaction in a database. Pages are fixed in size (normally 4KB). Pages used to store Btree nodes in a database. These nodes can be either a collection of table rows or keys that used for indexing (To learn more about Btree and Pages, read this blog post).

How Transparent Data Encryption works?

Now, keeping data on remote file is not an issue since now we know we can store data in encrypted form. Here on, we are going to discussing how everything put in together. For that, let us see how MySQL implement this feature.

TDE in MySQL

Here, an application needs to store a table in MySQL server. It sends SQL query to the MySQL server. MySQL server read the database pages to evaluate the SQL expression. But, Page is in the encrypted format. Now the database needs to find the relevant key to decrypt page. Obviously, you need to keep this key in a secure place. Key Vault is a secure vault that used to store such keys, certificates, and passwords. MySQL read the key from Key Vault and decrypt the pages and execute the SQL Query. If any modification happens on Pages, then it encrypts the page and saves it on the file. Here AES encryption used with CBC mode used to encrypt and decrypt the data.

Key Vault

Centralized management of TDE direct connection of TDE master key (Image from: https://docs.oracle.com/cd/E50341_01/doc.1210/e41361/okv_concepts.htm)

Key vault provides centralized storage to store keys even in the distributed database. Instead of using separate keys for multiple TDE requirements, you can maintain a single key called Master key. Additional to storing keys, Key vault provides a way to administrate roles. Following are the roles used in Oracle Key Vault.

1. System Administrator
2. Key Administrator
3. Audit Manager

System administrator having authority to create, modify and delete a user. It can add a new endpoint to the system. Further, this role is responsible to schedule backup and set up high availability.

Key Administrator control user and endpoint access to the virtual wallet. It can manage user groups and endpoint groups.

Audit manager has read access to all security objects. It is responsible to manage the audit trails.

Pros and Cons

Seems TDE is a nice way to save data from a hacker. However, TDE has both pros and cons, when it using in the production.

Pros

1. Easy to implement
2. No changes required in the application layer
3. Features such as Mirroring, Always On and Log Shipping can be used along with TDE

Cons

1. Data secured only when it at rest. Database server still can read your data and keys.
2. Data that not required to hide, is also stored in encrypted form.
3. Small performance impact due to encryption overhead.
4. The database backup may become heavier when it compressed

Other Database encryption methods

Other than using TDE to hide data in page level, Column Level encryption also available. In this case, Database encrypts the column rather encrypting pages. This makes column level encryption much more flexible compared to TDE. Further, Database can use separate keys to encrypt each of the columns. Performance of the database becomes a drawback since multiple keys used to encrypt a single database.

Conclusion

TDE protect your data in remote database server by using encryption. Purpose of the TDE is secure data in databases file level(at rest). Almost all of the commercial databases provide the ability to use TDE to protect data. Even though you use TDE to secure your database from a hacker, still you need to take action to save your data from other possible adversaries such as XSS attack, SQL injection, Replay attack etc. Designing software that runs on the web requires lot more security than it runs as a single desktop application. Therefore whole design should be tightly secure, in order to provide a fully secure platform to the end user.