MySQL 授予对具有特定前缀的多个表的权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5887104/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Grant privileges on several tables with specific prefix
提问by Chad
I'm using the table prefix method for having several clients use the same database. The number of tables created per client will be ~55. Instead of doing all of the granting by manually listing out the tables, can I do something like the following?
我正在使用表前缀方法让多个客户端使用相同的数据库。每个客户端创建的表数量约为 55。不是通过手动列出表格来完成所有授权，我可以执行以下操作吗？
GRANT SELECT,INSERT,UPDATE,DELETE ON database.prefix_* TO 'username'@'localhost' IDENTIFIED BY 'password';
回答by CogitoErgoSum
Advance Note: This is not my answer. I found it at http://lists.mysql.com/mysql/202610and have copied and pasted for simplicity credit to Stephen Cook
预先说明：这不是我的答案。我在http://lists.mysql.com/mysql/202610 上找到了它，并且为了简单起见复制并粘贴了 Stephen Cook
You can use the INFORMATION_SCHEMA.TABLES view to generate the GRANT statements for you. Write a query along these lines:
您可以使用 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_%'
Then run it, copy the results, and run those results as a query or script. You can of course get as crazy as you want with this, for example if you do this for many users maybe write a stored procedure that takes a parameter for the username and can therefore be used as a tool whenever you need it.
然后运行它，复制结果，并将这些结果作为查询或脚本运行。您当然可以随心所欲地使用它，例如，如果您为许多用户执行此操作，则可能会编写一个存储过程，该过程采用用户名参数，因此可以在需要时用作工具。
It isn't a syntax you asked for, but it is a nice trick that works.
这不是您要求的语法，但它是一个有效的好技巧。
--
——
Replace the table schema 'test' with the name of your database. foo_% can be replaced with the appropraite prefix_%
将表模式“test”替换为您的数据库名称。foo_% 可以替换为适当的前缀_%
I tried this on my own and it worked great.
我自己试过这个，效果很好。
回答by James C
I'm not sure if you can wildcard table names, you can definitely wildcard database names though. Watch out though as _is a wildcard matching any single character (like .in a regular expression).
我不确定您是否可以通配表名称，但您绝对可以通配数据库名称。但_要注意匹配任何单个字符的通配符（如.在正则表达式中）。
The Documention is here: http://dev.mysql.com/doc/refman/5.5/en/grant.html
文档在这里：http: //dev.mysql.com/doc/refman/5.5/en/grant.html
The “_” and “%” wildcards are permitted when specifying database names in GRANT statements that grant privileges at the global or database levels. This means, for example, that if you want to use a “_” character as part of a database name, you should specify it as “\_” in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON `foo\_bar`.* TO ....
在授予全局或数据库级别权限的 GRANT 语句中指定数据库名称时，允许使用“_”和“%”通配符。这意味着，例如，如果要使用“_”字符作为数据库名称的一部分，则应在 GRANT 语句中将其指定为“\_”，以防止用户能够访问匹配的其他数据库通配符模式；例如， GRANT ... ON `foo\_bar`.* TO ....
回答by Grynn
The following
下列
GRANT SELECT , INSERT , UPDATE , DELETE ON ``database.prefix\_%``. * TO 'username'@'localhost'
GRANT SELECT , INSERT , UPDATE , DELETE ON ``database.prefix\_%``. * TO 'username'@'localhost'
works fine (tested with MySQL 5.6.12 on Windows)
工作正常（在 Windows 上用 MySQL 5.6.12 测试）
回答by tilo
using bash:
使用 bash：
mysql -Ne "show tables from test like 'foo_%'" | xargs -I {} mysql -Ne "GRANT SELECT ON test.{} TO 'foouser'@'%'"
