未提供密码时如何禁用 MySQL root 登录? [英] How to disable MySQL root logins when no password is supplied?

问题描述

MySQL 安装在我的笔记本电脑上，它工作正常，除了我可以在不提供 root 密码的情况下登录.我也可以通过提供 root 密码登录.如果提供的密码不匹配，则拒绝访问.最初安装 MySQL 时，root 密码已更改为我自己选择的密码.我今天才注意到无密码登录.

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.

所以，如果没有提供密码，我需要停止对 root 帐户的访问.到目前为止，我尝试使用以下方法重置 root 密码:

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;

我仍然可以通过以下方式登录 MySQL:

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

%> mysql -u  {enter}

如何阻止这种行为?

其他细节:

%> 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

推荐答案

目前的答案不再适用于 MySQL 8.确实:

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

在 MySQL 8.0 中，caching_sha2_password 是默认的身份验证插件，而不是 mysql_native_password，后者是 MySQL 5.7 中的默认值.

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.

所以解决方法是运行mysql，然后

So the solution is to run mysql, then

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

然后 mysql -uroot -p 试试它是否有效.(我不记得是否需要 service mysql restart).

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

如果您已经应用了这个实际问题的主要答案中的技术，这里是如何为 MySQL 8 恢复它:

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;

这篇关于未提供密码时如何禁用 MySQL root 登录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！