From: Stephen Cook (sclistsgmail.com)
Date: Fri Oct 13 2006 - 18:20:14 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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=sclistsgmail.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]