Admin password of MariaDb doesn't seem to work

I've just installed MariaDb on a fresh Ubuntu Gnome and ran mysql_secure_installation afterwards where I set a decent admin password, removed the anonymous user etc.

Afterwards I realized some strange behavior regarding the admin password:

• If I try to login from my normal user account using the command mysql -u root -p I always get an error: ERROR 1698 (28000): Access denied for user 'root'@'localhost'
  I'm pretty sure I entered the correct password which I set with mysql_secure_installation earlier...
• When I run the command from root using sudo mysql -u root -p, I always get access to the database, no matter which password I actually enter...

Is this normal behavior, am I doing anything wrong or have I somehow screwed up the installation?

Solution 1:

That behaviour sounds consistent with enabling the plugin for socket authentication for the root user, where MariaDB trusts operating system credentials received over the socket and does not rely on a password. By using sudo or logging on as root you can connect to the database server as root, because you're root on the OS, but other OS users can't.

You can remove that option with:

$ sudo mysql -u root

mysql> use mysql;
mysql> update user set plugin='' where User='root';
mysql> flush privileges;

and then you get the expected behaviour any user that has the MariaDB root password should be able to log on as root.

The alternative is to set up another user, not root, that also has full administrator privileges and use that, rather than root, for admin purposes:

$ sudo mysql -u root
mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost'
->     WITH GRANT OPTION;