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.

Asked By: Forkbeard


The answer:

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.

Answered By: Forkbeard

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

Answered By: Ercan
Categories: Answers Tags: ,
Answers are sorted by their score. The answer accepted by the question owner as the best is marked with
at the top-right corner.