| 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 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. Regards, 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, Kun
[15 Oct 2020 14:47]
Kun Yang
[Info] 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
Description: 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. JUST LIKE FOLLOWING --------------------------------------------------------------------------- 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 owners. 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