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