Specifying username at mysql logs me in as ''

Question

I created user 'wordpress' and granted privileges at mysql via phpMyAdmin. However, when I log-in at the command line as user wordpress, I can't seem to access what I should be able to access. Take a look:

>mysql -u wordpress
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> SHOW GRANTS;
+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.00 sec)

Isn't that supposed to show Grants for wordpress@localhost?

Furthermore, I granted all (dangerous, I know, but I'm sanity testing) priveleges for wordpress on datbase sitedb. I get the following when I try to use sitedb (this, in the same session):

mysql> USE sitedb;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'sitedb'

What's up?

Answer 1

It looks like there was some error in creating mysql user, I would suggest you to create user and grant privileges from terminal.

Answer 2

From the MySQL documentation:

The USAGE privilege specifier stands for “no privileges.”

It looks like your user doesn't have the correct privileges. Are you sure your granted all privileges on the correct user for the correct database? Try specifically granting all privileges to the sitedb database only.

Answer 3

I figured it out...

I logged in as as root and checked the priveleges of root and where it differs from wordpress:

mysql> SHOW GRANTS;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR wordpress;
+-----------------------------------------------------------------------------+
| Grants for wordpress@%                                                      |
+-----------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wordpress'@'%' WITH GRANT OPTION            |
| GRANT ALL PRIVILEGES ON `skytreader`.* TO 'wordpress'@'%' WITH GRANT OPTION |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

As it stands, the only difference I can gather is the host. I went back to phpMyAdmin to change the host of user wordpress and specify it as 'localhost'. And lo,

>mysql -u wordpress
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> SHOW GRANTS;
+-------------------------------------------------------------------------------------+
| Grants for wordpress@localhost                                                      |
+-------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wordpress'@'localhost' WITH GRANT OPTION            |
| GRANT ALL PRIVILEGES ON `skytreader`.* TO 'wordpress'@'localhost' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> USE sitedb;
Database changed

Now I wonder why is this so? Isn't it that 'wordpress'@'%' should stand for wild card host (and so it should include localhost)? I'm guessing this is some kind of security feature...