Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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)
share|improve this question
1  
Did you run flush privileges after granting? –  quanta Aug 26 '11 at 3:01
    
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 3:07
    
@quanta - Yes, I did run FLUSH PRIVILEGES after granting. –  Mike Moore Aug 26 '11 at 4:17
    
@Shane - I just appended the results of SHOW GRANTS... to my answer. –  Mike Moore Aug 26 '11 at 4:18
1  
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 4:31
add comment

1 Answer

up vote 4 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.