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