List:General Discussion« Previous MessageNext Message »
From:shawn green Date:March 16 2012 4:23pm
Subject:Re: Can't connect as non-root user to database
View as plain text  
On 3/16/2012 7:00 AM, Clemens Eisserer wrote:
> Hi Rik,
>
>> Hm, is the mysql-client library the same as the mysql-server?
> Yes.
>
>> And does mysql --host=127.0.0.1 --user=someone -p somedb work (or it's actual
>> IP-address, forcing the TCP/IP connect instead of possible sockets) ?
>
> This is really strange - with -h127.0.0.1 I get the same error:
> ERROR 1045 (28000): Access denied for user 'someone'@'localhost'
> (using password: YES)
>
> However with -h192.168.1.102 everything works as expected, although I
> used 'someone'@'%' everywhere.
> Does '%' not include local connections / unix domain sockets?
>
> Thanks, Clemens
>

On Unix-based systems the alias 'localhost' implies the local Unix 
socket, not a networking port. The important part to notice is that you 
created a user from "@%" but the error message said "@localhost". As 
that is a local socket, the pattern matching algorithm applied to the % 
to compare the incoming address (the source of the networked connection) 
to the account fails. Therefore it does not match to @localhost as the 
network was not involved.

There is a logic behind this method of operation. Users with physical 
access to the machine (or remote access through tunneling protocols like 
ssh) are local to the files and processes themselves. The security 
exposure for this type of user means that this is most likely a very 
privileged person and they probably need to be allowed privileges for 
full administrative actions. Therefore a local MySQL user (coming in 
through the local Unix socket) may be assigned very different 
permissions than a user who happens to know the administrative account's 
password but is only allowed to login remotely (via the network). By 
keeping @localhost separate from @<host matching patterns>, we allow you 
(the DBA) to deny privileged access to any other user that cannot login 
directly from the host machine.

Hopefully, this clarifies why your localhost account was unable to login.

Additional reading:
http://dev.mysql.com/doc/refman/5.5/en/connection-access.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