Couldn't login without sudo on MariaDB 10.5

Question

For some unknown reason when I installed mariadb (currently ver 10.5 on my Manjaro linux). it wouldn't let me login without sudo. So i investigated (and i used that that authentication plugin unix_socket and mysql_native_password etc thing; to no avail unfortunately, because user table is no longer a table but a view); later I find out using MariaDB authentication mariadb documentation; that the password for root and mysql are invalid in the mysql.grobal_priv table.

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mypass');

and also changed mysql password

SET PASSWORD FOR 'mysql'@'localhost' = PASSWORD('pass');

Now i can login without password, but my question is

1. why did it happened. I did set the password on mysql_secure_installation command.
2. Is setting this way (which sets authentication_string and Password same) is all I needed to do.

---

FYI: I did used special symbols !@ in my password. Maybe bash console had some part in this invalid password initialization. Just guessing

Answer 1

For a brute-force approach on MariaDB < 10.4 (for later versions see MariaDB-10.4+ user control for better methods - eg. "SET PASSWORD" or "ALTER USER" etc.!):

In answer to question 1: The default access for a new MariaDB install is root IDENTIFIED VIA unix_socket (hence sudo required). So even though you did set a password during the install, it isn't used (i.e. invalid). It is done this way to be secure (no remote access), and also so that other processes running as root can access the server without a password (upgrade checks etc.).

In answer to question 2: Yes, it is that simple, see below. But be aware that you may need to update the password for the system maintenance task - check your logs (systemctl status mariadb.service).

To change for 'root' anyway:

GRANT ALL PRIVILEGES on *.* to 'root'@'localhost' IDENTIFIED BY '<password>';
FLUSH PRIVILEGES;

or

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin = 'unix_socket';
FLUSH PRIVILEGES;

Then restart mariadb:

systemctl restart mariadb

And voila you will have access from your personal account via "mysql -u root -p"

To change back:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION;
FLUSH PRIVILEGES;

I generally find that it is better to leave the 'root' user as is (socket), and simply create a new "root" user ("super", "administrator", "superadmin" etc.) with GRANT ALL PRIVILEGES ON ., which uses a password to authenticate if required..

GRANT ALL PRIVILEGES ON *.* TO 'super'@'localhost' IDENTIFIED BY '<password>' WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'super'@'localhost' WITH GRANT OPTION
FLUSH PRIVILEGES;