Why is GRANT not working in MySQL?

Question

I'm scratching my head on this one as I see a ton of helper websites showing how to create MySQL users and grant privileges but for some reason it just does not work for me. I tried on both WinXP and a MacBook Pro laptop with the latest MySQL version installed.

The following is just an example from when I worked with WordPress. The actual database is something different but same issues.

Here are the steps:

mysql> mysql -uroot -p<password> mysql> CREATE DATABASE wwordpress; mysql> CREATE USER 'www'@'localhost' IDENTIFIED BY 'basic'; 

Query OK, 0 rows affected (0.14 sec)

mysql> GRANT INSERT ON wordpress.* TO 'www'@'localhost' IDENTIFIED BY 'basic'; 

Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES; 

Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM mysql.user WHERE User='www' \G 

 *************************** 1. row ***************************                  Host: localhost                  User: www              Password: *E85C94AF0F09C892339D31CF7570A970BCDC5805           Select_priv: N           Insert_priv: N           Update_priv: N           Delete_priv: N           Create_priv: N             Drop_priv: N           Reload_priv: N         Shutdown_priv: N          Process_priv: N             File_priv: N            Grant_priv: N       References_priv: N            Index_priv: N            Alter_priv: N          Show_db_priv: N            Super_priv: N Create_tmp_table_priv: N      Lock_tables_priv: N          Execute_priv: N       Repl_slave_priv: N      Repl_client_priv: N      Create_view_priv: N        Show_view_priv: N   Create_routine_priv: N    Alter_routine_priv: N      Create_user_priv: N            Event_priv: N          Trigger_priv: N              ssl_type:            ssl_cipher:           x509_issuer:          x509_subject:         max_questions: 0           max_updates: 0       max_connections: 0  max_user_connections: 0 1 row in set (0.00 sec)  mysql> 

As you can see "Insert_priv: N" should be "Y".

Any suggestions?

Thanks in advance.

Answer 1

What you are selecting are the global privileges. You are however giving database (and host, but that doesn't matter) specific privileges.

GRANT INSERT ON wordpress.* TO 'www'@'localhost' IDENTIFIED BY 'basic';

These permissions are stored in the db table.

Just to point you in the right direction:

SHOW GRANTS FOR 'www'@'localhost' 

http://dev.mysql.com/doc/refman/5.0/en/show-grants.html

Answer 2

That's not where the most user GRANTed rights are stored - try

SHOW GRANTS FOR 'www'@'localhost' 

to see database-specific permissions instead. (A grant would only show up in the user table if it was for all databases.)

Here's a (rather old) step-by-step detail of how permissions are stored in MySQL - I don't think things have changed much.