MySQL security best practices

1 .MySQL security best practices A 101 webinar presented by Dimitri Vanoverbeke 

2 . Whoami Dimitri Vanoverbeke Solution Engineer in Percona 2 www.percona.com 

3 . MySQL Security 101 overview • Having a security mindset. • Infrastructure • Operating system • Applications • MySQL privileges • SSL communication • Handling ransomware • Encryption options 3 www.percona.com 

4 . Have the correct mindset • Applications should be written with security from the ground up. • Work together with your sysadmins and devteam to make the correct choices. • Disable and restrict remote access • Understand the cloud means working on other peoples computers. • Restrictive mindset 4 www.percona.com 

5 . Infrastructure: Network • Segregate your network • Only application servers should be able to connect to the DB remotely. • Dev access/general access should be limited by using a bastion/jumphost • DO NOT OPEN IT UP TO THE INTERNET!!! (or use strict firewall rules) • Example on https://www.shodan.io/search? query=mysql • IPS/IDS appliance/software can be handy • Snort, Bro Network Security monitor, OSSEC 5 www.percona.com 

6 . Be friends with your network engineer 6 www.percona.com 

7 . Operating system security • Deploy security patches as soon as possible. • Make sure permissions are correct: • mysql should be the owner • Don’t use chmod 777 :-) • 0750 dirs , 0640 files • selinux setenforce 1 • If PCI compliant? • ecryptfs, luks, EBS encryption (starting at medium sizes) • Use trusted package sources! • Establishing a patching policy! 7 www.percona.com 

8 . Applications • Perform penetration tests on staging environments. • Validate user inputs • Watch out for SQL injections. 8 www.percona.com 

9 . Use configuration management • Use your favourite configuration management solution. Tools like puppet, chef, ansible and salt are excellent tools to ensure compliancy: $users = { 'dim0@localhost' => { ensure => 'present', max_connections_per_hour => '0', max_queries_per_hour => '0', max_updates_per_hour => '0', max_user_connections => '0', password_hash => '*T5D3A5831A93829BE2468926B4132313728C250DBF', }, } 9 www.percona.com 

10 . (Again) use configuration management (enforcement) • Configuration management will help you with: • Consistent and effective rollout of your configuration files • Compliancy • MySQL database version (security patches, feature updates, etc) • OS security updates • User management • Resource limitations • Documents environments • Ensures the correct packages are installed • Less manual work 10 www.percona.com 

11 . MySQL privileges • Limit your user privileges to key application servers. • Be restrictive for your users! • Use complex passwords • Use the password validation plugin! 11 www.percona.com 

12 . Password validation plugin 12 www.percona.com 

13 . Password requirement • Use a strict policy along your organisation • Make sure it’s not a dictionary word • Give limitations! • Don’t use root as a standard acces user to your database! 13 www.percona.com 

14 . MySQL Grants • Identify users based on: user@host • user: username • host: hostname/ip/network of the client that connects • different host, different user, different ‘grants’ • use of wildcards could be BAAAAD :) • Examples: ‘dim0’@‘localhost’, ‘root’@‘localhost’ ‘tommeketoch’@‘app0001’, ‘kenju’@‘192.168.%’ ‘ledijkske’@‘192.168.1.212’, ‘fredjen’@‘app.fq.dn’ • Creating A User: > CREATE USER 'dim0'@'app0001'; • Drop user: change CREATE into DROP > DROP USER [IF EXISTS] ‘dim0’@‘app0001’; 14 www.percona.com 

15 . MySQL Grants (2) • Grant the user some kind of privilege • Grant ... to: server, trigger, database, stored procedure, table, view, column, index • Example: INSERT, SELECT, UPDATE, DELETE • SQL Command: > GRANT SELECT ON db.* TO ‘dim0’@‘app0001’; > GRANT INSERT ON *.* TO ‘dim0’@‘app0001’; • Revoking privileges: change GRANT into REVOKE 15 www.percona.com 

16 . MySQL grants (3) • Password Expiration Policy • Watch out for granting all privileges, the grant option or even super! • User Account Locking MySQL supports locking and unlocking user accounts using the ACCOUNT LOCK and ACCOUNT UNLOCK clauses 16 www.percona.com 

17 . Grants (Limit your resources) • For every user: max_user_connections mysql> GRANT USAGE ON db.* TO ‘dim0'@'localhost' WITH MAX_QUERIES_PER_HOUR 1000 MAX_UPDATES_PER_HOUR 999 MAX_CONNECTIONS_PER_HOUR 100 MAX_USER_CONNECTIONS 5; FLUSH USER_RESOURCES; It’s however not really popular… :-D 17 www.percona.com 

18 . SSL connection • SSL encryption to ensure in transit encryption. • Requirement for PCI and other security compliance. • Can give a slight performance penalty • AWS/RDS users should definitely have a look at this 18 www.percona.com 

19 . Handling ransomware • Apply all updates! • When is the last time you rebooted? • Again limit access to trusted services and users. • Make sure you have backups locally and offsite • TEST 19 www.percona.com 

20 . Encryption • Encrypting your filesystem is still the most popular option. • Since MySQL 5.7 table level encryption is included. 20 www.percona.com 

21 . Audit plugin • Example of output <AUDIT_RECORD "NAME"="Query" "RECORD"="23_2014-04-29T09:29:40" "TIMESTAMP"="2014-04-29T10:20:10 UTC" "COMMAND_CLASS"="select" "CONNECTION_ID"="49" "STATUS"="0" "SQLTEXT"="SELECT * from mysql.user" "USER"="root[root] @ localhost []” "HOST"="localhost" "OS_USER"="" "IP"="" /> • Output to syslog possible 21 www.percona.com 

22 . Closing remarks • Disable the use of the “LOAD DATA LOCAL INFILE” • Maybe use the pam plugin! Remember be restrictive!!! 22 www.percona.com 

23 . Percona Live Europe Call for Papers & Registration are Open! Championing Open Source Databases ▪ MySQL, MongoDB, Open Source Databases ▪ Time Series Databases, PostgreSQL, RocksDB ▪ Developers, Business/Case Studies, Operations ▪ September 25-27th, 2017 ▪ Radisson Blu Royal Hotel, Dublin, Ireland 23 © 2017 Percona Submit Your Proposal by July 17th! www.percona.com/live/e17