Description:
The internal mapping of privilege grants to tables appears to be confused by
lower_case_table_names=2, in that it doesn't respond to privileges granted using
mixed-case names. For instance, the following example:
[Running as DB admin]
mysql> show grants for LOCAL_browser;
+-------------------------------------------------------------------------------------+
| Grants for LOCAL_browser@%
|
+-------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'LOCAL_browser'@'%' IDENTIFIED BY PASSWORD '******' |
| GRANT SELECT ON `fred`.* TO 'LOCAL_browser'@'%'
|
| GRANT SELECT ON `LOCAL`.* TO 'LOCAL_browser'@'%'
|
| GRANT SELECT ON `LOCAL_test`.* TO 'LOCAL_browser'@'%'
|
| GRANT SELECT ON `test_browser_data`.* TO 'LOCAL_browser'@'%'
|
+-------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql> revoke all on `LOCAL`.* from 'LOCAL_browser'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'LOCAL_browser' on host
'localhost'
mysql> revoke all on `local`.* from 'LOCAL_browser'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'LOCAL_browser' on host
'localhost'
bailey@myhost(0) ~ $ mysql -u LOCAL_browser -p -D LOCAL
Enter password:
ERROR 1044 (42000): Access denied for user 'LOCAL_browser'@'localhost' to database
'local'
[Running as DB admin]
mysql> grant SELECT on local.* to LOCAL_browser@localhost;
Query OK, 0 rows affected (0.03 sec)
mysql> quit
Bye
bailey@myhost(0) ~ $ mysql -u LOCAL_browser -p -D LOCAL
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 19 to server version: 4.1.9-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
One can work around the problem by replicating all grants using lower-case database and
table names. The user names remain case-sensitive.
I have also noticed that CREATE TABLE, when given a mixed- or upper-case table name,
downcases the name when creating the table, so suspect that the overall issue here is one
of converting to lower case internally in circumstances where this is not required.
How to repeat:
Please see example above.
Suggested fix:
If I understand the intent of lower_case_table_names=2 correctly, I'd argue that names
should be downcased only when accessing existing files (e.g. stat, open for read/append),
and not for internal comparisons nor for file creation.
Alternatively, perhaps Mac OS X should operate under lower_case_table_names=0. I don't
appreciate all of the ramifications this may have on indices, though, and it won't solve
the internal problem with privileges.
Thanks!
Description: The internal mapping of privilege grants to tables appears to be confused by lower_case_table_names=2, in that it doesn't respond to privileges granted using mixed-case names. For instance, the following example: [Running as DB admin] mysql> show grants for LOCAL_browser; +-------------------------------------------------------------------------------------+ | Grants for LOCAL_browser@% | +-------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'LOCAL_browser'@'%' IDENTIFIED BY PASSWORD '******' | | GRANT SELECT ON `fred`.* TO 'LOCAL_browser'@'%' | | GRANT SELECT ON `LOCAL`.* TO 'LOCAL_browser'@'%' | | GRANT SELECT ON `LOCAL_test`.* TO 'LOCAL_browser'@'%' | | GRANT SELECT ON `test_browser_data`.* TO 'LOCAL_browser'@'%' | +-------------------------------------------------------------------------------------+ 5 rows in set (0.00 sec) mysql> revoke all on `LOCAL`.* from 'LOCAL_browser'@'localhost'; ERROR 1141 (42000): There is no such grant defined for user 'LOCAL_browser' on host 'localhost' mysql> revoke all on `local`.* from 'LOCAL_browser'@'localhost'; ERROR 1141 (42000): There is no such grant defined for user 'LOCAL_browser' on host 'localhost' bailey@myhost(0) ~ $ mysql -u LOCAL_browser -p -D LOCAL Enter password: ERROR 1044 (42000): Access denied for user 'LOCAL_browser'@'localhost' to database 'local' [Running as DB admin] mysql> grant SELECT on local.* to LOCAL_browser@localhost; Query OK, 0 rows affected (0.03 sec) mysql> quit Bye bailey@myhost(0) ~ $ mysql -u LOCAL_browser -p -D LOCAL 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 19 to server version: 4.1.9-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> One can work around the problem by replicating all grants using lower-case database and table names. The user names remain case-sensitive. I have also noticed that CREATE TABLE, when given a mixed- or upper-case table name, downcases the name when creating the table, so suspect that the overall issue here is one of converting to lower case internally in circumstances where this is not required. How to repeat: Please see example above. Suggested fix: If I understand the intent of lower_case_table_names=2 correctly, I'd argue that names should be downcased only when accessing existing files (e.g. stat, open for read/append), and not for internal comparisons nor for file creation. Alternatively, perhaps Mac OS X should operate under lower_case_table_names=0. I don't appreciate all of the ramifications this may have on indices, though, and it won't solve the internal problem with privileges. Thanks!