Loop through all users in MySQL and add REQUIRE SSL flag

How do I loop through all users (we have > 3000) in a MySQL database, and add the REQUIRE SSL flag to the every account? I don't want to modify the existing users permissions, host, or password, simply add REQUIRE SSL

You could GRANT each existing user the special no-privilege USAGE privilege, which doesn't change their existing privileges but can be used to REQUIRE SSL for an existing user.

USAGE can be specified to create a user that has no privileges, or to specify the REQUIRE or WITH clauses for an account without changing its existing privileges.

— http://dev.mysql.com/doc/refman/5.6/en/grant.html

mysql> SHOW GRANTS;
+---------------------------------------------------------------------------------------------+
| Grants for sqlbot@%                                                                         |
+---------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'sqlbot'@'%' IDENTIFIED BY PASSWORD '*XXX' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> GRANT USAGE ON *.* TO 'sqlbot'@'%' REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS;
+---------------------------------------------------------------------------------------------------------+
| Grants for sqlbot@%                                                                                     |
+---------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'sqlbot'@'%' IDENTIFIED BY PASSWORD '*XXX' REQUIRE SSL WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The above approach adds the SSL restriction without otherwise changing my privileges.

Alternately, you should be able to manipulate the mysql.user table directly with queries. The only caveats here: don't add this to your own SUPER account until you know you've got SSL working correctly, and remember to FLUSH PRIVILEGES; after manually modifying the grant tables. Without that last step, the changes won't be noticed by the server until the next restart, since the grant tables are cached and only changes made by GRANT or REVOKE are noticed by the server otherwise.

This query should force all users to use SSL. You may want to modify it to suit your requirements, based on what a correctly-configured user looks like in your user table, or try it on only one user and not the entire user base, to confirm that the behavior you get is the behavior you expect.

mysql> UPDATE mysql.user SET ssl_type = 'any' WHERE ssl_type = '';
mysql> FLUSH PRIVILEGES;

There is no reason I can think of that this should be problematic, but please use this suggestion with caution in production.

StackExchange Database Administrators Q#73242, answer score: 7