Grant privileges on several tables with specific prefix

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

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.TABLESWHERE    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_%

I tried this on my own and it worked great.

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

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 ....

using bash:

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