List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:May 21 2007 2:59am
Subject:Re: Problem with GRANT ... 'user'@'%'
View as plain text  
Hi,

Miguel Cardenas wrote:
>> 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
Thread
Problem with GRANT ... 'user'@'%'Miguel Cardenas20 May
  • Re: Problem with GRANT ... 'user'@'%'Baron Schwartz20 May
    • Re: Problem with GRANT ... 'user'@'%'Mathieu Bruneau20 May
      • Re: Problem with GRANT ... 'user'@'%'Baron Schwartz20 May
        • Re: Problem with GRANT ... 'user'@'%'Miguel Cardenas22 May
        • Selecting rows by DATE rangesMiguel Cardenas24 Jun
          • Re: Selecting rows by DATE rangesPintér Tibor24 Jun
          • Re: Selecting rows by DATE rangesMogens Melander25 Jun
            • Re: Selecting rows by DATE rangesJørn Dahl-Stamnes25 Jun
      • Re: Problem with GRANT ... 'user'@'%'Miguel Cardenas22 May
        • Re: Problem with GRANT ... 'user'@'%'Mogens Melander22 May
    • Re: Problem with GRANT ... 'user'@'%'Miguel Cardenas21 May
      • Re: Problem with GRANT ... 'user'@'%'Mogens Melander21 May
      • Re: Problem with GRANT ... 'user'@'%'Baron Schwartz21 May
        • Re: Problem with GRANT ... 'user'@'%'Miguel Cardenas22 May