MySQLでSSL接続を有効にする

環境

• AmazonLinux AMI release 2014.03
• MySQL 5.5
• OpenSSL 1.0.0

MySQLのインストール

パッケージでのインストール

$ sudo yum install mysql-server
$ sudo /etc/init.d/mysqld start

mysqlサーバのSSL対応の確認

mysql> show variables like '%ssl%';
+---------------+----------+
| 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)

SSL証明書の作成

サーバ証明書の作成

$ openssl genrsa 2048 > ca-key.pem
$ openssl req -new -x509 -nodes -days 3600 \
    -key ca-key.pem -out ca-cert.pem
$ openssl req -newkey rsa:2048 -days 3600  \
    -nodes -keyout server-key.pem -out server-req.pem
$ openssl rsa -in server-key.pem -out server-key.pem
$ openssl x509 -req -in server-req.pem -days 3600 \
    -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 \
    -out server-cert.pem

クライアント証明書の作成

※ クライアント認証を有効にする場合に使用する

$ openssl req -newkey rsa:2048 -days 3600 \
    -nodes -keyout client-key.pem -out client-req.pem
$ openssl rsa -in client-key.pem -out client-key.pem
$ openssl x509 -req -in client-req.pem -days 3600 \
    -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 \
    -out client-cert.pem

MySQLへ設定追加

/etc/my.cnfへ以下を追加

/etc/my.cnf

[mysqld]

...(省略)...

ssl-ca = /path/to/ca-cert.pem
ssl-cert = /path/to/server-cert.pem
ssl-key = /path/to/server-key.pem

mysqldを再起動

$ sudo /etc/init.d/mysqld restart

mysqlサーバのSSL対応を再度、確認

mysql> show variables like '%ssl%';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| have_openssl  | YES                      |
| have_ssl      | YES                      |
| ssl_ca        | /path/to/ca-cert.pem     |
| ssl_capath    |                          |
| ssl_cert      | /path/to/server-cert.pem |
| ssl_cipher    |                          |
| ssl_key       | /path/to/server-key.pem  |
+---------------+--------------------------+
7 rows in set (0.00 sec)

動作確認

mysqlへ接続

$ mysql -u root -p --ssl-ca=/path/to/ca-cert.pem

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.38, for Linux (x86_64) using  deadline 5.1

Connection id:          1030
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.38 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 10 days 13 hours 13 min 24 sec

Threads: 2  Questions: 12840  Slow queries: 8  Opens: 258  Flush tables: 1  Open tables: 251  Queries per second avg: 0.014
--------------

SSL接続必須のアカウントを作成

mysql> grant all on test.* ssluser@'localhost' identified by ${password} require ssl;

※ SSL接続を必須とする場合は、アカウント作成時に、sslオプションを指定する。

mysqlへ接続(オプションなし)

$ mysql -u ssluser -p
ERROR 1045 (28000): Access denied for user 'ssluser'@'localhost' (using password: YES)

※ 接続できない

mysqlへ接続(オプションあり)

$ mysql -u ssluser -p --ssl-ca=/path/to/ca-cert.pem

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.38, for Linux (x86_64) using  deadline 5.1

Connection id:          1043
Current database:
Current user:           ssluser@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.38 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 10 days 13 hours 50 min 34 sec

Threads: 2  Questions: 12927  Slow queries: 8  Opens: 258  Flush tables: 1  Open tables: 251  Queries per second avg: 0.014
--------------

参考

[6.3.9.3 Using SSL Connections]
(http://dev.mysql.com/doc/refman/5.5/en/using-ssl-connections.html)
[6.3.9.5 Setting Up SSL Certificates and Keys for MySQL]
(http://dev.mysql.com/doc/refman/5.5/en/creating-ssl-certs.html)
[13.7.1.3 GRANT Syntax]
(http://dev.mysql.com/doc/refman/5.5/en/grant.html)