How to require SSL for all remote users

Question

Given a MySQL system with multiple remote users (users of the form 'joecool'@'192.168.1.2'); is there a SQL statement I can use to REQUIRE SSL for all the remote users?

The single user command is:

GRANT USAGE ON *.* TO 'joecool'@'192.168.1.2' REQUIRE SSL; 

Having an "all user" version would be especially useful because phpMyAdmin doesn't support the REQUIRE SSL flag when creating or modifying users.

Answer 1

You can configure mysqld with require_secure_transport.

[mysqld] ... ssl-ca = ... ssl-cert = ... ssl-key = ... ... require-secure-transport = ON 

This capability supplements per-account SSL requirements, which take precedence. For example, if an account is defined with REQUIRE SSL, enabling require_secure_transport does not make it possible to use the account to connect using a Unix socket file.

Answer 2

The (formerly) accepted answer by Honza seems incorrect, see its comments. It seems not possible to use a GRANT query to alter multiple users at once since MySQL does not support wildcards for user names.

As you suggested yourself you can alter records in the mysql.user table directly using an UPDATE query and as Marc Delisle suggested, afterwards flush priviliges with:

FLUSH PRIVILEGES; 

Also see dba.stackexchange.com > How to grant multiple users privileges.