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
> >
--
_____________________________________________________________________
/ Brian C. Hill [EMAIL PROTECTED] http://brian.bch.net \
| UNIX Specialist BCH Technical Services http://www.bch.net |
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
