User Comments

Hopefully this will help make things more clear, since the existing documentation on Mysql/SSL compatability is rather vague. If you want Mysql to support SSL/encrypted connections, you will have to compile it yourself from the source code. The distributed binaries DO NOT have SSL support. If you start a server and execute the command 'SHOW VARIABLES LIKE "%ssl%" and it returns have_openssl = 'false', you will have to re-compile Mysql. Currently, I believe the only SSL support in Mysql is through OpenSSL, so unless you have OpenSSL installed on your system you'll have to wait for a future release. It can be installed on a Windows platform (see http://www.openssl.org to download and for instructions on installing OpenSSL).

Here is what it took to compile Mysql with support for OpenSSL under Solaris 2.8 (this is assuming you already have OpenSSL installed and working, I am currently using v0.9.7b):

1) Download and unpack the Mysql source tarball. CD to the directory where you unpacked it.

2) Before you start compiling Mysql, you may have to change the configure script depending on where you have OpenSSL installed, or Mysql won't find the OpenSSL library. configure only checks the following directories looking for openssl/ssl.h:
/usr/ssl/include /usr/local/ssl/include /usr/include /usr/include/ssl /opt/ssl/include /opt/openssl/include /usr/local/ssl/include /usr/local/include

and these directories for libssl.a:
/usr/ssl/lib /usr/local/ssl/lib /usr/lib/openssl /usr/lib /opt/ssl/lib /opt/openssl/lib /usr/local/lib/

If Mysql doesn't find OpenSSL in one of these directories, it won't compile with support for OpenSSL, but if you don't notice the error message, you won't find out until you've finished compiling and start up your server. Compiling mysql with the option --with-openssl=/path/to/openssl fails, because the configure script only handles the values --with-openssl[=yes] or --with-openssl=no and not a path, so the behavior is unspecified and it does nothing (this probably should be submitted as a bug report). If you have OpenSSL installed anywhere else, you will have to edit the file named configure in the source directory. It searches for OpenSSL starting at line 20863 (in version 4.0.12) -- just add the path to where you have OpenSSL installed (line 20879 for the include directory and line 20887 for the lib directory).

3) Start compiling Mysql with the options --with-vio and --with-openssl and any other options you want. For more information on this step and compiler-specific options refer to the Mysql documentation:
http://www.mysql.com/doc/en/Installing_source.html
http://www.mysql.com/doc/en/configure_options.html
http://www.mysql.com/doc/en/MySQL_binaries.html

4) Under Solaris 2.8, after running configure you have to remove all references to the crypt library if you want OpenSSL to work, because crypt.h from the crypt library and des_old.h from the OpenSSL library both try to define the same variable name. Disabling crypt won't affect your installation; you don't need it since you have OpenSSL. To do this, edit config.status and remove -lcrypt (for my install, this was on line 484). -lcrypto is different, that's part of the OpenSSL library, leave that. Also, in config.h you will need to make 3 changes (again, for my install these were on lines 151, 320, and 434 -- do a search for the string CRYPT to find them in your file):
anywhere you see something like
#define HAVE_CRYPT 1
change it to
/* #undef HAVE_CRYPT */
for HAVE_CRYPT, HAVE_CRYPT_H, and HAVE_LIBCRYPT.

5) You should now be able to run make and make install to finish your installation. You can then follow the instructions to create your own certificates, or use existing certificates by giving the following options either on the command line, or in a configuration-file (recommended):
ssl-ca = /path/to/cacert.pem
ssl-cert = /path/to/my-cert.pem
ssl-key = /path/to/my-key.pem
These options are valid for both the client and server.


As for using certificates to replace passwords, yes, it can be done. Following the instructions in the Mysql documentation for GRANT options, do something like the following:
GRANT SELECT, INSERT, UPDATE ON database.* TO new_user@'hostname' REQUIRE X509;
This user would then be able to log in using only the certificate and would not require a password. However, I would strongly suggest using REQUIRE SUBJECT "..." AND ISSUER "..." and not just X509 as REQUIRE X509 would allow anyone with a valid certificate to log in as that user, without having to give a password. If you choose to do this, make sure your REQUIRE is specific enough that no one else could spoof the certificate (i.e., specifying ISSUER alone would allow anyone with a certificate issued by your same issuer to connect, specifying SUBJECT alone would allow anyone to connect regardless of who issued the certificate). You will still have to embed the paths to your certificate and key, though, or else give them in a config-file just as you would a username/password combination. Your config-file can be just as secure as using a certificate, and your certificate can be as insecure as using a config-file if you're not careful with permissions.

After compiling Mysql (v4.0.12) with OpenSSL support and running a server under Solaris 2.8 and setting up an account using REQUIRE SSL, the only way I have found that the connection is accepted is by specifying all 3 ssl parameters: --ssl-ca=cacert.pem, --ssl-cert=client.cert, and --ssl-key=client.key. Just using the option --ssl from the command-line gives connection refused.

MySql and SSL will not work with JSSE 1.0.3 and a JRE less than 1.4 due to an TLSv1/SSLv2 handshake problem (Which I'm working on right now, and will post if I succeed). MySql SSL should support other protocols other than TLSv1 during handshaking to give (slightly) older api's a chance at connecting. Most people WILL be happy to have a light encryption on traffic for simple privacy reasons, and do not need or want Fort knox (If someone want's it bad enough they'll get it however much effort is put in). Why not put in a simple Diffie Hellman keyswap?

To compile with SSL support on OpenBSD (3.3, in my case), you must patch the following:

[sql/item_strfunc.cc]
26a27
> #include <unistd.h>

[sql/mysql_priv.h]
460a461
> #include <openssl/des.h>

I used the following configure, it's a hybrid between the official 3.x port and my own options. Works great so far (raid optional):

CC=gcc CFLAGS="-felide-constructors -fno-exceptions \
-fno-rtti" ./configure --enable-static \
--localstatedir=/var/mysql --with-libwrap=/usr \
--with-pthread --with-raid --with-mysqld-user=mysql \
--with-unix-socket-path=/var/run/mysql/mysql.sock \
--without-bench --without-debug --without-docs \
--without-readline --with-vio --with-openssl

For windows ssl download the windows source. On each project define HAVE_OPENSSL and HAVE_VIO. in the lib_release directory input the dll and lib of openssl ("libeay32.lib, ssleay32.lib"). in the include path input a directory openssl with the *.h files produced by openssl compilation at the inc32 folder.in the project mysqld add the file des_key_file.cpp (i do not know why they did not put it in the first place) and compile all the projects.

Apparently there is a licensing issue preventing distribution of SSL-enabled binaries for Windows.

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=bbvdsu%241il%241%40FreeBSD.csie.NCTU.edu.tw

Let's hope this gets resolved.

NO NEED TO RECOMPILE (or hardly any need to)

For those of us who would prefer to use the downloadable binary distributions, (which do not have SSL support) I wanted to share a work around that allows SSL connectivity with most of the features of integrated SSL support.

Basically, stunnel is used to encrypt the connection, but there are some changes to how it is set up, so that MySQL can tell distinguish SSL users, allowing emulation of the REQUIRE SSL feature.

I am not sure what features of integrated SSL support are not addressed in this workaround, but for me, the ability to require SSL connections of remote users, and being able to prevent local users (including root) from connecting remotely seemed to be all I needed. (Comments welcome on this)

STEP 1: If your machine does not already have stunnel, it is available here: http://www.stunnel.org. It is also available as an RPM from many places if you are running Linux.

STEP 2: You need to find or assign an IP to the machine that does not have port 3306 (or wherever your MySQL server is) available to the network. This is easy if you are behind a firewall, but if you are on a local LAN, you can always put a second NIC in your machine and leave it disconnected, if no more elegant solution can be found.

The only limit I found is that the IP you choose CANNOT be the main IP if there is more than one, or a gateway IP if the machine is a router.

For the rest of this tip, I will use 10.0.0.55 as the example IP.

STEP 3: Stunnel.org has a nice example page of how to connect to a MySQL server using SSL, and it is here: http://www.stunnel.org/examples/mysql.html

If you look at this example page, the line describing the server code:

./stunnel -P/tmp/ -p stunnel.pem -d 3307 -r localhost:3306

Should be changed to:

./stunnel -P/tmp/ -p stunnel.pem -d 3307 -r 10.0.0.55:3306

NOTE: Stunnel 4.x is VERY different than the 3.x version, which is used in the example above. If you have 4.x Stunnel, then instead, add the following to your stunnel.conf file:

[mysqls]
accept = 3307 # (or whatever port you want for secure MySQL)
connect =10.0.0.55:3306


STEP 4: Now, all you have to do when creating users is to use 10.0.0.55 as the host name, and it is equivalent to requiring SSL connectivity.

Examples:
User Joe@10.0.0.55 will need to use SSL to connect remotely and will not be able to log on locally.

User Jane@localhost will not be able to log on remotely.

If you wanted Joe to connect locally, just create Joe@localhost with the same password as Joe@10.0.055. Similarly, creating Jane@10.0.0.55 will give Jane remote SSL access.

Finally, our machine uses Cpanel virtual hosting software, which does automatic updates to MySQL. (Yet another reason for not building your own executable!)

Regardless, if you are using Cpanel, the stunnel.conf file is in a non-standard place. It is in one of the directories below /usr/local/cpanel/etc/stunnel/, usually the default directory.

The problem: Dataconnections between MySQL-Server and the Client are unencrypted. This means that authentication, the request and the output are transmitted in plain text over the net. To use SSL in MySQL the option must compiled within. In most distributions it isn't.
Here's a small workaround to use a secure connection without recompiling the MySQL-Server:

First install stunnel on the server & on the client. www.stunnel.org

Generate a SSL-Certificate (On Server & Client):
umask 077; \
/usr/bin/openssl req -new -x509 -days 3650 -nodes -config \
/usr/share/doc/packages/stunnel/stunnel.cnf \
-out /etc/stunnel/stunnel.pem -keyout /etc/stunnel/stunnel.pem

----------------------------------------------------------
----------------------------------------------------------
the file "/usr/share/doc/packages/stunnel/stunnel.cnf"
contains only openSSL-Data like this:

# create RSA certs - Server
RANDFILE = stunnel.rnd
[ req ]
default_bits = 1024
encrypt_key = yes
distinguished_name = req_dn
x509_extensions = cert_type
[ req_dn ]
countryName = Country Name (2 letter code)
countryName_default = PL
countryName_min = 2
countryName_max = 2
stateOrProvinceName = State or Province Name(full name)
stateOrProvinceName_default = Some-State
localityName = Locality Name (eg, city)
0.organizationName = Organization Name (eg, company)
0.organizationName_default = Stunnel Developers Ltd
organizationalUnitName = Organizational Unit Name (eg, section)
#organizationalUnitName_default =
0.commonName = Common Name (FQDN of your server)
0.commonName_default = localhost
# To create a certificate for more than one name uncomment:
# 1.commonName = DNS alias of your server
# 2.commonName = DNS alias of your server
# ...
# See http://home.netscape.com/eng/security/ssl_2.0_certificate.html
# to see how Netscape understands commonName.
[ cert_type ]
nsCertType = server
----------------------------------------------------------
----------------------------------------------------------






In /etc/stunnel/stunnel.conf (on Server runing MySQL) add or uncomment these lines:
#
# Authentication stuff
#
verify = 2
CAfile = /etc/stunnel/certs.pem
cert = /etc/stunnel/stunnel.pem
[mysqls]
accept = 3307
connect = localhost:3306
client = no


Create /etc/stunnel/certs.pem with owner and permission "root.root 600"


On the client copy from the file /etc/stunnel/stunnel.pem the section:
-----BEGIN CERTIFICATE-----
f05yAI/lCUxXYdOMIICOTCCAaKg
MBEGA1UECBMKf05yAI/lCUxXYdO
f05yAI/lCUxXYdOIEx0ZDESMBAG
MTA5MzA1NFowf05yAI/lCUxXYdO
f05yAI/lCUxXYdOBgNVBAoTFlN0
dDCBnzANBgkqf05yAI/lCUxXYdO
f05yAI/lCUxXYdOexW1uigvYk7f
bBDRCEC39YIQf05yAI/lCUxXYdO
f05yAI/lCUxXYdOUjOPdHWz5CB2
SIb3DQEBBAUAf05yAI/lCUxXYdO
f05yAI/lCUxXYdOlTG9m64pAyD6
U44OtGGV+cwcf05yAI/lCUxXYdO
-----END CERTIFICATE-----
and paste it on the Server in /etc/stunnel/certs.pem


On the client add or uncomment these lines in /etc/stunnel/stunnel.conf
#
# Authentication stuff
#
cert = /etc/stunnel/stunnel.pem
[mysqls]
accept = 3306
connect = IP_or_FQDN_of_SERVER:3307
client = yes



Start the service on both machines.

Connect to the MySQL-Server: mysql -u root -h 127.0.0.1 -p

Check the stream if its encrypted with:
tcpdump -l -i eth0 -w - src or dst port 3306 | strings
....thats it !!!


A.Mathibe, April,3rd 2004

Add your own comment.