MySQL Bugs: #17864: create new database without permissions

Bug #17864	create new database without permissions
Submitted:	2 Mar 2006 15:21	Modified:	7 Mar 2006 8:41
Reporter:	Marcel Haertel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.27-BK, 4.0.26	OS:	Linux (Linux, freebsd)
Assigned to:		CPU Architecture:	Any

Description:
user without permission to create databases can create database. example: user bla has no rights in mysql user-table and full rights in mysql db-table for his database DB_C_bla. user bla can´t create new databases excepting a database named DB?C?bla

How to repeat:
clara2:/var/spool/mail/spider/Maildir/new # mysql -u bla -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4154497 to server version: 4.0.26

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+-----------------+
| Database        |
+-----------------+
| DB_C_bla |
+-----------------+
1 row in set (0.00 sec)

mysql> create database `test`;
ERROR 1044: Access denied for user: 'bla@localhost' to database 'test'

mysql> create database `DB?C?bla`;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+-----------------+
| Database        |
+-----------------+
| DB?C?bla |
| DB_C_bla |
+-----------------+
2 rows in set (0.00 sec)

mysql> drop database `DB?C?bla`;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+-----------------+
| Database        |
+-----------------+
| DB_C_bla |
+-----------------+
1 row in set (0.00 sec)

mysql> exit           
Bye
clara2:/var/spool/mail/spider/Maildir/new # 

Suggested fix:
charset?!?

Thank you for a (possibly, known) bug report. I was able to repeat the behaviour you described with 4.0.27-BK build on Linux:

openxs@suse:~/dbs/4.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.0.27

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all on DB_C_bla.* to user1@'localhost' identified by 'user1';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/4.0> bin/mysql -uuser1 -p test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10 to server version: 4.0.27

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| DB_C_bla |
| test     |
+----------+
2 rows in set (0.00 sec)

mysql> create database `DB?C?bla`;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+----------+
| Database |
+----------+
| DB?C?bla |
| DB_C_bla |
| test     |
+----------+
3 rows in set (0.00 sec)

mysql> drop database `DB?C?bla`;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+----------+
| Database |
+----------+
| DB_C_bla |
| test     |
+----------+
2 rows in set (0.00 sec)

mysql> create database anyotherone;
ERROR 1044: Access denied for user: 'user1@localhost' to database 'anyotherone'
mysql> create database `t?st`;
ERROR 1044: Access denied for user: 'user1@localhost' to database 't?st'

this is reproducible under mysql 4.1.18

Sorry, but this misleading behaviour is not a bug. It is clearly documented in the manual (http://dev.mysql.com/doc/refman/4.1/en/grant.html):

"Note: the ‘_’ and ‘%’ wildcards are allowed when specifying database names in GRANT  statements that grant privileges at the global or database levels. This means, for example, that if you want to use a ‘_’ character as part of a database name, you should specify it as ‘\_’ in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern; for example, GRANT ... ON `foo\_bar`.* TO ...."

You (and me) got what we asked for. Sorry again about improper verification - my fault.