Enhancing MySQL Security

当涉及到数据时,安全性总是一个挑战。更重要的是,像gdpr这样的法规在其基础上增加了一个全新的层,对访问和操作数据的规则也越来越严格。在本演示中,请与我们一起检查安全性最佳实践,以及可用于MySQL的传统和新功能,包括新MySQL8附带的功能。
在本文中,DBA和SysAdmins将介绍OS和MySQL上可用的安全特性。这些功能包括:
-所以安全性
-SSL
-ACL
-TDE
-审核插件
-MySQL8特性(撤销、重做和binlog加密)
-新缓存\ sha2 \密码
-角色
-密码管理
-FIPS模式

展开查看详情

1.

2.▪ ▪ ▪ ▪ ▪ ▪ ▪

3.▪ ▪ ▪ ▪

4.▪ ▪ ▪ ▪ ▪ ▪ ▪ ▪ caching_sha2_password ▪ ▪

5.● ● ● ● ●

6.▪ ▪ ▪ ▪ ▪ ▪ ▪ ▪ ▪ ▪

7.

8.• • • • • •

9.

10.▪ ▪ ▪ ▪ ▪ ▪ ▪ ▪ caching_sha2_password ▪ ▪

11.

12.• • • • ▪ • mysql_ssl_rsa_setup ▪ • openssl

13.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)

14.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

15.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

16.

17.▪ ▪ ▪ ▪ ▪ ▪ ▪ ▪ caching_sha2_password ▪ ▪

18.

19.• • validate_password

20.•

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

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

23.

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

25.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)

26.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)

27.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)

28.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)

29.▪ ▪ ▪ ▪ ▪ ▪ ▪ ▪ caching_sha2_password ▪ ▪