List:General Discussion« Previous MessageNext Message »
From:shawn green Date:March 16 2012 8:28pm
Subject:Re: Can't connect as non-root user to database
View as plain text  
On 3/16/2012 2:41 PM, Clemens Eisserer wrote:
> Hi Shawn,
>
> I understand the logic behind seperating local and remote users,
> postgresql does the same thing in its pg_hba.conf file.
> However, what I don't understand is the way this turned out to be such
> a huge problem (for me), as it worked already with MySQL-5.1 a few
> years ago. I've worked with other DBMs a bit as well, so I am not an
> absolute noob.
>
> To be honest I am a bit troubled with your explanation. If the % does
> not include "localhost", why is it now possible to me to login as
> "someone"@"%" after I have deleted that anonymous user claudio
> mentioned (thanks again!).
> Furthermore, why does mysql print "access denied for
> someone@localhost" even when I specify -h127.0.0.1?
>
> Thanks a lot for your response, Clemens
>

Because there is also an entry in your hosts file that equates the 
address 127.0.0.1 to the alias 'localhost'. The MySQL permissions checks 
looks for the the most specific match, first. Host matches are more 
specific than user matches as demonstrated in the examples here:
http://dev.mysql.com/doc/refman/5.5/en/connection-access.html

Once you eliminated the anonymous @localhost user, the system was able 
to check other possible matches, in order of specificity, to see if your 
login attempt qualified for access.

Because the socket-based direct match of someone@localhost failed, the 
system progressed to the network-based match of someone@% where the 
alias was able to resolve your address back from 127.0.0.1 into 
'localhost' which matched your pattern and granted you access.

Any previous improper behavior in older versions was most likely 
corrected when we fixed several bugs related to the IPv6 loopback 
address. Details are covered in the changelog to version 5.5.3 and the 
specific bug reports linked from within it.
http://dev.mysql.com/doc/refman/5.5/en/news-5-5-3.html

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
Can't connect as non-root user to databaseClemens Eisserer16 Mar
  • Re: Can't connect as non-root user to databaseClaudio Nanni16 Mar
Re: Can't connect as non-root user to databaseClemens Eisserer16 Mar
  • Re: Can't connect as non-root user to databaseRik Wasmus16 Mar
    • Re: Can't connect as non-root user to databaseClemens Eisserer16 Mar
      • Re: Can't connect as non-root user to databaseClaudio Nanni16 Mar
        • Re: Can't connect as non-root user to databaseClaudio Nanni16 Mar
        • Re: Can't connect as non-root user to databaseClemens Eisserer16 Mar
          • Re: Can't connect as non-root user to databaseClaudio Nanni16 Mar
      • Re: Can't connect as non-root user to databaseRik Wasmus16 Mar
      • Re: Can't connect as non-root user to databaseshawn green16 Mar
        • how to sync mysql.user table between to two mysql instancesCharles Brown16 Mar
          • Re: how to sync mysql.user table between to two mysql instancesBaron Schwartz16 Mar
            • RE: how to sync mysql.user table between to two mysql instancesDavid Lerer16 Mar
              • RE: how to sync mysql.user table between to two mysql instancesCharles Brown16 Mar
                • RE: how to sync mysql.user table between to two mysql instancesDavid Lerer16 Mar
            • Re: how to sync mysql.user table between to two mysql instancesJohan De Meersman16 Mar
              • RE: how to sync mysql.user table between to two mysql instancesCharles Brown17 Mar
        • Re: Can't connect as non-root user to databaseClemens Eisserer16 Mar
          • Re: Can't connect as non-root user to databaseshawn green16 Mar