未提供密码时如何禁用 MySQL root 登录？

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

提问by a coder

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.

MySQL 安装在我的笔记本电脑上并且运行良好，除了允许我在不提供 root 密码的情况下登录。我也可以通过提供 root 密码登录。如果提供的密码不匹配，则拒绝访问。最初安装 MySQL 时，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:

因此，当未提供密码时，我需要停止访问 root 帐户。到目前为止我尝试过的是使用以下方法重置 root 密码：

mysqladmin -u root password TopSecretPassword

mysqladmin -u root password TopSecretPassword

I then logged in to the console and issued:

然后我登录到控制台并发出：

mysql> flush privileges;  exit;

mysql> flush privileges;  exit;

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

我仍然可以使用以下命令登录 MySQL：

%> mysql -u  {enter}

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

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

采纳答案by coffeefiend

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.

就我而言，这是由于存在位于 ~/.my.cnf 的特定于用户的配置文件，其中包含用户和密码。就我而言，cPanel 创建了这个配置文件。

[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.

用户特定的配置文件是 MySQl 的一个特性，所有读取的配置文件的位置在文档中有详细说明：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:

如果您在 *nix dist 上运行 mysql，请运行以下命令以查看您是否有特定于用户的配置文件：

cat ~/.my.cnf 

回答by Jeremy Davis

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.

在较新版本的 MySQL/MariaDB（例如 Debian Stretch 等）中遇到此行为的用户应注意，在 mysql.user 表中，有一个名为“插件”的列。如果启用了 'unix_socket' 插件，那么 root 将能够通过命令行登录而无需密码。其他登录机制将被禁用。

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):

它应该返回这样的东西（启用了 unix_socket）：

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

To disable that and require root to use a password:

要禁用它并要求 root 使用密码：

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).

注意：正如@marioivangf（在评论中）在较新版本的 MySQL（例如 5.7.x）中所指出的，您可能需要将插件设置为“mysql_native_password”（而不是空白）。

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/3363571Big thanks to @SakuraKinomoto (please go up vote his answer if you find this useful).

来源：https: //stackoverflow.com/a/44301734/3363571 非常感谢@SakuraKinomoto（如果你觉得这有用，请投票给他的答案）。

回答by RolandoMySQLDBA

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.

这将显示您如何尝试登录以及 mysql 如何允许您登录。

Run these two queries:

运行这两个查询：

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

That should do it !!!

应该这样做！！！

CAVEAT #1

警告 #1

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

如果这不起作用，请检查 /etc/my.cnf 是否有此选项：

skip-grant-tables

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

如果它在 my.cnf 中，请将其删除并重新启动 mysql。

CAVEAT #2

警告#2

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

需要注意的另一件事是拥有多个 root 用户。请运行这个：

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 定义为有密码，但仍然以 root 身份进入，这表明有多个 root 用户。mysql.user 中可能有这些条目

• root@localhost
• [email protected]
• root@'hostnameofserver'

• 根@本地主机
• 根@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.

如果 root 用户没有密码，mysql 可能允许来自任何 root 用户的身份验证。这应该在您运行时表现出来，SELECT USER(),CURRENT_USER();因为每个函数的输出将显示为不同的。

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:

如果一个 root 用户拥有 MD5 密码而所有其他 root 用户都没有，您可以将该 MD5 密码传播给其他 root 用户，如下所示：

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

回答by SomeOne_1

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:

如果有人在搜索如何更改 root 密码和停止没有密码的 root 登录时遇到这篇文章，Percona 再次救了我的屁股：

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

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';

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