MySQL GRANT to User@'host' not behaving as expected
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?
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
Reinstalling fixed the issue. Not sure what the problem was.
精彩评论