Securing your database servers from external attacks

下载 0

无羡

发布于

1591

人观看

#信息技术

您的基础结构的一个关键部分是数据库层，但是人们没有足够的关注它，因为通过错误选择的默认值来判断有多少被咬，或者只是缺乏对运行安全数据库层的理解。在本文中，我将重点介绍mysql/mariadb、postgresql和mongodb，并介绍Percona 4000多个客户在这方面经过十多年的咨询后获得的外部认证、审核、加密、SSL、防火墙、复制和更多gems。

展开查看详情

1 .Securing your database servers from external attacks Colin Charles, Chief Evangelist, Percona Inc. colin.charles@percona.com / byte@bytebot.net http://bytebot.net/blog/ | @bytebot on Twitter Percona Webinar 28 June 2018

2 .whoami • Chief Evangelist, Percona Inc • Focusing on the MySQL ecosystem (MySQL, Percona Server, MariaDB Server), as well as the MongoDB ecosystem (Percona Server for MongoDB) + 100% open source tools from Percona like Percona Monitoring & Management, Percona xtrabackup, Percona Toolkit, etc. Now supporting PostgreSQL too! “To champion unbiased open source database solutions” • Founding team of MariaDB Server (2009-2016) [Monty Program Ab, merged with SkySQL Ab, now MariaDB Corporation] • Formerly MySQL AB (exit: Sun Microsystems) • Past lives include The Fedora Project (FESCO), OpenOffice.org • MySQL Community Contributor of the Year Award winner 2014

3 .License • Creative Commons BY-NC-SA 4.0 • https://creativecommons.org/licenses/by-nc-sa/4.0/legalcode  

4 .Attack vectors • External • Trust based security, password/authentication method theft, network snooping or spoofing, theft of physical server, administrator • Internal • Permissions, SQL injection, application itself vulnerable, operating system vulnerabilities

5 .So first, attack vectors from a high level

6 .Trust based security • PostgreSQL calls it “trust authentication” which you can enable for local connections (e.g. via socket authentication) • In MariaDB Server, this is provided via Unix socket (and MySQL calls it socket peer credentials) • Why is this probably not a good thing? • Ref: https://www.postgresql.org/docs/10/static/auth-pg-hba- conf.html / https://mariadb.com/kb/en/library/authentication-plugin- unix-socket/ / https://dev.mysql.com/doc/refman/5.5/en/socket- pluggable-authentication.html

7 .Password snooping • In PostgreSQL, you have MD5, but also scram-sha-256, which will avoid risk of duplicate salt values being replayed • In MySQL, you want to now use the caching_sha2_password (better performance compared to sha256_password) • In MariaDB Server, there’s ed25519

8 .Password attacks • Weak passwords? • Reusing old passwords? • Brute-force password attacks? • By far, the best here is MySQL 8 • MariaDB Server can help against weak passwords • PostgreSQL itself doesn’t, but you might use external authentication like LDAP/PAM/SSPI to help prevent • MongoDB has external authentication via LDAP (Percona Server for MongoDB)

9 .Network operations… • Connections, replication, all must use SSL • Overhead argument? Performance hit is quite negligible • MySQL 8 has SSL based connections on by default • You never want replication in plaintext (especially with "the cloud”) • Don’t just “prefer”/require SSL - without SSL root certs, there is no way to know if you’re connecting to a legitimate server. Make sure you’re verifying the certificate authority • ref: https://www.percona.com/blog/2017/06/27/ssl-connections-in-mysql-5-7/ / https://www.percona.com/blog/2017/09/19/proxysql-improves-mysql-ssl- connections/ (help via a proxy) / http://databaseblog.myname.nl/2017/05/ mysql-and-ssltls-performance.html

10 .Time for some pretty graphs…

11 .Data theft • Disk volume encryption • PostgreSQL has column encryption • MariaDB Server and MySQL have at-rest encryption of your entire InnoDB tablespace

12 .Let’s MySQL…

13 .MySQL Variants • MySQL Community Edition • Won’t cover: 3.23, 4.0, 4.1, 5.0, 5.1 • Will focus on: 5.5, 5.6, 5.7, and the newly released 8.0 • MySQL Enterprise Edition • Percona Server for MySQL • 5.5, 5.6, 5.7 • MariaDB Server • Won’t cover: 5.1, 5.2, 5.3 • 5.5, 10.0, 10.1, 10.2, with 10.3 as an alpha • Galera Cluster, Group Replication/InnoDB Cluster • X Protocol/mysqlsh (33060) • What we won’t cover: MySQL Cluster (NDBCLUSTER)

14 .Structured Query Language (SQL) • ISO/IEC 9075 (reviewed every 5 years), SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2006, SQL:2008, SQL:2011, SQL:2016 • select @@global.sql_mode; • ANSI - come close to the SQL standard • STRICT_TRANS_TABLES - If a value could not be inserted as given into a transactional table, abort the statement. • TRADITIONAL - “give an error instead of a warning” when inserting an incorrect value into a column. • https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html • Deprecated - MariaDB Server has NO_AUTO_CREATE_USER but MySQL 5.7 has this in standard sql_mode

15 .MySQL security by version • GRANT (3.23) • ALTER USER (5.6) • REVOKE (3.23) • SHOW CREATE USER (5.7) • SET PASSWORD (3.23) • CREATE ROLE (8.0) • SHOW GRANTS (3.23) • DROP ROLE (8.0) • DROP USER (4.1) • SET ROLE (8.0) • SHOW PRIVILEGES (4.1) • SET DEFAULT ROLE (8.0) • CREATE USER (5.0) • N/B: ROLES came to MariaDB • RENAME USER (5.0) Server in 10.0, and the DEFAULT ROLE came in 10.1

16 .mysql.user table • host • user • password (removed in 5.7; still present in MariaDB) • plugin (5.5) • authentication_string (5.5) • password_expired (5.6) • account_locked (5.7) Comparing mysql.user • Create_role_priv (8.0) between MariaDB Server 10.2 and MySQL 5.7 • Drop_role_priv (8.0)

17 .Key security features by version • 5.1 - McAfee Audit plugin (optional random on install), • MariaDB 10.1 - default roles, at • 5.5 - pluggable authentication mysql.user password_expired rest table/tablespace (MariaDB 5.2 backport), proxy column; Enterprise Firewall encryption, users, changes in mysql.user • 5.7 - grep for root password on simple_password_check, table, client password warning; installation, password expiry cracklib_password_check, Enterprise provided Audit and every ‘n’ days, user accounts AWS Key Management plugin PAM authentication (present can be locked/unlocked, • MariaDB 10.2 - user limits, again in Percona Server for mysql_ssl_rsa_setup, ed25519 auth MySQL and MariaDB Server) mysql.user.password removed, • Percona Server for MySQL 5.5 • 5.6 - encrypted client super_read_only, at rest - extended SHOW GRANTS, credentials tablespace encryption utility user, userstats (mysql_config_editor), • 8.0 - roles + mysql.user • Percona Server for MySQL 5.6 sha256_password, password changes - super_read_only expiry, • MariaDB 10.0 - roles, userstats VALIDATE_PASSWORD_STRE • Percona Server for MySQL 5.7 NGTH(), --random-passwords - Vault plugin

18 .Installation Default Passwords • 'root' user • Pre 5.7 no password • 5.7 expired random password • Anonymous users • Removed in 5.7

19 .How are passwords stored in MySQL? (5.5) mysql55 >SELECT /* 5.5 */ host, user, password, plugin, authentication_string FROM mysql.user;  +-----------+------+----------+--------+-----------------------+  | host | user | password | plugin | authentication_string |  +-----------+------+----------+--------+-----------------------+  | localhost | root | | | |  | mysql55 | root | | | |  | 127.0.0.1 | root | | | |  | ::1 | root | | | |  | localhost | | | | NULL |  | mysql55 | | | | NULL |  +-----------+------+----------+--------+-----------------------+  6 rows in set (0.00 sec) 

20 .How are passwords stored in MySQL? (5.6) mysql56 >SELECT /* 5.6 */ host, user, password, plugin, authentication_string, password_expired FROM mysql.user;  +-----------+------+----------+-----------------------+-----------------------+------------------+  | host | user | password | plugin | authentication_string | password_expired |  +-----------+------+----------+-----------------------+-----------------------+------------------+  | localhost | root | | mysql_native_password | | N |  | mysql56 | root | | mysql_native_password | | N |  | 127.0.0.1 | root | | mysql_native_password | | N |  | ::1 | root | | mysql_native_password | | N |  | localhost | | | mysql_native_password | NULL | N |  | mysql56 | | | mysql_native_password | NULL | N |  +-----------+------+----------+-----------------------+-----------------------+------------------+  6 rows in set (0.00 sec) 

21 .How are passwords stored in MySQL? (5.7) mysql57 >SELECT /* 5.7 */ host, user, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user;  +-----------+---------------+-----------------------+------------------------------------------- +------------------+-----------------------+-------------------+----------------+  | host | user | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked |  +-----------+---------------+-----------------------+------------------------------------------- +------------------+-----------------------+-------------------+----------------+  | localhost | root | mysql_native_password | *E89C1DBB80A00976B61D19025C3081E4B190D8BE | N | 2017-09-03 18:45:43 | NULL | N |  | localhost | mysql.session | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2017-09-03 18:42:33 | NULL | Y |  | localhost | mysql.sys | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2017-09-03 18:42:33 | NULL | Y |  +-----------+---------------+-----------------------+------------------------------------------- +------------------+-----------------------+-------------------+----------------+  3 rows in set (0.01 sec) 

22 .How are passwords stored in MySQL? (8.0) mysql> SELECT /* 8.0 */ host, user, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked FROM mysql.user; +-----------+------------------+-----------------------+------------------------------------------------------------------------ +------------------+-----------------------+-------------------+----------------+ | host | user | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | +-----------+------------------+-----------------------+------------------------------------------------------------------------ +------------------+-----------------------+-------------------+----------------+ | localhost | mysql.infoschema | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2018-04-25 13:04:15 | NULL | Y | | localhost | mysql.session | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2018-04-25 13:04:15 | NULL | Y | | localhost | mysql.sys | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N | 2018-04-25 13:04:15 | NULL | Y | | localhost | root | caching_sha2_password | $A$005$hqy-OG+.:|qsypaH/HS.i19CInGfOtklCz3kyo4cZxqCFy2bEHcogi6/ | N | 2018-04-25 13:04:19 | NULL | N | +-----------+------------------+-----------------------+------------------------------------------------------------------------ +------------------+-----------------------+-------------------+----------------+ 4 rows in set (0.00 sec)

23 .Minimum password policy (5.7+) mysql> alter user root identified by 'percona'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> select @@validate_password_policy; +----------------------------+ | @@validate_password_policy | +----------------------------+ | LOW | +----------------------------+ 1 row in set (0.00 sec) https://dev.mysql.com/doc/refman/5.7/en/validate-password-options- variables.html#sysvar_validate_password_policy

24 .VALIDATE_PASSWORD_STRENGTH() mysql> select validate_password_strength('percona'); +---------------------------------------+ | validate_password_strength('percona') | +---------------------------------------+ | 25 | +---------------------------------------+ 1 row in set (0.00 sec) mysql> select validate_password_strength('Percona.123'); +-------------------------------------------+ | validate_password_strength('Percona.123') | +-------------------------------------------+ | 100 | +-------------------------------------------+ 1 row in set (0.00 sec)

26 .mysql_native_password format (deprecated in 5.7) mysql56 > SELECT PASSWORD('test123') AS pwd;  +-------------------------------------------+  | pwd |  +-------------------------------------------+  | *676243218923905CF94CB52A3C9D3EB30CE8E20D |  +-------------------------------------------+  1 row in set (0.00 sec)    mysql57 > SELECT PASSWORD('test123') AS pwd;  +-------------------------------------------+  | pwd |  +-------------------------------------------+  | *676243218923905CF94CB52A3C9D3EB30CE8E20D |  +-------------------------------------------+  1 row in set, 1 warning (0.00 sec)  https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_old_passwords https://dev.mysql.com/doc/refman/5.7/en/password-hashing.html

27 .Security Hardening 101 • Set a password for ‘root’ • Remove anonymous users • SELECT host, user, password FROM mysql.user WHERE user=‘’; • SELECT host, user, authentication_string FROM mysql.user WHERE user=‘’; • Remove the ‘test’ database • All the above is taken care of with mysql_secure_installation • 8.0 is awesome in the sense that the test database is not there by default, you can turn on the VALIDATE_PASSWORD_PLUGIN(), and so on

28 .Why are anonymous users bad? mysql55 >USE test; mysql55 >CREATE TABLE t1(i1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, v1 VARCHAR(100) NOT NULL); mysql55 >INSERT INTO t1(i1, v1) VALUES (1, REPEAT('abcde',20)); mysql55 >INSERT INTO t1(i1, v1) SELECT NULL, a.v1 FROM t1 a, t1 b, t1 c; Query OK, 1 row affected (0.00 sec) mysql55 >INSERT INTO t1(i1, v1) SELECT NULL, a.v1 FROM t1 a, t1 b, t1 c; Query OK, 8 rows affected (0.00 sec) mysql55 >INSERT INTO t1(i1, v1) SELECT NULL, a.v1 FROM t1 a, t1 b, t1 c; Query OK, 1000 rows affected (0.02 sec) # Does it return in your VM, or fill your host disk? Be Destructive

29 .MySQL 5.6 improvements ● Password expiry - ALTER USER 'foo'@'localhost' PASSWORD EXPIRE; ● Password validation plugin - VALIDATE_PASSWORD_STRENGTH() ● 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 ○ cat $HOME/.mysql_secret

0点赞

0收藏

0下载