Grants for own objects ?

hello ML,

i'm new to MySQL, so i have a very basic question. I have to install a data=
base server for about 15 persons. The server is intended for testing and ev=
aluating. 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 user=
s.
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. Bu=
t 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 dat=
abases/tables ?


Bernd

--
Bernd Lentes
Systemadministration
Institut für Entwicklungsgenetik
HelmholtzZentrum münchen
bernd.lentes@helmholtz-muenchen.de
phone: +49 89 3187 1241
fax: +49 89 3187 3826
http://www.helmholtz-muenchen.de/idg
Im Kampf um das Unerreichbare verliert das
Erreichte seinen Wert
=20

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Grants for own objects ?

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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Grants for own objects ?

Carsten Pederseb wrote:

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

It's from 2005 and about MySQL-version 5.

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

I did.

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

Using the GRANT, i get an error:
GRANT ALL ON "lentes_%".* TO "lentes"@"localhost" WITH GRANT OPTION;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual =
that corresponds to your MySQL server version for the right syntax to use n=
ear '"lentes_%".* TO "lentes"@"localhost" WITH GRANT OPTION' at line 1

The book says it's not possible to use metacharacters like % with GRANT.
If i insert the line manually in the db-table, it works:
insert into db (host, db, User, select_priv, insert_priv, update_priv, del=
ete_priv, create_priv, drop_priv, grant_priv, references_priv, index_priv, =
alter_priv, create_tmp_table_priv, lock_tables_priv, create_view_priv, show=
_view_priv, create_routine_priv, alter_routine_priv, execute_priv) values (=
'localhost', 'lentes%', 'lentes', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '=
Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');

This should be the same as using grant ?

As user lentes, i'm now able to create a database called e.g. lentes_1 :
mysql> create database lentes_1;
Query OK, 1 row affected (0.01 sec)

And now i can assign grants on this database to other users:
mysql> grant select on lentes_1.* to 'eitz'@'localhost';
Query OK, 0 rows affected (0.00 sec)


Bernd


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Grants for own objects ?

--000e0cd116f01e16ac0484bed355
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Apr 21, 2010 at 2:39 PM, Lentes, Bernd <
bernd.lentes@helmholtz-muenchen.de> wrote:

> The book says it's not possible to use metacharacters like % with GRANT.
>

The book is wrong. You have to use backticks to quote the lentes_% part,
though - it's an annoying quirk.

Please ritually burn the book and provide humorous pictures of the event in
order to receive further support :-)


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--000e0cd116f01e16ac0484bed355--