IcingaWeb: DB migration message after update to 2.12

Icinga Web
,
1

Hi,

after upgrading from IcingaWeb 2.11.4 to 2.12, I get this message under “Migrations”:

It seems that the currently used database user does not have the required privileges to execute the CREATE, SELECT, INSERT, UPDATE, DELETE, DROP, ALTER, CREATE VIEW, INDEX, EXECUTE SQL commands. Please provide an alternative user that has the appropriate credentials to resolve this issue.`

The user does have all this privileges on the icingaweb-db (show grants returns “ALL PRIVILEGES”). The only difference I can recognize is that select * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES; shows a privilege_type called “EXECUTE”, not “EXECUTE SQL”.

Database Server is MariaDB 10.11.4

Maybe a small incompatibility?

Best regards,

Markus

2

Hi, is the user used in the resources configuration of the icingaweb-db database the same one used to manually check the privileges (which seems to have all the required privileges)?

You can also explicitly check the privileges for a specific user as follows and share the output here (Note the database user might differ from the currently logged one in Icinga Web, so you need to use the username configured in the resources ini config of that database):

SHOW GRANTS FOR icingaweb-db-user;
3

Hello Yonas,

it is the same user as in resources.ini

The permission is restricted to a network address:

>show grants for ‘icingaweb_v211’@‘10.254.254.%’;
GRANT USAGE ON *.* TO icingaweb_v211@10.254.254.% IDENTIFIED BY PASSWORD <pass>
GRANT ALL PRIVILEGES ON `icingaweb_v211`.* TO `icingaweb_v211`@`10.254.254.%`

I already tried to create a user without the host-part and drop this user above - does not change anything.
Do you know what request icingaweb does to check the DB-User?

Note that I can apply the schema update by hand without problems; I just don’t understand why this message appears.

4

Icinga Web uses these queries to check MYSQL user privileges.

Database level privileges check:

SELECT PRIVILEGE_TYPE FROM information_schema.SCHEMA_PRIVILEGES
  WHERE GRANTEE = "'icingaweb_v211'@'10.254.254.%'" AND TABLE_SCHEMA = 'icingaweb_211'

If not all the required privileges are granted on database level, it will additionally perform checks for all Icinga Web database tables as follows:

SELECT PRIVILEGE_TYPE FROM information_schema.TABLE_PRIVILEGES
  WHERE GRANTEE = "'icingaweb_v211'@'10.254.254.%'" AND TABLE_SCHEMA = 'icingaweb_211' AND TABLE_NAME LIKE 'icingaweb_user_preference'
5

The result of:

SELECT PRIVILEGE_TYPE FROM information_schema.SCHEMA_PRIVILEGES WHERE GRANTEE = "'icingaweb_v211'@'10.254.254.%'" AND TABLE_SCHEMA = 'icingaweb_v211'

is

+-------------------------+
| PRIVILEGE_TYPE          |
+-------------------------+
| SELECT                  |
| INSERT                  |
| UPDATE                  |
| DELETE                  |
| CREATE                  |
| DROP                    |
| REFERENCES              |
| INDEX                   |
| ALTER                   |
| CREATE TEMPORARY TABLES |
| LOCK TABLES             |
| EXECUTE                 |
| CREATE VIEW             |
| SHOW VIEW               |
| CREATE ROUTINE          |
| ALTER ROUTINE           |
| EVENT                   |
| TRIGGER                 |
| DELETE HISTORY          |
+-------------------------+

which returns all privileges questioned in the WebGUI message, except that there is no “EXECUTE SQL” but an “EXECUTE” priv.

The table privileges query returns nothing (empty set).

6

Are there any pending migrations below the modules section? Might also be caused due to some module migrations.

7

Currently, there’s only the Web-DB itself pending for migration:

image

8

Then I don’t see why this form should’ve ever shown up.

it is the same user as in resources.ini

Are you sure about this? Just FYI, this has to be the Icinga Web authentication backend and not monitoring or Icinga DB backend resources.

9

Yes, I’ve double-checked.

The authentication user and group backends use a database connection named icingaweb_v211_db3, which has this user configured.

There’s a database for director and IDO, which use different users each.

Never mind, this is not a real problem, just wondering why.

10

Just as a suggestion can you try the following mysql permissions ?
Does it solve the permission Message in the Front-end ?

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `icinga2web_dbuser`.* TO `icingaweb2db`@`%`                                                             |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, CREATE TEMPORARY TABLES, EXECUTE ON `icingaweb2db`.* TO `icinga2web_dbuser`@`%`                           |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE VIEW ON `icingaweb2db`.`icingaweb_group_membership` TO `icinga2web_dbuser`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE VIEW ON `icingaweb2db`.`icingaweb_rememberme` TO `icinga2web_dbuser`@`%`       |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE VIEW ON `icingaweb2db`.`icingaweb_user` TO `icinga2web_dbuser`@`%`             |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE VIEW ON `icingaweb2db`.`icingaweb_group` TO `icinga2web_dbuser`@`%`            |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE VIEW ON `icingaweb2db`.`icingaweb_user_preference` TO `icinga2web_dbuser`@`%`  |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE VIEW ON `icingaweb2db`.`icingaweb_schema` TO `icinga2web_dbuser`@`%`           |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+

Regards

David

11

Hello again, first of all, sorry for the confusion about my statement:

It does not have to be the same as your authentication backend as you may also authenticate using some externals and LDAP. You just need to make sure what database is used to store the configurations by navigating to icingaweb2/config/general and checking the Configuration Database section.

And since your DB name also contains an underscore (icingaweb_v211_db3), you may have encountered the same problem as in https://github.com/Icinga/icingaweb2/issues/5147.

12

Thank you for linking this issue. I think this is exactly the problem I encounter. Will try on next schema update.