Integrating ssl with mysql - Access Denied

Question

I'm trying to setting up ssl for mysql by referring this.
I'm able to complete first 3 steps but having issue with the 4th which is as following:

GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'localhost' IDENTIFIED BY 'ssluser' REQUIRE SSL;
FLUSH PRIVILEGES;

Then I restart the mysql server.

After executing this statement when I try to run mysql -ussluser -pssluser -P3306 --ssl-key="C:\Program Files\MySQL\MySQL Server 5.5\certs\ca-cert.pem",
it shows following error: Access denied for user 'ssluser'@'localhost' (using password: YES)
I'm using 3306 here as it's my default port.

How it can say Access Denied when I have already executed GRANT statement.

Note that

• I executed mysql -ussluser -pssluser before using GRANT statement with REQUIRE SSL and I was able to connect to mysql.

• If I try SHOW GRANTS FOR 'ssluser'@'localhost';
  I get

  GRANT ALL PRIVILEGES ON *.* TO \'ssluser\'@\'localhost\' IDENTIFIED BY PASSWORD \'*C56A6573BEE146CB8243543295FD80ADCE588EFF\' REQUIRE SSL WITH GRANT OPTION
  

• Before executing GRANT statement, I was able to connect to workbench through ssluser. But now its giving access denied error.

• When I use show global variables like 'have_%ssl'; I get

  have_openssl DISABLED have_ssl DISABLED

• and when I use this SHOW STATUS LIKE 'Ssl_cipher'; I get

  Ssl_cipher _________

• I have created all server and client certificates and placed them in certs directory inside mysql server root directory.

I'm trying it from couple of days but have found nothing. Any help appreciated.

I'm doing this for the first time. Can anyone guide me through detailed procedure to do this?

Answer 1

I was struggling with a similar error message today and here is what I discovered.

1. The "REQUIRE SSL" option for the GRANT only requires SSL for connection and does not require a client side certificate to be provided.
2. The mysql CLI does not handle SSL like I expected. For example, on MySQL 5.5, the --ssl option doesn't seem to really enable the SSL transport.
3. I had to add the option --ssl-cipher=DHE-RSA-AES256-SHA:AES128-SHA to get the mysql client to really use SSL and allow authentication with the client.

Here are the exact steps I used to setup my new user:

CREATE USER 'ssl-user'@'%' identified by '<password>';
GRANT USAGE ON *.* TO 'ssl-user'@'%' identified by '<password>' REQUIRE SSL;
GRANT ALL PRIVILEGES ON `your-database`.* TO 'ssl-user'@'%';