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

user_445986 0
Viewed 35536

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 !

mariadb passwords authentication mysql
5 Answers

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.

user_115804940

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.

user_53181290

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
CDR_505420

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 FLUSH PRIVILEGES;

Problem solved!

user_169121720
Related