MySQL and MariaDB Encryption Choices for Today’s Use Cases

MySQL and MariaDB Encryption Choices for Today’s Use Cases

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.

SQL Server Always Encrypted—Is It Right for My Data?

lock icon, encryptionChances are your company holds sensitive transactional data like personal, financial or health records. This data is often subject to regulations and is also coveted by cybercriminals. Unfortunately, unless encrypted it is vulnerable to compromise both at rest on the server that stores it, and in transit to/from clients that request it.

For Microsoft SQL Server users, Transparent Data Encryption (TDE) has long been available to protect data at rest in the event that database files or backups are compromised. But TDE still leaves encrypted data vulnerable to a privileged user who can access it via the database.

Further, TDE offers nothing to secure SQL Server data in transit. HTTPS can help, but still leaves data exposed to man-in-the-middle (MITM) and other attacks.

To help mitigate these security weaknesses, SQL Server 2016 introduced the Always Encrypted feature to protect sensitive data at rest, in motion and even in memory. Outside an application client’s connection, the data remains encrypted—across the network, server, storage and database.

With Always Encrypted, only users and applications with access to valid keys can decrypt the data, so MTM attacks, insider threats, etc. are nullified. In addition, Always Encrypted lets you encrypt data at the column level, whereas TDE requires encrypting the entire database.

Who should use Always Encrypted?

New privacy regulations and other compliance and data governance mandates are driving more and more SQL Server users to leverage Always Encrypted. It can be a big help with separating those who own and can view the data from those who manage the data but should otherwise not access it.

For example, Always Encrypted lets you protect Personal Information (PI) that you store in the cloud. Even when the data is outside your direct control, the cloud service provider, third-party DBAs, your on-premises admins and other privileged users can’t access it.

Because it is available with all SQL Server 2016 and newer editions (as of SP1), you can try Always Encrypted without purchasing additional licenses. However, the real investment lies in modifying the client application, which will handle the encryption/decryption outside the SQL Server or Azure SQL Database environment.

Always Encrypted cryptography

Always Encrypted is aptly named: from the standpoint of the database engine the data is literally “always encrypted.” But some queries on encrypted data are still supported, depending on the column-level encryption setting.

You can choose either of two encryption types:

  1. Deterministic encryption, which always generates the same encrypted values. This enables the database engine to perform specific operations (point lookups, grouping, equality joins, and also supports indexing.
  2. Randomized encryption, which is even more secure but makes the data write/display-only. Thus, it can’t be evaluated or used in any database operations and cannot be indexed.

The downside of deterministic encryption is that unauthorized users could potentially extrapolate information about the data in encrypted columns by examining patterns within the encrypted values, especially if the set of possible values is small (e.g., true/false). Randomized encryption generates a less predictable result, but precludes indexing, searching, grouping or joining operations on encrypted columns.

For columns of sensitive data that will be used as search or grouping parameters (e.g., US Social Security Numbers), you’ll need to use deterministic encryption. Randomized encryption can be used for data that isn’t grouped with other records and won’t be used to join tables.

History and enhancements

As noted above, Always Encrypted has been available across all SQL Server and Azure SQL Database editions and SQL Database service tiers since SQL Server 2016 (13.x) SP1. Prior to that, it was only available in the Enterprise and Developer Editions.

Beginning with SQL Server 2019 (15.x), the new Always Encrypted with secure enclaves feature extends the database operations you can perform on encrypted data to include pattern matching, other comparison operators and in-place encryption.

A secure enclave is basically a protected memory area specifically for processing Always Encrypted data within SQL Server. To the rest of the SQL Server engine, and to other processes on the host machine, a secure enclave is a “black box” that shields all code and data inside from outside processes, including debuggers.

To validate a secure enclave before sending it encryption keys, etc., both the client-side driver and SQL Server must contact an external attestation service. The process also relies on “enclave-enabled” column master and encryption keys.

While not without complexities and performance overhead, secure enclaves significantly extend the kinds of operations you can perform on Always Encrypted data.

How Always Encrypted works

Before it can process Always Encrypted data, a client application must be configured to use an approved driver, which will automatically encrypt and decrypt columns of sensitive data. The driver encrypts the data before passing it to the database engine. It also needs to automatically rewrite queries to preserve their semantics. When the database returns query results, the driver transparently decrypts these before storing them.

This works because only the client-side application, not the SQL Server environment, has access to the encryption keys. Other applications can retrieve the encrypted values but can do nothing with them.

To encrypt a column of data with Always Encrypted, you must first generate a column encryption key and a column master key. The former encrypts the data and the latter encrypts the former.

The column encryption key resides on the SQL Server instance, while the database engine stores metadata pointing to the column master key’s location. The column master key itself resides in the Windows Certificate Store, Azure Key Vault, a hardware security module or some other trusted external key store. The database engine never sees or stores either key as plaintext.

Always Encrypted use cases

What are some good reasons to try Always Encrypted? Here a few of the top use cases:

  • To improve security of data in transit beyond what SSL can provide.
  • To meet the demands of regulated industries like financial services and telecommunications around protecting Personally Identifiable Information (PII) like credit card numbers, customer names/addresses, etc.
  • To improve security when outsourcing SQL Server DBA services, Always Encrypted ensures separation of duties between third-party DBAs and in-house application administrators.
  • To improve security in scenarios where on-premises client instances need to access sensitive data stored on Microsoft Azure. Because the column master key resides on-premises in a trusted key store, Microsoft admins cannot access the cloud-based data.
  • To improve security in situations where both the client application and database environment are hosted on Azure (as SQL Database or SQL Server instances running in a virtual machine). In this case, both the data and keys are potentially exposed to Microsoft admins on the cloud platform hosting the client tier. However, the data is still always encrypted in the cloud database.

In short, Always Encrypted makes the most sense for protecting PII and other types of data that you need to store, but don’t want to search on or display to application users.

Limitations with Always Encrypted

As noted above, Always Encrypted limits the kinds of operations you can perform on data; e.g., copying data between columns using UPDATE, SELECT INTO, etc. It also restricts the data types you can encrypt. Some of the excluded types include XML, IMAGE, TEXTNTEXT and GEOGRAPHY, as well as user-defined data types.

If you need to query Always Encrypted data from multiple applications, such as reporting tools and data warehouses, you will need to give them access to the appropriate database drivers and decryption certificates. This can be more challenging if you need to replicate encrypted data to other SQL Server instances.

Performance can also be a limiting factor with using Always Encrypted. Encryption/decryption obviously involve compute and storage overhead that will impact both the duration of queries and the volume of storage your application needs. You’ll probably want to do some testing with your specific CPU, RAM and disk setup to gauge the production impact.

Next steps

Want to talk with a database security expert before you dive into Always Encrypted? Contact Buda Consulting to schedule a free consultation.

In-Place Oracle Database Encryption with Zero Downtime

Have you been wanting to encrypt your Oracle database “since forever,” but feel like you just can’t afford the downtime? If a lot of data is involved, taking it all offline and encrypting it could be very time-consuming. So you’ve been putting the process off, while keeping your fingers crossed that your company’s network security will somehow protect you from a data breach and associated legal, compliance and reputational impacts. 

But did you know that you can now encrypt existing tablespaces in-place, either online or offline in Oracle? In case you missed it, Oracle Enterprise Edition version 12.2 (released in 2017) added Transparent Data Encryption (TDE), a much-needed feature that enables you to encrypt an existing database while it remains online. 

If you’ve been running an earlier Oracle version and haven’t seen a compelling reason to update, TDE could be it. This capability is a game-changer for those who want to “do the right thing” and encrypt their data at rest, but haven’t wanted to incur the downtime.

At a high level, here is how TDE works:

    • First, encrypt the system tablespaces (these must be done separate from user tablespaces)
    • Next, encrypt the user tablespaces, one at a time. 
    • Finally, drop and recreate any temporary tablespaces (these cannot be converted online)

That’s basically all there is to it! There are some technical issues that your DBA and/or security group will need to work out, such as key management and disk space. (You must have enough available disk space during the conversion to duplicate your largest tablespace.)

Of course, you need to back up your entire database before you start the encryption process. If you decide to tackle encryption gradually, then just back up each tablespace before you convert it.

Taking the important step of encrypting your sensitive data at rest will significantly improve your security posture.

So what are you waiting for? Get encrypting!

To schedule a free consultation on your database security, including encryption requirements, contact Buda Consulting.

SQL Server Data Encryption Options

SQL Server Data Encryption Options

These days it’s only a matter of time before our firewalls or access controls are breached. Then what?

In a multi-tiered security strategy, data encryption is a critical next line of defense. Data encryption is also mandated by a number of widely applicable regulations, including PCI-DSS and GDPR.

Fortunately for SQL Server users, Microsoft has developed a range of capabilities that enable you to encrypt database files at rest, in transit, in backups, when being accessed and even “always.” This post will give you an overview the five types of encryption that SQL Server offers.

SSL Transport Encryption For Data In Transit

Similar to how web applications secure traffic between the server and the browser, you can configure SQL Server to use Secure Sockets Layer (SSL) to encrypt data in transit between the server and the client.

This capability is available across all supported versions and all editions of SQL Server. It’s a great way to thwart so-called “man in the middle”/proxy attacks because it makes network traffic almost impossible to read.

For more information, including how to install a certification a SQL Server to support SSL, see this Microsoft Support page.

Backup Encryption

SQL Server allows you to encrypt SQL backups at the whole file level. Available for both Standard and Enterprise editions in SQL Server 2014 and newer, this is a powerful security measure as it protects backups even at offsite locations.

This feature gives you a choice of encryption algorithms and can use either a certificate or asymmetric key. You can also use backup encryption and Transparent Data Encryption (TDE, see below) at the same time (but with separate certificates/keys).

For more information on implementing cell-level encryption, see this Microsoft Docs page.

Transparent Data Encryption (TDE)

Available since SQL Server 2008 for Enterprise editions only, TDE lets you encrypt SQL data “at rest” within the files on disk. This protects the physical media—including the entire database, log files and any backups or snapshots—from being read in the event of unauthorized access to the media. (Data in unencrypted files can be accessed simply by restoring the files to another server.)

TDE is “transparent” in the sense that no changes are required for client or server applications to use a TDE-encrypted database. When SQL Server mounts an encrypted data file, it uses a database encryption key (DEK) to decrypt the data during use and then re-encrypts it again before writing it back to the file. A limitation of this approach is that data encrypted with TDE is unencrypted and potentially vulnerable both in memory and over the network.

For more information on implementing TDE see this Microsoft Docs page.

Cell-Level Encryption

SQL Server’s Cell-Level Encryption lets you encrypt specific columns within a database that contain sensitive data; e.g., credit card numbers, social security numbers, passwords, etc. This feature is available in all SQL Server editions.

Unlike with TDE, when data encrypted with Cell-Level Encryption is selected in a query, it remains encrypted by default, even in memory. However, all it takes to decrypt it is a call to the decryptbykey function in the query. Further, the need to use a function call for decryption means code changes in existing applications and queries.

For more information on implementing cell-level encryption, see this Microsoft Docs page.

Always Encrypted

Always Encrypted is a new (since SQL Server 2016) way to do column-level encryption for client applications using up-to-date data libraries. Always Encrypted encrypts data transparently at the client via the data connection layer, with no code changes required (though it requires a driver on client systems).

Data thus secured remains encrypted in transit, in memory and at rest—making it the only option for securing data from misuse by even the most privileged SQL users, like sysadmins. This makes it a good choice for applications that require separation of data owners and managers.

However, since SQL Server isn’t doing the encrypting, many functions won’t work with this approach. In particular, you can’t sort, index, look for string fragments or do calculations on data that’s encrypted.

For more information on implementing cell-level encryption, see this Microsoft Docs page.

In Conclusion

Microsoft has a strong focus on security, and this is evident in the wide range of options available to SQL Server users. But every organization’s needs are different, and there is no one-size-fits-all approach to implementing SQL Server encryption. Your SQL Server version and licensing scenario, application requirements, business processes, performance needs, regulatory environment and security risk profile will all impact your path to implementing encryption.

For expert guidance on how best to implement encryption in your database environment, including help with configuration and key management, contact Buda Consulting.

Oracle Data Encryption Options

Oracle Data Encryption Options

Oracle offers various authentication and audit features to protect data from unauthorized access. But what about data at rest in operating system files, backups or other storage media?

Protect Oracle Data At Rest With TDE

To protect data at rest, Oracle offers Transparent Data Encryption (TDE). With TDE you can encrypt sensitive data so that it is unreadable if the file it is stored in is exfiltrated or breached.

Data you encrypt with TDE is “transparently” decrypted when it is accessed by authorized users and applications. That is, decryption takes place without users even being aware that data is encrypted. Likewise, applications that process sensitive data can offer data encryption via TDE with little or no code changes.

Why use TDE? It helps ensure that your sensitive data is secure, supports compliance with a wide range of regulations like Sarbanes-Oxley (SOX), HIPAA and PCI, and can simplify your overall encryption/decryption policy and operations.

Another benefit of TDE is that it is pretty fine-grained. You can encrypt data at the column level or the tablespace level. Column-level encryption is perfect for confidential data like social security numbers or credit card numbers that are stored in table columns.

When you encrypt a tablespace, all objects created in that tablespace are encrypted automatically. Tablespace level encryption works well for tables that store sensitive data in multiple columns, or for when you want to protect an entire table and not just individual columns. It’s also handy anytime you want to avoid doing a nitty-gritty analysis of each table column to determine which ones require encryption.

To enable decryption and prevent unauthorized decryption, TDE uses a two-tiered, key-based encryption architecture. It stores encryption keys in a keystore, a hardware or software security module separate from the database. You can centrally (and automatically) manage these keystores using Oracle Key Vault.

To encrypt a tablespace, TDE uses an externally stored master key to encrypt the TDE tablespace encryption key, which is used to encrypt/decrypt tablespace data. For column-level encryption, Oracle transparently accesses a TDE master encryption key to encrypt or decrypt the TDE table key, which then encrypts/decrypts column-level data in the table.

Encryption Best Practices

Of course, your encryption strategy should be integrated with your overall information security program. Best-practice security tips related to encryption include:

      • Start by determining how sensitive the data is. Data that requires the strongest protection can be encrypted using the AES256 algorithm. Conversely, you can encrypt less sensitive data in several ways that offer performance benefits.
      • You also need to determine your approach to keystore protection based on data sensitivity. Options range from auto-login software keystores to hardware keystores. A separate keystore for TDE only is ideal if possible.
      • To limit damage from compromised admin credentials or insider threats, consider assigning separate security admins for TDE and for the database(s).
      • Backup your sensitive data using protected backup procedures.
      • Be aware that column-level encrypted data is decrypted during expression evaluation and could potentially be accessed in the associated on-disk swap file.
      • Also be aware that your Oracle data files could contain plaintext fragments (aka “ghost records” that were deleted logically from the table but still exist physically on-disk. These could potentially be accessed similarly to finding data on-disk after it has been deleted at the operating system level.

For more information on TDE, see the Oracle Advanced Security Guide online.

For expert help and guidance with encryption, backup/recovery, high availability and other business continuity and security concerns, contact Buda Consulting for a security risk assessment—the first step to finding and closing the gaps in your database security.