Securing your MySQL MariaDB data

1 . Securing your MySQL/MariaDB Server Data Colin Charles, Ronald Bradford Percona Live Santa Clara 2017 (now as a webminar - July 2017) #PerconaLive @bytebot @RonaldBradford 

2 .License ● Creative Commons BY-NC-SA 4.0 ● https://creativecommons.org/licenses/by-nc-sa/4.0/legalcode #PerconaLive @bytebot @RonaldBradford 

3 .About: Colin Charles ● Chief Evangelist, Percona Inc ● Founding team of MariaDB Server (2009-2016), previously at Monty Program Ab, merged with SkySQL Ab, now MariaDB Corporation ● Formerly MySQL AB (exit: Sun Microsystems) ● Past lives include Fedora Project (FESCO), OpenOffice.org ● MySQL Community Contributor of the Year Award winner 2014 ● http://bytebot.net/blog/ #PerconaLive @bytebot @RonaldBradford 

4 .About: Ronald Bradford ● MySQL Database Consultant at Pythian ● Author/Blogger/Speaker ● Oracle ACE Director 2010 - present ● MySQL Community Member of the Year Award winner 09, 13 ● Formally MySQL Inc 06-08, Oracle Corporation 96-99 ● http://ronaldbradford.com/presentations/ ● http://effectivemysql.com #PerconaLive @bytebot @RonaldBradford 

5 .Agenda ● Observed insecure practices ● Securing communications ● Securing connections ● Securing data ● Securing user accounts ● Securing server access #PerconaLive @bytebot @RonaldBradford 

6 .Signs of Poor Security ● old_passwords ● 'root' MySQL user without password ● Users without passwords ● 'root' MySQL user ● Anonymous users ● Generic OS DBA user e.g. 'dba' ● WITH GRANT privilege users ● Disabled OS security e.g. ● ALL ON *.* privileged users Firewall/SELinux/Apparmor ● '%' host user accounts ● Open data directory privileges ● Not using CREATE USER ● Default test database #PerconaLive @bytebot @RonaldBradford 

7 .Easy Fixes $ mysql_secure_installation 5.7 Functionality VALIDATE PASSWORD PLUGIN can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD plugin? There are three levels of password validation policy: LOW Length >= 8 MEDIUM Length >= 8, numeric, mixed case, and special characters STRONG Length >= 8, numeric, mixed case, special characters and dictionary file Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: ... Estimated strength of the password: 25 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) #PerconaLive @bytebot @RonaldBradford 

8 . Very easy to improve poor Current Insecure Practices practices ● Using password on command line ○ Command history ○ MySQL shell history ● Using simple passwords ○ It's just a test environment ● Using excessive permissions ○ GRANT, ALL, *.*, % #PerconaLive @bytebot @RonaldBradford 

9 .Command Line options (non-interactive) read MYSQL_USER read -s MYSQL_PWD mysql -u${MYSQL_USER} -p${MYSQL_PWD} ps rewrite ps -ef | grep mysql .... mysql -udemo -px xxxxxxx mysql # using $HOME/.my.cnf mysql --defaults-file=/path/to/.my.cnf ● What about gh-ost, pt-osc, other pt- tools etc https://dev.mysql.com/doc/refman/5.6/en/mysql-logging.html #PerconaLive @bytebot @RonaldBradford 

10 .Config Editor $ mysql_secure_installation $ mysql -uroot -p -e "CREATE USER demo@localhost IDENTIFIED BY 'passw0rd1';" $ echo "[client] user=demo password=passw0rd1" > $HOME/.my.cnf $ mysql -e "SELECT USER()" Since 5.6 $ rm $HOME/.my.cnf $ mysql -e "SELECT USER()" $ mysql_config_editor set --login-path=client --host=localhost --user=demo --password $ ls -al $HOME/.mylogin.cnf $ cat $HOME/.mylogin.cnf $ mysql_config_editor print $ mysql -e "SELECT USER()" $ mysqldump .... #PerconaLive @bytebot @RonaldBradford 

11 .Why being SUPER is bad (GRANT ALL ON *.*) ● Bypasses read_only (why we need super_read_only) ● Bypasses init_connect ● Can disable binary logging ● Can change dynamic configuration ● Takes the reserved connection http://ronaldbradford.com/blog/why-grant-all-is-bad-2010-08-06/ http://effectivemysql.com/presentation/mysql-idiosyncrasies-that-bite/ #PerconaLive @bytebot @RonaldBradford 

12 .Secure Communications ● SSL for replication ● SSL for client connections ● SSL for admin connections ● Encryption on the wire https://dev.mysql.com/doc/refman/5.6/en/secure-connections.html https://dev.mysql.com/doc/refman/5.7/en/secure-connections.html #PerconaLive @bytebot @RonaldBradford 

13 .Secure Communications [mysqld] ssl-ca=ca.pem ssl-cert=server-cert.pem ssl-key=server-key.pem #PerconaLive @bytebot @RonaldBradford 

14 .SSL Protocols and Ciphers mysql> SHOW SESSION STATUS LIKE 'Ssl_version'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Ssl_version | TLSv1 | +---------------+-------+ mysql> SHOW SESSION STATUS LIKE 'Ssl_cipher'; +---------------+---------------------------+ | Variable_name | Value | +---------------+---------------------------+ | Ssl_cipher | DHE-RSA-AES128-GCM-SHA256 | +---------------+---------------------------+ #PerconaLive @bytebot @RonaldBradford 

15 .SSL Client Connections https://dev.mysql.com/doc/connector-python/en/connector-python-connectargs.html import mysql.connector from mysql.connector.constants import ClientFlag config = { 'user': 'ssluser', 'password': 'asecret', 'host': '127.0.0.1', 'client_flags': [ClientFlag.SSL], 'ssl_ca': '/opt/mysql/ssl/ca.pem', 'ssl_cert': '/opt/mysql/ssl/client-cert.pem', 'ssl_key': '/opt/mysql/ssl/client-key.pem', } https://dev.mysql.com/doc/connectors/en/connector-net-tutorials-ssl.html #PerconaLive @bytebot @RonaldBradford 

16 .Secure Connections ● mysql_ssl_rsa_setup in MySQL 5.7 ○ This program creates the SSL certificate and key files and RSA key-pair files required to support secure connections using SSL and secure password exchange using RSA over unencrypted connections, if those files are missing. ● uses the openssl command #PerconaLive @bytebot @RonaldBradford 

17 .Secure Storage ● Encryption of data at rest ○ Data (table vs tablespace) ○ Binary Logs ○ Other Logs ● Key management #PerconaLive @bytebot @RonaldBradford 

18 .Encryption in MariaDB Server ● Encryption: tablespace OR table level encryption with support for rolling keys using the AES algorithm ○ table encryption — PAGE_ENCRYPTION=1 ○ tablespace encryption — encrypts everything including log files ● file_key_management_filename, file_key_management_filekey, file_key_management_encryption_algorithm ● Well documented — https://mariadb.com/kb/en/mariadb/data-at-rest-encryption/ ● Tablespace/logs scrubbing: background process that regularly scans through the tables and upgrades the encryption keys ● --encrypt-tmp-files & --encrypt-binlog #PerconaLive @bytebot @RonaldBradford 

19 .Encryption in MariaDB Server II [mysqld] CREATE TABLE customer ( plugin-load-add=file_key_management.so customer_id bigint not null primary file-key-management key, file-key-management-filename = customer_name varchar(80), /home/mdb/keys.enc customer_creditcard varchar(20)) innodb-encrypt-tables ENGINE=InnoDB innodb-encrypt-log innodb-encryption-threads=4 page_encryption=1 aria-encrypt-tables=1 # PAGE row format page_encryption_key=1; encrypt-tmp-disk-tables=1 # this is for Aria #PerconaLive @bytebot @RonaldBradford 

20 .Encryption in MariaDB Server III ● Use the preset! - /etc/my.cnf.d/enable_encryption.preset ● MariaDB Enterprise has a plugin for Amazon Key Management Server (KMS) ○ The reality is you can just compile this for MariaDB Server ● mysqlbinlog has no way to read (i.e. decrypt) an encrypted binlog ● This does not work with MariaDB Galera Cluster yet (gcache is not encrypted yet), and also xtrabackup needs additional work (i.e. if you encrypt the redo log) #PerconaLive @bytebot @RonaldBradford 

21 .Encryption in MySQL ● MySQL 5.7.11 introduces InnoDB tablespace encryption ● early-plugin-load=keyring_file.so in my.cnf ● Must use innodb_file_per_table ● Convert via ALTER TABLE table ENCRYPTION=‘Y’ ● Data is not encrypted in the redo/undo/binary logs ● Has external key management (Oracle Key Vault) #PerconaLive @bytebot @RonaldBradford 

22 .Secure Accounts ● Privileges ● Passwords ● Password filesystem storage #PerconaLive @bytebot @RonaldBradford 

23 .MySQL 5.6 improvements ● Password expiry - ALTER USER 'foo'@'localhost' PASSWORD EXPIRE; ● Password validation plugin - VALIDATE_PASSWORD_STRENGTH() ● mysql_config_editor - store authentication credentials in an encrypted login path file named .mylogin.cnf ○ http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html ● Random ‘root’ password on install ○ mysql_install_db —random-passwords ○ cat $HOME/.mysql_secret #PerconaLive @bytebot @RonaldBradford 

24 .PASSWORD EXPIRE $ export MYSQL_PS1="\u@\h [\d]> " $ mysql -uroot -p root@localhost [(none)]> CREATE USER demo IDENTIFIED BY 'passw0rd1'; $ mysql -udemo -p #passw0rd1 $ mysql -uroot root@localhost [(none)]> ALTER USER demo PASSWORD EXPIRE; $ mysql -udemo -p #passw0rd1 demo@localhost [(none)]> # No issue connecting demo@localhost [(none)]> USE test; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. # can reset password to current value demo@localhost [(none)]> ALTER USER demo IDENTIFIED BY 'passw0rd1'; #PerconaLive @bytebot @RonaldBradford 

25 .MySQL 5.7 improvements ● Improved password expiry — automatic password expiration available, so set default_password_lifetime in my.cnf ● You can also require password to be changed every n-days ○ ALTER USER foo@localhost PASSWORD EXPIRE INTERVAL n DAY; ● PASSWORD EXPIRE DEFAULT | NEVER options ● There is also account locking/unlocking now ○ ALTER USER foo@host ACCOUNT LOCK | UNLOCK; https://dev.mysql.com/doc/refman/5.7/en/alter-user.html #PerconaLive @bytebot @RonaldBradford 

26 .MariaDB Server passwords ● Password validation plugin ○ https://mariadb.com/kb/en/mariadb/development/mariadb-internals-documentation/password-v alidation/ ● simple_password_check password validation plugin ○ can enforce a minimum password length and guarantee that a password contains at least a specified number of uppercase and lowercase letters, digits, and punctuation characters. ● cracklib_password_check password validation plugin ○ Allows passwords that are strong enough to pass CrackLib test. This is the same test that pam_cracklib.so does #PerconaLive @bytebot @RonaldBradford 

27 .Authentication in MySQL / MariaDB Server ● auth_socket - Authenticates against the Unix socket file, using so_peercred ● sha256_password - default-authentication-plugin=sha256_password, passwords never exposed as cleartext when connecting; SSL or RSA auth ● ed25519 - Elliptic Curve Digital Signature Algorithm, same as OpenSSH ● Kerberos/GSSAPI/SSPI - User principals: <username>@<KERBEROS REALM> ● Active Directory (Enterprise only) ● mysql_no_login ( MySQL 5.7 ) - prevents all client connections to an account that uses it #PerconaLive @bytebot @RonaldBradford 

28 . PAM authentication Percona Server MariaDB Server INSTALL PLUGIN auth_pam SONAME 'auth_pam.so'; INSTALL SONAME ‘auth_pam’; CREATE USER byte IDENTIFIED WITH auth_pam; CREATE USER byte IDENTIFIED via pam USING ‘mariadb’; In /etc/pam.d/mysqld: Edit /etc/pam.d/mariadb: auth required pam_warn.so auth required pam_unix.so audit auth required pam_unix.so account required pam_unix.so audit account required pam_unix.so Just use —pam-use-cleartext-plugin for MySQL to use mysql_cleartext_password instead of dialog plugin #PerconaLive @bytebot @RonaldBradford 

29 .SQL Standard Roles ● Bundles users together, with similar privileges - follows the SQL standard ● MariaDB Server 10.0 (10.1 adds that each user can have a DEFAULT ROLE) ● MySQL 8.0 DMR CREATE ROLE audit_bean_counters; GRANT SELECT ON accounts.* to audit_bean_counters; GRANT audit_bean_counters to ceo; https://mariadb.com/kb/en/mariadb/roles_overview/ https://dev.mysql.com/doc/refman/8.0/en/roles.html #PerconaLive @bytebot @RonaldBradford