database permission check fails if the database name contains an underscore (during icingaweb2 migrations)
NavidSassan opened this issue · 5 comments
Describe the bug
The database permission check during the new icingaweb2 migrations via the webgui fails if the database name contains an underscore.
It complains that the user is missing permissions, even though they are present:
The provided credentials cannot be used to execute "CREATE ,SELECT ,INSERT ,UPDATE ,DELETE ,DROP ,ALTER ,CREATE VIEW ,INDEX ,EXECUTE" SQL commands and/or grant the missing privileges to other users.
To Reproduce
- Install an older icingaweb2 version, e.g. 2.11.0. Make sure to include an underscore in the database name, for example
icinga_web2. - Make sure that the database user has the correct permissions.
- Install icingaweb2 >= 2.12.0 (for the migration feature).
- Go to the migrations page. It prompts for a user, even though the user has enough permissions.
Expected behavior
The migration should work, as the database user has the required permissions.
Your Environment
- Icinga Web 2 version and modules (System - About): 2.12.0
- Web browser used: Firefox
- Icinga 2 version used (
icinga2 --version): r2.14.0-1 - PHP version used (
php --version): 8.0.30 - Server operating system and version: Rocky Linux 8
Additional context
The problem is caused by the escapeTableWildcards() function, introduced in 0c1365753e.
This causes to the _ in the database name to be escaped as \_ in the SQL statement, which does not match anything - making icingaweb2 believe that the user has no privileges.
Unfortunately I cannot find the issue linked in the commit, so I am not sure why escapeTableWildcards() was implemented in the first place.
MySQL allows to use _ and % as wildcards in table names while granting access. (At least in versions <8.0.35 it seems)
So a grant all on icinga_db.* to icinga_db@'%' will not only grant access to database icinga_db, but also to database icinga2db.
That's why we escape these wildcards in the database name provided by the user.
If you drop the grant using the wildcard and issue it again with the underscore escaped, the credentials are properly detected:
grant all on `icinga\_db`.* to icinga_db@'%'
Oh thanks a lot, that makes sense. Works as intended with the correct grant statement.
Shouldn't this be fixed with 2.12.1? I had exactly that issue with an upgrade from 2.11.4.
I also couldn't find this bug listed in milestone 2.12.1.
This doesn't require a fix by us. An underscore shouldn't be used in table names, unless escaped as noted previousy.