Loop through all users in MySQL and add REQUIRE SSL flag

Question

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

Answer 1

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.

• Justin: Thanks for the reply. What if I don't know the host for each user, they change and are not %. Thus doing GRANT USAGE ON *.* TO 'user-here'@'%' REQUIRE SSL; needs to replace %.
• mFeinstein: I am getting the following error: GRANT USAGE ON *.* TO 'myuser'@'%' REQUIRE SSL 0 row(s) affected, 1 warning(s):1287 Using GRANT statement to modify existing user's properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation.
• Michael - sqlbot: @mFeinstein that appears to be a warning, not an error. The "0 rows" counter is common for some administrative tasks, since your query only manipulates rows indirectly. The answer above is MySQL Server 5.6 and previous 5.x. In 5.7 they throw warnings for several operations like this, because there's more than one way to accomplish it and the "old" way is no longer preferred.
• mFeinstein: Yes, I just found the docs on the new ALTER USER to restrict SSL access only, thanks.