2014 opensuse conf: protect your mysql server

Protect Your ServerDos and Don’ts of secure MySQL Deployment.

Copyright © 2014, Oracle and/or its affiliates. All rights reserved.2

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.


Agenda

The post-install situation

How to harden it ?

More security

Security related changes in MySQL 5.7


• Former banking IT Manager• Veteran software developer• Leading the MySQL Server General

development team• Been with MySQL since 2006• Regular MySQL conference speaker

About Me


The Post-Install Situation :MySQL Server security in OpenSuse 13.1


The Good News


MySQL 5.6.12The Good News

Only 5 MRUs away from dev.mysql.com/downloads !– New authentication method sha256_password

– Manual password expiration : ALTER USER EXPIRE

– Password strength verification plugin and API

– Login paths

– Support SSL CRLs and key files with pass phrases

– Use SSL library’s random generator

– Obfuscate passwords in logs


Installation Layout

MySQL server service not on by default Separate mysql-community-server-test rpm Separate mysql-community-server-tools rpm No pre-packaged database No remote access by default

The Good News


The Not So Good News


MySQL 5.6.12

3 CPUs and 24 CVE reported security bugs away from 5.6.15 (last CVE)

More than 500 other bugs away from 5.6.18 (current) Lacks the advanced AES function modes



Installation layout

mysql_secure_installation not run– Anybody can connect as root

– Anonymous access to the server allowed

– No password strength checks

– Empty passwords for the default accounts

– Anybody gets full access to the test database

mysql_config_editor not in mysql-community-server-client



Installation layout. Continued.

Federated plugin installed by default Archive plugin actually not needed (error on startup) Some testing only authentication plugins installed by mysql-community-server

No SSL certificates. Even self-signed ones secure_file_priv set to NULL

– grants SQL read and write access to the full OS file system



Installation layout. The Sequel.

sha256_password plugin under-configured: no RSA keys No query logging: neither audit nor query log mysqld listens on all network interfaces



Random (Not So) Funny StoryRecognize the pattern ?

New Code


WHAT YOU GET IS A DEVELOPMENT INSTALLATION !


How to Harden Your MySQL installation ?


Post Server Installation

Run mysql_secure_installation ! Now ! Review and restrict the network interfaces that the server listens on Generate SSL keys and make sure the server can “talk” SSL Enable query logging. Create a log backup policy. Remove extra user accounts and privileges Remove unneeded files and packages Schedule regular backups !

Hardening your MySQL installation


Post Application(s) Installation

Remove extra user accounts. Restrict the remaining ones Review and maximally restrict the grants Make sure the user accounts authenticate using a reliable method Clean up extra temp files Make sure backups are still on and cover the new objects Remove unneeded files and packages Audit the server configuration for changes. Revert the bogus ones



Daily MySQL Use

Keep your installation up to date Monitor your server logs. Set alerts for “unusual” patterns. Monitor security related stats. Set alerts for “unusual” patterns. Monitor the server configuration. Monitor and verify the backups and their integrity Regularly probe your “defenses” by trying bad things on purpose Perform regular emergency drills Set procedures on maintaining your user account base



More Security


Harden your MySQL Server Instance

Consider turning off TCP/IP if your setup allows it Use and enforce SSL if you need TCP/IP

– Even self-signed will do. Part of PKI is better

Use SSL certificate requirements for users– GRANT … TO …. REQUIRE [CIPHER | ISSUER | SUBJECT] …

Be careful with your directories– tmpdir, datadir, secure-file-priv, plugin-dir

Additional steps



Monitor and keep the logs– Consider using an auditing plugin

– put extra protection on sensitive tables: custom logging triggers etc

Consider using external authentication– PAM, LDAP, windows domain

Harden your password policy– MySQL has a plugin for that !

Use login paths for your scripts

Even more steps



Parameter Recommended Value

secure_file_priv Designated directory

symbolic_links Boolean NO

default-storage-engine InnoDB

general-log Boolean ON

log-raw Default : OFF

skip-networking ON, if you can afford it.

ssl options Set to valid values

Useful parameters to set



Parameter Recommended Value

plugin-dir Designated read-only directory

chroot Designated directory, if you can afford it

core-file OFF

des-key-file File with DES keys

read_only ON for slaves !

sha256_password RSA key RSA public private keys if can’t use SSL

tmpdir Designated directory out of secure-file-priv

Useful parameters to set


New Security Features in MySQL 5.7 DMRs


Security Features in 5.7 DMRs

Audit log plugin works with Audit Vault Login paths and mysql_config_editor --syslog option to mysql Mark mysql_old_password (pre- 4.1 password format) as deprecated

5.7.1: 23 April 2013



Require explicit authentication plugin for all user accounts Rewrite mysql_secure_installation to C and harden it

– Enables password strength validation

– Generates random password for root and marks it as expired

– Restricts the root user so it can login only from localhost

Deprecate ENCODE()/DECODE() --error-log-verbosity control Client side protocol tracing plugins in libmysql

5.7.2: 21 Sep 2013



Redefine the meaning of the –ssl option– --ssl on the client enforces SSL now

– Other –ssl options enable ssl, but not enforce it

Proper connection state reset : mysql_reset_connection()

5.7.3: 3 Dec 2013



RPM packages secure by default– The effect of mysql_secure_installation by default

– Separate packages for non-essential tools and utilities

Automatic timed password expiration– Per site and per user

AES_ENCRYPT()/AES_DECRYPT() now support block modes and larger key sizes

Strong crypto random SQL function added: RANDOM_BYTES()

5.7.4: 31 Mar 2014


Questions ? Suggestions ?

2014 opensuse conf: protect your mysql server

Software

oracle andor

mysql installation

mysql server security

mysql community

test database mysql

server listens

server logs

server configuration