从 MySQL 数据库中删除权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17799806/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Remove privileges from MySQL database
提问by Cyntech
Before you think this is a duplicate question, I believe I have a unique, even if it is somewhat dim-witted, case.
在你认为这是一个重复的问题之前，我相信我有一个独特的案例，即使它有点愚蠢。
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 failederror.
几天前，我将我的 Ubuntu 10.04 服务器上的 MySQL 版本升级到 5.3.3（它领先于 10.04 的 Ubuntu 版本）。今天，我尝试登录 phpMyAdmin 并发现了一个有点可怕的Connection for controluser as defined in your configuration failed错误。
After following descriptions from several SO questions on how to fix this, I have become stuck.
在遵循了几个关于如何解决这个问题的 SO 问题的描述之后，我陷入了困境。
- I attempted to reconfigure phpMyAdmin, with no success.
- I attempted to uninstall phpMyAdmin and reinstallit, but it couldn't remove the privileges from the DB and failed.
- I then attempted to manually remove the privilegesof 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，但没有成功。
- 我试图卸载 phpMyAdmin 并重新安装它，但它无法从数据库中删除权限并且失败了。
- 然后我尝试手动删除用户的权限- 有点愚蠢，我可能会添加 - 从数据库中删除，然后删除数据库，然后删除用户（使用
flush privileges）。 - 我完全放弃了 phpMyAdmin 的整个安装（删除应用程序和 /etc/phpmyadmin 目录）并重新安装（使用 apt-get），但它说 phpmyadmin 用户的权限已经存在：
granting access to database phpmyadmin for phpmyadmin@localhost: already exists
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'
(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).
我如何完全删除这些授权/特权？如果需要，我很高兴从头开始（即phpmyadmin，而不是数据库）。
回答by Flo Doe
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.*.
mysql 中的 USAGE-privilege 仅意味着在 global level 上定义的用户 'phpadmin'@'localhost' 没有权限*.*。此外，同一用户对数据库 phpmyadmin 具有 ALL 权限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';Drop the user 'phpmyadmin'@'localhost'
DROP USER 'phpmyadmin'@'localhost';
撤销数据库级别的所有权限：
REVOKE ALL PRIVILEGES ON phpmyadmin.* FROM 'phpmyadmin'@'localhost';删除用户 'phpmyadmin'@'localhost'
DROP USER 'phpmyadmin'@'localhost';
Above procedure will entirely remove the user from your instance, this means you can recreate him from scratch.
上述过程将从您的实例中完全删除用户，这意味着您可以从头开始重新创建他。
To give you a bit background on what described above: as soon as you create a user the mysql.usertable 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 ALLto 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.
为您提供有关上述内容的一些背景知识：一旦您创建用户，该mysql.user表将被填充。如果您查看其中的记录，您将看到用户和所有权限设置为'N'。如果你做一个show grants for 'phpmyadmin'@'localhost';你会看到，上面已经很熟悉了，输出。简单地翻译为“用户没有全局级别的权限”。现在您ALL在数据库级别授予此用户的权限，这将存储在 table 中mysql.db。如果您执行 a，SELECT * FROM mysql.db WHERE db = 'nameofdb';您将'Y'在每个 priv 上看到 a 。
Above described shows the scenario you have on your db at the present. So having a user that only has USAGEprivilege means, that this user can connect, but besides of SHOW GLOBAL VARIABLES; SHOW GLOBAL STATUS;he has no other privileges.
上面描述的显示了您目前在数据库上的情况。所以拥有一个只有USAGE特权的用户意味着，这个用户可以连接，但除此之外SHOW GLOBAL VARIABLES; SHOW GLOBAL STATUS;他没有其他特权。
回答by RolandoMySQLDBA
As a side note, the reason revoke usage on *.* from 'phpmyadmin'@'localhost';does not work is quite simple : There is no grant called USAGE.
作为旁注，revoke usage on *.* from 'phpmyadmin'@'localhost';不起作用的原因很简单：没有名为USAGE.
The actual named grants are in the MySQL Documentation
The grant USAGEis a logical grant. How? 'phpmyadmin'@'localhost' has an entry in mysql.userwhere user='phpmyadmin' and host='localhost'. Any row in mysql.user semantically means USAGE. Running DROP USER 'phpmyadmin'@'localhost';should work just fine. Under the hood, it's really doing this:
授予USAGE是逻辑授予。如何？'phpmyadmin'@'localhost'mysql.user在 user='phpmyadmin' 和 host='localhost' 中有一个条目。mysql.user 中的任何行在语义上都意味着USAGE. 跑步DROP USER 'phpmyadmin'@'localhost';应该没问题。在幕后，它确实是这样做的：
DELETE FROM mysql.user WHERE user='phpmyadmin' and host='localhost';
DELETE FROM mysql.db WHERE user='phpmyadmin' and host='localhost';
FLUSH PRIVILEGES;
Therefore, the removal of a row from mysql.userconstitutes running REVOKE USAGE, even though REVOKE USAGEcannot literally be executed.
因此，即使不能从字面上执行，从行中移除也mysql.user构成 running 。REVOKE USAGEREVOKE USAGE
