= Using Secure Connections for MySQL =
À̼ºÈ£ myohan@gmail.com
¸¶Áö¸· ¼öÁ¤ 2005.07.15
==== ÀúÀÛ±Ç ====
ÀÌ ¹®¼­´Â http://mysql.com ¿¡¼­ ¹ßÃéÇÑ ³»¿ëµéÀ» ¼ø¼­´ë·Î Á¤¸®ÇÑ °ÍÀÔ´Ï´Ù. ³»¿ëÀ» º¸Àå µå¸± ¼ö ¾ø½À´Ï´Ù_(__)_ °è¼Ó ¼öÁ¤ Áß¿¡ ÀÖ½À´Ï´Ù. ÀÌ ¹®¼­¿¡ ³ª¿À´Â µî·Ï»óÇ¥ÀÇ ¼ÒÀ¯±ÇÀº °¢°¢ ±× ¼ÒÀ¯ÀÚ¿¡°Ô ÀÖ½À´Ï´Ù.
----
== 1. Requirements ==
MySQL¿¡¼­ SSL connections¸¦ ÀÌ¿ë ÇÏ·Á¸é OpenSSLÀ» Áö¿øÇÏ´Â MySQL 4.0.0 ¶Ç´Â ±× ÀÌ»óÀÇ versionÀÌ ÇÊ¿äÇÏ´Ù.

´ÙÀ½ÀÇ »çÇ×µéÀ» ÁغñÇØ¾ß ÇÑ´Ù.
 * OpenSSL library°¡ ÇÊ¿äÇÏ´Ù.
 * MySQLÀ» configureÇÒ¶§ --with-vio and --with-openssl ¿É¼ÇÀÌ ÇÊ¿äÇÏ´Ù.
 * mysql.user tableÀÇ SSL GRANT OptionsÀÌ ÇÊ¿äÇÏ´Ù.
 * mysqld server°¡ supports OpenSSLÀÇ ¿©ºÎ¸¦ üũÇØ¾ß ÇÑ´Ù.

{{{
mysql> SHOW VARIABLES LIKE 'have_openssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
+---------------+-------+ 
}}}

== 2. Setting Up SSL Certificates for MySQL ==
MySQLÀ» À§ÇÑ SSL certificates ÀÌ´Ù.

{{{
DIR=`pwd`/openssl
PRIV=$DIR/private

mkdir $DIR $PRIV $DIR/newcerts
cp /usr/share/ssl/openssl.cnf $DIR
replace ./demoCA $DIR -- $DIR/openssl.cnf

# Create necessary files: $database, $serial and $new_certs_dir
# directory (optional)

touch $DIR/index.txt
echo "01" > $DIR/serial

#
# Generation of Certificate Authority(CA)
#

openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/cacert.pem \
-config $DIR/openssl.cnf

# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ................++++++
# .........++++++
# writing new private key to '/home/monty/openssl/private/cakey.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# 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) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL admin
# Email Address []:

#
# Create server request and key
#
openssl req -new -keyout $DIR/server-key.pem -out \
$DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf

# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# ..++++++
# ..........++++++
# writing new private key to '/home/monty/openssl/server-key.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# 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) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL server
# Email Address []:
#
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:

#
# Remove the passphrase from the key (optional)
#

openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem

#
# Sign server cert
#
openssl ca -policy policy_anything -out $DIR/server-cert.pem \
-config $DIR/openssl.cnf -infiles $DIR/server-req.pem

# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Enter PEM pass phrase:
# Check that the request matches the signature
# Signature ok
# The Subjects Distinguished Name is as follows
# countryName :PRINTABLE:'FI'
# organizationName :PRINTABLE:'MySQL AB'
# commonName :PRINTABLE:'MySQL admin'
# Certificate is to be certified until Sep 13 14:22:46 2003 GMT
# (365 days)
# Sign the certificate? [y/n]:y
#
#
# 1 out of 1 certificate requests certified, commit? [y/n]y
# Write out database with 1 new entries
# Data Base Updated

#
# Create client request and key
#
openssl req -new -keyout $DIR/client-key.pem -out \
$DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf

# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Generating a 1024 bit RSA private key
# .....................................++++++
# .............................................++++++
# writing new private key to '/home/monty/openssl/client-key.pem'
# Enter PEM pass phrase:
# Verifying password - Enter PEM pass phrase:
# -----
# 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) [AU]:FI
# State or Province Name (full name) [Some-State]:.
# Locality Name (eg, city) []:
# Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB
# Organizational Unit Name (eg, section) []:
# Common Name (eg, YOUR name) []:MySQL user
# Email Address []:
#
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:

#
# Remove a passphrase from the key (optional)
#
openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem

#
# Sign client cert
#

openssl ca -policy policy_anything -out $DIR/client-cert.pem \
-config $DIR/openssl.cnf -infiles $DIR/client-req.pem

# Sample output:
# Using configuration from /home/monty/openssl/openssl.cnf
# Enter PEM pass phrase:
# Check that the request matches the signature
# Signature ok
# The Subjects Distinguished Name is as follows
# countryName :PRINTABLE:'FI'
# organizationName :PRINTABLE:'MySQL AB'
# commonName :PRINTABLE:'MySQL user'
# Certificate is to be certified until Sep 13 16:45:17 2003 GMT
# (365 days)
# Sign the certificate? [y/n]:y
#
#
# 1 out of 1 certificate requests certified, commit? [y/n]y
# Write out database with 1 new entries
# Data Base Updated

#
# Create a my.cnf file that you can use to test the certificates
#

cnf=""
cnf="$cnf [client]"
cnf="$cnf ssl-ca=$DIR/cacert.pem"
cnf="$cnf ssl-cert=$DIR/client-cert.pem"
cnf="$cnf ssl-key=$DIR/client-key.pem"
cnf="$cnf [mysqld]"
cnf="$cnf ssl-ca=$DIR/cacert.pem"
cnf="$cnf ssl-cert=$DIR/server-cert.pem"
cnf="$cnf ssl-key=$DIR/server-key.pem"
echo $cnf | replace " " '
' > $DIR/my.cnf

Run MySQL server :

shell> mysqld --defaults-file=$DIR/my.cnf &

Run MySQL client :

shell> mysql --defaults-file=$DIR/my.cnf
}}}

== 3. SUBJECT¿Í ISSUER value ¼³Á¤ ==
{{{
mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
-> IDENTIFIED BY 'goodsecret'
-> REQUIRE SUBJECT '/C=KO/ST=Some-State/CN=Enwiser Inc/'
-> AND ISSUER'/C=KO/ST=Some-State/CN=Enwiser Inc/'
-> AND CIPHER 'EDH-RSA-DES-CBC3-SHA';
}}}

== 4. Configuration files and Configurations ==
=== 1. /etc/mysql/my.cnf ===
{{{
[client]
ssl-ca=/usr/local/mysql_ssl/openssl/cacert.pem
ssl-cert=/usr/local/mysql_ssl/openssl/client-cert.pem
ssl-key=/usr/local/mysql_ssl/openssl/client-key.pem
socket=/tmp/mysql.sock

[mysqld]
ssl-ca=/usr/local/mysql_ssl/openssl/cacert.pem
ssl-cert=/usr/local/mysql_ssl/openssl/server-cert.pem
ssl-key=/usr/local/mysql_ssl/openssl/server-key.pem
}}}

=== 2. MySQL configure Options ===
{{{
./configure --with-vio --with-openssl=/usr/local/ssl/ \
--prefix=/usr/local/mysql_ssl/ \
--localstatedir=/usr/local/mysql_ssl/data/
}}}

== 5. Âü°í¹®Çå ==
 * http://www.mysql.com
 * http://www.openssl.org