Understanding MySQL the Access Denied error in or outside a Docker container

; Date: Wed Mar 10 2021

Tags: Docker »»»» Docker MAMP »»»» Docker MySQL

I once wasted more than a full workday fighting the MySQL error message Access denied for user (using password: YES). After finding the solution I want to help others avoid this problem. Along the way to fixing the issue, I learned a lot about how MySQL authenticates user ID's. I've been using MySQL for years and had glossed over this topic, but it turns out to both not be terribly difficult, and very crucial to the solution.

I've been using MySQL since the 1990's, but had never gone deep. For example typical web hosting provider administrative control panels tells you a user name and password and instructions for using any database instance you request. Then you go to a command line and type something like this:

$ mysql -u finley -h mysql.example.com -p db_name

And voila you're connected to the MySQL database. The point is you're not creating the user account, the hosting provider is doing so, and you don't have to worry about how it is configured in MySQL. You're possibly not even writing MySQL code, since a common use is as the database layer for website software like Wordpress or Drupal. Likewise one might use PHPMyAdmin to administer their MySQL databases, and it, too, handles creating and deleting user ID's, so that we can blissfully use that application without learning how it actually works.

In other words, we can easily find ourselves staring at this error message and, lacking in-depth MySQL knowledge, wonder, how do we not only understand the Access Denied for user error, but how do we fix it?

MySQL authenticates a user based on three pieces of data: The user name, the IP address or domain name of the other end of the connection, and the supplied password. It searches the mysql.user table for a match on those three values. No match, and the Access Denied for user error is returned. While it's a simple model, there are several possible misconfigurations that can be frustrating to debug.

That error is that simple. We need to ensure having entered correct entries in the mysql.user table.

The inspiration came while updating my book Node.js Web Development. I am working on the chapter about deploying Node.js applications. That chapter includes a long section showing how to use Docker, specifically how to set up a pair of Docker containers, one running a MySQL server the other the Node.js application. The chapter was originally written four years ago, I was doing a fairly simple update, but it didn't work thanks to a repeated Access denied for user (using password: YES) error now matter how many ideas I tried, and how many stackoverflow postings I read.

After finally carefully reading the MySQL documentation, this whole mysterious subject makes a lot more sense. I had from somewhere picked up this snippet of MySQL:

sudo mysql --user=root  <<EOF
CREATE DATABASE userauth;
CREATE USER 'userauth'@'localhost' IDENTIFIED BY 'userauth';
GRANT ALL PRIVILEGES ON userauth.* TO 'userauth'@'localhost' WITH GRANT OPTION;
EOF

While it's fairly clear what's happening here, it was a little mysterious how it actually worked, and how to diagnose it if something went wrong. To state the fairly obvious, this snippet does the following:

  • Creates a database
  • Creates a USER with a username, a hostname, and a password (Identified By means password)
  • Gives that USER full access to the database that was just created

Some things that are not obvious is whether the USER 'userauth'@'localhost' is related to another USER, say, 'userauth'@'example.com'? Are the quote marks truly required? What does GRANT OPTION mean? What precisely do we do for remote users of the database?

For the rest of this post I want to describe some learnings and pointers to documentation that will hopefully save you some time.

Common Error - Improperly handling volume mounted MySQL data directory in Docker image

With Docker, a really excellent way to provision a MySQL server is:

docker run --detach --name db-userauth \
    --env MYSQL_USER=userauth \
    --env MYSQL_PASSWORD=userauth \
    --env MYSQL_DATABASE=userauth  \
    --mount type=bind,src=`pwd`/userauth-data,dst=/var/lib/mysql \
    --network authnet \
    --env MYSQL_ROOT_PASSWORD=w0rdw0rd \
    mysql/mysql-server:8.0 \
    --bind_address=0.0.0.0

This ensures the created container has a useful name, db-SERVICE. We have set up a user ID that has privileges only for the named table, while access to the root user is limited to connections from localhost. A very important thing is using --mount to ensure the MySQL data directory is not hidden inside the container, but is separate. That way we can delete and recreate the container any time we like, without losing the database. By connecting the container to a network we can limit connections to the container to other containers on the network. By not exposing the MySQL port (not adding -p 3306:3306), the database is assured to only be visible to other containers on the virtual network.

Before running this command make sure to do: mkdir userauth-data

The MISTAKE is to forget to recreate this directory any time you make another experiment with the container.

What's going on is that the first time the MySQL container launches (this is also true for MariaDB containers), scripts inside the container run and initialize a blank database. These scripts decide whether or not to run based on whether the data directory is empty. An empty data directory means the database must be initialized, otherwise the scripts assume there is a database and will not initialize a new database.

Suppose you are trying to debug Access denied for user (using password: YES) and are therefore trying idea after idea? Every time you change a setting, then recreate/restart the MySQL container, YOU MUST REMEMBER TO DELETE AND THEN CREATE AN EMPTY DATA DIRECTORY.

If the scripts detect a data directory they do not run, and therefore none of your attempts to change the user configuration will make any difference because the users were not recreated because the data directory initialization did not run because of the existing data directory.

... Edit MySQL container settings
$ rm -rf userauth-data
$ mkdir userauth-data
$ docker run ... as above

That's the pattern you must follow. On every experiment with the MySQL configuration, take those three steps.

How does MySQL validate the user/password in an incoming connection?

Look at this command again:

$ mysql -u finley -h mysql.example.com -p db_name

We'd be running this command on our laptop, on a web hosting server, or maybe a Raspberry Pi in the closet. The point is that we supply a user name, a password, and a host name for the database. The mysql program then connects to the database on the named server. That server, mysql.example.com, is probably not the same machine where we run the command.

When software like Wordpress accesses the database, it makes an API call with the same parameters.

Any time MySQL see's an incoming connection request, it collects these characteristics:

  • The supplied username and password
  • The socket has an IP address for the other end of the TCP/IP data channel

In other words, MySQL has this data: a) username, b) password, c) IP address the connection originated from

It uses that data to match rows in the table mysql.user (the user table in the mysql database), but what precisely does that mean? Those CREATE USER and GRANT commands obviously mention host names like localhost as well as IP addresses. But how does MySQL interpret it? Glad you asked.

Here's a first stage to examining the situation:

mysql> use mysql;
...
mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | %         |
| userauth      | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

The mysql database holds data tables MySQL uses for its own administrative purposes. The user table lists the USER ID's known to this MySQL server. There are several other columns, including a password column, but we only showed these to focus the discussion.

What MySQL does is find the entry in this table that matches those three attributes.

For the user name and password, MySQL does a simple string comparison.

However, for the host column it can do some pattern matching. Because this is an SQL database, the wildcard character is %. A host value of % then matches a connection from any host, while a host value of 172.20.%.% matches any IP address in the 172.20.x.x range.

Documentation:

Examples:

  • The host value can be a domain name or an IP address. For IP addresses it supports both traditional IPv4 and new-fangled IPv6 address formats.
  • Both % and _ are wildcard characters where it is pattern matching similar to the LIKE operator in SQL.
  • %.com Matches any connection from a host whose domain name is in .com
  • 198.51.100.% matches an IPv4 address where the first three octets are 198, 51 and 100.
  • 'david'@'198.51.100.0/255.255.255.0' Allows matching against a netmask

SOMEWHAT COMMON: Misconfigured user table

Apparently it is possible for MySQL to be confused by the entries in mysql.user and produce bad incomprehensible user authentication failures.

What's the difference between user@localhost and user@172.%?

Is there a difference between the same user name when connecting to MySQL from different IP addresses?

Yes.

Think about it. MySQL is matching against rows of the mysql.user table. Therefore a connection from localhost will not match against the row for 172.%. Therefore the user ID's user@localhost, user@foo.com, user@'192.168.1.%' and user@'172.%' are completely separate entities so far as MySQL is concerned.

Another factor is that the mysql.user table has one index. It is a multicolumn index made of the user and host columns.

User table entry with blank user name is anonymous user that matches any supplied user ID

This might be a common configuration issue that can produce unexpected results.

An entry in mysql.user with a blank user name matches any supplied user name, but then treats the connection as if it were the anonymous user. The MySQL documentation says:

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.

That last bit has to do with access rights GRANT declarations. Your software might have supplied the desired username expecting to get GRANT privileges based on that user name, but if it ends up matching the anonymous user then the connection will have the GRANT privileges of the anonymous user.

Documentation:

That page describes in detail how MySQL validates connection requests.

MySQL sorts the user table

According to the documentation:

  • Whenever the server reads the user table into memory, it sorts the rows.
  • When a client attempts to connect, the server looks through the rows in sorted order.
  • The server uses the first row that matches the client host name and user name.

Hence the mysql.user database is interpreted in an order that is different than how the table is presented with a SELECT query like the one earlier.

You may need to think about the sort order of the user table in some cases.

Getting MySQL to tell you the access rights for an account

Try this out:

mysql> show create user 'userauth'@'%';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for userauth@%                                                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'userauth'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*E42080C77C398C9F75A841BF6A26CDFE9977BD95' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for userauth@'%';
+--------------------------------------------------------------------------+
| Grants for userauth@%                                                    |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'userauth'@'%'                                     |
| GRANT ALL PRIVILEGES ON `userauth`.* TO 'userauth'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

It's relatively easy to get MySQL to show you the configuration for a given user ID.

This means you can try different user ID strings and see how MySQL responds.

Are the quotes always required?

From the MySQL documentation:

The user name and host name need not be quoted if they are legal as unquoted identifiers. Quotes are necessary to specify a user_name string containing special characters.

Nope. Quoting is not always required.

Using Docker to test both local and remote connections

With Docker it is very easy to simulate both localhost connections to MySQL, and connections from a remote machine. The mysql/mysql-server container contains a mysql program that we can use for this purpose.

$ docker run -it --rm --network authnet mysql/mysql-server  mysql -u userauth -h db-userauth -p
Enter password:

This creates a container based on the mysql/mysql-server image. The container executes in the foreground (the -it option) and will be removed when the container exits (--rm).

Therefore this container is clearly remote to your database container.

The hostname for your database is the -h db-userauth parameter.

In this example we attached it to a bridge network, using --network. This attribute is optional, and it depends on how your database container is configured.

$ docker exec -it db-userauth mysql -u root -p
ENter password:

This on the other hand executes mysql in the database container. This will therefore be a localhost connection to MySQL, while the previous example will be remote.

You may have a service container from which to diagnose connection attempts. You must first ensure the mysql client program is installed in the container.

$ docker exec -it svc-userauth mysql -u userauth -h db-userauth -p
Enter password:

This would be an existing container that is running software which normally connects to the db-userauth database. Therefore you can run mysql in that container to diagnose whether that container can reach the database container.

Sometimes it's the simplest and most stupid of errors

After learning all the above, I narrowed down the problem to this:

2020-02-18T04:03:18.988Z users:model-users Sequelize params {
  dbname: 'userauth',
  username: 'userauth',
  password: 'userautn',
  params: { host: 'db-userauth', port: 3306, dialect: 'mysql' }
}

Look carefully at the password. Do you see a problem with the password?

After having spent a day and a half diagnosing and learning all the above, to discover THAT was the error left me only able to say AAAARRRRGGGGHHHH.

Useful links

About the Author(s)

(davidherron.com) David Herron : David Herron is a writer and software engineer focusing on the wise use of technology. He is especially interested in clean energy technologies like solar power, wind power, and electric cars. David worked for nearly 30 years in Silicon Valley on software ranging from electronic mail systems, to video streaming, to the Java programming language, and has published several books on Node.js programming and electric vehicles.
Using SSH to remotely control a Docker Engine or Docker Swarm in two easy steps Running MongoDB with Docker and Docker Compose