I have a MySQL server installed and running on an Ubuntu server. I use a Win7 PC to remotely access the server and for my 'root' user this works fine, I can start up MySQL Workbench, configure the connection to use the 'root' user and successfully connect and manage the database server.
My son will be doing A-Level computing and I thought it'd be good for him to have access to the database to learn with. Obviously, I don't want him to access everything on the server so I created a user in his name and a database for him to use. I've granted privileges for create, update, insert, delete and selectto him on his database only:-
grant insert, delete, update, create, select on 'hisdatabase'.* to 'hisusername'@'%';
So far, so good.
The problem I'm having is that when I use MySQL Workbench to set up a connection using 'hisusername' I get an error and can't login. The error dialog is:-
Failed to connect to MySQL at [server ip address]:[port] with user [hisusername]
Access denied for user [hisusername]@[Win7Client ip address] (using password: YES)
This connection works fine if I specifiy 'root' as the user but not 'hisusername'.
As user 'root' works perfectly, I'm guessing I'm missing a configuration parameter for 'hisusername' that is preventing remote access. Does anyone have any idea what this might be?