Adding new host entries to existing mysql users

Question

I have a fairly well defined list of 50 or so users in a mysql database. All their entires in the user table look something like this:

mysql> select user,host,password from user where host = '1.2.3.4';
+--------+---------+----------+
| user   | host    | password |
+--------+---------+----------+
| user1  | 1.2.3.4 | *XXX...  |
| user2  | 1.2.3.4 | *YYY...  |
| user3  | 1.2.3.4 | *ZZZ...  |
etc.

What I would like to do is add a second entry for each user so that they can also connect from 10.% using the same password and granting full access from 10.%, but leaving the existing grants in place.

Is there a way I could do this all from within mysql (for automation purposes) or would I have to write a script that dumps the users & password hashes out then issues new grant statements for the 10.% host?

Answer 1

INSERT INTO user (user, host, password)
SELECT U.user, '10.x' AS NHost, U.password FROM user AS U