Aug
23
2018
--

Comparing Data At-Rest Encryption Features for MariaDB, MySQL and Percona Server for MySQL

Encryption at rest MariaDB MySQL Percona Server

Encryption at rest MariaDB MySQL Percona ServerProtecting the data stored in your database may have been at the top of your priorities recently, especially with the changes that were introduced earlier this year with GDPR.

There are a number of ways to protect this data, which until not so long ago would have meant either using an encrypted filesystem (e.g. LUKS), or encrypting the data before it is stored in the database (e.g. AES_ENCRYPT or other abstraction within the application). A few years ago, the options started to change, as Alexander Rubin discussed in MySQL Data at Rest Encryption, and now MariaDB®, MySQL®, and Percona Server for MySQL all support encryption at-rest. However, the options that you have—and, indeed, the variable names—vary depending upon which database version you are using.

In this blog post we will take a look at what constitutes the maximum level of at-rest encryption that can be achieved with each of the latest major GA releases from each provider. To allow a fair comparison across the three, we will focus on the file-based key management; keyring_file plugin for MySQL and Percona Server for MySQL along with file_key_management plugin for MariaDB.

MariaDB 10.3

The MariaDB team take the credit for leading the way with at-rest encryption, as most of their features have been present since the 10.1 release (most notably the beta release of 10.1.3 in March 2015). Google donated the tablespace encryption, and eperi donated per-table encryption and key identifier support.

The current feature set for MariaDB 10.3 comprises of the following variables:

Maximising at-rest encryption with MariaDB 10.3

Using the following configuration would give you maximum at-rest encryption with MariaDB 10.3:

plugin_load_add = file_key_management
file_key_management_filename = /etc/mysql/keys.enc
file_key_management_filekey = FILE:/etc/mysql/.key
file_key_management_encryption_algorithm = aes_cbc
innodb_encrypt_log = ON
innodb_encrypt_tables = FORCE
Innodb_encrypt_threads = 4
encrypt_binlog = ON
encrypt_tmp_disk_tables = ON
encrypt_tmp_files = ON
aria_encrypt_tables = ON

This configuration would provide the following at-rest protection:

  • automatic and enforced InnoDB tablespace encryption
  • automatic encryption of existing tables that have not been marked with
    ENCRYPTED=NO
  • 4 parallel encryption threads
  • encryption of temporary files and tables
  • encryption of Aria tables
  • binary log encryption
  • an encrypted file that contains the main encryption key

You can read more about preparing the keys, as well as the other key management plugins in the Encryption Key Management docs.

There is an existing bug related to encrypt_tmp_files (MDEV-14884), which causes the use of

mysqld --help --verbose

 to fail, which if you are using the official MariaDB Docker container for 10.3 will cause you to be unable to keep mysqld up and running. Messages similar to these would be visible in the Docker logs:

ERROR: mysqld failed while attempting to check config
command was: "mysqld --verbose --help --log-bin-index=/tmp/tmp.HDiERM4SPx"
2018-08-15 13:38:15 0 [Note] Plugin 'FEEDBACK' is disabled.
2018-08-15 13:38:15 0 [ERROR] Failed to enable encryption of temporary files
2018-08-15 13:38:15 0 [ERROR] Aborting

N.B. you should be aware of the limitations for the implementation, most notably log tables and files are not encrypted and may contain data along with any query text.

One of the key features supported by MariaDB that is not yet supported by the other providers is the automatic, parallel encryption of tables that will occur when simply enabling

innodb_encrypt_tables

 . This avoids the need to mark the existing tables for encryption using

ENCRYPTED=YES

 , although at the same time it also does not automatically add the comment and so you would not see this information. Instead, to check for encrypted InnoDB tables in MariaDB you should check

information_schema.INNODB_TABLESPACES_ENCRYPTION

 , an example query being:

mysql> SELECT SUBSTRING_INDEX(name, '/', 1) AS db_name,
   ->   SUBSTRING_INDEX(name, '/', -1) AS db_table,
   ->   COALESCE(ENCRYPTION_SCHEME, 0) AS encrypted
   -> FROM information_schema.INNODB_SYS_TABLESPACES
   -> LEFT JOIN INNODB_TABLESPACES_ENCRYPTION USING(name);
+---------+----------------------+-----------+
| db_name | db_table             | encrypted |
+---------+----------------------+-----------+
| mysql   | innodb_table_stats   |      1    |
| mysql   | innodb_index_stats   |      0    |
| mysql   | transaction_registry |      0    |
| mysql   | gtid_slave_pos       |      0    |
+---------+----------------------+-----------+

As can be inferred from this query, the system tables in MariaDB 10.3 are still predominantly MyISAM and as such cannot be encrypted.

MySQL

Whilst the enterprise version of MySQL has support for a number of data at-rest encryption features as of 5.7, most of them are not available to the community edition. The latest major release of the community version sees the main feature set comprise of:

The enterprise edition adds the following extra support:

Maximising at-rest encryption with MySQL 8.0

Using the following configuration would give you maximum at-rest encryption with MySQL 8.0:

early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql/keyring
innodb_redo_log_encrypt=ON
innodb_undo_log_encrypt=ON

This configuration would provide the following at-rest protection:

  • optional InnoDB tablespace encryption
  • redo and undo log encryption

You would need to create new, or alter existing tables with the

ENCRYPTION=Y

 option, which would then be visible by examining

information_schema.INNODB_TABLESPACES

 , an example query being:

mysql> SELECT TABLE_SCHEMA AS db_name,
   ->    TABLE_NAME AS db_table,
   ->    CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%' AS encrypted
   -> FROM information_schema.INNODB_TABLESPACES ts
   -> INNER JOIN information_schema.TABLES t ON t.TABLE_SCHEMA = SUBSTRING_INDEX(ts.name, '/', 1)
   ->                                        AND t.TABLE_NAME = SUBSTRING_INDEX(ts.name, '/', -1);
+---------+-----------------+-----------+
| db_name | db_table        | encrypted |
+---------+-----------------+-----------+
| sys     | sys_config      |     1     |
+---------+-----------------+-----------+

N.B. You are able to encrypt the tablespaces in 5.7, in which case you should use

information_schema.INNODB_SYS_TABLESPACES

 as the internal system views on the data dictionary were renamed (InnoDB Changes).

Unfortunately, whilst all of the tables in the mysql schema use the InnoDB engine (except for the log tables), you cannot encrypt them and instead get the following error:

ERROR 3183 (HY000): This tablespace can't be encrypted.

Interestingly, you are led to believe that you can indeed encrypt the

general_log

 and

slow_log

 tables, but this is in fact a bug (#91791).

Percona Server for MySQL

Last, but not least we have Percona Server for MySQL, which, whilst not completely matching MariaDB for features, is getting very close. As we shall see shortly, it does in fact have some interesting differences to both MySQL and MariaDB.

The current feature set for 5.7, which does indeed exceed the features provided by MySQL 5.7 and for the most part 8.0, is as follows:

Maximising at-rest encryption with Percona Server for MySQL 5.7

Using the following configuration would give you maximum at-rest encryption with Percona Server 5.7:

early-plugin-load=keyring_file.so
keyring_file_data=/var/lib/mysql-keyring/keyring
innodb_temp_tablespace_encrypt=ON
innodb_encrypt_online_alter_logs=ON
innodb_encrypt_tables=FORCE
encrypt_binlog=ON
encrypt_tmp_files=

This configuration would provide the following at-rest protection:

  • automatic and enforced InnoDB tablespace encryption
  • encryption of temporary files and tables
  • binary log encryption
  • encryption when performing online DDL

There are some additional features that are due for release in the near future:

  • Encryption of the doublewrite buffer
  • Automatic key rotation
  • Undo log and redo log encryption
  • InnoDB system tablespace encryption
  • InnoDB tablespace and redo log scrubbing
  • Amazon KMS keyring plugin

Just like MySQL, encryption of any existing tables needs to be specified via

ENCRYPTION=Y

 via an

ALTER

, however new tables are automatically encrypted. Another difference is that in order to check which tables are encrypted you can should the flag set against the tablespace in

information_schema.INNODB_SYS_TABLESPACES

, an example query being:

mysql> SELECT SUBSTRING_INDEX(name, '/', 1) AS db_name,
   ->    SUBSTRING_INDEX(name, '/', -1) AS db_table,
   ->    (flag & 8192) != 0 AS encrypted
   -> FROM information_schema.INNODB_SYS_TABLESPACES;
+---------+---------------------------+-----------+
| db_name | db_table                  | encrypted |
+---------+---------------------------+-----------+
| sys     | sys_config                |      1    |
| mysql   | engine_cost               |      1    |
| mysql   | help_category             |      1    |
| mysql   | help_keyword              |      1    |
| mysql   | help_relation             |      1    |
| mysql   | help_topic                |      1    |
| mysql   | innodb_index_stats        |      1    |
| mysql   | innodb_table_stats        |      1    |
| mysql   | plugin                    |      1    |
| mysql   | servers                   |      1    |
| mysql   | server_cost               |      1    |
| mysql   | slave_master_info         |      1    |
| mysql   | slave_relay_log_info      |      1    |
| mysql   | slave_worker_info         |      1    |
| mysql   | time_zone                 |      1    |
| mysql   | time_zone_leap_second     |      1    |
| mysql   | time_zone_name            |      1    |
| mysql   | time_zone_transition      |      1    |
| mysql   | time_zone_transition_type |      1    |
| mysql   | gtid_executed             |      0    |
+---------+---------------------------+-----------+

Here you will see something interesting! We are able to encrypt most of the system tables, including two that are of significance, as they can contain plain text passwords:

+---------+-------------------+-----------+
| db_name | db_table          | encrypted |
+---------+-------------------+-----------+
| mysql   | servers           |      1    |
| mysql   | slave_master_info |      1    |
+---------+-------------------+-----------+

In addition to the above, Percona Server for MySQL also supports using the opensource HashiCorp Vault to host the keyring decryption information using the keyring_vault plugin; utilizing this setup (provided Vault is not on the same device as your mysql service, and is configured correctly) gains you an additional layer of security.

You may also be interested in my earlier blog post on using Vault with MySQL, showing you how to store your credentials in a central location and use them to access your database, including the setup and configuration of Vault with Let’s Encrypt certificates.

Summary

There are significant differences both in terms of features and indeed variable names, but all of them are able to provide encryption of the InnoDB tablespaces that will be containing your persistent, sensitive data. The temporary tablespaces, InnoDB logs and temporary files contain transient data, so whilst they should ideally be encrypted, only a small section of data would exist in them for a finite amount of time which is less of a risk, albeit a risk nonetheless.

Here are the highlights:

MariaDB 10.3 MySQL 8.0 Percona Server 5.7
encrypted InnoDB data Y Y Y
encrypted non-InnoDB data Aria-only N N
encrypted InnoDB logs Y Y TBA
automatic encryption Y N Y
enforced encryption Y N Y
automatic key rotation Y N TBA
encrypted binary logs Y N Y
encrypted online DDL ? N Y
encrypted keyring Y Enterprise-only N
mysql.slave_master_info N N Y
mysql.servers N N Y
Hashicorp Vault N N Y
AWS KMS Y Enterprise-only TBA

 

Extra reading:

 

The post Comparing Data At-Rest Encryption Features for MariaDB, MySQL and Percona Server for MySQL appeared first on Percona Database Performance Blog.

Jul
06
2018
--

Another Day, Another Data Leak

another day another data leak Exactis

another day another data leak ExactisIn the last few days, there has been information released about yet another alleged data leak, placing in jeopardy “…[the] personal information on hundreds of millions of American adults, as well as millions of businesses.” In this case, the “victim” was Exactis, for whom data collection and data security are core business functions.

Some takeaways from Exactis

Please excuse the pun! In security, we have few chances to chuckle. In fact, as a Security Architect, I sigh deeply when I read about this kind of issue. Firstly, it’s preventable. Secondly, I worry that if an organization like Exactis is not getting it right, what chance the rest of the world?

As the Wired article notes the tool https://shodan.io/ can be revealing and well worth a look. For example, you can see there are still MANY elasticSearch systems exposed to the public internet here. Why not use shodan to check what everyone else in the world can see openly on your systems ?

Securing databases

Databases in themselves do not need to be at risk, as long as you take the necessary precautions. We discussed this in this blog post that I co-authored last year.

In this latest alleged gaffe, as far as I can discern, had the setup made use of iptables or a similar feature then the breach could not have occurred.

With immaculate timing, my colleague Marco Tusa wrote a post last month on how to set up iptables for Percona XtraDB Cluster, and if you are not sure if or how that applies to your setup, it is definitely worth a read. In fact, you can access all of our security blog posts if you would like some more pointers.

Of course, security does not stop with iptables. Application developers should already be familiar with the need to avoid SQL injection, and there is a decent SQL injection prevention cheat sheet here, offered by The Open Web Application Security Project (OWASP). Even if you don’t fully understand the technical details, a cheat sheet like this might help you to ask the right questions for your application.

MySQL resources

For a more in-depth look at MySQL security, I have two talks up on YouTube. The first of these is a twenty-minute presentation on hardening MySQL and the second on web application security and why you really should review yours. You could also check out our recorded webinar Security and Encryption in the MySQL world presented by Dimitri Vanoverbeke.

MongoDB resources

Of course, security challenges are not unique to SQL databases. If you are a MongoDB user, this webinar MongoDB Security: Making things secure by default might be of interest to you. Or perhaps this one on using LDAP Authentication with MongoDB? Adamo Tonete presents both of these webinars.

For a more widely applicable view, you could try Colin Charles’ recent webinar too.

There are always consequences

As Exactis are no doubt discovering, managing the fallout from such a breach is a challenge. If you are not sure where you stand on security, or what you can do to improve your situation, then audit services such as those we offer could prove to be a valuable investment.

Finally, some of you will be lucky enough to have someone dedicated to IT security in your organizations. Next time you see them, instead of avoiding their steely stare, why not invite them for a coffee* and a chat? It could be enlightening!

*Beer or scotch is also almost always accepted too…

The post Another Day, Another Data Leak appeared first on Percona Database Performance Blog.

Nov
07
2017
--

Proofpoint acquires Cloudmark for $110M in cybersecurity consolidation play

 As malicious groups continue to become more sophisticated in their hacking techniques, cybersecurity efforts are attempting to expand in their reach, and that is leading to some consolidation in the field. Today, cybersecurity firm Proofpoint — which provides SaaS products to protect businesses’ email, social media and other services — announced that it would pay $110 million… Read More

Mar
07
2016
--

Fear and loathing at RSA: Hacking, security and the limits of protection

RSA Conference banner. People are trying to steal your credentials or trick you into giving them important information. They are exploiting unpatched operating systems, locating security holes in applications and hacking into unprotected hardware. You’re not paranoid, you’re told. Hackers, con artists and social engineers really are out to get you, and frankly you’re pretty helpless to stop them.… Read More

Feb
09
2016
--

Digital Shadows Gets $14M To Keep Growing Its Digital Risk Scanning Service

data analysis research shutterstock UK cyber security startup Digital Shadows, which sells a SaaS service to businesses wanting to monitor and manage potential risks by keeping tabs on activity related to their digital footprint — has closed a $14 million Series B funding round, led by Trinity Ventures. As part of the investment, Trinity’s Fred Wang has joined the Digital Shadows board. Read More

Dec
23
2014
--

File carving methods for the MySQL DBA

File carving methods for the MySQL DBAThis is a long overdue blog post from London’s 44con Cyber Security conference back in September. A lot of old memories were brought to the front as it were; the one I’m going to cover in this blog post is: file carving.

So what is file carving? despite the terminology it’s not going to be a full roast dinner; unless you have an appetite for data which as you’re here I’m assuming you have.

The TL;DR of “what is file carving” is taking a target blob of data (often a multi GB / TB file) and reducing it in to targeted pieces of data, this could be for instance grabbing all the jpeg images in a packet capture / mysqldump; or pulling that single table/schema out of a huge mysqldump with –all-databases (if you’re not using mydumper you really should it avoids issues like this!) aka “Sorting the wheat from the chaff”.

Let’s take for example at the time of writing this post I am looking to extract a single schema out of one such mysqldump –all-database file of around 2GB (2GB of course isn’t large however it’s large enough to give a practical example; the methods for larger files are of course the same). So where to start?

You’ll need the following tools installed:

  1. xxd (you can substitute xxd for od, hexer or any other hex editing / viewing tool you are comfortable with, just make sure it can handle very large files)
  2. grep

Let’s carve out the mysql schema

dbusby@kali:~$ xxd yourdumpfile.sql | grep 'mysql' -B5 | grep 'ASE' -A2 -B2
00003c0: 6e74 2044 6174 6162 6173 653a 2060 6d79 nt Database: my
00003d0: 7371 6c60 0a2d 2d0a 0a43 5245 4154 4520 sql
.--..CREATE
00003e0: 4441 5441 4241 5345 202f 2a21 3332 3331 DATABASE /*!3231
00003f0: 3220 4946 204e 4f54 2045 5849 5354 532a 2 IF NOT EXISTS*
0000400: 2f20 606d 7973 716c 6020 2f2a 2134 3031 / mysql /*!40

Wonderful so we have some hex representation of the sql dumpfile why on earth do we want the hex? we need to define our offsets. In short our offsets are the position of the start and end of the chunk we intend to carve from the file.

From the above our start offset is 00003d9 at the start of CREATE DATABASE; for those unfamiliar with hexdump outputs I recommend looking at the tool hexer a vi like tool and pressing v to enter visual selection mode select a few characters and you’ll not something as follows “visual selection:  0x000003d9 – …”.

You can of course work out the range visually from the above, 00003d0 is the start of the line, each alphanumeric pair is a single byte the byte offset notation is hexedecimal 0,1,2,3,4,5,6,7,8,9,a,b,c,d,e,f.

00003d0: 7371 6c60 0a2d 2d0a 0a43 5245 4154 4520 sql.--..CREATE
00003d0 == s, 00003d1 == q, 00003d2 == l And so on, we can easily verify this using xxd
dbusby@kali:~$ xxd -s 0x3d9 yourdumpfile.sql | head -n3
00003d9: 4352 4541 5445 2044 4154 4142 4153 4520 CREATE DATABASE
00003e9: 2f2a 2133 3233 3132 2049 4620 4e4f 5420 /*!32312 IF NOT
00003f9: 4558 4953 5453 2a2f 2060 6d79 7371 6c60 EXISTS*/
mysql

right so now we need the end offset, as above we establish a search pattern as the schema data we're carving is in the midst of a larger file we can look for the start of the dump for the next schema.


dbusby@kali:~$ xxd -s 0x3d9 yourdumpfile.sql | grep '--' -A5 | grep C -A2 -B2 | less
...
0083b19: 2043 7572 7265 6e74 2044 6174 6162 6173 Current Databas
0083b29: 653a 2060 7065 7263 6f6e 6160 0a2d 2d0a e:
nextschema`.--.
...

I’ve piped into less here as there were many matches to the grep patterns.

From the above we can see a potential offset of 0x83b19 however we want to “backtrack” a few bytes to before the — comment start.


dbusby@kali:~$ xxd -s 0x83b14 yourdumpfile.sql | head -n1
0083b14: 2d2d 0a2d 2d20 4375 7272 656e 7420 4461 --.-- Current Da

Excellent we have our offsets starting at 0x3d9 ending at 0x83b14 we need to now convert base16 (hexidecimal) into base10 fortunatly we can do this usinc the bc utility very easily however we will need to fully expand and make upper case our offsets.


dbusby@kali:~$ echo 'ibase=16;00003D9' | bc
985
dbusby@kali:~$ echo 'ibase=16;0083B14' | bc
539412
dbusby@kali:~$ echo '539412-985' | bc
538427
dbusby@kali:~$ dd if=yourdumpfile.sql of=mysql.sql skip=985 bs=1 count=538427
538427+0 records in
538427+0 records out
538427 bytes (538 kB) copied, 1.08998 s, 494 kB/s

Let’s discuss this a little; what we have done here is convert our start offset to a base10 count of bytes to offset by when using dd (skip=985) we then convert the end offset to its base10 byte position, and by removing the startoffset base10 value this gives us the size of the chunk we are carving.

We now put this into a dd command line, and voila! we have a mysql.sql file which contains only the mysqldump data.

I hope this post helps somewhat to demystify file carving; the above techniques can be applied to any for of file carving need and is not limited only to mysql files.

The post File carving methods for the MySQL DBA appeared first on MySQL Performance Blog.

Jul
01
2014
--

Former Israeli Intelligence Officials Get $2.5M To Seed Cyber Security Company Hexadite

Old fashioned padlock Cyber security startup Hexadite, launched by three former Israeli intelligence officials, came out of stealth today and announced $2.5 million in seed funding. The money comes from YL Ventures with a contribution from former Microsoft Vice president Moshe Lichtman, a general partner at Israeli venture capital firm, Israel Growth Partners. Read More

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com