2

I'm trying to run a GRANT ALL statement:

GRANT ALL ON daisyrepository.* TO daisy@localhost IDENTIFIED BY 'password_here';

I've ran this statement on the server itself using Remote Desktop using:

  • PHPMyAdmin
  • MySQL Administrator
  • Command line client

Everytime I run this statement, I get the following error:

ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'daisyrepository'

A bit of information regarding priviledges:

root@%

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT, EXECUTE

root@localhost

ALL PRIVILEGES

If I understand correctly, even though I'm connected on the server, MySQL is treating my statements like I was coming from a remote location?

Edit #1 Clafification: the server I'm connected to is where MySQL is also running.

Edit #2 Once in RDP, I log into MySQL using the command line. If I run select current_user(), I get root@127.0.0.1. The my.ini file also has skip_name_resolve in it.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
TechFanDan
  • 3,329
  • 6
  • 46
  • 89

1 Answers1

4

You appear to always be connecting over TCP (rather than Unix sockets on Linux or named pipes on windows). Because you have skip_name_resolve in your ini file, the server doesn't reverse DNS the ip 127.0.0.1 back to localhost, hence the user you appear as is root@127.0.0.1, not root@localhost.

If you can, remove the skip_name_resolve from the ini file and make sure as Abe Petrillo mentions that you have '127.0.0.1 localhost' in your hosts file. This should allow you to connect as root@localhost. If you need skip_name_resolve to be put back, make sure you run,

GRANT ALL on *.* to 'root'@'127.0.0.1' identified by '$roots_password' WITH GRANT OPTION;

which should allow you to add the skip_name_resolve back into your ini file.

JohnC
  • 489
  • 2
  • 6
  • Does this mean that I would have 3 sets of permissions: %, 127.0.0.1 and localhost? Is that the norm? And should I be running without the skip_name_resolved? – TechFanDan May 09 '11 at 11:43
  • --skip-name-resolve: Do not resolve host names when checking client connections. Use only IP addresses. If you use this option, all Host column values in the grant tables must be IP addresses or localhost. I get the impression that I should try to investigate why I cannot connect using named pipes? – TechFanDan May 09 '11 at 12:26
  • @TekiusFanatikus I would not normally have a 'root'@'%'. If other servers need to connect, have specific users with locked down privileges. The 'root'@'localhost' we tend to leave (as long as it has a strong password) as it is the default way in if anything goes wrong. The 'root'@'127.0.0.1' is only needed if you choose to keep skip_name_resolve. We have used servers both with and without. Resolving ips means we can move client servers to other ips without having to update permissions, but can prevent some connections if there is ever an issue with local DNS servers. – JohnC May 09 '11 at 12:31
  • I'm still having a hard time understanding why I cannot access root@localhost when I RDP on the machine where MySQL is running. If you look at the documentation about skip-name-resolve (http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_skip-name-resolve), it says IPs or localhost. In my case, I have a wild card and localhost. Would getting rid of the wild card enable me to get at localhost? And if I look at http://dev.mysql.com/doc/refman/5.0/en/connection-access.html, it sorts the wildcard first, then localhost... so maybe I should get rid of root@%?? – TechFanDan May 09 '11 at 13:29
  • @TekiusFanatikus: The dev.mysql.com/doc/refman/5.0/en/connection-access.html says it sorts specific hosts before any wildcards, so you shouldn't have to remove root@%. The problem is that skip_name_resolve means that a TCP connection of 127.0.0.1 never gets reverse DNSed to localhost. We tend to use MySQL on linux, which by default with use unix sockets when running a local client. This sets the username to @'localhost', hence the reason for keeping @localhost entry. I am not sure as to whether windows sets the hostname to localhost when connecting on named pipes as we don't use it this way. – JohnC May 09 '11 at 15:39