MySQL Forums
Forum List  »  Newbie

Grant privilege appears to lowercase table names
Posted by: David Michell
Date: May 22, 2018 10:10PM

Granting a privilege to a user appears to lowercase the table instead of preserving the case, despite lower_case_table_names being set to 2. I was under the impression the case should be preserved? I've included a quick scenario below to demonstrate the issue.

**** example begins ****

mysql> show global variables like 'lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | ON |
| lower_case_table_names | 2 |
+------------------------+-------+
2 rows in set (0.01 sec)

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

mysql> create table Frog.SkinDetails (coLour varchar(255));
Query OK, 0 rows affected (0.01 sec)

mysql> create user fred;
Query OK, 0 rows affected (0.00 sec)

mysql> grant insert on `Frog`.`SkinDetails` to fred;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for fred;
+----------------------------------------------------+
| Grants for fred@% |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'fred'@'%' |
| GRANT INSERT ON `frog`.`skindetails` TO 'fred'@'%' |
+----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from mysql.tables_priv where User = 'fred';
+------+------+------+-------------+-----------------------+---------------------+------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+------+------+------+-------------+-----------------------+---------------------+------------+-------------+
| % | frog | fred | skindetails | development@localhost | 0000-00-00 00:00:00 | Insert | |
+------+------+------+-------------+-----------------------+---------------------+------------+-------------+
1 row in set (0.01 sec)

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

**** example ends ****

I'm running mysql "Server version: 5.6.39 Homebrew" on MacOS 10.13.4.

Anyone know if there's a way to force case preservation when granting privileges?

Thanks in advance.
David

Options: ReplyQuote


Subject
Written By
Posted
Grant privilege appears to lowercase table names
May 22, 2018 10:10PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.