Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am using MySQL 5.5 on FreeBSD, and everything is operational and has been for some time. The only problem is, no DB users other than root can authenticate properly.

mysql  Ver 14.14 Distrib 5.5.7-rc, for portbld-freebsd7.2 (i386) using  5.2

My primary method for adding users is by using phpMyAdmin. I have also tried using the command line. Either way, the users create just fine, but if I try mysql -u someuser -ppassword, it fails, even if I specify a DB that they have full access to (including GRANT even).

Because of this, I've been using the root login for everything, which is obviously a large security hole.

EDIT: I have flushed privileges multiple times with no luck. Below is output for show grants, I changed the real user/dbname pair to "someuser" here.

Grants for someuser@%:
GRANT USAGE ON *.* TO 'someuser'@'%'
GRANT ALL PRIVILEGES ON `someuser`.* TO 'someuser'@'%' WITH GRANT OPTION
share|improve this question
    
Have you tried to issue good ol' FLUSH PRIVILEGES already ? –  François Feugeas Jan 24 '11 at 14:23
    
Can you provide more information? For example, the output of show grants for user. –  Khaled Jan 24 '11 at 14:27
add comment

3 Answers

up vote 1 down vote accepted

You need to do three things in order to get things running properly

  • Add the users BOTH for % and localhost, this is VERY important

    GRANT ALL PRIVILEGES ON somedatabase.* to someuser@'%' identified by 'password';

    GRANT ALL PRIVILEGES ON somedatabase.* to someuser@localhost identified by 'password';

  • Once you get that done don't forget to FLUSH privileges

    FLUSH PRIVILEGES;

Also have in mind that privileges are additive, no reason to give USAGE if you give ALL later, and you really don't want to give the GRANT option to the user unless you're pretty sure it's strictly necessary.

share|improve this answer
    
Thanks, specifying localhost explicitly makes the problem go away. –  dukeOfOak Jan 24 '11 at 16:37
    
I don't believe that flush privileges is necessary if you're using the grant command; I believe you only need the explicit flush if you are modifying the access tables directly. –  larsks Jan 24 '11 at 17:22
    
flush privileges is mandatory after any privilege change larsks, it's not automatic –  lynxman Jan 24 '11 at 17:37
add comment

Check if users are added to the mysql-user table. If they are, try to run this as mysql-root-user.

flush privileges;

Are we taling about local users? Have you added something to the host-field? I should be 'localhost' if you try to connect from the same machine as the mysqlserver.

share|improve this answer
    
Database users, and host name is "%" in all cases. –  dukeOfOak Jan 24 '11 at 14:43
add comment

do you have a default user/pass setup in ~/.my.cnf

share|improve this answer
    
I do not. The problem exists universally, exim can't connect, PHP apps can't, etc. –  dukeOfOak Jan 24 '11 at 15:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.