3

First of all, my english is really bad, so sorry for my english mistakes. I have a MariaDB 10.2.15. I installed SSL, and its working fine, but I cant force use SSL to users.

my.cnf:

[mysqld]
ssl
ssl-ca=/etc/mariadb/ssl/ca.pem
ssl-cert=/etc/mariadb/ssl/server-cert.pem
ssl-key=/etc/mariadb/ssl/server-key.pem
ssl-cipher = AES128+EECDH:AES128+EDH

ssl variables:

+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| have_openssl        | YES                              |
| have_ssl            | YES                              |
| ssl_ca              | /etc/mariadb/ssl/ca.pem          |
| ssl_capath          |                                  |
| ssl_cert            | /etc/mariadb/ssl/server-cert.pem |
| ssl_cipher          | AES128+EECDH:AES128+EDH          |
| ssl_crl             |                                  |
| ssl_crlpath         |                                  |
| ssl_key             | /etc/mariadb/ssl/server-key.pem  |
| version_ssl_library | OpenSSL 1.0.1e-fips 11 Feb 2013  |
+---------------------+----------------------------------+

I found this variable:

require_secure_transport

and I also found this:

MySQL-only variable determining whether client to server connections need to be secure.

So, can I do anything else? Cause now, the users can connect without SSL. I feel its really unnecessary without forcing.

UPDATE for clear: SHOW GRANTS:

GRANT ALL PRIVILEGES ON *.* TO 'denes'@'%' IDENTIFIED BY PASSWORD '*SOMETHINGPASSWORD' REQUIRE SSL WITH GRANT OPTION

Login Panel - HeidiSQL

SSL Turned off as u can see

And I still can login to MariaDB over TCP without SSL

4
  • Have a look at stackoverflow.com/questions/16120856/… – Patrick Mevzek Jun 8 '18 at 21:54
  • @PatrickMevzek Thank you, but I already tried that, and still can login without SSL. – G3ph4z Jun 8 '18 at 22:37
  • Do you log through a TCP(TLS) socket or a local Unix one? – Patrick Mevzek Jun 8 '18 at 22:58
  • @PatrickMevzek Over TCP. If I try to login in UNIX, its working fine. But we tried lot of MySQL manager, like HeidiSQL, MySQL Workbench, DBEaver, and all of them allowed us to login without SSL. – G3ph4z Jun 8 '18 at 23:13
3

REQUIRE SSL grant option on users is what you need. It works for me as intended:

MariaDB [(none)]> CREATE USER testssl@localhost;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> GRANT USAGE ON *.* TO testssl@localhost REQUIRE SSL;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> Bye

$ sudo mysql -u testssl --ssl
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.7-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants;
+---------------------------------------------------------+
| Grants for testssl@localhost                            |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testssl'@'localhost' REQUIRE SSL |
+---------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> Bye

$ sudo mysql -u testssl --skip-ssl
ERROR 1045 (28000): Access denied for user 'testssl'@'localhost' (using password: NO)

$ grep ssl /etc/my.cnf
ssl-ca = /etc/mysql/ca.pem # [client]
ssl-cert = /etc/mysql/client-cert.pem
ssl-key = /etc/mysql/client-key.pem
ssl-ca = /etc/mysql/ca.pem # [server]
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem

You can see how workbench works with SSL, but fails to connect if I force it to not use SSL:

Works Doesn't work

require_secure_transport is MySQL 5.7+ only, and while it has something to do with forcing TLS, it also considers secure local unencrypted connections.

3
  • Hi Jynus! Thank you helping, but I already tried. I updated the post. Yes, its working fine with socket as u posted, but not over TCP. – G3ph4z Jun 9 '18 at 0:51
  • Can you run SHOW STATUS like 'ssl_cipher'; after connecting? My guess is that unchecking "use ssl" on your gui still allows ssl connections and that is why you don't get any error. Please provide a traffic dump- if it is not encrypted, you will still see the sql in clear text. – jynus Jun 9 '18 at 0:59
  • I got this as a result for "show status like 'ssl_cipher'" Ssl_cipher DHE-RSA-AES128-SHA You might be have right, but I can connect from delphi code with UniDAC without SSL, and its make me confused, cause I am sure, the UniDAC didnt use the SSL. I guess its working same as a HeidiSQL. I will give a try tomorrow. – G3ph4z Jun 9 '18 at 1:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.