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?

I'm assuming you left a line out between the last two lines (the one that asks for the password 'foo')? — Senseful, Jun 10 '10 at 03:51
No, that's the problem. MySQL is not asking for the password and simply gives me an access denied error. — Joel Dare, Jun 10 '10 at 03:57
Do you have a ~/.my.cnf file the client is possibly reading an (incorrect) password from? — Brock Batsell, Jun 10 '10 at 04:01

Senseful · Accepted Answer · 2010-06-10T20:24:49.900

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.

edited Jun 10 '10 at 20:24

answered Jun 10 '10 at 03:54

Senseful

86,719
67
308
465

That gives me MySQL output indicating usage, like the command is just plain wrong. I also tried the following. "mysql -ukylie -pfoo" That worked! Wonder why. – Joel Dare Jun 10 '10 at 04:01
When using the -p option followed by a space, mysql asks you to type in your password. It takes foo as the name of the database to connect to. Since there is no database named foo, it throws an error. If you omit the space, mysql interprets foo as your password and kylie as your database, which is correct. If you include the password in the command, it shows up in your history files etc. It is more secure to use the -p option without the password: mysql -p -u kylie kylie In this case you will be asked for your password, which will not show up in history files etc. – titanoboa Jun 10 '10 at 07:43

MySQL Grant Problem

1 Answers1