Wissen

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Sie befinden sich hier: Start » datenbankverbindungen_zu_einer_mysql_mit_ssl-verschluesselung
Zuletzt angesehen:
datenbankverbindungen_zu_einer_mysql_mit_ssl-verschluesselung

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung		Vorhergehende Überarbeitung
datenbankverbindungen_zu_einer_mysql_mit_ssl-verschluesselung [2023-04-20 13:04:28] – [Mit dem PDO-Client von PHP] manfreddatenbankverbindungen_zu_einer_mysql_mit_ssl-verschluesselung [2024-04-11 11:12:32] (aktuell) – [Mit dem PDO-Client von PHP] manfred
Zeile 1: Zeile 1:
 +====== 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 
 +
 +<file bash 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
 +</file>
 +
 +
 +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)
 +<file bash>
 +# 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
 +--------------
 +</file>
 +
 +Test mit PHP 7.0 (Ubuntu 16.04)
 +<file bash>
 +# echo "SHOW STATUS LIKE 'Ssl_cipher';" | mysql --ssl-mode=required -hmysqlserver01 -P3306 -t
 ++---------------+-----------------------------+
 +| Variable_name | Value                       |
 ++---------------+-----------------------------+
 +| Ssl_cipher    | ECDHE-RSA-AES128-GCM-SHA256 |
 ++---------------+-----------------------------+
 +</file>
 +
 +Test mit PHP 7.0 (Ubuntu 16.04)
 +<file bash>
 +# 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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
 ++-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 +</file>
 +
 +
 +==== 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>
 +<file 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;
 +?>
 +</file>
 +</hidden>
 +
 +<hidden /root/bin/mysql_show_ssl_cipher.php>
 +<file 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;
 +?>
 +</file>
 +</hidden>
 +----
 +
 +
 +===== 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 ====
 +
 +<hidden /root/bin/Creating_MySQL-SSL-Files.sh>
 +<file bash /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
 +</file>
 +</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
 +