Best Practices for Encrypting Data In Your Database
Tags: security, encryption, mysql
Published: 2023-04-21 06:43:40

**Data encryption is one of the most important caveats for safeguarding our data. These days where safety on the web is paramount and data breaches so rampant, it‘s critical to walk yourself through a couple of best practices to encrypt data inside of our database instances. Do that with us!**

## What Is Data Encryption?
These days where data breaches are so rampant, encrypting and securing our data is more important than ever. However, for many database administrators, encrypting data is still a pretty daunting task – however, properly encrypting data might be a must to adhere to security standards like HIPAA, GDPR, and the like.

Simply put, data encryption refers to the practice of turning – or “translating” – data into another form, so that only the people with the decryption key can read it. Generally, there are two ways to encrypt data in a database: by using data-at-rest encryption, or by using data-in-transit encryption.

## Data at Rest Encryption
Many database management systems, including, but not limited to MySQL and all of its flavors (Percona Server and MariaDB), support data-at-rest encryption methods for multiple types of data including, but not limited to tablespaces (the directories where table data is stored), and logging mechanisms (redo logs and undo logs.) Many database management systems come with quite a few prerequisites to make the encryption process successful: these may include a keyring plugin (keyring is sort of a vault that generates, encrypts, and decrypts generated keys) and a master encryption key.

Whatever might be the case, the loss of an encryption key is permanent and in almost all cases once the key is lost, the encrypted data can no longer be recovered.
While everything might sound complex, don’t fret – many database management systems don’t overcomplicate the whole process if you follow the steps to encrypt data that, in most cases, are outlined in their documentation. MySQL, for example, lets you encrypt data after defining a keyring (learn how to do that here – make sure to go through all of the prerequisites), and then running a query like so:
<br \>
![Encrypt data after defining a keyring in MySQL.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h4jvkevg0ca7zri4rkbs.png)
<figure><figcaption>Encrypt data after defining a keyring in MySQL.</figcaption></figure>
<br \>
If you’ve defined a master key (the key that should encrypt all of your data), the query will go through without any issue, however, if you didn’t, you may have to face an error like in the following example:
<br \>
![Error message when encrypting data if you’ve not defined a master key.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9s2qdjjxk4pa8vnx3rwy.png)
<figure><figcaption>Error message when encrypting data if you’ve not defined a master key.</figcaption></figure>
<br \>
If the master key (again, the key that encrypts data – please don’t lose it) is generated, though, some database management systems like MySQL also allow you to encrypt entire tablespaces upon their creation – in other words, you can encrypt files where the data is stored by running queries like the following (one query creates a tablespace, another one makes an existing tablespace encrypted):
<br \>
![Encrypting entire tablespaces in MySQL when a master key is generated.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9wtvtxyae0rig6pglj2b.png)
<figure><figcaption>Encrypting entire tablespaces in MySQL when a master key is generated.</figcaption></figure>
<br \>
Some database management systems also allow us to encrypt log files: for example, MySQL comes with the [innodb_redo_log_encrypt](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_redo_log_encrypt) database encryption option, which, if turned to ON (by default, the setting is OFF), will also encrypt redo log files, but the functionality of this option would heavily depend on the DBMS itself.

## Data In Transit Encryption
Aside from supporting data-at-rest encryption, most database management systems support data-in-transit encryption as well. MySQL, for example, allows people to elect to use [encrypted connections](https://dev.mysql.com/doc/refman/8.0/en/encrypted-connections.html) to prevent anyone with access to the network from watching all traffic. And that’s what data in transit encryption is all about, really – data in transit encrypts data while it’s moving back and forth. In other words, while it’s in transit.

To secure data in transit in database management systems, most of us will need to work with internal database configuration files – `my.cnf` for MySQL, `postgresql.conf` for PostgreSQL, etc.

First, find the part of the documentation where the database management system defines how to use encrypted connections (for MySQL, this part is located over here), then follow the pre-defined instructions: instructions will most likely stress the need to define a certificate authority (CA), the certificate file itself, and the server key. For MySQL, the definitions look like so:
<br \>
![Definitions in MySQL to use encrypted connections](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/99aoli71jhqspffpo0h3.png)
<figure><figcaption>Definitions in MySQL to use encrypted connections.</figcaption></figure>
<br \>
Look at the lines #221 to #224 – these lines define the path towards the certificate authority (ca), certificate (cert), and the key (key.) The `require_secure_transport` variable, on the other hand, specifies that all clients connecting to MySQL should be using encrypted connections. Creating SSL certificates (the certificates that enable encryption of data to be possible) is also rather easy in all database management systems – MySQL provides a guide on how to do that [here](https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-rsa-files.html).

## When to Encrypt Data?
For those who are familiar with the aforementioned data encryption methods, a natural question might arise – when should we encrypt our data in the first place? What encryption method to use? And the answers to these questions are rather simple: you should encrypt data everytime it’s sensitive enough to cause some damage to the reputation or data of the company you’re working at. In the best case scenario, you should consider encrypting data both in transit and at rest, but for most, encrypting data at rest will be enough: just make sure your website uses SSL (https://) to protect the data flowing back and forth from your website. If you find yourself having trouble encrypting data, though, talk to the security engineers over at the company you find yourself working at – they will most likely be able to find a solution to almost every encryption-related issue.

Also consider checking out [data breach search engines](https://breachdirectory.com/) like the one built by BreachDirectory – they will help your company prevent being a victim of data breaches by letting you implement data breach data into the infrastructure of your company. Encryption is important, and its importance is even more significant when you look at the incidents that have occurred in the past.

## Summary
Handling encrypted data inside of any database instance is not the simplest of tasks – however, with adequate preparation and know-how of how security issues are handled in the first place, everything can be done.

Make sure to explore the encryption capabilities offered by your DBMS of choice before employing any of them, play around with them in a demo environment before rolling out any changes to production, and until next time!

## About the author
Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - [BreachDirectory.com](https://breachdirectory.com/), frequently speaks at conferences and blogs in multiple places including his blog over at [lukasvileikis.com](https://www.lukasvileikis.com/).