mariadb直接可以mysql登录,Mysql（MariaDB 10.0.29）：设置root密码，但仍然可以登录而无需询问密码？...

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 !

解决方案

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.