1

When I create a new user in MariaDB, I cannot login as that user.

First I login to the database as root doing mariadb in a bash terminal.

I then run the following commands to create a database and a user:

CREATE DATABASE mydb;
grant all privileges on mydb.* TO 'myuser'@'localhost' identified by 'password';
FLUSH PRIVILEGES;

When I then run SELECT user,host,plugin FROM mysql.user;, it prints the following:

+-----------+-----------+-------------+
| user      | host      | plugin      |
+-----------+-----------+-------------+
| root      | localhost | unix_socket |
| myuser    | localhost |             |
+-----------+-----------+-------------+

However, when I then try to login as that user doing mariadb -u myuser mydb -p, I have no luck.

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

I have no idea what is happening.

I am running the following MariaDB version:

mysqld  Ver 10.1.44-MariaDB-0ubuntu0.18.04.1 for debian-linux-gnu on x86_64 (Ubuntu 18.04)
Improve this question

4 Answers 4

Reset to default
1

Try granting to 'myuser'@'127.0.0.1', @'::1', or even @'%'. In your logs, you will see 'Access denied for user' and what IP address you were trying to connect from.

Improve this answer
9
  • I am trying to connect from localhost.
    – Jeroen
    Commented Mar 30, 2020 at 11:26
  • 1
    I believe you, but it is important to find out what mysql sees as your IP.
    – Gerard H. Pille
    Commented Mar 30, 2020 at 11:29
  • 1
    OK, add '-h 127.0.0.1' and '-P 3306' to your connection.
    – Gerard H. Pille
    Commented Mar 30, 2020 at 11:37
  • 1
    Can you check if your database is listening on port 3306? "Never does it mention any failed or successful connection attempts." Either you don't log warnings, or the database never notices you try to connect. Try "curl 127.0.0.1:3306".
    – Gerard H. Pille
    Commented Mar 30, 2020 at 11:46
  • 1
    When I check on my mariadb, I don't have a plugin for root. Can you try "UPDATE mysql.user SET plugin = 'mysql_native_password', authentication_string = PASSWORD('password') WHERE User = 'myuser';" ?
    – Gerard H. Pille
    Commented Mar 30, 2020 at 13:12
0

The -p password prompt seems to be broken. Supplying the password on the command line works as it should.

mariadb -u myuser mydb --password="password"
Improve this answer
5
  • 2
    Does your password contain special characters?
    – Gerard H. Pille
    Commented Mar 30, 2020 at 13:18
  • @GerardH.Pille No, it is just a string of alphanumeric characters. I am using a fresh Ubuntu 18.10 installation too, with pretty much only mariadb installed, so there shouldn't be any silly conflicts between programs too. I am lost for words, frankly.
    – Jeroen
    Commented Mar 30, 2020 at 13:21
  • 1
    It's just that I find it hard to believe password entry to be broken. That would generate a lot of noise. I'll keep looking.
    – Gerard H. Pille
    Commented Mar 30, 2020 at 13:24
  • 1
    Can you try to copy the password, and then "paste" it when you have the password prompt? In case there would be a problem with the keyboard, or between the keyboard and the chair? ;-)
    – Gerard H. Pille
    Commented Mar 30, 2020 at 14:33
  • I have tried copy-pasting, using ctrl+v, using shift+insert, and using right click paste.
    – Jeroen
    Commented Mar 30, 2020 at 15:10
0

If your password contains special characters, you might want to give alpha-numeric password a try. Worked for me.

Improve this answer
2
  • 1
    Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Jan 8, 2023 at 20:23
  • As I commented on the other answer, my password was alphanumeric.
    – Jeroen
    Commented Jan 16, 2023 at 19:40
0

You first need to grant usage for the user, this effectively grants login privileges in MariaDB:

GRANT USAGE ON *.* TO 'myuser' IDENTIFIED BY 'your_pwd';

Then you can start granting the user privileges to databases:

GRANT ALL PRIVILEGES ON 'my_db'.* TO 'myuser' IDENTIFIED BY 'your_pwd';
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.