MySQL and MariaDB Encryption OptionsDec 10, 2019 / Posted By:Robert Buda
MySQL and MariaDB Encryption Options
Encryption remains one of the most widely used cornerstones of data security across organizations of all sizes and industries. Why? In many cases it’s both effective and relatively simple to implement.
There are two fundamental encryption capabilities that are almost universally necessary for basic data security:
- The ability to encrypt sensitive data “at rest”—that is, where it resides on disk—is a critical security capability for many organizations and applications, as well as a requirement for compliance with PCI, HIPAA, GDPR, CCPA and other regulations.
- Likewise, encrypting data “in transit” across private and public networks (such as using the HTTPS protocol for payment transactions online) is essential to prevent rampant data exfiltration.
MySQL and MariaDB each support both these encryption modalities. They give you the ability to encrypt data at rest at the database level, as well as encrypting connections between the MySQL or MariaDB client and the server.
Database-level encryption in MySQL
MySQL has offered strong encryption for data at rest at the database level since MySQL 5.7. This feature requires no application code, schema or data type changes. It is also straightforward for DBAs, as it does not require them to manage associated keys. Keys can be securely stored separate from the data and key rotation is easy.
MySQL currently supports database-level encryption for general tablespaces, file-per-table tablespaces and the mysql system tablespace. While earlier MySQL versions encrypted only InnoDB tables, newer versions can also encrypt various log files (e.g., undo logs and redo logs). Also, beginning with MySQL 8.0.16, you can set an encryption default for schemas and general tablespaces, enabling DBAs to control whether tables are encrypted automatically.
MySQL database-level encryption is overall secure, easy to implement and low-overhead. Among its limitations, it does not offer per-user granularity, and it cannot protect against a malicious root user (who can read the keyring file). Also, database-level encryption cannot protect data in RAM.
MySQL Enterprise Transparent Data Encryption
In addition to the generic database-level encryption just described, users of “select Commercial Editions” of MySQL Enterprise can also leverage Transparent Data Encryption (TDE). This feature encrypts data automatically, in real-time, before writing it to disk; and decrypts it automatically when reading it from disk.
TDE is “transparent” to users and applications in that it doesn’t require code, schema or data type changes. Developers and DBAs can encrypt/decrypt previously unencrypted MySQL tables with this approach. It uses database caching to improve performance and can be implemented without taking databases offline.
Other MySQL Enterprise Encryption Features
Besides TDE, MySQL Enterprise Edition 5.6 and newer offers encryption functions based on the OpenSSL library, which expose OpenSSL capabilities at the SQL level. By calling these functions, mySQL Enterprise applications can perform the following operations:
- Improve data protection with public-key asymmetric cryptography
- Create public and private keys and digital signatures
- Perform asymmetric encryption and decryption
- Use cryptographic hashes for digital signing and data verification/validation
MariaDB database-level encryption
MariaDB has supported encryption of tables and tablespaces since version 10.1.3. Once data-at-rest encryption is enabled in MariaDB, tables that are defined with ENCRYPTED=YES or with innodb_encrypt_tables=ON will be encrypted. Encryption is supported for the InnoDB and XtraDB storage engines, as well as for tables created with ROW_FORMAT=PAGE (the default) for the Aria storage engine.
An advantage of MariaDB’s database-level encryption is its flexibility. When using InnoDB or XtraDB you can encrypt all tablespaces/tables, individual tables, or everything but individual tables. You can also encrypt the log files, which is a good practice.
Encrypted MariaDB data is decrypted only when accessed via the MariaDB database, which makes it highly secure. A potential downside is that MariaDB’s encryption adds about 3-5% data size overhead.
Encrypting data “in transit” with MySQL
Depending on your application, there may be little point in encrypting sensitive data on disk if it is then passed “in the clear” between the MySQL client and server—exposing it to potential inspection and exfiltration of the data if your network is compromised.
MySQL supports encrypted connections between the server and clients via the Transport Layer Security (TLS) protocol, using OpenSSL.
By default, MySQL programs try to connect using encryption if it is supported on the server; unencrypted connections are the fallback. If your risk profile or regulatory mandates require it, MySQL lets you can make encrypted connections mandatory.
Encrypting data in transit with MariaDB
By default, MariaDB does not encrypt data during transmission over the network between clients and the server. To avoid “man-in-the-middle” attacks and other cyberattacks, you can encrypt data in data in transit using the Transport Layer Security (TLS) protocol—provided your MariaDB server was compiled with TLS support. Note that MariaDB does not support older SSL versions.
As you might expect, there are a number of steps involved in setting up data-in-transit encryption, such as creating certificates and enabling encryption on the client side. See this page in the MariaDB knowledgebase for details.
With data security being an increasing business and regulatory concern, there is a lot more to think about than just encryption to secure your MySQL or MariaDB environment: security policies, patching, file privileges, access controls and auditing capabilities to name just a few issues.
If you’re not sure whether your data is as secure as it should be, contact Buda Consulting for a free consultation on our database security assessment process.