Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- tachtler:mariadb_centos_7 [2018/04/27 05:45] – [SSL: Server-Zertifikat erstellen] klaus
+++ tachtler:mariadb_centos_7 [2018/04/27 06:38] (aktuell) – [SSL: Client-Zertifikat erstellen] klaus
@@ Zeile 1033: / Zeile 1033: @@
 Die Konfiguration beinhaltet
   - Erstellen einer **eignen CA** - **''MariaDB CA''**
-  - Erstellen eines **''self-signed''**-Zertifikats für den Server - **''MariaDB Server''**
+  - Erstellen eines **''self-signed''**-Zertifikats für den Server - **''db.idmz.tachtler.net''**
   - Erstellen eines **''self-signed''**-Zertifikats für den Client - **''MariaDB Client''**
@@ Zeile 1197: / Zeile 1197: @@
 Organization Name (eg, company) [Default Company Ltd]:Klaus Tachtler
 Organizational Unit Name (eg, section) []:.
-Common Name (eg, your name or your server's hostname) []:MariaDB Server
+Common Name (eg, your name or your server's hostname) []:db.idmz.tachtler.net
 Email Address []:hostmaster@tachtler.net
@@ Zeile 1268: / Zeile 1268: @@
 CA/emailAddress=hostmaster@tachtler.net
         Validity
-            Not Before: Apr 27 03:43:09 2018 GMT
+            Not Before: Apr 27 04:26:53 2018 GMT
-            Not After : Apr 23 03:43:09 2028 GMT
+            Not After : Apr 23 04:26:53 2028 GMT
+        Subject: C=DE, ST=Bayern (Bavaria), L=Muenchen (Munich), O=Klaus Tachtler,
+CN=db.idmz.tachtler.net/emailAddress=hostmaster@tachtler.net
+        Subject Public Key Info:
+            Public Key Algorithm: rsaEncryption
+                Public-Key: (2048 bit)
+                Modulus:
+:c5:81:5c:95:da:2b:19:33:cc:61:ba:b3:ea:bb:
+:a2:40:69:bf:7f:5a:71:1f:af:a0:94:bb:17:11:
+:b6:85:3b:06:a6:61:b1:88:50:a9:c0:a9:37:0c:
+                    c9:30:5e:87:26:0e:32:35:02:14:b5:93:02:fb:5c:
+:57:f1:e0:d0:a7:5c:79:f0:74:39:3f:6e:5b:a7:
+:bc:18:46:55:9f:78:43:4f:f2:69:17:b1:cb:da:
+d:6a:f5:90:03:1b:b2:f1:8c:19:0a:bc:47:d0:da:
+:30:0f:63:6f:45:0b:30:8a:34:7f:70:50:90:9a:
+                    f6:93:82:e4:ec:a5:74:4b:a9:80:30:d4:4a:11:41:
+:64:76:03:cf:be:a4:cc:46:da:e0:95:20:8f:6c:
+:5c:95:ab:21:e6:3d:39:69:df:69:36:a6:d8:73:
+c:d3:41:9e:51:72:0e:bf:c2:e7:7b:4b:da:e6:f9:
+f:66:8e:ba:be:a4:59:a7:dc:13:d4:00:41:83:e4:
+                    d2:3f:c8:73:e7:00:ee:63:fd:f5:e8:79:41:0e:45:
+:d5:50:c7:14:0f:2a:1a:97:eb:e1:a0:18:d6:40:
+c:07:0b:05:a9:72:c8:cd:a5:96:f1:c6:e1:39:04:
+                    ed:63:06:dc:8d:cd:30:90:e5:b4:2c:bd:de:b4:a6:
+:2a
+                Exponent: 65537 (0x10001)
+    Signature Algorithm: sha256WithRSAEncryption
+:c3:cb:a8:4e:4d:fe:82:78:18:72:6f:6d:76:7a:6b:64:25:
+         b6:f3:08:51:bd:19:f4:75:9e:bd:cc:05:50:d8:6b:2b:93:b5:
+:02:44:ff:ee:4a:b8:a9:db:57:9e:66:9c:25:28:a1:a5:48:
+e:4f:83:d4:1d:cd:c5:7e:4e:70:10:3a:9f:dd:dc:2d:82:c9:
+         e5:55:84:27:50:51:93:07:06:5e:1f:ae:b2:2c:59:23:ff:4f:
+:c5:3f:65:07:fc:42:cd:5c:47:2f:2f:54:ca:8a:70:34:9e:
+d:1d:bc:ec:35:9d:d0:7d:64:a5:65:16:9d:f2:77:e5:ee:a3:
+         f1:94:ec:f4:89:fe:a7:95:c6:27:38:6d:ef:44:90:90:03:70:
+         aa:cf:1d:57:0e:f1:2b:e5:09:ed:7d:c9:9e:c6:84:9a:77:5c:
+d:9f:4a:d4:e5:43:00:a8:04:51:00:26:4a:23:3b:09:ed:b1:
+a:1f:e8:62:09:5a:83:f6:43:4a:c9:53:4b:06:96:36:55:d1:
+c:f7:1e:59:1a:c2:cb:82:6f:3e:c7:cb:53:9a:62:04:0d:38:
+         f7:98:95:0c:5e:9f:e3:69:39:a3:c7:e5:b1:e6:b6:5e:52:87:
+:05:a2:ba:b0:12:c9:01:9b:0c:38:97:2c:0d:55:3e:06:ef:
+:ed:8a:35
+</code>
+==== SSL: Client-Zertifikat erstellen ====
+Nachfolgende Befehle erstellen ein **''MariaDB Client''**-Zertifikate aus der **eigenen CA**.
+:!: **HINWEIS** - **Dies kann in Client wie z.B.**
+  * **[[tachtler:phpmyadmin_centos_6|phpMyAdmin CentOS 6]]**
+  * **[[tachtler:phpmyadmin_centos_7#konfigurationsbeispiel|phpMyAdmin CentOS 7 - Konfigurationsbeispiel]]**
+**eingebunden werden**.
+**__1. Schritt__**: Zuerst müssen jeweils ein
+  * ''/etc/pki/mariadb/private/mariadb-client-key.pem'' - **Zertifikats-Schlüssel (Certificate-Key)**
+  * ''/etc/pki/mariadb/certs/mariadb-client-csr.pem'' - **Zertifikats-Antrag (Certificate-Request/CSR)**
+mit nachfolgendem Befehl erstellt werden:
+<code>
+# openssl req -newkey rsa:2048 -days 3649 -nodes -keyout /etc/pki/mariadb/private/mariadb-client-key.pem -out /etc/pki/mariadb/certs/mariadb-client-csr.pem
+Generating a 2048 bit RSA private key
+.............................................................................................................
+...........................................+++
+......+++
+writing new private key to '/etc/pki/mariadb/private/mariadb-client-key.pem'
+-----
+You are about to be asked to enter information that will be incorporated
+into your certificate request.
+What you are about to enter is what is called a Distinguished Name or a DN.
+There are quite a few fields but you can leave some blank
+For some fields there will be a default value,
+If you enter '.', the field will be left blank.
+-----
+Country Name (2 letter code) [XX]:DE
+State or Province Name (full name) []:Bayern (Bavaria)
+Locality Name (eg, city) [Default City]:Muenchen (Munich)
+Organization Name (eg, company) [Default Company Ltd]:Klaus Tachtler
+Organizational Unit Name (eg, section) []:.
+Common Name (eg, your name or your server's hostname) []:MariaDB Client
+Email Address []:hostmaster@tachtler.net
+Please enter the following 'extra' attributes
+to be sent with your certificate request
+A challenge password []:
+An optional company name []:.
+</code>
+Mit nachfolgendem Befehl kann nun überprüft werden, ob der **MariaDB Client Schlüssel** und der **MariaDB Client Zertifikatsantrag** erstellt wurden:
+<code>
+# ls -la /etc/pki/mariadb/*
+/etc/pki/mariadb/certs:
+total 16
+drwxr-xr-x 2 root root  118 Apr 27 05:48 .
+drwxr-xr-x 4 root root   32 Apr 27 04:44 ..
+-rw-r--r-- 1 root root 1460 Apr 27 04:54 mariadb-ca-crt.pem
+-rw-r--r-- 1 root root 1078 Apr 27 05:48 mariadb-client-csr.pem
+-rw-r--r-- 1 root root 1338 Apr 27 05:43 mariadb-server-crt.pem
+-rw-r--r-- 1 root root 1078 Apr 27 05:42 mariadb-server-csr.pem
+/etc/pki/mariadb/private:
+total 12
+drwxr-xr-x 2 root root   89 Apr 27 05:48 .
+drwxr-xr-x 4 root root   32 Apr 27 04:44 ..
+-rw-r--r-- 1 root root 1675 Apr 27 04:52 mariadb-ca-key.pem
+-rw-r--r-- 1 root root 1704 Apr 27 05:48 mariadb-client-key.pem
+-rw-r--r-- 1 root root 1679 Apr 27 05:42 mariadb-server-key.pem
+</code>
+**__2. Schritt__**: Anschließend muss der soeben erstellte **MariaDB Client Schlüssel** noch in einen **RSA** kompatiblen Schlüssel umgewandelt werden, was mit nachfolgendem Befehl durchgeführt werden kann:
+<code>
+# openssl rsa -in /etc/pki/mariadb/private/mariadb-client-key.pem  -out /etc/pki/mariadb/private/mariadb-client-key.pem
+writing RSA key
+</code>
+**__3. Schritt__**: Abschließend wird nun der **MariaDB Client Zertifikatsantrag** durch **signieren** durch das **ROOT**-Zertifikat, ein **MariaDB Client Zertifikat** erstellt, was mit nachfolgendem Befehl durchführt werden kann:
+<code>
+# # openssl x509 -req -in /etc/pki/mariadb/certs/mariadb-client-csr.pem -days 3649 -CA /etc/pki/mariadb/certs/mariadb-ca-crt.pem -CAkey /etc/pki/mariadb/private/mariadb-ca-key.pem -set_serial 01 -out /etc/pki/mariadb/certs/mariadb-client-crt.pem
+Signature ok
+subject=/C=DE/ST=Bayern (Bavaria)/L=Muenchen (Munich)/O=Klaus Tachtler/CN=MariaDB Client/emailAddress=hostmaster@tachtler.net
+Getting CA Private Key
+</code>
+Mit nachfolgendem Befehl kann überprüft werden, ob das **MariaDB Server Zertifikat** erstellt wurde:
+<code>
+# ls -la /etc/pki/mariadb/*
+/etc/pki/mariadb/certs:
+total 20
+drwxr-xr-x 2 root root  147 Apr 27 05:50 .
+drwxr-xr-x 4 root root   32 Apr 27 04:44 ..
+-rw-r--r-- 1 root root 1460 Apr 27 04:54 mariadb-ca-crt.pem
+-rw-r--r-- 1 root root 1338 Apr 27 05:50 mariadb-client-crt.pem
+-rw-r--r-- 1 root root 1078 Apr 27 05:48 mariadb-client-csr.pem
+-rw-r--r-- 1 root root 1338 Apr 27 05:43 mariadb-server-crt.pem
+-rw-r--r-- 1 root root 1078 Apr 27 05:42 mariadb-server-csr.pem
+/etc/pki/mariadb/private:
+total 12
+drwxr-xr-x 2 root root   89 Apr 27 05:48 .
+drwxr-xr-x 4 root root   32 Apr 27 04:44 ..
+-rw-r--r-- 1 root root 1675 Apr 27 04:52 mariadb-ca-key.pem
+-rw-r--r-- 1 root root 1675 Apr 27 05:49 mariadb-client-key.pem
+-rw-r--r-- 1 root root 1679 Apr 27 05:42 mariadb-server-key.pem
+</code>
+Mit nachfolgendem Befehl kann das soeben erstellt **''MariaDB Client''**-Zertifikate ausgegeben und damit auch überprüft werden:
+<code>
+# openssl x509 -noout -text -in /etc/pki/mariadb/certs/mariadb-client-crt.pem
+Certificate:
+    Data:
+        Version: 1 (0x0)
+        Serial Number: 1 (0x1)
+    Signature Algorithm: sha256WithRSAEncryption
+        Issuer: C=DE, ST=Bayern (Bavaria), L=Muenchen (Munich), O=Klaus Tachtler, CN=MariaDB
+CA/emailAddress=hostmaster@tachtler.net
+        Validity
+            Not Before: Apr 27 03:50:10 2018 GMT
+            Not After : Apr 23 03:50:10 2028 GMT
         Subject: C=DE, ST=Bayern (Bavaria), L=Muenchen (Munich), O=Klaus Tachtler, CN=MariaDB
-Server/emailAddress=hostmaster@tachtler.net
+Client/emailAddress=hostmaster@tachtler.net
         Subject Public Key Info:
             Public Key Algorithm: rsaEncryption
                 Public-Key: (2048 bit)
                 Modulus:
-:ea:10:23:47:fb:c8:f0:fb:a0:c3:df:70:a7:ed:
+:d1:41:07:b9:d5:e5:83:33:f0:ef:6f:46:97:3d:
-                    aa:1b:55:18:7a:de:89:0b:d8:e4:d6:35:8c:d9:8f:
+e:0b:0d:d4:03:db:bb:f4:2d:46:75:4e:ba:99:7e:
-:c2:18:b9:df:16:73:38:cd:3c:3c:a4:9d:8b:44:
+:dc:cd:b4:b6:59:18:41:4f:29:83:ef:b2:54:4b:
-                    ac:1f:33:92:e8:96:81:f3:33:57:e2:6f:9f:e3:b2:
+                    f7:92:50:f7:cc:8d:66:6a:ea:f4:fc:7e:f0:1b:c2:
-:a7:0b:6f:53:06:c2:27:78:22:59:5e:87:a2:f1:
+:8e:34:e5:bd:40:76:db:25:23:d1:58:46:e6:67:
-e:a6:6c:75:5e:62:cf:4a:a0:4d:fb:b3:1f:92:f3:
+:48:66:c7:bb:43:0a:7b:f3:c4:26:0d:1a:b3:01:
-                    f2:18:da:ba:0b:99:f5:a3:19:33:e6:38:bf:12:1f:
+                    d7:40:3a:d4:9a:2b:74:8d:1e:9f:ec:77:0c:74:34:
-b:ef:5a:2b:65:3e:54:f3:fa:5b:95:54:09:d4:7f:
+                    cb:6a:4d:43:13:b7:63:1a:5a:6b:84:17:6e:c1:2f:
-e:b1:01:43:2e:48:db:6d:cd:29:06:a8:ce:4e:5c:
+:f2:58:2d:89:be:7d:ad:e8:26:3b:fa:bb:7d:5d:
-                    d0:8d:fc:4c:ef:10:5b:57:9b:29:53:3b:78:d1:5f:
+                    b7:d9:a3:87:7b:3a:aa:5b:4d:5a:95:75:42:1a:e1:
-c:ea:91:a7:d8:01:a1:41:4c:02:86:15:d9:e1:d2:
+                    bc:5f:4b:13:2a:70:3f:99:d2:ea:7f:7d:31:ef:aa:
-:2f:c9:a1:02:60:4a:d5:03:07:1e:f3:22:41:2c:
+                    a4:d5:33:77:e3:56:81:2e:b3:8d:41:78:51:d4:11:
-e:68:6f:59:d2:d7:17:2d:4e:dc:e2:b7:97:82:8d:
+                    fd:2a:e3:41:6b:85:bc:bf:05:fe:03:f5:1b:20:5d:
-                    d9:9e:eb:41:94:19:02:35:3c:6c:34:bc:1b:19:1c:
+                    a6:2b:2f:a9:a6:1b:41:df:e1:59:db:cd:d0:f0:9f:
-                    f2:49:c7:c1:67:1c:02:3b:e4:59:46:0d:52:04:8c:
+f:21:25:00:1f:f1:e5:b6:bc:a0:93:57:1e:36:3f:
-                    b7:a9:30:65:76:4a:38:d6:ea:fa:e8:24:ef:93:e3:
+                    a8:96:00:8c:f2:59:97:b2:fa:60:30:15:57:57:b6:
-:39:f1:33:bc:ba:f0:41:1d:33:c1:37:d5:ca:8c:
+                    ad:db:ec:70:9a:13:cb:79:0a:8b:99:84:cf:74:e6:
-:51
+d:41
                 Exponent: 65537 (0x10001)
     Signature Algorithm: sha256WithRSAEncryption
-a:26:f7:27:e9:c7:72:23:58:f9:b0:24:82:be:a1:90:5e:38:
+:d8:29:47:9f:3f:ff:99:73:1d:b2:fe:20:49:53:68:52:f0:
-:cc:cb:a2:ad:d6:e2:28:e9:0f:8c:46:60:e1:88:00:34:ee:
+:54:70:f7:f2:33:c5:16:4a:63:15:ea:9f:23:6e:1c:a5:3f:
-         a9:82:79:59:76:55:23:b3:ea:f3:73:a2:14:90:78:d4:7b:4e:
+         e2:5c:03:76:32:73:d3:ae:63:3d:88:56:71:8e:a7:60:37:a0:
-         b3:7c:24:10:45:0d:04:88:cb:47:68:b0:a8:51:ce:f2:47:de:
+         b1:9e:7e:41:88:d4:48:16:7c:8a:eb:b2:99:58:d5:b5:39:f2:
-         ae:ce:6c:ae:48:6b:08:bd:17:4a:79:f9:28:cc:c4:da:ed:cf:
+:f7:71:9b:4b:3f:0e:a8:d6:a8:98:c7:03:ef:18:ec:50:2d:
-:3b:13:bb:19:41:a5:a9:8d:e3:32:3b:0e:17:ab:4c:42:3f:
+b:5e:86:44:60:79:eb:a7:44:71:53:ea:19:48:28:e7:bc:50:
-:f1:24:e2:a4:e5:59:37:36:98:9a:00:b5:b0:da:9c:a4:1e:
+:a9:04:11:8c:93:10:ca:ff:d7:bf:c4:90:2e:af:66:bc:19:
-:62:83:94:3a:88:9d:02:fc:71:f5:13:b8:d4:67:33:a4:47:
+:a1:6c:b2:61:ef:d3:c6:2a:2a:19:21:b5:df:c1:ed:4d:21:
-         e2:a2:a2:65:1b:e2:ca:70:1b:4f:cf:7b:43:be:d4:17:f8:f3:
+:e4:3c:26:b3:c1:36:93:7d:21:20:9b:c3:fc:94:d3:29:b9:
-f:ea:41:56:ae:a8:e8:75:00:ec:da:41:53:6a:cb:fa:23:1e:
+         cd:7b:79:18:43:d0:14:b0:57:83:b0:39:c0:00:61:5e:94:57:
-:2a:ab:7d:aa:af:72:a3:75:fd:23:b1:14:4d:e6:de:43:8b:
+         b7:bb:2d:9f:64:26:f4:9b:5a:ff:9b:7a:a3:10:50:99:d5:08:
-         a4:6c:b7:0a:ed:97:44:b8:e6:f2:ac:b9:d9:eb:39:d1:f9:35:
+c:c9:d1:95:33:84:02:fb:95:d4:0f:f9:e8:7c:bf:37:c2:26:
-:9e:c0:a2:29:5c:69:70:d6:99:af:d1:fb:b0:8b:4e:c9:71:
+f:ab:c4:03:41:71:ce:bf:62:5a:c4:77:89:5a:20:15:56:81:
-         f4:26:4f:2b:01:6f:c7:6e:a7:45:a7:2a:80:10:a7:3d:2a:a4:
+f:dd:f7:f2:89:4e:fd:98:6e:89:79:58:6a:b9:5d:38:02:e6:
-c:fb:8f:e2
+c:f1:31:c9
+</code>
+==== /etc/my.cnf ====
+Um den [[https://mariadb.org/|MariaDB]]-Datenbank-Server mit **SSL**-Verschlüsselung nutzen zu können, ist es erforderlich das
+  * **ROOT**-Zertifikat aus der **eigenen CA** und das
+  * **''MariaDB Server''**-Zertifikate
+  * **''MariaDB Server''**-Schlüssel
+in den  mit einzubinden.
+Die Anpassungen sind in der Konfigurationsdatei **''/etc/my.cnf''** vorzunehmen und in diesem Beispiel, durch voranstellen eines Kommentars im Format:
+<code>
+# Tachtler
+</code>
+gekennzeichnet.
+Hier eine mögliche Anpassung (**komplette Konfigurationsdatei/nur SSL-Verschlüsselung**):
+<code ini>
+[mysqld]
+# Tachtler
+# default: datadir=/var/lib/mysql
+datadir=/var/lib/mysql/data
+socket=/var/lib/mysql/mysql.sock
+# Disabling symbolic-links is recommended to prevent assorted security risks
+symbolic-links=0
+# Settings user and group are ignored when systemd is used.
+# If you need to run mysqld under a different user or group,
+# customize your systemd unit file for mariadb according to the
+# instructions in http://fedoraproject.org/wiki/Systemd
+# Tachtler - ssl -
+ssl-ca=/etc/pki/mariadb/certs/mariadb-ca-crt.pem
+ssl-cert=/etc/pki/mariadb/certs/mariadb-server-crt.pem
+ssl-key=/etc/pki/mariadb/private/mariadb-server-key.pem
+[mysqld_safe]
+log-error=/var/log/mariadb/mariadb.log
+pid-file=/var/run/mariadb/mariadb.pid
+#
+# include all files from the config directory
+#
+!includedir /etc/my.cnf.d
+</code>
+Um überprüfen zu können, ob eine **SSL**-Verschlüsselung nun im [[https://mariadb.org/|MariaDB]]-Datenbank-Server aktiv ist, ist eine Anmeldung am [[https://mariadb.org/|MariaDB]]-Datenbank-Server erforderlich und nachfolgende Befehle, welche den Status in Bezug auf die **SSL**-Verschlüsselung zeigen:
+<code mysql>
+# mysql -h 127.0.0.1 -u root -p
+Enter password:
+Welcome to the MariaDB monitor.  Commands end with ; or \g.
+Your MariaDB connection id is 7
+Server version: 5.5.56-MariaDB MariaDB Server
+Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
+Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
+MariaDB [(none)]>
+</code>
+Anschließend zeigt nachfolgender **SQL**-Befehl die **SSL**-Verschlüsselungseinstellungen an:
+<code mysql>
+MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
++---------------+-------------------------------------------------+
+| Variable_name | Value                                           |
++---------------+-------------------------------------------------+
+| have_openssl  | YES                                             |
+| have_ssl      | YES                                             |
+| ssl_ca        | /etc/pki/mariadb/certs/mariadb-ca-crt.pem       |
+| ssl_capath    |                                                 |
+| ssl_cert      | /etc/pki/mariadb/certs/mariadb-server-crt.pem   |
+| ssl_cipher    |                                                 |
+| ssl_key       | /etc/pki/mariadb/private/mariadb-server-key.pem |
++---------------+-------------------------------------------------+
+rows in set (0.01 sec)
+</code>
+Um die Verbindung zum [[https://mariadb.org/|MariaDB]]-Datenbank-Server zu beenden, kann nachfolgender Befehl verwendet werden:
+<code>
+MariaDB [(none)]> quit
+Bye
+</code>
+Nachfolgender Befehl baut eine **SSL**-Verschlüsselte Verbindung zum [[https://mariadb.org/|MariaDB]]-Datenbank-Server auf und zeigt so, ob **SSL**-Verschlüsselte Verbindungen möglich sind:
+<code>
+# openssl s_client -connect 127.0.0.1:3306
+CONNECTED(00000003)
+140490808899488:error:140770FC:SSL routines:SSL23_GET_SERVER_HELLO:unknown protocol:s23_clnt.c:794:
+---
+no peer certificate available
+---
+No client certificate CA names sent
+---
+SSL handshake has read 7 bytes and written 289 bytes
+---
+New, (NONE), Cipher is (NONE)
+Secure Renegotiation IS NOT supported
+Compression: NONE
+Expansion: NONE
+No ALPN negotiated
+SSL-Session:
+    Protocol  : TLSv1.2
+    Cipher    : 0000
+    Session-ID:
+    Session-ID-ctx:
+    Master-Key:
+    Key-Arg   : None
+    Krb5 Principal: None
+    PSK identity: None
+    PSK identity hint: None
+    Start Time: 1524802147
+    Timeout   : 300 (sec)
+    Verify return code: 0 (ok)
+---
 </code>