0

In MariaDB on windows I create a user and database with the following

flush privileges;
CREATE USER 'test-one'@'%' IDENTIFIED BY 'test-one';
GRANT USAGE ON *.* TO 'test-one'@'%' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
CREATE DATABASE IF NOT EXISTS `test-one`;
GRANT ALL PRIVILEGES ON `test-one`.* TO 'test-one'@'%';

After that I am unable to connect to the database

.\mysql.exe -u "test-one" -p
Enter password: ********
ERROR 1045 (28000): Access denied for user 'test-one'@'localhost' (using password: YES)

Similar behaviour for

.\mysql.exe -u "test-one" -p -h 127.0.0.1

What does work is (replace with the actual hostname) .\mysql.exe -u "test-one" -p -h

Is this by design? The hostname wildcard '%' does not include localhost nor 127.0.0.1

theking2
  • 159
  • 1
  • 8

1 Answers1

1

This is true as long as anonymous users exist in the database. When you remove those users, localhost and 127.0.0.1 start matching the wildcards.

From the MariaDB Documentation:

Note that the credentials added when creating a user with the '%' wildcard host will not grant access in all cases. For example, some systems come with an anonymous localhost user, and when connecting from localhost this will take precedence.

These anonymous users can also be removed when the mysql_secure_installation script is run:

By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment.

Remove anonymous users? [Y/n]

markusjm
  • 316
  • 1
  • 6
  • Such anonymous user accounts are possibly there to slow down hackers? – Rick James Dec 18 '19 at 22:51
  • I guess it's possible but if I recall correctly those are removed by `mysql_secure_installation`. I can't really say what the true purpose of those users was originally but to me personally they seem to be a point of confusion most of the time. – markusjm Dec 20 '19 at 08:46