Provide new system check diagnostic for required database priviliges

[diosmosis]

Summary

Matomo does not report if the database user does not have enough privileges to perform installation and update logic. One would expect that there would be an error thrown in this case, but it seems possible for this to fail silently. In this case, a diagnostic that checked for required privileges before installing would be helpful.

Refs #17420 (comment)

[tsteur]

fyi I believe we may have this check in Matomo for WordPress already if I remember correctly. Not 100% sure though. Before working on this we could check what's there and potentially reuse ( or move it to core)

[sgiehl]

There is a check prior the installation here:

matomo/plugins/Installation/FormDatabaseSetup.php

Lines 209 to 289 in dd3af29

	/**
	* Validation rule that checks that the supplied DB user has enough privileges.
	*
	* The following privileges are required for Matomo to run:
	* - CREATE
	* - ALTER
	* - SELECT
	* - INSERT
	* - UPDATE
	* - DELETE
	* - DROP
	* - CREATE TEMPORARY TABLES
	*
	*/
	class RuleCheckUserPrivileges extends HTML_QuickForm2_Rule
	{
	public const TEST_TABLE_NAME = 'piwik_test_table';
	public const TEST_TEMP_TABLE_NAME = 'piwik_test_table_temp';
	/**
	* Checks that the DB user entered in the form has the necessary privileges for Piwik
	* to run.
	*/
	public function validateOwner()
	{
	// try and create the database object
	try {
	$this->createDatabaseObject();
	} catch (Exception $ex) {
	if ($this->isAccessDenied($ex)) {
	return false;
	} else {
	return true; // if we can't create the database object, skip this validation
	}
	}
	$db = Db::get();
	try {
	// try to drop tables before running privilege tests
	$this->dropExtraTables($db);
	} catch (Exception $ex) {
	if ($this->isAccessDenied($ex)) {
	return false;
	} else {
	throw $ex;
	}
	}
	// check each required privilege by running a query that uses it
	foreach (self::getRequiredPrivileges() as $privilegeType => $queries) {
	if (!is_array($queries)) {
	$queries = array($queries);
	}
	foreach ($queries as $sql) {
	try {
	if (in_array($privilegeType, array('SELECT'))) {
	$ret = $db->fetchAll($sql);
	} else {
	$ret = $db->exec($sql);
	}
	// In case an exception is not thrown check the return
	if ($ret === -1) {
	return false;
	}
	} catch (Exception $ex) {
	if ($this->isAccessDenied($ex)) {
	return false;
	} else {
	throw new Exception("Test SQL failed to execute: $sql\nError: " . $ex->getMessage());
	}
	}
	}
	}
	// remove extra tables that were created
	$this->dropExtraTables($db);
	return true;
	}

But if the permissions are changed after Matomo was set up, I guess that would not automatically be detected somewhere.