浮雲雅築: 7月 2016

2016年7月22日星期五

[研究] 讓 MySQL Server 5.1.37 和 Client 用 SSL 連線 (Cent OS 6.8 x64)

[研究] 讓 MySQL Server 5.1.37 和 Client 用 SSL 連線 (Cent OS 6.8 x64)

2016-07-22

注意，建立証書/憑證時，Client與Server的Common Name必須與CA証書的Common Name不同才行，否則會失敗。

參考

MariaDB - Secure Connections
https://mariadb.com/kb/en/mariadb/secure-connections/

MySQL 5.7 Reference Manual / 7.4.6.2 Creating SSL Certificates and Keys Using openssl

http://dev.mysql.com/doc/refman/5.7/en/creating-ssl-files-using-openssl.html

	憑證請求Certificate request	憑證Certificate (Public Key)	私鑰 (Private Key)
CA	--	ca.pem	ca-key.pem
Server	server-req.pem	server-cert.pem	server-key.pem
Client	client-req.pem	client-cert.pem	client-key.pem

安裝 MySQL Server, OpenSSL

[root@localhost ~]# yum -y install mysql-server mysql openssl

[root@localhost ~]# service mysqld restart
[root@localhost ~]# chkconfig mysqld on

如果想變更 root 密碼，可用下面方法

/usr/bin/mysqladmin -u root password 'new-password'

/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

[root@localhost ~]# yum list | grep openssl

openssl.x86_64 1.0.1e-48.el6_8.1 @updates
krb5-pkinit-openssl.x86_64 1.10.3-57.el6 base
openssl.i686 1.0.1e-48.el6_8.1 updates
openssl-devel.i686 1.0.1e-48.el6_8.1 updates
openssl-devel.x86_64 1.0.1e-48.el6_8.1 updates
openssl-perl.x86_64 1.0.1e-48.el6_8.1 updates
openssl-static.x86_64 1.0.1e-48.el6_8.1 updates
openssl098e.i686 0.9.8e-20.el6.centos.1 base
openssl098e.x86_64 0.9.8e-20.el6.centos.1 base
xmlsec1-openssl.i686 1.2.20-4.el6 base
xmlsec1-openssl.x86_64 1.2.20-4.el6 base
xmlsec1-openssl-devel.i686 1.2.20-4.el6 base
xmlsec1-openssl-devel.x86_64 1.2.20-4.el6 base
[root@localhost ~]#

使用的是 OpenSSL 1.0.1e 版

先確認支援 SSL (應該看到類似下面訊息)

[root@localhost ~]# ldd /usr/libexec/mysqld | grep ssl
libssl.so.10 => /usr/lib64/libssl.so.10 (0x00007f08f2522000)

[root@localhost ~]#

(一) CA 憑證

建立私鑰 ca-key.pem

[root@localhost ~]# openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus
.........+++
..............................................................................................+++
e is 65537 (0x10001)

建立憑證 ca.pem

[root@localhost ~]# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.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) []:a

Email Address []:

[root@localhost ~]#

(二) Server 憑證

注意，建立証書時，Client與Server的Common Name必須與CA証書的Common Name不同才行，否則會失敗。

建立私鑰 (server-key.pem) 及申請 (server-req.pem)
問題全部按下 Enter

[root@localhost ~]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out 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) []:b
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@localhost ~]#

建立公鑰 server-key.pem

[root@localhost ~]# openssl rsa -in server-key.pem -out server-key.pem

writing RSA key

建立憑證 server-cert.pem

[root@localhost ~]# openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Signature ok

subject=/C=XX/L=Default City/O=Default Company Ltd

Getting CA Private Key

[root@localhost ~]#

(三) Client 憑證

建立私鑰及申請

[root@localhost ~]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out 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) []:c

Email Address []:

Please enter the following 'extra' attributes

to be sent with your certificate request

A challenge password []:

An optional company name []:

[root@localhost ~]#

建立公鑰

[root@localhost ~]# openssl rsa -in client-key.pem -out client-key.pem
writing RSA key

建立憑證

[root@localhost ~]# openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd
Getting CA Private Key
[root@localhost ~]#

[root@localhost ~]# mkdir /etc/newcerts

[root@localhost ~]# cp client-* ca* server*.pem /etc/newcerts/

[root@localhost ~]# ls /etc/newcerts

ca-key.pem client-cert.pem client-req.pem server-key.pem

ca.pem client-key.pem server-cert.pem server-req.pem

********************************************************************************

檢查 MariaDB 目前是否支援SSL

[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
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> 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)

mysql> \q
Bye
[root@localhost ~]#

目前不支援，編輯 /etc/my.cnf

[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@localhost ~]#

[root@localhost ~]# vi /etc/my.cnf

(Windows 版是 my.ini)

[mysqld] 區域最後加上

ssl-ca=/etc/newcerts/ca.pem

ssl-cert=/etc/newcerts/server-cert.pem

ssl-key=/etc/newcerts/server-key.pem

重新啟動讓 my.cnf 的設定生效

[root@localhost ~]# service mysqld restart

Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@localhost ~]#

再次檢查

[root@localhost ~]# mysql -e "show variables like '%ssl%';"

+---------------+-------------------------------+

| Variable_name | Value |

+---------------+-------------------------------+

| have_openssl | YES |

| have_ssl | YES |

| ssl_ca | /etc/newcerts/ca.pem |

| ssl_capath | |

| ssl_cert | /etc/newcerts/server-cert.pem |

| ssl_cipher | |

| ssl_key | /etc/newcerts/server-key.pem |

+---------------+-------------------------------+

現在支援了

測試

接下來測試 MariaDB Server 和 Client 的連線

建立 TestDB 資料庫，TestUser 使用者，密碼為 TestPassword，並強制該使用者連上資料庫必須用 SSL 方式

mysql -e "create database TestDB"

mysql -e "show databases; "

mysql -e "grant all on TestDB.* to TestUser@localhost; "

mysql -e "set password for TestUser@localhost=password('TestPassword'); "

mysql -e "grant all privileges on TestDB.* to TestUser@'%' identified by 'TestPassword' require ssl;"

mysql -e "grant all privileges on TestDB.* to TestUser@localhost identified by 'TestPassword' require ssl;"

mysql -e "flush privileges; "

mysql -e "use mysql; select Host,User, ssl_type from user; "

嘗試直接連線，不用 SSL，結果失敗 (-u 和帳號之間空一格，-p和密碼間不空格)

[root@localhost phpMyAdmin]# mysql -u TestUser -pTestPassword TestDB

ERROR 1045 (28000): Access denied for user 'TestUser'@'localhost' (using password: YES)

[root@localhost phpMyAdmin]#

用 SSL 方式連線，可以連上。

Your MySQL connection id is 12
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> \q
Bye
[root@localhost ~]#

上面測試成功了。

建立証書/憑證時，Common Name 相同，會有類似下面錯誤。

[root@localhost phpMyAdmin]# mysql -u TestUser -pTestPassword TestDB --ssl-key=/etc/newcerts/client-key.pem --ssl-cert=/etc/newcerts/client-cert.pem --ssl-ca=/etc/newcerts/ca.pem

ERROR 2026 (HY000): SSL connection error: error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed

[root@localhost phpMyAdmin]#

(完)

相關

安裝MariaDB的基本設定 + SSL
http://www.myhome.net.tw/2016_02/p25.htm

MariaDB 啟用 SSL/TLS
http://abcg5.pixnet.net/blog/post/114321773-mariadb-%E5%95%9F%E7%94%A8-ssl

啟用 MariaDB 支援 SSL 傳輸
http://technote.aven-network.com/610/%E5%95%9F%E7%94%A8-mariadb-%E6%94%AF%E6%8F%B4-ssl-%E5%82%B3%E8%BC%B8

Setting up MySQL SSL and secure connections
https://www.percona.com/blog/2013/06/22/setting-up-mysql-ssl-and-secure-connections/

Tutorial: Install Wordpress with MariaDB in SSL replication.
https://www.ulyaoth.net/resources/tutorial-install-wordpress-with-mariadb-in-ssl-replication.40/

MySQL 5.7 Reference Manual / 7.4.6.2 Creating SSL Certificates and Keys Using openssl

http://dev.mysql.com/doc/refman/5.7/en/creating-ssl-files-using-openssl.html

Chapter 4 MySQL Functions (PDO_MYSQL)

https://dev.mysql.com/doc/apis-php/en/apis-php-pdo-mysql.html

mysqli_ssl_set()

https://dev.mysql.com/doc/apis-php/en/apis-php-mysqli.ssl-set.html

[研究] phpMyAdmin 4.4.6 安裝 (CentOS 7.1 x64)
http://shaurong.blogspot.com/2015/05/phpmyadmin-446-centos-71-x64.html

[研究] phpMyAdmin 4.0.10.10 安裝 (CentOS 6.6 x64)
http://shaurong.blogspot.com/2015/05/phpmyadmin-401010-centos-66-x64.html

(完)

[研究] 讓 MariaDB Server 5.5.47 和 Client 用 SSL 連線 (Cent OS 7.3 x64)

[研究] 讓 MariaDB Server 5.5.47 和 Client 用 SSL 連線 (Cent OS 7.3 x64)

2016-07-22

注意，建立証書/憑證時，Client與Server的Common Name必須與CA証書的Common Name不同才行，否則會失敗。

參考

MariaDB - Secure Connections
https://mariadb.com/kb/en/mariadb/secure-connections/

MySQL 5.7 Reference Manual / 7.4.6.2 Creating SSL Certificates and Keys Using openssl

http://dev.mysql.com/doc/refman/5.7/en/creating-ssl-files-using-openssl.html

	憑證請求Certificate request	憑證Certificate (Public Key)	私鑰 (Private Key)
CA	--	ca.pem	ca-key.pem
Server	server-req.pem	server-cert.pem	server-key.pem
Client	client-req.pem	client-cert.pem	client-key.pem

[root@localhost ~]# yum list | grep openssl
openssl.x86_64 1:1.0.1e-42.el7.9 @anaconda
openssl-libs.x86_64 1:1.0.1e-42.el7.9 @anaconda
apr-util-openssl.x86_64 1.5.2-6.el7 base
openssl.x86_64 1:1.0.1e-51.el7_2.5 updates
openssl-devel.i686 1:1.0.1e-51.el7_2.5 updates
openssl-devel.x86_64 1:1.0.1e-51.el7_2.5 updates
openssl-libs.i686 1:1.0.1e-51.el7_2.5 updates
openssl-libs.x86_64 1:1.0.1e-51.el7_2.5 updates
openssl-perl.x86_64 1:1.0.1e-51.el7_2.5 updates
openssl-static.i686 1:1.0.1e-51.el7_2.5 updates
openssl-static.x86_64 1:1.0.1e-51.el7_2.5 updates
openssl098e.i686 0.9.8e-29.el7.centos.3 updates
openssl098e.x86_64 0.9.8e-29.el7.centos.3 updates
xmlsec1-openssl.i686 1.2.20-5.el7 base
xmlsec1-openssl.x86_64 1.2.20-5.el7 base
xmlsec1-openssl-devel.i686 1.2.20-5.el7 base
xmlsec1-openssl-devel.x86_64 1.2.20-5.el7 base
[root@localhost ~]#

使用的是 OpenSSL 1.0.1e 版

安裝 MariaDB ( CentOS 7.x 預設不是 MySQL 了)

[root@localhost ~]# yum -y install mariadb-server mariadb mysql

啟動
[root@localhost ~]# systemctl restart mariadb

如果想變更 root 密碼，可用下面方法

/usr/bin/mysqladmin -u root password 'new-password'

/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

先確認 MariaDB 支援 SSL (應該看到下面訊息)

[root@localhost ~]# ldd /usr/libexec/mysqld | grep ssl
libssl.so.10 => /lib64/libssl.so.10 (0x00007f803dbd1000)
[root@localhost ~]#

(一) CA 憑證

[root@localhost ~]# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.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) []:a

Email Address []:

[root@localhost ~]#

建立公鑰 server-key.pem

[root@localhost ~]# openssl rsa -in server-key.pem -out server-key.pem

writing RSA key

建立憑證 server-cert.pem

[root@localhost ~]# openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Signature ok

subject=/C=XX/L=Default City/O=Default Company Ltd

Getting CA Private Key

[root@localhost ~]#

(三) Client 憑證

建立私鑰及申請

[root@localhost ~]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out 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) []:c

Email Address []:

Please enter the following 'extra' attributes

to be sent with your certificate request

A challenge password []:

An optional company name []:

[root@localhost ~]#

建立公鑰

[root@localhost ~]# mkdir /etc/newcerts

[root@localhost ~]# cp client-* ca* server*.pem /etc/newcerts/

[root@localhost ~]# ls /etc/newcerts

ca-key.pem client-cert.pem client-req.pem server-key.pem

ca.pem client-key.pem server-cert.pem server-req.pem

********************************************************************************

檢查 MariaDB 目前是否支援SSL

[root@localhost ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 2

Server version: 5.5.47-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 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)

MariaDB [(none)]> \q

Bye

[root@localhost ~]#

目前不支援，編輯 /etc/my.cnf

[root@localhost ~]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

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

[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

[root@localhost ~]#

[root@localhost ~]# vi /etc/my.cnf

(Windows 版是 my.ini)

[mysqld] 區域最後加上

ssl-ca=/etc/newcerts/ca.pem

ssl-cert=/etc/newcerts/server-cert.pem

ssl-key=/etc/newcerts/server-key.pem

重新啟動讓 my.cnf 的設定生效

[root@localhost ~]# service mariadb restart

Redirecting to /bin/systemctl restart mariadb.service

再次檢查

[root@localhost ~]# mysql -e "show variables like '%ssl%';"

+---------------+-------------------------------+

| Variable_name | Value |

+---------------+-------------------------------+

| have_openssl | YES |

| have_ssl | YES |

| ssl_ca | /etc/newcerts/ca.pem |

| ssl_capath | |

| ssl_cert | /etc/newcerts/server-cert.pem |

| ssl_cipher | |

| ssl_key | /etc/newcerts/server-key.pem |

+---------------+-------------------------------+

現在支援了

測試

接下來測試 MariaDB Server 和 Client 的連線

建立 TestDB 資料庫，TestUser 使用者，密碼為 TestPassword，並強制該使用者連上資料庫必須用 SSL 方式

mysql -e "create database TestDB"

mysql -e "show databases; "

mysql -e "grant all on TestDB.* to TestUser@localhost; "

mysql -e "set password for TestUser@localhost=password('TestPassword'); "

mysql -e "grant all privileges on TestDB.* to TestUser@'%' identified by 'TestPassword' require ssl;"

mysql -e "grant all privileges on TestDB.* to TestUser@localhost identified by 'TestPassword' require ssl;"

mysql -e "flush privileges; "

mysql -e "use mysql; select Host,User, ssl_type from user; "

嘗試直接連線，不用 SSL，結果失敗 (-u 和帳號之間空一格，-p和密碼間不空格)

[root@localhost phpMyAdmin]# mysql -u TestUser -pTestPassword TestDB

ERROR 1045 (28000): Access denied for user 'TestUser'@'localhost' (using password: YES)

[root@localhost phpMyAdmin]#

用 SSL 方式連線，可以連上。

[root@localhost ~]# mysql -u TestUser -pTestPassword TestDB --ssl-key=/etc/newcerts/client-key.pem --ssl-cert=/etc/newcerts/client-cert.pem --ssl-ca=/etc/newcerts/ca.pem
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.47-MariaDB MariaDB Server

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [TestDB]> \q
Bye
[root@localhost ~]#

上面測試成功了。

建立証書/憑證時，Common Name 相同，會有下面錯誤。

[root@localhost phpMyAdmin]# mysql -u TestUser -pTestPassword TestDB --ssl-key=/etc/newcerts/client-key.pem --ssl-cert=/etc/newcerts/client-cert.pem --ssl-ca=/etc/newcerts/ca.pem

ERROR 2026 (HY000): SSL connection error: error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed

[root@localhost phpMyAdmin]#

(完)

相關