授予具有特定前缀的多个表的权限 [英] Grant privileges on several tables with specific prefix

问题描述

我使用表前缀方法让多个客户端使用同一个数据库。每个客户端创建的表数量将为〜55。

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?

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

推荐答案

提前注意：这不是我的答案。我在 http://lists.mysql.com/mysql/202610 找到它，并复制和

Advance Note: This is not my answer. I found it at http://lists.mysql.com/mysql/202610 and have copied and pasted for simplicity credit to Stephen Cook

您可以使用INFORMATION_SCHEMA.TABLES视图为您生成GRANT
语句。沿着这些行写一个查询：

You can use the INFORMATION_SCHEMA.TABLES view to generate the GRANT statements for you. Write a query along these lines:

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.

-

将表模式'test'替换为数据库的名称。 foo_％可以替换为appropraite前缀_％

Replace the table schema 'test' with the name of your database. foo_% can be replaced with the appropraite prefix_%

我自己试过，效果很好。

I tried this on my own and it worked great.

这篇关于授予具有特定前缀的多个表的权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！