4.3.9.1 Basics

Beginning with version 4.0.0, MySQL has support for SSL encrypted connections. To understand how MySQL uses SSL, it's necessary to explain some basic SSL and X509 concepts. People who are already familiar with them can skip this part.

By default, MySQL uses unencrypted connections between the client and the server. This means that someone could watch all your traffic and look at the data being sent or received. They could even change the data while it is in transit between client and server. Sometimes you need to move information over public networks in a secure fashion; in such cases, using an unencrypted connection is unacceptable.

SSL is a protocol that uses different encryption algorithms to ensure that data received over a public network can be trusted. It has mechanisms to detect any change, loss or replay of data. SSL also incorporates algorithms to recognise and provide identity verification using the X509 standard.

Encryption is the way to make any kind of data unreadable. In fact, today's practice requires many additional security elements from encryption algorithms. They should resist many kind of known attacks like just messing with the order of encrypted messages or replaying data twice.

X509 is a standard that makes it possible to identify someone on the Internet. It is most commonly used in e-commerce applications. In basic terms, there should be some company (called a ``Certificate Authority'') that assigns electronic certificates to anyone who needs them. Certificates rely on asymmetric encryption algorithms that have two encryption keys (a public key and a secret key). A certificate owner can prove his identity by showing his certificate to other party. A certificate consists of its owner's public key. Any data encrypted with this public key can be decrypted only using the corresponding secret key, which is held by the owner of the certificate.

MySQL doesn't use encrypted connections by default, because doing so would make the client/server protocol much slower. Any kind of additional functionality requires the computer to do additional work and encrypting data is a CPU-intensive operation that requires time and can delay MySQL main tasks. By default MySQL is tuned to be fast as possible.

If you need more information about SSL, X509, or encryption, you should use your favourite Internet search engine and search for keywords in which you are interested.

User Comments

I would hope the openssl will be compiled into the
binary releases in future as it is a very desirable
feature when connecting to a MySQL server via the
internet is necessary. I don't see how it will slow
down the normal connections because ssl will not be
activated unless the client connects by specifying the
CLIENT_SSL flag.

A few gripes here. first off, i agree with the comment
posted above. they should have the ssl enabled
binary able to be downloaded. at least have the
option of downloading one with ssl enabled. Also,
and most important: I cannot get mysql to build on
redhat8.0. i can install the rpm fine, but the rpm
doesn't have ssl options. i have downgraded my gcc
as i saw somewhere on this site, and i still get the
same error:

libmysql.c: In function `mysql_real_connect':
libmysql.c:1673: warning: passing arg 5 of
`gethostbyname_r' from incompatible pointer type
libmysql.c:1673: too few arguments to function
`gethostbyname_r'
libmysql.c:1673: warning: assignment makes
pointer from integer without a cast
make[2]: *** [libmysql.lo] Error 1
make[2]: Leaving directory
`/home/admins/shad0/build/mysql-4.0.4-
beta/libmysql'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory
`/home/admins/shad0/build/mysql-4.0.4-beta'
make: *** [all-recursive-am] Error 2
[root@lachesis mysql-4.0.4-beta]#

did that with 3.23.53 as well. this is quite annoying,
and i have spent the entire day trying to get this to
work as stated.

To fix that Problem just install: gcc-c++-3.2-7 on your RedHat8.0 and i will work fine! dependencie rpm is libstdc++-devel

nice day

I also had to install libtermcap-devel on RH8, which is not installed by default.

I had the same problem with SuSe 8.1 distribution.
examined a whole day the source code of mysql - was a pain in the neck :-)

They should mention that the gcc++ compiler is needed - the error message is a bit confusing.

Manfred

I would very much like to see more explanation on:

1. What the steps are to install SSL in the server machine. Right now all I see are vague, hand-waving explanations. Once OpenSSL is installed, is there any configuration of that application with MySQL? If have_openssl is FALSE, what can you do?

2. Windows Support. There's nothing right now that even MENTIONS Windows. Is that b/c the Windows binaries do not support SSL? Is strongly suspect so, as I've followed the directions above to the letter and cannot enable SSL on my machine. It'd have been nice if there was some mention of that.

3. Open vs. Secure sections of each message sent. Given that the USER table delineates whether a particular userid can connect openly, or needs SSL, that suggests that the userid is sent openly, and only the password and subsequent requests/responses are encrypted. This is fairly important for those of us who are concerned with the userid's getting out.

Finally, I'd also love to see some sort of constraints on passwords. For example, requiring some sort of strength on a password, such as:

WEAK: pass
STRONGER: pass34
STRONGER: MyPass34
STRONG: Freddled!5^0@Gruntbuggedly

These are just my own examples: You'd need someone more qualified in security to truly identify the criteria that determines a strong password.

It might be implemented as a column in the User table, delineating that a user cannot connect if the password supplied does not meet some level of complexity.

It'd also be nice to mention whether the mysql client can actually connect securely, what would need to be done to enable it to, if anything could be done, and some other suggested method of administering the database if nothing could be done.

Any chance we could use existing ssh keys so we
don't have to embed passwords in scripts or in secret
files?

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.

Add your own comment.