MySQLTalk.org MYSQL discussions groups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Problem with GRANT ... 'user'@'%'          MySQLTalk.org Forum Index -> MySQL General Discussion View previous topic :: View next topic   Author Message Miguel Cardenas Guest Posted: Sun May 20, 2007 6:51 am    Post subject: Problem with GRANT ... 'user'@'%' Hello list I have a problem adding a user with host '%' ... *** If I add a user with host '%' when trying to connect get this error: ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES) *** If I add the same user with localhost it connects and works fine... My specific doubt is: 1) do I need to add a same user with both '%' and 'localhost' to connect locally and from another location???, or... 2) for some reason a host '%' is not working or need to do something so it allows connections from any host? Thanks for your help Back to top Baron Schwartz Guest Posted: Sun May 20, 2007 8:03 am    Post subject: Re: Problem with GRANT ... 'user'@'%' Hi, Miguel Cardenas wrote: Quote: Hello list I have a problem adding a user with host '%' ... *** If I add a user with host '%' when trying to connect get this error: ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES) *** If I add the same user with localhost it connects and works fine... My specific doubt is: 1) do I need to add a same user with both '%' and 'localhost' to connect locally and from another location???, or... 2) for some reason a host '%' is not working or need to do something so it allows connections from any host? Remember to quote the user and host carefully. So, GRANT ... TO 'user'@'%' not GRANT ... TO user@% If you have doubts about what you actually granted, do this: SHOW GRANTS FOR 'user' And see what host it claims the permissions are for. If you did it wrong, it will say no such user exists. If you do all this and everything looks okay, write back to the list again and maybe we can help you more. Oh, one more place to look is at the server configuration in /etc/my.cnf or equivalent, to be sure networking is configured right. You want to check the bind-address and skip-networking settings. You don't want skip-networking, and you want bind-address set to the machine's IP address. Now that I think of it, this is more likely to be the problem for you. Cheers Baron Back to top Mathieu Bruneau Guest Posted: Sun May 20, 2007 7:51 pm    Post subject: Re: Problem with GRANT ... 'user'@'%' Baron Schwartz a écrit : Quote: Hi, Miguel Cardenas wrote: Hello list I have a problem adding a user with host '%' ... *** If I add a user with host '%' when trying to connect get this error: ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES) *** If I add the same user with localhost it connects and works fine... My specific doubt is: 1) do I need to add a same user with both '%' and 'localhost' to connect locally and from another location???, or... 2) for some reason a host '%' is not working or need to do something so it allows connections from any host? Remember to quote the user and host carefully. So, GRANT ... TO 'user'@'%' not GRANT ... TO user@% If you have doubts about what you actually granted, do this: SHOW GRANTS FOR 'user' And see what host it claims the permissions are for. If you did it wrong, it will say no such user exists. If you do all this and everything looks okay, write back to the list again and maybe we can help you more. Oh, one more place to look is at the server configuration in /etc/my.cnf or equivalent, to be sure networking is configured right. You want to check the bind-address and skip-networking settings. You don't want skip-networking, and you want bind-address set to the machine's IP address. Now that I think of it, this is more likely to be the problem for you. Cheers Baron Localhost is indeed a special value that isn't include in '%'. It's a feature not a bug ;) Regards, -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org Back to top Baron Schwartz Guest Posted: Sun May 20, 2007 9:10 pm    Post subject: Re: Problem with GRANT ... 'user'@'%' Mathieu Bruneau wrote: Quote: Baron Schwartz a écrit : Hi, Miguel Cardenas wrote: Hello list I have a problem adding a user with host '%' ... *** If I add a user with host '%' when trying to connect get this error: ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES) *** If I add the same user with localhost it connects and works fine... My specific doubt is: 1) do I need to add a same user with both '%' and 'localhost' to connect locally and from another location???, or... 2) for some reason a host '%' is not working or need to do something so it allows connections from any host? Remember to quote the user and host carefully. So, GRANT ... TO 'user'@'%' not GRANT ... TO user@% If you have doubts about what you actually granted, do this: SHOW GRANTS FOR 'user' And see what host it claims the permissions are for. If you did it wrong, it will say no such user exists. If you do all this and everything looks okay, write back to the list again and maybe we can help you more. Oh, one more place to look is at the server configuration in /etc/my.cnf or equivalent, to be sure networking is configured right. You want to check the bind-address and skip-networking settings. You don't want skip-networking, and you want bind-address set to the machine's IP address. Now that I think of it, this is more likely to be the problem for you. Cheers Baron Localhost is indeed a special value that isn't include in '%'. It's a feature not a bug Hm, I didn't know that! I thought it was only a special value to the client tools, which I know will try to connect via socket on UNIX machines when they see 'localhost' but will try to connect via TCP/IP when they see '127.0.0.1'. Thanks for pointing this out! Baron Back to top Mogens Melander Guest Posted: Mon May 21, 2007 2:14 am    Post subject: Re: Problem with GRANT ... 'user'@'%' You are still missing the GRANT for 'myuser'@'localhost' On Mon, May 21, 2007 03:06, Miguel Cardenas wrote: Quote: Remember to quote the user and host carefully. So, The quoting is okay, I tested again If you have doubts about what you actually granted, do this: SHOW GRANTS FOR 'user' I've sent this command: grant all on mydatabase.* to 'myuser'@'%' identified by 'mypass'; Then tested the show grants: show grants for 'myuser'; ------- Grants for myuser@% : GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '...' GRANT ALL PRIVILEGES ON `mydatabase`.* TO 'myuser'@'%' so it appears that user is added correctly, but when I try to connect get this error again: ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES) Oh, one more place to look is at the server configuration in /etc/my.cnf or equivalent, to be sure networking is configured right. You want to check the bind-address and skip-networking settings. You don't want skip-networking, and you want bind-address set to the machine's IP address. Now that I think of it, this is more likely to be the problem for you. - skip-networking is disabled - bind-address option is not present in /etc/my.cnf should I try to add a: bind-address = x.y.z.a to the configuration file :-? Thanks for any comment ****************** P.S. I've added the same user but using 'myuser'@'localhost' whithout deleting the first one created, and the new show grants outputs the *same* two lines of information Grants for myuser@% : GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '...' GRANT ALL PRIVILEGES ON `mydatabase`.* TO 'myuser'@'%' but this time am able to connect. What can be wrong? or do I need to add both '%' and 'localhost' hosts? ****************** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mogens (AT) fumlersoft (DOT) dk -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. Back to top Miguel Cardenas Guest Posted: Mon May 21, 2007 6:06 am    Post subject: Re: Problem with GRANT ... 'user'@'%' Quote: Remember to quote the user and host carefully. So, The quoting is okay, I tested again Quote: If you have doubts about what you actually granted, do this: SHOW GRANTS FOR 'user' I've sent this command: grant all on mydatabase.* to 'myuser'@'%' identified by 'mypass'; Then tested the show grants: show grants for 'myuser'; ------- Grants for myuser@% : GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '...' GRANT ALL PRIVILEGES ON `mydatabase`.* TO 'myuser'@'%' so it appears that user is added correctly, but when I try to connect get this error again: ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES) Quote: Oh, one more place to look is at the server configuration in /etc/my.cnf or equivalent, to be sure networking is configured right. You want to check the bind-address and skip-networking settings. You don't want skip-networking, and you want bind-address set to the machine's IP address. Now that I think of it, this is more likely to be the problem for you. - skip-networking is disabled - bind-address option is not present in /etc/my.cnf should I try to add a: bind-address = x.y.z.a to the configuration file :-? Thanks for any comment ****************** P.S. I've added the same user but using 'myuser'@'localhost' whithout deleting the first one created, and the new show grants outputs the *same* two lines of information Grants for myuser@% : GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '...' GRANT ALL PRIVILEGES ON `mydatabase`.* TO 'myuser'@'%' but this time am able to connect. What can be wrong? or do I need to add both '%' and 'localhost' hosts? ****************** Back to top Baron Schwartz Guest Posted: Mon May 21, 2007 7:59 am    Post subject: Re: Problem with GRANT ... 'user'@'%' Hi, Miguel Cardenas wrote: Quote: Remember to quote the user and host carefully. So, The quoting is okay, I tested again If you have doubts about what you actually granted, do this: SHOW GRANTS FOR 'user' I've sent this command: grant all on mydatabase.* to 'myuser'@'%' identified by 'mypass'; Then tested the show grants: show grants for 'myuser'; ------- Grants for myuser@% : GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '...' GRANT ALL PRIVILEGES ON `mydatabase`.* TO 'myuser'@'%' so it appears that user is added correctly, but when I try to connect get this error again: ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES) Oh, one more place to look is at the server configuration in /etc/my.cnf or equivalent, to be sure networking is configured right. You want to check the bind-address and skip-networking settings. You don't want skip-networking, and you want bind-address set to the machine's IP address. Now that I think of it, this is more likely to be the problem for you. - skip-networking is disabled - bind-address option is not present in /etc/my.cnf should I try to add a: bind-address = x.y.z.a to the configuration file :-? Thanks for any comment ****************** P.S. I've added the same user but using 'myuser'@'localhost' whithout deleting the first one created, and the new show grants outputs the *same* two lines of information Grants for myuser@% : GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY PASSWORD '...' GRANT ALL PRIVILEGES ON `mydatabase`.* TO 'myuser'@'%' but this time am able to connect. What can be wrong? or do I need to add both '%' and 'localhost' hosts? ****************** I think you now have two users, which you will only be able to see with these: SHOW GRANTS FOR 'myuser'@'%' SHOW GRANTS FOR 'myuser'@'localhost' If you don't specify a hostname in SHOW GRANTS, '%' is assumed. My mistake for not telling you this before. If you enable networking, and connect with mysql -h 127.0.0.1 instead of mysql -h localhost You will connect via TCP/IP, and you will be connecting as 'myuser'@'%' user. (As someone else said, localhost is a magical value with special behavior; it defaults to connecting via a socket on UNIX-ish systems, instead of TCP/IP). The simplest way to set this server configuration, if you haven't already, is probably to set bind-address to 127.0.0.1, which allows TCP/IP connections from the same machine. If you want to be able to connect from elsewhere, set it to the server's IP address. Cheers Baron Back to top Miguel Cardenas Guest Posted: Tue May 22, 2007 6:16 am    Post subject: Re: Problem with GRANT ... 'user'@'%' Quote: If you don't specify a hostname in SHOW GRANTS, '%' is assumed. My mistake for not telling you this before. If you enable networking, and connect with mysql -h 127.0.0.1 instead of mysql -h localhost Well, in fact 127.0.0.1 and localhost produce the same effect but by using the ip address of the server works fine :D Somebody has just sent a comment saying that localhost and a normal ip address work different, and in fact was right, now am able to connect to the ip instead of localhost... Thanks for all your comments Back to top Miguel Cardenas Guest Posted: Tue May 22, 2007 6:19 am    Post subject: Re: Problem with GRANT ... 'user'@'%' Quote: Localhost is indeed a special value that isn't include in '%'. It's a feature not a bug ;) Regards, Bingo! That was the point! If i connect to the server ip or server name it works perfectly, but if I try to connect to localhost it fails unless I add a new user specific to localhost :D Thanks for your comments Back to top Miguel Cardenas Guest Posted: Tue May 22, 2007 6:23 am    Post subject: Re: Problem with GRANT ... 'user'@'%' Quote: Hm, I didn't know that! I thought it was only a special value to the client tools, which I know will try to connect via socket on UNIX machines when they see 'localhost' but will try to connect via TCP/IP when they see '127.0.0.1'. Thanks for pointing this out! Baron In fact that was the problem, connecting to localhost didn't work unless I add specifically localhost... If tried with the host ip or name worked perfectly, so your comment had sense and solved it all... Just one comment, 127.0.0.1 had the same effect as localhost, I should connect to the specific host ip or name. Thanks for all your comments Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         MySQLTalk.org Forum Index -> MySQL General Discussion All times are GMT Page 1 of 1   Jump to: Select a forum MySQL----------------MySQL and JavaMySQL AnnouncementsMySQL++MySQL and .NETMySQL ODBCMySQL and PerlDatabase BenchmarksMySQL BugsMySQL Cluster DiscussionMySQL CommunityMySQL DocumentationEventum DevelopmentEventum General DiscussionMySQL General DiscussionMySQL GUI ToolsMySQL InternalsMySQL CommitsMaxDBMySQL PackagersMySQL Replication DiscussionBrisbane MySQL Users GroupMySQL on Win32  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.