1<?php
2/**
3 * set of functions with the Privileges section in pma
4 */
5
6declare(strict_types=1);
7
8namespace PhpMyAdmin\Server;
9
10use PhpMyAdmin\Core;
11use PhpMyAdmin\DatabaseInterface;
12use PhpMyAdmin\Html\Generator;
13use PhpMyAdmin\Html\MySQLDocumentation;
14use PhpMyAdmin\Message;
15use PhpMyAdmin\Query\Compatibility;
16use PhpMyAdmin\Relation;
17use PhpMyAdmin\RelationCleanup;
18use PhpMyAdmin\Response;
19use PhpMyAdmin\Template;
20use PhpMyAdmin\Url;
21use PhpMyAdmin\Util;
22use function array_map;
23use function array_merge;
24use function array_unique;
25use function count;
26use function explode;
27use function htmlspecialchars;
28use function implode;
29use function in_array;
30use function is_array;
31use function ksort;
32use function max;
33use function mb_chr;
34use function mb_strpos;
35use function mb_strrpos;
36use function mb_strtolower;
37use function mb_strtoupper;
38use function mb_substr;
39use function preg_match;
40use function preg_replace;
41use function sprintf;
42use function str_replace;
43use function strlen;
44use function strpos;
45use function trim;
46use function uksort;
47
48/**
49 * Privileges class
50 */
51class Privileges
52{
53    /** @var Template */
54    public $template;
55
56    /** @var RelationCleanup */
57    private $relationCleanup;
58
59    /** @var DatabaseInterface */
60    public $dbi;
61
62    /** @var Relation */
63    public $relation;
64
65    /**
66     * @param Template          $template        Template object
67     * @param DatabaseInterface $dbi             DatabaseInterface object
68     * @param Relation          $relation        Relation object
69     * @param RelationCleanup   $relationCleanup RelationCleanup object
70     */
71    public function __construct(
72        Template $template,
73        $dbi,
74        Relation $relation,
75        RelationCleanup $relationCleanup
76    ) {
77        $this->template = $template;
78        $this->dbi = $dbi;
79        $this->relation = $relation;
80        $this->relationCleanup = $relationCleanup;
81    }
82
83    /**
84     * Escapes wildcard in a database+table specification
85     * before using it in a GRANT statement.
86     *
87     * Escaping a wildcard character in a GRANT is only accepted at the global
88     * or database level, not at table level; this is why I remove
89     * the escaping character. Internally, in mysql.tables_priv.Db there are
90     * no escaping (for example test_db) but in mysql.db you'll see test\_db
91     * for a db-specific privilege.
92     *
93     * @param string $dbname    Database name
94     * @param string $tablename Table name
95     *
96     * @return string the escaped (if necessary) database.table
97     */
98    public function wildcardEscapeForGrant(string $dbname, string $tablename): string
99    {
100        if (strlen($dbname) === 0) {
101            return '*.*';
102        }
103        if (strlen($tablename) > 0) {
104            return Util::backquote(
105                Util::unescapeMysqlWildcards($dbname)
106            )
107            . '.' . Util::backquote($tablename);
108        }
109
110        return Util::backquote($dbname) . '.*';
111    }
112
113    /**
114     * Generates a condition on the user name
115     *
116     * @param string $initial the user's initial
117     *
118     * @return string   the generated condition
119     */
120    public function rangeOfUsers($initial = '')
121    {
122        // strtolower() is used because the User field
123        // might be BINARY, so LIKE would be case sensitive
124        if ($initial === null || $initial === '') {
125            return '';
126        }
127
128        return " WHERE `User` LIKE '"
129            . $this->dbi->escapeString($initial) . "%'"
130            . " OR `User` LIKE '"
131            . $this->dbi->escapeString(mb_strtolower($initial))
132            . "%'";
133    }
134
135    /**
136     * Parses privileges into an array, it modifies the array
137     *
138     * @param array $row Results row from
139     *
140     * @return void
141     */
142    public function fillInTablePrivileges(array &$row)
143    {
144        $row1 = $this->dbi->fetchSingleRow(
145            'SHOW COLUMNS FROM `mysql`.`tables_priv` LIKE \'Table_priv\';',
146            'ASSOC'
147        );
148        // note: in MySQL 5.0.3 we get "Create View', 'Show view';
149        // the View for Create is spelled with uppercase V
150        // the view for Show is spelled with lowercase v
151        // and there is a space between the words
152
153        $av_grants = explode(
154            '\',\'',
155            mb_substr(
156                $row1['Type'],
157                mb_strpos($row1['Type'], '(') + 2,
158                mb_strpos($row1['Type'], ')')
159                - mb_strpos($row1['Type'], '(') - 3
160            )
161        );
162
163        $users_grants = explode(',', $row['Table_priv']);
164
165        foreach ($av_grants as $current_grant) {
166            $row[$current_grant . '_priv']
167                = in_array($current_grant, $users_grants) ? 'Y' : 'N';
168        }
169        unset($row['Table_priv']);
170    }
171
172    /**
173     * Extracts the privilege information of a priv table row
174     *
175     * @param array|null $row        the row
176     * @param bool       $enableHTML add <dfn> tag with tooltips
177     * @param bool       $tablePrivs whether row contains table privileges
178     *
179     * @return array
180     *
181     * @global resource $user_link the database connection
182     */
183    public function extractPrivInfo($row = null, $enableHTML = false, $tablePrivs = false)
184    {
185        if ($tablePrivs) {
186            $grants = $this->getTableGrantsArray();
187        } else {
188            $grants = $this->getGrantsArray();
189        }
190
191        if ($row !== null && isset($row['Table_priv'])) {
192            $this->fillInTablePrivileges($row);
193        }
194
195        $privs = [];
196        $allPrivileges = true;
197        foreach ($grants as $current_grant) {
198            if (($row === null || ! isset($row[$current_grant[0]]))
199                && ($row !== null || ! isset($GLOBALS[$current_grant[0]]))
200            ) {
201                continue;
202            }
203
204            if (($row !== null && $row[$current_grant[0]] === 'Y')
205                || ($row === null
206                && ($GLOBALS[$current_grant[0]] === 'Y'
207                || (is_array($GLOBALS[$current_grant[0]])
208                && count($GLOBALS[$current_grant[0]]) == $_REQUEST['column_count']
209                && empty($GLOBALS[$current_grant[0] . '_none']))))
210            ) {
211                if ($enableHTML) {
212                    $privs[] = '<dfn title="' . $current_grant[2] . '">'
213                    . $current_grant[1] . '</dfn>';
214                } else {
215                    $privs[] = $current_grant[1];
216                }
217            } elseif (! empty($GLOBALS[$current_grant[0]])
218                && is_array($GLOBALS[$current_grant[0]])
219                && empty($GLOBALS[$current_grant[0] . '_none'])
220            ) {
221                // Required for proper escaping of ` (backtick) in a column name
222                $grant_cols = array_map(
223                    /** @param string $val */
224                    static function ($val) {
225                        return Util::backquote($val);
226                    },
227                    $GLOBALS[$current_grant[0]]
228                );
229
230                if ($enableHTML) {
231                    $privs[] = '<dfn title="' . $current_grant[2] . '">'
232                        . $current_grant[1] . '</dfn>'
233                        . ' (' . implode(', ', $grant_cols) . ')';
234                } else {
235                    $privs[] = $current_grant[1]
236                        . ' (' . implode(', ', $grant_cols) . ')';
237                }
238            } else {
239                $allPrivileges = false;
240            }
241        }
242        if (empty($privs)) {
243            if ($enableHTML) {
244                $privs[] = '<dfn title="' . __('No privileges.') . '">USAGE</dfn>';
245            } else {
246                $privs[] = 'USAGE';
247            }
248        } elseif ($allPrivileges
249            && (! isset($_POST['grant_count']) || count($privs) == $_POST['grant_count'])
250        ) {
251            if ($enableHTML) {
252                $privs = [
253                    '<dfn title="'
254                    . __('Includes all privileges except GRANT.')
255                    . '">ALL PRIVILEGES</dfn>',
256                ];
257            } else {
258                $privs = ['ALL PRIVILEGES'];
259            }
260        }
261
262        return $privs;
263    }
264
265    /**
266     * Returns an array of table grants and their descriptions
267     *
268     * @return array array of table grants
269     */
270    public function getTableGrantsArray()
271    {
272        return [
273            [
274                'Delete',
275                'DELETE',
276                __('Allows deleting data.'),
277            ],
278            [
279                'Create',
280                'CREATE',
281                __('Allows creating new tables.'),
282            ],
283            [
284                'Drop',
285                'DROP',
286                __('Allows dropping tables.'),
287            ],
288            [
289                'Index',
290                'INDEX',
291                __('Allows creating and dropping indexes.'),
292            ],
293            [
294                'Alter',
295                'ALTER',
296                __('Allows altering the structure of existing tables.'),
297            ],
298            [
299                'Create View',
300                'CREATE_VIEW',
301                __('Allows creating new views.'),
302            ],
303            [
304                'Show view',
305                'SHOW_VIEW',
306                __('Allows performing SHOW CREATE VIEW queries.'),
307            ],
308            [
309                'Trigger',
310                'TRIGGER',
311                __('Allows creating and dropping triggers.'),
312            ],
313        ];
314    }
315
316    /**
317     * Get the grants array which contains all the privilege types
318     * and relevant grant messages
319     *
320     * @return array
321     */
322    public function getGrantsArray()
323    {
324        return [
325            [
326                'Select_priv',
327                'SELECT',
328                __('Allows reading data.'),
329            ],
330            [
331                'Insert_priv',
332                'INSERT',
333                __('Allows inserting and replacing data.'),
334            ],
335            [
336                'Update_priv',
337                'UPDATE',
338                __('Allows changing data.'),
339            ],
340            [
341                'Delete_priv',
342                'DELETE',
343                __('Allows deleting data.'),
344            ],
345            [
346                'Create_priv',
347                'CREATE',
348                __('Allows creating new databases and tables.'),
349            ],
350            [
351                'Drop_priv',
352                'DROP',
353                __('Allows dropping databases and tables.'),
354            ],
355            [
356                'Reload_priv',
357                'RELOAD',
358                __('Allows reloading server settings and flushing the server\'s caches.'),
359            ],
360            [
361                'Shutdown_priv',
362                'SHUTDOWN',
363                __('Allows shutting down the server.'),
364            ],
365            [
366                'Process_priv',
367                'PROCESS',
368                __('Allows viewing processes of all users.'),
369            ],
370            [
371                'File_priv',
372                'FILE',
373                __('Allows importing data from and exporting data into files.'),
374            ],
375            [
376                'References_priv',
377                'REFERENCES',
378                __('Has no effect in this MySQL version.'),
379            ],
380            [
381                'Index_priv',
382                'INDEX',
383                __('Allows creating and dropping indexes.'),
384            ],
385            [
386                'Alter_priv',
387                'ALTER',
388                __('Allows altering the structure of existing tables.'),
389            ],
390            [
391                'Show_db_priv',
392                'SHOW DATABASES',
393                __('Gives access to the complete list of databases.'),
394            ],
395            [
396                'Super_priv',
397                'SUPER',
398                __(
399                    'Allows connecting, even if maximum number of connections '
400                    . 'is reached; required for most administrative operations '
401                    . 'like setting global variables or killing threads of other users.'
402                ),
403            ],
404            [
405                'Create_tmp_table_priv',
406                'CREATE TEMPORARY TABLES',
407                __('Allows creating temporary tables.'),
408            ],
409            [
410                'Lock_tables_priv',
411                'LOCK TABLES',
412                __('Allows locking tables for the current thread.'),
413            ],
414            [
415                'Repl_slave_priv',
416                'REPLICATION SLAVE',
417                __('Needed for the replication slaves.'),
418            ],
419            [
420                'Repl_client_priv',
421                'REPLICATION CLIENT',
422                __('Allows the user to ask where the slaves / masters are.'),
423            ],
424            [
425                'Create_view_priv',
426                'CREATE VIEW',
427                __('Allows creating new views.'),
428            ],
429            [
430                'Event_priv',
431                'EVENT',
432                __('Allows to set up events for the event scheduler.'),
433            ],
434            [
435                'Trigger_priv',
436                'TRIGGER',
437                __('Allows creating and dropping triggers.'),
438            ],
439            // for table privs:
440            [
441                'Create View_priv',
442                'CREATE VIEW',
443                __('Allows creating new views.'),
444            ],
445            [
446                'Show_view_priv',
447                'SHOW VIEW',
448                __('Allows performing SHOW CREATE VIEW queries.'),
449            ],
450            // for table privs:
451            [
452                'Show view_priv',
453                'SHOW VIEW',
454                __('Allows performing SHOW CREATE VIEW queries.'),
455            ],
456            [
457                'Delete_history_priv',
458                'DELETE HISTORY',
459                // phpcs:ignore Generic.Files.LineLength.TooLong
460                /* l10n: https://mariadb.com/kb/en/library/grant/#table-privileges "Remove historical rows from a table using the DELETE HISTORY statement" */
461                __('Allows deleting historical rows.'),
462            ],
463            [
464                // This was finally removed in the following MariaDB versions
465                // @see https://jira.mariadb.org/browse/MDEV-20382
466                'Delete versioning rows_priv',
467                'DELETE HISTORY',
468                // phpcs:ignore Generic.Files.LineLength.TooLong
469                /* l10n: https://mariadb.com/kb/en/library/grant/#table-privileges "Remove historical rows from a table using the DELETE HISTORY statement" */
470                __('Allows deleting historical rows.'),
471            ],
472            [
473                'Create_routine_priv',
474                'CREATE ROUTINE',
475                __('Allows creating stored routines.'),
476            ],
477            [
478                'Alter_routine_priv',
479                'ALTER ROUTINE',
480                __('Allows altering and dropping stored routines.'),
481            ],
482            [
483                'Create_user_priv',
484                'CREATE USER',
485                __('Allows creating, dropping and renaming user accounts.'),
486            ],
487            [
488                'Execute_priv',
489                'EXECUTE',
490                __('Allows executing stored routines.'),
491            ],
492        ];
493    }
494
495    /**
496     * Get sql query for display privileges table
497     *
498     * @param string $db       the database
499     * @param string $table    the table
500     * @param string $username username for database connection
501     * @param string $hostname hostname for database connection
502     *
503     * @return string sql query
504     */
505    public function getSqlQueryForDisplayPrivTable($db, $table, $username, $hostname)
506    {
507        if ($db === '*') {
508            return 'SELECT * FROM `mysql`.`user`'
509                . " WHERE `User` = '" . $this->dbi->escapeString($username) . "'"
510                . " AND `Host` = '" . $this->dbi->escapeString($hostname) . "';";
511        }
512
513        if ($table === '*') {
514            return 'SELECT * FROM `mysql`.`db`'
515                . " WHERE `User` = '" . $this->dbi->escapeString($username) . "'"
516                . " AND `Host` = '" . $this->dbi->escapeString($hostname) . "'"
517                . " AND `Db` = '" . $this->dbi->escapeString($db) . "'";
518        }
519
520        return 'SELECT `Table_priv`'
521            . ' FROM `mysql`.`tables_priv`'
522            . " WHERE `User` = '" . $this->dbi->escapeString($username) . "'"
523            . " AND `Host` = '" . $this->dbi->escapeString($hostname) . "'"
524            . " AND `Db` = '" . $this->dbi->escapeString(Util::unescapeMysqlWildcards($db)) . "'"
525            . " AND `Table_name` = '" . $this->dbi->escapeString($table) . "';";
526    }
527
528    /**
529     * Displays a dropdown to select the user group
530     * with menu items configured to each of them.
531     *
532     * @param string $username username
533     *
534     * @return string html to select the user group
535     */
536    public function getHtmlToChooseUserGroup($username)
537    {
538        $cfgRelation = $this->relation->getRelationsParam();
539        $groupTable = Util::backquote($cfgRelation['db'])
540            . '.' . Util::backquote($cfgRelation['usergroups']);
541        $userTable = Util::backquote($cfgRelation['db'])
542            . '.' . Util::backquote($cfgRelation['users']);
543
544        $userGroup = '';
545        if (isset($GLOBALS['username'])) {
546            $sql_query = 'SELECT `usergroup` FROM ' . $userTable
547                . " WHERE `username` = '" . $this->dbi->escapeString($username) . "'";
548            $userGroup = $this->dbi->fetchValue(
549                $sql_query,
550                0,
551                0,
552                DatabaseInterface::CONNECT_CONTROL
553            );
554        }
555
556        $allUserGroups = [];
557        $sql_query = 'SELECT DISTINCT `usergroup` FROM ' . $groupTable;
558        $result = $this->relation->queryAsControlUser($sql_query, false);
559        if ($result) {
560            while ($row = $this->dbi->fetchRow($result)) {
561                $allUserGroups[$row[0]] = $row[0];
562            }
563        }
564        $this->dbi->freeResult($result);
565
566        return $this->template->render('server/privileges/choose_user_group', [
567            'all_user_groups' => $allUserGroups,
568            'user_group' => $userGroup,
569            'params' => ['username' => $username],
570        ]);
571    }
572
573    /**
574     * Sets the user group from request values
575     *
576     * @param string $username  username
577     * @param string $userGroup user group to set
578     *
579     * @return void
580     */
581    public function setUserGroup($username, $userGroup)
582    {
583        $userGroup = $userGroup ?? '';
584        $cfgRelation = $this->relation->getRelationsParam();
585        if (empty($cfgRelation['db']) || empty($cfgRelation['users']) || empty($cfgRelation['usergroups'])) {
586            return;
587        }
588
589        $userTable = Util::backquote($cfgRelation['db'])
590            . '.' . Util::backquote($cfgRelation['users']);
591
592        $sql_query = 'SELECT `usergroup` FROM ' . $userTable
593            . " WHERE `username` = '" . $this->dbi->escapeString($username) . "'";
594        $oldUserGroup = $this->dbi->fetchValue(
595            $sql_query,
596            0,
597            0,
598            DatabaseInterface::CONNECT_CONTROL
599        );
600
601        if ($oldUserGroup === false) {
602            $upd_query = 'INSERT INTO ' . $userTable . '(`username`, `usergroup`)'
603                . " VALUES ('" . $this->dbi->escapeString($username) . "', "
604                . "'" . $this->dbi->escapeString($userGroup) . "')";
605        } else {
606            if (empty($userGroup)) {
607                $upd_query = 'DELETE FROM ' . $userTable
608                    . " WHERE `username`='" . $this->dbi->escapeString($username) . "'";
609            } elseif ($oldUserGroup != $userGroup) {
610                $upd_query = 'UPDATE ' . $userTable
611                    . " SET `usergroup`='" . $this->dbi->escapeString($userGroup) . "'"
612                    . " WHERE `username`='" . $this->dbi->escapeString($username) . "'";
613            }
614        }
615        if (! isset($upd_query)) {
616            return;
617        }
618
619        $this->relation->queryAsControlUser($upd_query);
620    }
621
622    /**
623     * Displays the privileges form table
624     *
625     * @param string $db     the database
626     * @param string $table  the table
627     * @param bool   $submit whether to display the submit button or not
628     *
629     * @return string html snippet
630     *
631     * @global array     $cfg         the phpMyAdmin configuration
632     * @global resource  $user_link   the database connection
633     */
634    public function getHtmlToDisplayPrivilegesTable(
635        $db = '*',
636        $table = '*',
637        $submit = true
638    ) {
639        $sql_query = '';
640
641        if ($db === '*') {
642            $table = '*';
643        }
644        $username = '';
645        $hostname = '';
646        $row = [];
647        if (isset($GLOBALS['username'])) {
648            $username = $GLOBALS['username'];
649            $hostname = $GLOBALS['hostname'];
650            $sql_query = $this->getSqlQueryForDisplayPrivTable(
651                $db,
652                $table,
653                $username,
654                $hostname
655            );
656            $row = $this->dbi->fetchSingleRow($sql_query);
657        }
658        if (empty($row)) {
659            if ($table === '*' && $this->dbi->isSuperUser()) {
660                $row = [];
661                if ($db === '*') {
662                    $sql_query = 'SHOW COLUMNS FROM `mysql`.`user`;';
663                } elseif ($table === '*') {
664                    $sql_query = 'SHOW COLUMNS FROM `mysql`.`db`;';
665                }
666                $res = $this->dbi->query($sql_query);
667                while ($row1 = $this->dbi->fetchRow($res)) {
668                    if (mb_substr($row1[0], 0, 4) === 'max_') {
669                        $row[$row1[0]] = 0;
670                    } elseif (mb_substr($row1[0], 0, 5) === 'x509_'
671                        || mb_substr($row1[0], 0, 4) === 'ssl_'
672                    ) {
673                        $row[$row1[0]] = '';
674                    } else {
675                        $row[$row1[0]] = 'N';
676                    }
677                }
678                $this->dbi->freeResult($res);
679            } elseif ($table === '*') {
680                $row = [];
681            } else {
682                $row = ['Table_priv' => ''];
683            }
684        }
685        if (isset($row['Table_priv'])) {
686            $this->fillInTablePrivileges($row);
687
688            // get columns
689            $res = $this->dbi->tryQuery(
690                'SHOW COLUMNS FROM '
691                . Util::backquote(
692                    Util::unescapeMysqlWildcards($db)
693                )
694                . '.' . Util::backquote($table) . ';'
695            );
696            $columns = [];
697            if ($res) {
698                while ($row1 = $this->dbi->fetchRow($res)) {
699                    $columns[$row1[0]] = [
700                        'Select' => false,
701                        'Insert' => false,
702                        'Update' => false,
703                        'References' => false,
704                    ];
705                }
706                $this->dbi->freeResult($res);
707            }
708        }
709
710        if (! empty($columns)) {
711            $res = $this->dbi->query(
712                'SELECT `Column_name`, `Column_priv`'
713                . ' FROM `mysql`.`columns_priv`'
714                . ' WHERE `User`'
715                . ' = \'' . $this->dbi->escapeString($username) . "'"
716                . ' AND `Host`'
717                . ' = \'' . $this->dbi->escapeString($hostname) . "'"
718                . ' AND `Db`'
719                . ' = \'' . $this->dbi->escapeString(
720                    Util::unescapeMysqlWildcards($db)
721                ) . "'"
722                . ' AND `Table_name`'
723                . ' = \'' . $this->dbi->escapeString($table) . '\';'
724            );
725
726            while ($row1 = $this->dbi->fetchRow($res)) {
727                $row1[1] = explode(',', $row1[1]);
728                foreach ($row1[1] as $current) {
729                    $columns[$row1[0]][$current] = true;
730                }
731            }
732            $this->dbi->freeResult($res);
733        }
734
735        return $this->template->render('server/privileges/privileges_table', [
736            'is_global' => $db === '*',
737            'is_database' => $table === '*',
738            'row' => $row,
739            'columns' => $columns ?? [],
740            'has_submit' => $submit,
741            'supports_references_privilege' => Compatibility::supportsReferencesPrivilege($this->dbi),
742            'is_mariadb' => $this->dbi->isMariaDB(),
743        ]);
744    }
745
746    /**
747     * Get the HTML snippet for routine specific privileges
748     *
749     * @param string $username   username for database connection
750     * @param string $hostname   hostname for database connection
751     * @param string $db         the database
752     * @param string $routine    the routine
753     * @param string $url_dbname url encoded db name
754     *
755     * @return string
756     */
757    public function getHtmlForRoutineSpecificPrivileges(
758        $username,
759        $hostname,
760        $db,
761        $routine,
762        $url_dbname
763    ) {
764        $privileges = $this->getRoutinePrivileges($username, $hostname, $db, $routine);
765
766        return $this->template->render('server/privileges/edit_routine_privileges', [
767            'username' => $username,
768            'hostname' => $hostname,
769            'database' => $db,
770            'routine' => $routine,
771            'privileges' => $privileges,
772            'dbname' => $url_dbname,
773            'current_user' => $this->dbi->getCurrentUser(),
774        ]);
775    }
776
777    /**
778     * Gets the currently active authentication plugins
779     *
780     * @return array  array of plugin names and descriptions
781     */
782    public function getActiveAuthPlugins()
783    {
784        $get_plugins_query = 'SELECT `PLUGIN_NAME`, `PLUGIN_DESCRIPTION`'
785            . ' FROM `information_schema`.`PLUGINS` '
786            . "WHERE `PLUGIN_TYPE` = 'AUTHENTICATION';";
787        $resultset = $this->dbi->query($get_plugins_query);
788
789        $result = [];
790
791        while ($row = $this->dbi->fetchAssoc($resultset)) {
792            // if description is known, enable its translation
793            if ($row['PLUGIN_NAME'] === 'mysql_native_password') {
794                $row['PLUGIN_DESCRIPTION'] = __('Native MySQL authentication');
795            } elseif ($row['PLUGIN_NAME'] === 'sha256_password') {
796                $row['PLUGIN_DESCRIPTION'] = __('SHA256 password authentication');
797            }
798
799            $result[$row['PLUGIN_NAME']] = $row['PLUGIN_DESCRIPTION'];
800        }
801
802        return $result;
803    }
804
805    /**
806     * Displays the fields used by the "new user" form as well as the
807     * "change login information / copy user" form.
808     *
809     * @param string $mode are we creating a new user or are we just
810     *                     changing  one? (allowed values: 'new', 'change')
811     * @param string $user User name
812     * @param string $host Host name
813     *
814     * @return string  a HTML snippet
815     */
816    public function getHtmlForLoginInformationFields(
817        $mode = 'new',
818        $user = null,
819        $host = null
820    ) {
821        global $pred_username, $pred_hostname, $username, $hostname, $new_username;
822
823        [$usernameLength, $hostnameLength] = $this->getUsernameAndHostnameLength();
824
825        if (isset($username) && strlen($username) === 0) {
826            $pred_username = 'any';
827        }
828
829        $currentUser = $this->dbi->fetchValue('SELECT USER();');
830        $thisHost = null;
831        if (! empty($currentUser)) {
832            $thisHost = str_replace(
833                '\'',
834                '',
835                mb_substr(
836                    $currentUser,
837                    mb_strrpos($currentUser, '@') + 1
838                )
839            );
840        }
841
842        if (! isset($pred_hostname) && isset($hostname)) {
843            switch (mb_strtolower($hostname)) {
844                case 'localhost':
845                case '127.0.0.1':
846                    $pred_hostname = 'localhost';
847                    break;
848                case '%':
849                    $pred_hostname = 'any';
850                    break;
851                default:
852                    $pred_hostname = 'userdefined';
853                    break;
854            }
855        }
856
857        $serverType = Util::getServerType();
858        $serverVersion = $this->dbi->getVersion();
859        $authPlugin = $this->getCurrentAuthenticationPlugin(
860            $mode,
861            $user,
862            $host
863        );
864
865        $isNew = (($serverType === 'MySQL' || $serverType === 'Percona Server') && $serverVersion >= 50507)
866            || ($serverType === 'MariaDB' && $serverVersion >= 50200);
867
868        $activeAuthPlugins = ['mysql_native_password' => __('Native MySQL authentication')];
869        if ($isNew) {
870            $activeAuthPlugins = $this->getActiveAuthPlugins();
871            if (isset($activeAuthPlugins['mysql_old_password'])) {
872                unset($activeAuthPlugins['mysql_old_password']);
873            }
874        }
875
876        return $this->template->render('server/privileges/login_information_fields', [
877            'pred_username' => $pred_username ?? null,
878            'pred_hostname' => $pred_hostname ?? null,
879            'username_length' => $usernameLength,
880            'hostname_length' => $hostnameLength,
881            'username' => $username ?? null,
882            'new_username' => $new_username ?? null,
883            'hostname' => $hostname ?? null,
884            'this_host' => $thisHost,
885            'is_change' => $mode === 'change',
886            'auth_plugin' => $authPlugin,
887            'active_auth_plugins' => $activeAuthPlugins,
888            'is_new' => $isNew,
889        ]);
890    }
891
892    /**
893     * Get username and hostname length
894     *
895     * @return array username length and hostname length
896     */
897    public function getUsernameAndHostnameLength()
898    {
899        /* Fallback values */
900        $username_length = 16;
901        $hostname_length = 41;
902
903        /* Try to get real lengths from the database */
904        $fields_info = $this->dbi->fetchResult(
905            'SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH '
906            . 'FROM information_schema.columns '
907            . "WHERE table_schema = 'mysql' AND table_name = 'user' "
908            . "AND COLUMN_NAME IN ('User', 'Host')"
909        );
910        foreach ($fields_info as $val) {
911            if ($val['COLUMN_NAME'] === 'User') {
912                $username_length = $val['CHARACTER_MAXIMUM_LENGTH'];
913            } elseif ($val['COLUMN_NAME'] === 'Host') {
914                $hostname_length = $val['CHARACTER_MAXIMUM_LENGTH'];
915            }
916        }
917
918        return [
919            $username_length,
920            $hostname_length,
921        ];
922    }
923
924    /**
925     * Get current authentication plugin in use - for a user or globally
926     *
927     * @param string $mode     are we creating a new user or are we just
928     *                         changing  one? (allowed values: 'new', 'change')
929     * @param string $username User name
930     * @param string $hostname Host name
931     *
932     * @return string authentication plugin in use
933     */
934    public function getCurrentAuthenticationPlugin(
935        $mode = 'new',
936        $username = null,
937        $hostname = null
938    ) {
939        global $dbi;
940
941        /* Fallback (standard) value */
942        $authentication_plugin = 'mysql_native_password';
943        $serverVersion = $this->dbi->getVersion();
944
945        if (isset($username, $hostname) && $mode === 'change') {
946            $row = $this->dbi->fetchSingleRow(
947                'SELECT `plugin` FROM `mysql`.`user` WHERE `User` = "'
948                . $dbi->escapeString($username)
949                . '" AND `Host` = "'
950                . $dbi->escapeString($hostname)
951                . '" LIMIT 1'
952            );
953            // Table 'mysql'.'user' may not exist for some previous
954            // versions of MySQL - in that case consider fallback value
955            if (is_array($row) && isset($row['plugin'])) {
956                $authentication_plugin = $row['plugin'];
957            }
958        } elseif ($mode === 'change') {
959            [$username, $hostname] = $this->dbi->getCurrentUserAndHost();
960
961            $row = $this->dbi->fetchSingleRow(
962                'SELECT `plugin` FROM `mysql`.`user` WHERE `User` = "'
963                . $dbi->escapeString($username)
964                . '" AND `Host` = "'
965                . $dbi->escapeString($hostname)
966                . '"'
967            );
968            if (is_array($row) && isset($row['plugin'])) {
969                $authentication_plugin = $row['plugin'];
970            }
971        } elseif ($serverVersion >= 50702) {
972            $row = $this->dbi->fetchSingleRow(
973                'SELECT @@default_authentication_plugin'
974            );
975            $authentication_plugin = is_array($row) ? $row['@@default_authentication_plugin'] : null;
976        }
977
978        return $authentication_plugin;
979    }
980
981    /**
982     * Returns all the grants for a certain user on a certain host
983     * Used in the export privileges for all users section
984     *
985     * @param string $user User name
986     * @param string $host Host name
987     *
988     * @return string containing all the grants text
989     */
990    public function getGrants($user, $host)
991    {
992        $grants = $this->dbi->fetchResult(
993            "SHOW GRANTS FOR '"
994            . $this->dbi->escapeString($user) . "'@'"
995            . $this->dbi->escapeString($host) . "'"
996        );
997        $response = '';
998        foreach ($grants as $one_grant) {
999            $response .= $one_grant . ";\n\n";
1000        }
1001
1002        return $response;
1003    }
1004
1005    /**
1006     * Update password and get message for password updating
1007     *
1008     * @param string $err_url  error url
1009     * @param string $username username
1010     * @param string $hostname hostname
1011     *
1012     * @return Message success or error message after updating password
1013     */
1014    public function updatePassword($err_url, $username, $hostname)
1015    {
1016        global $dbi;
1017
1018        // similar logic in /user-password
1019        $message = null;
1020
1021        if (isset($_POST['pma_pw'], $_POST['pma_pw2']) && empty($_POST['nopass'])) {
1022            if ($_POST['pma_pw'] != $_POST['pma_pw2']) {
1023                $message = Message::error(__('The passwords aren\'t the same!'));
1024            } elseif (empty($_POST['pma_pw']) || empty($_POST['pma_pw2'])) {
1025                $message = Message::error(__('The password is empty!'));
1026            }
1027        }
1028
1029        // here $nopass could be == 1
1030        if ($message === null) {
1031            $hashing_function = 'PASSWORD';
1032            $serverType = Util::getServerType();
1033            $serverVersion = $this->dbi->getVersion();
1034            $authentication_plugin
1035                = ($_POST['authentication_plugin'] ?? $this->getCurrentAuthenticationPlugin(
1036                    'change',
1037                    $username,
1038                    $hostname
1039                ));
1040
1041            // Use 'ALTER USER ...' syntax for MySQL 5.7.6+
1042            if (
1043                in_array($serverType, ['MySQL', 'Percona Server'], true)
1044                && $serverVersion >= 50706
1045            ) {
1046                if ($authentication_plugin !== 'mysql_old_password') {
1047                    $query_prefix = "ALTER USER '"
1048                        . $this->dbi->escapeString($username)
1049                        . "'@'" . $this->dbi->escapeString($hostname) . "'"
1050                        . ' IDENTIFIED WITH '
1051                        . $authentication_plugin
1052                        . " BY '";
1053                } else {
1054                    $query_prefix = "ALTER USER '"
1055                        . $this->dbi->escapeString($username)
1056                        . "'@'" . $this->dbi->escapeString($hostname) . "'"
1057                        . " IDENTIFIED BY '";
1058                }
1059
1060                // in $sql_query which will be displayed, hide the password
1061                $sql_query = $query_prefix . "*'";
1062
1063                $local_query = $query_prefix
1064                    . $this->dbi->escapeString($_POST['pma_pw']) . "'";
1065            } elseif ($serverType === 'MariaDB' && $serverVersion >= 10000) {
1066                // MariaDB uses "SET PASSWORD" syntax to change user password.
1067                // On Galera cluster only DDL queries are replicated, since
1068                // users are stored in MyISAM storage engine.
1069                $query_prefix = "SET PASSWORD FOR  '"
1070                    . $this->dbi->escapeString($username)
1071                    . "'@'" . $this->dbi->escapeString($hostname) . "'"
1072                    . " = PASSWORD ('";
1073                $sql_query = $local_query = $query_prefix
1074                    . $this->dbi->escapeString($_POST['pma_pw']) . "')";
1075            } elseif ($serverType === 'MariaDB'
1076                && $serverVersion >= 50200
1077                && $this->dbi->isSuperUser()
1078            ) {
1079                // Use 'UPDATE `mysql`.`user` ...' Syntax for MariaDB 5.2+
1080                if ($authentication_plugin === 'mysql_native_password') {
1081                    // Set the hashing method used by PASSWORD()
1082                    // to be 'mysql_native_password' type
1083                    $this->dbi->tryQuery('SET old_passwords = 0;');
1084                } elseif ($authentication_plugin === 'sha256_password') {
1085                    // Set the hashing method used by PASSWORD()
1086                    // to be 'sha256_password' type
1087                    $this->dbi->tryQuery('SET `old_passwords` = 2;');
1088                }
1089
1090                $hashedPassword = $this->getHashedPassword($_POST['pma_pw']);
1091
1092                $sql_query        = 'SET PASSWORD FOR \''
1093                    . $this->dbi->escapeString($username)
1094                    . '\'@\'' . $this->dbi->escapeString($hostname) . '\' = '
1095                    . ($_POST['pma_pw'] == ''
1096                        ? '\'\''
1097                        : $hashing_function . '(\''
1098                        . preg_replace('@.@s', '*', $_POST['pma_pw']) . '\')');
1099
1100                $local_query = 'UPDATE `mysql`.`user` SET '
1101                    . " `authentication_string` = '" . $hashedPassword
1102                    . "', `Password` = '', "
1103                    . " `plugin` = '" . $authentication_plugin . "'"
1104                    . " WHERE `User` = '" . $dbi->escapeString($username)
1105                    . "' AND Host = '" . $dbi->escapeString($hostname) . "';";
1106            } else {
1107                // USE 'SET PASSWORD ...' syntax for rest of the versions
1108                // Backup the old value, to be reset later
1109                $row = $this->dbi->fetchSingleRow(
1110                    'SELECT @@old_passwords;'
1111                );
1112                $orig_value = $row['@@old_passwords'];
1113                $update_plugin_query = 'UPDATE `mysql`.`user` SET'
1114                    . " `plugin` = '" . $authentication_plugin . "'"
1115                    . " WHERE `User` = '" . $dbi->escapeString($username)
1116                    . "' AND Host = '" . $dbi->escapeString($hostname) . "';";
1117
1118                // Update the plugin for the user
1119                if (! $this->dbi->tryQuery($update_plugin_query)) {
1120                    Generator::mysqlDie(
1121                        $this->dbi->getError(),
1122                        $update_plugin_query,
1123                        false,
1124                        $err_url
1125                    );
1126                }
1127                $this->dbi->tryQuery('FLUSH PRIVILEGES;');
1128
1129                if ($authentication_plugin === 'mysql_native_password') {
1130                    // Set the hashing method used by PASSWORD()
1131                    // to be 'mysql_native_password' type
1132                    $this->dbi->tryQuery('SET old_passwords = 0;');
1133                } elseif ($authentication_plugin === 'sha256_password') {
1134                    // Set the hashing method used by PASSWORD()
1135                    // to be 'sha256_password' type
1136                    $this->dbi->tryQuery('SET `old_passwords` = 2;');
1137                }
1138                $sql_query        = 'SET PASSWORD FOR \''
1139                    . $this->dbi->escapeString($username)
1140                    . '\'@\'' . $this->dbi->escapeString($hostname) . '\' = '
1141                    . ($_POST['pma_pw'] == ''
1142                        ? '\'\''
1143                        : $hashing_function . '(\''
1144                        . preg_replace('@.@s', '*', $_POST['pma_pw']) . '\')');
1145
1146                $local_query      = 'SET PASSWORD FOR \''
1147                    . $this->dbi->escapeString($username)
1148                    . '\'@\'' . $this->dbi->escapeString($hostname) . '\' = '
1149                    . ($_POST['pma_pw'] == '' ? '\'\'' : $hashing_function
1150                    . '(\'' . $this->dbi->escapeString($_POST['pma_pw']) . '\')');
1151            }
1152
1153            if (! $this->dbi->tryQuery($local_query)) {
1154                Generator::mysqlDie(
1155                    $this->dbi->getError(),
1156                    $sql_query,
1157                    false,
1158                    $err_url
1159                );
1160            }
1161            // Flush privileges after successful password change
1162            $this->dbi->tryQuery('FLUSH PRIVILEGES;');
1163
1164            $message = Message::success(
1165                __('The password for %s was changed successfully.')
1166            );
1167            $message->addParam('\'' . $username . '\'@\'' . $hostname . '\'');
1168            if (isset($orig_value)) {
1169                $this->dbi->tryQuery(
1170                    'SET `old_passwords` = ' . $orig_value . ';'
1171                );
1172            }
1173        }
1174
1175        return $message;
1176    }
1177
1178    /**
1179     * Revokes privileges and get message and SQL query for privileges revokes
1180     *
1181     * @param string $dbname    database name
1182     * @param string $tablename table name
1183     * @param string $username  username
1184     * @param string $hostname  host name
1185     * @param string $itemType  item type
1186     *
1187     * @return array ($message, $sql_query)
1188     */
1189    public function getMessageAndSqlQueryForPrivilegesRevoke(
1190        string $dbname,
1191        string $tablename,
1192        $username,
1193        $hostname,
1194        $itemType
1195    ) {
1196        $db_and_table = $this->wildcardEscapeForGrant($dbname, $tablename);
1197
1198        $sql_query0 = 'REVOKE ALL PRIVILEGES ON ' . $itemType . ' ' . $db_and_table
1199            . ' FROM \''
1200            . $this->dbi->escapeString($username) . '\'@\''
1201            . $this->dbi->escapeString($hostname) . '\';';
1202
1203        $sql_query1 = 'REVOKE GRANT OPTION ON ' . $itemType . ' ' . $db_and_table
1204            . ' FROM \'' . $this->dbi->escapeString($username) . '\'@\''
1205            . $this->dbi->escapeString($hostname) . '\';';
1206
1207        $this->dbi->query($sql_query0);
1208        if (! $this->dbi->tryQuery($sql_query1)) {
1209            // this one may fail, too...
1210            $sql_query1 = '';
1211        }
1212        $sql_query = $sql_query0 . ' ' . $sql_query1;
1213        $message = Message::success(
1214            __('You have revoked the privileges for %s.')
1215        );
1216        $message->addParam('\'' . $username . '\'@\'' . $hostname . '\'');
1217
1218        return [
1219            $message,
1220            $sql_query,
1221        ];
1222    }
1223
1224    /**
1225     * Get REQUIRE clause
1226     *
1227     * @return string REQUIRE clause
1228     */
1229    public function getRequireClause()
1230    {
1231        $arr = isset($_POST['ssl_type']) ? $_POST : $GLOBALS;
1232        if (isset($arr['ssl_type']) && $arr['ssl_type'] === 'SPECIFIED') {
1233            $require = [];
1234            if (! empty($arr['ssl_cipher'])) {
1235                $require[] = "CIPHER '"
1236                        . $this->dbi->escapeString($arr['ssl_cipher']) . "'";
1237            }
1238            if (! empty($arr['x509_issuer'])) {
1239                $require[] = "ISSUER '"
1240                        . $this->dbi->escapeString($arr['x509_issuer']) . "'";
1241            }
1242            if (! empty($arr['x509_subject'])) {
1243                $require[] = "SUBJECT '"
1244                        . $this->dbi->escapeString($arr['x509_subject']) . "'";
1245            }
1246            if (count($require)) {
1247                $require_clause = ' REQUIRE ' . implode(' AND ', $require);
1248            } else {
1249                $require_clause = ' REQUIRE NONE';
1250            }
1251        } elseif (isset($arr['ssl_type']) && $arr['ssl_type'] === 'X509') {
1252            $require_clause = ' REQUIRE X509';
1253        } elseif (isset($arr['ssl_type']) && $arr['ssl_type'] === 'ANY') {
1254            $require_clause = ' REQUIRE SSL';
1255        } else {
1256            $require_clause = ' REQUIRE NONE';
1257        }
1258
1259        return $require_clause;
1260    }
1261
1262    /**
1263     * Get a WITH clause for 'update privileges' and 'add user'
1264     *
1265     * @return string
1266     */
1267    public function getWithClauseForAddUserAndUpdatePrivs()
1268    {
1269        $sql_query = '';
1270        if (((isset($_POST['Grant_priv']) && $_POST['Grant_priv'] === 'Y')
1271            || (isset($GLOBALS['Grant_priv']) && $GLOBALS['Grant_priv'] === 'Y'))
1272            && ! ((Util::getServerType() === 'MySQL' || Util::getServerType() === 'Percona Server')
1273                && $this->dbi->getVersion() >= 80011)
1274        ) {
1275            $sql_query .= ' GRANT OPTION';
1276        }
1277        if (isset($_POST['max_questions']) || isset($GLOBALS['max_questions'])) {
1278            $max_questions = isset($_POST['max_questions'])
1279                ? (int) $_POST['max_questions'] : (int) $GLOBALS['max_questions'];
1280            $max_questions = max(0, $max_questions);
1281            $sql_query .= ' MAX_QUERIES_PER_HOUR ' . $max_questions;
1282        }
1283        if (isset($_POST['max_connections']) || isset($GLOBALS['max_connections'])) {
1284            $max_connections = isset($_POST['max_connections'])
1285                ? (int) $_POST['max_connections'] : (int) $GLOBALS['max_connections'];
1286            $max_connections = max(0, $max_connections);
1287            $sql_query .= ' MAX_CONNECTIONS_PER_HOUR ' . $max_connections;
1288        }
1289        if (isset($_POST['max_updates']) || isset($GLOBALS['max_updates'])) {
1290            $max_updates = isset($_POST['max_updates'])
1291                ? (int) $_POST['max_updates'] : (int) $GLOBALS['max_updates'];
1292            $max_updates = max(0, $max_updates);
1293            $sql_query .= ' MAX_UPDATES_PER_HOUR ' . $max_updates;
1294        }
1295        if (isset($_POST['max_user_connections'])
1296            || isset($GLOBALS['max_user_connections'])
1297        ) {
1298            $max_user_connections = isset($_POST['max_user_connections'])
1299                ? (int) $_POST['max_user_connections']
1300                : (int) $GLOBALS['max_user_connections'];
1301            $max_user_connections = max(0, $max_user_connections);
1302            $sql_query .= ' MAX_USER_CONNECTIONS ' . $max_user_connections;
1303        }
1304
1305        return ! empty($sql_query) ? ' WITH' . $sql_query : '';
1306    }
1307
1308    /**
1309     * Get HTML for addUsersForm, This function call if isset($_GET['adduser'])
1310     *
1311     * @param string $dbname database name
1312     *
1313     * @return string HTML for addUserForm
1314     */
1315    public function getHtmlForAddUser($dbname)
1316    {
1317        $isGrantUser = $this->dbi->isGrantUser();
1318        $loginInformationFieldsNew = $this->getHtmlForLoginInformationFields('new');
1319        $privilegesTable = '';
1320        if ($isGrantUser) {
1321            $privilegesTable = $this->getHtmlToDisplayPrivilegesTable('*', '*', false);
1322        }
1323
1324        return $this->template->render('server/privileges/add_user', [
1325            'database' => $dbname,
1326            'login_information_fields_new' => $loginInformationFieldsNew,
1327            'is_grant_user' => $isGrantUser,
1328            'privileges_table' => $privilegesTable,
1329        ]);
1330    }
1331
1332    /**
1333     * @param string $db    database name
1334     * @param string $table table name
1335     *
1336     * @return array
1337     */
1338    public function getAllPrivileges(string $db, string $table = ''): array
1339    {
1340        $databasePrivileges = $this->getGlobalAndDatabasePrivileges($db);
1341        $tablePrivileges = [];
1342        if ($table !== '') {
1343            $tablePrivileges = $this->getTablePrivileges($db, $table);
1344        }
1345        $routinePrivileges = $this->getRoutinesPrivileges($db);
1346        $allPrivileges = array_merge($databasePrivileges, $tablePrivileges, $routinePrivileges);
1347
1348        $privileges = [];
1349        foreach ($allPrivileges as $privilege) {
1350            $userHost = $privilege['User'] . '@' . $privilege['Host'];
1351            $privileges[$userHost] = $privileges[$userHost] ?? [];
1352            $privileges[$userHost]['user'] = (string) $privilege['User'];
1353            $privileges[$userHost]['host'] = (string) $privilege['Host'];
1354            $privileges[$userHost]['privileges'] = $privileges[$userHost]['privileges'] ?? [];
1355            $privileges[$userHost]['privileges'][] = $this->getSpecificPrivilege($privilege);
1356        }
1357
1358        return $privileges;
1359    }
1360
1361    /**
1362     * @param array $row Array with user privileges
1363     *
1364     * @return array
1365     */
1366    private function getSpecificPrivilege(array $row): array
1367    {
1368        $privilege = [
1369            'type' => $row['Type'],
1370            'database' => $row['Db'],
1371        ];
1372        if ($row['Type'] === 'r') {
1373            $privilege['routine'] = $row['Routine_name'];
1374            $privilege['has_grant'] = strpos($row['Proc_priv'], 'Grant') !== false;
1375            $privilege['privileges'] = explode(',', $row['Proc_priv']);
1376        } elseif ($row['Type'] === 't') {
1377            $privilege['table'] = $row['Table_name'];
1378            $privilege['has_grant'] = strpos($row['Table_priv'], 'Grant') !== false;
1379            $tablePrivs = explode(',', $row['Table_priv']);
1380            $specificPrivileges = [];
1381            $grantsArr = $this->getTableGrantsArray();
1382            foreach ($grantsArr as $grant) {
1383                $specificPrivileges[$grant[0]] = 'N';
1384                foreach ($tablePrivs as $tablePriv) {
1385                    if ($grant[0] != $tablePriv) {
1386                        continue;
1387                    }
1388
1389                    $specificPrivileges[$grant[0]] = 'Y';
1390                }
1391            }
1392            $privilege['privileges'] = $this->extractPrivInfo(
1393                $specificPrivileges,
1394                true,
1395                true
1396            );
1397        } else {
1398            $privilege['has_grant'] = $row['Grant_priv'] === 'Y';
1399            $privilege['privileges'] = $this->extractPrivInfo($row, true);
1400        }
1401
1402        return $privilege;
1403    }
1404
1405    /**
1406     * @param string $db database name
1407     *
1408     * @return array
1409     */
1410    private function getGlobalAndDatabasePrivileges(string $db): array
1411    {
1412        $listOfPrivileges = '`Select_priv`,
1413            `Insert_priv`,
1414            `Update_priv`,
1415            `Delete_priv`,
1416            `Create_priv`,
1417            `Drop_priv`,
1418            `Grant_priv`,
1419            `Index_priv`,
1420            `Alter_priv`,
1421            `References_priv`,
1422            `Create_tmp_table_priv`,
1423            `Lock_tables_priv`,
1424            `Create_view_priv`,
1425            `Show_view_priv`,
1426            `Create_routine_priv`,
1427            `Alter_routine_priv`,
1428            `Execute_priv`,
1429            `Event_priv`,
1430            `Trigger_priv`,';
1431
1432        $listOfComparedPrivileges = 'BINARY `Select_priv` = \'N\' AND
1433            BINARY `Insert_priv` = \'N\' AND
1434            BINARY `Update_priv` = \'N\' AND
1435            BINARY `Delete_priv` = \'N\' AND
1436            BINARY `Create_priv` = \'N\' AND
1437            BINARY `Drop_priv` = \'N\' AND
1438            BINARY `Grant_priv` = \'N\' AND
1439            BINARY `References_priv` = \'N\' AND
1440            BINARY `Create_tmp_table_priv` = \'N\' AND
1441            BINARY `Lock_tables_priv` = \'N\' AND
1442            BINARY `Create_view_priv` = \'N\' AND
1443            BINARY `Show_view_priv` = \'N\' AND
1444            BINARY `Create_routine_priv` = \'N\' AND
1445            BINARY `Alter_routine_priv` = \'N\' AND
1446            BINARY `Execute_priv` = \'N\' AND
1447            BINARY `Event_priv` = \'N\' AND
1448            BINARY `Trigger_priv` = \'N\'';
1449
1450        $query = '
1451            (
1452                SELECT `User`, `Host`, ' . $listOfPrivileges . ' \'*\' AS `Db`, \'g\' AS `Type`
1453                FROM `mysql`.`user`
1454                WHERE NOT (' . $listOfComparedPrivileges . ')
1455            )
1456            UNION
1457            (
1458                SELECT `User`, `Host`, ' . $listOfPrivileges . ' `Db`, \'d\' AS `Type`
1459                FROM `mysql`.`db`
1460                WHERE \'' . $this->dbi->escapeString($db) . '\' LIKE `Db` AND NOT (' . $listOfComparedPrivileges . ')
1461            )
1462            ORDER BY `User` ASC, `Host` ASC, `Db` ASC;
1463        ';
1464        $result = $this->dbi->query($query);
1465        if ($result === false) {
1466            return [];
1467        }
1468
1469        $privileges = [];
1470        while ($row = $this->dbi->fetchAssoc($result)) {
1471            $privileges[] = $row;
1472        }
1473
1474        return $privileges;
1475    }
1476
1477    /**
1478     * @param string $db    database name
1479     * @param string $table table name
1480     *
1481     * @return array
1482     */
1483    private function getTablePrivileges(string $db, string $table): array
1484    {
1485        $query = '
1486            SELECT `User`, `Host`, `Db`, \'t\' AS `Type`, `Table_name`, `Table_priv`
1487            FROM `mysql`.`tables_priv`
1488            WHERE
1489                ? LIKE `Db` AND
1490                ? LIKE `Table_name` AND
1491                NOT (`Table_priv` = \'\' AND Column_priv = \'\')
1492            ORDER BY `User` ASC, `Host` ASC, `Db` ASC, `Table_priv` ASC;
1493        ';
1494        $statement = $this->dbi->prepare($query);
1495        if ($statement === false
1496            || ! $statement->bind_param('ss', $db, $table)
1497            || ! $statement->execute()
1498        ) {
1499            return [];
1500        }
1501
1502        $result = $statement->get_result();
1503        $statement->close();
1504        if ($result === false) {
1505            return [];
1506        }
1507
1508        $privileges = [];
1509        while ($row = $this->dbi->fetchAssoc($result)) {
1510            $privileges[] = $row;
1511        }
1512
1513        return $privileges;
1514    }
1515
1516    /**
1517     * @param string $db database name
1518     *
1519     * @return array
1520     */
1521    private function getRoutinesPrivileges(string $db): array
1522    {
1523        $query = '
1524            SELECT *, \'r\' AS `Type`
1525            FROM `mysql`.`procs_priv`
1526            WHERE Db = \'' . $this->dbi->escapeString($db) . '\';
1527        ';
1528        $result = $this->dbi->query($query);
1529        if ($result === false) {
1530            return [];
1531        }
1532
1533        $privileges = [];
1534        while ($row = $this->dbi->fetchAssoc($result)) {
1535            $privileges[] = $row;
1536        }
1537
1538        return $privileges;
1539    }
1540
1541    /**
1542     * Get HTML error for View Users form
1543     * For non superusers such as grant/create users
1544     *
1545     * @return string
1546     */
1547    public function getHtmlForViewUsersError()
1548    {
1549        return Message::error(
1550            __('Not enough privilege to view users.')
1551        )->getDisplay();
1552    }
1553
1554    /**
1555     * Returns edit, revoke or export link for a user.
1556     *
1557     * @param string $linktype    The link type (edit | revoke | export)
1558     * @param string $username    User name
1559     * @param string $hostname    Host name
1560     * @param string $dbname      Database name
1561     * @param string $tablename   Table name
1562     * @param string $routinename Routine name
1563     * @param string $initial     Initial value
1564     *
1565     * @return string HTML code with link
1566     */
1567    public function getUserLink(
1568        $linktype,
1569        $username,
1570        $hostname,
1571        $dbname = '',
1572        $tablename = '',
1573        $routinename = '',
1574        $initial = ''
1575    ) {
1576        $html = '<a';
1577        switch ($linktype) {
1578            case 'edit':
1579                $html .= ' class="edit_user_anchor"';
1580                break;
1581            case 'export':
1582                $html .= ' class="export_user_anchor ajax"';
1583                break;
1584        }
1585        $params = [
1586            'username' => $username,
1587            'hostname' => $hostname,
1588        ];
1589        switch ($linktype) {
1590            case 'edit':
1591                $params['dbname'] = $dbname;
1592                $params['tablename'] = $tablename;
1593                $params['routinename'] = $routinename;
1594                break;
1595            case 'revoke':
1596                $params['dbname'] = $dbname;
1597                $params['tablename'] = $tablename;
1598                $params['routinename'] = $routinename;
1599                $params['revokeall'] = 1;
1600                break;
1601            case 'export':
1602                $params['initial'] = $initial;
1603                $params['export'] = 1;
1604                break;
1605        }
1606
1607        $html .= ' href="' . Url::getFromRoute('/server/privileges');
1608        if ($linktype === 'revoke') {
1609            $html .= '" data-post="' . Url::getCommon($params, '', false);
1610        } else {
1611            $html .= Url::getCommon($params, '&');
1612        }
1613        $html .= '">';
1614
1615        switch ($linktype) {
1616            case 'edit':
1617                $html .= Generator::getIcon('b_usredit', __('Edit privileges'));
1618                break;
1619            case 'revoke':
1620                $html .= Generator::getIcon('b_usrdrop', __('Revoke'));
1621                break;
1622            case 'export':
1623                $html .= Generator::getIcon('b_tblexport', __('Export'));
1624                break;
1625        }
1626
1627        return $html . '</a>';
1628    }
1629
1630    /**
1631     * Returns number of defined user groups
1632     *
1633     * @return int
1634     */
1635    public function getUserGroupCount()
1636    {
1637        $cfgRelation = $this->relation->getRelationsParam();
1638        $user_group_table = Util::backquote($cfgRelation['db'])
1639            . '.' . Util::backquote($cfgRelation['usergroups']);
1640        $sql_query = 'SELECT COUNT(*) FROM ' . $user_group_table;
1641
1642        return $this->dbi->fetchValue(
1643            $sql_query,
1644            0,
1645            0,
1646            DatabaseInterface::CONNECT_CONTROL
1647        );
1648    }
1649
1650    /**
1651     * Returns name of user group that user is part of
1652     *
1653     * @param string $username User name
1654     *
1655     * @return mixed|null usergroup if found or null if not found
1656     */
1657    public function getUserGroupForUser($username)
1658    {
1659        $cfgRelation = $this->relation->getRelationsParam();
1660
1661        if (empty($cfgRelation['db'])
1662            || empty($cfgRelation['users'])
1663        ) {
1664            return null;
1665        }
1666
1667        $user_table = Util::backquote($cfgRelation['db'])
1668            . '.' . Util::backquote($cfgRelation['users']);
1669        $sql_query = 'SELECT `usergroup` FROM ' . $user_table
1670            . ' WHERE `username` = \'' . $username . '\''
1671            . ' LIMIT 1';
1672
1673        $usergroup = $this->dbi->fetchValue(
1674            $sql_query,
1675            0,
1676            0,
1677            DatabaseInterface::CONNECT_CONTROL
1678        );
1679
1680        if ($usergroup === false) {
1681            return null;
1682        }
1683
1684        return $usergroup;
1685    }
1686
1687    /**
1688     * This function return the extra data array for the ajax behavior
1689     *
1690     * @param string $password  password
1691     * @param string $sql_query sql query
1692     * @param string $hostname  hostname
1693     * @param string $username  username
1694     *
1695     * @return array
1696     */
1697    public function getExtraDataForAjaxBehavior(
1698        $password,
1699        $sql_query,
1700        $hostname,
1701        $username
1702    ) {
1703        if (isset($GLOBALS['dbname'])) {
1704            //if (preg_match('/\\\\(?:_|%)/i', $dbname)) {
1705            if (preg_match('/(?<!\\\\)(?:_|%)/', $GLOBALS['dbname'])) {
1706                $dbname_is_wildcard = true;
1707            } else {
1708                $dbname_is_wildcard = false;
1709            }
1710        }
1711
1712        $user_group_count = 0;
1713        if ($GLOBALS['cfgRelation']['menuswork']) {
1714            $user_group_count = $this->getUserGroupCount();
1715        }
1716
1717        $extra_data = [];
1718        if (strlen($sql_query) > 0) {
1719            $extra_data['sql_query'] = Generator::getMessage('', $sql_query);
1720        }
1721
1722        if (isset($_POST['change_copy'])) {
1723            $cfgRelation = $this->relation->getRelationsParam();
1724            $user = [
1725                'name' => $username,
1726                'host' => $hostname,
1727                'has_password' => ! empty($password) || isset($_POST['pma_pw']),
1728                'privileges' => implode(', ', $this->extractPrivInfo(null, true)),
1729                'has_group' => ! empty($cfgRelation['users']) && ! empty($cfgRelation['usergroups']),
1730                'has_group_edit' => $cfgRelation['menuswork'] && $user_group_count > 0,
1731                'has_grant' => isset($_POST['Grant_priv']) && $_POST['Grant_priv'] === 'Y',
1732            ];
1733            $extra_data['new_user_string'] = $this->template->render('server/privileges/new_user_ajax', [
1734                'user' => $user,
1735                'is_grantuser' => $this->dbi->isGrantUser(),
1736                'initial' => $_GET['initial'] ?? '',
1737            ]);
1738
1739            /**
1740             * Generate the string for this alphabet's initial, to update the user
1741             * pagination
1742             */
1743            $new_user_initial = mb_strtoupper(
1744                mb_substr($username, 0, 1)
1745            );
1746            $newUserInitialString = '<a href="';
1747            $newUserInitialString .= Url::getFromRoute('/server/privileges', ['initial' => $new_user_initial]);
1748            $newUserInitialString .= '">' . $new_user_initial . '</a>';
1749            $extra_data['new_user_initial'] = $new_user_initial;
1750            $extra_data['new_user_initial_string'] = $newUserInitialString;
1751        }
1752
1753        if (isset($_POST['update_privs'])) {
1754            $extra_data['db_specific_privs'] = false;
1755            $extra_data['db_wildcard_privs'] = false;
1756            if (isset($dbname_is_wildcard)) {
1757                $extra_data['db_specific_privs'] = ! $dbname_is_wildcard;
1758                $extra_data['db_wildcard_privs'] = $dbname_is_wildcard;
1759            }
1760            $new_privileges = implode(', ', $this->extractPrivInfo(null, true));
1761
1762            $extra_data['new_privileges'] = $new_privileges;
1763        }
1764
1765        if (isset($_GET['validate_username'])) {
1766            $sql_query = "SELECT * FROM `mysql`.`user` WHERE `User` = '"
1767                . $this->dbi->escapeString($_GET['username']) . "';";
1768            $res = $this->dbi->query($sql_query);
1769            $row = $this->dbi->fetchRow($res);
1770            if (empty($row)) {
1771                $extra_data['user_exists'] = false;
1772            } else {
1773                $extra_data['user_exists'] = true;
1774            }
1775        }
1776
1777        return $extra_data;
1778    }
1779
1780    /**
1781     * no db name given, so we want all privs for the given user
1782     * db name was given, so we want all user specific rights for this db
1783     * So this function returns user rights as an array
1784     *
1785     * @param string $username username
1786     * @param string $hostname host name
1787     * @param string $type     database or table
1788     * @param string $dbname   database name
1789     *
1790     * @return array database rights
1791     */
1792    public function getUserSpecificRights($username, $hostname, $type, $dbname = '')
1793    {
1794        $user_host_condition = ' WHERE `User`'
1795            . " = '" . $this->dbi->escapeString($username) . "'"
1796            . ' AND `Host`'
1797            . " = '" . $this->dbi->escapeString($hostname) . "'";
1798
1799        if ($type === 'database') {
1800            $tables_to_search_for_users = [
1801                'tables_priv',
1802                'columns_priv',
1803                'procs_priv',
1804            ];
1805            $dbOrTableName = 'Db';
1806        } elseif ($type === 'table') {
1807            $user_host_condition .= " AND `Db` LIKE '"
1808                . $this->dbi->escapeString($dbname) . "'";
1809            $tables_to_search_for_users = ['columns_priv'];
1810            $dbOrTableName = 'Table_name';
1811        } else { // routine
1812            $user_host_condition .= " AND `Db` LIKE '"
1813                . $this->dbi->escapeString($dbname) . "'";
1814            $tables_to_search_for_users = ['procs_priv'];
1815            $dbOrTableName = 'Routine_name';
1816        }
1817
1818        // we also want privileges for this user not in table `db` but in other table
1819        $tables = $this->dbi->fetchResult('SHOW TABLES FROM `mysql`;');
1820
1821        $db_rights_sqls = [];
1822        foreach ($tables_to_search_for_users as $table_search_in) {
1823            if (! in_array($table_search_in, $tables)) {
1824                continue;
1825            }
1826
1827            $db_rights_sqls[] = '
1828                SELECT DISTINCT `' . $dbOrTableName . '`
1829                FROM `mysql`.' . Util::backquote($table_search_in)
1830               . $user_host_condition;
1831        }
1832
1833        $user_defaults = [
1834            $dbOrTableName  => '',
1835            'Grant_priv'    => 'N',
1836            'privs'         => ['USAGE'],
1837            'Column_priv'   => true,
1838        ];
1839
1840        // for the rights
1841        $db_rights = [];
1842
1843        $db_rights_sql = '(' . implode(') UNION (', $db_rights_sqls) . ')'
1844            . ' ORDER BY `' . $dbOrTableName . '` ASC';
1845
1846        $db_rights_result = $this->dbi->query($db_rights_sql);
1847
1848        while ($db_rights_row = $this->dbi->fetchAssoc($db_rights_result)) {
1849            $db_rights_row = array_merge($user_defaults, $db_rights_row);
1850            if ($type === 'database') {
1851                // only Db names in the table `mysql`.`db` uses wildcards
1852                // as we are in the db specific rights display we want
1853                // all db names escaped, also from other sources
1854                $db_rights_row['Db'] = Util::escapeMysqlWildcards(
1855                    $db_rights_row['Db']
1856                );
1857            }
1858            $db_rights[$db_rights_row[$dbOrTableName]] = $db_rights_row;
1859        }
1860
1861        $this->dbi->freeResult($db_rights_result);
1862
1863        if ($type === 'database') {
1864            $sql_query = 'SELECT * FROM `mysql`.`db`'
1865                . $user_host_condition . ' ORDER BY `Db` ASC';
1866        } elseif ($type === 'table') {
1867            $sql_query = 'SELECT `Table_name`,'
1868                . ' `Table_priv`,'
1869                . ' IF(`Column_priv` = _latin1 \'\', 0, 1)'
1870                . ' AS \'Column_priv\''
1871                . ' FROM `mysql`.`tables_priv`'
1872                . $user_host_condition
1873                . ' ORDER BY `Table_name` ASC;';
1874        } else {
1875            $sql_query = 'SELECT `Routine_name`, `Proc_priv`'
1876                . ' FROM `mysql`.`procs_priv`'
1877                . $user_host_condition
1878                . ' ORDER BY `Routine_name`';
1879        }
1880
1881        $result = $this->dbi->query($sql_query);
1882
1883        while ($row = $this->dbi->fetchAssoc($result)) {
1884            if (isset($db_rights[$row[$dbOrTableName]])) {
1885                $db_rights[$row[$dbOrTableName]]
1886                    = array_merge($db_rights[$row[$dbOrTableName]], $row);
1887            } else {
1888                $db_rights[$row[$dbOrTableName]] = $row;
1889            }
1890            if ($type !== 'database') {
1891                continue;
1892            }
1893
1894            // there are db specific rights for this user
1895            // so we can drop this db rights
1896            $db_rights[$row['Db']]['can_delete'] = true;
1897        }
1898        $this->dbi->freeResult($result);
1899
1900        return $db_rights;
1901    }
1902
1903    /**
1904     * Parses Proc_priv data
1905     *
1906     * @param string $privs Proc_priv
1907     *
1908     * @return array
1909     */
1910    public function parseProcPriv($privs)
1911    {
1912        $result = [
1913            'Alter_routine_priv' => 'N',
1914            'Execute_priv'       => 'N',
1915            'Grant_priv'         => 'N',
1916        ];
1917        foreach (explode(',', (string) $privs) as $priv) {
1918            if ($priv === 'Alter Routine') {
1919                $result['Alter_routine_priv'] = 'Y';
1920            } else {
1921                $result[$priv . '_priv'] = 'Y';
1922            }
1923        }
1924
1925        return $result;
1926    }
1927
1928    /**
1929     * Get a HTML table for display user's table specific or database specific rights
1930     *
1931     * @param string $username username
1932     * @param string $hostname host name
1933     * @param string $type     database, table or routine
1934     * @param string $dbname   database name
1935     *
1936     * @return string
1937     */
1938    public function getHtmlForAllTableSpecificRights(
1939        $username,
1940        $hostname,
1941        $type,
1942        $dbname = ''
1943    ) {
1944        $uiData = [
1945            'database' => [
1946                'form_id'        => 'database_specific_priv',
1947                'sub_menu_label' => __('Database'),
1948                'legend'         => __('Database-specific privileges'),
1949                'type_label'     => __('Database'),
1950            ],
1951            'table' => [
1952                'form_id'        => 'table_specific_priv',
1953                'sub_menu_label' => __('Table'),
1954                'legend'         => __('Table-specific privileges'),
1955                'type_label'     => __('Table'),
1956            ],
1957            'routine' => [
1958                'form_id'        => 'routine_specific_priv',
1959                'sub_menu_label' => __('Routine'),
1960                'legend'         => __('Routine-specific privileges'),
1961                'type_label'     => __('Routine'),
1962            ],
1963        ];
1964
1965        /**
1966         * no db name given, so we want all privs for the given user
1967         * db name was given, so we want all user specific rights for this db
1968         */
1969        $db_rights = $this->getUserSpecificRights($username, $hostname, $type, $dbname);
1970        ksort($db_rights);
1971
1972        $foundRows = [];
1973        $privileges = [];
1974        foreach ($db_rights as $row) {
1975            $onePrivilege = [];
1976
1977            $paramTableName = '';
1978            $paramRoutineName = '';
1979
1980            if ($type === 'database') {
1981                $name = $row['Db'];
1982                $onePrivilege['grant']        = $row['Grant_priv'] === 'Y';
1983                $onePrivilege['table_privs']   = ! empty($row['Table_priv'])
1984                    || ! empty($row['Column_priv']);
1985                $onePrivilege['privileges'] = implode(',', $this->extractPrivInfo($row, true));
1986
1987                $paramDbName = $row['Db'];
1988            } elseif ($type === 'table') {
1989                $name = $row['Table_name'];
1990                $onePrivilege['grant'] = in_array(
1991                    'Grant',
1992                    explode(',', $row['Table_priv'])
1993                );
1994                $onePrivilege['column_privs']  = ! empty($row['Column_priv']);
1995                $onePrivilege['privileges'] = implode(',', $this->extractPrivInfo($row, true));
1996
1997                $paramDbName = Util::escapeMysqlWildcards($dbname);
1998                $paramTableName = $row['Table_name'];
1999            } else { // routine
2000                $name = $row['Routine_name'];
2001                $onePrivilege['grant'] = in_array(
2002                    'Grant',
2003                    explode(',', $row['Proc_priv'])
2004                );
2005
2006                $privs = $this->parseProcPriv($row['Proc_priv']);
2007                $onePrivilege['privileges'] = implode(
2008                    ',',
2009                    $this->extractPrivInfo($privs, true)
2010                );
2011
2012                $paramDbName = Util::escapeMysqlWildcards($dbname);
2013                $paramRoutineName = $row['Routine_name'];
2014            }
2015
2016            $foundRows[] = $name;
2017            $onePrivilege['name'] = $name;
2018
2019            $onePrivilege['edit_link'] = '';
2020            if ($this->dbi->isGrantUser()) {
2021                $onePrivilege['edit_link'] = $this->getUserLink(
2022                    'edit',
2023                    $username,
2024                    $hostname,
2025                    $paramDbName,
2026                    $paramTableName,
2027                    $paramRoutineName
2028                );
2029            }
2030
2031            $onePrivilege['revoke_link'] = '';
2032            if ($type !== 'database' || ! empty($row['can_delete'])) {
2033                $onePrivilege['revoke_link'] = $this->getUserLink(
2034                    'revoke',
2035                    $username,
2036                    $hostname,
2037                    $paramDbName,
2038                    $paramTableName,
2039                    $paramRoutineName
2040                );
2041            }
2042
2043            $privileges[] = $onePrivilege;
2044        }
2045
2046        $data = $uiData[$type];
2047        $data['privileges'] = $privileges;
2048        $data['username']   = $username;
2049        $data['hostname']   = $hostname;
2050        $data['database']   = $dbname;
2051        $data['type']       = $type;
2052
2053        if ($type === 'database') {
2054            // we already have the list of databases from libraries/common.inc.php
2055            // via $pma = new PMA;
2056            $pred_db_array = $GLOBALS['dblist']->databases;
2057            $databases_to_skip = [
2058                'information_schema',
2059                'performance_schema',
2060            ];
2061
2062            $databases = [];
2063            $escaped_databases = [];
2064            if (! empty($pred_db_array)) {
2065                foreach ($pred_db_array as $current_db) {
2066                    if (in_array($current_db, $databases_to_skip)) {
2067                        continue;
2068                    }
2069                    $current_db_escaped = Util::escapeMysqlWildcards($current_db);
2070                    // cannot use array_diff() once, outside of the loop,
2071                    // because the list of databases has special characters
2072                    // already escaped in $foundRows,
2073                    // contrary to the output of SHOW DATABASES
2074                    if (in_array($current_db_escaped, $foundRows)) {
2075                        continue;
2076                    }
2077                    $databases[] = $current_db;
2078                    $escaped_databases[] = $current_db_escaped;
2079                }
2080            }
2081            $data['databases'] = $databases;
2082            $data['escaped_databases'] = $escaped_databases;
2083        } elseif ($type === 'table') {
2084            $result = @$this->dbi->tryQuery(
2085                'SHOW TABLES FROM ' . Util::backquote($dbname),
2086                DatabaseInterface::CONNECT_USER,
2087                DatabaseInterface::QUERY_STORE
2088            );
2089
2090            $tables = [];
2091            if ($result) {
2092                while ($row = $this->dbi->fetchRow($result)) {
2093                    if (in_array($row[0], $foundRows)) {
2094                        continue;
2095                    }
2096
2097                    $tables[] = $row[0];
2098                }
2099                $this->dbi->freeResult($result);
2100            }
2101            $data['tables'] = $tables;
2102        } else { // routine
2103            $routineData = $this->dbi->getRoutines($dbname);
2104
2105            $routines = [];
2106            foreach ($routineData as $routine) {
2107                if (in_array($routine['name'], $foundRows)) {
2108                    continue;
2109                }
2110
2111                $routines[] = $routine['name'];
2112            }
2113            $data['routines'] = $routines;
2114        }
2115
2116        return $this->template->render('server/privileges/privileges_summary', $data);
2117    }
2118
2119    /**
2120     * Get HTML for display the users overview
2121     * (if less than 50 users, display them immediately)
2122     *
2123     * @param array  $result         ran sql query
2124     * @param array  $db_rights      user's database rights array
2125     * @param string $themeImagePath a image source link
2126     * @param string $text_dir       text directory
2127     *
2128     * @return string HTML snippet
2129     */
2130    public function getUsersOverview($result, array $db_rights, $themeImagePath, $text_dir)
2131    {
2132        $cfgRelation = $this->relation->getRelationsParam();
2133
2134        while ($row = $this->dbi->fetchAssoc($result)) {
2135            $row['privs'] = $this->extractPrivInfo($row, true);
2136            $db_rights[$row['User']][$row['Host']] = $row;
2137        }
2138        $this->dbi->freeResult($result);
2139
2140        $user_group_count = 0;
2141        if ($cfgRelation['menuswork']) {
2142            $sql_query = 'SELECT * FROM ' . Util::backquote($cfgRelation['db'])
2143                . '.' . Util::backquote($cfgRelation['users']);
2144            $result = $this->relation->queryAsControlUser($sql_query, false);
2145            $group_assignment = [];
2146            if ($result) {
2147                while ($row = $this->dbi->fetchAssoc($result)) {
2148                    $group_assignment[$row['username']] = $row['usergroup'];
2149                }
2150            }
2151            $this->dbi->freeResult($result);
2152
2153            $user_group_count = $this->getUserGroupCount();
2154        }
2155
2156        $hosts = [];
2157        foreach ($db_rights as $user) {
2158            ksort($user);
2159            foreach ($user as $host) {
2160                $check_plugin_query = 'SELECT * FROM `mysql`.`user` WHERE '
2161                    . "`User` = '" . $host['User'] . "' AND `Host` = '"
2162                    . $host['Host'] . "'";
2163                $res = $this->dbi->fetchSingleRow($check_plugin_query);
2164
2165                $hasPassword = false;
2166                if ((isset($res['authentication_string'])
2167                    && ! empty($res['authentication_string']))
2168                    || (isset($res['Password'])
2169                    && ! empty($res['Password']))
2170                ) {
2171                    $hasPassword = true;
2172                }
2173
2174                $hosts[] = [
2175                    'user' => $host['User'],
2176                    'host' => $host['Host'],
2177                    'has_password' => $hasPassword,
2178                    'has_select_priv' => isset($host['Select_priv']),
2179                    'privileges' => $host['privs'],
2180                    'group' => $group_assignment[$host['User']] ?? '',
2181                    'has_grant' => $host['Grant_priv'] === 'Y',
2182                ];
2183            }
2184        }
2185
2186        return $this->template->render('server/privileges/users_overview', [
2187            'menus_work' => $cfgRelation['menuswork'],
2188            'user_group_count' => $user_group_count,
2189            'theme_image_path' => $themeImagePath,
2190            'text_dir' => $text_dir,
2191            'initial' => $_GET['initial'] ?? '',
2192            'hosts' => $hosts,
2193            'is_grantuser' => $this->dbi->isGrantUser(),
2194            'is_createuser' => $this->dbi->isCreateUser(),
2195        ]);
2196    }
2197
2198    /**
2199     * Get HTML for Displays the initials
2200     *
2201     * @param array $array_initials array for all initials, even non A-Z
2202     *
2203     * @return string HTML snippet
2204     */
2205    public function getHtmlForInitials(array $array_initials)
2206    {
2207        // initialize to false the letters A-Z
2208        for ($letter_counter = 1; $letter_counter < 27; $letter_counter++) {
2209            if (isset($array_initials[mb_chr($letter_counter + 64)])) {
2210                continue;
2211            }
2212
2213            $array_initials[mb_chr($letter_counter + 64)] = false;
2214        }
2215
2216        $initials = $this->dbi->tryQuery(
2217            'SELECT DISTINCT UPPER(LEFT(`User`,1)) FROM `user`'
2218            . ' ORDER BY UPPER(LEFT(`User`,1)) ASC',
2219            DatabaseInterface::CONNECT_USER,
2220            DatabaseInterface::QUERY_STORE
2221        );
2222        if ($initials) {
2223            while ([$tmp_initial] = $this->dbi->fetchRow($initials)) {
2224                $array_initials[$tmp_initial] = true;
2225            }
2226        }
2227
2228        // Display the initials, which can be any characters, not
2229        // just letters. For letters A-Z, we add the non-used letters
2230        // as greyed out.
2231
2232        uksort($array_initials, 'strnatcasecmp');
2233
2234        return $this->template->render('server/privileges/initials_row', [
2235            'array_initials' => $array_initials,
2236            'initial' => $_GET['initial'] ?? null,
2237        ]);
2238    }
2239
2240    /**
2241     * Get the database rights array for Display user overview
2242     *
2243     * @return array    database rights array
2244     */
2245    public function getDbRightsForUserOverview()
2246    {
2247        // we also want users not in table `user` but in other table
2248        $tables = $this->dbi->fetchResult('SHOW TABLES FROM `mysql`;');
2249
2250        $tablesSearchForUsers = [
2251            'user',
2252            'db',
2253            'tables_priv',
2254            'columns_priv',
2255            'procs_priv',
2256        ];
2257
2258        $db_rights_sqls = [];
2259        foreach ($tablesSearchForUsers as $table_search_in) {
2260            if (! in_array($table_search_in, $tables)) {
2261                continue;
2262            }
2263
2264            $db_rights_sqls[] = 'SELECT DISTINCT `User`, `Host` FROM `mysql`.`'
2265                . $table_search_in . '` '
2266                . (isset($_GET['initial'])
2267                ? $this->rangeOfUsers($_GET['initial'])
2268                : '');
2269        }
2270        $user_defaults = [
2271            'User'       => '',
2272            'Host'       => '%',
2273            'Password'   => '?',
2274            'Grant_priv' => 'N',
2275            'privs'      => ['USAGE'],
2276        ];
2277
2278        // for the rights
2279        $db_rights = [];
2280
2281        $db_rights_sql = '(' . implode(') UNION (', $db_rights_sqls) . ')'
2282            . ' ORDER BY `User` ASC, `Host` ASC';
2283
2284        $db_rights_result = $this->dbi->query($db_rights_sql);
2285
2286        while ($db_rights_row = $this->dbi->fetchAssoc($db_rights_result)) {
2287            $db_rights_row = array_merge($user_defaults, $db_rights_row);
2288            $db_rights[$db_rights_row['User']][$db_rights_row['Host']]
2289                = $db_rights_row;
2290        }
2291        $this->dbi->freeResult($db_rights_result);
2292        ksort($db_rights);
2293
2294        return $db_rights;
2295    }
2296
2297    /**
2298     * Delete user and get message and sql query for delete user in privileges
2299     *
2300     * @param array $queries queries
2301     *
2302     * @return array Message
2303     */
2304    public function deleteUser(array $queries)
2305    {
2306        $sql_query = '';
2307        if (empty($queries)) {
2308            $message = Message::error(__('No users selected for deleting!'));
2309        } else {
2310            if ($_POST['mode'] == 3) {
2311                $queries[] = '# ' . __('Reloading the privileges') . ' …';
2312                $queries[] = 'FLUSH PRIVILEGES;';
2313            }
2314            $drop_user_error = '';
2315            foreach ($queries as $sql_query) {
2316                if ($sql_query[0] === '#') {
2317                    continue;
2318                }
2319
2320                if ($this->dbi->tryQuery($sql_query)) {
2321                    continue;
2322                }
2323
2324                $drop_user_error .= $this->dbi->getError() . "\n";
2325            }
2326            // tracking sets this, causing the deleted db to be shown in navi
2327            unset($GLOBALS['db']);
2328
2329            $sql_query = implode("\n", $queries);
2330            if (! empty($drop_user_error)) {
2331                $message = Message::rawError($drop_user_error);
2332            } else {
2333                $message = Message::success(
2334                    __('The selected users have been deleted successfully.')
2335                );
2336            }
2337        }
2338
2339        return [
2340            $sql_query,
2341            $message,
2342        ];
2343    }
2344
2345    /**
2346     * Update the privileges and return the success or error message
2347     *
2348     * @return array success message or error message for update
2349     */
2350    public function updatePrivileges(
2351        string $username,
2352        string $hostname,
2353        string $tablename,
2354        string $dbname,
2355        string $itemType
2356    ): array {
2357        $db_and_table = $this->wildcardEscapeForGrant($dbname, $tablename);
2358
2359        $sql_query0 = 'REVOKE ALL PRIVILEGES ON ' . $itemType . ' ' . $db_and_table
2360            . ' FROM \'' . $this->dbi->escapeString($username)
2361            . '\'@\'' . $this->dbi->escapeString($hostname) . '\';';
2362
2363        if (! isset($_POST['Grant_priv']) || $_POST['Grant_priv'] !== 'Y') {
2364            $sql_query1 = 'REVOKE GRANT OPTION ON ' . $itemType . ' ' . $db_and_table
2365                . ' FROM \'' . $this->dbi->escapeString($username) . '\'@\''
2366                . $this->dbi->escapeString($hostname) . '\';';
2367        } else {
2368            $sql_query1 = '';
2369        }
2370
2371        $grantBackQuery = null;
2372        $alterUserQuery = null;
2373
2374        // Should not do a GRANT USAGE for a table-specific privilege, it
2375        // causes problems later (cannot revoke it)
2376        if (! (strlen($tablename) > 0
2377            && implode('', $this->extractPrivInfo()) === 'USAGE')
2378        ) {
2379            [$grantBackQuery, $alterUserQuery] = $this->generateQueriesForUpdatePrivileges(
2380                $itemType,
2381                $db_and_table,
2382                $username,
2383                $hostname,
2384                $dbname
2385            );
2386        }
2387        if (! $this->dbi->tryQuery($sql_query0)) {
2388            // This might fail when the executing user does not have
2389            // ALL PRIVILEGES themselves.
2390            // See https://github.com/phpmyadmin/phpmyadmin/issues/9673
2391            $sql_query0 = '';
2392        }
2393        if (! empty($sql_query1) && ! $this->dbi->tryQuery($sql_query1)) {
2394            // this one may fail, too...
2395            $sql_query1 = '';
2396        }
2397
2398        if ($grantBackQuery !== null) {
2399            $this->dbi->query($grantBackQuery);
2400        } else {
2401            $grantBackQuery = '';
2402        }
2403
2404        if ($alterUserQuery !== null) {
2405            $this->dbi->query($alterUserQuery);
2406        } else {
2407            $alterUserQuery = '';
2408        }
2409
2410        $sql_query = $sql_query0 . ' ' . $sql_query1 . ' ' . $grantBackQuery . ' ' . $alterUserQuery;
2411        $message = Message::success(__('You have updated the privileges for %s.'));
2412        $message->addParam('\'' . $username . '\'@\'' . $hostname . '\'');
2413
2414        return [
2415            $sql_query,
2416            $message,
2417        ];
2418    }
2419
2420    /**
2421     * Generate the query for the GRANTS and requirements + limits
2422     *
2423     * @return array<int,string|null>
2424     */
2425    private function generateQueriesForUpdatePrivileges(
2426        string $itemType,
2427        string $db_and_table,
2428        string $username,
2429        string $hostname,
2430        string $dbname
2431    ): array {
2432        $alterUserQuery = null;
2433
2434        $grantBackQuery = 'GRANT ' . implode(', ', $this->extractPrivInfo())
2435            . ' ON ' . $itemType . ' ' . $db_and_table
2436            . ' TO \'' . $this->dbi->escapeString($username) . '\'@\''
2437            . $this->dbi->escapeString($hostname) . '\'';
2438
2439        $isMySqlOrPercona = Util::getServerType() === 'MySQL' || Util::getServerType() === 'Percona Server';
2440        $needsToUseAlter = $isMySqlOrPercona && $this->dbi->getVersion() >= 80011;
2441
2442        if ($needsToUseAlter) {
2443            $alterUserQuery = 'ALTER USER \'' . $this->dbi->escapeString($username) . '\'@\''
2444            . $this->dbi->escapeString($hostname) . '\' ';
2445        }
2446
2447        if (strlen($dbname) === 0) {
2448            // add REQUIRE clause
2449            if ($needsToUseAlter) {
2450                $alterUserQuery .= $this->getRequireClause();
2451            } else {
2452                $grantBackQuery .= $this->getRequireClause();
2453            }
2454        }
2455
2456        if ((isset($_POST['Grant_priv']) && $_POST['Grant_priv'] === 'Y')
2457            || (strlen($dbname) === 0
2458            && (isset($_POST['max_questions']) || isset($_POST['max_connections'])
2459            || isset($_POST['max_updates'])
2460            || isset($_POST['max_user_connections'])))
2461        ) {
2462            if ($needsToUseAlter) {
2463                $alterUserQuery .= $this->getWithClauseForAddUserAndUpdatePrivs();
2464            } else {
2465                $grantBackQuery .= $this->getWithClauseForAddUserAndUpdatePrivs();
2466            }
2467        }
2468        $grantBackQuery .= ';';
2469
2470        if ($needsToUseAlter) {
2471            $alterUserQuery .= ';';
2472        }
2473
2474        return [$grantBackQuery, $alterUserQuery];
2475    }
2476
2477    /**
2478     * Get List of information: Changes / copies a user
2479     *
2480     * @return array
2481     */
2482    public function getDataForChangeOrCopyUser()
2483    {
2484        $queries = null;
2485        $password = null;
2486
2487        if (isset($_POST['change_copy'])) {
2488            $user_host_condition = ' WHERE `User` = '
2489                . "'" . $this->dbi->escapeString($_POST['old_username']) . "'"
2490                . ' AND `Host` = '
2491                . "'" . $this->dbi->escapeString($_POST['old_hostname']) . "';";
2492            $row = $this->dbi->fetchSingleRow(
2493                'SELECT * FROM `mysql`.`user` ' . $user_host_condition
2494            );
2495            if (! $row) {
2496                $response = Response::getInstance();
2497                $response->addHTML(
2498                    Message::notice(__('No user found.'))->getDisplay()
2499                );
2500                unset($_POST['change_copy']);
2501            } else {
2502                foreach ($row as $key => $value) {
2503                    $GLOBALS[$key] = $value;
2504                }
2505                $serverVersion = $this->dbi->getVersion();
2506                // Recent MySQL versions have the field "Password" in mysql.user,
2507                // so the previous extract creates $row['Password'] but this script
2508                // uses $password
2509                if (! isset($row['password']) && isset($row['Password'])) {
2510                    $row['password'] = $row['Password'];
2511                }
2512                if ((Util::getServerType() === 'MySQL' || Util::getServerType() === 'Percona Server')
2513                    && $serverVersion >= 50606
2514                    && $serverVersion < 50706
2515                    && ((isset($row['authentication_string'])
2516                    && empty($row['password']))
2517                    || (isset($row['plugin'])
2518                    && $row['plugin'] === 'sha256_password'))
2519                ) {
2520                    $row['password'] = $row['authentication_string'];
2521                }
2522
2523                if (Util::getServerType() === 'MariaDB'
2524                    && $serverVersion >= 50500
2525                    && isset($row['authentication_string'])
2526                    && empty($row['password'])
2527                ) {
2528                    $row['password'] = $row['authentication_string'];
2529                }
2530
2531                // Always use 'authentication_string' column
2532                // for MySQL 5.7.6+ since it does not have
2533                // the 'password' column at all
2534                if (in_array(Util::getServerType(), ['MySQL', 'Percona Server'])
2535                    && $serverVersion >= 50706
2536                    && isset($row['authentication_string'])
2537                ) {
2538                    $row['password'] = $row['authentication_string'];
2539                }
2540                $password = $row['password'];
2541                $queries = [];
2542            }
2543        }
2544
2545        return [
2546            $queries,
2547            $password,
2548        ];
2549    }
2550
2551    /**
2552     * Update Data for information: Deletes users
2553     *
2554     * @param array $queries queries array
2555     *
2556     * @return array
2557     */
2558    public function getDataForDeleteUsers($queries)
2559    {
2560        if (isset($_POST['change_copy'])) {
2561            $selected_usr = [
2562                $_POST['old_username'] . '&amp;#27;' . $_POST['old_hostname'],
2563            ];
2564        } else {
2565            // null happens when no user was selected
2566            $selected_usr = $_POST['selected_usr'] ?? null;
2567            $queries = [];
2568        }
2569
2570        // this happens, was seen in https://reports.phpmyadmin.net/reports/view/17146
2571        if (! is_array($selected_usr)) {
2572            return [];
2573        }
2574
2575        foreach ($selected_usr as $each_user) {
2576            [$this_user, $this_host] = explode('&amp;#27;', $each_user);
2577            $queries[] = '# '
2578                . sprintf(
2579                    __('Deleting %s'),
2580                    '\'' . $this_user . '\'@\'' . $this_host . '\''
2581                )
2582                . ' ...';
2583            $queries[] = 'DROP USER \''
2584                . $this->dbi->escapeString($this_user)
2585                . '\'@\'' . $this->dbi->escapeString($this_host) . '\';';
2586            $this->relationCleanup->user($this_user);
2587
2588            if (! isset($_POST['drop_users_db'])) {
2589                continue;
2590            }
2591
2592            $queries[] = 'DROP DATABASE IF EXISTS '
2593                . Util::backquote($this_user) . ';';
2594            $GLOBALS['reload'] = true;
2595        }
2596
2597        return $queries;
2598    }
2599
2600    /**
2601     * update Message For Reload
2602     */
2603    public function updateMessageForReload(): ?Message
2604    {
2605        $message = null;
2606        if (isset($_GET['flush_privileges'])) {
2607            $sql_query = 'FLUSH PRIVILEGES;';
2608            $this->dbi->query($sql_query);
2609            $message = Message::success(
2610                __('The privileges were reloaded successfully.')
2611            );
2612        }
2613
2614        if (isset($_GET['validate_username'])) {
2615            $message = Message::success();
2616        }
2617
2618        return $message;
2619    }
2620
2621    /**
2622     * update Data For Queries from queries_for_display
2623     *
2624     * @param array      $queries             queries array
2625     * @param array|null $queries_for_display queries array for display
2626     *
2627     * @return array
2628     */
2629    public function getDataForQueries(array $queries, $queries_for_display)
2630    {
2631        $tmp_count = 0;
2632        foreach ($queries as $sql_query) {
2633            if ($sql_query[0] !== '#') {
2634                $this->dbi->query($sql_query);
2635            }
2636            // when there is a query containing a hidden password, take it
2637            // instead of the real query sent
2638            if (isset($queries_for_display[$tmp_count])) {
2639                $queries[$tmp_count] = $queries_for_display[$tmp_count];
2640            }
2641            $tmp_count++;
2642        }
2643
2644        return $queries;
2645    }
2646
2647    /**
2648     * update Data for information: Adds a user
2649     *
2650     * @param string|array|null $dbname      db name
2651     * @param string            $username    user name
2652     * @param string            $hostname    host name
2653     * @param string|null       $password    password
2654     * @param bool              $is_menuwork is_menuwork set?
2655     *
2656     * @return array
2657     */
2658    public function addUser(
2659        $dbname,
2660        $username,
2661        $hostname,
2662        ?string $password,
2663        $is_menuwork
2664    ) {
2665        $message = null;
2666        $queries = null;
2667        $queries_for_display = null;
2668        $sql_query = null;
2669
2670        if (! isset($_POST['adduser_submit']) && ! isset($_POST['change_copy'])) {
2671            return [
2672                $message,
2673                $queries,
2674                $queries_for_display,
2675                $sql_query,
2676                false, // Add user error
2677            ];
2678        }
2679
2680        $sql_query = '';
2681        // Some reports where sent to the error reporting server with phpMyAdmin 5.1.0
2682        // pred_username was reported to be not defined
2683        $predUsername = $_POST['pred_username'] ?? '';
2684        if ($predUsername === 'any') {
2685            $username = '';
2686        }
2687        switch ($_POST['pred_hostname']) {
2688            case 'any':
2689                $hostname = '%';
2690                break;
2691            case 'localhost':
2692                $hostname = 'localhost';
2693                break;
2694            case 'hosttable':
2695                $hostname = '';
2696                break;
2697            case 'thishost':
2698                $_user_name = $this->dbi->fetchValue('SELECT USER()');
2699                $hostname = mb_substr(
2700                    $_user_name,
2701                    mb_strrpos($_user_name, '@') + 1
2702                );
2703                unset($_user_name);
2704                break;
2705        }
2706        $sql = "SELECT '1' FROM `mysql`.`user`"
2707            . " WHERE `User` = '" . $this->dbi->escapeString($username) . "'"
2708            . " AND `Host` = '" . $this->dbi->escapeString($hostname) . "';";
2709        if ($this->dbi->fetchValue($sql) == 1) {
2710            $message = Message::error(__('The user %s already exists!'));
2711            $message->addParam('[em]\'' . $username . '\'@\'' . $hostname . '\'[/em]');
2712            $_GET['adduser'] = true;
2713
2714            return [
2715                $message,
2716                $queries,
2717                $queries_for_display,
2718                $sql_query,
2719                true, // Add user error
2720            ];
2721        }
2722
2723        [
2724            $create_user_real,
2725            $create_user_show,
2726            $real_sql_query,
2727            $sql_query,
2728            $password_set_real,
2729            $password_set_show,
2730            $alter_real_sql_query,
2731            $alter_sql_query,
2732        ] = $this->getSqlQueriesForDisplayAndAddUser(
2733            $username,
2734            $hostname,
2735            ($password ?? '')
2736        );
2737
2738        if (empty($_POST['change_copy'])) {
2739            $_error = false;
2740
2741            if ($create_user_real !== null) {
2742                if (! $this->dbi->tryQuery($create_user_real)) {
2743                    $_error = true;
2744                }
2745                if (isset($password_set_real, $_POST['authentication_plugin']) && ! empty($password_set_real)) {
2746                    $this->setProperPasswordHashing(
2747                        $_POST['authentication_plugin']
2748                    );
2749                    if ($this->dbi->tryQuery($password_set_real)) {
2750                        $sql_query .= $password_set_show;
2751                    }
2752                }
2753                $sql_query = $create_user_show . $sql_query;
2754            }
2755
2756            [$sql_query, $message] = $this->addUserAndCreateDatabase(
2757                $_error,
2758                $real_sql_query,
2759                $sql_query,
2760                $username,
2761                $hostname,
2762                $dbname,
2763                $alter_real_sql_query,
2764                $alter_sql_query
2765            );
2766            if (! empty($_POST['userGroup']) && $is_menuwork) {
2767                $this->setUserGroup($GLOBALS['username'], $_POST['userGroup']);
2768            }
2769
2770            return [
2771                $message,
2772                $queries,
2773                $queries_for_display,
2774                $sql_query,
2775                $_error, // Add user error if the query fails
2776            ];
2777        }
2778
2779        // Copy the user group while copying a user
2780        $old_usergroup =
2781            $_POST['old_usergroup'] ?? null;
2782        $this->setUserGroup($_POST['username'], $old_usergroup);
2783
2784        if ($create_user_real !== null) {
2785            $queries[] = $create_user_real;
2786        }
2787        $queries[] = $real_sql_query;
2788
2789        if (isset($password_set_real, $_POST['authentication_plugin']) && ! empty($password_set_real)) {
2790            $this->setProperPasswordHashing(
2791                $_POST['authentication_plugin']
2792            );
2793
2794            $queries[] = $password_set_real;
2795        }
2796        // we put the query containing the hidden password in
2797        // $queries_for_display, at the same position occupied
2798        // by the real query in $queries
2799        $tmp_count = count($queries);
2800        if (isset($create_user_real)) {
2801            $queries_for_display[$tmp_count - 2] = $create_user_show;
2802        }
2803        if (isset($password_set_real) && ! empty($password_set_real)) {
2804            $queries_for_display[$tmp_count - 3] = $create_user_show;
2805            $queries_for_display[$tmp_count - 2] = $sql_query;
2806            $queries_for_display[$tmp_count - 1] = $password_set_show;
2807        } else {
2808            $queries_for_display[$tmp_count - 1] = $sql_query;
2809        }
2810
2811        return [
2812            $message,
2813            $queries,
2814            $queries_for_display,
2815            $sql_query,
2816            false, // Add user error
2817        ];
2818    }
2819
2820    /**
2821     * Sets proper value of `old_passwords` according to
2822     * the authentication plugin selected
2823     *
2824     * @param string $auth_plugin authentication plugin selected
2825     *
2826     * @return void
2827     */
2828    public function setProperPasswordHashing($auth_plugin)
2829    {
2830        // Set the hashing method used by PASSWORD()
2831        // to be of type depending upon $authentication_plugin
2832        if ($auth_plugin === 'sha256_password') {
2833            $this->dbi->tryQuery('SET `old_passwords` = 2;');
2834        } elseif ($auth_plugin === 'mysql_old_password') {
2835            $this->dbi->tryQuery('SET `old_passwords` = 1;');
2836        } else {
2837            $this->dbi->tryQuery('SET `old_passwords` = 0;');
2838        }
2839    }
2840
2841    /**
2842     * Update DB information: DB, Table, isWildcard
2843     *
2844     * @return array
2845     */
2846    public function getDataForDBInfo()
2847    {
2848        $username = null;
2849        $hostname = null;
2850        $dbname = null;
2851        $tablename = null;
2852        $routinename = null;
2853        $return_db = null;
2854
2855        if (isset($_REQUEST['username'])) {
2856            $username = (string) $_REQUEST['username'];
2857        }
2858        if (isset($_REQUEST['hostname'])) {
2859            $hostname = (string) $_REQUEST['hostname'];
2860        }
2861        /**
2862         * Checks if a dropdown box has been used for selecting a database / table
2863         */
2864        if (Core::isValid($_POST['pred_tablename'])) {
2865            $tablename = $_POST['pred_tablename'];
2866        } elseif (Core::isValid($_REQUEST['tablename'])) {
2867            $tablename = $_REQUEST['tablename'];
2868        } else {
2869            unset($tablename);
2870        }
2871
2872        if (Core::isValid($_POST['pred_routinename'])) {
2873            $routinename = $_POST['pred_routinename'];
2874        } elseif (Core::isValid($_REQUEST['routinename'])) {
2875            $routinename = $_REQUEST['routinename'];
2876        } else {
2877            unset($routinename);
2878        }
2879
2880        if (isset($_POST['pred_dbname'])) {
2881            $is_valid_pred_dbname = true;
2882            foreach ($_POST['pred_dbname'] as $key => $db_name) {
2883                if (! Core::isValid($db_name)) {
2884                    $is_valid_pred_dbname = false;
2885                    break;
2886                }
2887            }
2888        }
2889
2890        if (isset($_REQUEST['dbname'])) {
2891            $is_valid_dbname = true;
2892            if (is_array($_REQUEST['dbname'])) {
2893                foreach ($_REQUEST['dbname'] as $key => $db_name) {
2894                    if (! Core::isValid($db_name)) {
2895                        $is_valid_dbname = false;
2896                        break;
2897                    }
2898                }
2899            } else {
2900                if (! Core::isValid($_REQUEST['dbname'])) {
2901                    $is_valid_dbname = false;
2902                }
2903            }
2904        }
2905
2906        if (isset($is_valid_pred_dbname) && $is_valid_pred_dbname) {
2907            $dbname = $_POST['pred_dbname'];
2908            // If dbname contains only one database.
2909            if (count($dbname) === 1) {
2910                $dbname = $dbname[0];
2911            }
2912        } elseif (isset($is_valid_dbname) && $is_valid_dbname) {
2913            $dbname = $_REQUEST['dbname'];
2914        } else {
2915            unset($dbname, $tablename);
2916        }
2917
2918        if (isset($dbname)) {
2919            if (is_array($dbname)) {
2920                $db_and_table = $dbname;
2921                $return_db = $dbname;
2922                foreach ($db_and_table as $key => $db_name) {
2923                    $db_and_table[$key] .= '.';
2924                }
2925            } else {
2926                $unescaped_db = Util::unescapeMysqlWildcards($dbname);
2927                $db_and_table = Util::backquote($unescaped_db) . '.';
2928                $return_db = $dbname;
2929            }
2930            if (isset($tablename)) {
2931                $db_and_table .= Util::backquote($tablename);
2932            } else {
2933                if (is_array($db_and_table)) {
2934                    foreach ($db_and_table as $key => $db_name) {
2935                        $db_and_table[$key] .= '*';
2936                    }
2937                } else {
2938                    $db_and_table .= '*';
2939                }
2940            }
2941        } else {
2942            $db_and_table = '*.*';
2943        }
2944
2945        // check if given $dbname is a wildcard or not
2946        $databaseNameIsWildcard = ! is_array($dbname ?? '') && preg_match(
2947            '/(?<!\\\\)(?:_|%)/',
2948            $dbname ?? ''
2949        );
2950
2951        return [
2952            $username,
2953            $hostname,
2954            $return_db,
2955            $tablename ?? null,
2956            $routinename ?? null,
2957            $db_and_table,
2958            $databaseNameIsWildcard,
2959        ];
2960    }
2961
2962    /**
2963     * Get title and textarea for export user definition in Privileges
2964     *
2965     * @param string $username username
2966     * @param string $hostname host name
2967     *
2968     * @return array ($title, $export)
2969     */
2970    public function getListForExportUserDefinition($username, $hostname)
2971    {
2972        $export = '<textarea class="export" cols="60" rows="15">';
2973
2974        if (isset($_POST['selected_usr'])) {
2975            // export privileges for selected users
2976            $title = __('Privileges');
2977
2978            //For removing duplicate entries of users
2979            $_POST['selected_usr'] = array_unique($_POST['selected_usr']);
2980
2981            foreach ($_POST['selected_usr'] as $export_user) {
2982                $export_username = mb_substr(
2983                    $export_user,
2984                    0,
2985                    (int) mb_strpos($export_user, '&')
2986                );
2987                $export_hostname = mb_substr(
2988                    $export_user,
2989                    mb_strrpos($export_user, ';') + 1
2990                );
2991                $export .= '# '
2992                    . sprintf(
2993                        __('Privileges for %s'),
2994                        '`' . htmlspecialchars($export_username)
2995                        . '`@`' . htmlspecialchars($export_hostname) . '`'
2996                    )
2997                    . "\n\n";
2998                $export .= $this->getGrants($export_username, $export_hostname) . "\n";
2999            }
3000        } else {
3001            // export privileges for a single user
3002            $title = __('User') . ' `' . htmlspecialchars($username)
3003                . '`@`' . htmlspecialchars($hostname) . '`';
3004            $export .= $this->getGrants($username, $hostname);
3005        }
3006        // remove trailing whitespace
3007        $export = trim($export);
3008
3009        $export .= '</textarea>';
3010
3011        return [
3012            $title,
3013            $export,
3014        ];
3015    }
3016
3017    /**
3018     * Get HTML for display Add userfieldset
3019     *
3020     * @param string $db    the database
3021     * @param string $table the table name
3022     *
3023     * @return string html output
3024     */
3025    public function getAddUserHtmlFieldset($db = '', $table = '')
3026    {
3027        if (! $this->dbi->isCreateUser()) {
3028            return '';
3029        }
3030        $rel_params = [];
3031        $url_params = ['adduser' => 1];
3032        if (! empty($db)) {
3033            $url_params['dbname']
3034                = $rel_params['checkprivsdb']
3035                    = $db;
3036        }
3037        if (! empty($table)) {
3038            $url_params['tablename']
3039                = $rel_params['checkprivstable']
3040                    = $table;
3041        }
3042
3043        return $this->template->render('server/privileges/add_user_fieldset', [
3044            'url_params' => $url_params,
3045            'rel_params' => $rel_params,
3046        ]);
3047    }
3048
3049    /**
3050     * Get HTML snippet for display user overview page
3051     *
3052     * @param string $themeImagePath a image source link
3053     * @param string $text_dir       text directory
3054     *
3055     * @return string
3056     */
3057    public function getHtmlForUserOverview($themeImagePath, $text_dir)
3058    {
3059        $password_column = 'Password';
3060        $server_type = Util::getServerType();
3061        $serverVersion = $this->dbi->getVersion();
3062        if (($server_type === 'MySQL' || $server_type === 'Percona Server')
3063            && $serverVersion >= 50706
3064        ) {
3065            $password_column = 'authentication_string';
3066        }
3067        // $sql_query is for the initial-filtered,
3068        // $sql_query_all is for counting the total no. of users
3069
3070        $sql_query = $sql_query_all = 'SELECT *,' .
3071            ' IF(`' . $password_column . "` = _latin1 '', 'N', 'Y') AS 'Password'" .
3072            ' FROM `mysql`.`user`';
3073
3074        $sql_query .= (isset($_GET['initial'])
3075            ? $this->rangeOfUsers($_GET['initial'])
3076            : '');
3077
3078        $sql_query .= ' ORDER BY `User` ASC, `Host` ASC;';
3079        $sql_query_all .= ' ;';
3080
3081        $res = $this->dbi->tryQuery(
3082            $sql_query,
3083            DatabaseInterface::CONNECT_USER,
3084            DatabaseInterface::QUERY_STORE
3085        );
3086        $res_all = $this->dbi->tryQuery(
3087            $sql_query_all,
3088            DatabaseInterface::CONNECT_USER,
3089            DatabaseInterface::QUERY_STORE
3090        );
3091
3092        $errorMessages = '';
3093        if (! $res) {
3094            // the query failed! This may have two reasons:
3095            // - the user does not have enough privileges
3096            // - the privilege tables use a structure of an earlier version.
3097            // so let's try a more simple query
3098
3099            $this->dbi->freeResult($res);
3100            $this->dbi->freeResult($res_all);
3101            $sql_query = 'SELECT * FROM `mysql`.`user`';
3102            $res = $this->dbi->tryQuery(
3103                $sql_query,
3104                DatabaseInterface::CONNECT_USER,
3105                DatabaseInterface::QUERY_STORE
3106            );
3107
3108            if (! $res) {
3109                $errorMessages .= $this->getHtmlForViewUsersError();
3110                $errorMessages .= $this->getAddUserHtmlFieldset();
3111            } else {
3112                // This message is hardcoded because I will replace it by
3113                // a automatic repair feature soon.
3114                $raw = 'Your privilege table structure seems to be older than'
3115                    . ' this MySQL version!<br>'
3116                    . 'Please run the <code>mysql_upgrade</code> command'
3117                    . ' that should be included in your MySQL server distribution'
3118                    . ' to solve this problem!';
3119                $errorMessages .= Message::rawError($raw)->getDisplay();
3120            }
3121            $this->dbi->freeResult($res);
3122        } else {
3123            $db_rights = $this->getDbRightsForUserOverview();
3124            // for all initials, even non A-Z
3125            $array_initials = [];
3126
3127            foreach ($db_rights as $right) {
3128                foreach ($right as $account) {
3129                    if (empty($account['User']) && $account['Host'] === 'localhost') {
3130                        $emptyUserNotice = Message::notice(
3131                            __(
3132                                'A user account allowing any user from localhost to '
3133                                . 'connect is present. This will prevent other users '
3134                                . 'from connecting if the host part of their account '
3135                                . 'allows a connection from any (%) host.'
3136                            )
3137                            . MySQLDocumentation::show('problems-connecting')
3138                        )->getDisplay();
3139                        break 2;
3140                    }
3141                }
3142            }
3143
3144            /**
3145             * Displays the initials
3146             * Also not necessary if there is less than 20 privileges
3147             */
3148            if ($this->dbi->numRows($res_all) > 20) {
3149                $initials = $this->getHtmlForInitials($array_initials);
3150            }
3151
3152            /**
3153            * Display the user overview
3154            * (if less than 50 users, display them immediately)
3155            */
3156            if (isset($_GET['initial'])
3157                || isset($_GET['showall'])
3158                || $this->dbi->numRows($res) < 50
3159            ) {
3160                $usersOverview = $this->getUsersOverview(
3161                    $res,
3162                    $db_rights,
3163                    $themeImagePath,
3164                    $text_dir
3165                );
3166            }
3167
3168            $response = Response::getInstance();
3169            if (! $response->isAjax()
3170                || ! empty($_REQUEST['ajax_page_request'])
3171            ) {
3172                if ($GLOBALS['is_reload_priv']) {
3173                    $flushnote = new Message(
3174                        __(
3175                            'Note: phpMyAdmin gets the users’ privileges directly '
3176                            . 'from MySQL’s privilege tables. The content of these '
3177                            . 'tables may differ from the privileges the server uses, '
3178                            . 'if they have been changed manually. In this case, '
3179                            . 'you should %sreload the privileges%s before you continue.'
3180                        ),
3181                        Message::NOTICE
3182                    );
3183                    $flushnote->addParamHtml(
3184                        '<a href="' . Url::getFromRoute('/server/privileges', ['flush_privileges' => 1])
3185                        . '" id="reload_privileges_anchor">'
3186                    );
3187                    $flushnote->addParamHtml('</a>');
3188                } else {
3189                    $flushnote = new Message(
3190                        __(
3191                            'Note: phpMyAdmin gets the users’ privileges directly '
3192                            . 'from MySQL’s privilege tables. The content of these '
3193                            . 'tables may differ from the privileges the server uses, '
3194                            . 'if they have been changed manually. In this case, '
3195                            . 'the privileges have to be reloaded but currently, you '
3196                            . 'don\'t have the RELOAD privilege.'
3197                        )
3198                        . MySQLDocumentation::show(
3199                            'privileges-provided',
3200                            false,
3201                            null,
3202                            null,
3203                            'priv_reload'
3204                        ),
3205                        Message::NOTICE
3206                    );
3207                }
3208                $flushNotice = $flushnote->getDisplay();
3209            }
3210        }
3211
3212        return $this->template->render('server/privileges/user_overview', [
3213            'error_messages' => $errorMessages,
3214            'empty_user_notice' => $emptyUserNotice ?? '',
3215            'initials' => $initials ?? '',
3216            'users_overview' => $usersOverview ?? '',
3217            'is_createuser' => $this->dbi->isCreateUser(),
3218            'flush_notice' => $flushNotice ?? '',
3219        ]);
3220    }
3221
3222    /**
3223     * Get HTML snippet for display user properties
3224     *
3225     * @param bool         $dbname_is_wildcard whether database name is wildcard or not
3226     * @param string       $url_dbname         url database name that urlencode() string
3227     * @param string       $username           username
3228     * @param string       $hostname           host name
3229     * @param string|array $dbname             database name
3230     * @param string       $tablename          table name
3231     *
3232     * @return string
3233     */
3234    public function getHtmlForUserProperties(
3235        $dbname_is_wildcard,
3236        $url_dbname,
3237        $username,
3238        $hostname,
3239        $dbname,
3240        $tablename
3241    ) {
3242        global $cfg;
3243
3244        $sql = "SELECT '1' FROM `mysql`.`user`"
3245            . " WHERE `User` = '" . $this->dbi->escapeString($username) . "'"
3246            . " AND `Host` = '" . $this->dbi->escapeString($hostname) . "';";
3247
3248        $user_does_not_exists = (bool) ! $this->dbi->fetchValue($sql);
3249
3250        $loginInformationFields = '';
3251        if ($user_does_not_exists) {
3252            $loginInformationFields = $this->getHtmlForLoginInformationFields();
3253        }
3254
3255        $_params = [
3256            'username' => $username,
3257            'hostname' => $hostname,
3258        ];
3259        if (! is_array($dbname) && strlen($dbname) > 0) {
3260            $_params['dbname'] = $dbname;
3261            if (strlen($tablename) > 0) {
3262                $_params['tablename'] = $tablename;
3263            }
3264        } else {
3265            $_params['dbname'] = $dbname;
3266        }
3267
3268        $privilegesTable = $this->getHtmlToDisplayPrivilegesTable(
3269            // If $dbname is an array, pass any one db as all have same privs.
3270            Core::ifSetOr($dbname, is_array($dbname) ? $dbname[0] : '*', 'length'),
3271            Core::ifSetOr($tablename, '*', 'length')
3272        );
3273
3274        $tableSpecificRights = '';
3275        if (! is_array($dbname) && strlen($tablename) === 0
3276            && empty($dbname_is_wildcard)
3277        ) {
3278            // no table name was given, display all table specific rights
3279            // but only if $dbname contains no wildcards
3280            if (strlen($dbname) === 0) {
3281                $tableSpecificRights .= $this->getHtmlForAllTableSpecificRights(
3282                    $username,
3283                    $hostname,
3284                    'database'
3285                );
3286            } else {
3287                // unescape wildcards in dbname at table level
3288                $unescaped_db = Util::unescapeMysqlWildcards($dbname);
3289
3290                $tableSpecificRights .= $this->getHtmlForAllTableSpecificRights(
3291                    $username,
3292                    $hostname,
3293                    'table',
3294                    $unescaped_db
3295                );
3296                $tableSpecificRights .= $this->getHtmlForAllTableSpecificRights(
3297                    $username,
3298                    $hostname,
3299                    'routine',
3300                    $unescaped_db
3301                );
3302            }
3303        }
3304
3305        $databaseUrl = Util::getScriptNameForOption(
3306            $cfg['DefaultTabDatabase'],
3307            'database'
3308        );
3309        $databaseUrlTitle = Util::getTitleForTarget(
3310            $cfg['DefaultTabDatabase']
3311        );
3312        $tableUrl = Util::getScriptNameForOption(
3313            $cfg['DefaultTabTable'],
3314            'table'
3315        );
3316        $tableUrlTitle = Util::getTitleForTarget(
3317            $cfg['DefaultTabTable']
3318        );
3319
3320        $changePassword = '';
3321        $userGroup = '';
3322        $changeLoginInfoFields = '';
3323        if (! is_array($dbname) && strlen($dbname) === 0 && ! $user_does_not_exists) {
3324            //change login information
3325            $changePassword = $this->getFormForChangePassword($username, $hostname, true);
3326            $userGroup = $this->getUserGroupForUser($username);
3327            $changeLoginInfoFields = $this->getHtmlForLoginInformationFields('change', $username, $hostname);
3328        }
3329
3330        return $this->template->render('server/privileges/user_properties', [
3331            'user_does_not_exists' => $user_does_not_exists,
3332            'login_information_fields' => $loginInformationFields,
3333            'params' => $_params,
3334            'privileges_table' => $privilegesTable,
3335            'table_specific_rights' => $tableSpecificRights,
3336            'change_password' => $changePassword,
3337            'database' => $dbname,
3338            'dbname' => $url_dbname,
3339            'username' => $username,
3340            'hostname' => $hostname,
3341            'is_databases' => $dbname_is_wildcard || is_array($dbname) && count($dbname) > 1,
3342            'is_wildcard' => $dbname_is_wildcard,
3343            'table' => $tablename,
3344            'current_user' => $this->dbi->getCurrentUser(),
3345            'user_group' => $userGroup,
3346            'change_login_info_fields' => $changeLoginInfoFields,
3347            'database_url' => $databaseUrl,
3348            'database_url_title' => $databaseUrlTitle,
3349            'table_url' => $tableUrl,
3350            'table_url_title' => $tableUrlTitle,
3351        ]);
3352    }
3353
3354    /**
3355     * Get queries for Table privileges to change or copy user
3356     *
3357     * @param string $user_host_condition user host condition to
3358     *                                    select relevant table privileges
3359     * @param array  $queries             queries array
3360     * @param string $username            username
3361     * @param string $hostname            host name
3362     *
3363     * @return array
3364     */
3365    public function getTablePrivsQueriesForChangeOrCopyUser(
3366        $user_host_condition,
3367        array $queries,
3368        $username,
3369        $hostname
3370    ) {
3371        $res = $this->dbi->query(
3372            'SELECT `Db`, `Table_name`, `Table_priv` FROM `mysql`.`tables_priv`'
3373            . $user_host_condition,
3374            DatabaseInterface::CONNECT_USER,
3375            DatabaseInterface::QUERY_STORE
3376        );
3377        while ($row = $this->dbi->fetchAssoc($res)) {
3378            $res2 = $this->dbi->query(
3379                'SELECT `Column_name`, `Column_priv`'
3380                . ' FROM `mysql`.`columns_priv`'
3381                . ' WHERE `User`'
3382                . ' = \'' . $this->dbi->escapeString($_POST['old_username']) . "'"
3383                . ' AND `Host`'
3384                . ' = \'' . $this->dbi->escapeString($_POST['old_username']) . '\''
3385                . ' AND `Db`'
3386                . ' = \'' . $this->dbi->escapeString($row['Db']) . "'"
3387                . ' AND `Table_name`'
3388                . ' = \'' . $this->dbi->escapeString($row['Table_name']) . "'"
3389                . ';',
3390                DatabaseInterface::CONNECT_USER,
3391                DatabaseInterface::QUERY_STORE
3392            );
3393
3394            $tmp_privs1 = $this->extractPrivInfo($row);
3395            $tmp_privs2 = [
3396                'Select' => [],
3397                'Insert' => [],
3398                'Update' => [],
3399                'References' => [],
3400            ];
3401
3402            while ($row2 = $this->dbi->fetchAssoc($res2)) {
3403                $tmp_array = explode(',', $row2['Column_priv']);
3404                if (in_array('Select', $tmp_array)) {
3405                    $tmp_privs2['Select'][] = $row2['Column_name'];
3406                }
3407                if (in_array('Insert', $tmp_array)) {
3408                    $tmp_privs2['Insert'][] = $row2['Column_name'];
3409                }
3410                if (in_array('Update', $tmp_array)) {
3411                    $tmp_privs2['Update'][] = $row2['Column_name'];
3412                }
3413                if (! in_array('References', $tmp_array)) {
3414                    continue;
3415                }
3416
3417                $tmp_privs2['References'][] = $row2['Column_name'];
3418            }
3419            if (count($tmp_privs2['Select']) > 0 && ! in_array('SELECT', $tmp_privs1)) {
3420                $tmp_privs1[] = 'SELECT (`' . implode('`, `', $tmp_privs2['Select']) . '`)';
3421            }
3422            if (count($tmp_privs2['Insert']) > 0 && ! in_array('INSERT', $tmp_privs1)) {
3423                $tmp_privs1[] = 'INSERT (`' . implode('`, `', $tmp_privs2['Insert']) . '`)';
3424            }
3425            if (count($tmp_privs2['Update']) > 0 && ! in_array('UPDATE', $tmp_privs1)) {
3426                $tmp_privs1[] = 'UPDATE (`' . implode('`, `', $tmp_privs2['Update']) . '`)';
3427            }
3428            if (count($tmp_privs2['References']) > 0
3429                && ! in_array('REFERENCES', $tmp_privs1)
3430            ) {
3431                $tmp_privs1[]
3432                    = 'REFERENCES (`' . implode('`, `', $tmp_privs2['References']) . '`)';
3433            }
3434
3435            $queries[] = 'GRANT ' . implode(', ', $tmp_privs1)
3436                . ' ON ' . Util::backquote($row['Db']) . '.'
3437                . Util::backquote($row['Table_name'])
3438                . ' TO \'' . $this->dbi->escapeString($username)
3439                . '\'@\'' . $this->dbi->escapeString($hostname) . '\''
3440                . (in_array('Grant', explode(',', $row['Table_priv']))
3441                ? ' WITH GRANT OPTION;'
3442                : ';');
3443        }
3444
3445        return $queries;
3446    }
3447
3448    /**
3449     * Get queries for database specific privileges for change or copy user
3450     *
3451     * @param array  $queries  queries array with string
3452     * @param string $username username
3453     * @param string $hostname host name
3454     *
3455     * @return array
3456     */
3457    public function getDbSpecificPrivsQueriesForChangeOrCopyUser(
3458        array $queries,
3459        $username,
3460        $hostname
3461    ) {
3462        $user_host_condition = ' WHERE `User`'
3463            . ' = \'' . $this->dbi->escapeString($_POST['old_username']) . "'"
3464            . ' AND `Host`'
3465            . ' = \'' . $this->dbi->escapeString($_POST['old_hostname']) . '\';';
3466
3467        $res = $this->dbi->query(
3468            'SELECT * FROM `mysql`.`db`' . $user_host_condition
3469        );
3470
3471        while ($row = $this->dbi->fetchAssoc($res)) {
3472            $queries[] = 'GRANT ' . implode(', ', $this->extractPrivInfo($row))
3473                . ' ON ' . Util::backquote($row['Db']) . '.*'
3474                . ' TO \'' . $this->dbi->escapeString($username)
3475                . '\'@\'' . $this->dbi->escapeString($hostname) . '\''
3476                . ($row['Grant_priv'] === 'Y' ? ' WITH GRANT OPTION;' : ';');
3477        }
3478        $this->dbi->freeResult($res);
3479
3480        $queries = $this->getTablePrivsQueriesForChangeOrCopyUser(
3481            $user_host_condition,
3482            $queries,
3483            $username,
3484            $hostname
3485        );
3486
3487        return $queries;
3488    }
3489
3490    /**
3491     * Prepares queries for adding users and
3492     * also create database and return query and message
3493     *
3494     * @param bool   $_error               whether user create or not
3495     * @param string $real_sql_query       SQL query for add a user
3496     * @param string $sql_query            SQL query to be displayed
3497     * @param string $username             username
3498     * @param string $hostname             host name
3499     * @param string $dbname               database name
3500     * @param string $alter_real_sql_query SQL query for ALTER USER
3501     * @param string $alter_sql_query      SQL query for ALTER USER to be displayed
3502     *
3503     * @return array<int,string|Message>
3504     */
3505    public function addUserAndCreateDatabase(
3506        $_error,
3507        $real_sql_query,
3508        $sql_query,
3509        $username,
3510        $hostname,
3511        $dbname,
3512        $alter_real_sql_query,
3513        $alter_sql_query
3514    ): array {
3515        if ($_error || (! empty($real_sql_query)
3516            && ! $this->dbi->tryQuery($real_sql_query))
3517        ) {
3518            $_POST['createdb-1'] = $_POST['createdb-2']
3519                = $_POST['createdb-3'] = null;
3520            $message = Message::rawError((string) $this->dbi->getError());
3521        } elseif ($alter_real_sql_query !== '' && ! $this->dbi->tryQuery($alter_real_sql_query)) {
3522            $_POST['createdb-1'] = $_POST['createdb-2']
3523                = $_POST['createdb-3'] = null;
3524            $message = Message::rawError((string) $this->dbi->getError());
3525        } else {
3526            $sql_query .= $alter_sql_query;
3527            $message = Message::success(__('You have added a new user.'));
3528        }
3529
3530        if (isset($_POST['createdb-1'])) {
3531            // Create database with same name and grant all privileges
3532            $q = 'CREATE DATABASE IF NOT EXISTS '
3533                . Util::backquote(
3534                    $this->dbi->escapeString($username)
3535                ) . ';';
3536            $sql_query .= $q;
3537            if (! $this->dbi->tryQuery($q)) {
3538                $message = Message::rawError((string) $this->dbi->getError());
3539            }
3540
3541            /**
3542             * Reload the navigation
3543             */
3544            $GLOBALS['reload'] = true;
3545            $GLOBALS['db'] = $username;
3546
3547            $q = 'GRANT ALL PRIVILEGES ON '
3548                . Util::backquote(
3549                    Util::escapeMysqlWildcards(
3550                        $this->dbi->escapeString($username)
3551                    )
3552                ) . '.* TO \''
3553                . $this->dbi->escapeString($username)
3554                . '\'@\'' . $this->dbi->escapeString($hostname) . '\';';
3555            $sql_query .= $q;
3556            if (! $this->dbi->tryQuery($q)) {
3557                $message = Message::rawError((string) $this->dbi->getError());
3558            }
3559        }
3560
3561        if (isset($_POST['createdb-2'])) {
3562            // Grant all privileges on wildcard name (username\_%)
3563            $q = 'GRANT ALL PRIVILEGES ON '
3564                . Util::backquote(
3565                    Util::escapeMysqlWildcards(
3566                        $this->dbi->escapeString($username)
3567                    ) . '\_%'
3568                ) . '.* TO \''
3569                . $this->dbi->escapeString($username)
3570                . '\'@\'' . $this->dbi->escapeString($hostname) . '\';';
3571            $sql_query .= $q;
3572            if (! $this->dbi->tryQuery($q)) {
3573                $message = Message::rawError((string) $this->dbi->getError());
3574            }
3575        }
3576
3577        if (isset($_POST['createdb-3'])) {
3578            // Grant all privileges on the specified database to the new user
3579            $q = 'GRANT ALL PRIVILEGES ON '
3580            . Util::backquote(
3581                $dbname
3582            ) . '.* TO \''
3583            . $this->dbi->escapeString($username)
3584            . '\'@\'' . $this->dbi->escapeString($hostname) . '\';';
3585            $sql_query .= $q;
3586            if (! $this->dbi->tryQuery($q)) {
3587                $message = Message::rawError((string) $this->dbi->getError());
3588            }
3589        }
3590
3591        return [
3592            $sql_query,
3593            $message,
3594        ];
3595    }
3596
3597    /**
3598     * Get the hashed string for password
3599     *
3600     * @param string $password password
3601     *
3602     * @return string
3603     */
3604    public function getHashedPassword($password)
3605    {
3606        $password = $this->dbi->escapeString($password);
3607        $result = $this->dbi->fetchSingleRow(
3608            "SELECT PASSWORD('" . $password . "') AS `password`;"
3609        );
3610
3611        return $result['password'];
3612    }
3613
3614    /**
3615     * Check if MariaDB's 'simple_password_check'
3616     * OR 'cracklib_password_check' is ACTIVE
3617     *
3618     * @return bool if at least one of the plugins is ACTIVE
3619     */
3620    public function checkIfMariaDBPwdCheckPluginActive()
3621    {
3622        $serverVersion = $this->dbi->getVersion();
3623        if (! (Util::getServerType() === 'MariaDB' && $serverVersion >= 100002)) {
3624            return false;
3625        }
3626
3627        $result = $this->dbi->tryQuery(
3628            'SHOW PLUGINS SONAME LIKE \'%_password_check%\''
3629        );
3630
3631        /* Plugins are not working, for example directory does not exists */
3632        if ($result === false) {
3633            return false;
3634        }
3635
3636        while ($row = $this->dbi->fetchAssoc($result)) {
3637            if ($row['Status'] === 'ACTIVE') {
3638                return true;
3639            }
3640        }
3641
3642        return false;
3643    }
3644
3645    /**
3646     * Get SQL queries for Display and Add user
3647     *
3648     * @param string $username username
3649     * @param string $hostname host name
3650     * @param string $password password
3651     *
3652     * @return array ($create_user_real, $create_user_show, $real_sql_query, $sql_query
3653     *                $password_set_real, $password_set_show, $alter_real_sql_query, $alter_sql_query)
3654     */
3655    public function getSqlQueriesForDisplayAndAddUser($username, $hostname, $password)
3656    {
3657        $slashedUsername = $this->dbi->escapeString($username);
3658        $slashedHostname = $this->dbi->escapeString($hostname);
3659        $slashedPassword = $this->dbi->escapeString($password);
3660        $serverType = Util::getServerType();
3661        $serverVersion = $this->dbi->getVersion();
3662
3663        $create_user_stmt = sprintf(
3664            'CREATE USER \'%s\'@\'%s\'',
3665            $slashedUsername,
3666            $slashedHostname
3667        );
3668        $isMariaDBPwdPluginActive = $this->checkIfMariaDBPwdCheckPluginActive();
3669
3670        // See https://github.com/phpmyadmin/phpmyadmin/pull/11560#issuecomment-147158219
3671        // for details regarding details of syntax usage for various versions
3672
3673        // 'IDENTIFIED WITH auth_plugin'
3674        // is supported by MySQL 5.5.7+
3675        if (($serverType === 'MySQL' || $serverType === 'Percona Server')
3676            && $serverVersion >= 50507
3677            && isset($_POST['authentication_plugin'])
3678        ) {
3679            $create_user_stmt .= ' IDENTIFIED WITH '
3680                . $_POST['authentication_plugin'];
3681        }
3682
3683        // 'IDENTIFIED VIA auth_plugin'
3684        // is supported by MariaDB 5.2+
3685        if ($serverType === 'MariaDB'
3686            && $serverVersion >= 50200
3687            && isset($_POST['authentication_plugin'])
3688            && ! $isMariaDBPwdPluginActive
3689        ) {
3690            $create_user_stmt .= ' IDENTIFIED VIA '
3691                . $_POST['authentication_plugin'];
3692        }
3693
3694        $create_user_real = $create_user_stmt;
3695        $create_user_show = $create_user_stmt;
3696
3697        $password_set_stmt = 'SET PASSWORD FOR \'%s\'@\'%s\' = \'%s\'';
3698        $password_set_show = sprintf(
3699            $password_set_stmt,
3700            $slashedUsername,
3701            $slashedHostname,
3702            '***'
3703        );
3704
3705        $sql_query_stmt = sprintf(
3706            'GRANT %s ON *.* TO \'%s\'@\'%s\'',
3707            implode(', ', $this->extractPrivInfo()),
3708            $slashedUsername,
3709            $slashedHostname
3710        );
3711        $real_sql_query = $sql_query = $sql_query_stmt;
3712
3713        // Set the proper hashing method
3714        if (isset($_POST['authentication_plugin'])) {
3715            $this->setProperPasswordHashing(
3716                $_POST['authentication_plugin']
3717            );
3718        }
3719
3720        // Use 'CREATE USER ... WITH ... AS ..' syntax for
3721        // newer MySQL versions
3722        // and 'CREATE USER ... VIA .. USING ..' syntax for
3723        // newer MariaDB versions
3724        if ((($serverType == 'MySQL' || $serverType == 'Percona Server')
3725            && $serverVersion >= 50706)
3726            || ($serverType == 'MariaDB'
3727            && $serverVersion >= 50200)
3728        ) {
3729            $password_set_real = null;
3730
3731            // Required for binding '%' with '%s'
3732            $create_user_stmt = str_replace(
3733                '%',
3734                '%%',
3735                $create_user_stmt
3736            );
3737
3738            // MariaDB uses 'USING' whereas MySQL uses 'AS'
3739            // but MariaDB with validation plugin needs cleartext password
3740            if ($serverType == 'MariaDB'
3741                && ! $isMariaDBPwdPluginActive
3742            ) {
3743                $create_user_stmt .= ' USING \'%s\'';
3744            } elseif ($serverType == 'MariaDB') {
3745                $create_user_stmt .= ' IDENTIFIED BY \'%s\'';
3746            } elseif (($serverType == 'MySQL' || $serverType == 'Percona Server') && $serverVersion >= 80011) {
3747                if (mb_strpos($create_user_stmt, 'IDENTIFIED') === false) {
3748                    // Maybe the authentication_plugin was not posted and then a part is missing
3749                    $create_user_stmt .= ' IDENTIFIED BY \'%s\'';
3750                } else {
3751                    $create_user_stmt .= ' BY \'%s\'';
3752                }
3753            } else {
3754                $create_user_stmt .= ' AS \'%s\'';
3755            }
3756
3757            if ($_POST['pred_password'] === 'keep') {
3758                $create_user_real = sprintf(
3759                    $create_user_stmt,
3760                    $slashedPassword
3761                );
3762                $create_user_show = sprintf(
3763                    $create_user_stmt,
3764                    '***'
3765                );
3766            } elseif ($_POST['pred_password'] === 'none') {
3767                $create_user_real = sprintf(
3768                    $create_user_stmt,
3769                    null
3770                );
3771                $create_user_show = sprintf(
3772                    $create_user_stmt,
3773                    '***'
3774                );
3775            } else {
3776                if (! (($serverType === 'MariaDB' && $isMariaDBPwdPluginActive)
3777                    || ($serverType === 'MySQL' || $serverType === 'Percona Server') && $serverVersion >= 80011)
3778                ) {
3779                    $hashedPassword = $this->getHashedPassword($_POST['pma_pw']);
3780                } else {
3781                    // MariaDB with validation plugin needs cleartext password
3782                    $hashedPassword = $_POST['pma_pw'];
3783                }
3784                $create_user_real = sprintf(
3785                    $create_user_stmt,
3786                    $hashedPassword
3787                );
3788                $create_user_show = sprintf(
3789                    $create_user_stmt,
3790                    '***'
3791                );
3792            }
3793        } else {
3794            // Use 'SET PASSWORD' syntax for pre-5.7.6 MySQL versions
3795            // and pre-5.2.0 MariaDB versions
3796            if ($_POST['pred_password'] === 'keep') {
3797                $password_set_real = sprintf(
3798                    $password_set_stmt,
3799                    $slashedUsername,
3800                    $slashedHostname,
3801                    $slashedPassword
3802                );
3803            } elseif ($_POST['pred_password'] === 'none') {
3804                $password_set_real = sprintf(
3805                    $password_set_stmt,
3806                    $slashedUsername,
3807                    $slashedHostname,
3808                    null
3809                );
3810            } else {
3811                $hashedPassword = $this->getHashedPassword($_POST['pma_pw']);
3812                $password_set_real = sprintf(
3813                    $password_set_stmt,
3814                    $slashedUsername,
3815                    $slashedHostname,
3816                    $hashedPassword
3817                );
3818            }
3819        }
3820
3821        $alter_real_sql_query = '';
3822        $alter_sql_query = '';
3823        if (($serverType === 'MySQL' || $serverType === 'Percona Server') && $serverVersion >= 80011) {
3824            $sql_query_stmt = '';
3825            if ((isset($_POST['Grant_priv']) && $_POST['Grant_priv'] === 'Y')
3826                || (isset($GLOBALS['Grant_priv']) && $GLOBALS['Grant_priv'] === 'Y')
3827            ) {
3828                $sql_query_stmt = ' WITH GRANT OPTION';
3829            }
3830            $real_sql_query .= $sql_query_stmt;
3831            $sql_query .= $sql_query_stmt;
3832
3833            $alter_sql_query_stmt = sprintf(
3834                'ALTER USER \'%s\'@\'%s\'',
3835                $slashedUsername,
3836                $slashedHostname
3837            );
3838            $alter_real_sql_query = $alter_sql_query_stmt;
3839            $alter_sql_query = $alter_sql_query_stmt;
3840        }
3841
3842        // add REQUIRE clause
3843        $require_clause = $this->getRequireClause();
3844        $with_clause = $this->getWithClauseForAddUserAndUpdatePrivs();
3845
3846        if (($serverType === 'MySQL' || $serverType === 'Percona Server') && $serverVersion >= 80011) {
3847            $alter_real_sql_query .= $require_clause;
3848            $alter_sql_query .= $require_clause;
3849            $alter_real_sql_query .= $with_clause;
3850            $alter_sql_query .= $with_clause;
3851        } else {
3852            $real_sql_query .= $require_clause;
3853            $sql_query .= $require_clause;
3854            $real_sql_query .= $with_clause;
3855            $sql_query .= $with_clause;
3856        }
3857
3858        if ($alter_real_sql_query !== '') {
3859            $alter_real_sql_query .= ';';
3860            $alter_sql_query .= ';';
3861        }
3862        $create_user_real .= ';';
3863        $create_user_show .= ';';
3864        $real_sql_query .= ';';
3865        $sql_query .= ';';
3866        // No Global GRANT_OPTION privilege
3867        if (! $this->dbi->isGrantUser()) {
3868            $real_sql_query = '';
3869            $sql_query = '';
3870        }
3871
3872        // Use 'SET PASSWORD' for pre-5.7.6 MySQL versions
3873        // and pre-5.2.0 MariaDB
3874        if ((($serverType === 'MySQL' || $serverType === 'Percona Server')
3875            && $serverVersion >= 50706)
3876            || ($serverType === 'MariaDB'
3877            && $serverVersion >= 50200)
3878        ) {
3879            $password_set_real = null;
3880            $password_set_show = null;
3881        } else {
3882            if ($password_set_real !== null) {
3883                $password_set_real .= ';';
3884            }
3885            $password_set_show .= ';';
3886        }
3887
3888        return [
3889            $create_user_real,
3890            $create_user_show,
3891            $real_sql_query,
3892            $sql_query,
3893            $password_set_real,
3894            $password_set_show,
3895            $alter_real_sql_query,
3896            $alter_sql_query,
3897        ];
3898    }
3899
3900    /**
3901     * Returns the type ('PROCEDURE' or 'FUNCTION') of the routine
3902     *
3903     * @param string $dbname      database
3904     * @param string $routineName routine
3905     *
3906     * @return string type
3907     */
3908    public function getRoutineType($dbname, $routineName)
3909    {
3910        $routineData = $this->dbi->getRoutines($dbname);
3911
3912        foreach ($routineData as $routine) {
3913            if ($routine['name'] === $routineName) {
3914                return $routine['type'];
3915            }
3916        }
3917
3918        return '';
3919    }
3920
3921    /**
3922     * @param string $username User name
3923     * @param string $hostname Host name
3924     * @param string $database Database name
3925     * @param string $routine  Routine name
3926     *
3927     * @return array
3928     */
3929    private function getRoutinePrivileges(
3930        string $username,
3931        string $hostname,
3932        string $database,
3933        string $routine
3934    ): array {
3935        $sql = 'SELECT `Proc_priv`'
3936            . ' FROM `mysql`.`procs_priv`'
3937            . " WHERE `User` = '" . $this->dbi->escapeString($username) . "'"
3938            . " AND `Host` = '" . $this->dbi->escapeString($hostname) . "'"
3939            . " AND `Db` = '"
3940            . $this->dbi->escapeString(Util::unescapeMysqlWildcards($database)) . "'"
3941            . " AND `Routine_name` LIKE '" . $this->dbi->escapeString($routine) . "';";
3942        $privileges = $this->dbi->fetchValue($sql);
3943        if ($privileges === false) {
3944            $privileges = '';
3945        }
3946
3947        return $this->parseProcPriv($privileges);
3948    }
3949
3950    public function getFormForChangePassword(string $username, string $hostname, bool $editOthers): string
3951    {
3952        global $route;
3953
3954        $isPrivileges = $route === '/server/privileges';
3955
3956        $serverType = Util::getServerType();
3957        $serverVersion = $this->dbi->getVersion();
3958        $origAuthPlugin = $this->getCurrentAuthenticationPlugin(
3959            'change',
3960            $username,
3961            $hostname
3962        );
3963
3964        $isMySqlOrPerconaDb = ($serverType === 'MySQL' || $serverType === 'Percona Server');
3965
3966        $isNew = ($isMySqlOrPerconaDb && $serverVersion >= 50507)
3967            || ($serverType === 'MariaDB' && $serverVersion >= 50200);
3968        $hasMoreAuthPlugins = ($isMySqlOrPerconaDb && $serverVersion >= 50706)
3969            || ($this->dbi->isSuperUser() && $editOthers);
3970
3971        $activeAuthPlugins = ['mysql_native_password' => __('Native MySQL authentication')];
3972
3973        if ($isNew && $hasMoreAuthPlugins) {
3974            $activeAuthPlugins = $this->getActiveAuthPlugins();
3975            if (isset($activeAuthPlugins['mysql_old_password'])) {
3976                unset($activeAuthPlugins['mysql_old_password']);
3977            }
3978        }
3979
3980        return $this->template->render('server/privileges/change_password', [
3981            'username' => $username,
3982            'hostname' => $hostname,
3983            'is_privileges' => $isPrivileges,
3984            'is_new' => $isNew,
3985            'has_more_auth_plugins' => $hasMoreAuthPlugins,
3986            'active_auth_plugins' => $activeAuthPlugins,
3987            'orig_auth_plugin' => $origAuthPlugin,
3988        ]);
3989    }
3990}
3991