Thanks a lot Michael. I can't believe i didn't see that little detail... that
happens when you're just exhausted, i guess.
Thanks a lot. :-)
Francisco
----- Original Message -----
From: Michael Stassen
To: Paco Zarabozo A.
Cc:
Sent: Tuesday, July 25, 2006 1:50 AM
Subject: Re: Granting all to a user with a db name prefix
Paco Zarabozo A. wrote:
> Hello All,
>
> I'm trying to GRANT ALL to a user only on DBs that math a prefix, but i
> can't find the way to so it on the documentation. Let's assume the username
> is "john". I want him to have all privileges only on databases with the
> prefix john, so he can:
>
> - create and drop databases starting ONLY with john (like john_sessions,
> john123, john_mytest, john_mail, etc)
> - have any kind of privileges on such databases
>
> According to the documentation, i can use % and _ as wildcards. However,
> mysql gives my an error if i try to use % wildcard. Only _ wildcard is
> accepted, but the following example:
>
> GRANT ALL ON JOHN_.* to 'JOHN'@'%' IDENTIFIED BY 'foo';
>
> ..only allows user john to create databases starting with john, followed by
> ONE single character. Using this, i can give 32 different grants in order
to
> allow up to 32 characters after 'john', but i'm sure that's not the way.
>
> If i try the wildcard %, i get an error. I've tried the following:
>
> GRANT ALL ON JOHN%.* to 'JOHN'@'%' IDENTIFIED BY 'foo';
> GRANT ALL ON 'JOHN%'.* to 'JOHN'@'%' IDENTIFIED BY 'foo';
> GRANT ALL ON "JOHN%".* to 'JOHN'@'%' IDENTIFIED BY 'foo';
> GRANT ALL ON 'JOHN%'.'*' to 'JOHN'@'%' IDENTIFIED BY 'foo';
> GRANT ALL ON JOHN*.* to 'JOHN'@'%' IDENTIFIED BY 'foo';
> GRANT ALL ON 'JOHN*'.* to 'JOHN'@'%' IDENTIFIED BY 'foo';
>
> ..and almost all similar ways. Am i missing something? I temporarily fixed
> the problem by directly editing mysql.db to change the wildcard _ for % in
> the respective record, and it works fine. However, i really want to know
the
> right way to do it. I hope someone there gives me the answer.
>
> Thanks a lot, have fun.
>
> Francisco
If you look closely, the answer is in the example at the end of the paragraph
you cite from the manual: "GRANT ... ON `foo\_bar`.* TO ..." You need to
quote
with backticks, the one thing you didn't try. Hence,
GRANT ALL ON `JOHN%`.* to 'JOHN'@'%' IDENTIFIED BY 'foo';
should work.
And yes, I would agree that's poorly documented.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
