80

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.

Nick Jennings
  • 3,853
  • 6
  • 30
  • 45

6 Answers6

153

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.

Pedro
  • 3,511
  • 2
  • 26
  • 31
  • 6
    I am getting `ERROR 1396 (HY000): Operation RENAME USER failed for 'foo'@'%'` – nishant Sep 12 '17 at 06:07
  • 2
    `foo` is an example - replace `foo` by the actual username in your database – Pedro Jun 20 '18 at 19:01
  • 1
    I already changed it to the user name. `'foo'` was just an alias for me as yours. – nishant Jun 21 '18 at 06:05
  • 2
    This should be the accepted answer since privileges won't be maintained updating just the `hostname` column – mikew Feb 25 '20 at 14:57
  • This served the exact purpose I was looking for. i.e. just update host from % to localhost with all the permissions intact. I would also say that this should be the accepted answer. – Ateeq Ahmed Oct 21 '21 at 13:53
  • @nishant make sure that username with the same host doesn't exist i.e. which you are updating to. I got the same error and realized that I had the same user to which I was updating to. – Ateeq Ahmed Oct 21 '21 at 13:59
  • Without being able to confirm myself, it does look like a better solution than what I came up with at the time. I'm marking as the accepted answer. – Nick Jennings Sep 01 '22 at 14:32
125

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;
Nick Jennings
  • 3,853
  • 6
  • 30
  • 45
  • Good deal! (And yes, much better to post this as an answer than the original comment. I missed your comment at the time...) – T.J. Crowder Aug 21 '12 at 09:39
  • 8
    This does not correctly re-assign all the privileges, @pedrocheckos answer below correctly performs the required action. – Richard Newman Mar 31 '17 at 13:12
8

The more general answer is

UPDATE mysql.user SET host = {newhost} WHERE user = {youruser}
e18r
  • 7,578
  • 4
  • 45
  • 40
1

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)]>
jhayton
  • 301
  • 1
  • 10
1

I received the same error with RENAME USER and GRANTS aren't covered by the currently accepted solution:

The most reliable way seems to be to run SHOW GRANTS for the old user, find/replace what you want to change regarding the user's name and/or host and run them and then finally DROP USER the old user. Not forgetting to run FLUSH PRIVILEGES (best to run this after adding the new users' grants, test the new user, then drop the old user and flush again for good measure).

    > SHOW GRANTS FOR 'olduser'@'oldhost';
    +-----------------------------------------------------------------------------------+
    | Grants for olduser@oldhost                                                        |
    +-----------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'olduser'@'oldhost' IDENTIFIED BY PASSWORD '*PASSHASH'      |
    | GRANT SELECT ON `db`.* TO 'olduser'@'oldhost'                                     |
    +-----------------------------------------------------------------------------------+
    2 rows in set (0.000 sec)

    > GRANT USAGE ON *.* TO 'newuser'@'newhost' IDENTIFIED BY PASSWORD '*SAME_PASSHASH';
    Query OK, 0 rows affected (0.006 sec)

    > GRANT SELECT ON `db`.* TO 'newuser'@'newhost';
    Query OK, 0 rows affected (0.007 sec)

    > DROP USER 'olduser'@'oldhost';
    Query OK, 0 rows affected (0.016 sec)
CrackerJack9
  • 3,650
  • 1
  • 27
  • 48
0

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...)

T.J. Crowder
  • 1,031,962
  • 187
  • 1,923
  • 1,875
  • Thanks for your reply T.J. I tried modifying a user record for both mysql.user and mysql.db and although the changes seemed to be executed fine, I still cannot connect from any host on the internal network other than the original host which the user was allowed access to. I'm not sure how to modify .frm files (they are binary)... I assume you mean the ones in the mysql/ directory (and not the applicable DB in which the user has access to). I'm hesitant to go mucking about in files directly though. Is there really no supported way to do this in MySQL? It seems to be rather short-sighted. – Nick Jennings Dec 16 '09 at 12:13
  • Having changed the tables, I expect you need to execute a `FLUSH PRIVILEGES` command (see http://dev.mysql.com/doc/refman/5.0/en/flush.html) or, of course, stop and start the service. The `frm` files I'm talking about would be for views in the database, and so would be in the database subdirectory. The ones I'm familiary with for the MyISAM storage engine are plain text; YMMV. – T.J. Crowder Dec 16 '09 at 12:57
  • "familiary"? My typing skills have really deteriorated... ;) – T.J. Crowder Dec 16 '09 at 12:57
  • for reference, the solution was: 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 ; – Nick Jennings Dec 16 '09 at 13:53