授予具有特定前缀的多个表的权限

Question

我正在使用表前缀方法，使多个客户端使用同一个数据库。每个客户端创建的表数量将约为55张。我想知道是否可以像以下方式一样，而不是手动列出所有表格来完成授权操作：

GRANT SELECT,INSERT,UPDATE,DELETE ON database.prefix_* TO 'username'@'localhost' IDENTIFIED BY 'password';

- Chad

---

很奇怪。这里有一个相同的问题，得到了积极的回答。链接 - Pavel Strakhov

@PavelStrakhov 我知道你的评论有点老了，但是这个问题是关于表通配符（MySQL不支持）的，而你链接的问题是关于数据库通配符（支持）。 - leemes

Answer 1

重要提示：这不是我的答案。我在http://lists.mysql.com/mysql/202610上找到了它，并为了简化而复制粘贴，特此感谢Stephen Cook。

您可以使用INFORMATION_SCHEMA.TABLES视图来生成GRANT语句。编写类似以下的查询：

SELECT   CONCAT('GRANT SELECT ON test.', TABLE_NAME, ' to ''foouser'';')
FROM     INFORMATION_SCHEMA.TABLES
WHERE    TABLE_SCHEMA = 'test'
      AND TABLE_NAME LIKE 'foo_%'

然后运行它，复制结果，并将这些结果作为查询或脚本运行。当然，您可以尽情发挥，例如如果您为多个用户执行此操作，可以编写一个接受用户名参数的存储过程，因此可以在需要时用作工具。

虽然这不是您要求的语法，但这是一个有效的技巧。

--

将表模式“test”替换为您的数据库名称。foo_％可以替换为适当的前缀_％。

我自己尝试了这种方法，效果很好。

- CogitoErgoSum

---

OP没有礼貌地未接受这个答案，但它对我很有帮助，所以我点了+1。 - user470714

您救了我的一天，先生！ - jestro

非常棒的解决方案，如果您需要在许多（带前缀的）表上添加特定权限。 - Ramon de la Fuente

如果你只是去掉用户名周围的单引号，同样适用于PostgreSQL。+1 - Gjordis

不错的解决方案。但是它没有涵盖一个情况，即在用户被授予权限后创建了一个具有该前缀的新表。 - Sergii Golubev

Answer 2

我不确定你是否可以使用通配符来匹配表名，但你可以使用通配符匹配数据库名称。需要注意的是，_ 是通配符，可以匹配任意单个字符（就像正则表达式中的.）。

文档在这里：http://dev.mysql.com/doc/refman/5.5/en/grant.html 引用：

“_”和“%”通配符在指定全局或数据库级别授予权限的GRANT语句中允许使用。这意味着，例如，如果您想将“_”字符作为数据库名称的一部分使用，则应在GRANT语句中将其指定为“\_”，以防止用户能够访问与通配符模式相匹配的其他数据库；例如，GRANT ... ON `foo\_bar`.* TO ....

- James C

---

似乎我们无法在表名中使用通配符。 - Nam G VU

Answer 3

使用bash：

mysql -Ne "show tables from test like 'foo_%'" | xargs -I {} mysql -Ne "GRANT SELECT ON test.{} TO 'foouser'@'%'"

- tilo

Answer 4

以下

GRANT SELECT, INSERT, UPDATE, DELETE ON `database`.`prefix\_%`.* TO  'username'@'localhost'

运行良好（在Windows上测试过MySQL 5.6.12）

- Grynn

---

很遗憾，这只适用于动态指定数据库名称，而不能动态指定表名。 - Danny Bullis

这在5.7版本中不起作用。如GRANT Syntax文档所述：“当数据库名称未用于在数据库级别授予权限，而是作为授予某些其他对象（如表或例程）权限的限定符时，通配符将被视为普通字符。” - Courtney Miles