MySQL Bugs: #17647: Trouble with "create database"

Bug #17647	Trouble with "create database"
Submitted:	22 Feb 2006 13:33	Modified:	9 Aug 2006 12:44
Reporter:	Michal Prokopiuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.19-BK, 4.1.16	OS:	Linux (Linux)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
I create database, create user, and give him privileges to database sample:
create database 'sample';
grant all on sample.* to 'sample'@'%' identified by 'password';

When I connect to database as user sample I can create database:
$ mysql -h my.mysql.server -u sample -p -A sample
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 263935 to server version: 4.1.16-standard

mysql> create database another;
ERROR 1044: Access denied for user 'sample'@'%' to database 'another'

It's ok, because i haven't privileges to create database. But i try create database the same as sample but one letter in name is printed in capital.

mysql> create database sAmple;  
Query OK, 1 row affected (0.00 sec)

I try on mysql 4.1.16 (shared wersion), 4.1.13a (compliled from source)

Of course I haven't  any records in mysql.db and mysql.user, but I have directory in datadir named sAmple. I try fix it by change users privileges, but i didn't see any solution.

How to repeat:
$ mysql -u root -p -S /path/to/socket
Enter password: 
mysql> create database 'sample';
mysql> grant all on sample.* to 'sample'@'%' identified by 'password';
mysql> \q
$ mysql -h my.mysql.server -u sample -p -A sample
mysql> create database sAmple; 
Query OK, 1 row affected (0.00 sec)

Thank you for a bug report. Verified just as described with 4.1.19-BK (ChangeSet@1.2491, 2006-04-01 05:44:10+02:00) on Linux:

openxs@suse:~/dbs/4.1> 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 3 to server version: 4.1.19

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

mysql> create database sample;
Query OK, 1 row affected (0.01 sec)

mysql> grant all on sample.* to 'sample'@'localhost' identified by 'password';
Query OK, 0 rows affected (0.01 sec)

mysql> \q
openxs@suse:~/dbs/4.1> bin/mysql -usample -ppassword -A sample;
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.1.19

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

mysql> create database another;
ERROR 1044 (42000): Access denied for user 'sample'@'localhost' to database 'another'
mysql> create database SAmple;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+----------+
| Database |
+----------+
| SAmple   |
| sample   |
| test     |
+----------+
3 rows in set (0.00 sec)

I remember very similar bug report, so it can be a duplicate, but I was not able to find it, yet.

A fix has been commited:

http://lists.mysql.com/commits/5927

thank you for solution

ok to push

I found another case linked with this bug:

If you have rights on "ab_mydatabase", you can create
"ab?mydatabase" too 
(maybe it's possible to replace every char with the questionmark, but till now only tested and seen with an underscore)

I think this is the same bug. OS filesystem feature tricks mysql's rights management.

MySQL-Versions: 4.1.20, 4.0.27
Plattform: Linux

Pushed into 4.1.21

Todo: merge into 5.0 and 5.1

Merged into 5.0.25 and 5.1.12.

Gerald,

That's a separate issue, and is documented behaviour: the underscore acts as a wildcard in an identifier unless the identifier is set off with backticks (`...`). See http://dev.mysql.com/doc/refman/5.0/en/legal-names.html and subsections.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented in 4.1.21/5.0.25/5.1.12 changelogs.

NOTE: It is *not* recommended to depend on case-sensitivity of identifiers for databases or tables, since this makes them non-portable to platforms with case-insensitive filesystems.

This bug has been registered at cve.mitre.org, please mention "CVE-2006-4226" in the changelog!

Will this security fix be pushed to 4.0.x?