8

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?

suamikim
  • 183
  • 1
  • 1
  • 3

2 Answers2

19

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;
HBruijn
  • 72,524
  • 21
  • 127
  • 192
0

The answer from HBruijn specified to use

update user set plugin='' where User='root';

However, this broke the login system for me, as no one was able to login anymore (whether using correct password or not, and sudo or not)

For me to get it working I needed to use this instead:

update user set plugin="mysql_native_password" where User='root';
Alex
  • 11
  • 2