That's my fault. It does work with 4.0.18. (The manual is usually pretty good at specifying when something is 4.1 only, by the way.) You need to quote the db string with backticks, not single quotes (as I did in my example). So, you would use
GRANT SELECT ON `dev\_%`.* TO [EMAIL PROTECTED]
Sorry to have led you astray.
Michael
Brian C. Hill wrote:
Hi Michael,
Thanks for the reply.
It doesn't work, at least not with 4.0.18. I also tried without the _ to simplify the case, but the grant statement isn't wild about the wildcard. :)
The docs you list are for 4.1. I guess I will have to wait until 4.1. :)
In the meantime, this works well:
SYSTEM rm /var/mysql/tmp/grants-tmp.sql; SELECT DISTINCT concat('GRANT SELECT ON ',db,'.* TO [EMAIL PROTECTED]'%\' IDENTIFIED BY \'abc\';'), concat('GRANT SELECT ON ',db,'.* TO [EMAIL PROTECTED]'%\' IDENTIFIED BY \'def\';'), concat('GRANT SELECT ON ',db,'.* TO [EMAIL PROTECTED]'%\' IDENTIFIED BY \'ghi\';') FROM db WHERE db LIKE 'dev\_%' INTO OUTFILE '/var/mysql/tmp/grants-tmp.sql'; SOURCE /var/mysql/tmp/grants-tmp.sql;
Brian ====================================================================== On Thu, Sep 16, 2004 at 05:28:51PM -0400, Michael Stassen wrote:
This is documented in the manual:
Note: the '_' and '%' wildcards are allowed when specifying database names in GRANT statements that grant privileges at the global or database levels. <http://dev.mysql.com/doc/mysql/en/GRANT.html>
So, if dev is the prefix, you need:
GRANT SELECT ON 'dev%'.* TO [EMAIL PROTECTED]
If dev_ is your prefix, the next line from the manual is relevant:
This means, for example, that if you want to use a '_' character as part of a database name, you should specify it as '\_' in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON 'foo\_bar'.* TO ....
Then you would need
GRANT SELECT ON 'dev\_%'.* TO [EMAIL PROTECTED]
Michael
Brian C. Hill wrote:
I have about 20 DB's with the same prefix.
How can I do something like
GRANT SELECT ON dev_*.* ....
I have seen examples for the _other_ DB software, like msql, that does something like:
SELECT 'GRANT SELECT ON '+name+' TO webuser;' from sysobjects where type = 'U'
(which generates the grant statements to run)
Is this possible in mysql? Maybe something like:
SELECT 'GRANT SELECT ON '+name+' TO webuser;' from `show databases like 'dev_%'`
I know that sub-queries aren't possible, but does anyone have any suggestions that don't involve weighty shell scripts?
Is there anyway to write show databases to file without the bordering box?
Brian
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
