MySQL and MariaDB Encryption Choices for Today’s Use Cases

MySQL and MariaDB Encryption Choices for Today’s Use Cases

by | Aug 10, 2021 | Database Security, MariaDB, MySQL

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:

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

This post explains how to setup, configure and test database-level encryption in MariaDB. For an overview of MariaDB’s database-level encryption, see this page in the knowledgebase.

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.

Conclusion

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.

MySQL Parser Deficiencies

MySQL Parser Deficiencies

by | Jul 5, 2013 | Best Practices, Database, MySQL

MySQL is a very popular database for many applications. But the parser has some important deficiencies that developers need to be aware of. The parser is inconsistent and not robust in the way that it enforces statement syntax. This may lead to ambiguity and application defects that are very difficult to find. I include one example below and link to another blog article that discusses another.

Inadequate Group By Parsing

The MySQL group by function does not require that all non-aggregated elements in the select list be present in the group by clause. This causes an ambiguity because if a given element in the select list is neither present in the group by clause, nor aggregated in the select list, then an arbitrary value is chosen (it actually seems to be the first value but I don’t know if that is always the case).  Consider this example table that contains the number of games a set of players has played in each of four seasons.

We can see that each player has played 100 games in each of the four seasons.

Now consider this simple query to get a sum of all the games that the player has played across all seasons:

As expected, we can see that each player has played 400 games.

But watch what happens if a novice developer places the season number field in the select list without placing it in the group by clause as well:

If the developer does not realize his error, he will think that each player played 400 games in season 1!

The parser should have prevented the query from being executed with the season_number in the select list when it was neither aggregated in some form or present in the group by clause.

This type of error is very difficult to find because the resulting data looks plausible unless you really know the data.

Function Syntax Inconsistency

The next example is related to the way that the parser enforces one statement syntax vs another. This involves the tolerance of whitespace (line breaks) when specifying the sum function vs the format function. This inconsistency does not concern me as much as the group by problem because it does result in an error that is visible to the developer, but it does illustrate a lack of consistency and robustness in the parser.

As MacLoghLainns Weblog describes, the SUM function requires that the opening parentheses be on the same line as the SUM keyword, while the FORMAT function does not have that restriction. View the blog article for more details.

Please reply back with comments or other issues that you have found that might help others avoid unexpected problems in their MySQL applications.

Visit www.budaconsulting.com for more information about Buda Consulting.

Query Rewrite: What is MySQL Doing To My Queries?

by | Nov 30, 2011 | MySQL

The Impact of Query Rewrite on MySQL Query Optimization

When processing a query, every relational database vendor takes a number of similar steps to parse, validate, and optimize a query. MySQL does the following:

  1. Validates the query syntax — does it use valid operators, are the clauses in the proper order, etc.
  2. Validates the query semantics — are the the objects (tables, views, columns) valid database objects in the database, etc.
  3. Transforms the query — rewrites the query so the optimizer can more easily choose the best path.
  4. Optimizes the query — determines the best execution path based on a large number of factors including statistics about the table and index data.

When analyzing a database performance problem, it helps to have an understanding of what is happening during step 3 and 4 above.

Occasionally I find a blog article that does a great job of explaining one these processes for a particular database vendor and I would like to share one that I found today. This excellent post was written by Guilhem Bichot and does a great job of illustrating the transformation (query rewrite) step and the impact of this step on the execution path selection.

This post describes the process in MySQL but the principles are similar for any database vendor.

Note that the example that Guilhem uses shows how the database itself may introduce a query hint while rewriting the query. I have mentioned in the past that I avoid hints because over time the usefulness and necessity of the hint may change and it may indeed hurt you. Of course, since the query rewrite process is executed in real time it is not subject to this problem.