====== MySQL mit SSL verschlüsselten Verbindungen ====== * [[https://dev.mysql.com/doc/refman/5.7/en/using-encrypted-connections.html]] * [[https://dev.mysql.com/doc/refman/5.7/en/replication-solutions-encrypted-connections.html]] > mysqlshow --ssl-mode=VERIFY_IDENTITY --ssl-key='/etc/ssl/private/MySQL/client-key.pem' --ssl-cert='/etc/ssl/private/MySQL/client-cert.pem' --ssl-ca='/etc/ssl/private/MySQL/ca.pem' > echo "SHOW STATUS WHERE variable_name IN ('Ssl_cipher','Ssl_cipher_list');" | mysql -t +-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+ | Ssl_cipher | ECDHE-RSA-AES256-GCM-SHA384 | | Ssl_cipher_list | ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:ECDHE-RSA-AES256-SHA:ECDHE-RSA-AES128-SHA | +-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+ === Ubuntu 16.04 === Welche CIPHER es in openssl gibt, kann man beispielsweise so ermitteln: > openssl ciphers -V TLSv1.2 0xC0,0x28 - ECDHE-RSA-AES256-SHA384 TLSv1.2 Kx=ECDH Au=RSA Enc=AES(256) Mac=SHA384 0x00,0x6B - DHE-RSA-AES256-SHA256 TLSv1.2 Kx=DH Au=RSA Enc=AES(256) Mac=SHA256 0x00,0x69 - DH-RSA-AES256-SHA256 TLSv1.2 Kx=DH/RSA Au=DH Enc=AES(256) Mac=SHA256 0x00,0x6D - ADH-AES256-SHA256 TLSv1.2 Kx=DH Au=None Enc=AES(256) Mac=SHA256 0xC0,0x2A - ECDH-RSA-AES256-SHA384 TLSv1.2 Kx=ECDH/RSA Au=ECDH Enc=AES(256) Mac=SHA384 0x00,0x3D - AES256-SHA256 TLSv1.2 Kx=RSA Au=RSA Enc=AES(256) Mac=SHA256 Leider können nicht immer alle openssl-CIPHER verwendet werden. Beispielsweise funktionieren nur diese 6 Cipher auf "''Ubuntu 16.04''" mit der Kombination "''OpenSSL 1.0.2g''", "''PHP 7.0''" und "''mysql-community-server 5.7.31''", obwohl openssl 97 Cipher unterstützt von denen 41 zum Standard TLS1.2 gehören: AES128-SHA SSLv3 AES256-SHA SSLv3 DHE-RSA-AES128-SHA SSLv3 DHE-RSA-AES256-SHA SSLv3 ECDHE-RSA-AES128-SHA SSLv3 ECDHE-RSA-AES256-SHA SSLv3 === Ubuntu 18.04 === # openssl version OpenSSL 1.1.0h 27 Mar 2018 (Library: OpenSSL 1.1.1g 21 Apr 2020) # openssl ciphers --help Usage: ciphers [options] Valid options are: -help Display this summary -v Verbose listing of the SSL/TLS ciphers -V Even more verbose -s Only supported ciphers -tls1 TLS1 mode -tls1_1 TLS1.1 mode -tls1_2 TLS1.2 mode -psk include ciphersuites requiring PSK -srp include ciphersuites requiring SRP # openssl ciphers -s TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256:TLS_AES_128_GCM_SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:DHE-RSA-AES256-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256:DHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA:ECDHE-RSA-AES256-SHA:DHE-RSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES128-SHA:DHE-RSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:AES256-SHA:AES128-SHA # openssl ciphers -s | tr -s ':' '\n' TLS_AES_256_GCM_SHA384 TLS_CHACHA20_POLY1305_SHA256 TLS_AES_128_GCM_SHA256 ECDHE-ECDSA-AES256-GCM-SHA384 ECDHE-RSA-AES256-GCM-SHA384 DHE-RSA-AES256-GCM-SHA384 ECDHE-ECDSA-CHACHA20-POLY1305 ECDHE-RSA-CHACHA20-POLY1305 DHE-RSA-CHACHA20-POLY1305 ECDHE-ECDSA-AES128-GCM-SHA256 ECDHE-RSA-AES128-GCM-SHA256 DHE-RSA-AES128-GCM-SHA256 ECDHE-ECDSA-AES256-SHA384 ECDHE-RSA-AES256-SHA384 DHE-RSA-AES256-SHA256 ECDHE-ECDSA-AES128-SHA256 ECDHE-RSA-AES128-SHA256 DHE-RSA-AES128-SHA256 ECDHE-ECDSA-AES256-SHA ECDHE-RSA-AES256-SHA DHE-RSA-AES256-SHA ECDHE-ECDSA-AES128-SHA ECDHE-RSA-AES128-SHA DHE-RSA-AES128-SHA AES256-GCM-SHA384 AES128-GCM-SHA256 AES256-SHA256 AES128-SHA256 AES256-SHA AES128-SHA ===== SSL-Verschlüsselung auf dem Server aktivieren ===== in einem separaten Terminal-Fenster den Status überwachen: # watch -n1 'echo "SHOW SLAVE STATUS \G;" | mysql -hlocalhost -P3306 | egrep "Master_Host:|_Running:|_Err"' die Benutzer auf verschlüsselte Verbindungen umstellen: # echo "SELECT host,user,authentication_string,password_expired,password_last_changed,ssl_type FROM mysql.user;" | mysql -t # echo "ALTER USER 'otto'@'%' REQUIRE X509;" | mysql -t # echo "ALTER USER 'fritz'@'%' REQUIRE X509;" | mysql -t # echo "SELECT host,user,authentication_string,password_expired,password_last_changed,ssl_type FROM mysql.user;" | mysql -t die SSL-Verschlüsselung der Replikationsverbindungen muss durch **''MASTER_SSL=1''** aktiviert werden # echo "STOP SLAVE;" | mysql ; echo "CHANGE MASTER TO MASTER_HOST='10.10.230.11', MASTER_PORT=3306, MASTER_USER='otto', MASTER_PASSWORD='replipasswort', MASTER_AUTO_POSITION=1, MASTER_SSL=1 FOR CHANNEL 'kanalfritz02'; CHANGE MASTER TO MASTER_HOST='10.10.230.12', MASTER_PORT=3306, MASTER_USER='otto', MASTER_PASSWORD='replipasswort', MASTER_AUTO_POSITION=1, MASTER_SSL=1 FOR CHANNEL 'kanalfritz03'; START SLAVE;" | mysql die Schlüssel und Zertifikate müssen an ihrem vorbestimmten Ort liegen: # ll /var/lib/mysql/data/*.pem -rw-r--r-- 1 mysql mysql 1119 Mär 6 12:02 /var/lib/mysql/data/ca.pem -rw------- 1 mysql mysql 1676 Jun 29 11:49 /var/lib/mysql/data/private_key.pem -rw-r--r-- 1 mysql mysql 452 Jun 29 11:49 /var/lib/mysql/data/public_key.pem -rw-r--r-- 1 mysql mysql 1119 Mär 6 11:58 /var/lib/mysql/data/server-cert.pem -rw------- 1 mysql mysql 1675 Mär 6 11:58 /var/lib/mysql/data/server-key.pem Es ist aber auch möglich, die MySQL-Client-Konfiguration so anzupassen, das man sich die CLI-Parameter sparen kann: # cat /etc/mysql/conf.d/mysql.cnf [mysqld] ssl = 1 #ssl_capath = /var/lib/mysql ssl_ca = ca.pem ssl_cert = server-cert.pem ssl_key = server-key.pem ssl-cipher = ECDHE-RSA-AES256-SHA:ECDHE-RSA-AES128-SHA:DHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA:AES256-SHA:AES128-SHA require_secure_transport = ON # service mysql restart jetzt kann jeder unterstützte Cipher verwendet werden: # echo "SHOW VARIABLES LIKE '%ssl%';" | mysql -t +---------------+-----------------+ | 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 | +---------------+-----------------+ jetzt können maximal nur die unten ausgewählten Cipher verwendet werden: # echo "SHOW VARIABLES LIKE '%ssl%';" | mysql -t +---------------+-------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------------------------------------------------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | ca.pem | | ssl_capath | | | ssl_cert | server-cert.pem | | ssl_cipher | ECDHE-RSA-AES256-SHA:ECDHE-RSA-AES128-SHA:DHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA:AES256-SHA:AES128-SHA | | ssl_crl | | | ssl_crlpath | | | ssl_key | server-key.pem | +---------------+-------------------------------------------------------------------------------------------------------+ jetzt sind noch unverschlüsselte Verbindungen möglich: # echo "SHOW VARIABLES LIKE '%secure%';" | mysql -t +--------------------------+-----------------------+ | Variable_name | Value | +--------------------------+-----------------------+ | require_secure_transport | OFF | | secure_auth | ON | | secure_file_priv | /var/lib/mysql-files/ | +--------------------------+-----------------------+ jetzt sind nur noch verschlüsselte Verbindungen möglich: # echo "SHOW VARIABLES LIKE '%secure%';" | mysql -t +--------------------------+-----------------------+ | Variable_name | Value | +--------------------------+-----------------------+ | require_secure_transport | ON | | secure_auth | ON | | secure_file_priv | /var/lib/mysql-files/ | +--------------------------+-----------------------+ # echo "SHOW STATUS LIKE 'Ssl_server_not_%';" | mysql -t +-----------------------+--------------------------+ | Variable_name | Value | +-----------------------+--------------------------+ | Ssl_server_not_after | Feb 28 11:24:36 2030 GMT | | Ssl_server_not_before | Mar 2 11:24:36 2020 GMT | +-----------------------+--------------------------+ ===== Mit dem MySQL-Client auf MySQL-Datenbanken über eine SSL/TLS verschlüsselte Verbindung zugreifen ===== siehe auch: __Mit PHP [[::PHP#MySQL-Datenbankzugriff über eine per SSL/TLS verschlüsselte Verbindung]]__ relevante PEM-Dateien auf dem Server-System, werden automatisch angelegt, wenn der MysqlD startet: /var/lib/mysql/ca-key.pem /var/lib/mysql/ca.pem /var/lib/mysql/client-cert.pem /var/lib/mysql/client-key.pem /var/lib/mysql/private_key.pem /var/lib/mysql/public_key.pem /var/lib/mysql/server-cert.pem /var/lib/mysql/server-key.pem benötigte PEM-Dateien auf dem Server-System in die MySQL-Konfiguration einbinden: /etc/ssl/private/MySQL/server-key.pem /etc/ssl/private/MySQL/server-cert.pem /etc/ssl/private/MySQL/ca.pem Der Aufruf mit Parametern auf der CLI: > mysqlshow -uroot -pgeheim -hdbserver01 -P3306 --ssl-mode=required --tls-version=TLSv1.2 > mysqlshow -uroot -pgeheim -hdbserver01 -P3306 --ssl-mode=required +------------------------------------+ | Databases | +------------------------------------+ | information_schema | | mysql | | performance_schema | | sys | +------------------------------------+ Verbindungstest: > mysqlshow -uroot -pgeheim -hdbserver01 -P3306 +------------------------------------+ | Databases | +------------------------------------+ | information_schema | | mysql | | performance_schema | | sys | +------------------------------------+ ===== MySQL sein SSL-Zertifikat prüfen ===== #!/bin/bash # diese Methode ist universell und funktioniert bei MySQL und bei MariaDB; # der Pfad zum Zertifikat wird aus der DB ausgelesen; # die Informationen werden dann direkt aus der PEM-Datei gelesen; MYSQL_LOGIN="mysql" #MYSQL_LOGIN="mysql --defaults-file=/root/.my.cnf" ( cd $(echo "SELECT @@datadir;" | ${MYSQL_LOGIN} -N) #SSL_ZERTIFIKAT="$(echo "SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Current_tls_cert';" | ${MYSQL_LOGIN} -N)" SSL_ZERTIFIKAT="$(echo "SELECT @@ssl_cert;" | ${MYSQL_LOGIN} -N)" BIS="$(date -d"$(openssl x509 -noout -enddate -in "${SSL_ZERTIFIKAT}" | awk -F'=' '{print $2}')" +'%F %T')" echo "${SSL_ZERTIFIKAT}"; echo "$(date -d "${BIS}" +%s) $(date +%s)" | awk '{printf "%u %s\n", ($1 - $2) / 86400,"Tage"}' echo "${BIS}"; openssl x509 -noout -subject -issuer -in "${SSL_ZERTIFIKAT}" | sed 's/^[^=]*=//' ) | tr -s '\n' '|' | sed 's/[|]/; /g' echo #!/usr/bin/env python3 import glob import subprocess import datetime import os WARN_DAYS = 30 CRIT_DAYS = 10 now = datetime.datetime.now() sockets = sorted(glob.glob("/var/run/mysqld/mysqld*.sock")) for sock in sockets: if not os.path.exists(sock): continue try: ssl_cert = subprocess.check_output( ["mysql", "-NS", f"{sock}"], input="SELECT @@ssl_cert;", text=True, stderr=subprocess.DEVNULL ).strip() ssl_ca = subprocess.check_output( ["mysql", "-NS", f"{sock}"], input="SELECT @@ssl_ca;", text=True, stderr=subprocess.DEVNULL ).strip() if not ssl_cert.startswith("/"): datadir = subprocess.check_output( ["mysql", "-NS", f"{sock}"], input="SELECT @@datadir;", text=True, stderr=subprocess.DEVNULL ).strip() cert_path = datadir + ssl_cert else: cert_path = ssl_cert except Exception: print(f"3 mysql_ssl_cert_{os.path.basename(sock)} UNKNOWN - mysql error") continue try: out = subprocess.check_output( ["openssl", "x509", "-in", cert_path, "-noout", "-enddate", "-subject", "-issuer"], text=True, stderr=subprocess.DEVNULL ) except Exception: print(f"3 mysql_ssl_cert_{os.path.basename(sock)} UNKNOWN - cannot read cert {cert_path}") continue expiry = None subject = "" issuer = "" for line in out.splitlines(): if line.startswith("notAfter="): raw = line.split("=", 1)[1].strip() expiry = datetime.datetime.strptime(raw, "%b %d %H:%M:%S %Y %Z") elif line.startswith("subject="): subject = line.replace("subject=", "").strip() elif line.startswith("issuer="): issuer = line.replace("issuer=", "").strip() if not expiry: print(f"3 mysql_ssl_cert_{os.path.basename(sock)} UNKNOWN - cannot parse expiry") continue # ----------- days_left -------------- days_left = (expiry - now).days if days_left < CRIT_DAYS: status = 2 elif days_left < WARN_DAYS: status = 1 else: status = 0 expiry_str = expiry.strftime("%Y-%m-%d %H:%M:%S") svc = f"mysql_ssl_cert_{os.path.basename(sock)}" summary = f"{cert_path}; {days_left} Tage; {expiry_str};" if subject: summary += f" {subject};" if issuer: summary += f" {issuer};" print(f"{status} {svc} days_left={days_left};{WARN_DAYS};{CRIT_DAYS};0; {summary}")