Integrating ssl with mysql - Access Denied

access-denied, mysql, ssl

Solution

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

- The "REQUIRE SSL" option for the GRANT only requires SSL for connection and does not require a client side certificate to be provided.

- 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.

- 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'@'%';

Problem

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?

Original source