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.
Attachments
Activity
privileges for SHOW TABLES are checked here:
|
sql_show.cc |
|
5276
|
#ifndef NO_EMBEDDED_ACCESS_CHECKS
|
5277
|
if (!(thd->col_access & TABLE_ACLS)) |
5278
|
{
|
5279
|
table_acl_check.db= *db_name;
|
5280
|
table_acl_check.table_name= *table_name;
|
5281
|
table_acl_check.grant.privilege= thd->col_access;
|
5282
|
if (check_grant(thd, TABLE_ACLS, &table_acl_check, TRUE, 1, TRUE)) |
5283
|
continue; |
5284
|
}
|
5285
|
#endif |
and it does look like it doesn't take into account lower_case_table_names like it's done elsewhere.
The patch
https://github.com/MariaDB/server/commit/00928c35fe4502b5aac611bfef1e079c149edf1f
is OK to push. Thanks.
I'm raising the Priority of this to Critical as it has a huge impact in various situations.
All Applications that rely on SHOW TABLES in order to build a browsable list of data-tables available
will display a wrong / incomplete result due to this.
In fact, the presence of this bug doesn't allow to use any mysql-user account that has a limitation on Tables - if the tables are not named in lower case. (Unless the access method used works by querying directly without having to pick tables from an "available Tables" list)