[mySQL5.5] 13장. MySQL 을SSL connection가능하게 설정

n  MySQL 을 ssl connection 가능하게 설정해 봅니다.

 

[root@EDYDR51P0 ~]# cd /etc/

[root@EDYDR51P0 etc]# mkdir newcerts

[root@EDYDR51P0 etc]# cd newcerts

[root@EDYDR51P0 newcerts]# which openssl

/usr/bin/openssl

 

1. RSA private key 를 생성합니다.( 2048 bit long modulus)

[root@EDYDR51P0 newcerts]# openssl genrsa 2048 > ca-key.pem

Generating RSA private key, 2048 bit long modulus

...................+++

......+++

e is 65537 (0x10001)

 

2. ca certificate을 생성합니다.

[root@EDYDR51P0 newcerts]# openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.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) [GB]:KO

State or Province Name (full name) [Berkshire]:Seoul

Locality Name (eg, city) [Newbury]:Seoul

Organization Name (eg, company) [My Company Ltd]:Oracle Korea

Organizational Unit Name (eg, section) []:

Common Name (eg, your name or your server's hostname) []:edydr51p0

Email Address []:

 

 

 

 

3.

[root@EDYDR51P0 newcerts]# 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) [GB]:KO

State or Province Name (full name) [Berkshire]:Seoul

Locality Name (eg, city) [Newbury]:Seoul

Organization Name (eg, company) [My Company Ltd]:Oracle Koea

Organizational Unit Name (eg, section) []:

Common Name (eg, your name or your server's hostname) []:edydr51p0

Email Address []:

 

Please enter the following 'extra' attributes

to be sent with your certificate request

A challenge password []:oracle

An optional company name []:

 

4. private key를 생성합니다.

[root@EDYDR51P0 newcerts]# 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=KO/ST=Seoul/L=Seoul/O=Oracle Koea/CN=edydr51p0

Getting CA Private Key

 

5.

[root@EDYDR51P0 newcerts]# 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) [GB]:KO

State or Province Name (full name) [Berkshire]:Seoul

Locality Name (eg, city) [Newbury]:Seoul

Organization Name (eg, company) [My Company Ltd]:Oracle Korea

Organizational Unit Name (eg, section) []:

Common Name (eg, your name or your server's hostname) []:edudr51p0

Email Address []:

 

Please enter the following 'extra' attributes

to be sent with your certificate request

A challenge password []:oracle

An optional company name []:

 

6.

[root@EDYDR51P0 newcerts]# 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=KO/ST=Seoul/L=Seoul/O=Oracle Korea/CN=edudr51p0

Getting CA Private Key

7. Stefan에게 ssl접속권한을 부여합니다.

[root@EDYDR51P0 newcerts]# mysql -uroot -poracle

mysql> grant select on mysql.user to 'Stefan'@'localhost' identified by 'oracle' REQUIRE SSL;

 

mysql>  show grants for 'Stefan'@'localhost'\G

*************************** 1. row ***************************

Grants for Stefan@localhost: GRANT USAGE ON *.* TO 'Stefan'@'localhost' IDENTIFIED BY PASSWORD '*2447D497B9A6A15F2776055CB2D1E9F86758182F' REQUIRE SSL

*************************** 2. row ***************************

Grants for Stefan@localhost: GRANT SELECT ON `mysql`.`user` TO 'Stefan'@'localhost'

2 rows in set (0.00 sec)

 

8.mysql client프로세스를 kill한후, mysqld를 다시 시작합니다.

[root@EDYDR51P0 newcerts]# /etc/init.d/mysql stop

Shutting down MySQL.                                       [  OK  ]

 [root@EDYDR51P0 newcerts]# /etc/init.d/mysql start --ssl-ca=/etc/newcerts/ca-cert.pem --ssl-cert=/etc/newcerts/server-cert.pem --ssl-key=/etc/newcerts/server-key.pem

Starting MySQL.                                            [  OK  ]

 

# ps –ef|grep mysql

root     10535     1  0 22:05 pts/2    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/EDYDR51P0.pid --ssl-ca=/etc/newcerts/ca-cert.pem --ssl-cert=/etc/newcerts/server-cert.pem --ssl-key=/etc/newcerts/server-key.pem

mysql    10809 10535  0 22:05 pts/2    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --ssl-ca=/etc/newcerts/ca-cert.pem --ssl-cert=/etc/newcerts/server-cert.pem --ssl-key=/etc/newcerts/server-key.pem --log-error=/var/lib/mysql/EDYDR51P0.err --pid-file=/var/lib/mysql/EDYDR51P0.pid --socket=/var/lib/mysql/mysql.sock --port=3306

: mysqld가 시작된 옵션을 확인해 봅니다.

 

9. Stefan유저로 non-ssl로 접속하면 오류가 발생합니다.

[root@EDYDR51P0 newcerts]# mysql -uStefan -poracle

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

 

이번에는 인증서를 통한 접속은 잘 됩니다.

[root@EDYDR51P0 newcerts]# mysql -uStefan -poracle --ssl-ca=/etc/newcerts/ca-cert.pem

mysql> show variables like 'have_ssl';

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

| Variable_name | Value |

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

| have_ssl      | YES   |

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

 

mysql> show status like 'Ssl_cipher';

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

| Variable_name | Value              |

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

| Ssl_cipher    | DHE-RSA-AES256-SHA |

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

 

[root@EDYDR51P0 newcerts]# /etc/init.d/mysql stop

Shutting down MySQL.                                       [  OK  ]

[root@EDYDR51P0 newcerts]# /etc/init.d/mysql start

Starting MySQL.                                            [  OK  ]