Managing MySQL User Accounts - 4.5.7.3 Setting Up SSL Certificates for MySQL (
Page 6 of 7 )
Here is an example for setting up SSL certificates for MySQL:
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
To test SSL connections, start the server as follows, where $DIR is the pathname to the directory where the sample my.cnf option file is located:
shell> mysqld --defaults-file=$DIR/my.cnf &
Then invoke a client program using the same option file:
shell> mysql --defaults-file=$DIR/my.cnf
If you have a MySQL source distribution, you can also test your setup by modifying the preceding my.cnf file to refer to the demonstration certificate and key files in the SSL directory of the distribution.
