Enhancing MySQL Security

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 ▪ ▪