MySQL GRANT to User@'host' not behaving as expected

Question

EDIT: I reinstalled MySQL and this fixed itself. No idea what the issue was.

I ran the following commands in MySQL from the command line:

1. REVOKE ALL PRIVILEGES ON MyDB.* FROM user@'%';
2. DROP USER user@'%";
3. GRANT INSERT,SELECT,DELETE,UPDATE ON MyDB.* TO user@'%' IDENTIFIED BY 'somepassword';
4. FLUSH PRIVILEGES;

The grants for the user are:

GRANT USAGE ON . to 'user'@'%' IDENTIFIED BY PASSWORD GRANT SELECT,INSERT,UPDATE,DELETE ON 'MyDB'.* TO 'user'@'%'

But then I get the following error message when I try to do an update.

UPDATE command denied to user 'user'@'somehost' for table 'sometable'

Relevant info:

SELECT,INSERT, and DELETE all work properly.

I am using C# with Connector/NET

'somehost' is on the same network as the server (different computer).

'sometable' is in MyDB.

If I log in to the server with 'user' on the host machine, update queries work just fine.

EDIT:

If I grant UPDATE,SELECT,INSERT,DELETE to user@'somehost.net', UPDATE queries work without a problem.

Any ideas?

do you have a user object defined for `'user'@'somehost'`? MySQL will see that as different from `'user'@'%'` if it exists. — Jeremy Holovacs, Sep 21 '11 at 15:02

score 1 · Answer 1 · answered Sep 21 '11 at 15:15

1

After taking away all the grants, first you should give the usage privilege to the user.

GRANT USAGE on MyDB.* to 'user'@'localhost'

Usage privilege tells the server that this user is allowed to use MySQL

answered Sep 21 '11 at 15:15

Ashwini Dhekane

2,280
14
19

If that is the case why would SELECT and INSERT work properly? I guess I forgot to mention that explicitly though. – Ignacio Sep 21 '11 at 16:37
If you are not planning to give any privilege then only you need to give **GRANT USAGE on MyDB.* to 'user'@'localhost'** which means you can connect to the server with the MyDB specified. – Vishal Sep 22 '11 at 13:32
@Ignacio, I doubt you have a 'user'@'somehost'. What is **SHOW GRANTS FOR 'user'@'somehost'** return? – Vishal Sep 22 '11 at 13:34
I know I don't have a 'user'@'somehost'. I have 'user'@'%'. What I don't understand is why 'user' can INSERT,SELECT,and DELETE (from 'somehost') without any issues, but UPDATE is denied. All 4 privileges were given in the exact same GRANT statement, and this is the only user (other than root) that I have even created. The return for "SHOW GRANTS FOR 'user'@'%' is contained in the original question. – Ignacio Sep 22 '11 at 14:01

score 1 · Accepted Answer · answered Oct 28 '11 at 02:52

1

Reinstalling fixed the issue. Not sure what the problem was.

answered Oct 28 '11 at 02:52

Ignacio

5,300
1
15
10

MySQL GRANT to User@'host' not behaving as expected

2 Answers2