Wildcard for table permissions

I want to grant a user permission to select,insert,update,delete on a few tables in the prod database. However, I find it time consuming to write each grant statement out per table. Is it possible to use a wildcard?

There are 300 tables, the user only needs access to 18 of them. The tables the user needs access to are prefixed 'vs_'.

Can I do a grant select,insert,update,delete on prod.vs_\ ? I know prod.\ is possible but wasn't sure on the prefix of a table.

No, the wildcard for table names can only be *, and does not permit other characters or patterns.

You could generate the 18 GRANT statements you need:

SELECT CONCAT('GRANT SELECT,INSERT,UPDATE,DELETE ON prod.`', TABLE_NAME, '` TO ...;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'prod' AND TABLE_NAME LIKE 'vs\_%';

Capture the output of that query, and then run it as a series of statements.

StackExchange Database Administrators Q#65254, answer score: 5