Add host to existing user @ Mysql with the same permission

Question

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?

Answer 1

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)

Answer 2

If you can install or already have the MySQL Utilities, then you can use the mysqluserclone script.

Reference: 5.28 mysqluserclone — Clone Existing User to Create New User

The command is then a simple:

shell> mysqluserclone --source=root@localhost --destination=root@localhost \
      source_user@localhost target_user:[email protected]

In the above example you are connecting to root@localhost (as the source) and root@localhost (as the target) and are then copying the permissions from source_user@localhost to [email protected].

So you could use:

shell> mysqluserclone --source=root@localhost --destination=root@localhost \
      Reynaldi@localhost Reynaldi:[email protected]

Further details can be found in the rather limited documentation.