I created a user via MySQL Workbench.
CREATE USER 'fba_user'@'%' IDENTIFIED BY 'my_Pa$$word';
CREATE USER 'fba_user'@'localhost' IDENTIFIED BY 'my_Pa$$word';
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON fallbackauthdb.* TO 'fba_user'@'%' IDENTIFIED BY 'my_Pa$$word' REQUIRE SSL;
GRANT ALL PRIVILEGES ON fallbackauthdb.* TO 'fba_user'@'localhost' IDENTIFIED BY 'my_Pa$$word' REQUIRE SSL;
FLUSH PRIVILEGES;
I configured the MySQL Server (5.5.34-0ubuntu0.13.04.1) to use SSL by generating the needed certificates and keys and configured them via /etc/mysql/my.cnf
If I try to connect via CLI on a client machine everything just works great.
user@ClientPC:~$ mysql -h mysql.example.com --port=3306 -v --ssl-ca=/home/user/demo/ca-cert.pem --ssl-cert=/home/user/demo/client-cert.pem --ssl-key=/home/user/demo/client-key.pem -u fba_user -p
Enter password: my_Pa$$word
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 99
Server version: 5.5.34-0ubuntu0.13.04.1 (Ubuntu)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Reading history-file /home/user/.mysql_history
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW STATUS LIKE "%SSL%";
--------------
SHOW STATUS LIKE "%SSL%"
--------------
+--------------------------------+------------------------+
| Variable_name | Value |
+--------------------------------+------------------------+
| Com_show_processlist | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | DHE-RSA-AES256-SHA |
| ... | ... |
+--------------------------------+------------------------+
But if I use PHP (PHP/5.4.9-4ubuntu2.3) with PDO to connect to the database, the connection is not working. I tried php5-mysql driver and I tried the php5-mysqlnd (Native Driver). Both do not change a thing.
<?php
error_reporting(E_ALL);
ini_set("display_errors", "1");
if (!defined('PDO::ATTR_DRIVER_NAME')) {
echo 'PDO unavailable';
}
elseif (defined('PDO::ATTR_DRIVER_NAME')) {
echo 'PDO available';
}
$pdo = new PDO('mysql:host=mysql.example.com;dbname=fallbackauthdb;charset=utf8', 'fba_user', 'my_Pa$$word', array(
PDO::MYSQL_ATTR_SSL_KEY => '/etc/mysql/client-key.pem',
PDO::MYSQL_ATTR_SSL_CERT => '/etc/mysql/client-cert.pem',
PDO::MYSQL_ATTR_SSL_CA => '/etc/mysql/ca-cert.pem'
));
foreach ($pdo->query('SHOW STATUS LIKE "%Ssl%"') as $row) {
print_r($row[0] ."=". $row[1] . PHP_EOL);
}
?>
Thats the error message I got:
PDO available
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000] [1045] Access denied for user 'fba_user'@'mysql.example.com' (using password: YES)' in /var/www/gfa/sql.php:17 Stack trace: #0 /var/www/gfa/sql.php(17): PDO->__construct('mysql:host=mysq...', 'fba_user', 'my_Pa$$...', Array) #1 {main} thrown in /var/www/gfa/sql.php on line 17
If I disable SSL by "not requireing" it on the fba_user, the connection with PDO just works fine. I think its a bug, but maybe someone else can help please?
EDIT 1: If I use the database-root user instead, the connection is working, but I'm not sure if ssl is used, because the query:
SHOW STATUS LIKE "%SSL%";
does return
DHE-RSA-AES256-SHA
but I think the system is lying and is not using SSL secured mysql-connection at all.
EDIT 2: I sniffed the packets and yes, for the root-User the connection is secured via SSL, but for the fba_user this isn't the case.
Why I thought the system is lying?: I can change the path in the PHP script for the client certificates and I can even delete them from the system and reboot the server. The connection is still SSL encrypted, WTF?
I have the same issue at post Can't connect with PDO using ssl but mysqli with ssl works
I would suggest not using root user because I don't think you can force SSL and PDO will fail silently making you think it's working or packets could be encrypted if you're already using SSL (https) I think. An easy test is to just make another user, force ssl, try command line remote connection than use PDO ssl and connection will fail/be blocked and tell you SSL is not working via PDO.
Anyways we had to switch to MySQLI to get DB SSL properly working and using PDO did no work at all. I am still looking for ways to diagnose/debug and figure out why we can't connect using PDO ssl because it's a problem and some third party code ships with PDO as well meaning we can't use their code if we can't figure this out and we do have cause to use the multiple drivers PDO supports so I am trying to figure this out.
If you've made any progress let me know :).
Following code will help you
PDO('mysql:host='.HOST.';dbname='.DBNAME.';charset=utf8', USER, PASSWORD,
array( PDO::MYSQL_ATTR_SSL_KEY =>'/mysqlsslcertificate1/client-key.pem',
PDO::MYSQL_ATTR_SSL_CERT=>'/mysqlsslcertificate1/client-cert.pem',
PDO::MYSQL_ATTR_SSL_CA =>'/mysqlsslcertificate1/ca-cert.pem' )
);