MySQL Bugs: #50762: Users and Roles fail to generate grant privilege SQL statement fo schema objects

Bug #50762	Users and Roles fail to generate grant privilege SQL statement fo schema objects
Submitted:	30 Jan 2010 21:40	Modified:	16 Feb 2010 17:37
Reporter:	Chris Van Vranken	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.15 OSS Rev 5053	OS:	Windows
Assigned to:	Alfredo Kojima	CPU Architecture:	Any
Tags:	grant, roles, users

Description:
Using roles and Users to grant permissions on a schema object results in the following SQL command.

grant ALL on  `schemaName` to 'cvanvranken'@'hostName.urmc-sh.rochester.edu';

this fails when queried against MySQL Server version 5.0.75 (perhaps it works with newer versions of MySQL server?):

How to repeat:
Create a user and a role.  Add a schema object to the role with the All privilege.
Forward Engineer the user privileges, with the option 'do not create user'.

Suggested fix:
The generated SQL statement should have .* appended to the schema name, such as: 

grant ALL on  `CLSS_tickets`.* to 'cvanvranken'@'hostName.urmc-sh.rochester.edu';

GRANT ALL ON database.table ...;

also valid:

GRANT ALL ON database.* ...;

or even:

GRANT ALL ON *.* ...;

The syntax is wrong here.

Fixed to append .* on GRANT statements on schemas.

fixed in repository

An entry has been added to the 5.2.16 changelog:

On the MySQL Model page using Roles and Users to grant privileges to a schema resulted in errors when the model was forward engineered, and the resultant script applied against a MySQL server. The script failed due to invalid SQL syntax, as the SQL created did not correctly apply the privileges to all schema objects.