MySQL and MariaDB Server security essentials

Colin Charles于2017年3月4日以15倍的规模展示了“MySQL/Mariadb服务器安全要点”。


1.MySQL/MariaDB Server security essentials Colin Charles, Chief Evangelist, Percona Inc. / | @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), • 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 | | | | 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 • 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 • options.html#option_mysqld_log-warnings • #log_warnings

15.MySQL 5.6 improvements • Password expiry • ALTER USER 'foo'@'localhost' PASSWORD EXPIRE; • 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 • • 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) • • 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 • 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 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’; 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 ‘’

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