Add host to existing user @ Mysql with the same permission

I would like to add another host for existing users at my DB. I want this new record to have a same privileges with the existing user as well. I saw this thread https://stackoverflow.com/questions/19281305/adding-new-host-entries-to-existing-mysql-users but it seems it does not copy the whole privileges that the existing users have. Any programmatic solution instead of manually adding the user one by one?

There is no other way to do, just manually copy all of the columns from the table.. Using 5.5.29 my list looks like this...

Host, User, Password, Select_priv, Insert_priv, Update_priv,
Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv,
Process_priv, File_priv, Grant_priv, References_priv, Index_priv,
Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv,
Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv,
Create_view_priv, Show_view_priv, Create_routine_priv,
Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv,
Create_tablespace_priv, ssl_type, ssl_cipher, x509_issuer,
x509_subject, max_questions, max_updates, max_connections,
max_user_connections, plugin, authentication_string, password_expired,
is_role, default_role, max_statement_time

(as copied from SHOW FULL COLUMNS FROM mysql.user), and then you'll see the Host column is at the top. So you put in what you want.. like this:

'192.168.172.14', User, Password, Select_priv, Insert_priv, Update_priv,
Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv,
Process_priv, File_priv, Grant_priv, References_priv, Index_priv,
Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv,
Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv,
Create_view_priv, Show_view_priv, Create_routine_priv,
Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv,
Create_tablespace_priv, ssl_type, ssl_cipher, x509_issuer,
x509_subject, max_questions, max_updates, max_connections,
max_user_connections, plugin, authentication_string, password_expired,
is_role, default_role, max_statement_time

Then you just take both of those big nasty text blocks and plop it into a statement.

INSERT INTO mysql.users (firstblock)
  SELECT secondblock
  FROM mysql.user;

And then you cry, and wish you were using a real database that naturally handles multiple auth modules, from different hosts.

Feel free to add selectivity (a WHERE clause so as not to open up the new host for connections from all users)

StackExchange Database Administrators Q#191946, answer score: 3