Enhancing MySQL security

当我们谈到数据时,安全性总是一个挑战,但是像gdpr这样的法规在数据之上带来了一个新的层,对访问和操作数据的规则越来越严格。加入我们的演示,查看安全最佳实践、MySQL可用的传统和新功能,包括新MySQL8附带的功能。
在本次谈话中,DBA和SysAdmins将介绍OS和MySQL上可用的安全功能:
所以安全性
SSL
国际计算语言学协会
时域微分方程
审核插件
MySQL8特性(撤销、重做和binlog加密)
新缓存\ sha2 \密码
角色
密码管理
FIPS模式
我们将与2000名支持客户分享我们的工作经验,帮助观众熟悉所有安全概念和方法,并为您提供应用于您的环境的必要知识。

展开查看详情

1.Enhancing MySQL Security Vinicius Grippa Percona

2.About me • Support Engineer at Percona since 2017 Working with MySQL for over 5 years - Started with SQL Server • Working with databases for 7 years 2

3.Agenda • OS/Cloud security • SSL • Password management • Audit plugin • Percona Server encryption features • MySQL 8 features (undo, redo encryption) • TDE • New caching_sha2_password • FIPS mode • Roles 3

4.Basic Principles • Minimum access • Isolate • Audit • Avoid spying • Default firewall 4

5.Agenda • OS/Cloud security • SSL • Password management • Audit plugin • Percona Server encryption features • MySQL 8 features (undo, redo encryption) • TDE • New caching_sha2_password • FIPS mode • Roles 5

6.OS/Cloud Security

7.OS/Cloud security • Uninstall services that are not used • Do not run compilers • Firewalls • Block internet access • Disable remote root login • Use of SSH Key • AppArmor / SELinux 7

8.OS/Cloud security • Use of Amazon Virtual Private Cloud (VPC) • Use AWS Identity and Access Management (IAM) policies • Use security groups 8

9.Agenda • OS/Cloud security • SSL • Password management • Audit plugin • Percona Server encryption features • MySQL 8 features (undo, redo encryption) • TDE • New caching_sha2_password • FIPS mode • Roles 9

10.SSL

11.SSL • Move information over a network in a secure fashion • SSL provides an way to cryptograph the data • Default for MySQL 5.7 or higher • Certificates • MySQL 5.7 - mysql_ssl_rsa_setup • MySQL 5.6 - openssl 11

12.SSL mysql > show global variables like '%ssl%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | ca.pem | | ssl_capath | | | ssl_cert | server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | server-key.pem | +---------------+-----------------+ 9 rows in set (0.03 sec) 12

13.SSL mysql: root@localhost ((none)) GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'%' IDENTIFIED BY 'sekret' REQUIRE SSL; Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.01 sec) [root@node1 ~]# mysql -ussluser -psekret --ssl-cert=/var/lib/mysql/client-cert.pem --ssl-key=/var/lib/mysql/client-key.pem --ssl-ca=/var/lib/mysql/ca.pem -h 127.0.0.1 -P 3306 -e " \s"| grep SSL mysql: [Warning] Using a password on the command line interface can be insecure. SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256 13

14.SSL It is also possible to set ssl-mode to ensure that all connections use SSL. This option is available only for client programs, not the server. [client] ssl-mode=required 14

15.SSL 15

16.Agenda • OS/Cloud security • SSL • Password management • Audit plugin • Percona Server encryption features • MySQL 8 features (undo, redo encryption) • TDE • New caching_sha2_password • FIPS mode • Roles 16

17.Password Management

18.Password Management • Password expiration • validate_password plugin 18

19.Password expiration • MySQL enables database administrators to expire account passwords manually, and to establish a policy for automatic password expiration. Expiration policy can be established globally, and individual accounts can be set to either defer to the global policy or override the global policy with specific per-account behavior. 19

20.Password expiration Individual accounts mysql> create user test_expired_user@localhost identified by 'Sekr$K1et' PASSWORD EXPIRE INTERVAL 1 day; Query OK, 0 rows affected (0.01 sec) Globally mysql> SET GLOBAL default_password_lifetime = 1; 20

21.Password expiration mysql: test_expired_user@localhost ((none)) > show databases; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. 21

22.validate_plugin Its main purpose is to test passwords and improve security. It is possible to ensure the strength, length and required characters of the password. 22

23.validate_plugin - Installing # Runtime mysql: root@localhost ((none)) > INSTALL PLUGIN validate_password SONAME 'validate_password.so'; Query OK, 0 rows affected (0.07 sec) # my.cnf [mysqld] plugin-load-add=validate_password.so 23

24.validate_plugin - Validate mysql: root@localhost ((none)) > show global variables like '%plugin%'; +-------------------------------+--------------------------+ | Variable_name | Value | +-------------------------------+--------------------------+ | default_authentication_plugin | mysql_native_password | | plugin_dir | /usr/lib64/mysql/plugin/ | +-------------------------------+--------------------------+ 2 rows in set (0.00 sec) 24

25.validate_plugin - Validate mysql: root@localhost ((none)) > SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'validate%'; +-------------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +-------------------+---------------+ | validate_password | ACTIVE | +-------------------+---------------+ 1 row in set (0.00 sec) 25

26.validate_plugin - Example mysql: root@localhost ((none)) > set global validate_password_length = 6; Query OK, 0 rows affected (0.00 sec) mysql: root@localhost ((none)) > set global validate_password_policy=2; Query OK, 0 rows affected (0.00 sec) 26

27.validate_plugin - Example mysql: root@localhost ((none)) > create user test_password@localhost identified by 'PasSw0Rd'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql: root@localhost ((none)) > create user test_password@localhost identified by 'PasSw0Rd12@'; Query OK, 0 rows affected (0.00 sec) 27

28.Agenda • OS/Cloud security • SSL • Password management • Audit plugin • Percona Server encryption features • MySQL 8 features (undo, redo encryption) • TDE • New caching_sha2_password • FIPS mode • Roles 28

29.Audit Plugin