Re: Granting all to a user with a db name prefix

[Paco Zarabozo A.]

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]

Granting all to a user with a db name prefix

[Paco Zarabozo A.]

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


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]