====== Datenbankverbindungen zu einer MySQL mit SSL-Verschlüsselung ====== siehe auch: * [[::OpenSSL]] * **[[https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/connection-options.html#encrypted-connection-options]]** > 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 ==== * [[https://dev.mysql.com/doc/refman/5.7/en/using-encrypted-connections.html]] > 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 [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!!! #!/usr/bin/php '/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() . "
"; die(); } // Verbindung nutzen foreach ($dbh->query($sql) as $row) { echo $row['Database'] . "\n"; } // und nach der Nutzung, die Verbindung beenden $dbh = null; ?> #!/usr/bin/php '/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() . "
"; die(); } // Verbindung nutzen foreach ($dbh->query($sql) as $row) { echo $row['Tables_in_' . $dbname]."\n"; } // und nach der Nutzung, die Verbindung beenden $dbh = null; ?> ---- ===== MySQL: Creating SSL Keys and Certificates Using OpenSSL ===== * [[https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-files-using-openssl.html]] * [[https://security.stackexchange.com/questions/74345/provide-subjectaltname-to-openssl-directly-on-the-command-line]] ==== per Skript ==== #!/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 < ${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 < ${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 > /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