Bug #101177 MySQL 8.0 doesn't work with the underscore escape of schema name in Workbench
Submitted: 15 Oct 2020 6:16 Modified: 15 Oct 2020 14:47
Reporter: Kun Yang Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Workbench: Administration Severity:S2 (Serious)
Version:8.0.21 OS:Linux (Ubuntu 20.04 x86_64)
Assigned to: CPU Architecture:x86
Tags: WBBugReporter

[15 Oct 2020 6:16] Kun Yang
In Administration -> Users and Privileges -> Schema Privileges Tab of MySQL Workbench, if you add an entry from selecting and the selected schema have underscore in the name, for example a schema called 'new_schema', the Workbench will escape it as 'new\_schema' automatically.

But it seems that MySQL 8.0 doesn't recognize the \ mark as a escape mark as you will be denied for accessing the 'new_schema' table.


Welcome to the MySQL monitor.  Commands end with ; or \g.                                                                                                                                                     
Your MySQL connection id is 752                                                                                                                                                                               
Server version: 8.0.18-google (Google)                                                                                                                                                                        
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.                                                                                                                                  
Oracle is a registered trademark of Oracle Corporation and/or its                                                                                                                                             
affiliates. Other names may be trademarks of their respective                                                                                                                                                 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.                                                                                                                                
mysql> SHOW DATABASES;                                                                                                                                                                                        
| Database           |                                                                                                                                                                                        
| information_schema |                                                                                                                                                                                        
| insight_user       |                                                                                                                                                                                        
| mysql              |                                                                                                                                                                                        
| performance_schema |                                                                                                                                                                                        
| sys                |                                                                                                                                                                                        
5 rows in set (0.01 sec)                                                                                                                                                                                      
mysql> SHOW GRANTS;                                                                                                                                                                                           
| Grants for hotel.admin@%                                                  |                                                                                                                            
| GRANT SHOW DATABASES ON *.* TO `hotel.admin`@`%`                          |                                                                                                                            
| GRANT SELECT, INSERT, UPDATE, DELETE ON `insight\_user`.* TO              |
| `hotel.admin`@`%`                                                         |                                                                                                                            
2 rows in set (0.01 sec)                                                                                                                                                                                      
mysql> USE insight_user;                                                                                                                                                                                      
ERROR 1044 (42000): Access denied for user 'hotel.admin'@'%' to database 'insight_user'                                                                                                                       

How to repeat:
1. Create a schema with underscore in name.
2. Use Workbench to grant privileges by select the schema directly.
3. Access the table with granted user in step 2.
4. Get denied.

Suggested fix:
DO NOT escape underscore in schema name for MySQL 8.0
[15 Oct 2020 11:06] Kun Yang
Escape in MySQL Workbench, Fig1

Attachment: escape_in_workbench_1.png (image/png, text), 38.73 KiB.

[15 Oct 2020 11:06] Kun Yang
Escape in MySQL Workbench, Fig2

Attachment: escape_in_workbench_2.png (image/png, text), 64.27 KiB.

[15 Oct 2020 11:07] Kun Yang
The escape works with MySQL 5.7

Attachment: mysql_5_7_works_well.png (image/png, text), 85.06 KiB.

[15 Oct 2020 11:08] Kun Yang
MySQL 8.0 does not work with the escape

Attachment: mysql_8_0_not_work.png (image/png, text), 76.59 KiB.

[15 Oct 2020 11:10] Kun Yang
No escape works in MySQL 8.0

Attachment: mysql_8_0_workaroud.png (image/png, text), 84.93 KiB.

[15 Oct 2020 13:10] MySQL Verification Team
Hello Kun Yang,

Thank you for the report.
I could not repeat the issue at our end using MySQL Workbench 8.0.21 version on Windows 10 and Ubuntu 20.04.
Imho you might have problem with privileges. Please login to MySQL with appropriate privileges and try again.

Thank you for your interest in MySQL.

Ashwini Patil
[15 Oct 2020 13:11] MySQL Verification Team
8.0.21 test results

Attachment: 101177_test_results.png (image/png, text), 157.49 KiB.

[15 Oct 2020 13:55] Kun Yang
Hi, Ashwini Patil

Thanks for you test! I did a same test using 8.0.20 and it seems that this problem has been fixed! (But since MySQL version in GCP is 8.0.18 and you can't choice, I will use the workaround at first..)

Thank you!

Best Regards,
[15 Oct 2020 14:47] Kun Yang

I found this problem may not be a bug, it seems caused by the partial_revokes feature. MySQL 8.0 Server in GCP have this partial_revokes turned on.

`Partial revokes must name the schema literally. Schema names that contain the % or _ SQL wildcard characters (for example, myschema%) are not permitted.`
- https://dev.mysql.com/doc/refman/8.0/en/partial-revokes.html