An Introduction to MariaDB’s Data at Rest Encryption (DARE) – Part 2

Okay, so you’ve read the first post on enabling MariaDB’s data at rest encryption, and now you are ready to create an encrypted table.

And just to get it out of the way for those interested, you can always check your encrypted (and non-encrypted) table stats via:

SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION;

ENCRYPTION_SCHEME=1 means the table is encrypted and ENCRYPTION_SCHEME=0 means they are not.

But let’s get into some specific examples.

I find the following 4 tables interesting, as the first 3 essentially all create the same table, and the 4th shows how to create a non-encrypted table once you have encryption enabled.

CREATE TABLE t10 (id int) ENGINE=INNODB;
CREATE TABLE t11 (id int) ENGINE=INNODB ENCRYPTED=YES;
CREATE TABLE t12 (id int) ENGINE=INNODB ENCRYPTED=YES ENCRYPTION_KEY_ID=18;
CREATE TABLE t13 (id int) ENGINE=INNODB ENCRYPTED=NO;
MariaDB> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'dare/t1%';
+-------+----------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+
| SPACE | NAME     | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID |
+-------+----------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+
|    48 | dare/t10 |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |
|    49 | dare/t11 |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |
|    50 | dare/t13 |                 0 |                  0 |               0 |                   1 |                     NULL |                         NULL |              1 |
|    51 | dare/t12 |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |             18 |
+-------+----------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+

So when configuered as above, then we see t10, t11, and t12 were all encrypted no matter whether we specified ENCRYPTED=YES in the CREATE TABLE. We also see t10 and t11 used KEY_ID #1 since we did not specify ENCRYPTION_KEY_ID in the CREATE TABLE, whereas t12 did use KEY_ID #18 since we specified ENCRYPTION_KEY_ID=18 in the CREATE TABLE.

We see that t13 was not encrypted, since we did specify ENCRYPTED=NO in the CREATE TABLE. This is possible when you use innodb_file_per_table, the default.

If we attempt to create a table specifying ENCRYPTION_KEY_ID to a value that is not in the keys.txt file, then you’ll see an error like the following:

MariaDB> CREATE TABLE t15 (id int) ENGINE=INNODB ENCRYPTED=YES ENCRYPTION_KEY_ID=17;
ERROR 1005 (HY000): Can't create table `dare`.`t15` (errno: 140 "Wrong create options")

You will see the same if you try to ALTER an existing table from one ENCRYPTION_KEY_ID to another that does not exist.

Examples with above settings:

Let’s encrypt a non-encrypted table (t13) to be encrypted:

MariaDB> SELECT NAME, ENCRYPTION_SCHEME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'dare/t13';
+----------+-------------------+
| NAME     | ENCRYPTION_SCHEME |
+----------+-------------------+
| dare/t13 |                 0 |
+----------+-------------------+

MariaDB> ALTER TABLE t13 ENCRYPTED=YES;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB> SELECT NAME, ENCRYPTION_SCHEME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'dare/t13';
+----------+-------------------+
| NAME     | ENCRYPTION_SCHEME |
+----------+-------------------+
| dare/t13 |                 1 |
+----------+-------------------+

Now let’s perform the reverse, un-encrpyt an encrypted table:

MariaDB> SELECT NAME, ENCRYPTION_SCHEME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'dare/t13';
+----------+-------------------+
| NAME     | ENCRYPTION_SCHEME |
+----------+-------------------+
| dare/t13 |                 1 |
+----------+-------------------+

MariaDB> ALTER TABLE t13 ENCRYPTED=NO;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB> SELECT NAME, ENCRYPTION_SCHEME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'dare/t13';
+----------+-------------------+
| NAME     | ENCRYPTION_SCHEME |
+----------+-------------------+
| dare/t13 |                 0 |
+----------+-------------------+

Let’s convert an ENCRYPTION_KEY_ID:

MariaDB> SELECT NAME, CURRENT_KEY_ID FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'dare/t11';
+----------+----------------+
| NAME     | CURRENT_KEY_ID |
+----------+----------------+
| dare/t11 |              1 |
+----------+----------------+

MariaDB [dare]> ALTER TABLE t11 ENCRYPTION_KEY_ID=18;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB> SELECT NAME, CURRENT_KEY_ID FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'dare/t11';
+----------+----------------+
| NAME     | CURRENT_KEY_ID |
+----------+----------------+
| dare/t11 |             18 |
+----------+----------------+

Now change innodb-encrypt-tables=FORCE:

With this setting, if you attempt to create a table with ENCRYPTED=NO, then the command will fail:

MariaDB [dare]> CREATE TABLE t23 (id int) ENGINE=INNODB ENCRYPTED=NO;
ERROR 1005 (HY000): Can't create table `dare`.`t23` (errno: 140 "Wrong create options")

Similarly, if you attempt to change an encrypted table to a non-encrypted table with FORCE in effect, you’ll see an error like the below:

MariaDB> CREATE TABLE t20 (id int) ENGINE=INNODB;
Query OK, 0 rows affected (0.04 sec)

MariaDB> ALTER TABLE t20 ENCRYPTED=NO;
ERROR 1005 (HY000): Can't create table `dare`.`#sql-29b4_2` (errno: 140 "Wrong create options")

Now revert the FORCE back to 1 for innodb-encrypt-tables, and drop innodb-encryption-threads from 4 to 0 because of high CPU usage issue – if you need to avoid it (and also revert innodb-encrypt-tables back to 1 instead of FORCE)

MariaDB> CREATE TABLE t30 (id int) ENGINE=INNODB;
Query OK, 0 rows affected (0.14 sec)

MariaDB> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'dare/t3%';
+-------+----------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+
| SPACE | NAME     | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID |
+-------+----------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+
|    63 | dare/t30 |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |
+-------+----------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+

I did this test since it could be necessary that to set innodb-encryption-threads=0 if the high CPU utilzation issue requires you to do so, and you do not need the background threads to perform key rotation (discussed more on the manual page listed above).

Also, I did this because I’ve heard that if you set innodb-encryption-threads=0, then newly created tables where you do not explicitly set ENCRYPTED=YES will not be encrypted. I do not see that behavior, as you can see above that the table is encrypted. So I do want to dispell this notion whilst I’m at it.

Misc:

I did notice that one you set an ENCRYPTION_KEY_ID for a table, it remains with that CREATE TABLE output forever, even if you un-encrypt the table. Even an null ALTER TABLE does not remove it. It is harmless, but soemthing to be aware of:

MariaDB> ALTER TABLE t3 ENCRYPTED=NO;
Query OK, 0 rows affected, 1 warning (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 1

MariaDB> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------+
| Level   | Code | Message                                                          |
+---------+------+------------------------------------------------------------------+
| Warning |  140 | InnoDB: Ignored ENCRYPTION_KEY_ID 18 when encryption is disabled |
+---------+------+------------------------------------------------------------------+

MariaDB> ALTER TABLE t3 ENGINE=INNODB;
Query OK, 0 rows affected, 1 warning (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 1

MariaDB> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------+
| Level   | Code | Message                                                          |
+---------+------+------------------------------------------------------------------+
| Warning |  140 | InnoDB: Ignored ENCRYPTION_KEY_ID 18 when encryption is disabled |
+---------+------+------------------------------------------------------------------+

MariaDB> SHOW CREATE TABLE t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) DEFAULT NULL,
  `value` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 `encryption_key_id`=18 `encrypted`=NO

Lastly, I should mention that if you want to export a certain table to another instance, if it is encrypted, it will not work as-is. You will need to un-encrypt the table first, then perform the discard/import tablespace on the destination server, and then re-encrypt the table on the source server.

I hope this has helped covered the basics and a number of use cases to help you get your data at rest encrypted.

An Introduction to MariaDB’s Data at Rest Encryption (DARE) – Part 1

Encryption is becoming more and more prevalent and increasingly necessary in today’s world, so I wanted to provide a good overall “getting started” article on using MariaDB’s data at rest encryption (DARE) for anyone out there interested in setting this up in their environment.

MariaDB’s data encryption at rest manual page covers a lot of the specifics, but I wanted to create a quick start guide and also note a few items that might not be immediately obvious.

And due to the number of my examples, I’m splitting this into two posts. The first will focus solely on setting up encryption so you can use it. The second will focus on using it with a number of examples and common use cases.

Also, I feel that I should mention from the outset that, currently, this data at rest encryption only applies to InnoDB/XtraDB tables and Aria tables (that are created with ROW_FORMAT=PAGE, the default), and other limitations are listed on the above page.

1. First off, you will need to create a keys.txt file. The example on the above page works fine for this, but you may want to eventually create your own:

1;770A8A65DA156D24EE2A093277530142
18;F5502320F8429037B8DAEF761B189D12F5502320F8429037B8DAEF761B189D12

Save that as keys.txt (or whatever you want) and I’ll place it in the datadir for the sake of this example.

The first number is the encryption key identifier (a 32-bit number), and the latter the actual hex-encoded encryption key (which can be 128, 192, or 256-bit). The two values are to be separated by a semi-colon. You only need the first key (i.e., #1), but you can add as many keys as you like, and use various keys for various tables if you desire. The number 1 key is also the default.

2. Add the following lines to your configuration file (these are the basics from the manual page above):

plugin-load-add=file_key_management.dll
file-key-management
file-key-management-filename = "C:/Program Files/MySQL/mariadb-10.1.21/data/keys.txt"
innodb-encrypt-tables
innodb-encrypt-log
innodb-encryption-threads=4

Of course if on Linux, change the “.dll” to “.so”. Or you can always load it dynamically, but when you also need to set related variables, I prefer this way.

  • The first three lines load the key_management plugin, enable file-key-management, and point to the file-key file you created.
  • The fourth is what enables encryption. You can set to 0 (off), 1 (on), or FORCE (to always force every single table to be encrypted).
  • The fifth tells the server to enable encryption for the log files. From what I understand, this is for the InnoDB log files. There is a separate option to encrypt binary logs (- -encrypt-binlog). The manual recommends to enable this if you are encrypting tables since the data in the logs would not be encrypted otherwise, and thus viewable.
  • The sixth specifies how many background encryption threads to startup and use. These threads perform background key rotation and scrubbing. Note that this will add some overhead, and these background threads can cause higher CPU utilization since they are running and checking tables all of the time. More on this topic can be found here: https://jira.mariadb.org/browse/MDEV-11581

3. Restart your instance, and check that all looks normal.

Verify that the plugin loaded properly:

MariaDB> SHOW PLUGINS;
+-------------------------------+----------+--------------------+-------------------------+---------+
| Name                          | Status   | Type               | Library                 | License |
+-------------------------------+----------+--------------------+-------------------------+---------+
...
| file_key_management           | ACTIVE   | ENCRYPTION         | file_key_management.dll | GPL     |
+-------------------------------+----------+--------------------+-------------------------+---------+

And if using the above, you will see the 4 encryption threads start up in the error log:

2017-02-08 16:41:44 55840 [Note] InnoDB: Creating #1 thread id 33908 total threads 4.
2017-02-08 16:41:44 55840 [Note] InnoDB: Creating #2 thread id 33924 total threads 4.
2017-02-08 16:41:44 55840 [Note] InnoDB: Creating #3 thread id 38532 total threads 4.
2017-02-08 16:41:44 55840 [Note] InnoDB: Creating #4 thread id 29060 total threads 4.

4. Now you are ready to create an encrypted table. More on this in part #2 of this blog post.

Click here to read An Introduction to MariaDB’s Data at Rest Encryption (DARE) – Part 2.

Hope this helps.

Treating NULLs as not less than zero in ORDER BY Revisited

In my post yesterday, I shared a little known trick for sorting NULLs last when using ORDER BY ASC.

To summarize briefly, NULLs are treated as less than 0 when used in ORDER BY, However, sometimes you do not want that behavior, and you need the NULLs listed last, even though you want your numbers in ascending order.

So a query like the following returns the NULLs first (expected behavior):

SELECT * FROM t1 ORDER BY col1 ASC, col2 ASC;
+--------+------+
| col1   | col2 |
+--------+------+
| apple  | NULL |
| apple  |    5 |
| apple  |   10 |
| banana | NULL |
| banana |    5 |
| banana |   10 |
+--------+------+

The trick I mentioned in my post is to rewrite the query like:

SELECT * FROM t1 ORDER BY col1 ASC, -col2 DESC;

The difference is that we added a minus sign (-) in front of the column we want sorted and we change ASC to DESC.

Now this query returns what we’re looking for:

+--------+------+
| col1   | col2 |
+--------+------+
| apple  |    5 |
| apple  |   10 |
| apple  | NULL |
| banana |    5 |
| banana |   10 |
| banana | NULL |
+--------+------+

I could not really find this behavior documented at the time, and thus did some more digging to find out if this is intended behavior and if it should continue to work in the future (i.e., can we rely on it).

The answer is yes to both, it is intended, and it will continue to work.

Now, why does this work this way?

  • It is known that sorting in ascending (ASC) order NULLs are listed first, and if descending (DESC) they are listed last.
  • It is known that minus sign (-) preceding the column followed by DESC is the same as ASC (essentially the inverse). This is because if a > b, then -a < -b.
  • And the last bit of the puzzle is that NULL == -NULL.

So since -NULL == NULL, and we are now using DESC, the NULLs will be last. And then the remainder of the INT values will be in ASC order, since we effectively converted them to negative values and the changed order to DESC, effectively putting those INTs in ASC order.

Hope this helps clarify for anyone out there interested.

Treating NULLs as not less than zero in ORDER BY

I was working on a seemingly basic query the other day where the user needed to have an INT column listed in ascending order (i.e., 1, 2, 3, …).

However, the tricky part came in because the column allowed NULLs and the user needed the NULLs to be listed last, not first, which is the default behavior in both MariaDB and MySQL.

We first devised a somewhat convoluted solution where we used ISNULL() first in the ORDER BY, and then the column, but that wasn’t ideal since it added an additional check for each row in the ORDER BY, which we wanted to avoid in a query returning ~5M rows.

To illustrate, a normal query just sorting in ASC order returned:

MariaDB> SELECT * FROM t1 ORDER BY col1 ASC, col3 ASC;
+--------+--------+------+
| col1   | col2   | col3 |
+--------+--------+------+
| apple  | yellow | NULL |
| apple  | red    |    5 |
| apple  | green  |   10 |
| banana | brown  | NULL |
| banana | green  |    5 |
| banana | yellow |   10 |
+--------+--------+------+

But of course we want the NULLs last in each respective group, so we first added an ISNULL() check, which does return what we need:

MariaDB> SELECT * FROM t1 ORDER BY col1 ASC, ISNULL(col3) ASC, col3 ASC;
+--------+--------+------+
| col1   | col2   | col3 |
+--------+--------+------+
| apple  | red    |    5 |
| apple  | green  |   10 |
| apple  | yellow | NULL |
| banana | green  |    5 |
| banana | yellow |   10 |
| banana | brown  | NULL |
+--------+--------+------+

However, ideally, we wanted to eliminate the ISNULL() call.

The solution was to use a little know trick (at least to me, and I also don’t see it documented) where you add a minus sign (“-“) in front of the column you want to sort and also change the ASC to DESC. The minus sign is essentially the inverse, hence the change from ASC to DESC, but it has the added benefit (in this case) of now listing NULLs last.

MariaDB> SELECT * FROM t1 ORDER BY col1 ASC, -col3 DESC;
+--------+--------+------+
| col1   | col2   | col3 |
+--------+--------+------+
| apple  | red    |    5 |
| apple  | green  |   10 |
| apple  | yellow | NULL |
| banana | green  |    5 |
| banana | yellow |   10 |
| banana | brown  | NULL |
+--------+--------+------+

Hope this helps.