MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

You probably have an anonymous user ''@'localhost' or ''@'127.0.0.1'.

As per the manual:

When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows: (...)

When a client attempts to connect, the server looks through the rows [of table mysql.user] in sorted order.

The server uses the first row that matches the client host name and user name.

(...) The server uses sorting rules that order rows with the most-specific Host values first. Literal host names [such as 'localhost'] and IP addresses are the most specific.

Hence, such an anonymous user would "mask" any other user like '[any_username]'@'%' when connecting from localhost.

'bill'@'localhost' does match 'bill'@'%', but would match (e.g.) ''@'localhost' beforehands.

The recommended solution is to drop this anonymous user (this is usually a good thing to do anyways).

Below edits are mostly irrelevant to the main question. These are only meant to answer some questions raised in other comments within this thread.

Edit 1

Authenticating as 'bill'@'%' through a socket.

    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass --socket=/tmp/mysql-5.5.sock
    Welcome to the MySQL monitor (...)

    mysql> SELECT user, host FROM mysql.user;
    +------+-----------+
    | user | host      |
    +------+-----------+
    | bill | %         |
    | root | 127.0.0.1 |
    | root | ::1       |
    | root | localhost |
    +------+-----------+
    4 rows in set (0.00 sec)

    mysql> SELECT USER(), CURRENT_USER();
    +----------------+----------------+
    | USER()         | CURRENT_USER() |
    +----------------+----------------+
    | bill@localhost | bill@%         |
    +----------------+----------------+
    1 row in set (0.02 sec)

    mysql> SHOW VARIABLES LIKE 'skip_networking';
    +-----------------+-------+
    | Variable_name   | Value |
    +-----------------+-------+
    | skip_networking | ON    |
    +-----------------+-------+
    1 row in set (0.00 sec)

Edit 2

Exact same setup, except I re-activated networking, and I now create an anonymous user ''@'localhost'.

    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql
    Welcome to the MySQL monitor (...)

    mysql> CREATE USER ''@'localhost' IDENTIFIED BY 'anotherpass';
    Query OK, 0 rows affected (0.00 sec)

    mysql> Bye

    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \
        --socket=/tmp/mysql-5.5.sock
    ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \
        -h127.0.0.1 --protocol=TCP
    ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)
    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -ppass \
        -hlocalhost --protocol=TCP
    ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

Edit 3

Same situation as in edit 2, now providing the anonymous user's password.

    root@myhost:/home/mysql-5.5.16-linux2.6-x86_64# ./mysql -ubill -panotherpass -hlocalhost
    Welcome to the MySQL monitor (...)

    mysql> SELECT USER(), CURRENT_USER();
    +----------------+----------------+
    | USER()         | CURRENT_USER() |
    +----------------+----------------+
    | bill@localhost | @localhost     |
    +----------------+----------------+
    1 row in set (0.01 sec)

Conclusion 1, from edit 1: One can authenticate as 'bill'@'%'through a socket.

Conclusion 2, from edit 2: Whether one connects through TCP or through a socket has no impact on the authentication process (except one cannot connect as anyone else but 'something'@'localhost' through a socket, obviously).

Conclusion 3, from edit 3: Although I specified -ubill, I have been granted access as an anonymous user. This is because of the "sorting rules" advised above. Notice that in most default installations, a no-password, anonymous user exists (and should be secured/removed).

Try:

~$ mysql -u root -p
Enter Password:

mysql> grant all privileges on *.* to bill@localhost identified by 'pass' with grant option;

When you ran

mysql -u bill -p

and got this error

ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

mysqld is expecting you to connect as bill@localhost

Try creating bill@localhost

CREATE USER bill@localhost IDENTIFIED BY 'passpass';
grant all privileges on *.* to bill@localhost with grant option;

If you want to connect remotely, you must specify either the DNS name, the public IP, or 127.0.0.1 using TCP/IP:

mysql -u bill -p [email protected]
mysql -u bill -p -h10.1.2.30
mysql -u bill -p -h127.0.0.1 --protocol=TCP

Once you login, please run this

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in MySQL

CURRENT_USER() reports how you were allowed to authenticate in MySQL from the mysql.user table

This will give you a better view of how and why you were allowed to login to mysql. Why is this view important to know? It has to do with the user authentication ordering protocol.

Here is an example: I will create an anonymous user on my desktop MySQL

mysql> select user,host from mysql.user;
+---------+-----------+
| user    | host      |
+---------+-----------+
| lwdba   | %         |
| mywife  | %         |
| lwdba   | 127.0.0.1 |
| root    | 127.0.0.1 |
| lwdba   | localhost |
| root    | localhost |
| vanilla | localhost |
+---------+-----------+
7 rows in set (0.00 sec)

mysql> grant all on *.* to x@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> select user,host from mysql.user;
+---------+-----------+
| user    | host      |
+---------+-----------+
| lwdba   | %         |
| mywife  | %         |
| x       | %         |
| lwdba   | 127.0.0.1 |
| root    | 127.0.0.1 |
| lwdba   | localhost |
| root    | localhost |
| vanilla | localhost |
+---------+-----------+
8 rows in set (0.00 sec)

mysql> update mysql.user set user='' where user='x';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;
+---------+-----------+
| user    | host      |
+---------+-----------+
|         | %         |
| lwdba   | %         |
| mywife  | %         |
| lwdba   | 127.0.0.1 |
| root    | 127.0.0.1 |
| lwdba   | localhost |
| root    | localhost |
| vanilla | localhost |
+---------+-----------+
8 rows in set (0.00 sec)

mysql>

OK watch me login as anonymous user:

C:\MySQL_5.5.12>mysql -urol -Dtest -h127.0.0.1 --protocol=TCP
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.5.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user(),current_user();
+---------------+----------------+
| user()        | current_user() |
+---------------+----------------+
| rol@localhost | @%             |
+---------------+----------------+
1 row in set (0.00 sec)

mysql>

Authentication ordering is very strict. It checks from the most specific to the least. I wrote about this authentiation style in the DBA StackExchange.

Don't forget to explicitly call for TCP as the protocol for mysql client when necessary.

MySQL ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)

Tags:

Mysql

Access Denied

Related

Recent Posts