Mysql 删除用户并授予多个主机 [英] Mysql drop user with grants for multiple hosts
问题描述
我向我的 mysql-db 添加了一个用户,并授予从多个主机访问的权限,例如:
I added a user to my mysql-db with grants to access from several hosts, like:
GRANT ALL PRIVILEGES ON `db`.* TO 'dbuser'@'host1';
GRANT ALL PRIVILEGES ON `db`.* TO 'dbuser'@'host2';
GRANT ALL PRIVILEGES ON `db`.* TO 'dbuser'@'host3';
GRANT ALL PRIVILEGES ON `db`.* TO 'dbuser'@'host4';
....
完全删除用户的最短方法是什么?我试过了:
What is the shortest way to remove the user completly? I tried:
drop user 'dbuser'@'%';
ERROR 1396 (HY000): Operation DROP USER failed for 'dbuser'@'%'
drop user 'dbuser';
ERROR 1396 (HY000): Operation DROP USER failed for 'dbuser'@'%'
show grants for 'dbuser';
ERROR 1141 (42000): There is no such grant defined for user 'dbuser' on host '%'
我想,% 将被视为通配符.Butr似乎是为每个主机删除用户的唯一方法,例如:
I thought, % wouzld be treated as a wildcard. Butr the only way seems to drop the user for every host, like:
drop user 'dbuser'@'host1';
drop user 'dbuser'@'host2';
drop user 'dbuser'@'host3';
...
难道没有更方便的方法来删除用户吗?
Isn't there a more convenient way to remove the user?
推荐答案
MySQL <= 5.6.x
MySQL <= 5.6.x
select user,host,password from mysql.user;
MySQL >=5.7.x
MySQL >=5.7.x
select user,host,authentication_string from mysql.user;
以上您将需要创建一个回滚计划,相信我,如果删除大量用户并且您想节省时间,您总是需要一个回滚计划.
The above you will need to create a rollback plan and believe me you will always need one if deleting a lot of users and you want to save time.
现在回答您的问题:
select concat("DROP USER ","'",user,"'@'",host,"';") from mysql.user where host like "127.0.%" or host like "192.168%";
根据您的平台,探索如何将输出粘贴到文件中并执行
Depending on your platform , explore how to stick the output into a file and execute it
这篇关于Mysql 删除用户并授予多个主机的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!