Bug #77865 New schema privilege definitions do not honor wildcards

• Submitted: 28 Jul 2015 19:45
• Modified: 15 Oct 2015 19:19
• Reporter: Mike Reid
• Status: Closed
• Category: MySQL Workbench: Administration
• Severity: S3 (Non-critical)
• Version: 6.3.4
• OS: MacOS (10.10.4)
• CPU Architecture: Any
• Tags: privileges, schema, wildcard

[28 Jul 2015 19:45] Mike Reid

Description:
When creating new user privileges with access to All Schemas (via % wildcard), the resulting user record in mysql.db is created with an _asterisk_ in mysql.db.Host and mysql.db.Db instead of %, resulting in inability to connect to the database.

How to repeat:
1. Open Workbench and connect to a MySQL server (using v5.6.16-log for this test)

2. Navigate to Administration - Users and Privileges

3. Begin creating a new user by clicking "Add Account"

4. Enter a login name (MyNewUser), password, and confirm password on the "Login" tab

5. Click the "Schema Privileges" tab

6. Click "Add Entry"

7. Select "All Schema (%)" or "Schemas matching pattern:" and enter "%" (either way = same result), press OK.

NOTE: Schema _looks_ correct, % is listed.

8. Grant SELECT (or more) Object Rights and click APPLY.

9. Watch as the Schema privilege record is converted from % (percent) to * (asterisk)

10. Attempt to connect to the server/database using the new user ...access will be denied.

Suggested fix:
Please correctly account for wildcard schemas when administering user privileges in Workbench. 

Until then, the following commands correct this issue:

mysql> UPDATE mysql.db SET Host='%', Db='%' WHERE User = 'MyNewUser';
mysql> FLUSH PRIVILEGES;

[30 Jul 2015 9:41] MySQL Verification Team

Hello Mike Reid,

Thank you for the report.
Observed this with 6.2.5, 6.3.4 on Win7.
User is allowed to connect instance but any later actions are denied.

Thanks,
Umesh

[15 Oct 2015 19:19] Philip Olson

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 6.3.5 release, and here's the changelog entry:

When creating new user privileges with access to All Schemas (via the %
wildcard), the resulting user record in mysql.db was created with an
asterisk (*) in mysql.db.Host and mysql.db.Db instead of using %,
resulting in inability to connect to the database. 

As a workaround, you could have executed queries such as "UPDATE mysql.db SET
Host='%', Db='%' WHERE User = 'MyNewUserName'; FLUSH PRIVILEGES;".

Thank you for the bug report.