Mysql (MariaDB 10.0.29): Set root password, but still can login without asking password?

Question

I want to secure mysql by setting root password. I reset root password successfully:

MariaDB [(none)]> select Host, User, Password from mysql.user;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *58319282EAB9E38D49CA25844B73DA62C80C2ABC |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)

But, after flush privileges, restart Mysql, I can still login mysql (on local host) without typing password.

root@myhost:/# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.0.29-MariaDB SLE 12 SP1 package

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

How can I force mysql asking password when connect ? Thanks !

Answer 1

On MySQL table you have a column called plugin:

MariaDB [(none)]> SELECT host, user, password, plugin FROM mysql.user LIMIT 0,1;
+-----------+------+-------------------------------------------+--------+
| host      | user | password                                  | plugin |
+-----------+------+-------------------------------------------+--------+
| localhost | root | *                                         |        |
+-----------+------+-------------------------------------------+--------+
1 row in set (0.00 sec)

If I remember correctly the default plugin for mariaDB installations are 'console' or 'unix_socket', and this plugin allows you to enter without password, from console. But also disable authentication with password, and you cannot connect from another clients.

Simply update the plugin field with empty ('') value, and then, use FLUSH PRIVILEGES;

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
UPDATE mysql.user SET plugin = '' WHERE user = 'root' AND host = 'localhost';
FLUSH PRIVILEGES;

With this, you have the problem solved.

Answer 2

I found I needed to change this:

UPDATE mysql.user SET plugin = 'auth_socket' WHERE user = 'root' AND host = 'localhost';

To

 UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND host = 'localhost';

That is the plugin for a password AFAICT.

Answer 3

For Server version: 10.7.5-MariaDB

I solved this problem by following steps:

Using command SHOW GRANTS FOR 'root'@'localhost';

If you see a line similar to this one

GRANT ALL PRIVILEGES ON . TO root@localhost IDENTIFIED VIA mysql_native_password USING 'A_HASH_PASSWORD' OR unix_socket WITH GRANT OPTION

That means your root user can be authenticated by 2 methods password or unix_socket.

Now you can use command

GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY 'YOUR_RAW_PASSWORD' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Problem solved!

Answer 4

I know this question is old, but I had the same issue and the steps below solved my problem:

on the Linux terminal, run the command below to set a new password for root user, skip this step, if root password is already set.

mysqladmin --user=root password "newpassword"

Login to mysql

mysql -uroot -p

Enter 'root' user password

Run following query on MariaDB console

MariaDB [(none)]> select host,user,password from mysql.user;

You may notice some records with 'user' column values are ''(empty), delete those records using following query

MariaDB [(none)]> delete from mysql.user where user='';
MariaDB [(none)]> flush privileges;
Query OK, 2 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye
#$ mysql
ERROR 1045 (28000): Access denied for user ''@'localhost' (using password: NO)

You wont be able to login to mysql without password after following above steps.

Answer 5

This one worked for me.

After installation

sudo mysql

to login.

Then

alter user root@localhost identified via '';
flush privileges;
\q

Now you can login as root without password from localhost as had been the default behavior before.

mysql -uroot