Cannot login to mariadb from user account. ERROR 1045 (28000)-CodePudding

I created one myaccount with a password and used : select user,password,host,plugin from mysql.user;

 --------------------- ------------------------------------------- ----------- ------------- 
| user                | password                                  | host      | plugin      |
 --------------------- ------------------------------------------- ----------- ------------- 
| root                | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | localhost | unix_socket |
| myaccount@localhost | *28F1DE8C4229B1D4F62F752EA3812747CE4E5CA0 | %         |             |
 --------------------- ------------------------------------------- ----------- -------------

As you can see, there is no plugin configured for myaccount. I was expecting this would mean that I'd be allowed to access the mariadb with my password, but when logging from my account which is called libra and not myaccount I get a :

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

How can I set the plugin attribute to allow password authentication from any account? myaccount doesn't exist as a user on my machine.

CodePudding user response：

This isn't an auth plugin issue.

There's no account named 'myaccount'@'localhost' on your database. There is an account named 'myaccount@localhost'@'%', however. Yeah, that name is strange. Yeah, the way one creates accounts on MariaDB / MySQL is strange.

Try this:

CREATE USER 'myaccount'@'localhost' IDENTIFIED BY 'REDACTED';
GRANT ALL PRIVILEGES ON *.* TO 'myaccount'@'localhost' WITH GRANT OPTION;
CREATE USER 'myaccount'@'%' IDENTIFIED BY 'REDACTED';
GRANT ALL PRIVILEGES ON *.* TO 'myaccount'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

That will set you up to log in both from localhost and any other host, to accounts with administrative privileges on the server. That requires, on MariaDB / MySQL, two accounts. Sigh. (If you don't want administrative privileges, change the GRANT statements.)

And, of course, replace REDACTED with the password you want.