Re-assign host access permission to MySQL user

mysql permissions
Nick Jennings picture Nick Jennings · Dec 16, 2009 · Viewed 112.1k times · Source

I have several thousand MySQL users all set to allow access from a specific host. The problem is that now I'm going to have two machines (more in the future) which will need to use the same account to access each of their databases.

I'd like a quick and easy (as automated as possible) way to run through and modify the host portion of each user account to fit an internal network wildcard. For example:

'bugsy'@'internalfoo' has access to the 'bugsy' DB.

I want to now allow bugsy access from anywhere on the internal network

'bugsy'@'10.0.0.%' has access to the 'bugsy' DB.

Answer

Nick Jennings picture Nick Jennings · Aug 20, 2012

For reference, the solution is: 

UPDATE mysql.user SET host = '10.0.0.%' WHERE host = 'internalfoo' AND user != 'root';
UPDATE mysql.db SET host = '10.0.0.%' WHERE host = 'internalfoo' AND user != 'root';
FLUSH PRIVILEGES;

Related questions

MySQL error 1449: The user specified as a definer does not exist

When I run the following query I get an error: SELECT `a`.`sl_id` AS `sl_id`, `a`.`quote_id` AS `quote_id`, `a`.`sl_date` AS `sl_date`, `a`.`sl_type` AS `sl_type`, `a`.`sl_status` AS `sl_…

Read More
MySQL Stored Procedure Permissions

I am trying to give a user permission to run a stored procedure at the stored procedure level on a MySQL Database rather than allowing a user to execute any stored procedure in the database. I was trying to execute …

Read More
Grant a user permission to only view a MySQL view and nothing else

This question was originally using MySQL 5.1.44, but is applicable to MySQL 8.0+ too. Let's say I have a table with records inserted by different users of my application. How can I give a specific user access to only see his/her …

Read More