Problems with sql permissions on icingaweb2

Icinga Web 2
1

Hi,
Have the following icinga packages on my Debian 9 system:

ii icinga-l10n 1.1.0-1.stretch all l10n (short for Localization) provides all translations available for Icinga.
ii icinga-php-common 1.0.0-1.stretch all Icinga PHP Common for Icinga Web 2
ii icinga-php-library 0.7.0-1.stretch all Icinga PHP Library for Icinga Web 2
ii icinga-php-thirdparty 0.10.0-2.stretch all Icinga PHP Thirdparty for Icinga Web 2
ii icinga2 2.13.1-1.stretch amd64 host and network monitoring system
ii icinga2-bin 2.13.1-1.stretch amd64 host and network monitoring system - daemon
ii icinga2-common 2.13.1-1.stretch all host and network monitoring system - common files
ii icinga2-doc 2.13.1-1.stretch all host and network monitoring system - documentation
ii icinga2-ido-mysql 2.13.1-1.stretch amd64 host and network monitoring system - MySQL support
ii icingacli 2.9.4-1.stretch all simple CLI tool for Icingaweb2 and its modules
ii icingaweb2 2.9.4-1.stretch all simple and responsive web interface for Icinga
ii icingaweb2-common 2.9.4-1.stretch all simple and responsive web interface for Icinga - common files
ii icingaweb2-module-doc 2.9.4-1.stretch all simple and responsive web interface for Icinga - documentation module
ii icingaweb2-module-monitoring 2.9.4-1.stretch all simple and responsive web interface for Icinga - monitoring module
ii php-icinga 2.9.4-1.stretch all PHP library to communicate with and use Icinga

Aplication log is full of the following:

16.11. 17:52
ERROR PDOException in /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php:401 with message: SQLSTATE[42000]: Syntax error or access violation: 1142 DELETE command denied to user ‘icinga_user’@‘localhost’ for table ‘icingaweb_rememberme’
#0 /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php(401): PDO->prepare(String)
#1 /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php(489): ipl\Sql\Connection->prepexec(String)
#2 /usr/share/php/Icinga/Web/RememberMe.php(170): ipl\Sql\Connection->delete(String, Array)
#3 /usr/share/icingaweb2/application/controllers/ApplicationStateController.php(70): Icinga\Web\RememberMe::removeExpired()
#4 /usr/share/icingaweb2/library/vendor/Zend/Controller/Action.php(507): Icinga\Controllers\ApplicationStateController->indexAction()
#5 /usr/share/php/Icinga/Web/Controller/Dispatcher.php(76): Zend_Controller_Action->dispatch(String)
#6 /usr/share/icingaweb2/library/vendor/Zend/Controller/Front.php(937): Icinga\Web\Controller\Dispatcher->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#7 /usr/share/php/Icinga/Application/Web.php(304): Zend_Controller_Front->dispatch(Object(Icinga\Web\Request), Object(Icinga\Web\Response))
#8 /usr/share/php/Icinga/Application/webrouter.php(107): Icinga\Application\Web->dispatch()
#9 /usr/share/icingaweb2/public/index.php(4): require_once(String)
#10 {main}

How can I fix this error?

2

This blog shows using GRANT ALL for icingaweb2. I don’t believe it needs to be that permissive, but I keep it in its own schema to isolate it.

GRANT ALL PRIVILEGES ON icingaweb.* TO 'icingaweb'@'localhost' IDENTIFIED BY 'icingaweb';
3

Hi,
Thanks for the suggestion, but my solution was slightly different. I was able to find, that I need to create additional table icingaweb_remember me. Found this here:

So I did it. I granted not full privileges, but only select insert update delete on this new table. And seems that problem was gone after that.

Tried to login with remember me option and got another error:

Failed to let user “admin” stay logged in: PDOException in /usr/share/icinga-php/ipl/vendor/ipl/sql/src/Connection.php:402 with message: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column ‘random_iv’ at row 1

Looked at column’s type and saw it set to varchar(24). Set it to varchar(256) and the new error was also gone.

I only did not understand, why did I need to create this new table. Why was it not in my DB already?

And also I see some grants created:
GRANT CREATE TEMPORARY TABLES, EXECUTE ON icinga\_users.* TO ‘icinga_username’@‘localhost’

But I can’t see no such db on my server. Can you look at your side? Do you have the same grant for such DB? Or is this some kind of install error?

4

I don’t recall having had to alter any tables on my install, unless part of an upgrade script.

For the icingaweb schema, I have that user:

GRANT 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