Benutzer-Werkzeuge

Webseiten-Werkzeuge

A PCRE internal error occured. This might be caused by a faulty plugin

datenbankverbindungen_zu_einer_mysql_mit_ssl-verschluesselung

====== Datenbankverbindungen zu einer MySQL mit SSL-Verschlüsselung ====== ===== 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> ==== 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 ==== <file 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; ?> </file> <file php /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; ?> </file> ----

datenbankverbindungen_zu_einer_mysql_mit_ssl-verschluesselung.txt · Zuletzt geändert: 2020/09/21 18:01 von manfred