MySQL Bugs: #16845: GRANT syntax error for database wildcard

Bug #16845	GRANT syntax error for database wildcard
Submitted:	27 Jan 2006 15:17	Modified:	27 Jan 2006 16:04
Reporter:	David Smith	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	14.7	OS:	Windows (Windows XP)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
It seems like the GRANT syntax does not allow wildcards to be put into the database portion of the command.  However, I can manually insert a row into the mysql.db table to contain a wildcard in the db field and the result is parsed correctly.  This feature is valuable to my usage.  I can create backup databases without having root privileges that match my naming crieria, so the mysql and other database schemas are protected.

  

How to repeat:
as root:

GRANT ALL PRIVILEGES ON mybackups_%.* TO 'myuser'@'localhost' IDENTIFIED BY 'myuser';

--> SYNTAX ERROR

GRANT usage ON 'test.*' TO 'myuser'@'localhost' IDENTIFIED BY 'myuser';

INSERT INTO mysql.db VALUES('localhost','mybackups_%', 'myuser', 'Y', 'Y', 'Y'
, 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y');

FLUSH PRIVILEGES;

now as myuser:

CREATE DATABASE mybackups_123;
USE mybackups_123;
CREATE TABLE t(id INT NOT NULL PRIMARY KEY);
SELECT * FROM t;

--> all these commands work!!!

CREATE DATABASE not_in_pattern;

--> ACCESS DENIED 

Suggested fix:
Please allow the GRANT syntax to support this, since I do not think it is good practice to manually insert rows into mysql schema tables.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Please read: http://dev.mysql.com/doc/refman/5.0/en/legal-names.html

An identifier may be quoted or unquoted. If an identifier is a reserved word or contains special characters, you must quote it whenever you refer to it. (Exception: A word that follows a period in a qualified name must be an identifier, so it is not necessary to quote it, even if it is a reserved word.) For a list of reserved words, see Section 9.6, “Treatment of Reserved Words in MySQL”. Special characters are those outside the set of alphanumeric characters from the current character set, ‘_’, and ‘$’.