MySQL Bugs: #85257: adding privlidges using Schemas Matching Pattern adds extra ".*"

Bug #85257	adding privlidges using Schemas Matching Pattern adds extra ".*"
Submitted:	1 Mar 2017 20:47	Modified:	3 Mar 2017 5:44
Reporter:	robert mundkowsky	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: Administration	Severity:	S5 (Performance)
Version:	6.3.8, 6.3.9	OS:	Windows (Microsoft Windows 8.1 Enterprise)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
Workbench adds extra .* to schema matching pattern that causes problems.  Database version I think is 5.5.47-0ubuntu0.14.04.1

How to repeat:
I want to grant privileges for user to one table. I select "User and Privileges" in Workbench. Then select "Schema Privileges" tab and press "Add Entry" button.  Then I select "Schema matching pattern" and I enter

foo.bar

Check the CREATE, DROP DDL Rights and press Apply.

Looking at the grants in the database shows error (show grants for 'xxx';):

GRANT CREATE ON `'foo.bar`.* TO 'xxx'@'%'

There is an extra .* that is been added by the Workbench that makes this grant not work.

Suggested fix:
Do not append .* automatically.

Hello  robert mundkowsky,

Thank you for the bug report.
I could not see any issues in adding privileges using Schemas Matching Pattern with MySQL workbench 6.3.9 version.

Thanks,
Chiranjeevi.

It is extremely easy to replicate this problem. Take the mysql-workbench-community-6.3.8-winx64-noinstall.zip and unzip it. 

If you look at the grants, you will see that if you selected a pattern for one table like xxx.bbb , your tool has extra .* added to it! The .* is needed if you selected a schema like xxx.* , but it fails for a grant on a table like xxx.bbb.* . Below is the command you need to  run and to see grants and the 3rd line is the example of the error your tool makes.

mysql> show grants for 'tester@%';
+----------------------------------------------------+
| Grants for tester@%@%                              |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO 'tester@%'@'%'               |
| GRANT ALL PRIVILEGES ON `test`.* TO 'tester@%'@'%' |
| GRANT SELECT ON `xxx`.* TO 'tester@%'@'%'        |
| GRANT SELECT ON `xxx.bbb`.* TO 'tester@%'@'%'  |
+----------------------------------------------------+

I found where in the code the bug is and I will give you the fix as well. 

source file:

mysql-workbench\modules\db.mysql\src\db_mysql_diffsqlgen_grant.h

Line 64 should be:

object_str = *priv->databaseObjectType() + " " + dbmysql::full_name(*schema);

Rather than:

object_str = *priv->databaseObjectType() + " " + dbmysql::full_name(*schema) + ".*";

This is per https://dev.mysql.com/doc/refman/5.7/en/grant.html which states priv_level can be "tbl_name" and not "tbl_name.*"

Hello robert mundkowsky,

Thank you for your feedback.
Verified this behavior on MySQL Workbench in 6.3.9 version.

Thanks,
Chiranjeevi.

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT CREATE, DROP ON `test.test_table`.* TO 'root'@'localhost'     |
| GRANT CREATE, DROP ON `bug_85257`.* TO 'root'@'localhost'           |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql>