All Things Open 2016, Data Encryption at Rest in MySQL and MariaDB

Data Encryption at Rest in MySQL and MariaDB presentation at All Things Open 2016, Raleigh, NC @dataindataout

Transcript

1. Valerie Parham-Thompson 2016 October 26 DATA ENCRYPTION AT REST
2. WHAT’S THE PROBLEM? OVERVIEW ALTERNATIVES YES, DEAR KEY ROTATION KEY STORAGE THIRD-PARTY TOOLS BACKUPS PERFORMANCE SUMMARY
3. OVERVIEW PROTECTING DATA It has been possible to protect data in transit (moving over the network), and proper authentication can be used to protect data in use (being used by an application), but data at rest (sitting on a file system) has been an area of risk in MySQL/MariaDB.
4. OVERVIEW WHAT IS THE RISK? Without encryption of data at rest, a bad actor with access to the file system supporting the database can view data even without proper database-level permissions. Having root access to the file system is very common!
5. OVERVIEW WHAT IS SENSITIVE DATA? PCI: credit card data PII: names, drivers license, financial data, social security number HIPAA: medical, health insurance data See: https://en.wikipedia.org/wiki/California_S.B._1386
6. EXPOSED! INSERT SENSITIVE DATA MariaDB [allthingsopen]> create table t1 ( -> `intcol1` int(32) DEFAULT NULL, -> `intcol2` int(32) DEFAULT NULL, -> `charcol1` varchar(128) DEFAULT NULL, -> `charcol2` varchar(128) DEFAULT NULL, -> `charcol3` varchar(128) DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1; MariaDB [allthingsopen]> insert into allthingsopen.t1 values (1,2,'my','secret','123-45-6789'); Query OK, 1 row affected (0.00 sec)
7. EXPOSED! DATA FILES [root@encr_maria ~]# ls -al /var/lib/mysql/ allthingsopen/ total 108 drwx------. 2 mysql mysql 45 Oct 19 15:09 . drwxr-xr-x. 6 mysql mysql 4096 Oct 19 15:07 .. -rw-rw----. 1 mysql mysql 65 Oct 19 15:07 db.opt -rw-rw----. 1 mysql mysql 932 Oct 19 15:09 t1.frm -rw-rw----. 1 mysql mysql 98304 Oct 19 15:09 t1.ibd [root@encr_maria ~]# strings /var/lib/mysql/ allthingsopen/t1.ibd ... mysecret123-45-6789
8. EXPOSED! BINARY LOG [root@encr_maria ~]# strings encr_maria-bin.000001 ... insert into allthingsopen.t1 values (1,2,’my','secret','123-45-6789') [root@encr_comm ~]# strings /var/lib/mysql/encr_comm- bin.000001 ... secret 123-45-6789
9. EXPOSED! REDO LOG [root@encr_maria ~]# yum install vim-common ... [root@encr_maria ~]# xxd /var/lib/mysql/ib_logfile0 | grep -v "0000" ... 018a580: 0110 8000 0001 8000 0002 6d79 7365 6372 ..........mysecr 018a590: 6574 3132 332d 3435 2d36 3738 3982 0081 et123-45-6789... [root@encr_comm ~]# xxd /var/lib/mysql/ib_logfile0 | grep -v “0000" ... 0269ec0: 011b 0110 8000 0001 8000 0002 6d79 7365 ............myse 0269ed0: 6372 6574 3132 332d 3435 2d36 3738 3937 cret123-45-67897 (xxd is another command-line tool that allows you to see text within binary files. Install vim-common to use it.)
10. BUT FIRST… SOME ALTERNATIVES OVERVIEW ALTERNATIVES YES, DEAR KEY ROTATION KEY STORAGE THIRD-PARTY TOOLS BACKUPS PERFORMANCE SUMMARY
11. ALTERNATIVES OVERVIEW • Don’t store sensitive data. • Encrypt data from the application. • Use column-level encryption. • Encrypt the file system.
12. ALTERNATIVE DON’T DO IT • Outsource data storage. • Don’t store data if you don’t have a legitimate need for it. • Regularly archive data from departed users or data that has aged out.
13. ALTERNATIVE: ENCRYPT FROM APP EXAMPLE OF ENCRYPTED DATA MariaDB [allthingsopen]> select * from t1\G intcol1: 1 intcol2: 2 charcol1: my charcol2: secret charcol3: “?????-{??S@?/@%?>??????9?
14. ALTERNATIVE: ENCRYPT FROM APP NOT VISIBLE IN DATA FILES [root@encr_maria ~]# strings /var/lib/mysql/ allthingsopen/t1.ibd ... mysecret"?????-{??S@?/ %?>??? ???9?
15. ALTERNATIVE: ENCRYPT FROM APP NOT VISIBLE IN BINLOGS [root@encr_maria ~]# xxd /var/lib/mysql/encr_maria-bin.000001 ... 0001800: 0800 0049 4e53 4552 5420 494e 544f 2061 ...INSERT INTO a 0001810: 6c6c 7468 696e 6773 6f70 656e 2e74 310a llthingsopen.t1. 0001820: 2020 2020 2020 2020 2869 6e74 636f 6c31 (intcol1 0001830: 2c20 696e 7463 6f6c 322c 2063 6861 7263 , intcol2, charc 0001840: 6f6c 312c 2063 6861 7263 6f6c 322c 2063 ol1, charcol2, c 0001850: 6861 7263 6f6c 3329 0a20 2020 2020 2020 harcol3). 0001860: 2020 2020 2056 414c 5545 530a 2020 2020 VALUES. 0001870: 2020 2020 2831 2c20 322c 2027 6d79 272c (1, 2, 'my', 0001880: 2027 7365 6372 6574 272c 2027 5c22 9880 'secret', '\".. 0001890: bdf3 ff2d 7bb5 e553 40a7 2f10 4013 25da ...-{..S@./.@.%. 00018a0: 863e 95cf 81a2 1fa6 ccce 39fe 1e27 29df .>........9..'). 00018b0: d807 5810 0100 0000 1b00 0000 ca18 0000 ..X.............
16. ALTERNATIVE: ENCRYPT FROM APP NOT VISIBLE IN REDO LOG [root@encr_maria ~]# strings /var/lib/mysql/ ib_logfile0 ... mysecret"?????-{??S@?/ %?>??? ???9?
17. ALTERNATIVE: COLUMN LEVEL EXAMPLE OF INPUT AND OUTPUT ~ vparham$ mysql -h192.168.56.80 -uvalerie -pP@55word -e"SET block_encryption_mode = 'aes-256-cbc'; SET @key_str = SHA2('correct horse battery staple',512); SET @init_vector = RANDOM_BYTES(16); SET @crypt_str = AES_ENCRYPT('123-45-6789',@key_str,@init_vector); INSERT INTO allthingsopen.t1 values (1, 2, 'my', 'secret', @crypt_str); SELECT charcol3 from allthingsopen.t1; SELECT AES_DECRYPT(charcol3,@key_str,@init_vector) from allthingsopen.t1;" +----------------------------+ | charcol3 | +----------------------------+ | ûÕ‚?ŒÿPv¶K¾ìjµ> | +----------------------------+ +---------------------------------------------+ | AES_DECRYPT(charcol3,@key_str,@init_vector) | +---------------------------------------------+ | 123-45-6789 | +---------------------------------------------+
18. ALTERNATIVE: COLUMN LEVEL NOT VISIBLE IN DATA FILES [root@encr_comm ~]# xxd /var/lib/mysql/allthingsopen/ t1.ibd | grep -v 0000 ... 000c090: 011a 0110 8000 0001 8000 0002 6d79 7365 ............myse 000c0a0: 6372 6574 fbd5 823f 8cff 5076 04b6 4bbe cret....?..Pv..K.
19. ALTERNATIVE: COLUMN LEVEL NOT VISIBLE IN BINLOGS [root@encr_comm ~]# xxd /var/lib/mysql/encr_comm-bin.000001 ... 0001480: e001 0000 0002 0000 0002 6d79 0673 6563 ..........my.sec 0001490: 7265 7410 fbd5 823f 8cff 5076 04b6 4bbe ret....?..Pv..K. 00014a0: ec6a b53e efac dece 65e0 0758 1001 0000 .j.>....e..X.... 00014b0: 001f 0000 00c7 1400 0000 00a0 0000 0000 ................
20. ALTERNATIVE: COLUMN LEVEL NOT VISIBLE IN REDO LOGS [root@encr_comm ~]# strings /var/lib/mysql/ib_logfile0 ... mysecret
21. ALTERNATIVE: FILE SYSTEM EXAMPLE SETUP [root@encr_fs_maria ~]# #add a new disk [root@encr_fs_maria ~]# fdisk /dev/sdb [root@encr_fs_maria ~]# mkfs.ext4 /dev/sdb1 [root@encr_fs_maria ~]# sudo systemctl stop mariadb [root@encr_fs_maria ~]# mkdir /mnt/var [root@encr_fs_maria ~]# mount /dev/sdb1 /mnt/var [root@encr_fs_maria ~]# vi /etc/fstab [root@encr_fs_maria ~]# rsync -a /var/lib/mysql/ /mnt/var/ [root@encr_fs_maria ~]# vi /etc/my.cnf.d/server.cnf [root@encr_fs_maria ~]# sudo systemctl start mariadb [root@encr_fs_maria ~]# sudo systemctl stop mariadb [root@encr_fs_maria ~]# umount /dev/sdb1 [root@encr_fs_maria ~]# cryptsetup open /dev/sdb1 sda-crypt --type plain [root@encr_fs_maria ~]# dd if=/dev/sdb1 of=/dev/mapper/sda-crypt bs=512 [root@encr_fs_maria ~]# mount /dev/mapper/sda-crypt /mnt/var/ [root@encr_fs_maria ~]# vi /etc/fstab [root@encr_fs_maria ~]# cryptsetup open /dev/sdb1 sda-crypt --type plain [root@encr_fs_maria ~]# reboot [root@encr_fs_maria ~]# mount /dev/mapper/sda-crypt /mnt/var/ [root@encr_fs_maria ~]# sudo systemctl start mariadb Don’t do this.
22. READY FOR DATA ENCRYPTION AT REST? OVERVIEW ALTERNATIVES YES, DEAR KEY ROTATION KEY STORAGE THIRD-PARTY TOOLS BACKUPS PERFORMANCE SUMMARY
23. YES, DEaR, MARIADB CREATE KEY [root@encr_maria ~]# openssl enc -aes-256-cbc -P -md sha1 enter aes-256-cbc encryption password: Verifying - enter aes-256-cbc encryption password: salt=1796388689D7D859 key=35BD497FFED9E420C4341F72AECF693AC74ACEB5B511AD27DD7E7CC6 8613C98C iv =9FEE916B7B08389C9407CD9E6A67A304 [root@encr_maria ~]# vi /var/lib/mysql/keys.txt [root@encr_maria ~]# cat /var/lib/mysql/keys.txt 1;9FEE916B7B08389C9407CD9E6A67A304;35BD497FFED9E420C4341F72A ECF693AC74ACEB5B511AD27DD7E7CC68613C98C
24. YES, DEaR, MARIADB EDIT CONFIG FILE [root@encr_maria ~]# cat /etc/my.cnf [mysqld] plugin_load_add=file_key_management.so file_key_management file_key_management_filename = /var/lib/mysql/keys.txt innodb-encrypt-tables innodb-encryption-threads=4 innodb-encrypt-log encrypt_binlog encrypt-tmp-disk-tables=1 encrypt-tmp-files
25. YES, DEaR, MARIADB VERIFY PLUGIN IS LOADED [root@encr_maria ~]# sudo systemctl restart mariadb MariaDB [(none)]> show plugins ... | file_key_management | ACTIVE | ENCRYPTION | file_key_management.so | GPL | ...
26. YES, DEaR, MARIADB ENCRYPTION CONFIGS ON MariaDB [(none)]> show global variables like '%encr%'; +------------------------------------------+---------+ | Variable_name | Value | +------------------------------------------+---------+ | aria_encrypt_tables | OFF | | encrypt_binlog | ON | | encrypt_tmp_disk_tables | ON | | encrypt_tmp_files | ON | | file_key_management_encryption_algorithm | aes_cbc | | innodb_default_encryption_key_id | 1 | | innodb_encrypt_log | ON | | innodb_encrypt_tables | ON | | innodb_encryption_rotate_key_age | 1 | | innodb_encryption_rotation_iops | 100 | | innodb_encryption_threads | 4 | +------------------------------------------+---------+
27. YES, DEaR, MARIADB ENCRYPTION METHODS There are three ways to encrypt table data in MariaDB 10.1: • innodb_encrypt_tables in the my.cnf will encrypt all tables (unless specified as “encrypted=no”). • innodb_encrypt_tables=FORCE in the my.cnf will encrypt all new tables created, and will prevent table creation with “encrypted=no.” • If the configuration innodb_encrypt_tables is not in the my.cnf, but the plugin is present, you can explicitly encrypt a table by including “encrypted=yes” in the table creation statement. (If innodb_encryption_threads is set higher than 0, then existing tables will be encrypted in the background.)
28. YES, DEaR, MARIADB INNODB_ENCRYPTION_THREADS=4 [root@encr_maria ~]# tail -f /var/lib/mysql/ encr_maria.err ... 2016-10-19 19:44:31 140629860751488 [Note] InnoDB: Creating #1 thread id 140629124273920 total threads 4. 2016-10-19 19:44:31 140629860751488 [Note] InnoDB: Creating #2 thread id 140629115881216 total threads 4. 2016-10-19 19:44:31 140629860751488 [Note] InnoDB: Creating #3 thread id 140629107488512 total threads 4. 2016-10-19 19:44:31 140629860751488 [Note] InnoDB: Creating #4 thread id 140629099095808 total threads 4. ...
29. YES, DEaR, MARIADB EFFECTS OF BACKGROUND THREADS MariaDB [(none)]> select name from information_schema.innodb_tablespaces_encryption where encryption_scheme=1; Empty set (0.00 sec) MariaDB [(none)]> select name from information_schema.innodb_tablespaces_encryption where encryption_scheme=1; +--------------------------+ | name | +--------------------------+ ... | allthingsopen/t1 | +--------------------------+ 5 rows in set (0.00 sec)
30. YES, DEaR, MARIADB INSERTING SENSITIVE DATA MariaDB [allthingsopen]> create table t2 ( `intcol1` int(32) DEFAULT NULL, `intcol2` int(32) DEFAULT NULL, `charcol1` varchar(128) DEFAULT NULL, `charcol2` varchar(128) DEFAULT NULL, `charcol3` varchar(128) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; MariaDB [allthingsopen]> insert into t2 values (3, 4, 'your', 'secret', '123-45-6789'); [root@encr_maria ~]# cat /etc/my.cnf ... innodb-encrypt-tables ...
31. YES, DEaR, MARIADB NOT VISIBLE IN DATA FILES [root@encr_maria ~]# strings /var/lib/mysql/ allthingsopen/t2.ibd v_1x Y2#2 7;-H {J)Ky 'BRt $qC5 rlDa ...
32. YES, DEaR, MARIADB NOT VISIBLE IN BINLOGS [root@encr_maria ~]# xxd /var/lib/mysql/encr_maria-bin.000015 ... 00003e0: 7b92 71e0 1f6d b2d0 4da0 3e39 1a26 0000 {.q..m..M.>9.&.. 00003f0: 00f0 6681 8eb5 e6a0 125b 9940 6c2f a37b ..f......[.@l/.{ 0000400: 2108 3996 7e3d be8d 53ba 869e 54d3 9173 !.9.~=..S...T..s 0000410: ff3b c285 0000 0068 255c 6dd0 2205 a14b .;.....h%\m."..K 0000420: ad8b 3b12 6ccd cd83 565c c05c fb71 4665 ..;.l…V\.\.qFe ... [root@encr_maria ~]# cat /etc/my.cnf ... encrypt_binlog
33. YES, DEaR, MARIADB (MYSQLBINLOG DOESN’T WORK) [root@encr_maria ~]# mysqlbinlog /var/lib/mysql/ encr_maria-bin.000015 ... # at 249 # Encryption scheme: 1, key_version: 1, nonce: e7f7531d547cc3ee52111b95 # The rest of the binlog is encrypted! ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 39, event_type: 187 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
34. YES, DEaR, MARIADB NOT VISIBLE IN REDO LOG [root@encr_maria ~]# xxd /var/lib/mysql/ib_logfile0 | grep -v 0000 ... 01b4c90: 6338 691d 9b81 aba3 134e 07e7 85e6 62c9 c8i......N....b. 01b4ca0: 4d72 e699 b357 aeb9 cbcb 5c99 6718 3b17 Mr...W....\.g.;. 01b4cb0: 03d7 56e5 fbe1 1dd2 32c3 b4e8 3ab4 46bc ..V.....2...:.F. 01b4cc0: 500d 9899 4dbe b733 a505 9a03 2c34 ca7e P...M..3....,4.~ 01b4cd0: d29f 2f8c 4849 0d40 eb18 531d 9531 22bd ../.HI.@..S..1”. ... [root@encr_maria ~]# cat /etc/my.cnf ... innodb-encrypt-log
35. YES, DEaR, MARIADB REMAINING EXPOSURES Not all data on disk is encrypted: • slow log • error log • general log • audit log • relay logs on unencrypted slaves
36. YES, DEaR, MARIADB VISIBLE IN SLOW LOG MariaDB [allthingsopen]> set global slow_query_log=on; MariaDB [allthingsopen]> set global long_query_time=0; MariaDB [(none)]> update allthingsopen.t2 set charcol3='456-78-9123' where intcol1>1; # Time: 161019 20:55:33 # User@Host: root[root] @ localhost [] # Thread_id: 6 Schema: QC_hit: No # Query_time: 0.003517 Lock_time: 0.000118 Rows_sent: 0 Rows_examined: 1 # Rows_affected: 0 SET timestamp=1476924933; update allthingsopen.t2 set charcol3='456-78-9123' where intcol1>1; See: https://jira.mariadb.org/browse/MDEV-9639
37. YES, DEaR, MARIADB VISIBLE IN ERROR LOG MariaDB [(none)]> set global log_warnings=2; MariaDB [(none)]> insert into allthingsopen.t2 (select * from allthingsopen.t1 where charcol3 like '123-45-6789' limit 1); Query OK, 0 rows affected, 1 warning (0.00 sec) Records: 0 Duplicates: 0 Warnings: 1 [root@encr_maria ~]# less /var/lib/mysql/encr_maria.err ... 2016-10-19 20:58:53 140158235900672 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: insert into allthingsopen.t2 (select * from allthingsopen.t1 where charcol3 like '123-45-6789' limit 1)
38. YES, DEaR, MARIADB VISIBLE IN GENERAL LOG MariaDB [(none)]> set global general_log=on; MariaDB [(none)]> insert into allthingsopen.t2 values (5, 6, 'my', 'secret', '987-65-4321'); [root@encr_maria ~]# less /var/lib/mysql/encr_maria.log /usr/sbin/mysqld, Version: 10.1.18-MariaDB (MariaDB Server). started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id Command Argument 161019 21:00:42 6 Query insert into allthingsopen.t2 values (5, 6, 'my', 'secret', '987-65-4321')
39. YES, DEaR, MARIADB VISIBLE IN AUDIT LOG MariaDB [(none)]> INSTALL PLUGIN server_audit SONAME ‘server_audit.so'; MariaDB [(none)]> set global server_audit_mode=1; MariaDB [(none)]> SET GLOBAL server_audit_events=‘CONNECT,QUERY,TABLE'; MariaDB [(none)]> SET GLOBAL server_audit_logging=ON; MariaDB [(none)]> insert into allthingsopen.t1 values (6, 6, 'my', 'secret', ‘777-77-7777'); [root@encr_maria ~]# tail -f /var/lib/mysql/ server_audit.log 20161019 21:12:30,encr_maria,root,localhost, 7,6,QUERY,allthingsopen,'insert into allthingsopen.t1 values (6, 6, \'my\', \'secret\', \'777-77-7777\')',0
40. YES, DEaR, MARIADB VISIBLE IN RELAY LOGS [root@encr_maria_slave ~]# xxd /var/lib/mysql/ encr_maria_slave-relay-bin.000002 ... 0000300: 0008 0061 6c6c 7468 696e 6773 6f70 656e ...allthingsopen 0000310: 0069 6e73 6572 7420 696e 746f 2074 3320 .insert into t3 0000320: 7661 6c75 6573 2028 342c 2035 2c20 276d values (4, 5, 'm 0000330: 7927 2c20 2773 6563 7265 7427 2c20 2731 y', 'secret', '1 0000340: 3233 2d34 352d 3637 3839 2729 f513 0858 23-45-6789')...X ... encrypt_binlog will encrypt both binlogs and relay logs on the configured server, but relay logs on any attached slaves are not encrypted without configuration on those slaves. Watch those database permissions!
41. YES, DEaR, MARIADB DISABLING ENCRYPTION MariaDB [(none)]> set global innodb_encryption_threads=0; MariaDB [(none)]> select name from information_schema.innodb_tablespaces_encryption where encryption_scheme=1; | test/sbtest1 | MariaDB [allthingsopen]> alter table t2 encrypted=‘no'; MariaDB [allthingsopen]> select name from information_schema.innodb_tablespaces_encryption where encryption_scheme=1; Empty set (0.00 sec) # remove encryption configs from configurationn files [root@centosbase ~]# sudo systemctl restart mariadb
42. YES, DEaR, ORACLE EDIT MY.CNF TO INSTALL PLUGIN [mysqld] early-plugin-load=keyring_file.so keyring_file_data=/var/lib/mysql/mysql-keyring/keyring [root@encr_percona ~]# service mysql start mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%'; +--------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +--------------+---------------+ | keyring_file | ACTIVE | +--------------+---------------+ mysql> show global variables like '%keyring%'; +-------------------+--------------------------------------+ | Variable_name | Value | +-------------------+--------------------------------------+ | keyring_file_data | /var/lib/mysql/mysql-keyring/keyring | +-------------------+--------------------------------------+ Store your key outside the data directory.
43. YES, DEaR, ORACLE CREATE KEY [root@encr_percona ~]# cat /var/lib/mysql/mysql- keyring/keyring [root@encr_percona ~]# mysql> CREATE TABLE t1 (c1 INT) ENCRYPTION=‘Y'; [root@encr_percona ~]# cat /var/lib/mysql/mysql- keyring/keyring Keyring file version:1.00 INNODBKey- ad5cb6c1-962a-11e6-aaa4-0800276886e3-1AESFYY ̷`WD Create table with encryption to populate keyfile.
44. YES, DEaR, ORACLE INSERT SENSITIVE DATA mysql> create table t2 ( `intcol1` int(32) DEFAULT NULL, `intcol2` int(32) DEFAULT NULL, `charcol1` varchar(128) DEFAULT NULL, `charcol2` varchar(128) DEFAULT NULL, `charcol3` varchar(128) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ENCRYPTION='Y'; # or if table exists mysql> alter table t2 encryption='Y'; mysql> insert into t2 values (3, 4, 'your', 'secret', '123-45-6789');
45. YES, DEaR, ORACLE NOT VISIBLE IN DATA FILES [root@encr_percona allthingsopen]# strings /var/lib/ mysql/allthingsopen/t2.ibd A-<W h9Ps t#4i~ mEHPV #)yh d.}_H S<.c! "2fyN4` zC}1;@ ...
46. YES, DEaR, ORACLE BINLOGS NOT ENCRYPTED [root@encr_percona allthingsopen]# xxd /var/lib/ mysql/encr_percona-bin.000002 0000c80: 0000 0001 0002 0005 ffe0 0300 0000 0400 ................ 0000c90: 0000 0479 6f75 7206 7365 6372 6574 0b31 ...your.secret.1 0000ca0: 3233 2d34 352d 3637 3839 6e4d 2aa4 5cce 23-45-6789nM*.\.
47. YES, DEaR, ORACLE REDO LOG NOT ENCRYPTED [root@encr_percona allthingsopen]# xxd /var/lib/ mysql/ib_logfile0 | grep -v 0000 ... 0266af0: 796f 7572 7365 6372 6574 3132 332d 3435 yoursecret123-45 0266b00: 2d36 3738 3937 1c00 0017 2e2f 616c 6c74 -67897...../allt ...
48. YES, DEaR, ORACLE REMAINING EXPOSURES Only the tablespace is encrypted, leaving the following exposed: • binlogs • redo log • relay logs on unencrypted slaves • slow log • error log • general log • audit log
49. YES, DEaR, ORACLE DON’T LOSE THE MASTER KEY [root@encr_percona ~]# ls -l /var/lib/mysql/mysql- keyring/ total 0 -rw-r-----. 1 mysql mysql 0 Oct 19 16:18 keyring mysql> select * from t2; ERROR 3185 (HY000): Can't find master key from keyring, please check keyring plugin is loaded. Special note for Xtrabackup later.
50. AREA OF OPPORTUNITY OVERVIEW ALTERNATIVES YES, DEAR KEY ROTATION KEY STORAGE THIRD-PARTY TOOLS BACKUPS PERFORMANCE SUMMARY
51. KEY ROTATION: ORACLE ROTATING THE MASTER KEY [root@encr_percona ~]# ls -al /var/lib/mysql/mysql- keyring/keyring -rw-r-----. 1 mysql mysql 795 Oct 19 20:31 /var/ lib/mysql/mysql-keyring/keyring [root@encr_percona ~]# mysql -e'alter instance rotate innodb master key’; [root@encr_percona ~]# ls -al /var/lib/mysql/mysql- keyring/keyring -rw-r-----. 1 mysql mysql 923 Oct 19 20:58 /var/ lib/mysql/mysql-keyring/keyring
52. KEY ROTATION: ORACLE ROTATING THE HEADER KEY [root@encr_percona ~]# ls -al /var/lib/mysql/ allthingsopen/t2.ibd -rw-r-----. 1 mysql mysql 98304 Oct 19 21:08 /var/ lib/mysql/allthingsopen/t2.ibd [root@encr_percona ~]# mysql -e'alter instance rotate innodb master key’; [root@encr_percona ~]# ls -al /var/lib/mysql/ allthingsopen/t2.ibd -rw-r-----. 1 mysql mysql 98304 Oct 19 21:09 /var/ lib/mysql/allthingsopen/t2.ibd Try at home: look at the header contents of the .ibd file.
53. KEY ROTATION: MARIADB SOME ALTERNATIVES Default encryption plugin: no rotation Key rotation available with: • Amazon Web Services (AWS) Key Management Services (KMS) (https://mariadb.com/kb/en/mariadb/ aws-key-management-encryption-plugin/) • eperi Gateway for Databases (http://eperi.de/mariadb)
54. KEY ROTATION: MARIADB MAINTENANCE: DECRYPT # decrypt MariaDB [(none)]> set global innodb_encryption_threads=0; MariaDB [(none)]> select name from information_schema.innodb_tablespaces_encryption where encryption_scheme=1; | test/sbtest1 | MariaDB [allthingsopen]> alter table t2 encrypted=‘no'; MariaDB [allthingsopen]> select name from information_schema.innodb_tablespaces_encryption where encryption_scheme=1; Empty set (0.00 sec)
55. KEY ROTATION: MARIADB MAINTENANCE: RE-ENCRYPT [root@centosbase mysql]# mv keys.txt keys.bak # comment out encryption configs from my.cnf [root@centosbase ~]# sudo systemctl restart mariadb # re-encrypt; create new keys.txt and uncomment encryption configs in my.cnf [root@centosbase ~]# sudo systemctl restart mariadb MariaDB [(none)]> alter table allthingsopen.t2 encrypted='yes';
56. DON’T PUT YOUR KEY AT THE FRONT DOOR OVERVIEW ALTERNATIVES YES, DEAR KEY ROTATION KEY STORAGE THIRD-PARTY TOOLS BACKUPS PERFORMANCE SUMMARY
57. KEY STORAGE CAN ENCRYPT KEYFILE [root@maria101 mysql]# openssl enc -aes-256-cbc -md sha1 -k mypassword -in /var/lib/mysql/keys.txt -out / var/lib/mysql/keys.enc [root@maria101 mysql]# cat /etc/my.cnf | grep key- management file-key-management-filename = /var/lib/mysql/keys.enc file-key-management-filekey = mypassword …but of course, the password is exposed in the my.cnf on disk
58. KEY STORAGE CLEVER IDEAS Store the keyfile on a USB stick. Store the keyfile on a directory mounted only during database startup. Your clever idea!
59. KEY STORAGE COMMERCIAL SOLUTIONS AWS Key Management Service eperi Gateway for Databases Oracle Key Vault
60. WHAT TOOLS CAN STREAM DATA OR LOGS FROM YOUR SERVER? OVERVIEW ALTERNATIVES YES, DEAR KEY ROTATION KEY STORAGE THIRD-PARTY TOOLS BACKUPS PERFORMANCE SUMMARY
61. THIRD PARTY TOOLS ONE EXAMPLE: ANEMOMETER Note that this user doesn’t have broad permissions.
62. THIRD PARTY TOOLS ANOTHER EXAMPLE: EMAIL DELIVERY This is a common report, widely broadcast.
63. SOME GOOD, SOME BAD OVERVIEW ALTERNATIVES YES, DEAR KEY ROTATION KEY STORAGE THIRD-PARTY TOOLS BACKUPS PERFORMANCE SUMMARY
64. BACKUPS MYSQLDUMP: MARIADB MariaDB [allthingsopen]> select name from information_schema.innodb_tablespaces_encryption where encryption_scheme=1; ... | allthingsopen/t2 | ... [root@encr_maria ~]# mysqldump allthingsopen t2 > dumpfile [root@encr_maria ~]# cat dumpfile ... INSERT INTO `t2` VALUES (3,4,'your','secret','456-78-9123'), (5,6,'my','secret','987-65-4321');
65. BACKUPS MYSQLDUMP: ORACLE mysql> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `intcol1` int(32) DEFAULT NULL, `intcol2` int(32) DEFAULT NULL, `charcol1` varchar(128) DEFAULT NULL, `charcol2` varchar(128) DEFAULT NULL, `charcol3` varchar(128) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ENCRYPTION='Y' [root@encr_percona ~]# mysqldump allthingsopen t2 > dumpfile [root@encr_percona ~]# cat dumpfile | grep -i insert INSERT INTO `t2` VALUES (3,4,'your','secret','123-45-6789'), (6,7,'my','secret','123-45-6789');
66. BACKUPS XTRABACKUP: MARIADB [root@encr_maria ~]# innobackupex --user=root -- password=xxx --socket=/var/lib/mysql/mysql.sock / backup/ xtrabackup: Generating a list of tablespaces InnoDB: Allocated tablespace ID 4 for allthingsopen/t2, old maximum was 0 161023 10:06:19 [01] Copying ./ibdata1 to /backup/ 2016-10-23_10-06-19/ibdata1 [01] xtrabackup: Database page corruption detected at page 1, retrying... File ./ibdata1 seems to be corrupted. [01] xtrabackup: Error: xtrabackup_copy_datafile() failed. [01] xtrabackup: Error: failed to copy datafile. See: https://mariadb.com/kb/en/mariadb/plans-for-10x/
67. BACKUPS XTRABACKUP: ORACLE [root@encr_percona ~]# innobackupex --user=root -- password=xxx/ [root@encr_percona ~]# innobackupex --apply-log / [root@encr_percona ~]# strings /2016-10-19_18-26-49/ allthingsopen/t2.ibd ZwYS?. 1R2W b[K- ... [root@encr_percona ~]# cp /var/lib/mysql/mysql- keyring/keyring /tmp/keyringbackup [root@encr_percona ~]# innobackupex --copy-back / 2016-10-19_18-26-49/ [root@encr_percona ~]# sudo chown -R mysql: /var/ lib/mysql [root@encr_percona ~]# cp /tmp/mysql/mysql-keyring/ keyring /var/lib/mysql/mysql-keyring/keyring Works, but backup and restore the keyfile manually.
68. ENCRYPTION ISN’T FREE OVERVIEW ALTERNATIVES YES, DEAR KEY ROTATION KEY STORAGE THIRD-PARTY TOOLS BACKUPS PERFORMANCE SUMMARY
69. PERFORMANCE SETUP SYSBENCH sysbench \ --test=/usr/share/doc/sysbench/tests/db/oltp.lua \ --mysql-table-engine=innodb \ --oltp-test-mode=complex \ --oltp-read-only=off \ --oltp-table-size=100000 \ --max-requests=1 \ --num-threads=4 \ --max-time=10 \ --mysql-socket=/var/lib/mysql/mysql.sock \ --mysql-user=root \ --mysql-password=password \ --mysql-db=test \ prepare
70. PERFORMANCE RUN SYSBENCH # for num-threads 4, 8, 16, 32, 64, 128 sysbench \ --test=/usr/share/doc/sysbench/tests/db/oltp.lua \ --mysql-table-engine=innodb \ --oltp-test-mode=complex \ --oltp-read-only=off \ --oltp-table-size=100000 \ --max-requests=100000000 \ --num-threads=32 \ --max-time=10 \ --mysql-socket=/var/lib/mysql/mysql.sock \ --mysql-user=root \ --mysql-password=password \ --mysql-db=test \ run >> sysbench.log
71. PERFORMANCE CONVERT SYSBENCH OUTPUT #example conversion file [root@encr_maria_just_tablespace ~]# cat sysbench.csv 1 1772 2 2044 4 2273 8 2174 16 2256 32 2313
72. PERFORMANCE MARIADB https://www.libreoffice.org/
73. PERFORMANCE PERCONA Don’t compare between versions because configs can differ outside of the isolated encryption changes within versions.
74. OVERVIEW ALTERNATIVES YES, DEAR KEY ROTATION KEY STORAGE THIRD-PARTY TOOLS BACKUPS PERFORMANCE SUMMARY
75. THE FOLLOWING OPEN SOURCE SOFTWARE TOOLS WERE USED MariaDB Percona Server Oracle MySQL Community Sysbench LibreOffice CentOS Golang Anemometer
76. THANK YOU Twitter: @dataindataout Email: valerie@opengroupware.ch