Long a cornerstone of data security, encryption is becoming more important than ever as organizations come to grips with major trends like teleworking, privacy mandates and Zero Trust architectures. To comprehensively protect data from the widest possible range of threats and meet the demands of these new use cases, you need two fundamental encryption capabilities:
- The ability to encrypt sensitive data “at rest”—that is, where it resides on disk. This is a critical security capability for many organizations and applications, as well as a de facto requirement for compliance with privacy regulations like HIPAA, GDPR and CCPA. PCI DSS also requires that stored card data be encrypted.
- Encrypting data “in transit” across private and public networks. Common examples include using the HTTPS protocol for secure online payment transactions, as well as encrypting messages within VPN tunnels. Zero Trust further advocates encrypting data transmitted over your internal networks, since your “perimeter” is presumed to be compromised.
MySQL and MariaDB each support “at rest” and “in transit” encryption modalities. They both 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.
MySQL database-level encryption
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 adds little 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 discussed, 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, which is increasingly advocated as hackers’ ability to crack hashed passwords increases
- 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.
One 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
To avoid exposing sensitive data to potential inspection and exfiltration if your internal network is compromised, or if the data is transiting public networks, you can encrypt the data when it passes between the MySQL client and the server.
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 obligations require it, MySQL lets you 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 block “man-in-the-middle” attacks, side channel attacks and other threats to data in transit, you can encrypt 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 multiple 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, and new use cases like teleworking and privacy compliance becoming the norm, encryption will certainly be used to secure more and more MySQL and MariaDB environments.
If you’d like a “second opinion” on where and how to implement encryption to address your business needs, contact Buda Consulting for a free consultation on our database security assessment process.
If you like this article, please share it with your colleagues and subscribe to our blog to get the latest updates.
Deep Oracle and MS SQL DBA Expertise
Led by Bob Buda, one of the first-ever certified Oracle DBAs, Buda Consulting delivers unmatched experience and commitment. We’ll unleash your data’s true potential and rein in security and compliance risks.