Bug #59677 Table name case sensitive in grant privileges on mac
Submitted: 22 Jan 2011 21:27 Modified: 25 May 2011 23:14
Reporter: Darren Cassar Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Security: Privileges Severity:S2 (Serious)
Version:5.1.41, 5.5.8 OS:Mac OS X
Assigned to: CPU Architecture:Any

[22 Jan 2011 21:27] Darren Cassar
Description:
Granting privileges on a table which has uppercase letters on mac results in client not able to query. The testing was done on: 5.0.77, 5.1.35,40,41,42,44,45,50, 5.5.8 and 6.0.11 and found to have started as from 5.1.41, still an issue with 5.5.8 but not on 6.0.11.

How to repeat:
as user with grant option:

create database testing;
use testing
create table table1(a int);
create table Table2(a int);
grant select on testing.table1 to 'dc'@'localhost';
grant select on testing.Table2 to 'dc'@'localhost';

as dc from localhost:
use testing
show tables;
[22 Jan 2011 21:58] Peter Laursen
This is not specific for Mac. Same on Windows with 'lower_case_table_names' = 2 setting in configuration (on both 5.1.54 and 5.5.8):

5.5.8:

SHOW VARIABLES LIKE 'lower_case_table_names'; -- returns 2
SHOW GRANTS; 
/*
Grants for dc@localhost                               
------------------------------------------------------
GRANT USAGE ON *.* TO 'dc'@'localhost'                
GRANT SELECT ON `testing`.`table1` TO 'dc'@'localhost'
GRANT SELECT ON `testing`.`table2` TO 'dc'@'localhost'
*/

USE testing;
SHOW TABLES;

/* returrns

Tables_in_testing
-----------------
table1           
*/

All the *ever returning* issues with 'lower_case_table_names' in MySQL since 4.0 are so annoying (and also unforgivable *STUPID* and *UNATTENTIVE* (as there are more than enough reports already with 'lower_case_table_names' -issues). 

To pin it out:

1) SHOW GRANTS should return table names in *mixed case* as tables were created.
2) SHOW TABLES should return both tables (and also in *mixed case*).
[23 Jan 2011 10:46] Peter Laursen
One more detail:

SHOW GRANTS;
/*
Grants for dc@localhost                               
------------------------------------------------------
GRANT USAGE ON *.* TO 'dc'@'localhost'                
GRANT SELECT ON `testing`.`table1` TO 'dc'@'localhost'
*/

USE testing;
SELECT * FROM table2; -- succeeds with any lettercase.
[23 Jan 2011 13:22] Peter Laursen
I missed a line in my  output.  it hsould be 

Grants for dc@localhost                               
------------------------------------------------------
GRANT USAGE ON *.* TO 'dc'@'localhost'                
GRANT SELECT ON `testing`.`table1` TO 'dc'@'localhost'
GRANT SELECT ON `testing`.`table2` TO 'dc'@'localhost'

The problem is that SHOW and SELECT FROM I_S does not respect 'lower_case_table_names' setting.
[23 Jan 2011 18:14] Peter Laursen
.. and also that SHOW GRANTS does not.
[25 Mar 2011 0:51] Sveta Smirnova
Thank you for the report.

Please provide output of SHOW TABLES in your environment. I get both tables:

$mysql51 -udc testing
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 225
Server version: 5.1.57-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show tables;
+-------------------+
| Tables_in_testing |
+-------------------+
| table1            |
| table2            |
+-------------------+
2 rows in set (0.00 sec)

Please also use latest versions of MySQL: 5.1.56 and 5.5.10
[25 Apr 2011 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[26 May 2011 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".