Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Backslash in database names and MySQL 8.0

bsfrye's profile image bsfrye posted 3 weeks ago in General Permalink

We are using Aurora on AWS and we have databases that all have underscores in the names (i.e. GooberFormBuilder_Reporting). When saving user permissions, heidi places a backslash in the grant:

GRANT SELECT ON GooberFormBuilder\_Reporting.* TO 'temp'@'localhost';

This worked fine prior to our switch to MySQL8. But now when we do this, my user is still not getting SELECT on that db. If I manually execute the GRANT statement without the backslash, my user permissions are correct.

ansgar's profile image ansgar posted 3 weeks ago Permalink

Just tested on MySQL 8.0.26, but without problems: a user with a database grant to foo\_bar can access the database foo_bar (but not the other ones, which states he has no global super privileges).

But I guess you made the same error as I did first: when adding the database object, the user manager did not auto-select any mode for the grant, which resulted in an empty grant:

Description

Please recheck if the grant still exists, and if it has modes/checkboxes selected, so it looks like this:

Description

The backslash is still recommended by the MySQL docs (including the one for v8): https://dev.mysql.com/doc/refman/8.0/en/grant.html#grant-quoting

The underscore wildcard would otherwise allow the same user to access a database with a different character, for example fooxbar.

By the way: the same single character wildcard is used in LIKE expressions:

... WHERE col LIKE 'abc_def' => may match "abdxdef", "abdydef" etc.

bsfrye's profile image bsfrye posted 3 weeks ago Permalink

This is very strange. This is the GRANT statement that is running:

Description

This is what that user sees when logged in: Description

When I run the GRANT manually without the \ - the new user can see the database

Any other ideas on how to troubleshoot this. MySQL 8.0.23 (Aurora on AWS)

ansgar's profile image ansgar posted 3 weeks ago Permalink

Perhaps it's just the query for the tree which fails. Are you able to run a custom query on the database, like for example SHOW TABLES FROM AdrianPepsiCoGlobalRO_Reporting?

bsfrye's profile image bsfrye posted 3 weeks ago Permalink

Yes - I am able to run queries just fine. Everything work except the user manager. I setup an RDS instance on AWS and it all worked as expected, but Aurora does not. This seems to be an AWS aurora issue

ansgar's profile image ansgar posted 3 weeks ago Permalink

I meant, if you connect with the newly created user (which does not show the wanted database in the tree), and then run a query like SHOW TABLES FROM nameOfDatabase. Does that return an error?

bsfrye's profile image bsfrye posted 2 weeks ago Permalink

Connecting with newly created user and executing SHOW TABLES with that user results in an access denied message:

/ SQL Error (1044): Access denied for user 'temp'@'%' to database 'AdrianPepsiCoGlobalRO_Reporting' /

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.