A MySQL client can establish an encrypted connection to a MySQL server. In standard configuration, a client connection is unencrypted, which can lead to data being intercepted on the way. The MySQL encryption can be done separately for each client connection, so both encrypted and unencrypted connections can be used simultaneously. It can also be configured as required for individual connections.
mysql> show variables like '%ssl%';
+---------------+----------------------------+
| Variable_name | Value |
+---------------+----------------------------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | /etc/mysql/ca-cert.pem |
| ssl_capath | |
| ssl_cert | /etc/mysql/server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /etc/mysql/server-key.pem |
+---------------+----------------------------+
9 rows in set (0.02 sec)
mysql> show variables like '%ssl%';
+---------------+----------------------------+
| Variable_name | Value |
+---------------+----------------------------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | /etc/mysql/ca-cert.pem |
| ssl_capath | |
| ssl_cert | /etc/mysql/server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /etc/mysql/server-key.pem |
+---------------+----------------------------+
9 rows in set (0.02 sec)
how to enable ssl
step.1.
Download and install the required mysql related packages
yum install mysql-server perl-DBD-MySQL perl-DBI
step.2.
create a directory
[root@node2 ~]# mkdir -p /home/mysql/certs/
step.3.
You need to issue certificates with 2048 bits and a validity of 3650 days. After this period, the certificates must be renewed or recreated. Depending on your requirements, you might lower the time frame for validity.
# Generate CA file
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem
# Generate server certifacte
openssl req -newkey rsa:2048 -days 3560 -nodes -keyout server-key.pem > server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
# Generate client certificate
openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem > client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
# Verify certificates
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
step.4.
[root@node2 certs]# openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK
step.5 .Add the following into the "/etc/my.cnf" file, under the "[mysqld]" section.
[root@node2 certs]# vi /etc/my.cnf
[mysqld]
#SSL
#ssl=1
ssl-ca=/home/mysql/certs/ca-cert.pem
ssl-cert=/home/mysql/certs/server-cert.pem
ssl-key=/home/mysql/certs/server-key.pem
step.7.Add in the following client section to the "/etc/my.cnf" file.
[root@node2 certs]# vi /etc/my.cnf
[client]
ssl-ca=/home/mysql/certs/ca-cert.pem
ssl-cert=/home/mysql/certs/client-cert.pem
ssl-key=/home/mysql/certs/client-key.pem
step.8. Restart the mysqld service.
[root@node2 certs]# systemctl restart mysqld
step.9.login mysql and check ssl and have_ssl variable
[pankaj@node2 certs]$ mysql --ssl-cert=ca-cert.pem --ssl-key=client-key.pem --ssl-cert=client-cert.pem -u root -p -v -v -v
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 262
Server version: 5.7.30-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Reading history-file /home/pankaj/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
You are enforcing ssl conection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
mysql>
mysql>
mysql>
[root@node3 ~]# mysql -u db_user -h 192.168.15.227 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.7.30-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show variables like '%ssl%';
+---------------+-----------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /home/mysql/certs/ca-cert.pem |
| ssl_capath | |
| ssl_cert | /home/mysql/certs/server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /home/mysql/certs/server-key.pem |
+---------------+-----------------------------------+
9 rows in set (0.00 sec)
mysql> show variables like '%have_ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl | YES |
+---------------+-------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| Ssl_cipher | ECDHE-RSA-AES128-GCM-SHA256 |
+---------------+-----------------------------+
1 row in set (0.02 sec)
step.9.
User Creation
Once SSL is configured, any connection to MySQL can optionally choose to use SSL or X509. The use of SSL can be forced using
REQUIRE. Using REQUIRE SSL means the client must have access to the "ca-cert.pem" certificate. Using "REQUIRE X509" means the client also needs access to the client certificate and key. We can test this using the following users.
CREATE DATABASE IF NOT EXISTS pankaj DEFAULT CHARACTER SET utf8;
CREATE USER 'ssl_user'@'%' identified 'Ssluser@123';
GRANT ALL PRIVILEGES ON pankaj.* TO 'ssl_user'@'%' REQUIRE SSL WITH GRANT OPTION;
FLUSH PRIVILEGES;
No comments:
Post a Comment