2

I issue the following command to allow root login from any host

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'xxxx' WITH GRANT OPTION;
FLUSH PRIVILEGES;

but it does not update?


mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*xxxxxxxxxxxxxxxxx33487256' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
Darin Dimitrov
  • 1,023,142
  • 271
  • 3,287
  • 2,928
veccy
  • 925
  • 3
  • 12
  • 20

2 Answers2

4

I suspect it's because you're issuing the "show grants" command when connected as root to localhost. Try this and see what is returned:

SHOW GRANTS FOR 'root'@'%';

And if that doesn't show up anything, have a look in the mysql.user table, there might be a clue in there:

SELECT user, host FROM mysql.user;
Kenny
  • 1,090
  • 7
  • 9
  • Thanks, It does show it with your command. why would I still be unable to login from a different host. ERROR 1045 (28000): Access denied for user 'root'@'xxxx' (using password: YES) – veccy Sep 24 '11 at 19:33
  • What command are you issuing? And do you have MySQL on your server set up to allow connections from remote hosts? – Kenny Sep 24 '11 at 19:43
  • Thats what I want to archive. Does GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' not provide this – veccy Sep 24 '11 at 21:47
  • I did GRANT ALL ON . TO 'root'@'%' - instead of GRANT ALL PRIVILEGES ON . TO 'root'@'%' and it worked – veccy Sep 24 '11 at 22:49
  • Sorry, I meant, what command are you issuing on the client to connect to the server? Such as: 'mysql -u user -p' - I'm wondering whether the problem is with the command you are issuing when connection, rather than the server being the problem. Also, can you find out whether your server is accepting connections from outside of localhost by seeing what 'bind-address' is set to in your my.cnf file? – Kenny Sep 25 '11 at 08:32
0

create user 'user'@'%' identified by 'pass'; Query OK, 0 rows affected (0.00 sec)

what i did was to add with grant option :) grant all on . to 'user'@'%' with grant option;

then it began to work it should be...

best regards, emrah benligil

user3310948
  • 1