未加星标

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

字体大小 | |
[数据库(mysql) 所属分类 数据库(mysql) | 发布者 店小二03 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

Okay, so you’ve read thefirst 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.

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: TIRYInnoDBCPUCUAU
分页:12
转载请注明
本文标题:An Introduction to MariaDB’s Data at Rest Encryption (DARE) Part 2
本站链接:http://www.codesec.net/view/533152.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(44)