Wissen

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
====== 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