Granting privileges to mysql user not working

Question

I am logged in to mysql as root. I can add a user like this:

CREATE USER test01@localhost IDENTIFIED BY 'test01';

When I attempt to grant privileges to the test01 user, nothing seems to happen:

GRANT SELECT,INSERT,UPDATE,DELETE ON test_database.* TO 'test01'@'localhost';

All of the *_priv columns are set to N for the test01 user.

Any ideas what I am doing wrong here? Thanks!

Here are the results of SHOW GRANTS...

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

What do `show grants for 'test01'@'localhost';` and `select * from mysql.user;` give you? (strip the password hashes out of the user table output - it's relevant whether a hash is in the column, but not what it is) — Shane Madden, Aug 26 '11 at 03:07
@Shane - I just appended the results of `SHOW GRANTS...` to my answer. — Mike Moore, Aug 26 '11 at 04:18
What is the output of `select * from mysql.db where user='test01' and host='localhost' and db='test_database'\G`? — quanta, Aug 26 '11 at 04:31

score 9 · Accepted Answer · answered Aug 26 '11 at 04:39

All of the *_priv columns are set to N for the test01 user.

Did you check it from mysql.user table, something like this:

mysql> select * from mysql.user where user='test01' and host='localhost'\G

If so, you're checking in wrong place. All the privileges in this table is global privileges, it is assigned by using on *.*.

While you are granting at database level on test_database.*, you must check in mysql.db table:

select * from mysql.db where user='test01' and host='localhost' and db='test_database'\G

Read more: http://dev.mysql.com/doc/refman/5.0/en/grant.html

Granting privileges to mysql user not working

1 Answers1