Webseiten-Werkzeuge


datenbankverbindungen_zu_einer_mysql_mit_ssl-verschluesselung

Datenbankverbindungen zu einer MySQL mit SSL-Verschlüsselung

> 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 |
+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+

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

/root/bin/mysql_show_databases+ssl.php
#!/usr/bin/php
 
<?php
// VERSION="2020090100"
 
// Argumente
$dbuser = $argv['1'];
$dbpass = $argv['2'];
$dbhost = $argv['3'];
$dbport = $argv['4'];
 
// diese Zertifikate benutzen
$ssl_optionen=array(
        PDO::MYSQL_ATTR_SSL_CA                  => 'NULL',
        PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT  => 'false'
);
 
// Abfrage definieren
$sql = "SHOW DATABASES;";
 
// Verbindung zum DBMS aufbauen
try {
        $dbh = new pdo(
                '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;
?>
/root/bin/mysql_show_ssl_cipher.php
#!/usr/bin/php
 
<?php
// VERSION="2020090100"
 
// Argumente
$dbuser = $argv['1'];
$dbpass = $argv['2'];
$dbhost = $argv['3'];
$dbport = $argv['4'];
 
// diese Zertifikate benutzen
$ssl_optionen=array(
        PDO::MYSQL_ATTR_SSL_CA                  => 'NULL',
        PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT  => 'false'
);
 
// Abfrage definieren
//$sql = "SELECT * FROM user;";
$sql = "SHOW STATUS LIKE 'ssl_cipher%';";
 
// Verbindung zur Datenbank aufbauen
try {
        $dbh = new pdo(
                'mysql:host=' . $dbhost . ';port=' . $dbport . ';dbname=mysql', $dbuser , $dbpass, $ssl_optionen
        );
} catch (PDOException $e) {
        print "Error!: " . $e->getMessage() . "<br/>";
        die();
}
 
// Verbindung nutzen
foreach ($dbh->query($sql) as $row) {
        //echo $row['Host']."\t\t".$row['User']."\t\t".$row['authentication_string']."\n";
        echo $row['Variable_name']."\t\t".$row['Value']."\n";
}
 
// und nach der Nutzung, die Verbindung beenden
$dbh = null;
?>

datenbankverbindungen_zu_einer_mysql_mit_ssl-verschluesselung.txt · Zuletzt geändert: 2021/05/10 11:09 von manfred