MySQL allows connection without password although password is set

Question

I'm setting a MySQL server (actually a Percona server, but that shouldn't matter) and I'm setting a password to the root user. At the end, I have this:

mysql> select host, user, password from user;
+-----------+------------------+-------------------------------------------+
| host      | user             | password                                  |
+-----------+------------------+-------------------------------------------+
| localhost | root             | *huge string here, no kidding             |
| localhost | debian-sys-maint | *another huge string here                 |
+-----------+------------------+-------------------------------------------+
2 rows in set (0.00 sec)

I thought this should not allow the root user to connect without a password. However, if I go to the command line, I can connect with mysql -u root or just mysql. If I do mysql -u root -p and hit enter for the password, then I get ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO).

Could anyone explain to me how to make sure a user can only connect with a password?

Edit: if relevant, I set the password with SET PASSWORD FOR 'root'@'localhost' = PASSWORD('somethinghere');

Edit: output of show grants, it indicates I used a password to login but I did not.

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*huge string here, no kidding' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Did you do a FLUSH PRIVILEGES; in MySQL after setting the password? You can also try doing mysqladmin -u root reload and mysqladmin -u root refresh (no password since that's how it seems to want to work at the moment). — Mara Ormston
– Mara Ormston, Commented Apr 7, 2013 at 18:42
yes, I do a FLUSH PRIVILEGES right after setting them. I ran these two commands you mentioned and nothing changed — Marcelo Diniz
– Marcelo Diniz, Commented Apr 7, 2013 at 18:43
Hmm... if none of that is working, can you just restart MySQL and see if that makes a difference? Not sure what Percona uses for that, but in Debian you do a sudo /etc/init.d/mysql restart and in CentOS/RHEL you do sudo service mysqld restart — Mara Ormston
– Mara Ormston, Commented Apr 7, 2013 at 18:45
Ok, let's look at how you tried to set the password then. The way I do it is with: sudo mysqladmin -u root password 'test' assuming I wanted a stupid password of just test - What did you use to set it? — Mara Ormston
– Mara Ormston, Commented Apr 7, 2013 at 18:47

Marcelo Diniz · Accepted Answer · 2013-04-08 09:18:29Z

14

Facepalm. It turned out there was a .my.cnf on /root with username and password, and so it was possible to login only with mysql when using the root account (that's what I was using). It was created by a Chef recipe (percona was installed via Chef) and I wasn't aware of it.

The hint was to look at the output of show grants. Even though I entered no password it still said I entered one, so there must be one somewhere!

answered Apr 8, 2013 at 9:18

Marcelo Diniz

2,5072 gold badges22 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL allows connection without password although password is set

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related