展开查看详情
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
热门城市
