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
