On Mon, Nov 24, 2008 at 12:24:00PM +0000, simon ransome wrote:
Chris G wrote:
I always seem to get in a tangle with installing and setting up mySql.
The essential problem seems to be that mySql wants to refer to the local machine as 'localhost', and 127.0.0.1, and 'chris'. Thus when I try and add a user it seems that the user gets added with privileges for one (or some) but not all of 'localhost', and 127.0.0.1, and 'chris' and then doesn't work because I need to acces the database as one of the other host names.
Adding a user as 'user'@* seems to be even worse and doesn't work at all.
MySQL host (and strings in queries) wildcards are of the form "%", so you would need to add something like:
"Grant all on database.* to 'user'@'%' identified by 'password'"; "Flush privileges" (depending upon version, may not be neccesary now)
You also need to be aware that in the table mysql.user, there are a set of top-level access rules - there will generally be several inserted by default for 'users'@'localhost'.
Yes, I seem to remember this being a problem. In fact I seem to remember that using '%' as the hostname doesn't work at all in some cases.
This can cause some fun as if you grantnew privileges to user@'%' and then connect locally, this rule can appear *after* the default, meaning it is never reached: so either delete the row with the localhost host, or overrule with an additional grant for 'user'@'localhost' (to which 127.0.0.1 should be an alias, and possibly "chris" depending upon how that's resolved). You can see what's already there with a "select * from mysql.user;"
OK, thanks, I'll try '%' as host and delete all other entries for the user and see if I get what I want.