MySQL and MariaDB Server security essentials

1 .MySQL/MariaDB Server security essentials Colin Charles, Chief Evangelist, Percona Inc. colin.charles@percona.com / byte@bytebot.net http://www.bytebot.net/blog/ | @bytebot on Twitter SCALE15X, Pasadena, California 4 March 2017 

2 . whoami • Chief Evangelist (in the CTO office), 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 2 

3 .Historically… • No password for the ‘root’ user • There is a default ‘test’ database • Find a password from application config files (wp-config.php, drupal’s settings.php, etc.) • Are your datadir permissions secure (/var/lib/mysql)? • can you run strings mysql/user.MYD ? 

4 .Can you view privileges to find a user with more access? SELECT host,user,password from mysql.user; +--------------+-------------------+----------+ | host | user | password | +--------------+-------------------+----------+ | localhost | root | | | sirius | root | | | 127.0.0.1 | root | | | ::1 | root | | | localhost | | | | sirius | | | +--------------+-------------------+----------+ 

5 .More things to think about • Does replication connection have global permissions? • If you can start/stop mysqld process, you can reset passwords • Can you edit my.cnf? You can run a SQL file when mysqld starts with init-file 

6 .sql_mode • 5.6 default = NO_ENGINE_SUBSTITUTION • SQL_MODE = STRICT_ALL_TABLES, NO_ENGINE_SUBSTITUTION • Keeps on improving, like deprecating NO_ZERO_DATE, NO_ZERO_IN_DATE (5.6.17) and making it part of strict mode 

7 .Signs of poor security • old_passwords • 'root' MySQL user • Users without passwords • Generic OS DBA user e.g. • Anonymous users 'dba' • GRANT privilege users • Disabled OS Firewall/SELinux/ Apparmor • ALL privilege users • Open data directory privileges • '%' host user accounts • Default test database • 'root' MySQL user without password 

8 .mysql_secure_installation • Pretty basic to run, but many don’t • Remove anonymous users • Remove test database • Remove non-localhost root users • Set a root password 

9 .Creating users • The lazy way • CREATE USER ‘foo’@‘%’; • GRANT ALL ON *.* TO ‘foo’@‘%’; • The above gives you access to all tables in all databases + access from any external location • ALL gives you a lot of privileges, including SHUTDOWN, SUPER, CHANGE MASTER, KILL, USAGE, etc. 

10 .SUPER privileges • Can bypass a read_only server • Can bypass init_connect • Can disable binary logging • Can dynamically change configuration • Reached max_connections? Can still make one connection • https://dev.mysql.com/doc/refman/5.6/en/privileges- provided.html#priv_super • SUPER Read Only: prohibit client updates for everyone 

11 .So… only give users what they need • CREATE USER ‘foo’@‘localhost’ IDENTIFIED by ‘password’; • GRANT CREATE, SELECT, INSERT, UPDATE, DELETE on db.* to ‘foo’@‘localhost’; 

12 .And when it comes to applications… • Viewer? (read only access only) • SELECT • User? (read/write access) • SELECT, INSERT, UPDATE, DELETE • DBA? (provide access to the database) • CREATE, DROP, etc. 

13 .Installation • Using your Linux distribution… mostly gets you MariaDB when you ask for mysql-server • Except on Debian/Ubuntu ▪ However, when you get mariadb-server, you get an authentication plugin — auth_socket for “automatic logins” ▪ You are asked by debhelper to enter a password • You can use the APT/YUM repositories from Oracle MySQL, Percona or MariaDB • Don’t disable SELinux: system_u:system_r:mysqld_t:s0 

14 .Enable log-warnings • Enable —log_warnings=2 • Can keep track of access denied messages • Worth noting there are differences here in MySQL & MariaDB • https://dev.mysql.com/doc/refman/5.6/en/server- options.html#option_mysqld_log-warnings • https://mariadb.com/kb/en/mariadb/server-system-variables/ #log_warnings 

15 .MySQL 5.6 improvements • Password expiry • ALTER USER 'foo'@'localhost' PASSWORD EXPIRE; • https://dev.mysql.com/doc/refman/5.6/en/password-expiration- sandbox-mode.html • Password validation plugin • VALIDATE_PASSWORD_STRENGTH() 

16 .MySQL 5.6 II • 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 stored in $HOME/.mysql_secret 

17 .MySQL 5.7 • 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; • There is also account locking/unlocking now • ACCOUNT LOCK/ACCOUNT UNLOCK 

18 .SSL • You’re using the cloud and you’re using replication… you don’t want this in cleartext • Setup SSL (note: yaSSL vs OpenSSL can cause issues) • https://dev.mysql.com/doc/refman/5.6/en/ssl-connections.html • Worth noting 5.7 has a new tool: mysql_ssl_rsa_setup 

19 .Initialise data directory using mysqld now • mysql_install_db is deprecated in 5.7 • mysqld itself handles instance initialisation • mysqld —initialize • mysqld —initialize-insecure 

20 .MariaDB passwords • Password validation plugin (finally) exists now • https://mariadb.com/kb/en/mariadb/development/mariadb-internals- documentation/password-validation/ • 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 

21 .authentication plugins 

22 .What you do today • MySQL stores accounts in the user table of the my mysql database • CREATE USER ‘foo’@‘localhost’ IDENTIFIED BY ‘password’; 

23 .select plugin_name, plugin_status from information_schema.plugins where plugin_type='authentication'; +-----------------------+---------------+ | plugin_name | plugin_status | +-----------------------+---------------+ | mysql_native_password | ACTIVE | | mysql_old_password | ACTIVE | +-----------------------+---------------+ 2 rows in set (0.00 sec) 

24 .Subtle difference w/MariaDB & MySQL usernames • Usernames in MariaDB > 5.5.31? 80 character limit (which you have to reload manually) create user 'long12345678901234567890'@'localhost' identified by 'pass'; Query OK, 0 rows affected (0.01 sec) vs ERROR 1470 (HY000): String 'long12345678901234567890' is too long for user name (should be no longer than 16) 

25 .Installing plugins • MariaDB: INSTALL SONAME ‘auth_socket’ • MySQL: INSTALL PLUGIN auth_socket SONAME ‘auth_socket.so’ 

26 .auth_socket • Authenticates against the Unix socket file • Uses so_peercred socket option to obtain information about user running client • CREATE USER ‘foo’@‘localhost’ IDENTIFIED with auth_socket; • Refuses connection of any other user but foo from connecting 

27 .sha256_password • Default in 5.6, needs SSL-built MySQL (if using it, best to set it in my.cnf) • default-authentication-plugin=sha256_password • Default SSL is yaSSL, but with OpenSSL you get RSA encryption • client can transmit passwords to RSA server during connection • There exists key paths for private/public keys • Passwords never exposed as cleartext when connecting 

28 .PAM Authentication • MySQL PAM • Percona PAM (auth_pam & auth_pam_compat) • MariaDB PAM (pam) 

29 .Let’s get somethings out of the way • PAM = Pluggable Authentication Module • Use pam_ldap to to authenticate credentials against LDAP server — configure /etc/pam_ldap.conf (you also obviously need /etc/ ldap.conf) • Simplest way is of course /etc/shadow auth