MySQL Grant Problem

Question

Why might the following grant statement fail to work?

grant all on kylie.* to 'kylie'@'localhost' identified by 'foo';

Here's the complete output.

$ mysql -A
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 63
Server version: 5.1.37 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases like 'kylie%';
+-------------------+
| Database (kylie%) |
+-------------------+
| kylie             | 
+-------------------+
1 row in set (0.00 sec)

mysql> grant all on kylie.* to 'kylie'@'localhost' identified by 'foo';
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye

$ mysql -u kylie 
ERROR 1045 (28000): Access denied for user 'kylie'@'localhost' (using password: YES)

It seems that these grant options get me every time. I think I have them memorized and the docs seem to check out too, but they often fail to work. What am I missing?

Answer 1

Try something like:

mysql -ukylie -pfoo kylie

The last kylie should tell it to use kylie as the default database (i.e. the one you have permission for). It may not be required, but I'm wondering if it'll work or not for you.

Updated thanks to the comments.

Answer 2

After grating or changing anything major you must execute:

FLUSH PRIVILEGES;

From MySQL site:

PRIVILEGES

Reloads the privileges from the grant tables in the mysql database. On Unix, this also occurs if the server receives a SIGHUP signal.

The server caches information in memory as a result of GRANT, CREATE USER, CREATE SERVER, and INSTALL PLUGIN statements. This memory is not released by the corresponding REVOKE, DROP USER, DROP SERVER, and UNINSTALL PLUGIN statements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with FLUSH PRIVILEGES.