Together with Pedro Pereira we decided to investigate how MySQL make secure connections with clients. This is the first milestone of our msc in Cryptography.
It was proposed that we investigate the internal authentication process that MySQL do using X.509 certificates format.
This post gives a short introduction to tools and methods we use, Public-key cryptography, Certificates, OpenSSL, MySQL and VirtualBox.
We use the VirtualBox to install mysql, to avoid installing it in our OS. So, all the commands showed here have to maked in this virtual machine.
Configuring VirtualBox
As we said before, we installed MySQL in a virtual machine, so we decided access the virtual machine by ssh and remote connections to
MySQL.
NAT vs Port forward
By default the network connection in VirtualBox is made by Network Address Translation (NAT), i.e. each package that is sent by the guest machine is modified so that it appears to come from the host machine. Thus it is very easy to guest machine to connect with the entire network (including Internet), but never could start a connection from host machine to guest machine, since the interface of the guest
is hidden by the host machine.
To resolve this issue, and can access from host machine to the guest by ssh and the MySQL we decided to use the Port forward system that VirtualBox offers.
We have the guest machine running a ssh service accepting connections on port 22. Our goal is to make each package reaches a certain TCP port (eg 2222) on the host machine to to be redirected to TCP port 22 in guest machine.
The command that allows us to do this in VirtualBox is: VBoxManage. We make this with following commands, in which would be the name we gave to our guest machine:
shell> VBoxManage setextradata
"VBoxInternal/Devices/pcnet/0/LUN#0/Config/ssh/HostPort" 2222
shell> VBoxManage setextradata
"VBoxInternal/Devices/pcnet/0/LUN#0/Config/ssh/GuestPort" 22
shell> VBoxManage setextradata
"VBoxInternal/Devices/pcnet/0/LUN#0/Config/ssh/Protocol" TCP
From now every time we want to connect by ssh to the guest machine only run the following command in a shell:
shell> ssh -l -p 2222 localhost
Similarly the same happens with MySQL connections. We want all packages targeted to port 3333 on host machine is redirected to the port 3306 of guest machine . So being able to access the MySQL that is installed on the guest machine:
shell> VBoxManage setextradata
"VBoxInternal/Devices/pcnet/0/LUN#0/Config/mysql/HostPort" 3333
shell> VBoxManage setextradata
"VBoxInternal/Devices/pcnet/0/LUN#0/Config/mysql/GuestPort" 3306
shell> VBoxManage setextradata
"VBoxInternal/Devices/pcnet/0/LUN#0/Config/mysql/Protocol" TCP
Public-key cryptography
The cryptography asymmetric system can be explained with the following analogy: a mailbox,
is accessible to the public through its address (public key), then anyone can send a
message for this box. Just who has the key to the box is the only who can read the messages (private key).
We only guarantee that any person can send encrypted messages to the owner of the mailbox. However we could not guarantee the identity of who recieve the message (the private key may have been compromised). We also can not guarantee the identity of the person who sent (Later we will see that the use of certificates resolve this problem).
For Bob send a message to Alice, he uses her public key to encrypt the message. This cryptogram is sent to Alice that decrypt with her private key.
Later, the Alice responds to Bob, encrypting the message with his public key.
In a different kind of use of public key can have a scenario in which Alice communicate with Bob, encrypts the message with her private key (digital signature) and encrypt it again with Bob’s public key. Thus, on the other side of the channel, Bob uses his private key and
subsequent Alice’s public key, thus obtaining the original clear text.
The cryptogram generated is an example of a symmetric cipher and is more robust than the previous scenario where only one key is used each time. Imagine now a case in which a third malicious actor, publish your public key and claim to be Alice. Thus it is likely someone who cheat and can read some of the messages intended for Alice. Although we have secure connections/strongly encrypted messages, there is still no guarantee the identity of any of the actors in the process of communication. In this context, we use X.509 certificates
Certificates
An X.509 certificate of public key is an electronic document that can be compared to Identity card. However, instead of attaching a photo to the name of the person, it combines the key to their own (identity). But the certificate may not be issued by the concerned stakeholders because any one could falsify one certificate and claiming a false identity.
There we need that there is an entity (Certification Authority) trusted by both sides to ensure the identities of both. The CA ‘s sign (encrypt) the certificates with theirs private keys allowing validity to who decrypt the signature with their public key CA’s.
But if go to the top of hierarchy in the chain of certificates we will face a problem: who signs the CA certificate? The bottom line is we have always to belive in a entity, now users no longer communicate among themselves but the CA’s do that. The CA ‘s can sign their certificates, an example of a self-signed certificate that normally is a root Certificate.
Installing MySQL
The process we used here, was tested in a machine with Ubuntu 7.10 and 8.04:
shell> apt -get install mysql-server-5.0 mysql-client-5.0
The OpenSSL came compiled by default in the .deb package, but if we have to compile it we only would have to specify the following in the process of setting up the Makefile:
shell> ./ configure --with - openssl
Now, that we have instaled MySQL, we can go into it typing:
shell> mysql -h SERVER -u root -p
This way we got an uncrypted connection to the server, to obtain an encrypted you must add the option –ssl. This option when introduced on the server side means that the server will allow secure connections, in client-side allows to connect to the server via a secure connection. But this option alone is not enough, it is also necessary to introduce –ssl-ca and possibly the –ssl-cert and –ssl-key.
We have to enter with this flags if we not set the appropriate paths of certificates and their keys in the file /etc/mysql/my.conf.
But we’ll see below in more detail how to use these options, now just want to add a user “user” with the password “passwd” in the database “dBASE” located in “servidordeteste.com” demanding an SSL connection:
mysql > GRANT ALL PRIVILEGES ON dbase .* TO ’user’@’servidordeteste.com’
IDENTIFIED BY ’passwd ’
REQUIRE SUBJECT ’/CN=user ’
AND ISSUER ’/CN=CA ’
AND CIPHER ’EDH-RSA-DES-CBC3-SHA ’;
The CIPHER part means the ciphers used for encryption and you should pick up the ciphers stronger because MySQL can use weaker ciphers.
Now, we get out of the MySQL administration program to demonstrate how to generate keys and certificates.
Generate certificates
We will demonstrate how to create a fictitious CA, generate certificates of potential clients/servers and pointed through the private key of CA, just like real in the process. First we create a tree of folders to contain the structuring of certificates:
shell> mkdir -m 755
~/teste/CA
~/teste/CA/private
~/teste/CA/certs
~/teste/CA/newcerts
~/teste/CA/crl
The CA folder represents the folder of our certification authority, the private folder will hold private keys; certs folder will have the clients/servers certificates, the newcerts is a required folder for the OpenSSL to store decrypted certificates, whose names will be their serial numbers; finally crl folder will keep the list of revoked certificates.
Now copy the default OpenSSL configuration file to our CA folder:
shell> cp /etc/ssl/openssl .cnf ~/teste/CA/myopenssl .cnf
and we change permission, allowing only the user can read and write:
shell> chmod 600 ~/teste/CA/myopenssl .cnf
We need to create two files, one will be the OpenSSl database:
shell> touch ~/teste/CA/index.txt
and the other, containing the serial numbers of each certificate. We don’t have anyone, so we put “01” in that file:
shell> echo '01' > ~/teste/CA/serial
Now run all commands in the folder ~/test/CA because is there we have the OpenSSL configuration file. The next step is to generate the self-signed CA certificate: generate the CA private key of 2048 bits (Today, less than 2048 bits is no longer considered completely safe).
shell> openssl genrsa -out private/ca-privkey.key 2048
if we want to check the contents of the key:
shell> openssl rsa -text -in private/ca-privkey.key
and if just generate a public key from private key:
shell> openssl rsa -pubout -in private/ca-privkey.key -out ca-publkey.key
Now we generate the certificate (valid for 365 days) and their public key and through private key we signed it:
shell> openssl req -config myopenssl.cnf -new -x509 -extensions v3_ca
-key private/ca-privkey.key -out certs/ca-cert.crt -days 365
Note that the “Common Name” (CN) is the identifier that distinguishes the entity/person therefore has to be well written. In this case
CN = CA.
Now, if we want to verify the content of the certificate:
shell> openssl x509 -in certs/ca-cert.crt -noout -text
The private key must be stored under very strong permissions, only the root should be able to read it:
shell> chmod 400 private/ca-privkey.key
Then we change the OpenSSL configuration file (myopenssl.cnf) so that we have this information:
[ CA_default ] dir = . certs = $dir/certs crl_dir = $dir/crl database = $dir/index.txt # unique_subject = no new_certs_dir = $dir/newcerts certificate = $dir/certs/myca.crt serial = $dir/serial # crlnumber = $dir/crlnumber crl = $dir/crl.pem private_key = $dir/private/myca.key RANDFILE = $dir/private/.rand x509_extensions = usr_cert
Now we can produce the client/server certificate:
we generate the private key and certificate request with the public key:
shell> openssl req -config myopenssl.cnf -new -newkey rsa:2048
-nodes -keyout private/privkey.key -out cert-req.csr
Then we change the permissions of the new key as before. Note that the “Common Name” (CN) is the identifier that distinguishes a person/entity therefore has to be well written.
In this case CN = user.
we can verify the content of the request:
shell> openssl req -in cert-req.csr -noout -text
And with this command we sign the certificate:
shell> openssl ca -config myopenssl.cnf -cert certs/ca-cert.csr
-keyfile private/ca-privkey.key -out certs/cert.crt
-infiles cert-req.csr
This last command creates two additional files on certs folder. The cert.crt (signed certificate) and newcerts/01.pem (decrypted certificate). Naturally we would have to repeat the process for similar entity (client/server).
Right now we’re ready to connect with MySQL.
Connecting to MySQL
The cryptographic methods discussed in the first part of this port are situated in a context of communication. However there are many situations where we need to ensure a secure connection. One of those situations: you may want to connect to a remote database.
When accessing to a remote database anyone with access to the same network can inspect all traffic or worse, change it while passing between the client and server. We can however, use the option –compress on the client side to compress the traffic but still unencrypted and unsafe.
But as we said earlier, MySQL supports encrypted connections through the use of libraries of OpenSSL. Here we can see the MySQL Makefile’s SSL section:
Ln 318: openssl_includes = @openssl_includes@ Ln 319: openssl_libs = @openssl_libs@
So any kind of encryption/maintenance of certificates in MySQL is controlled by the functions that are part of the OpenSSL API.
Configuring SSL in MySQL
To ensure the authenticity can be assured we add the following lines to /etc/mysql/my.conf:
[ client ] ssl -ca=/home/user/teste/certs/ca-cert.crt ssl -cert =/home/user/teste/certs/cert.crt #(client) ssl -key =/home/user/teste/private/privkey.key #(client) [ mysqld ] ssl -ca=/home/user/teste/certs/ca-cert.crt ssl -cert =/home/user/teste/certs/cert.crt #(server) ssl -key =/home/user/teste/private/privkey.key #(server)
Consider the initial situation in the role of client, we can access to the server, but now in a secure way. Then:
shell> mysql -h SERVER -u USER -p --ssl
If everything went well we now can connect via a secure connection and authenticated using X.509 certificates.
mysql > show variables like '%ssl%'; +---------------+----------------------------------------+ | Variable_name | Value | +---------------+----------------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /home/user/test/certs/ca-cert.crt | | ssl_capath | | | ssl_cert | /home/user/test/certs/server-cert.crt | | ssl_cipher | | | ssl_key | /home/user/test/private/server-key.key | +---------------+----------------------------------------+ 7 rows in set (0.11 sec)
As a final note, of this part, we mention that the whole process of this part refers to only one user, to another we must repeat everything, of course.
SSL Program
As extra, we decide to implement a simple program that use SSL connections in JAVA.
We found that the MySQL Connector/J supports some properties that are useful to establish SSL connections.
The property useSSL tells the server that we use a secure connection.
In this case the user ssluser was created with the command GRANT … REQUIRE SSL, ensuring that
can only connect by SSL.
import com.mysql.jdbc.*;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
public class Main {
public static void main(String[] args) {
Connection conn = null;
try {
String userName = "ssluser";
String password = "password";
Class.forName("com.mysql.jdbc.Driver").newInstance();
String url = "jdbc:mysql://localhost:3333/mysql" //port 3306 of guest machine
+ "?useSSL=true";
conn = (Connection) DriverManager.getConnection(url, userName, password);
Statement stmt = (Statement) conn.createStatement();
ResultSet rs = stmt.executeQuery("select User,Host,ssl_type from mysql.user;");
while (rs.next()) {
System.out.print(rs.getString(1) + " ");
System.out.print(rs.getString(2) + " ");
System.out.println(rs.getString(3) + " ");
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("SQLException: " + e.getMessage());
System.out.println("SQLState: " + e.getSQLState());
System.out.println("VendorError: " + e.getErrorCode());
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
System.out.println("Database connection terminated");
} catch (Exception e) { }
}
}
}
}
This simple program run well, it print the above table to stdout.
We wanted to implement the same application using certificates, but not, we have errors for which no solution yet found. The documentation, unfortunately not worked for us.
Anyway, as a great experience find everything we describe in this post. We learned a lot about cryptography …
Ulisses Costa Blog 