4.3.9.2 Requirements

To get secure connections to work with MySQL you must do the following:

1. Install the OpenSSL library. We have tested MySQL with OpenSSL 0.9.6. http://www.openssl.org/.
2. Configure MySQL with --with-vio --with-openssl.
3. If you are using an old MySQL installation, you have to update your mysql.user table with some new SSL-related columns. You can do this by running the mysql_fix_privilege_tables.sh script. This is necessary if your grant tables date from a version prior to MySQL 4.0.0.
4. You can check if a running mysqld server supports OpenSSL by examining if SHOW VARIABLES LIKE 'have_openssl' returns YES.

User Comments

How about something here about changing a user's passwd with mysqladmin ?
I hate the idea of passwords appearing on screen. I would expect to be able to
create a user and then get them to set their passwd, possibly using my
terminal. They don't want to type in their passwd so I can see it and I certainly
don't want them to !

Hmm, this is not exactly an OpenBSD bug, it is more a portabitility problem. Nontheless, the solution is to compile mysql with pth-threads. To do so:

cd /usr/ports/databases/mysql/;
make FLAVOR=pth install

Surely it would be quite handy to give examples
of how to give privilidges by database, table,
etc. The above examples only illustrate how to
give someone control over all my tables. Now why
would I want to do that more than once or twice?
I cannot work out how to grant a user permission
to look at only certain databases!

So now after trial and error (AGAIN!) I have
found that to give select and insert
privilelidges to someone for a database you use
the following:
GRANT SELECT,INSERT ON database.* TO user@host

The * refers to all tables on the named
database. If you want to limit things to a
specific table on the database I think you just
put the table name in place of the *. But I
think you may need to do the command with the *
first but with USAGE instead of SELECT,INSERT and
THEN give the SELECT,INSERT priviliges for the
table. I am confused about this, and sorry I
can't make myself clearer!

You can configure a C API client to use SSL.
Just add CLIENT_SSL (no quotes) as the
db_flags argument to mysql_real_connect().
Recompile, and voila!

As far as I can tell, you can't use the mysql
Monitor to do SSL (but you may know better!). I
also don't know how to make a secure
connection from PHP or Perl.

For all those who would like to use this on windows... :)

Here's how to do it in Visual Studio:

Download the source, and load it in VS. Go to the Properties of mysqld, and where you can add Define statements, add HAVE_VIO and HAVE_OPENSSL (not WITH_OPENSSL). then compile the mysqld project, and you're done!

Guys, this isn't that hard. Once you've configured mysql --with-vio and --with-openssl, follow the instructions in section 4.3.9.3, "Setting Up SSL Certificates for MySQL". Using this as the example, I started my mysql daemon with, among other things:

--ssl-ca=$OPENSSL/cacert.pem --ssl-key=$OPENSSL/server-key.pem --ssl-cert=$OPENSSL/server-cert.pem

Where $OPENSSL is the path you defined for your openssl stuff (the example uses ' DIR=`pwd`/openssl ').

To connect with an ssl-enabled client:

/usr/local/mysql-4.0.12/bin/mysql --ssl-ca=$OPENSSL/cacert.pem --ssl-key=$OPENSSL/client-key.pem --ssl-cert=$OPENSSL/client-cert.pem -p -h<hostname>

And lastly, from using 'perldoc DBD::mysql' for the latest version of the DBI/DBD installation yields this line for connecting using the Perl DBI:

$dbh = DBI->connect("DBI:mysql:test:localhost;mysql_ssl=1;mysql_ssl_client_key=$openssl/client-key.pem;mysql_ssl_client_cert=$openssl/client-cert.pem;mysql_ssl_ca_file=$openssl/cacert.pem", $user, $pass)

Obviously, to make the above cleaner, just make a string $dsn, and then use *that* in the connect line. And to verify all of this works, just run tcpdump on the server, extracting packets with dst 3306, and display them in ASCII. Turn ssl on to see the information encrypted, turn ssl off to see it in the clear.

Add your own comment.