MariaDB accepts any password
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.
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.
also check your my.cnf if it has “skip-grant-tables” if yes, remove it.