LinuxでMySQLサーバーとクライアントのSSLを設定する方法

このチュートリアルでは、暗号化にSSH接続を使用してMySQLサーバーへの安全な接続を設定し、データベース内のデータを安全に保ち、ハッカーがデータを盗むことができないようにする方法について説明します。 SSLは、フィッシング攻撃から保護できるSSL証明書の手段を検証するために使用されます。これにより、MySQLサーバーでSSLを有効にする方法もわかります。

SSLサポートの有効化

MySQLサーバーに接続し、MySQLサーバーのSSLステータスを確認します

# mysql -u root -p
mysql> show variables like '%ssl%';
Output:
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl | DISABLED  |
| have_ssl     |  DISABLED |
| ssl_ca       |           |
| ssl_capath   |           |
| ssl_cert     |           |
| ssl_cipher   |           |
| ssl_key      |           |
+---------------+----------+
7 rows in set (0.00 sec)
mysql> \q
Bye

MySQL用のSSL証明書の生成

証明書ファイルを保存するためのディレクトリを作成します

# mkdir /etc/certificates
# cd /etc/certificates

サーバー証明書の生成

# openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus
...................................................................................+++
..........+++
e is 65537 (0x10001)
# openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
Generating a 2048 bit RSA private key
..................+++
..............................................................................................+++
writing new private key to 'server-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]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
# openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd
Error opening CA Certificate ca-cert.pem
139991633303368:error:02001002:system library:fopen:No such file or directory:bss_file.c:398:fopen('ca-cert.pem','r')
139991633303368:error:20074002:BIO routines:FILE_CTRL:system lib:bss_file.c:400:
unable to load certificate
Generating client certificates

# openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
Generating a 2048 bit RSA private key
...............................................+++
.................+++
writing new private key to '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]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:
Email Address []:
Please enter the following 'extra' attributes openssl x509 -req -in client-req.pem -days 1000 -CA ca-# cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd
Error opening CA Certificate ca-cert.pem
140327140685640:error:02001002:system library:fopen:No such file or directory:bss_file.c:398:fopen('ca-cert.pem','r')
140327140685640:error:20074002:BIO routines:FILE_CTRL:system lib:bss_file.c:400:
unable to load certificate to be sent with your certificate request
A challenge password []:
An optional company name []:

次に、my.cnfファイルを開き、証明書を追加します

# vi /etc/my.cnf
[mysqld]
ssl-ca=/etc/certificates/cacert.pem
ssl-cert=/etc/certificates/server-cert.pem
ssl-key=/etc/certificates/server-key.pem

MySQLサーバーを再起動し、証明書のステータスを確認します

#service mysqld restart
#mysql -uroot -p
mysql>show variables like '%ssl%';
+---------------+-----------------------------------+
| Variable_name |        Value                      |
+---------------+-----------------------------------+
| have_openssl  |          YES                      |
| have_ssl      |          YES                      |
| ssl_ca        |/etc/certificates/cacert.pem       |
| ssl_capath    |                                   |
| ssl_cert      | /etc/certificates/server-cert.pem |
| ssl_cipher    |                                   |
| ssl_key       | /etc/certificates/server-key.pem  |
+---------------+-----------------------------------+
7 rows in set (0.00 sec)

SSLアクセスを使用するユーザーの作成

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘ssl_user’@’%’ IDENTIFIED BY ‘password’ REQUIRE SSL;
mysql> FLUSH PRIVILEGES;

MySQLクライアントのSSLを構成する

サーバー側から、client-cert.pemclient-key.pemclient-req.pemをサーバーからクライアントにコピーする必要がありました。

# scp /etc/ certificates/client-cert.pem [email protected]:/etc/certificates
# scp /etc/ certificates/client-key.pem [email protected]:/etc/certificates
# scp /etc/ certificates/client-req.pem [email protected]:/etc/certificates

クライアントに転送されたファイルがクライアントに接続されたら、SSL証明書を使用してMySQLに接続してみてください。

# mysql --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -h 192.168.87.156 -u ssluser -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> status
--------------
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
Connection id: 3
Current database:
Current user: root@localhost
SSL: Clipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.73 Source distribution
Protocol version: 10
Connection: 192.168.87.158 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 11 min 13 sec
Threads: 1 Questions: 8 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.11
-------------

後で、/ etc / my.cnfファイルの設定を永続的に追加して、MySQLサーバーに接続するときにSSLを使用して接続する必要があるようにします。

# vi /etc/my.cnf
[client]
ssl-ca=/etc/certificates/ client-cert.pem
ssl-cert=/etc/certificates/client-cert.pem
ssl-key=/etc/certificates/client-key.pem

この構成とセットアップが完了すると、SSLキーを使用してクライアント側からMySQLサーバーに接続し、データを盗むことからデータを保護し、ハッカーからデータを保護することができます。