7

Running MariaDB 10.1.23-MariaDB-9+deb9u1 on Debian 9.1. Fresh OS installation, installed MariaDB with apt-get install mariadb-server mariadb-client

Apparently MariaDB doesn't ask for a root password on install so I'm going to set it after the fact:

# mysql -uroot
> select user from mysql.user;
+------+
| user |
+------+
| root |
+------+

Ok, so root exists. Now to change its password:

> set password for 'root'@'localhost' = PASSWORD('P@ssw0rd');
> flush privileges;
> exit

Did it work?

# mysql -uroot -pblabla
MariaDB [(none)]>

Setting the password went ok but why is MariaDB accepting any random password and even an empty one?

This installation doesn't accept ALTER USER statement.

Forkbeard
  • 597
  • 8
  • 18
  • Let us [continue this discussion in chat](http://chat.stackexchange.com/rooms/63605/discussion-between-forkbeard-and-derobert). – Forkbeard Aug 10 '17 at 12:33

2 Answers2

17

The answer: https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/:

Apparently the mysql-server installation on 16.04 (or any 5.7 installation?) allows root access not through password, but through the auth_socket plugin. Running sudo mysql -u root (n.b. w/o a password) will give you mysql console whereas running the command as non-root prompts you for a password.

It would seem that changing the password doesn't make much of a difference since the auth backend doesn't even check for a password.

To disable this auth_socket plugin, on the mysql prompt do

update mysql.user set plugin=null where user='root';
flush privileges;

This makes MariaDB also ask a password for [Linux] root.

Thanks jesse-b and derobert for the in-depth discussion and your answers.

Forkbeard
  • 597
  • 8
  • 18
1

also check your my.cnf if it has "skip-grant-tables" if yes, remove it.

Ercan
  • 111
  • 2