List:General Discussion« Previous MessageNext Message »
From:Stephen Cook Date:October 13 2006 11:20pm
Subject:Re: Grant privs to multiple tables at once?
View as plain text  
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
> 
> 
Thread
Grant privs to multiple tables at once?David Felio12 Oct
  • Re: Grant privs to multiple tables at once?Anders Lundgren12 Oct
    • Re: Grant privs to multiple tables at once?David Felio12 Oct
  • Re: Grant privs to multiple tables at once?Stephen Cook14 Oct