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

Bug #17647	Trouble with "create database"
Submitted:	22 Feb 2006 14:33	Modified:	9 Aug 2006 14:44
Reporter:	Michal Prokopiuk
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	4.1.19-BK, 4.1.16	OS:	Linux (Linux)
Assigned to:	Alexander Barkov	Target Version:

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?