Bug #70310 "Forward Engineer - SQL Create Script" bug with privileges
Submitted: 12 Sep 2013 8:32 Modified: 7 Nov 2013 3:06
Reporter: T. M. Email Updates:
Status: Closed Impact on me:
Category:MySQL Workbench: Modeling Severity:S3 (Non-critical)
Version:6.06.11184 OS:Windows (7)
Assigned to: CPU Architecture:Any
Tags: workbench bug privileges

[12 Sep 2013 8:32] T. M.

There is a bug in Mysql Workbench on "Forward Engineer - SQL Create Script".

LOCK TABLES privilege is generated at table level but it's a database level privilege.

GRANT LOCK TABLES ON TABLE `db`.`table` TO 'user';
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used

instead of 

GRANT LOCK TABLES on `db`.* TO 'user';
Query OK, 0 rows affected (0.00 sec)

How to repeat:
In mysql workbench,

- Create a model
- Create a table
- Create a role with "LOCK TABLES" privileges on the table previously created
- Create an user and link it with the role previously created
- Goto File > Export > Forward Engineer - SQL Create Script
- Check "Export User Objects"

Look at the generated script :
GRANT LOCK TABLES ON TABLE `db`.`table` TO 'user';

instead of

GRANT LOCK TABLES on `db`.* TO 'user';

Suggested fix:
- Remove "LOCK TABLES" from the table privileges object
- Create a "Database Object" and put LOCK TABLES privileges with others database level privileges like "Event"

Or Ugly way, keep "LOCK TABLES" on table object and fix script "GRANT LOCK TABLES on `db`.* TO 'user';", but it's a bit confusing.
[12 Sep 2013 12:29] T. M.
Ok, i found a trick when you right click on "Objects", you can add "db.*" object and then check "LOCK TABLES".

But you should remove "Lock tables" from "table" object.
[17 Sep 2013 11:09] Lisa Ehrlinger
Thank you for the bug report!
[7 Nov 2013 3:06] Philip Olson
Fixed as of MySQL Workbench 6.0.8, and here's the changelog entry:

The "Forward Engineer - SQL Create Script" wizard would generate the "LOCK
TABLES" privilege at the table level, instead of the database level.

Thank you for the bug report.