· KLDP.org · KLDP.net · KLDP Wiki · KLDP BBS ·
myslq_ssl

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/


ID
Password
Join
Let him who takes the Plunge remember to return it by Tuesday.


sponsored by andamiro
sponsored by cdnetworks
sponsored by HP

Valid XHTML 1.0! Valid CSS! powered by MoniWiki
last modified 2005-07-15 18:12:12
Processing time 0.0048 sec