![授予特定服务器列表上的 MySQL 权限](https://linux22.com/image/577724/%E6%8E%88%E4%BA%88%E7%89%B9%E5%AE%9A%E6%9C%8D%E5%8A%A1%E5%99%A8%E5%88%97%E8%A1%A8%E4%B8%8A%E7%9A%84%20MySQL%20%E6%9D%83%E9%99%90.png)
假设我有一个 MySQL 服务器,我想向 10 个用户(user1、user2、...user10)授予一组(非平凡的)权限,每个用户可能来自 10 个不同的主机中的任何一个,这些主机无法通过通配符或网络掩码专门选择;例如:
- abc.foo.example.com
- def.bar.example.com
- ghi.baz.example.com
- ETC。
有没有一种简单的方法来管理这个问题,而不需要为每个不同的用户@主机组合维护 10x10=100 组 GRANT?
我能想到一些折衷方案:
使用更宽泛的通配符,如:“user1'@'%.example.com”。这包括所有必要的主机,但也允许来自 example.com 内其他主机的访问,其中一些主机可能是恶意的。(想象一下网络中有数千名学生的大学。)强密码可以缓解这个问题,但拥有额外的主机特定安全层仍然很好。
执行 #1,但也使用防火墙将 MySQL 端口限制到我们想要允许的 10 个特定主机。缺点是如果我们稍后想要添加第 11 个用户,并且该用户的 GRANT 非常受限,可以从任何地方访问:'user11'@'%'。
答案1
这是一个概念性的例子
假设你想创建一个用户列表
- 用户1
- 用户2
- ...
- 用户10
每个用户都可以使用这些前缀访问 example.com
- abc.foo
- 定义条形码
- 吉巴兹
并且每个用户都被授予
插入、更新、删除、选择
用户的密码是“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
试一试 !!!
答案2
我知道最接近你所问的是安全项目。它创建了一组存储过程和帮助程序,您可以使用它们在 MySQL 中创建伪角色并管理许多权限。请注意,我自己并没有真正使用过它;我只是饶有兴趣地看了博客文章。您可以在http://mysqlpreacher.com/wordpress/tag/securich/去年在MySQL大会上也提出过:http://en.oreilly.com/mysql2011/public/schedule/detail/17372虽然我找不到幻灯片,但 Sheeri K. Cabral 在 IOUG Collaborate 的演讲中提到过它:http://technocation.org/files/doc/MySQLSecurity2011_06.pdf
答案3
首先,我建议在您的 mysql 配置中禁用 DNS 解析。(skip-name-resolve 选项)如果您通过 FQDN(abc.bcd.example.com)而不是 IP 设置授权,那么当 DNS 查询无法按时运行时,您可能会遇到麻烦,并且用户将无法从您授予他的特权(或它,如果它是一个应用程序)中受益。
此外,使用 skip-name-resolve 时连接时间会更快,在 mysql 加载时您不能没有此选项。
对于 100 项授权,我将使用 shell 脚本来添加权限,如果您想查询它们,请使用 Percona Toolkit 的一部分 pt-show-grants。
希望这可以帮助