[MDEV-30765] SHOW TABLES not working properly with lower_case_table_names=2 Created: 2023-03-01  Updated: 2023-06-07  Resolved: 2023-05-11

Status: Closed
Project: MariaDB Server
Component/s: Platform Windows
Affects Version/s: 10.6.12
Fix Version/s: 10.4.31, 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3, 11.1.2

Type: Bug Priority: Critical
Reporter: dognose Assignee: Oleg Smirnov
Resolution: Fixed Votes: 0
Labels: innodb
Environment:

Windows Machine
lower_case_table_names=2


 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.

 Comments   
Comment by dognose [ 2023-03-11 ]

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.

  • MySQL Workbench
  • PhpMyAdmin
  • Altium
  • Visual-Studio Designer
  • ...

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)

Comment by Sergei Golubchik [ 2023-03-30 ]

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.

Comment by Oleg Smirnov [ 2023-04-25 ]

bar, please review branch bb-10.4-mdev-30765.

Comment by Alexander Barkov [ 2023-05-10 ]

The patch
https://github.com/MariaDB/server/commit/00928c35fe4502b5aac611bfef1e079c149edf1f
is OK to push. Thanks.

Comment by Oleg Smirnov [ 2023-05-11 ]

Pushed to 10.4.

Generated at Thu Feb 08 10:18:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.