List:General Discussion« Previous MessageNext Message »
From:Carsten Pedersen Date:April 21 2010 9:42am
Subject:Re: Grants for own objects ?
View as plain text  
Lentes, Bernd skrev:
> hello ML,
> 
> i'm new to MySQL, so i have a very basic question. I have to install a database
> server for about 15 persons. The server is intended for testing and evaluating. The users
> should be able to create their own databases and tables.. And they should be able to give
> grants on their own objects to other users.
> Following the recommendation in a MySQL-Book, i inserted the following line in the db
> table:
> 
> [...]
> localhost | lentes\_% | lentes | Y           | Y           | Y           | Y         
>  | Y           | Y         | Y          | Y               | Y          | Y          | Y   
>                  | Y                | Y                | Y              | Y               
>    | Y                  | Y
> [...]
> 
> That means that the user lentes can create databases, where the name begins with
> lentes_ . This works.
> 
> Being logged in as lentes, i'm able to create a databse called lentes_1. But i'm not
> able to give grants to other users:
> 
> mysql> grant select on lentes_1.* to 'eitz'@'localhost';
> ERROR 1044 (42000): Access denied for user 'lentes'@'localhost' to database
> 'lentes_1'
> 
> Uer lentes has no global privileges. How can i achieve that users are able to create
> their own databases/tables and to assign grants for their own databases/tables ?

First, don't mess around with the grant tables. Many years ago, that was 
indeed the way to control user access, but things have progressed since 
then. How old is that MySQL book?

Remove the manual edits you have made to the grant tables, and use only 
GRANT and REVOKE. I believe this will do what you want:

CREATE USER "lentes"@"localhost";
GRANT ALL ON "lentes_%".* TO "lentes"@"localhost" WITH GRANT OPTION;

Hth,

/ Carsten


Thread
Grants for own objects ?Bernd Lentes21 Apr
  • Re: Grants for own objects ?Carsten Pedersen21 Apr
    • RE: Grants for own objects ?Bernd Lentes21 Apr
      • Re: Grants for own objects ?Johan De Meersman21 Apr