☰

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

tag-icon • tag-icon mysql permissions
授予特定服务器列表上的 MySQL 权限

假设我有一个 MySQL 服务器，我想向 10 个用户（user1、user2、...user10）授予一组（非平凡的）权限，每个用户可能来自 10 个不同的主机中的任何一个，这些主机无法通过通配符或网络掩码专门选择；例如：

  • abc.foo.example.com
  • def.bar.example.com
  • ghi.baz.example.com
  • ETC。

有没有一种简单的方法来管理这个问题，而不需要为每个不同的用户@主机组合维护 10x10=100 组 GRANT？

我能想到一些折衷方案：

  1. 使用更宽泛的通配符，如：“user1'@'%.example.com”。这包括所有必要的主机，但也允许来自 example.com 内其他主机的访问，其中一些主机可能是恶意的。（想象一下网络中有数千名学生的大学。）强密码可以缓解这个问题，但拥有额外的主机特定安全层仍然很好。

  2. 执行 #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。

希望这可以帮助

相关内容