Mysql (MariaDB 10.0.29): Set root password, but st

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

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 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

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.