Remote accessing a MySQL server for non-root user

Question

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?

Answer 1

I found the solution! I needed to add "identified by" clause to the grant statement. I added that clause to the "create user" statement but I wasn't aware that it was required for adding permissions to the user as well! It works great now.

P.S. So can you have different passwords to have different permissions for the same user? I get the idea of having different permissions based on where they're signing in from (localhost, ip-address, etc) but doing it based on what password they use seems odd (or is it just a by-product of the way the authentication system works?).