datenbankverbindungen_zu_einer_mysql_mit_ssl-verschluesselung
Inhaltsverzeichnis
Datenbankverbindungen zu einer MySQL mit SSL-Verschlüsselung
siehe auch:
> 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 |
+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
SSL-Verschlüsselung auf dem MySQL-Server einrichten
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 "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.20.11', MASTER_PORT=3306, MASTER_USER='otto', MASTER_PASSWORD='Rk9TcDg1YkxPcHpICg==', MASTER_AUTO_POSITION=1, MASTER_SSL=1 FOR CHANNEL 'kanalsabio02'; CHANGE MASTER TO MASTER_HOST='10.10.20.12', MASTER_PORT=3306, MASTER_USER='otto', MASTER_PASSWORD='Rk9TcDg1YkxPcHpICg==', MASTER_AUTO_POSITION=1, MASTER_SSL=1 FOR CHANNEL 'kanalsabio03'; 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 # vi /var/lib/mysql/cnf/conf.d/cluster57.cnf [mysqld] ssl = 1 ssl_capath = /var/lib/mysql/data 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
# echo "SHOW VARIABLES LIKE '%ssl%';" | mysql -hlocalhost -P3306 -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 | +---------------+-------------------------------------------------------------------------------------------------------+
# echo "SHOW STATUS LIKE 'Ssl_server_not_%';" | mysql -hlocalhost -P3306 -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 | +-----------------------+--------------------------+
SSL-Verschlüsselung auf dem MySQL-Client einrichten
# ll /etc/ssl/ca.pem /etc/ssl/server-cert.pem /etc/ssl/server-key.pem -rw------- 1 root root 1120 Jun 29 12:28 /etc/ssl/ca.pem -rw------- 1 root root 1120 Jun 29 12:28 /etc/ssl/server-cert.pem -rw------- 1 root root 1676 Jun 29 12:28 /etc/ssl/server-key.pem # cat /etc/mysql/conf.d/mysql.cnf [mysql] ssl_ca = /etc/ssl/ca.pem ssl_cert = /etc/ssl/server-cert.pem ssl_key = /etc/ssl/server-key.pem
Verbindungstest:
# mysql -h10.11.12.13 -udbuser -pgeheim dbname
Mit dem MySQL-Client auf MySQL-Datenbanken über eine SSL/TLS verschlüsselte Verbindung zugreifen
> mysqlshow -hdatenbank.domain.de -P3306 --ssl-mode=VERIFY_IDENTITY --ssl-key=/etc/ssl/server-key.pem --ssl-cert=/etc/ssl/server-key.pem --ssl-ca=/etc/ssl/ca.pem --tls-version=TLSv1.2 +------------------------------------+ | Databases | +------------------------------------+ | information_schema | | mysql | | performance_schema | | sys | +------------------------------------+
### MySQL 5.7 # ohne Zertifikatsprüfung > echo "STATUS;" | mysql --tls-version=TLSv1.2 --ssl-mode=REQUIRED -S /var/run/mysqld/mysqld.sock > echo "STATUS;" | mysql --tls-version=TLSv1.2 --ssl-mode=REQUIRED -hlocalhost -P3306 > echo "STATUS;" | mysql --tls-version=TLSv1.2 --ssl-mode=REQUIRED -h127.0.0.1 -P3306 ... SSL: Cipher in use is ECDHE-RSA-AES256-GCM-SHA384 ... # Einweg-Zertifikatsprüfung (One-Way TLS) > echo "STATUS;" | mysql -h127.0.0.1 -P3306 --tls-version=TLSv1.2 --ssl-mode=VERIFY_IDENTITY --ssl-ca=/var/lib/mysql/ca.pem # Zweiwege-Zertifikatsprüfung (Two-Way TLS) > echo "STATUS;" | mysql -h127.0.0.1 -P3306 --tls-version=TLSv1.2 --ssl-mode=VERIFY_IDENTITY --ssl-ca=/var/lib/mysql/ca.pem --ssl-cert=/var/lib/mysql/client-cert.pem --ssl-key=/var/lib/mysql/client-key.pem ### MariaDB 10.10 # ohne Zertifikatsprüfung > echo "STATUS;" | mysql --tls-version=TLSv1.2 --ssl-verify-server-cert -h127.0.0.1 -P3306
verschlüsselt und unverschlüsselte Verbindungen möglich
Mit dieser Konfiguration kann man unverschlüsselt und verschlüsselt auf die DB zugreifen.
Das gilt auch für die Replikationsverbindungen.
Sollte die Verschlüsselung (mit der Option "require_secure_transport=ON") erzwungen werden, dann sind auch die Replikationsverbindungen verschlüsselt und müssen entsprechend umkonfiguriert werden.
Das MySQL-DBMS konfigurieren
ein MySQL-DBMS erzeugt beim Start diese Schlüssel (wenn sie nicht bereits dort liegen):
/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
Zertifikat kontrollieren:
> openssl x509 -noout -text -in /var/lib/mysql/ca.pem > openssl x509 -noout -text -in /var/lib/mysql/client-cert.pem
- my.cnf
[mysqld] # # * SSL # ssl = 1 ssl-ca = ca.pem ssl-cert = server-cert.pem ssl-key = server-key.pem # ### PHP 7.2 (Ubuntu 18.04) tls_version = TLSv1.2 ssl-cipher = ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384 # ### nur noch verschlüsselte Verbindungen möglich #require_secure_transport = ON
SSL-Test auf eine MySQL-DB:
# openssl s_client -connect 127.0.0.1:3306 -tls1_2 # echo "STATUS;" | mysql --ssl-mode=required -h127.0.0.1 -P3306
Mit dem MySQL-Client auf der CLI
Test mit PHP 7.0 (Ubuntu 16.04)
# echo "STATUS;" | mysql --ssl-mode=required -hmysqlserver01 -P3306 -------------- mysql Ver 14.14 Distrib 5.7.31, for Linux (x86_64) using EditLine wrapper Connection id: 2890 Current database: Current user: root@10.12.20.18 SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256 Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.31-log MySQL Community Server (GPL) Protocol version: 10 Connection: mysqlserver01 via TCP/IP Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 17 min 46 sec Threads: 5 Questions: 1847 Slow queries: 0 Opens: 4495 Flush tables: 1 Open tables: 1632 Queries per second avg: 1.732 --------------
Test mit PHP 7.0 (Ubuntu 16.04)
# echo "SHOW STATUS LIKE 'Ssl_cipher';" | mysql --ssl-mode=required -hmysqlserver01 -P3306 -t +---------------+-----------------------------+ | Variable_name | Value | +---------------+-----------------------------+ | Ssl_cipher | ECDHE-RSA-AES128-GCM-SHA256 | +---------------+-----------------------------+
Test mit PHP 7.0 (Ubuntu 16.04)
# echo "SHOW STATUS WHERE Variable_name IN ('Ssl_cipher','Ssl_cipher_list','Ssl_server_not_after','Ssl_server_not_before','Ssl_version');" | mysql --ssl-mode=required -hmysqlserver01 -P3306 -t mysql: [Warning] Using a password on the command line interface can be insecure. +-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Ssl_cipher | ECDHE-RSA-AES128-GCM-SHA256 | | Ssl_cipher_list | ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:DHE-RSA-AES128-SHA256:DHE-DSS-AES128-SHA256:DHE-DSS-AES256-GCM-SHA384:DHE-RSA-AES256-SHA256:DHE-DSS-AES256-SHA256:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA:ECDHE-ECDSA-AES256-SHA:DHE-DSS-AES128-SHA:DHE-RSA-AES128-SHA:DHE-RSA-AES256-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:AES128-SHA:AES256-SHA:DHE-RSA-AES256-GCM-SHA384 | | Ssl_server_not_after | Aug 15 16:27:13 2030 GMT | | Ssl_server_not_before | Aug 17 16:27:13 2020 GMT | | Ssl_version | TLSv1.2 | +-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Mit dem PDO-Client von PHP
Für "Einweg-Zertifikatsprüfung (One-Way TLS)" wird PDO::MYSQL_ATTR_SSL_KEY und PDO::MYSQL_ATTR_SSL_CERT nicht benötigt und kann aus den Skripten unten, entfernt werden.
Für "Zweiwege-Zertifikatsprüfung (Two-Way TLS)" ist PDO::MYSQL_ATTR_SSL_KEY und PDO::MYSQL_ATTR_SSL_CERT zusätzlich erforderlich (steht bereits drin).
Die Option PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT darf nicht verwendet werden!
Ihr ist es scheißegal ob sie auf "true" oder "false" gesetzt wird, sie schaltet die Verifikation IMMER ab!!!
<hidden /root/bin/mysql_show_databases+ssl.php>
- /root/bin/mysql_show_databases+ssl.php
#!/usr/bin/php <?php // VERSION="2020082100" // Argumente $dbuser = $argv['1']; $dbpass = $argv['2']; $dbhost = $argv['3']; $dbport = $argv['4']; // diese Zertifikate benutzen $ssl_optionen=array( PDO::MYSQL_ATTR_SSL_KEY => '/var/lib/mysql/client-key.pem', PDO::MYSQL_ATTR_SSL_CERT => '/var/lib/mysql/client-cert.pem', PDO::MYSQL_ATTR_SSL_CA => '/var/lib/mysql/ca.pem' ); // Abfrage definieren $sql = "SHOW DATABASES;"; // Verbindung zum DBMS aufbauen try { $dbh = new pdo( // 'mysql:host=' . $dbhost . ';port=' . $dbport , $dbuser , $dbpass 'mysql:host=' . $dbhost . ';port=' . $dbport , $dbuser , $dbpass, $ssl_optionen ); } catch (PDOException $e) { print "Error!: " . $e->getMessage() . "<br/>"; die(); } // Verbindung nutzen foreach ($dbh->query($sql) as $row) { echo $row['Database'] . "\n"; } // und nach der Nutzung, die Verbindung beenden $dbh = null; ?>
</hidden>
<hidden /root/bin/mysql_show_ssl_cipher.php>
- /root/bin/mysql_show_ssl_cipher.php
#!/usr/bin/php <?php // VERSION="2020082100" // Argumente $dbuser = $argv['1']; $dbpass = $argv['2']; $dbhost = $argv['3']; $dbport = $argv['4']; $dbname = $argv['5']; // diese Zertifikate benutzen $ssl_optionen=array( PDO::MYSQL_ATTR_SSL_KEY => '/var/lib/mysql/client-key.pem', PDO::MYSQL_ATTR_SSL_CERT => '/var/lib/mysql/client-cert.pem', PDO::MYSQL_ATTR_SSL_CA => '/var/lib/mysql/ca.pem' ); // Abfrage definieren $sql = "SHOW TABLES"; // Verbindung zur Datenbank aufbauen try { $dbh = new pdo( //'mysql:host=' . $dbhost . ';port=' . $dbport . ';dbname=mysql', $dbuser , $dbpass //'mysql:host=' . $dbhost . ';port=' . $dbport . ';dbname=mysql', $dbuser , $dbpass, $ssl_optionen 'mysql:host=' . $dbhost . ';port=' . $dbport . ';dbname=' . $dbname , $dbuser , $dbpass, $ssl_optionen ); } catch (PDOException $e) { print "Error!: " . $e->getMessage() . "<br/>"; die(); } // Verbindung nutzen foreach ($dbh->query($sql) as $row) { echo $row['Tables_in_' . $dbname]."\n"; } // und nach der Nutzung, die Verbindung beenden $dbh = null; ?>
</hidden>
MySQL: Creating SSL Keys and Certificates Using OpenSSL
per Skript
<hidden /root/bin/Creating_MySQL-SSL-Files.sh>
- /root/bin/Creating_MySQL-SSL-Files.sh
#!/bin/bash #------------------------------------------------------------------------------# # Create necessary files: $database, $serial and $new_certs_dir # directory (optional) #------------------------------------------------------------------------------# #VERSION="v2023052400" # erstellt VERSION="v2023052500" # jetzt funktioniert es if [ x == "x${1}" ] ; then DER_NAME="mysqldb" else DER_NAME="${1}" fi ### FQDNs von weiteren Netzwerken, in denen dieser Host NICs hat ### bzw. alle FQDNs von allen Cluster-Knoten, die zu diesem DBMS-Cluster gehören ALT_DNS_NAME="DNS:mysqldb.lan.datenbank, DNS:mysqldb.domain.net" DAYS="3650" BITS="4096" rm -fr ${DER_NAME} mkdir -p ${DER_NAME} touch ${DER_NAME}/index.txt echo "01" > ${DER_NAME}/serial # # Konfigurationsdatei erzeugen # #cp /etc/ssl/openssl.cnf ${DER_NAME}/ #replace ./demoCA ${DER_NAME} -- ${DER_NAME}/openssl.cnf # cat <<EOF > ${DER_NAME}/openssl.cnf HOME = . oid_section = new_oids [ new_oids ] tsa_policy1 = 1.2.3.4.1 tsa_policy2 = 1.2.3.4.5.6 tsa_policy3 = 1.2.3.4.5.7 [ ca ] default_ca = CA_default [ CA_default ] dir = /root/Creating_MySQL-SSL-Files/${DER_NAME} certs = \$dir/certs crl_dir = \$dir/crl database = \$dir/index.txt new_certs_dir = \$dir/ certificate = \$dir/ca.pem serial = \$dir/serial crlnumber = \$dir/crlnumber crl = \$dir/crl.pem private_key = \$dir/ca-key.pem x509_extensions = usr_cert name_opt = ca_default cert_opt = ca_default default_days = ${DAYS} default_crl_days = 30 default_md = default preserve = no policy = policy_match [ policy_match ] countryName = match stateOrProvinceName = match organizationName = match organizationalUnitName = optional commonName = supplied emailAddress = optional [ policy_anything ] countryName = optional stateOrProvinceName = optional localityName = optional organizationName = optional organizationalUnitName = optional commonName = supplied emailAddress = optional [ req ] default_bits = ${BITS} default_keyfile = privkey.pem distinguished_name = req_distinguished_name attributes = req_attributes x509_extensions = v3_ca string_mask = utf8only [ req_distinguished_name ] countryName = Country Name (2 letter code) countryName_default = DE countryName_min = 2 countryName_max = 2 stateOrProvinceName = State or Province Name (full name) stateOrProvinceName_default = Hessen localityName = Locality Name (eg, city) localityName_default = Frankfurt 0.organizationName = Organization Name (eg, company) 0.organizationName_default = Datenbank AG organizationalUnitName = Organizational Unit Name (eg, section) organizationalUnitName_default = Die UNIX-Administratoren commonName = Common Name (e.g. server FQDN or YOUR name) commonName_max = 64 commonName_default = ${DER_NAME} emailAddress = Email Address emailAddress_max = 64 emailAddress_default = ssladmin@domain.de subjectAltName = DNS-Namen subjectAltName_default = ${ALT_DNS_NAME} [ req_attributes ] challengePassword = A challenge password challengePassword_min = 4 challengePassword_max = 20 challengePassword_default = asdf unstructuredName = An optional company name unstructuredName_default = IQ [ usr_cert ] basicConstraints = CA:FALSE nsComment = "OpenSSL Generated Certificate" subjectKeyIdentifier = hash authorityKeyIdentifier = keyid,issuer [ v3_req ] basicConstraints = CA:FALSE keyUsage = nonRepudiation, digitalSignature, keyEncipherment [ v3_ca ] subjectKeyIdentifier = hash authorityKeyIdentifier = keyid:always,issuer basicConstraints = critical,CA:true [ crl_ext ] authorityKeyIdentifier = keyid:always [ proxy_cert_ext ] basicConstraints = CA:FALSE nsComment = "OpenSSL Generated Certificate" subjectKeyIdentifier = hash authorityKeyIdentifier = keyid,issuer proxyCertInfo = critical,language:id-ppl-anyLanguage,pathlen:3,policy:foo [ tsa ] default_tsa = tsa_config1 [ tsa_config1 ] dir = /root/Creating_MySQL-SSL-Files/${DER_NAME} serial = \$dir/tsaserial crypto_device = builtin signer_cert = \$dir/tsacert.pem certs = \$dir/cacert.pem signer_key = \$dir/private/tsakey.pem signer_digest = sha256 default_policy = tsa_policy1 other_policies = tsa_policy2, tsa_policy3 digests = sha256, sha384, sha512 accuracy = secs:1, millisecs:500, microsecs:100 clock_precision_digits = 0 ordering = yes tsa_name = yes ess_cert_id_chain = no ess_cert_id_alg = sha512 EOF #------------------------------------------------------------------------------# echo " # # 1. Generation of Certificate Authority(CA) # => Common Name (eg, YOUR name) []:MySQL admin" echo "openssl req -nodes -new -x509 -keyout ${DER_NAME}/ca-key.pem -out ${DER_NAME}/ca.pem -config ${DER_NAME}/openssl.cnf" openssl req -nodes -new -x509 -keyout ${DER_NAME}/ca-key.pem -out ${DER_NAME}/ca.pem -config ${DER_NAME}/openssl.cnf if [ ! -r ${DER_NAME}/ca.pem ] ; then echo "Das erstellen von ${DER_NAME}/ca.pem ist fehlgeschlagen." echo "ABBRUCH" exit 1 fi echo " #------------------------------------------------------------------------------# # 2. Create server key and server request # => Common Name (eg, YOUR name) []:MySQL server" echo "openssl req -rand /dev/urandom -nodes -new -keyout ${DER_NAME}/server-key.pem -out ${DER_NAME}/server-req.pem -config ${DER_NAME}/openssl.cnf" openssl req -rand /dev/urandom -nodes -new -keyout ${DER_NAME}/server-key.pem -out ${DER_NAME}/server-req.pem -config ${DER_NAME}/openssl.cnf if [ ! -r ${DER_NAME}/server-req.pem ] ; then echo "Das erstellen von ${DER_NAME}/server-req.pem ist fehlgeschlagen." echo "ABBRUCH" exit 1 fi echo " # # 3. Sign server cert #" echo "openssl ca -cert ${DER_NAME}/ca.pem -policy policy_anything -out ${DER_NAME}/server-cert.pem -config ${DER_NAME}/openssl.cnf -infiles ${DER_NAME}/server-req.pem" openssl ca -cert ${DER_NAME}/ca.pem -policy policy_anything -out ${DER_NAME}/server-cert.pem -config ${DER_NAME}/openssl.cnf -infiles ${DER_NAME}/server-req.pem if [ ! -r ${DER_NAME}/server-cert.pem ] ; then echo "Das erstellen von ${DER_NAME}/server-cert.pem ist fehlgeschlagen." echo "ABBRUCH" exit 1 fi cat ${DER_NAME}/index.txt cat ${DER_NAME}/serial rm ${DER_NAME}/index.txt touch ${DER_NAME}/index.txt echo "01" > ${DER_NAME}/serial echo " #------------------------------------------------------------------------------# # 4. Create client request and client key # => Common Name (eg, YOUR name) []:MySQL user" echo "openssl req -rand /dev/urandom -nodes -new -keyout ${DER_NAME}/client-key.pem -keyform PEM -out ${DER_NAME}/client-req.pem -outform PEM -config ${DER_NAME}/openssl.cnf" openssl req -rand /dev/urandom -nodes -new -keyout ${DER_NAME}/client-key.pem -keyform PEM -out ${DER_NAME}/client-req.pem -outform PEM -config ${DER_NAME}/openssl.cnf if [ ! -r ${DER_NAME}/client-req.pem ] ; then echo "Das erstellen von ${DER_NAME}/client-req.pem ist fehlgeschlagen." echo "ABBRUCH" exit 1 fi echo " # # 5. Sign client cert #" echo "openssl ca -cert ${DER_NAME}/ca.pem -policy policy_anything -out ${DER_NAME}/client-cert.pem -config ${DER_NAME}/openssl.cnf -infiles ${DER_NAME}/client-req.pem" openssl ca -cert ${DER_NAME}/ca.pem -policy policy_anything -out ${DER_NAME}/client-cert.pem -config ${DER_NAME}/openssl.cnf -infiles ${DER_NAME}/client-req.pem if [ ! -r ${DER_NAME}/client-cert.pem ] ; then echo "Das erstellen von ${DER_NAME}/client-cert.pem ist fehlgeschlagen." echo "ABBRUCH" exit 1 fi #------------------------------------------------------------------------------# echo openssl rsa -noout -modulus -in ${DER_NAME}/ca-key.pem | openssl md5 | awk -v z=${DER_NAME}/ca-key.pem '{print $NF,z}' openssl x509 -noout -modulus -in ${DER_NAME}/ca.pem | openssl md5 | awk -v z=${DER_NAME}/ca.pem '{print $NF,z}' echo openssl rsa -noout -modulus -in ${DER_NAME}/server-key.pem | openssl md5 | awk -v z=${DER_NAME}/server-key.pem '{print $NF,z}' openssl x509 -noout -modulus -in ${DER_NAME}/server-cert.pem | openssl md5 | awk -v z=${DER_NAME}/server-cert.pem '{print $NF,z}' openssl req -noout -modulus -in ${DER_NAME}/server-req.pem | openssl md5 | awk -v z=${DER_NAME}/server-req.pem '{print $NF,z}' openssl req -text -verify -in ${DER_NAME}/server-req.pem >/dev/null echo openssl rsa -noout -modulus -in ${DER_NAME}/client-key.pem | openssl md5 | awk -v z=${DER_NAME}/client-key.pem '{print $NF,z}' openssl x509 -noout -modulus -in ${DER_NAME}/client-cert.pem | openssl md5 | awk -v z=${DER_NAME}/client-cert.pem '{print $NF,z}' openssl req -noout -modulus -in ${DER_NAME}/client-req.pem | openssl md5 | awk -v z=${DER_NAME}/client-req.pem '{print $NF,z}' openssl req -text -verify -in ${DER_NAME}/client-req.pem >/dev/null echo #------------------------------------------------------------------------------# echo " # # 8. Create a my.cnf file that you can use to test the certificates #" cat <<EOF > ${DER_NAME}/my.cnf [client] ssl-ca=${DER_NAME}/ca.pem ssl-cert=${DER_NAME}/client-cert.pem ssl-key=${DER_NAME}/client-key.pem [mysqld] ssl_ca=${DER_NAME}/ca.pem ssl_cert=${DER_NAME}/server-cert.pem ssl_key=${DER_NAME}/server-key.pem EOF (cd ${DER_NAME} && tar -czf ../${DER_NAME}.tgz ca-key.pem ca.pem client-key.pem client-cert.pem server-key.pem server-cert.pem) chmod 0600 ${DER_NAME}.tgz ls -lha ${DER_NAME}.tgz
</hidden>
> /root/bin/Creating_MySQL-SSL-Files.sh mysqldb ... # # 4. Sign server cert # Signature ok subject=C = DE, ST = Hessen, L = Maintal, O = IQ-Optimize AG, OU = Operations, CN = mysqldb, emailAddress = ssladmin@domain.de, subjectAltName = "DNS:mysqldb.lan.datenbank, DNS:mysqldb.domain.net" Getting CA Private Key ... # # 7. Sign client cert # Signature ok subject=C = DE, ST = Hessen, L = Maintal, O = IQ-Optimize AG, OU = Operations, CN = mysqldb, emailAddress = ssladmin@domain.de, subjectAltName = "DNS:mysqldb.lan.datenbank, DNS:mysqldb.domain.net" Getting CA Private Key ... -rw------- 1 root root 11K Mai 24 15:26 mysqldb.tgz > find * -type f | grep -Fi .pem mysqldb/ca-key.pem mysqldb/ca.pem mysqldb/client-key.pem mysqldb/client-req.pem mysqldb/client-cert.pem mysqldb/server-key.pem mysqldb/server-req.pem mysqldb/server-cert.pem > tar tzf mysqldb.tgz ca-key.pem ca.pem client-key.pem client-cert.pem server-key.pem server-cert.pem > tar xzf mysqldb.tgz -C /var/lib/mysql/ > chown mysql:mysql /var/lib/mysql/*.pem
verschlüsselte Verbindung mit einem MySQL-DBMS
> tar xvzf mysqldb.tgz -C /var/lib/mysql/ > chown mysql:mysql /var/lib/mysql/*.pem
ohne Zertifikatsprüfung:
> mysql --tls-version=TLSv1.2 --ssl-mode=REQUIRED
Einweg-Zertifikatsprüfung (One-Way TLS):
> mysql --tls-version=TLSv1.2 --ssl-mode=REQUIRED --ssl-mode=VERIFY_IDENTITY --ssl-ca=/var/lib/mysql/ca.pem
Zweiwege-Zertifikatsprüfung (Two-Way TLS):
> mysql --tls-version=TLSv1.2 --ssl-mode=REQUIRED --ssl-mode=VERIFY_IDENTITY --ssl-ca=/var/lib/mysql/ca.pem --ssl-cert=/var/lib/mysql/client-cert.pem --ssl-key=/var/lib/mysql/client-key.pem
/home/http/wiki/data/pages/datenbankverbindungen_zu_einer_mysql_mit_ssl-verschluesselung.txt · Zuletzt geändert: von manfred