Practical Uses of CryoKey with MySQL
In my previous post, you learned how to set up a MySQL server to recognize CryoKey credentials. You also learned how to set up the MySQL command line tools to automatically present the CryoKey credentials when establishing database connections.
In practice, most people don't use command line tools to access databases. Therefore, I wanted to demonstrate how you would use CryoKey credentials to access MySQL in more typical database usage scenarios:
MySQL Workbench, a popular MySQL database management tool
Perl DBI with MySQL DBD
PHP with the MySQL Improved ('mysqli') API
PHP with ADODB
--- MySQL Workbench
MySQL Workbench is a powerful visual interface for the MySQL database system. You can use it to set up the MySQL SSL configuration to support CryoKey. However, as of right now (Workbench v5.2.37), you can't actually set up certificate-based database user accounts from the user interface. Therefore, you'll still need to use the 'GRANT' command with the 'REQUIRE SUBJECT' directive to manage access.
Either create or open a server instance. You will see the server dashboard:
To manage SSL configuration, go to the Security tab under Configuration->Options File:
To check the configuration (or availability) of SSL, look for the 'have_ssl' value of a server's System Variables tab under Management->Status and System Variables:
After you set up the server, you need to set up certificate-based accounts. Look at my previous post for details on how to get a user's CryoKey subject string and associate it with the account. To recap, first acquire CryoKey credentials. Then get the user's CryoKey subject string:
# openssl pkcs12 -in cryokey.pfx -clcerts -nokeys -out my.crt # chmod 0444 my.crt # openssl pkcs12 -in cryokey.pfx -nocerts -nodes | openssl rsa -out my.key # chmod 0400 my.key # openssl x509 -in my.crt -noout -subject | sed 's/^subject=\s*//'
Then, connect to the database as an administrator and link the user's CryoKey subject string in a 'GRANT':
mysql> GRANT ALL PRIVILIGES ON [database].* TO '[name]'@'[host]' REQUIRE SUBJECT '[CryoKey subject string]';
When creating or editing connections to databases in MySQL Workbench, you can specify the user's CryoKey credentials from the Advanced tab in the connection settings:
Note that shell variable expansion is not available. Therefore, use absolute paths for the certificate and key files, because the working directory may vary (under a typical Kubuntu 12.10 installation, the working directory would be 'Documents' in your home directory). If you connect using 'TCP/IP over SSH', the certificate and key files are relative to the user's account on the SSH server.
Click 'Test Connection' to see if you set up the connection properly. If the test succeeds, click 'OK' to apply the changes.
--- Perl DBI
Using CryoKey credentials to access MySQL databases is fairly simple under Perl DBI. Perl's MySQL DBD allows you to specify your CryoKey certificate and key files in the data source name. Just remember that the paths are relative to the current working directory.
This sample script opens a connection to a database called 'testdb' on a local MySQL server. The user, 'dbuser', will try to connect using the certificate from 'my.crt' and the key from 'my.key' located in the current working directory.
#!/usr/bin/perl use DBI; use strict; use warnings; my $dbname = "testdb"; my $hostname = "localhost"; my $username = "dbuser"; my $key = "my.key"; my $certificate = "my.crt"; my $dsn = "DBI:mysql:database=${dbname};host=${hostname};mysql_ssl=1;mysql_ssl_client_key=${key};mysql_ssl_client_cert=${certificate}"; my $db = DBI->connect($dsn, $username) or die "Can't connect: $DBI::errstr\n"; my $results = $db->selectall_arrayref("SHOW STATUS LIKE 'ssl_cipher'"); foreach my $i (@$results) { foreach my $j (@$i) { print "[$j] "; } print "\n"; } $db->disconnect();
Running it should output something like:
[Ssl_cipher] [DHE-RSA-AES256-SHA]
If the value is '[]' (empty), then something went wrong; make sure the certificate and key files are valid, and make sure the MySQL server has been set up for SSL.
--- PHP MySQL Improved (mysqli_*)
Direct certificate support is available to the 'mysqli' API, but not the older 'mysql' API. To specify a certificate, you use mysqli_init() to create a connection object, ssl_set() to specify the certificate and key, then real_connect() to open the connection. Afterwards, use the connection normally.
<html> <head> <title>CryoKey MySQL</title> </head> <body> <?php $dbname = 'testdb'; $hostname = 'localhost'; $username = 'dbuser'; $db = mysqli_init(); $db->ssl_set('my.key', 'my.crt', NULL, NULL, NULL); $db->real_connect($hostname, $username, NULL, $dbname); $result = $db->query("SHOW STATUS LIKE 'ssl_cipher'"); echo "<table border='1'>"; while ($row = $result->fetch_row()) { echo "<tr>"; foreach ($row as $cell) { echo "<td>{$cell}</td>"; } echo "</tr>"; } echo "</table>"; $db->close(); ?> </body> </html>
Running the script should output something like:
If the 'Ssl_cipher' field is empty, then something went wrong; make sure the certificate and key files are valid, and make sure the MySQL server has been set up for SSL.
--- PHP ADODB
The current version of ADODB doesn't let users specify SSL connection parameters. However, you can work around this limitation by specifying a certificate and key in the system options file (normally '/etc/mysql/my.cnf' as of MySQL v5.1.15). Simply add the SSL connection parameters to the '[client]' section:
... [client] ... ssl-cert=/etc/ssl/certs/mysql.pem ssl-key=/etc/ssl/private/mysql.key ...
Remember that, if you don't provide absolute paths, the files will be relative to the script's working directory. Make sure to set the certificate and key file permissions properly, or else everyone will have access to the credentials! In our example (and typical Linux distributions), system keys go in '/etc/ssl/private' with mode 0440 and group 'ssl-cert'. We also put system certificates in '/etc/ssl/certs' with mode 0444. Any users that need access to the system keys should be in the 'ssl-cert' group.
Then, use ADODB to connect using the 'mysqli' driver. You will need to set the 'MYSQL_CLIENT_SSL' client flag:
<html> <head> <title>CryoKey MySQL</title> </head> <body> <?php include_once("adodb5/adodb.inc.php"); $dbname = 'testdb'; $hostname = 'localhost'; $username = 'dbuser'; $db =& ADONewConnection("mysqli"); $db->clientFlags = MYSQL_CLIENT_SSL; $db->Connect($hostname, $username, NULL, $dbname); $db->SetFetchMode(ADODB_FETCH_ASSOC); $results = $db->Execute("SHOW STATUS LIKE 'ssl_cipher'"); if ($db->ErrorNo() == 0) { while (!$results->EOF) { $row = $results->FetchRow(); print_r($row); $results->MoveNext(); } $results->Close(); } ?> </body> </html>
If successful, you'll see a line that looks like:
Array ( [Variable_name] => Ssl_cipher [Value] => DHE-RSA-AES256-SHA )
If the value is empty, then something went wrong; make sure the certificate and key files are valid, and make sure the MySQL server has been set up for SSL.
This workaround has its limits. The files are usually relative to the current working directory. Therefore, the certificate and key must be in the same directory as the running PHP script, which leaves the key vulnerable. You can always use absolute paths to put the files somewhere less accessible. Or wait for ADODB to add more thorough MySQL SSL connection support.
MySQL's certificate support is very basic right now. While it certainly has room for improvement, most users generally don't use SSL connections, preferring to stick with username/password/host access controls. Right now, SSL connections are mainly used for the secure communications, especially during replication. Therefore, development on such support isn't a high priority, but the support should improve if more people start using MySQL's SSL capabilities.
Still, CryoKey credentials can work with MySQL despite the current state of support. They're especially useful for scripted database access. You may be asking, "Why bother?" Well, some people may want the added assurance of certificate based authentication, especially when combined with normal passwords. Besides, you may want to connect using an encrypted connection, and authenticating with CryoKey credentials isn't such a big leap if you're already connecting over SSL anyway.
If you do use CryoKey for database authentication, consider using a system identity (as opposed to a personal identity). For instance, you could generate credentials for [email protected] if you can get mail for that account (maybe through a forwarding mechanism).
The same credentials used in MySQL can also be used in other ways for typical Linux tools. In future posts, I'll describe some other back-end uses for CryoKey credentials, including the possibility of automating a verification flow for system accounts!








