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:
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 [email protected]. The my.ini file also has skip_name_resolve in it.
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 [email protected], 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.