List:General Discussion« Previous MessageNext Message »
From:shawn l.green Date:May 29 2014 7:47pm
Subject:Re: access denied fpr user 'root'@'localhost' (using password: NO)
View as plain text  
Hello Érico

On 5/29/2014 2:22 PM, Érico wrote:
> I have ran the following to test a fix for an app issue :
>
> delete from mysql.user where user='';
> 2lines got effected
>
> after this I can´t connect through command line anymore :
>
> ./mysqladmin -u root password <pwd>
>
> I get access denied for user 'root'@'localhost" (using password:'NO')
>
> how can I restore the db so I can connect through command line again ?
>
> thks
>

What that tells me is that you were never actually logging in as root 
but the system was authenticating you as the 'anonymous' user. Quoting 
from the very fine manual:

http://dev.mysql.com/doc/refman/5.6/en/account-names.html
####
A user name is either a nonblank value that literally matches the user 
name for incoming connection attempts, or a blank value (empty string) 
that matches any user name. An account with a blank user name is an 
anonymous user. To specify an anonymous user in SQL statements, use a 
quoted empty user name part, such as ''@'localhost'.
####


http://dev.mysql.com/doc/refman/5.6/en/connection-access.html
####
Identity checking is performed using the three user table scope columns 
(Host, User, and Password). The server accepts the connection only if 
the Host and User columns in some user table row match the client host 
name and user name and the client supplies the password specified in 
that row.
...
If the User column value is nonblank, the user name in an incoming 
connection must match exactly. If the User value is blank, it matches 
any user name. If the user table row that matches an incoming connection 
has a blank user name, the user is considered to be an anonymous user 
with no name, not a user with the name that the client actually 
specified. This means that a blank user name is used for all further 
access checking for the duration of the connection (that is, during 
Stage 2).
...
If you are able to connect to the server, but your privileges are not 
what you expect, you probably are being authenticated as some other 
account. To find out what account the server used to authenticate you, 
use the CURRENT_USER() function.
####

That same page in the manual (and its siblings) should also answer your 
questions as to how MySQL uses the `user` table, what the empty `user` 
and `password` column mean to login attempts, and how to configure 
SSL-based connections.

If you have forgotten your actual root@localhost password, you can reset 
it following one of the procedures provided here.
http://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html

Yours,
-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
access denied fpr user 'root'@'localhost' (using password: NO)Érico29 May 2014
  • Re: access denied fpr user 'root'@'localhost' (using password: NO)Reindl Harald29 May 2014
    • Re: access denied fpr user 'root'@'localhost' (using password: NO)Érico29 May 2014
      • Re: access denied fpr user 'root'@'localhost' (using password: NO)Reindl Harald29 May 2014
        • Re: access denied fpr user 'root'@'localhost' (using password: NO)Érico29 May 2014
          • Re: access denied fpr user 'root'@'localhost' (using password: NO)shawn l.green29 May 2014
  • Re: access denied fpr user 'root'@'localhost' (using password: NO)Kishore Vaishnav29 May 2014
    • Re: access denied fpr user 'root'@'localhost' (using password: NO)Érico29 May 2014
  • Re: access denied fpr user 'root'@'localhost' (using password: NO)shawn l.green29 May 2014