How to disable MySQL root logins when no password is supplied?

Question

MySQL is installed on my laptop and it works fine, except that I am allowed to log in without supplying the root password. I can also log in by supplying the root password. If the supplied password doesn't match, it denies access. The root password was changed to something of my own choosing when I originally installed MySQL. I just noticed the no-password logins today.

So, I need to stop access to the root account when a password isn't supplied. What I've tried so far is to reset the root password with:

mysqladmin -u root password TopSecretPassword

I then logged in to the console and issued:

mysql> flush privileges;  exit;

I'm still able to log in to MySQL with:

%> mysql -u  {enter}

How do I stop this behavior?

ADDITIONAL DETAILS:

%> mysql -u  {enter}

mysql>SELECT USER(), CURRENT_USER();
> root@localhost, root@localhost

mysql>SELECT COUNT(*) FROM mysql.users WHERE user='root' AND password='';
> COUNT(*)
> 0

mysql>SELECT COUNT(*) FROM mysql.users WHERE user='';
> COUNT(*)
> 0

mysql>SELECT COUNT(*) FROM mysql.users WHERE user='root';
> COUNT(*)
> 1

%> vi /etc/my.cnf
/skip-grant-tables
> E486: Pattern not found: skip-grant-tables

Answer 1

Users encountering this behaviour in newer versions of MySQL/MariaDB (e.g. Debian Stretch, etc) should be aware that in the mysql.user table, there is column called 'plugin'. If the 'unix_socket' plugin is enabled, then root will be able to log in via commandline without requiring a password. Other log in mechanisms will be disabled.

To check if that's the case:

SELECT host, user, password, plugin FROM mysql.user;

which should return something like this (with unix_socket enabled):

+-----------+------+--------------------------+-------------+
| host      | user | password                 | plugin      |
+-----------+------+--------------------------+-------------+
| localhost | root | <redacted_password_hash> | unix_socket |
+-----------+------+--------------------------+-------------+

To disable that and require root to use a password:

UPDATE mysql.user SET plugin = '' WHERE user = 'root' AND host = 'localhost';
FLUSH PRIVILEGES;

Note: As noted by @marioivangf (in a comment) in newer versions of MySQL (e.g. 5.7.x) you may need to set the plugin to 'mysql_native_password' (rather than blank).

Then restart:

service mysql restart

Problem fixed!:

root@lamp ~# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Source: https://stackoverflow.com/a/44301734/3363571 Big thanks to @SakuraKinomoto (please go up vote his answer if you find this useful).

Answer 2

Looks like you may have one or more anonymous users.

To see them run this query:

SELECT user,host,password FROM mysql.user WHERE user='';

To see that you authenticated as such, run this:

SELECT USER(),CURRENT_USER();

This will show how you tried to login and how mysql allowed you to login.

Run these two queries:

DELETE FROM mysql.user WHERE user='';
FLUSH PRIVILEGES;

That should do it !!!

CAVEAT #1

If this does not work, check /etc/my.cnf for this option:

skip-grant-tables

If that is in my.cnf, remove it and restart mysql.

CAVEAT #2

Something else to watch out for is having multiple root users. Please run this:

SELECT user,host,password FROM mysql.user WHERE user='root';

If you defined root to have a password and still get in as root, this is indicative of having multiple root users. There may be these entries in mysql.user

• root@localhost
• root@127.0.0.1
• root@'hostnameofserver'

mysql may allow authentication from any of the root users if a root user has no password. This should manifest itself when you run SELECT USER(),CURRENT_USER(); because the output of each function will show up as different.

If one root user has the MD5 password and all other root users do not, you can spread that MD5 password to the other root users as follows:

UPDATE mysql.user
SET password = 
(
    SELECT password FROM mysql.user
    WHERE user='root' AND password <> ''
)
WHERE user='root' AND password = '';
FLUSH PRIVILEGES;

Answer 3

I know this question is a few months old, but I had the same issue.

In my case, it was due to the presence of a user-specific configuration file located at ~/.my.cnf that contained the user and password. In my case, cPanel created this config file.

[client]
pass="ROOT_PASSWORD_WAS_HERE!"
user=root

User-specific configuration files are a feature of MySQl, and the location of all the config files read are detailed in the documentation: http://dev.mysql.com/doc/refman/5.1/en/option-files.html.

If you're running mysql on a *nix dist, run the following command to see if you have a user-specific config file:

cat ~/.my.cnf 

Answer 4

Current answers are no longer ok for MySQL 8. Indeed:

In MySQL 8.0, caching_sha2_password is the default authentication plugin rather than mysql_native_password, which was the default in MySQL 5.7.

So the solution is to run mysql, then

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd'; EXIT;

and then mysql -uroot -p to try if it worked. (I don't remember if service mysql restart was necessary).

If you already applied the technique from the main answer of this actual question, here is how to revert it for MySQL 8:

UPDATE mysql.user SET plugin = 'caching_sha2_password' WHERE user = 'root' AND host = 'localhost'; 
FLUSH PRIVILEGES;

Answer 5

If anyone comes across this post while searching how to change root password and stop root-logins without password, Percona saved my ass once again:

https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/

basically the command that worked for me:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';