MySQL/Percona Server/MariaDB Server security features overview Colin - PowerPoint PPT Presentation

MySQL/Percona Server/MariaDB Server security features overview Colin Charles, Chief Evangelist, Percona Inc. colin.charles@percona.com / byte@bytebot.net http://bytebot.net/blog/ | @bytebot on Twitter Percona Live Santa Clara 2018, Santa Clara, California, USA 25 April 2018

whoami • Chief Evangelist, Percona Inc • Focusing on the MySQL ecosystem (MySQL, Percona Server, MariaDB Server), as well as the MongoDB ecosystem (Percona Server for MongoDB) + 100% open source tools from Percona like Percona Monitoring & Management, Percona xtrabackup, Percona Toolkit, etc. • 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 The Fedora Project (FESCO), OpenOffice.org • MySQL Community Contributor of the Year Award winner 2014 #PerconaLive

License • Creative Commons BY-NC-SA 4.0 • https://creativecommons.org/licenses/by-nc-sa/4.0/legalcode 
 #PerconaLive

MySQL Variants • MySQL Community Edition • Won’t cover: 3.23, 4.0, 4.1, 5.0, 5.1 • Will focus on: 5.5, 5.6, 5.7, and the newly released 8.0 • MySQL Enterprise Edition • Percona Server for MySQL • 5.5, 5.6, 5.7 • MariaDB Server • Won’t cover: 5.1, 5.2, 5.3 • 5.5, 10.0, 10.1, 10.2, with 10.3 as an alpha • What we won’t cover: MySQL Cluster (NDBCLUSTER), Galera Cluster, Group Replication/ InnoDB Cluster, X Protocol/mysqlsh (33060) #PerconaLive

Structured Query Language (SQL) • ISO/IEC 9075 (reviewed every 5 years), SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2006, SQL:2008, SQL:2011, SQL:2016 • select @@global.sql_mode; • ANSI - come close to the SQL standard • STRICT_TRANS_TABLES - If a value could not be inserted as given into a transactional table, abort the statement. • TRADITIONAL - “give an error instead of a warning” when inserting an incorrect value into a column. • https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html • Deprecated - MariaDB Server has NO_AUTO_CREATE_USER but MySQL 5.7 has this in standard sql_mode #PerconaLive

MySQL security by version • GRANT (3.23) • REVOKE (3.23) • SET PASSWORD (3.23) • SHOW GRANTS (3.23) • DROP USER (4.1) • SHOW PRIVILEGES (4.1) • CREATE USER (5.0) • RENAME USER (5.0) • ALTER USER (5.6) • SHOW CREATE USER (5.7) • CREATE ROLE (8.0) • DROP ROLE (8.0) • SET ROLE (8.0) • SET DEFAULT ROLE (8.0) • N/B: ROLES came to MariaDB Server in 10.0, and the DEFAULT ROLE came in 10.1 #PerconaLive

mysql.user table • host • user • password (removed in 5.7; still present in MariaDB) • plugin (5.5) • authentication_string (5.5) • password_expired (5.6) • account_locked (5.7) Comparing mysql.user • Create_role_priv (8.0) between MariaDB Server 10.2 and MySQL 5.7 • Drop_role_priv (8.0) #PerconaLive

Key security features by version • 5.1 - McAfee Audit plugin • 5.5 - pluggable authentication (MariaDB 5.2 backport), proxy users, changes in mysql.user table, client password warning; Enterprise provided Audit and PAM authentication (present again in Percona Server for MySQL and MariaDB Server) • 5.6 - encrypted client credentials (mysql_config_editor), sha256_password, password expiry, VALIDATE_PASSWORD_STRENGTH(), --random-passwords (optional random on install), mysql.user password_expired column; Enterprise Firewall • 5.7 - grep for root password on installation, password expiry every ‘n’ days, user accounts can be locked/unlocked, mysql_ssl_rsa_setup, mysql.user.password removed, super_read_only, at rest tablespace encryption • 8.0 - roles + mysql.user changes • MariaDB 10.0 - roles, userstats • MariaDB 10.1 - default roles, at rest table/tablespace encryption, simple_password_check, cracklib_password_check, AWS Key Management plugin • MariaDB 10.2 - user limits, ed25519 auth • Percona Server for MySQL 5.5 - extended SHOW GRANTS, utility user, userstats • Percona Server for MySQL 5.6 - super_read_only • Percona Server for MySQL 5.7 - Vault plugin #PerconaLive

Installation Default Passwords • 'root' user • Pre 5.7 no password • 5.7 expired random password • Anonymous users • Removed in 5.7 #PerconaLive

How are passwords stored in MySQL? (5.5) mysql55 >SELECT /* 5.5 */ host, user, password, plugin, authentication_string FROM mysql.user; 
 +-----------+------+----------+--------+-----------------------+ 
 | host | user | password | plugin | authentication_string | 
 +-----------+------+----------+--------+-----------------------+ 
 | localhost | root | | | | 
 | mysql55 | root | | | | 
 | 127.0.0.1 | root | | | | 
 | ::1 | root | | | | 
 | localhost | | | | NULL | 
 | mysql55 | | | | NULL | 
 +-----------+------+----------+--------+-----------------------+ 
 6 rows in set (0.00 sec) 
 #PerconaLive

How are passwords stored in MySQL? (5.6) mysql56 >SELECT /* 5.6 */ host, user, password, plugin, authentication_string, password_expired FROM mysql.user; 
 +-----------+------+----------+-----------------------+-----------------------+------------------+ 
 | host | user | password | plugin | authentication_string | password_expired | 
 +-----------+------+----------+-----------------------+-----------------------+------------------+ 
 | localhost | root | | mysql_native_password | | N | 
 | mysql56 | root | | mysql_native_password | | N | 
 | 127.0.0.1 | root | | mysql_native_password | | N | 
 | ::1 | root | | mysql_native_password | | N | 
 | localhost | | | mysql_native_password | NULL | N | 
 | mysql56 | | | mysql_native_password | NULL | N | 
 +-----------+------+----------+-----------------------+-----------------------+------------------+ 
 6 rows in set (0.00 sec) 
 #PerconaLive

How are passwords stored in MySQL? (5.7) mysql57 >SELECT /* 5.7 */ host, user, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user; 
 +-----------+---------------+-----------------------+------------------------------------------- +------------------+-----------------------+-------------------+----------------+ 
 | host | user | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | 
 +-----------+---------------+-----------------------+------------------------------------------- +------------------+-----------------------+-------------------+----------------+ 
 | localhost | root | mysql_native_password | *E89C1DBB80A00976B61D19025C3081E4B190D8BE | N | 2017-09-03 18:45:43 | NULL | N | 
 | localhost | mysql.session | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2017-09-03 18:42:33 | NULL | Y | 
 | localhost | mysql.sys | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2017-09-03 18:42:33 | NULL | Y | 
 +-----------+---------------+-----------------------+------------------------------------------- +------------------+-----------------------+-------------------+----------------+ 
 3 rows in set (0.01 sec) 
 #PerconaLive

How are passwords stored in MySQL? (8.0) mysql> SELECT /* 8.0 */ host, user, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user; +-----------+------------------+-----------------------+------------------------------------------------------------------------ +------------------+-----------------------+-------------------+----------------+ | host | user | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | +-----------+------------------+-----------------------+------------------------------------------------------------------------ +------------------+-----------------------+-------------------+----------------+ | localhost | mysql.infoschema | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2018-04-25 13:04:15 | NULL | Y | | localhost | mysql.session | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2018-04-25 13:04:15 | NULL | Y | | localhost | mysql.sys | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2018-04-25 13:04:15 | NULL | Y | | localhost | root | caching_sha2_password | $A$005$hqy-OG+.:|qsypaH/HS.i19CInGfOtklCz3kyo4cZxqCFy2bEHcogi6/ | N | 2018-04-25 13:04:19 | NULL | N | +-----------+------------------+-----------------------+------------------------------------------------------------------------ +------------------+-----------------------+-------------------+----------------+ 4 rows in set (0.00 sec) #PerconaLive