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.
David Felio wrote:
> Assume database 'biggie' with 15 tables, 10 of which start with 'foo_'.
> I want the user 'foouser' to have access only to those tables that begin
> with 'foo_'.
>
> I'm hoping that I am just being blind because I don't see anything in
> the manual or in the MySQL book on granting to multiple tables at once
> and the * wildcard appears to only work by itself, not when appended to
> a string (i.e. I can't do "grant select on biggie.foo_* to
> 'foouser'..."). I've tried multiple variations of wildcards, to no avail.
>
> Please tell me I'm not going to have to explicitly grant privs to each
> table. There are actually several set of tables for a total of several
> hundred tables to which I will need to apply permissions.
>
> Thanks.
>
> David
>
> --MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
