Best Practices for Encrypting Data In Your Database


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!

Tools used in the tutorial
Tool Description Link

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:

Encrypt data after defining a keyring in MySQL.
Encrypt data after defining a keyring in MySQL.

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:

Error message when encrypting data if you’ve not defined a master key.
Error message when encrypting data if you’ve not defined a master key.

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):

Encrypting entire tablespaces in MySQL when a master key is generated.
Encrypting entire tablespaces in MySQL when a master key is generated.

Some database management systems also allow us to encrypt log files: for example, MySQL comes with the 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 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.

DbVisualizer logo

If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.

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:

Definitions in MySQL to use encrypted connections.
Definitions in MySQL to use encrypted connections.

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.

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 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.


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!

Dbvis download link img
About the author
Lukas Vileikis
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, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published

Discover DbVisualizer Security Features for MySQL

author Igor Bobriakov tags MySQL SECURITY 6 MINS 2023-10-10

Preventing SQL Injection Attacks with DbVisualizer

author Ochuko Onojakpor tags SECURITY 8 MINS 2023-06-13

How to Make Your SQL Server More Secure

author Igor Bobriakov tags SECURITY SQL SERVER 8 MINS 2023-05-17

Parameterized Queries in SQL – A Guide

author Lukas Vileikis tags SECURITY SQL 5 MINS 2023-05-12

Solved: sudo mysql_secure_installation command not found in MariaDB

author TheTable tags MARIADB SECURITY 3 MINS 2023-04-27

MySQL User Management: A Guide

author Lukas Vileikis tags SECURITY 8 MINS 2023-04-25

Preventing Illicit Uploads in MySQL – secure_file_priv

author Lukas Vileikis tags MySQL SECURITY 7 MINS 2023-03-21

A Guide to ACID In MySQL

author Lukas Vileikis tags ACID MySQL SECURITY 6 MINS 2023-03-17

Database Security 101: Best Practices to Secure Your Data

author Lukas Vileikis tags SECURITY 7 MINS 2023-03-02

How To Secure Data In A Postgres Database: A Guide

author Bonnie tags POSTGRESQL SECURITY 13 MINS 2023-02-21

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗