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