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
To disable the MySQL Binary Logging, you can use the --skip-log-bin or --disable-log-bin option at startup.
If the root account has an empty password, your MySQL installation is unprotected: Anyone can connect to the MySQL server as root without a password and be granted all privileges. The 'root'@'localhost' account also has a row in the mysql.
In the mysql client, tell the server to reload the grant tables so that account-management statements work: mysql> FLUSH PRIVILEGES; Then change the 'root'@'localhost' account password. Replace the password with the password that you want to use.
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).
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
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
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;
Current answers are no longer ok for MySQL 8. Indeed:
In MySQL 8.0,
caching_sha2_password
is the default authentication plugin rather thanmysql_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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With