Details
Critical Description
I've encountered this issue first with mysql 5.7,
then tested it on MariaDB 10.6 - issue is exactly the same:
When creating a user with table-based access, on a case-insensitive configuration (lower_case_table_names=2), the Statement
SHOW TABLES
|
does only show tables, where the table names case matches the GRANT-Statement.
Also it is not possible to generate a GRANT-Statement matching the case, they are turned into lower case.
Steps to reproduce (Windows machine, using lower_case_table_names=2):
CREATE TABLE `atable` ( |
`idatable` int(11) NOT NULL, |
PRIMARY KEY (`idatable`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
|
|
CREATE TABLE `Btable` ( |
`idBtable` int(11) NOT NULL, |
PRIMARY KEY (`idBtable`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 |
|
|
CREATE USER 'tester'@'%' IDENTIFIED BY 'password'; |
|
|
GRANT SELECT, SHOW VIEW ON `db`.`atable` TO 'tester'@'%' with grant option; |
GRANT SELECT, SHOW VIEW ON `db`.`Btable` TO 'tester'@'%' with grant option; |
|
|
SHOW GRANTS FOR tester; |
SHOW GRANTS already returns the lower-case of the statements as for mysql 5.4:
+-----------------------------------------------------------------------------+ |
|GRANT SELECT, SHOW VIEW ON `db`.`btable` TO `tester`@`%` WITH GRANT OPTION | |
|GRANT SELECT, SHOW VIEW ON `db`.`atable` TO `tester`@`%` WITH GRANT OPTION | |
+-----------------------------------------------------------------------------+ |
log on with tester.
C:\Users\me>mysql -utester -ppassword
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MariaDB connection id is 6
|
Server version: 10.6.5-MariaDB mariadb.org binary distribution
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MariaDB [(none)]> use db;
|
Database changed
|
MariaDB [db]> show tables;
|
+------------------------------+
|
| Tables_in_db |
|
+------------------------------+
|
| atable |
|
+------------------------------+
|
1 row in set (0.001 sec)
|
|
|
MariaDB [db]> select * from atable;
|
Empty set (0.000 sec)
|
|
|
MariaDB [db]> select * from btable;
|
Empty set (0.000 sec)
|
|
|
MariaDB [db]> select * from Btable;
|
Empty set (0.000 sec)
|
|
|
MariaDB [db]> select * from user;
|
ERROR 1142 (42000): SELECT command denied to user 'tester'@'localhost' for table 'user'
|
SHOW TABLES only shows the atable, where the table name is matching the GRANT-case. Querying both tables however works. But GRANT for Btable cannot be created with a upper-case B-letter.