/libraries/classes/Server/Privileges.php

Large files files are truncated, but you can click here to view the full file

<?php
/**
 * set of functions with the Privileges section in pma
 */

declare(strict_types=1);

namespace PhpMyAdmin\Server;

use mysqli_stmt;
use PhpMyAdmin\DatabaseInterface;
use PhpMyAdmin\Html\Generator;
use PhpMyAdmin\Html\MySQLDocumentation;
use PhpMyAdmin\Message;
use PhpMyAdmin\Query\Compatibility;
use PhpMyAdmin\Relation;
use PhpMyAdmin\RelationCleanup;
use PhpMyAdmin\ResponseRenderer;
use PhpMyAdmin\Template;
use PhpMyAdmin\Url;
use PhpMyAdmin\Util;

use function __;
use function array_map;
use function array_merge;
use function array_unique;
use function count;
use function explode;
use function htmlspecialchars;
use function implode;
use function in_array;
use function is_array;
use function is_scalar;
use function is_string;
use function json_decode;
use function ksort;
use function max;
use function mb_chr;
use function mb_strpos;
use function mb_strrpos;
use function mb_strtolower;
use function mb_strtoupper;
use function mb_substr;
use function preg_match;
use function preg_replace;
use function sprintf;
use function str_contains;
use function str_replace;
use function strlen;
use function trim;
use function uksort;

/**
 * Privileges class
 */
class Privileges
{
    /** @var Template */
    public $template;

    /** @var RelationCleanup */
    private $relationCleanup;

    /** @var DatabaseInterface */
    public $dbi;

    /** @var Relation */
    public $relation;

    /** @var Plugins */
    private $plugins;

    /**
     * @param Template          $template        Template object
     * @param DatabaseInterface $dbi             DatabaseInterface object
     * @param Relation          $relation        Relation object
     * @param RelationCleanup   $relationCleanup RelationCleanup object
     */
    public function __construct(
        Template $template,
        $dbi,
        Relation $relation,
        RelationCleanup $relationCleanup,
        Plugins $plugins
    ) {
        $this->template = $template;
        $this->dbi = $dbi;
        $this->relation = $relation;
        $this->relationCleanup = $relationCleanup;
        $this->plugins = $plugins;
    }

    /**
     * Escapes wildcard in a database+table specification
     * before using it in a GRANT statement.
     *
     * Escaping a wildcard character in a GRANT is only accepted at the global
     * or database level, not at table level; this is why I remove
     * the escaping character. Internally, in mysql.tables_priv.Db there are
     * no escaping (for example test_db) but in mysql.db you'll see test\_db
     * for a db-specific privilege.
     *
     * @param string $dbname    Database name
     * @param string $tablename Table name
     *
     * @return string the escaped (if necessary) database.table
     */
    public function wildcardEscapeForGrant(string $dbname, string $tablename): string
    {
        if (strlen($dbname) === 0) {
            return '*.*';
        }

        if (strlen($tablename) > 0) {
            return Util::backquote(
                Util::unescapeMysqlWildcards($dbname)
            )
            . '.' . Util::backquote($tablename);
        }

        return Util::backquote($dbname) . '.*';
    }

    /**
     * Generates a condition on the user name
     *
     * @param string|null $initial the user's initial
     *
     * @return string   the generated condition
     */
    public function rangeOfUsers($initial = '')
    {
        // strtolower() is used because the User field
        // might be BINARY, so LIKE would be case sensitive
        if ($initial === null || $initial === '') {
            return '';
        }

        return " WHERE `User` LIKE '"
            . $this->dbi->escapeString($initial) . "%'"
            . " OR `User` LIKE '"
            . $this->dbi->escapeString(mb_strtolower($initial))
            . "%'";
    }

    /**
     * Parses privileges into an array, it modifies the array
     *
     * @param array $row Results row from
     */
    public function fillInTablePrivileges(array &$row): void
    {
        $row1 = $this->dbi->fetchSingleRow('SHOW COLUMNS FROM `mysql`.`tables_priv` LIKE \'Table_priv\';', 'ASSOC');
        // note: in MySQL 5.0.3 we get "Create View', 'Show view';
        // the View for Create is spelled with uppercase V
        // the view for Show is spelled with lowercase v
        // and there is a space between the words

        $avGrants = explode(
            '\',\'',
            mb_substr(
                $row1['Type'],
                mb_strpos($row1['Type'], '(') + 2,
                mb_strpos($row1['Type'], ')')
                - mb_strpos($row1['Type'], '(') - 3
            )
        );

        $usersGrants = explode(',', $row['Table_priv']);

        foreach ($avGrants as $currentGrant) {
            $row[$currentGrant . '_priv'] = in_array($currentGrant, $usersGrants) ? 'Y' : 'N';
        }

        unset($row['Table_priv']);
    }

    /**
     * Extracts the privilege information of a priv table row
     *
     * @param array|null $row        the row
     * @param bool       $enableHTML add <dfn> tag with tooltips
     * @param bool       $tablePrivs whether row contains table privileges
     *
     * @return array
     *
     * @global resource $user_link the database connection
     */
    public function extractPrivInfo($row = null, $enableHTML = false, $tablePrivs = false)
    {
        if ($tablePrivs) {
            $grants = $this->getTableGrantsArray();
        } else {
            $grants = $this->getGrantsArray();
        }

        if ($row !== null && isset($row['Table_priv'])) {
            $this->fillInTablePrivileges($row);
        }

        $privs = [];
        $allPrivileges = true;
        foreach ($grants as $currentGrant) {
            if (
                ($row === null || ! isset($row[$currentGrant[0]]))
                && ($row !== null || ! isset($GLOBALS[$currentGrant[0]]))
            ) {
                continue;
            }

            if (
                ($row !== null && $row[$currentGrant[0]] === 'Y')
                || ($row === null
                && ($GLOBALS[$currentGrant[0]] === 'Y'
                || (is_array($GLOBALS[$currentGrant[0]])
                && count($GLOBALS[$currentGrant[0]]) == $_REQUEST['column_count']
                && empty($GLOBALS[$currentGrant[0] . '_none']))))
            ) {
                if ($enableHTML) {
                    $privs[] = '<dfn title="' . $currentGrant[2] . '">'
                    . $currentGrant[1] . '</dfn>';
                } else {
                    $privs[] = $currentGrant[1];
                }
            } elseif (
                ! empty($GLOBALS[$currentGrant[0]])
                && is_array($GLOBALS[$currentGrant[0]])
                && empty($GLOBALS[$currentGrant[0] . '_none'])
            ) {
                // Required for proper escaping of ` (backtick) in a column name
                $grantCols = array_map(
                    /**
                     * @param string $val
                     *
                     * @return string
                     */
                    static function ($val) {
                        return Util::backquote($val);
                    },
                    $GLOBALS[$currentGrant[0]]
                );

                if ($enableHTML) {
                    $privs[] = '<dfn title="' . $currentGrant[2] . '">'
                        . $currentGrant[1] . '</dfn>'
                        . ' (' . implode(', ', $grantCols) . ')';
                } else {
                    $privs[] = $currentGrant[1]
                        . ' (' . implode(', ', $grantCols) . ')';
                }
            } else {
                $allPrivileges = false;
            }
        }

        if (empty($privs)) {
            if ($enableHTML) {
                $privs[] = '<dfn title="' . __('No privileges.') . '">USAGE</dfn>';
            } else {
                $privs[] = 'USAGE';
            }
        } elseif ($allPrivileges && (! isset($_POST['grant_count']) || count($privs) == $_POST['grant_count'])) {
            if ($enableHTML) {
                $privs = [
                    '<dfn title="'
                    . __('Includes all privileges except GRANT.')
                    . '">ALL PRIVILEGES</dfn>',
                ];
            } else {
                $privs = ['ALL PRIVILEGES'];
            }
        }

        return $privs;
    }

    /**
     * Returns an array of table grants and their descriptions
     *
     * @return array array of table grants
     */
    public function getTableGrantsArray()
    {
        return [
            [
                'Delete',
                'DELETE',
                __('Allows deleting data.'),
            ],
            [
                'Create',
                'CREATE',
                __('Allows creating new tables.'),
            ],
            [
                'Drop',
                'DROP',
                __('Allows dropping tables.'),
            ],
            [
                'Index',
                'INDEX',
                __('Allows creating and dropping indexes.'),
            ],
            [
                'Alter',
                'ALTER',
                __('Allows altering the structure of existing tables.'),
            ],
            [
                'Create View',
                'CREATE_VIEW',
                __('Allows creating new views.'),
            ],
            [
                'Show view',
                'SHOW_VIEW',
                __('Allows performing SHOW CREATE VIEW queries.'),
            ],
            [
                'Trigger',
                'TRIGGER',
                __('Allows creating and dropping triggers.'),
            ],
        ];
    }

    /**
     * Get the grants array which contains all the privilege types
     * and relevant grant messages
     *
     * @return array
     */
    public function getGrantsArray()
    {
        return [
            [
                'Select_priv',
                'SELECT',
                __('Allows reading data.'),
            ],
            [
                'Insert_priv',
                'INSERT',
                __('Allows inserting and replacing data.'),
            ],
            [
                'Update_priv',
                'UPDATE',
                __('Allows changing data.'),
            ],
            [
                'Delete_priv',
                'DELETE',
                __('Allows deleting data.'),
            ],
            [
                'Create_priv',
                'CREATE',
                __('Allows creating new databases and tables.'),
            ],
            [
                'Drop_priv',
                'DROP',
                __('Allows dropping databases and tables.'),
            ],
            [
                'Reload_priv',
                'RELOAD',
                __('Allows reloading server settings and flushing the server\'s caches.'),
            ],
            [
                'Shutdown_priv',
                'SHUTDOWN',
                __('Allows shutting down the server.'),
            ],
            [
                'Process_priv',
                'PROCESS',
                __('Allows viewing processes of all users.'),
            ],
            [
                'File_priv',
                'FILE',
                __('Allows importing data from and exporting data into files.'),
            ],
            [
                'References_priv',
                'REFERENCES',
                __('Has no effect in this MySQL version.'),
            ],
            [
                'Index_priv',
                'INDEX',
                __('Allows creating and dropping indexes.'),
            ],
            [
                'Alter_priv',
                'ALTER',
                __('Allows altering the structure of existing tables.'),
            ],
            [
                'Show_db_priv',
                'SHOW DATABASES',
                __('Gives access to the complete list of databases.'),
            ],
            [
                'Super_priv',
                'SUPER',
                __(
                    'Allows connecting, even if maximum number of connections '
                    . 'is reached; required for most administrative operations '
                    . 'like setting global variables or killing threads of other users.'
                ),
            ],
            [
                'Create_tmp_table_priv',
                'CREATE TEMPORARY TABLES',
                __('Allows creating temporary tables.'),
            ],
            [
                'Lock_tables_priv',
                'LOCK TABLES',
                __('Allows locking tables for the current thread.'),
            ],
            [
                'Repl_slave_priv',
                'REPLICATION SLAVE',
                __('Needed for the replication slaves.'),
            ],
            [
                'Repl_client_priv',
                'REPLICATION CLIENT',
                __('Allows the user to ask where the slaves / masters are.'),
            ],
            [
                'Create_view_priv',
                'CREATE VIEW',
                __('Allows creating new views.'),
            ],
            [
                'Event_priv',
                'EVENT',
                __('Allows to set up events for the event scheduler.'),
            ],
            [
                'Trigger_priv',
                'TRIGGER',
                __('Allows creating and dropping triggers.'),
            ],
            // for table privs:
            [
                'Create View_priv',
                'CREATE VIEW',
                __('Allows creating new views.'),
            ],
            [
                'Show_view_priv',
                'SHOW VIEW',
                __('Allows performing SHOW CREATE VIEW queries.'),
            ],
            // for table privs:
            [
                'Show view_priv',
                'SHOW VIEW',
                __('Allows performing SHOW CREATE VIEW queries.'),
            ],
            [
                'Delete_history_priv',
                'DELETE HISTORY',
                // phpcs:ignore Generic.Files.LineLength.TooLong
                /* l10n: https://mariadb.com/kb/en/library/grant/#table-privileges "Remove historical rows from a table using the DELETE HISTORY statement" */
                __('Allows deleting historical rows.'),
            ],
            [
                // This was finally removed in the following MariaDB versions
                // @see https://jira.mariadb.org/browse/MDEV-20382
                'Delete versioning rows_priv',
                'DELETE HISTORY',
                // phpcs:ignore Generic.Files.LineLength.TooLong
                /* l10n: https://mariadb.com/kb/en/library/grant/#table-privileges "Remove historical rows from a table using the DELETE HISTORY statement" */
                __('Allows deleting historical rows.'),
            ],
            [
                'Create_routine_priv',
                'CREATE ROUTINE',
                __('Allows creating stored routines.'),
            ],
            [
                'Alter_routine_priv',
                'ALTER ROUTINE',
                __('Allows altering and dropping stored routines.'),
            ],
            [
                'Create_user_priv',
                'CREATE USER',
                __('Allows creating, dropping and renaming user accounts.'),
            ],
            [
                'Execute_priv',
                'EXECUTE',
                __('Allows executing stored routines.'),
            ],
        ];
    }

    /**
     * Get sql query for display privileges table
     *
     * @param string $db       the database
     * @param string $table    the table
     * @param string $username username for database connection
     * @param string $hostname hostname for database connection
     *
     * @return string sql query
     */
    public function getSqlQueryForDisplayPrivTable($db, $table, $username, $hostname)
    {
        if ($db === '*') {
            return 'SELECT * FROM `mysql`.`user`'
                . " WHERE `User` = '" . $this->dbi->escapeString($username) . "'"
                . " AND `Host` = '" . $this->dbi->escapeString($hostname) . "';";
        }

        if ($table === '*') {
            return 'SELECT * FROM `mysql`.`db`'
                . " WHERE `User` = '" . $this->dbi->escapeString($username) . "'"
                . " AND `Host` = '" . $this->dbi->escapeString($hostname) . "'"
                . " AND `Db` = '" . $this->dbi->escapeString($db) . "'";
        }

        return 'SELECT `Table_priv`'
            . ' FROM `mysql`.`tables_priv`'
            . " WHERE `User` = '" . $this->dbi->escapeString($username) . "'"
            . " AND `Host` = '" . $this->dbi->escapeString($hostname) . "'"
            . " AND `Db` = '" . $this->dbi->escapeString(Util::unescapeMysqlWildcards($db)) . "'"
            . " AND `Table_name` = '" . $this->dbi->escapeString($table) . "';";
    }

    /**
     * Sets the user group from request values
     *
     * @param string $username  username
     * @param string $userGroup user group to set
     */
    public function setUserGroup($username, $userGroup): void
    {
        $userGroup = $userGroup ?? '';
        $cfgRelation = $this->relation->getRelationsParam();
        if (empty($cfgRelation['db']) || empty($cfgRelation['users']) || empty($cfgRelation['usergroups'])) {
            return;
        }

        $userTable = Util::backquote($cfgRelation['db'])
            . '.' . Util::backquote($cfgRelation['users']);

        $sqlQuery = 'SELECT `usergroup` FROM ' . $userTable
            . " WHERE `username` = '" . $this->dbi->escapeString($username) . "'";
        $oldUserGroup = $this->dbi->fetchValue($sqlQuery, 0, 0, DatabaseInterface::CONNECT_CONTROL);

        if ($oldUserGroup === false) {
            $updQuery = 'INSERT INTO ' . $userTable . '(`username`, `usergroup`)'
                . " VALUES ('" . $this->dbi->escapeString($username) . "', "
                . "'" . $this->dbi->escapeString($userGroup) . "')";
        } else {
            if (empty($userGroup)) {
                $updQuery = 'DELETE FROM ' . $userTable
                    . " WHERE `username`='" . $this->dbi->escapeString($username) . "'";
            } elseif ($oldUserGroup != $userGroup) {
                $updQuery = 'UPDATE ' . $userTable
                    . " SET `usergroup`='" . $this->dbi->escapeString($userGroup) . "'"
                    . " WHERE `username`='" . $this->dbi->escapeString($username) . "'";
            }
        }

        if (! isset($updQuery)) {
            return;
        }

        $this->relation->queryAsControlUser($updQuery);
    }

    /**
     * Displays the privileges form table
     *
     * @param string $db     the database
     * @param string $table  the table
     * @param bool   $submit whether to display the submit button or not
     *
     * @return string html snippet
     *
     * @global array     $cfg         the phpMyAdmin configuration
     * @global resource  $user_link   the database connection
     */
    public function getHtmlToDisplayPrivilegesTable(
        $db = '*',
        $table = '*',
        $submit = true
    ) {
        $sqlQuery = '';

        if ($db === '*') {
            $table = '*';
        }

        $username = '';
        $hostname = '';
        $row = [];
        if (isset($GLOBALS['username'])) {
            $username = $GLOBALS['username'];
            $hostname = $GLOBALS['hostname'];
            $sqlQuery = $this->getSqlQueryForDisplayPrivTable($db, $table, $username, $hostname);
            $row = $this->dbi->fetchSingleRow($sqlQuery);
        }

        if (empty($row)) {
            if ($table === '*' && $this->dbi->isSuperUser()) {
                $row = [];
                if ($db === '*') {
                    $sqlQuery = 'SHOW COLUMNS FROM `mysql`.`user`;';
                } elseif ($table === '*') {
                    $sqlQuery = 'SHOW COLUMNS FROM `mysql`.`db`;';
                }

                $res = $this->dbi->query($sqlQuery);
                while ($row1 = $this->dbi->fetchRow($res)) {
                    if (mb_substr($row1[0], 0, 4) === 'max_') {
                        $row[$row1[0]] = 0;
                    } elseif (mb_substr($row1[0], 0, 5) === 'x509_' || mb_substr($row1[0], 0, 4) === 'ssl_') {
                        $row[$row1[0]] = '';
                    } else {
                        $row[$row1[0]] = 'N';
                    }
                }

                $this->dbi->freeResult($res);
            } elseif ($table === '*') {
                $row = [];
            } else {
                $row = ['Table_priv' => ''];
            }
        }

        if (isset($row['Table_priv'])) {
            $this->fillInTablePrivileges($row);

            // get columns
            $res = $this->dbi->tryQuery(
                'SHOW COLUMNS FROM '
                . Util::backquote(
                    Util::unescapeMysqlWildcards($db)
                )
                . '.' . Util::backquote($table) . ';'
            );
            $columns = [];
            if ($res) {
                while ($row1 = $this->dbi->fetchRow($res)) {
                    $columns[$row1[0]] = [
                        'Select' => false,
                        'Insert' => false,
                        'Update' => false,
                        'References' => false,
                    ];
                }

                $this->dbi->freeResult($res);
            }
        }

        if (! empty($columns)) {
            $res = $this->dbi->query(
                'SELECT `Column_name`, `Column_priv`'
                . ' FROM `mysql`.`columns_priv`'
                . ' WHERE `User`'
                . ' = \'' . $this->dbi->escapeString($username) . "'"
                . ' AND `Host`'
                . ' = \'' . $this->dbi->escapeString($hostname) . "'"
                . ' AND `Db`'
                . ' = \'' . $this->dbi->escapeString(
                    Util::unescapeMysqlWildcards($db)
                ) . "'"
                . ' AND `Table_name`'
                . ' = \'' . $this->dbi->escapeString($table) . '\';'
            );

            while ($row1 = $this->dbi->fetchRow($res)) {
                $row1[1] = explode(',', $row1[1]);
                foreach ($row1[1] as $current) {
                    $columns[$row1[0]][$current] = true;
                }
            }

            $this->dbi->freeResult($res);
        }

        return $this->template->render('server/privileges/privileges_table', [
            'is_global' => $db === '*',
            'is_database' => $table === '*',
            'row' => $row,
            'columns' => $columns ?? [],
            'has_submit' => $submit,
            'supports_references_privilege' => Compatibility::supportsReferencesPrivilege($this->dbi),
            'is_mariadb' => $this->dbi->isMariaDB(),
        ]);
    }

    /**
     * Get the HTML snippet for routine specific privileges
     *
     * @param string $username  username for database connection
     * @param string $hostname  hostname for database connection
     * @param string $db        the database
     * @param string $routine   the routine
     * @param string $urlDbname url encoded db name
     *
     * @return string
     */
    public function getHtmlForRoutineSpecificPrivileges(
        $username,
        $hostname,
        $db,
        $routine,
        $urlDbname
    ) {
        $privileges = $this->getRoutinePrivileges($username, $hostname, $db, $routine);

        return $this->template->render('server/privileges/edit_routine_privileges', [
            'username' => $username,
            'hostname' => $hostname,
            'database' => $db,
            'routine' => $routine,
            'privileges' => $privileges,
            'dbname' => $urlDbname,
            'current_user' => $this->dbi->getCurrentUser(),
        ]);
    }

    /**
     * Displays the fields used by the "new user" form as well as the
     * "change login information / copy user" form.
     *
     * @param string $mode are we creating a new user or are we just
     *                     changing  one? (allowed values: 'new', 'change')
     * @param string $user User name
     * @param string $host Host name
     *
     * @return string  a HTML snippet
     */
    public function getHtmlForLoginInformationFields(
        $mode = 'new',
        $user = null,
        $host = null
    ) {
        global $pred_username, $pred_hostname, $username, $hostname, $new_username;

        [$usernameLength, $hostnameLength] = $this->getUsernameAndHostnameLength();

        if (isset($username) && strlen($username) === 0) {
            $pred_username = 'any';
        }

        $currentUser = $this->dbi->fetchValue('SELECT USER();');
        $thisHost = null;
        if (! empty($currentUser)) {
            $thisHost = str_replace(
                '\'',
                '',
                mb_substr(
                    $currentUser,
                    mb_strrpos($currentUser, '@') + 1
                )
            );
        }

        if (! isset($pred_hostname) && isset($hostname)) {
            switch (mb_strtolower($hostname)) {
                case 'localhost':
                case '127.0.0.1':
                    $pred_hostname = 'localhost';
                    break;
                case '%':
                    $pred_hostname = 'any';
                    break;
                default:
                    $pred_hostname = 'userdefined';
                    break;
            }
        }

        $serverVersion = $this->dbi->getVersion();
        $authPlugin = $this->getCurrentAuthenticationPlugin($mode, $user, $host);

        $isNew = (Compatibility::isMySqlOrPerconaDb() && $serverVersion >= 50507)
            || (Compatibility::isMariaDb() && $serverVersion >= 50200);

        $activeAuthPlugins = ['mysql_native_password' => __('Native MySQL authentication')];
        if ($isNew) {
            $activeAuthPlugins = $this->plugins->getAuthentication();
            if (isset($activeAuthPlugins['mysql_old_password'])) {
                unset($activeAuthPlugins['mysql_old_password']);
            }
        }

        return $this->template->render('server/privileges/login_information_fields', [
            'pred_username' => $pred_username ?? null,
            'pred_hostname' => $pred_hostname ?? null,
            'username_length' => $usernameLength,
            'hostname_length' => $hostnameLength,
            'username' => $username ?? null,
            'new_username' => $new_username ?? null,
            'hostname' => $hostname ?? null,
            'this_host' => $thisHost,
            'is_change' => $mode === 'change',
            'auth_plugin' => $authPlugin,
            'active_auth_plugins' => $activeAuthPlugins,
            'is_new' => $isNew,
        ]);
    }

    /**
     * Get username and hostname length
     *
     * @return array username length and hostname length
     */
    public function getUsernameAndHostnameLength()
    {
        /* Fallback values */
        $usernameLength = 16;
        $hostnameLength = 41;

        /* Try to get real lengths from the database */
        $fieldsInfo = $this->dbi->fetchResult(
            'SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH '
            . 'FROM information_schema.columns '
            . "WHERE table_schema = 'mysql' AND table_name = 'user' "
            . "AND COLUMN_NAME IN ('User', 'Host')"
        );
        foreach ($fieldsInfo as $val) {
            if ($val['COLUMN_NAME'] === 'User') {
                $usernameLength = $val['CHARACTER_MAXIMUM_LENGTH'];
            } elseif ($val['COLUMN_NAME'] === 'Host') {
                $hostnameLength = $val['CHARACTER_MAXIMUM_LENGTH'];
            }
        }

        return [
            $usernameLength,
            $hostnameLength,
        ];
    }

    /**
     * Get current authentication plugin in use - for a user or globally
     *
     * @param string $mode     are we creating a new user or are we just
     *                         changing  one? (allowed values: 'new', 'change')
     * @param string $username User name
     * @param string $hostname Host name
     *
     * @return string authentication plugin in use
     */
    public function getCurrentAuthenticationPlugin(
        $mode = 'new',
        $username = null,
        $hostname = null
    ) {
        global $dbi;

        /* Fallback (standard) value */
        $authenticationPlugin = 'mysql_native_password';
        $serverVersion = $this->dbi->getVersion();

        if (isset($username, $hostname) && $mode === 'change') {
            $row = $this->dbi->fetchSingleRow(
                'SELECT `plugin` FROM `mysql`.`user` WHERE `User` = "'
                . $dbi->escapeString($username)
                . '" AND `Host` = "'
                . $dbi->escapeString($hostname)
                . '" LIMIT 1'
            );
            // Table 'mysql'.'user' may not exist for some previous
            // versions of MySQL - in that case consider fallback value
            if (is_array($row) && isset($row['plugin'])) {
                $authenticationPlugin = $row['plugin'];
            }
        } elseif ($mode === 'change') {
            [$username, $hostname] = $this->dbi->getCurrentUserAndHost();

            $row = $this->dbi->fetchSingleRow(
                'SELECT `plugin` FROM `mysql`.`user` WHERE `User` = "'
                . $dbi->escapeString($username)
                . '" AND `Host` = "'
                . $dbi->escapeString($hostname)
                . '"'
            );
            if (is_array($row) && isset($row['plugin'])) {
                $authenticationPlugin = $row['plugin'];
            }
        } elseif ($serverVersion >= 50702) {
            $row = $this->dbi->fetchSingleRow('SELECT @@default_authentication_plugin');
            $authenticationPlugin = is_array($row) ? $row['@@default_authentication_plugin'] : null;
        }

        return $authenticationPlugin;
    }

    /**
     * Returns all the grants for a certain user on a certain host
     * Used in the export privileges for all users section
     *
     * @param string $user User name
     * @param string $host Host name
     *
     * @return string containing all the grants text
     */
    public function getGrants($user, $host)
    {
        $grants = $this->dbi->fetchResult(
            "SHOW GRANTS FOR '"
            . $this->dbi->escapeString($user) . "'@'"
            . $this->dbi->escapeString($host) . "'"
        );
        $response = '';
        foreach ($grants as $oneGrant) {
            $response .= $oneGrant . ";\n\n";
        }

        return $response;
    }

    /**
     * Update password and get message for password updating
     *
     * @param string $errorUrl error url
     * @param string $username username
     * @param string $hostname hostname
     *
     * @return Message success or error message after updating password
     */
    public function updatePassword($errorUrl, $username, $hostname)
    {
        global $dbi;

        // similar logic in /user-password
        $message = null;

        if (isset($_POST['pma_pw'], $_POST['pma_pw2']) && empty($_POST['nopass'])) {
            if ($_POST['pma_pw'] != $_POST['pma_pw2']) {
                $message = Message::error(__('The passwords aren\'t the same!'));
            } elseif (empty($_POST['pma_pw']) || empty($_POST['pma_pw2'])) {
                $message = Message::error(__('The password is empty!'));
            }
        }

        // here $nopass could be == 1
        if ($message === null) {
            $hashingFunction = 'PASSWORD';
            $serverVersion = $this->dbi->getVersion();
            $authenticationPlugin = ($_POST['authentication_plugin'] ?? $this->getCurrentAuthenticationPlugin(
                'change',
                $username,
                $hostname
            ));

            // Use 'ALTER USER ...' syntax for MySQL 5.7.6+
            if (Compatibility::isMySqlOrPerconaDb() && $serverVersion >= 50706) {
                if ($authenticationPlugin !== 'mysql_old_password') {
                    $queryPrefix = "ALTER USER '"
                        . $this->dbi->escapeString($username)
                        . "'@'" . $this->dbi->escapeString($hostname) . "'"
                        . ' IDENTIFIED WITH '
                        . $authenticationPlugin
                        . " BY '";
                } else {
                    $queryPrefix = "ALTER USER '"
                        . $this->dbi->escapeString($username)
                        . "'@'" . $this->dbi->escapeString($hostname) . "'"
                        . " IDENTIFIED BY '";
                }

                // in $sql_query which will be displayed, hide the password
                $sqlQuery = $queryPrefix . "*'";

                $localQuery = $queryPrefix
                    . $this->dbi->escapeString($_POST['pma_pw']) . "'";
            } elseif (Compatibility::isMariaDb() && $serverVersion >= 10000) {
                // MariaDB uses "SET PASSWORD" syntax to change user password.
                // On Galera cluster only DDL queries are replicated, since
                // users are stored in MyISAM storage engine.
                $queryPrefix = "SET PASSWORD FOR  '"
                    . $this->dbi->escapeString($username)
                    . "'@'" . $this->dbi->escapeString($hostname) . "'"
                    . " = PASSWORD ('";
                $sqlQuery = $localQuery = $queryPrefix
                    . $this->dbi->escapeString($_POST['pma_pw']) . "')";
            } elseif (Compatibility::isMariaDb() && $serverVersion >= 50200 && $this->dbi->isSuperUser()) {
                // Use 'UPDATE `mysql`.`user` ...' Syntax for MariaDB 5.2+
                if ($authenticationPlugin === 'mysql_native_password') {
                    // Set the hashing method used by PASSWORD()
                    // to be 'mysql_native_password' type
                    $this->dbi->tryQuery('SET old_passwords = 0;');
                } elseif ($authenticationPlugin === 'sha256_password') {
                    // Set the hashing method used by PASSWORD()
                    // to be 'sha256_password' type
                    $this->dbi->tryQuery('SET `old_passwords` = 2;');
                }

                $hashedPassword = $this->getHashedPassword($_POST['pma_pw']);

                $sqlQuery = 'SET PASSWORD FOR \''
                    . $this->dbi->escapeString($username)
                    . '\'@\'' . $this->dbi->escapeString($hostname) . '\' = '
                    . ($_POST['pma_pw'] == ''
                        ? '\'\''
                        : $hashingFunction . '(\''
                        . preg_replace('@.@s', '*', $_POST['pma_pw']) . '\')');

                $localQuery = 'UPDATE `mysql`.`user` SET '
                    . " `authentication_string` = '" . $hashedPassword
                    . "', `Password` = '', "
                    . " `plugin` = '" . $authenticationPlugin . "'"
                    . " WHERE `User` = '" . $dbi->escapeString($username)
                    . "' AND Host = '" . $dbi->escapeString($hostname) . "';";
            } else {
                // USE 'SET PASSWORD ...' syntax for rest of the versions
                // Backup the old value, to be reset later
                $row = $this->dbi->fetchSingleRow('SELECT @@old_passwords;');
                $origValue = $row['@@old_passwords'];
                $updatePluginQuery = 'UPDATE `mysql`.`user` SET'
                    . " `plugin` = '" . $authenticationPlugin . "'"
                    . " WHERE `User` = '" . $dbi->escapeString($username)
                    . "' AND Host = '" . $dbi->escapeString($hostname) . "';";

                // Update the plugin for the user
                if (! $this->dbi->tryQuery($updatePluginQuery)) {
                    Generator::mysqlDie(
                        $this->dbi->getError(),
                        $updatePluginQuery,
                        false,
                        $errorUrl
                    );
                }

                $this->dbi->tryQuery('FLUSH PRIVILEGES;');

                if ($authenticationPlugin === 'mysql_native_password') {
                    // Set the hashing method used by PASSWORD()
                    // to be 'mysql_native_password' type
                    $this->dbi->tryQuery('SET old_passwords = 0;');
                } elseif ($authenticationPlugin === 'sha256_password') {
                    // Set the hashing method used by PASSWORD()
                    // to be 'sha256_password' type
                    $this->dbi->tryQuery('SET `old_passwords` = 2;');
                }

                $sqlQuery = 'SET PASSWORD FOR \''
                    . $this->dbi->escapeString($username)
                    . '\'@\'' . $this->dbi->escapeString($hostname) . '\' = '
                    . ($_POST['pma_pw'] == ''
                        ? '\'\''
                        : $hashingFunction . '(\''
                        . preg_replace('@.@s', '*', $_POST['pma_pw']) . '\')');

                $localQuery = 'SET PASSWORD FOR \''
                    . $this->dbi->escapeString($username)
                    . '\'@\'' . $this->dbi->escapeString($hostname) . '\' = '
                    . ($_POST['pma_pw'] == '' ? '\'\'' : $hashingFunction
                    . '(\'' . $this->dbi->escapeString($_POST['pma_pw']) . '\')');
            }

            if (! $this->dbi->tryQuery($localQuery)) {
                Generator::mysqlDie(
                    $this->dbi->getError(),
                    $sqlQuery,
                    false,
                    $errorUrl
                );
            }

            // Flush privileges after successful password change
            $this->dbi->tryQuery('FLUSH PRIVILEGES;');

            $message = Message::success(
                __('The password for %s was changed successfully.')
            );
            $message->addParam('\'' . $username . '\'@\'' . $hostname . '\'');
            if (isset($origValue)) {
                $this->dbi->tryQuery('SET `old_passwords` = ' . $origValue . ';');
            }
        }

        return $message;
    }

    /**
     * Revokes privileges and get message and SQL query for privileges revokes
     *
     * @param string $dbname    database name
     * @param string $tablename table name
     * @param string $username  username
     * @param string $hostname  host name
     * @param string $itemType  item type
     *
     * @return array ($message, $sql_query)
     */
    public function getMessageAndSqlQueryForPrivilegesRevoke(
        string $dbname,
        string $tablename,
        $username,
        $hostname,
        $itemType
    ) {
        $dbAndTable = $this->wildcardEscapeForGrant($dbname, $tablename);

        $sqlQuery0 = 'REVOKE ALL PRIVILEGES ON ' . $itemType . ' ' . $dbAndTable
            . ' FROM \''
            . $this->dbi->escapeString($username) . '\'@\''
            . $this->dbi->escapeString($hostname) . '\';';

        $sqlQuery1 = 'REVOKE GRANT OPTION ON ' . $itemType . ' ' . $dbAndTable
            . ' FROM \'' . $this->dbi->escapeString($username) . '\'@\''
            . $this->dbi->escapeString($hostname) . '\';';

        $this->dbi->query($sqlQuery0);
        if (! $this->dbi->tryQuery($sqlQuery1)) {
            // this one may fail, too...
            $sqlQuery1 = '';
        }

        $sqlQuery = $sqlQuery0 . ' ' . $sqlQuery1;
        $message = Message::success(
            __('You have revoked the privileges for %s.')
        );
        $message->addParam('\'' . $username . '\'@\'' . $hostname . '\'');

        return [
            $message,
            $sqlQuery,
        ];
    }

    /**
     * Get REQUIRE clause
     *
     * @return string REQUIRE clause
     */
    public function getRequireClause()
    {
        $arr = isset($_POST['ssl_type']) ? $_POST : $GLOBALS;
        if (isset($arr['ssl_type']) && $arr['ssl_type'] === 'SPECIFIED') {
            $require = [];
            if (! empty($arr['ssl_cipher'])) {
                $require[] = "CIPHER '"
                        . $this->dbi->escapeString($arr['ssl_cipher']) . "'";
            }

            if (! empty($arr['x509_issuer'])) {
                $require[] = "ISSUER '"
                        . $this->dbi->escapeString($arr['x509_issuer']) . "'";
            }

            if (! empty($arr['x509_subject'])) {
                $require[] = "SUBJECT '"
                        . $this->dbi->escapeString($arr['x509_subject']) . "'";
            }

            if (count($require)) {
                $requireClause = ' REQUIRE ' . implode(' AND ', $require);
            } else {
                $requireClause = ' REQUIRE NONE';
            }
        } elseif (isset($arr['ssl_type']) && $arr['ssl_type'] === 'X509') {
            $requireClause = ' REQUIRE X509';
        } elseif (isset($arr['ssl_type']) && $arr['ssl_type'] === 'ANY') {
            $requireClause = ' REQUIRE SSL';
        } else {
            $requireClause = ' REQUIRE NONE';
        }

        return $requireClause;
    }

    /**
     * Get a WITH clause for 'update privileges' and 'add user'
     *
     * @return string
     */
    public function getWithClauseForAddUserAndUpdatePrivs()
    {
        $sqlQuery = '';
        if (
            ((isset($_POST['Grant_priv']) && $_POST['Grant_priv'] === 'Y')
            || (isset($GLOBALS['Grant_priv']) && $GLOBALS['Grant_priv'] === 'Y'))
            && ! (Compatibility::isMySqlOrPerconaDb() && $this->dbi->getVersion() >= 80011)
        ) {
            $sqlQuery .= ' GRANT OPTION';
        }

        if (isset($_POST['max_questions']) || isset($GLOBALS['max_questions'])) {
            $maxQuestions = isset($_POST['max_questions'])
                ? (int) $_POST['max_questions'] : (int) $GLOBALS['max_questions'];
            $maxQuestions = max(0, $maxQuestions);
            $sqlQuery .= ' MAX_QUERIES_PER_HOUR ' . $maxQuestions;
        }

        if (isset($_POST['max_connections']) || isset($GLOBALS['max_connections'])) {
            $maxConnections = isset($_POST['max_connections'])
                ? (int) $_POST['max_connections'] : (int) $GLOBALS['max_connections'];
            $maxConnections = max(0, $maxConnections);
            $sqlQuery .= ' MAX_CONNECTIONS_PER_HOUR ' . $maxConnections;
        }

        if (isset($_POST['max_updates']) || isset($GLOBALS['max_updates'])) {
            $maxUpdates = isset($_POST['max_updates'])
                ? (int) $_POST['max_updates'] : (int) $GLOBALS['max_updates'];
            $maxUpdates = max(0, $maxUpdates);
            $sqlQuery .= ' MAX_UPDATES_PER_HOUR ' . $maxUpdates;
        }

        if (isset($_POST['max_user_connections']) || isset($GLOBALS['max_user_connections'])) {
            $maxUserConnections = isset($_POST['max_user_connections'])
                ? (int) $_POST['max_user_connections']
                : (int) $GLOBALS['max_user_connections'];
            $maxUserConnections = max(0, $maxUserConnections);
            $sqlQuery .= ' MAX_USER_CONNECTIONS ' . $maxUserConnections;
        }

        return ! empty($sqlQuery) ? ' WITH' . $sqlQuery : '';
    }

    /**
     * Get HTML for addUsersForm, This function call if isset($_GET['adduser'])
     *
     * @param string $dbname database name
     *
     * @return string HTML for addUserForm
     */
    public function getHtmlForAddUser($dbname)
    {
        $isGrantUser = $this->dbi->isGrantUser();
        $loginInformationFieldsNew = $this->getHtmlForLoginInformationFields('new');
        $privilegesTable = '';
        if ($isGrantUser) {
            $privilegesTable = $this->getHtmlToDisplayPrivilegesTable('*', '*', false);
        }

        return $this->template->render('server/privileges/add_user', [
            'database' => $dbname,
            'login_information_fields_new' => $loginInformationFieldsNew,
            'is_grant_user' => $isGrantUser,
            'privileges_table' => $privilegesTable,
        ]);
    }

    /**
     * @param string $db    database name
     * @param string $table table name
     *
     * @return array
     */
    public function getAllPrivileges(string $db, string $table = ''): array
    {
        $databasePrivileges = $this->getGlobalAndDatabasePrivileges($db);
        $tablePrivileges = [];
        if ($table !== '') {
            $tablePrivileges = $this->getTablePrivileges($db, $table);
        }

        $routinePrivileges = $this->getRoutinesPrivileges($db);
        $allPrivileges = array_merge($databasePrivileges, $tablePrivileges, $routinePrivileges);

        $privileges = [];
        foreach ($allPrivileges as $privilege) {
            $userHost = $privilege['User'] . '@' . $privilege['Host'];
            $privileges[$userHost] = $privileges[$userHost] ?? [];
            $privileges[$userHost]['user'] = (string) $privilege['User'];
            $privileges[$userHost]['host'] = (string) $privilege['Host'];
            $privileges[$userHost]['privileges'] = $privileges[$userHost]['privileges'] ?? [];
            $privileges[$userHost]['privileges'][] = $this->getSpecificPrivilege($privilege);
        }

        return $privileges;
    }

    /**
     * @param array $row Array with user privileges
     *
     * @return array
     */
    private function getSpecificPrivilege(array $row): array
    {
        $privilege = [
            'type' => $row['Type'],
            'database' => $row['Db'],
        ];
        if ($row['Type'] === 'r') {
            $privilege['routine'] = $row['Routine_name'];
            $privilege['has_grant'] = str_contains($row['Proc_priv'], 'Grant');
            $privilege['privileges'] = explode(',', $row['Proc_priv']);
        } elseif ($row['Type'] === 't') {
            $privilege['table'] = $row['Table_name'];
            $privilege['has_grant'] = str_contains($row['Table_priv'], 'Grant');
            $tablePrivs = explode(',', $row['Table_priv']);
            $specificPrivileges = [];
            $grantsArr = $this->getTableGrantsArray();
            foreach ($grantsArr as $grant) {
                $specificPrivileges[$grant[0]] = 'N';
                foreach ($tablePrivs as $tablePriv) {
                    if ($grant[0] != $tablePriv) {
                        continue;
                    }

                    $specificPrivileges[$grant[0]] = 'Y';
                }
            }

            $privilege['privileges'] = $this->extractPrivInfo($specificPrivileges, true, true);
        } else {
            $privilege['has_grant'] = $row['Grant_priv'] === 'Y';
            $privilege['privileges'] = $this->extractPrivInfo($row, true);
        }

        return $privilege;
    }

    /**
     * @param string $db database name
     *
     * @return array
     */
    private function getGlobalAndDatabasePrivileges(string $db): array
    {
        $listOfPrivileges = '`Select_priv`,
            `Insert_priv`,
            `Update_priv`,
            `Delete_priv`,
            `Create_priv`,
            `Drop_priv`,
            `Grant_priv`,
            `Index_priv`,
            `Alter_priv`,
            `References_priv`,
            `Create_tmp_table_priv`,
            `Lock_tables_priv`,
            `Create_view_priv`,
            `Show_view_priv`,
            `Create_routine_priv`,
            `Alter_routine_priv`,
            `Execute_priv`,
            `Event_priv`,
            `Trigger_priv`,';

        $listOfComparedPrivileges = 'BINARY `Select_priv` = \'N\' AND
            BINARY `Insert_priv` = \'N\' AND
            BINARY `Update_priv` = \'N\' AND
            BINARY `Delete_priv` = \'N\' AND
            BINARY `Create_priv` = \'N\' AND
            BINARY `Drop_priv` = \'N\' AND
            BINARY `Grant_priv` = \'N\' AND
            BINARY `References_priv` = \'N\' AND
            BINARY `Create_tmp_table_priv` = \'N\' AND
            BINARY `Lock_tables_priv` = \'N\' AND
            BINARY `Create_view_priv` = \'N\' AND
            BINARY `Show_view_priv` = \'N\' AND
            BINARY `Create_routine_priv` = \'N\' AND
            BINARY `Alter_routine_priv` = \'N\' AND
            BINARY `Execute_priv` = \'N\' AND
            BINARY `Event_priv` = \'N\' AND
            BINARY `Trigger_priv` = \'N\'';

        $query = '
            (
                SELECT `User`, `Host`, ' . $listOfPrivileges . ' \'*\' AS `Db`, \'g\' AS `Type`
                FROM `mysql`.`user`
                WHERE NOT (' . $listOfComparedPrivileges . ')
            )
            UNION
            (
                SELECT `User`, `Host`, ' . $listOfPrivileges . ' `Db`, \'d\' AS `Type`
                FROM `mysql`.`db`
                WHERE \'' . $this->dbi->escapeString($db) . '\' LIKE `Db` AND NOT (' . $listOfComparedPrivileges . ')
            )
            ORDER BY `User` ASC, `Host` ASC, `Db` ASC;
        ';
        $result = $this->dbi->query($query);
        if ($result === false) {
            return [];
        }

        $privileges = [];
        while ($row = $this->dbi->fetchAssoc($result)) {
            $privileges[] = $row;
        }

        return $privileges;
    }

    /**
     * @param string $db    database name
     * @param string $table table name
     *
     * @return array
     */
    private function getTablePrivileges(string $db, string $table): array
    {
        $query = '
            SELECT `User`, `Host`, `Db`, \'t\' AS `Type`, `Table_name`, `Table_priv`
            FROM `mysql`.`tables_priv`
            WHERE
                ? LIKE `Db` AND
                ? LIKE `Table_name` AND
                NOT (`Table_priv` = \'\' AND Column_priv = \'\')
            ORDER BY `User` ASC, `Host` ASC, `Db` ASC, `Table_priv` ASC;
        ';
        /** @var mysqli_stmt|false $statem…