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.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
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.
Method 2
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.
Method 3
Yes, you can modify the mysql.user table directly (be careful). Then you just issue a FLUSH PRIVILEGES statement to apply the changes to the running server.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0