MySQL: Grant Privileges followed by Flush Privileges has not effect, no error (logged in as root)

Question

I and a teammember are stumped because commands to grant privileges to a remote user are failing, but with no error. The new user was successfully added to mysql.user using CREATE USER, but GRANT PRIVILEGES followed by FLUSH PRIVILEGES isn't affecting the grants table. Both these commands are supposed to say 'Query OK. 0 rows affected,' which they do. But then SHOW GRANTS doesn't show the new privileges, nor can we log in with that username from the specified remote ip. Can anyone explain the following behavior?

mysql> SELECT CURRENT_USER();     ///I'm definitely in as root user
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'myNewUser'@'remoteIP';  //grant to existing user
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS;

 | Grants for root@localhost                                                                                                                   

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD           '*////TakenOut///' WITH GRANT OPTION |
 | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                              
2 rows in set (0.00 sec)

I would expect a 3rd row with privileges for 'myNewUser'.

Answer 1

The SHOW GRANTS statement shows the grants for the current user. In your case, for root@localhost. So you shouldn't be expecting to see a third row with privileges for myNewUser.

Rather, query the mysql.user table...

SELECT * FROM mysql.user WHERE User = 'myNewUser'

I would verify that the password is set for 'myNewUser'@'remoteip'. (MySQL identifies a "user" by the combination of the username and the host. The user 'myNewUser'@'thisip' is a different user than 'myNewUser'@'thatip'.)

---

Also, we have this setting in our MySQL my.cnf files:

# Disable DNS Lookup (use IP addresses only)
skip-name-resolve

So we use IP addresses, rather than hostnames. (This avoids the connect problems that occur when DNS can't do a DNS reverse lookup from the IP address to get a hostname.)

http://dev.mysql.com/doc/refman/5.1/en/host-cache.html

Bottom line is that MySQL does a "reverse lookup" of an IP address to get a hostname. If it can't get a hostname for a given IP address, connections from the IP address will fail.