Mysql (MariaDB 10.0.29): set the root password, but can you log in without asking for the password?

Question

I want to protect mysql by setting the 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 enter mysql (on the local host) without entering a 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 get mysql to ask for a password when connecting? Thanks!

Answer 1

In the 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 is 'console' or 'unix_socket', and this plugin allows you to enter without a password from the console. But also disable password authentication, and you will not be able to connect from other clients.

Just update the plugin field with an 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 a problem resolved.

Answer 2

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

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

 mysqladmin --user=root password "newpassword" 

Log in to MySQL

 mysql -uroot -p 

Enter root password

Run the following query on the MariaDB console

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

You may notice that some entries with the values ​​of the 'user' column are '' (empty), delete these entries using the 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 will not be able to enter MySQL without a password after performing the above steps.

Answer 3

I found that I need 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'; 

This is the AFAICT password plugin.