从 MySQL 数据库中删除权限 [英] Remove privileges from MySQL database

问题描述

在你认为这是一个重复的问题之前，我相信我有一个独特的案例，即使它有点愚蠢.

Before you think this is a duplicate question, I believe I have a unique, even if it is somewhat dim-witted, case.

几天前，我将我的 Ubuntu 10.04 服务器上的 MySQL 版本升级到 5.3.3(它领先于 10.04 的 Ubuntu 版本).今天，我尝试登录 phpMyAdmin 进行某些操作，但发现了有点可怕的在您的配置中定义的 controluser 连接失败 错误.

A few days ago, I upgraded the version of MySQL on my Ubuntu 10.04 server to 5.3.3 (it's ahead of the Ubuntu releases for 10.04). Today, I attempted to log into phpMyAdmin for something and discovered the somewhat dreaded Connection for controluser as defined in your configuration failed error.

在遵循几个关于如何解决这个问题的 SO 问题的描述之后，我陷入了困境.

After following descriptions from several SO questions on how to fix this, I have become stuck.

• 我尝试重新配置 phpMyAdmin，但没有成功.
• 我尝试卸载 phpMyAdmin 并重新安装，但无法从数据库中删除权限并失败.
• 然后我尝试手动删除权限 用户 - 有点愚蠢，我可能会添加 - 从数据库中添加，然后删除数据库，然后是用户(具有 flush 权限).
• 我完全放弃了 phpMyAdmin 的整个安装(删除应用程序和/etc/phpmyadmin 目录)并重新安装(使用 apt-get)，但它说 phpmyadmin 用户的权限已经存在:

• I attempted to reconfigure phpMyAdmin, with no success.
• I attempted to uninstall phpMyAdmin and reinstall it, but it couldn't remove the privileges from the DB and failed.
• I then attempted to manually remove the privileges of the user - somewhat foolishly, I might add - from the DB, then dropping the db, then the user (with flush privileges).
• I dropped the whole install of phpMyAdmin completely (deleting the application and the /etc/phpmyadmin directory) and reinstalled (using apt-get) but it said the permissions for the phpmyadmin user already existed:

为 phpmyadmin@localhost 授予对数据库 phpmyadmin 的访问权限:已经存在

所以，这就是我剩下的.我有一个不能修改也不能撤销的授权:

So, here is what I'm left with. I have a grant that I cannot modify, nor revoke:

mysql> show grants for 'phpmyadmin'@'localhost';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for phpmyadmin@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'phpmyadmin'@'localhost' IDENTIFIED BY PASSWORD '*46CFC7938B60837F46B610A2D10C248874555C14' |
| GRANT ALL PRIVILEGES ON `phpmyadmin`.* TO 'phpmyadmin'@'localhost'                                                |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.26 sec)

mysql> revoke usage on *.* from 'phpmyadmin'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'phpmyadmin' on host 'localhost'

mysql> revoke usage on *.* from 'phpmyadmin'@'localhost' identified by 'trustno1';
ERROR 1141 (42000): There is no such grant defined for user 'phpmyadmin' on host 'localhost'

(别担心，我不再使用这个密码了，但它是以前使用的密码，不是我为新的 phpmyadmin 安装选择的密码).

(Don't worry, I do not use this password anymore, but it was the password that was used previously and it is not the password I chose for the new phpmyadmin installation).

我如何完全删除这些授权/特权?如果需要，我很高兴从头开始(即 phpmyadmin，而不是数据库).

How do I totally remove these grants/privileges? I am happy to start again from scratch if need be (phpmyadmin that is, not the DB).

推荐答案

mysql 中的 USAGE-privilege 仅仅意味着在全局级别定义的用户 'phpadmin'@'localhost' 没有权限 *.*.此外，同一用户对数据库 phpmyadmin phpadmin.* 具有 ALL 权限.

The USAGE-privilege in mysql simply means that there are no privileges for the user 'phpadmin'@'localhost' defined on global level *.*. Additionally the same user has ALL-privilege on database phpmyadmin phpadmin.*.

因此，如果您想删除所有权限并完全从头开始，请执行以下操作:

So if you want to remove all the privileges and start totally from scratch do the following:

• 撤销数据库级别的所有权限:

• Revoke all privileges on database level:

撤销所有 phpmyadmin 权限.* from 'phpmyadmin'@'localhost';

删除用户 'phpmyadmin'@'localhost'

Drop the user 'phpmyadmin'@'localhost'

DROP USER 'phpmyadmin'@'localhost';

以上过程会将用户从您的实例中完全删除，这意味着您可以从头开始重新创建他.

Above procedure will entirely remove the user from your instance, this means you can recreate him from scratch.

为了让您了解上述内容的一些背景知识:一旦您创建了一个用户，mysql.user 表就会被填充.如果您查看其中的记录，您将看到设置为 'N' 的用户和所有权限.如果您执行 show grants for 'phpmyadmin'@'localhost'; 您将看到上面已经熟悉的输出.简单地翻译为用户没有全局级别的权限".现在您在数据库级别授予此用户ALL，这将存储在表mysql.db 中.如果你执行 SELECT * FROM mysql.db WHERE db = 'nameofdb'; 你会在每个 priv 上看到一个 'Y' .

To give you a bit background on what described above: as soon as you create a user the mysql.user table will be populated. If you look on a record in it, you will see the user and all privileges set to 'N'. If you do a show grants for 'phpmyadmin'@'localhost'; you will see, the allready familliar, output above. Simply translated to "no privileges on global level for the user". Now your grant ALL to this user on database level, this will be stored in the table mysql.db. If you do a SELECT * FROM mysql.db WHERE db = 'nameofdb'; you will see a 'Y' on every priv.

以上描述显示了您目前在数据库上的情况.所以拥有一个只有 USAGE 权限的用户意味着，这个用户可以连接，但除了 SHOW GLOBAL VARIABLES;SHOW GLOBAL STATUS; 他没有其他权限.

Above described shows the scenario you have on your db at the present. So having a user that only has USAGE privilege means, that this user can connect, but besides of SHOW GLOBAL VARIABLES; SHOW GLOBAL STATUS; he has no other privileges.

这篇关于从 MySQL 数据库中删除权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！