How to disable MySQL login if password is not specified?

Question

MySQL is installed on my laptop and it works fine, except that I am allowed to log in without supplying a root password. I can also log in with the root password. If the incoming password does not match, it denies access. The root password was changed to my choice when I initially installed MySQL. I just noticed logins without a password today.

So, I need to stop access to the root account if no password is provided. I have tried so far to reset the root password:

  mysqladmin -u root password TopSecretPassword 

Then I went into the console and wrote:

  mysql> flush privileges;  exit 

I can still enter MySQL with:

  %> mysql -u {enter} 

How to 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

I know that this issue is several months old, but I had the same problem.

In my case, this was due to the presence of a user configuration file located in ~ / .my.cnf, which contained the user and password. In my case, cPanel created this configuration file.

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

Custom configuration files are a function of MySQl, and the location of all read configuration files is described in detail in the documentation: http://dev.mysql.com/doc/refman/5.1/en/option-files.html .

If you are running mysql in * nix dist, run the following command to check if you have a custom configuration file:

 cat ~/.my.cnf 

Answer 2

Users experiencing this behavior in newer versions of MySQL / MariaDB (for example, Debian Stretch, etc.) should be aware that there is a column named 'plugin' in the mysql.user table. If the 'unix_socket' plugin is enabled, then root will be able to log in via the command line without a password. Other login mechanisms will be disabled.

To check if this is 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 this and require root to use a password:

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

Note. As @marioivangf notes (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 empty).

Then restart:

 service mysql restart 

The problem is fixed!

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

Source: fooobar.com/questions/895782 / ... Thanks a lot @SakuraKinomoto (please raise your voice if you find this helpful).

Answer 3

It looks like you may have one or more anonymous users.

To see how they run this query:

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

To verify that you are authenticated as such, run this:

 SELECT USER(),CURRENT_USER(); 

This will show how you tried to log in and how mysql allowed you to log in .

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 it is in my.cnf, uninstall it and restart mysql.

CAVEAT # 2

Something else to look out for is a few root users. Run this:

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

If you define root to have a password and still fall under root, this indicates that there are several root users. There may be these entries in mysql.user

• root @ local
• root@127.0.0.1
• root @hostnameofserver

mysql can allow authentication from any of the root users if the root user does not have a password. This should occur when running SELECT USER(),CURRENT_USER(); , because the output of each function will be displayed as different.

If one root user has an MD5 password and all other root users do not, you can distribute this MD5 password to 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;