Re-assign host access permission to MySQL user

问题:

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.

回答1:

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;

回答2:

The accepted answer only renamed the user but the privileges were left behind.

I'd recommend using:

RENAME USER 'foo'@'1.2.3.4' TO 'foo'@'1.2.3.5';

According to MySQL documentation:

RENAME USER causes the privileges held by the old user to be those held by the new user.

回答3:

The more general answer is

UPDATE mysql.user SET host = {newhost} WHERE user = {youruser}

回答4:

I haven't had to do this, so take this with a grain of salt and a big helping of "test, test, test".

What happens if (in a safe controlled test environment) you directly modify the Host column in the mysql.user and probably mysql.db tables? (E.g., with an update statement.) I don't think MySQL uses the user's host as part of the password encoding (the PASSWORD function doesn't suggest it does), but you'll have to try it to be sure. You may need to issue a FLUSH PRIVILEGES command (or stop and restart the server).

For some storage engines (MyISAM, for instance), you may also need to check/modify the .frm file any views that user has created. The .frm file stores the definer, including the definer's host. (I have had to do this, when moving databases between hosts where there had been a misconfiguration causing the wrong host to be recorded...)

回答5:

Similar issue where I was getting permissions failed. On my setup, I SSH in only. So What I did to correct the issue was

sudo MySQL
SELECT User, Host FROM mysql.user WHERE Host <> '%';
MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE Host <> '%';
+-------+-------------+
| User  | Host        |
+-------+-------------+
| root  | 169.254.0.% |
| foo   | 192.168.0.% |
| bar   | 192.168.0.% |
+-------+-------------+
4 rows in set (0.00 sec)

I need these users moved to 'localhost'. So I issued the following:

UPDATE mysql.user SET host = 'localhost' WHERE user = 'foo';
UPDATE mysql.user SET host = 'localhost' WHERE user = 'bar';

Run SELECT User, Host FROM mysql.user WHERE Host <> '%'; again and we see:

MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE Host <> '%';
+-------+-------------+
| User  | Host        |
+-------+-------------+
| root  | 169.254.0.% |
| foo   | localhost   |
| bar   | localhost   |
+-------+-------------+
4 rows in set (0.00 sec)

And then I was able to work normally again. Hope that helps someone.

$ mysql -u foo -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 74
Server version: 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>