在特定的服务器列表上授予MySQL权限

假设我有一个MySQL服务器，并且我想授予10个用户（user1，user2，… user10）的一组（非平凡）权限，每个用户可能来自10个不同主机中的任何一个，通过通配符或networking掩码来具体select; 例如：

abc.foo.example.com
def.bar.example.com
ghi.baz.example.com
等等

有没有一种简单的方法来pipe理，而不必为每个不同的用户@主机组合保持10×10 = 100套GRANTs？

我可以想到一些妥协：

使用更广泛的通配符，如：'user1'@'%.example.com'。这包括所有必要的主机，但也允许example.com中的其他主机访问，其中一些可能是恶意的。（想象一下，在networking内部有数千名学生的大学。）强大的密码可以缓解这个问题，但是拥有额外的特定于主机的安全层仍然很不错。
做＃1，但也使用防火墙来限制MySQL端口到我们想要允许的10个特定的主机。缺点是，如果我们稍后想要添加一个非常有限的GRANT的第11个用户，可以从任何地方访问：'user11'@'％'。

这是一个概念性的例子

假设你想创build一个用户列表

USER1
用户2
…
user10

每个用户都将使用这些前缀访问example.com

abc.foo
def.bar
ghi.baz

并且每个用户都被授予

INSERT，UPDATE，DELETE，SELECT

而用户的密码是“whateveriwant”

这里是一个纯粹的查询MySQL查询

select CONCAT('GRANT ',Grants,' TO ''',User,'''@''',Host,'.example.com'' IDENTIFIED BY PASSWORD ''',pwd,''';') GrantCommand from (select "INSERT,UPDATE,DELETE,SELECT ON *.*" Grants) G, (SELECT PASSWORD('whateveriwant') pwd) P, ( select 'user1' User union select 'user2' union select 'user3' union select 'user4' union select 'user5' union select 'user6' union select 'user7' union select 'user8' union select 'user9' union select 'user10' ) U, ( select 'abc.foo' Host union select 'def.bar' union select 'ghi.baz' ) H ;

这里是在MySQL客户端中执行纯粹的MySQL查询

 mysql> select CONCAT('GRANT ',Grants,' TO ''',User,'''@''',Host,'.example.com'' IDENTIFIED BY PASSWORD ''',pwd,''';') GrantCommand -> from -> (select "INSERT,UPDATE,DELETE,SELECT ON *.*" Grants) G, -> (SELECT PASSWORD('whateveriwant') pwd) P, -> ( -> select 'user1' User -> union select 'user2' -> union select 'user3' -> union select 'user4' -> union select 'user5' -> union select 'user6' -> union select 'user7' -> union select 'user8' -> union select 'user9' -> union select 'user10' -> ) U, -> ( -> select 'abc.foo' Host -> union select 'def.bar' -> union select 'ghi.baz' -> ) H -> ; +------------------------------------------------------------------------------------------------------------------------------------------------+ | GrantCommand | +------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user1'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user1'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user1'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user2'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user2'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user2'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user3'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user3'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user3'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user4'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user4'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user4'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user5'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user5'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user5'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user6'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user6'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user6'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user7'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user7'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user7'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user8'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user8'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user8'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user9'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user9'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user9'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user10'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user10'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | | GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user10'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; | +------------------------------------------------------------------------------------------------------------------------------------------------+ 30 rows in set (0.00 sec) mysql>

只需将该查询放在文本文件中，如/root/MakeBulkUserGrants.sql

然后就跑吧

 mysql -uroot -hlocalhost -p < /root/MakeBulkUserGrants.sql > /root/BulkUserGrants.sql

试一试！！！

我所知道的最接近的东西是Securich项目。它创build了一组存储过程和帮助程序，您可以使用它们来创build伪angular色并在MySQL中pipe理许多权限。请注意，我自己并没有真正使用它。我刚刚看了博客文章感兴趣。您可以在http://mysqlpreacher.com/wordpress/tag/securich/阅读更多内容。 这也是去年在MySQL会议上提出的： http ://en.oreilly.com/mysql2011/public/schedule/detail/17372虽然我找不到幻灯片，Sheeri K. Cabral在她的文章中提到过在IOUG上进行交stream协作： http ： //technocation.org/files/doc/MySQLSecurity2011_06.pdf

首先，我build议在你的mysqlconfiguration中禁用DNSparsing。（skip-name-resolve选项）如果您通过FQDN（abc.bcd.example.com）而不是IP设置授权，则当DNS查询无法及时运行并且用户无法从特权中受益时你给他（或者，如果它是一个应用程序）。

另外，使用skip-name-resolve时，连接时间将会快得多，而在没有这个选项的情况下，mysql的负载是不可能的。

对于100个授权，我将使用shell脚本来添加权限，如果您想要查询它们，请使用percona工具包的一部分pt-show-grants。

希望这可以帮助