从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(领先于Ubuntu 10.04版本).今天，我尝试登录phpMyAdmin并发现了一些令人恐惧的Connection for controluser as defined in your configuration failed错误.

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.

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

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

• 我尝试重新配置phpMyAdmin ，但没有成功.
• 我尝试卸载phpMyAdmin并重新安装，但是它无法从数据库中删除特权，但失败了.
• 然后我尝试手动删除特权的用户-有点愚蠢，我可能会添加-从数据库中添加，然后删除db，然后是用户(使用flush privileges).
• 我完全放弃了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:

granting access to database phpmyadmin for phpmyadmin@localhost: already exists

所以，这就是我剩下的.我有一笔赠款，我无法修改或撤销:

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特权仅表示在全局级别*.*上定义的用户'phpadmin'@'localhost'没有特权.此外，同一用户在数据库phpmyadmin phpadmin.*上具有ALL-privilege.

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:

REVOKE ALL PRIVILEGES ON 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屋！