List:General Discussion« Previous MessageNext Message »
From:Bernd Lentes Date:April 21 2010 12:39pm
Subject:RE: Grants for own objects ?
View as plain text  

Carsten Pederseb wrote:

> 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?

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

> 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:

I did.

> 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 near '"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, delete_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',

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)


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