问题描述
我使用表前缀方法让多个客户端使用同一个数据库。每个客户端创建的表数量将为〜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';
推荐答案
提前注意：这不是我的答案。我在找到它，并复制和
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 GRANTstatements 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 orscript. You can of course get as crazy as you want with this, forexample if you do this for many users maybe write a stored procedurethat takes a parameter for the username and can therefore be used as atool 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.
这篇关于授予具有特定前缀的多个表的权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！