MySQL ERROR 1045 Access denied for ‘user’@’localhost’

The Problem

The logfile of mysqld, /var/log/upstart/mysql.log, reported yet another error:

?120618 14:07:31 [Note] /usr/sbin/mysqld: ready for connections.

Version: ‘5.5.24-0ubuntu0.12.04.1’ socket: ‘/var/run/mysqld/mysqld.sock’ port: 3306 (Ubuntu)

mysqld is alive

Checking for tables which need an upgrade, are corrupt or were

not closed cleanly.

120618 14:07:36 [ERROR] Cannot find or open table nova/projects from

the internal data dictionary of InnoDB though the .frm file for the

table exists. Maybe you have deleted and recreated InnoDB data

This looked like a DB corruption. Assuming healing it will solve the problem I wasted a few hours on that, in vain. Finally it turned this is a harmless alert that has nothing to do with the Access Denied issue (but is probably a nova bug).

Some posts on the subject suggested that the socket permissions prevented local access. In my installation:

# ll /var/run/mysqld/mysqld.sock
srwxrwxrwx 1 mysql mysql 0 Jun 18 17:34 /var/run/mysqld/mysqld.sock

Which is ok.

Back to mysql’s user accounts.

User ‘glance’ was properly defined in mysql, as well as ‘keystone’ and ‘nova’. Permissions and grants looked ok – and let’s recall openstack worked (!). Connecting from any remote host (same command as above, with –host of course) worked fine. The glance daemons glance-api and glance-registry weren’t the only services imapcted by error 1045: keystone and nova had the same issue, and their respective log files (under /var/log/upstart) had thousands of lines of the OperationalError quoted above (which is due to the fact openstack’s upstart jobs have a very “slim” logic).

All that suggested that there was something special about ‘localhost’ access to mysql.

I use ‘etckeeper’ to keep a log and trace of what’s getting installed and modified. Comparing log timestamps and git changes, I concluded that the mess was caused by a simple modification to /etc/hosts: the yellow line below, which is a step in the installation procedure, created the havoc:

?127.0.0.1 localhost

127.0.1.1 ostk-controller1

10.0.0.40 ostk-controller1

10.0.0.41 ostk-nova1

A Poor Workaround

Putting this yellow line in comment provided a poor workaround: it solved mysql ERROR 1045, but the controller must have its hostname resolved in /etc/hosts so we’re not satisfied.

Further reading suggested that the error has something to do with the way mysql interprets ‘%’ in statements such as:

GRANT USAGE ON *.* TO ‘glance’@’%’ IDENTIFIED BY PASSWORD(‘openstack’);

Is it possible that ‘%’ stands for “all hosts except localhost”? i read this more than once (see References below) but could hardly belive. Interestingly, the MySQL documentation isn’t clear about this question and that’s the reason, i guess, there’s so much confusion and so many posts related to ERROR 1045.

It’s worth noting that the hint to the final answer was found in a post with the title “Any way to make anyhost ‘%’ include localhost”…

Understanding MySQL Access

I’ve set up a separate VM to explore that, and here are my findings.

After installing mysql-server (latest for Precise is 5.5.24), the USER table and GRANTS get the default settings listed below (for clarity i’ve cut the right side of the output so it doesn’t look exactly as on screen):

[14:57:22]root@mysqltests[~]
# mysql -u root -p
. . .
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)
. . .

mysql> show grants;
+———————————————————+
| Grants for root@localhost |
+———————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ |

| IDENTIFIED BY PASSWORD ‘*77B48D6366D102139D3719’ |

| WITH GRANT OPTION |
| GRANT PROXY ON ”@” TO ‘root’@’localhost’ WITH GRANT |

| OPTION |
+———————————————————+
2 rows in set (0.00 sec)

The mysql.user Table

At first glance, we have 2 users (root and debian-sys-maint). That’s wrong, because mysql’s “user” is a ‘user’@’host’ pair association. So we have 7 in total: ‘root’ is defined (with the same password) for any combination of ‘localhost’ (the first 4 lines), then we have 2 strange lines with empty username, and finally the debian backdoor ‘debian-sys-maint’.

The grants

The ‘show grants’ above shows only grants for ‘root’. But if we run the next staement, we see what access is provided to any user connecting from ‘localhost’:

mysql> show grants for ”@’localhost’;
+————————————–+
| Grants for @localhost |
+————————————–+
| GRANT USAGE ON *.* TO ”@’localhost’ |
+————————————–+

Which (indirectly) explains why running this command (as Linux user ‘ori’) doesn’t require a password:

[16:16:57]ori@mysqltests[~]

$ mysqladmin ping
mysqld is alive

Where this one fails:

[16:14:59]ori@mysqltests[~]
$ mysqladmin -uroot ping
mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘root’@’localhost’ (using password: NO)’

Honestly, in the beginning i thought there’s some balck magic here related to the user (‘ori’, in this case) defined during ubuntu installation, or a special Linux group memebership, or some apparmor profile or god-knows what else.

But there’s no black magic after all, and it’s all inside mysql:

The first thing to bear in mind is that the empty USER field ” is a wildcard, same as ‘%’ for host.

The second is that mysql prefers the explicit match over the wildcard. For example, user ‘root’ can match either [1] the explicit ‘root’@localhost’ row or [2] the wildcard ”@’localhost’ row. Since there’s an explicit entry for [1] in the table mysql.user, it’ll be used. This in turn requires a password so when i try to connect as ‘root’ without a password i’m rejected.

When i connect as ‘ori’ – which isn’t even a mysql user, there’s only one possible match – ”@’localhost’ and this line in the table doesn’t have a password.

This nicely explains why the above mysqladmin command works for ‘ori’ and fails for ‘root’.

To sum it up: mysql controls access (or connection request) based on the USER table. Which user, from which host and whether a password is required.

Once connected, the GRANTS determine what the user is allowed to do. When connected as ‘ori’ i’m limited to “USAGE” (e.g. check if server is up, what version and the like of inoffensive commands).

So far so good – but why ‘glance’@’localhost’ is denied access on the OpenStack controller?

When the static IP address of the conroller wasn’t in /etc/hosts (or after it was commented-out), there was only one match for ‘glance’ = ‘glance’@’%’

This, in turn, comes from the connection string (in /etc/glance/glance-registry.conf) which is:

sql_connection = mysql://glance:openstack@10.0.0.40/glance

It specifies user, password and host.

The line I’ve added for 10.0.0.40 in /etc/hosts, told mysql (indirectly) that host ‘ostk-controller1’ is actually ‘localhsot’. From now on, there are 2 possible matches for ‘glance’, and the one picked by mysql is ”@’localhost’. This row, however, doesn’t require a password – which the sql_connection string provide.

And that’s why all OpenStack services couldn’t connect to mysql.

Check against the USER table below, this was taken from ostk-controller (not the test VM):

mysql> SELECT user,host,password FROM mysql.user;
+——————+——————+————————-+
| user | host | password |

+——————+——————+————————-+
| root | localhost | *3A4A03AC22526F6B591010 |

| root | ostk-controller1 | *3A4A03AC22526F6B591010 |

| root | 127.0.0.1 | *3A4A03AC22526F6B591010 |

Solution for ERROR 1045

After understanding why, let’s improve on the poor workaround.

I’d like to credit an answer by Paul DuBois from 2004 for this solution(it’s worth noting that the subject was “Re: Any way to make anyhost ‘%’ include localhost”).

Borrowing from there, here’s the remedy:

in MySQL:

mysql -uroot -p

DELETE FROM mysql.user WHERE Host=’localhost’ AND User=”;

DELETE FROM mysql.user WHERE Host=’ostk-controller1′ AND User=”;

FLUSH PRIVILEGES;

in /etc/hosts:

Replace the line

127.0.1.1 ostk-controller1

by this one:

10.0.0.40 ostk-controller1

Quoting from Debian’s reference manual:

For a system with a permanent IP address, that permanent IP address should be used here instead of 127.0.1.1

finally restart networking and mysqld – or simply reboot.

A Second Solution

Months after going through the above study, i found out why some OpenStack installations don’t hit this issue; The keystone installation instructions (from Ubuntu, for Essex, can be found here) create each OSTK user in mysql twice, as in:

mysql> CREATE DATABASE keystone;
CREATE USER ‘keystone’@’localhost’ IDENTIFIED BY ‘Secret_pass’;
GRANT ALL PRIVILEGES ON keystone.* TO ‘keystone’@’localhost’
WITH GRANT OPTION;
CREATE USER ‘keystone’@’%’ IDENTIFIED BY ‘Secret_pass’;
GRANT ALL PRIVILEGES ON keystone.* TO ‘keystone’@’%’
IDENTIFIED BY ‘Secret_pass’;
FLUSH PRIVILEGES;

mysql -u root -p

CREATE USER ‘bill’@’%’ IDENTIFIED BY ‘passpass’;

grant all privileges on *.* to ‘bill’@’%’ with grant option;

mysql -u bill -p

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

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 -hmydb@mydomain.com
mysql -u bill -p -h10.1.2.30
mysql -u bill -p -h127.0.0.1 –protocol=TCP

SELECT USER(),CURRENT_USER();

mysql> grant all on *.* to x@’%’;
Query OK, 0 rows affected (0.02 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>

~$ mysql -u root -p
Enter Password:

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

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. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.16 MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>

Recent Posts

Pages

Categories

Archives

Recent Comments

Categories

MySQL ERROR 1045 Access denied for ‘user’@’localhost’

Leave a Reply Cancel reply

PRODUCTS