diff options
Diffstat (limited to 'srcs/phpmyadmin/libraries/classes/Server')
| -rw-r--r-- | srcs/phpmyadmin/libraries/classes/Server/Plugin.php | 274 | ||||
| -rw-r--r-- | srcs/phpmyadmin/libraries/classes/Server/Plugins.php | 74 | ||||
| -rw-r--r-- | srcs/phpmyadmin/libraries/classes/Server/Privileges.php | 5649 | ||||
| -rw-r--r-- | srcs/phpmyadmin/libraries/classes/Server/Select.php | 128 | ||||
| -rw-r--r-- | srcs/phpmyadmin/libraries/classes/Server/Status/Data.php | 430 | ||||
| -rw-r--r-- | srcs/phpmyadmin/libraries/classes/Server/Status/Monitor.php | 546 | ||||
| -rw-r--r-- | srcs/phpmyadmin/libraries/classes/Server/UserGroups.php | 390 | ||||
| -rw-r--r-- | srcs/phpmyadmin/libraries/classes/Server/Users.php | 64 |
8 files changed, 7555 insertions, 0 deletions
diff --git a/srcs/phpmyadmin/libraries/classes/Server/Plugin.php b/srcs/phpmyadmin/libraries/classes/Server/Plugin.php new file mode 100644 index 0000000..9b45297 --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Server/Plugin.php @@ -0,0 +1,274 @@ +<?php +/** + * Server Plugin value object + * @package PhpMyAdmin\Server + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Server; + +/** + * Server Plugin value object + * @package PhpMyAdmin\Server + */ +final class Plugin +{ + /** + * @var string + */ + private $name; + + /** + * @var string|null + */ + private $version; + + /** + * @var string + */ + private $status; + + /** + * @var string + */ + private $type; + + /** + * @var string|null + */ + private $typeVersion; + + /** + * @var string|null + */ + private $library; + + /** + * @var string|null + */ + private $libraryVersion; + + /** + * @var string|null + */ + private $author; + + /** + * @var string|null + */ + private $description; + + /** + * @var string + */ + private $license; + + /** + * @var string|null + */ + private $loadOption; + + /** + * @var string|null + */ + private $maturity; + + /** + * @var string|null + */ + private $authVersion; + + /** + * @param string $name Name of the plugin + * @param string|null $version Version from the plugin's general type descriptor + * @param string $status Plugin status + * @param string $type Type of plugin + * @param string|null $typeVersion Version from the plugin's type-specific descriptor + * @param string|null $library Plugin's shared object file name + * @param string|null $libraryVersion Version from the plugin's API interface + * @param string|null $author Author of the plugin + * @param string|null $description Description + * @param string $license Plugin's licence + * @param string|null $loadOption How the plugin was loaded + * @param string|null $maturity Plugin's maturity level + * @param string|null $authVersion Plugin's version as determined by the plugin author + */ + private function __construct( + string $name, + ?string $version, + string $status, + string $type, + ?string $typeVersion, + ?string $library, + ?string $libraryVersion, + ?string $author, + ?string $description, + string $license, + ?string $loadOption, + ?string $maturity, + ?string $authVersion + ) { + $this->name = $name; + $this->version = $version; + $this->status = $status; + $this->type = $type; + $this->typeVersion = $typeVersion; + $this->library = $library; + $this->libraryVersion = $libraryVersion; + $this->author = $author; + $this->description = $description; + $this->license = $license; + $this->loadOption = $loadOption; + $this->maturity = $maturity; + $this->authVersion = $authVersion; + } + + /** + * @param array $state array with the properties + * @return self + */ + public static function fromState(array $state): self + { + return new self( + $state['name'] ?? '', + $state['version'] ?? null, + $state['status'] ?? '', + $state['type'] ?? '', + $state['typeVersion'] ?? null, + $state['library'] ?? null, + $state['libraryVersion'] ?? null, + $state['author'] ?? null, + $state['description'] ?? null, + $state['license'] ?? '', + $state['loadOption'] ?? null, + $state['maturity'] ?? null, + $state['authVersion'] ?? null + ); + } + + /** + * @return array + */ + public function toArray(): array + { + return [ + 'name' => $this->getName(), + 'version' => $this->getVersion(), + 'status' => $this->getStatus(), + 'type' => $this->getType(), + 'type_version' => $this->getTypeVersion(), + 'library' => $this->getLibrary(), + 'library_version' => $this->getLibraryVersion(), + 'author' => $this->getAuthor(), + 'description' => $this->getDescription(), + 'license' => $this->getLicense(), + 'load_option' => $this->getLoadOption(), + 'maturity' => $this->getMaturity(), + 'auth_version' => $this->getAuthVersion(), + ]; + } + + /** + * @return string + */ + public function getName(): string + { + return $this->name; + } + + /** + * @return string|null + */ + public function getVersion(): ?string + { + return $this->version; + } + + /** + * @return string + */ + public function getStatus(): string + { + return $this->status; + } + + /** + * @return string + */ + public function getType(): string + { + return $this->type; + } + + /** + * @return string|null + */ + public function getTypeVersion(): ?string + { + return $this->typeVersion; + } + + /** + * @return string|null + */ + public function getLibrary(): ?string + { + return $this->library; + } + + /** + * @return string|null + */ + public function getLibraryVersion(): ?string + { + return $this->libraryVersion; + } + + /** + * @return string|null + */ + public function getAuthor(): ?string + { + return $this->author; + } + + /** + * @return string|null + */ + public function getDescription(): ?string + { + return $this->description; + } + + /** + * @return string + */ + public function getLicense(): string + { + return $this->license; + } + + /** + * @return string|null + */ + public function getLoadOption(): ?string + { + return $this->loadOption; + } + + /** + * @return string|null + */ + public function getMaturity(): ?string + { + return $this->maturity; + } + + /** + * @return string|null + */ + public function getAuthVersion(): ?string + { + return $this->authVersion; + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Server/Plugins.php b/srcs/phpmyadmin/libraries/classes/Server/Plugins.php new file mode 100644 index 0000000..eb8e85a --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Server/Plugins.php @@ -0,0 +1,74 @@ +<?php +/** + * Class Plugins + * @package PhpMyAdmin\Server + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Server; + +use PhpMyAdmin\DatabaseInterface; + +/** + * Class Plugins + * @package PhpMyAdmin\Server + */ +class Plugins +{ + /** + * @var DatabaseInterface + */ + private $dbi; + + /** + * @param DatabaseInterface $dbi DatabaseInterface instance + */ + public function __construct(DatabaseInterface $dbi) + { + $this->dbi = $dbi; + } + + /** + * @return Plugin[] + */ + public function getAll(): array + { + global $cfg; + + $sql = 'SHOW PLUGINS'; + if (! $cfg['Server']['DisableIS']) { + $sql = 'SELECT * FROM information_schema.PLUGINS ORDER BY PLUGIN_TYPE, PLUGIN_NAME'; + } + $result = $this->dbi->query($sql); + $plugins = []; + while ($row = $this->dbi->fetchAssoc($result)) { + $plugins[] = $this->mapRowToPlugin($row); + } + $this->dbi->freeResult($result); + + return $plugins; + } + + /** + * @param array $row Row fetched from database + * @return Plugin + */ + private function mapRowToPlugin(array $row): Plugin + { + return Plugin::fromState([ + 'name' => $row['PLUGIN_NAME'] ?? $row['Name'], + 'version' => $row['PLUGIN_VERSION'] ?? null, + 'status' => $row['PLUGIN_STATUS'] ?? $row['Status'], + 'type' => $row['PLUGIN_TYPE'] ?? $row['Type'], + 'typeVersion' => $row['PLUGIN_TYPE_VERSION'] ?? null, + 'library' => $row['PLUGIN_LIBRARY'] ?? $row['Library'] ?? null, + 'libraryVersion' => $row['PLUGIN_LIBRARY_VERSION'] ?? null, + 'author' => $row['PLUGIN_AUTHOR'] ?? null, + 'description' => $row['PLUGIN_DESCRIPTION'] ?? null, + 'license' => $row['PLUGIN_LICENSE'] ?? $row['License'], + 'loadOption' => $row['LOAD_OPTION'] ?? null, + 'maturity' => $row['PLUGIN_MATURITY'] ?? null, + 'authVersion' => $row['PLUGIN_AUTH_VERSION'] ?? null, + ]); + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Server/Privileges.php b/srcs/phpmyadmin/libraries/classes/Server/Privileges.php new file mode 100644 index 0000000..1e50fbb --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Server/Privileges.php @@ -0,0 +1,5649 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * set of functions with the Privileges section in pma + * + * @package PhpMyAdmin + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Server; + +use PhpMyAdmin\Core; +use PhpMyAdmin\DatabaseInterface; +use PhpMyAdmin\Display\ChangePassword; +use PhpMyAdmin\Message; +use PhpMyAdmin\Relation; +use PhpMyAdmin\RelationCleanup; +use PhpMyAdmin\Response; +use PhpMyAdmin\Template; +use PhpMyAdmin\Url; +use PhpMyAdmin\Util; + +/** + * Privileges class + * + * @package PhpMyAdmin + */ +class Privileges +{ + /** + * @var Template + */ + public $template; + + /** + * @var RelationCleanup + */ + private $relationCleanup; + + /** + * @var DatabaseInterface + */ + public $dbi; + + /** + * @var Relation + */ + public $relation; + + /** + * Privileges constructor. + * + * @param Template $template Template object + * @param DatabaseInterface $dbi DatabaseInterface object + * @param Relation $relation Relation object + * @param RelationCleanup $relationCleanup RelationCleanup object + */ + public function __construct( + Template $template, + $dbi, + Relation $relation, + RelationCleanup $relationCleanup + ) { + $this->template = $template; + $this->dbi = $dbi; + $this->relation = $relation; + $this->relationCleanup = $relationCleanup; + } + + /** + * Get Html for User Group Dialog + * + * @param string $username username + * @param bool $is_menuswork Is menuswork set in configuration + * + * @return string html + */ + public function getHtmlForUserGroupDialog($username, $is_menuswork) + { + $html = ''; + if (! empty($_GET['edit_user_group_dialog']) && $is_menuswork) { + $dialog = $this->getHtmlToChooseUserGroup($username); + $response = Response::getInstance(); + if ($response->isAjax()) { + $response->addJSON('message', $dialog); + exit; + } else { + $html .= $dialog; + } + } + + return $html; + } + + /** + * Escapes wildcard in a database+table specification + * before using it in a GRANT statement. + * + * Escaping a wildcard character in a GRANT is only accepted at the global + * or database level, not at table level; this is why I remove + * the escaping character. Internally, in mysql.tables_priv.Db there are + * no escaping (for example test_db) but in mysql.db you'll see test\_db + * for a db-specific privilege. + * + * @param string $dbname Database name + * @param string $tablename Table name + * + * @return string the escaped (if necessary) database.table + */ + public function wildcardEscapeForGrant($dbname, $tablename) + { + if (strlen($dbname) === 0) { + $db_and_table = '*.*'; + } else { + if (strlen($tablename) > 0) { + $db_and_table = Util::backquote( + Util::unescapeMysqlWildcards($dbname) + ) + . '.' . Util::backquote($tablename); + } else { + $db_and_table = Util::backquote($dbname) . '.*'; + } + } + return $db_and_table; + } + + /** + * Generates a condition on the user name + * + * @param string $initial the user's initial + * + * @return string the generated condition + */ + public function rangeOfUsers($initial = '') + { + // strtolower() is used because the User field + // might be BINARY, so LIKE would be case sensitive + if ($initial === null || $initial === '') { + return ''; + } + + $ret = " WHERE `User` LIKE '" + . $this->dbi->escapeString($initial) . "%'" + . " OR `User` LIKE '" + . $this->dbi->escapeString(mb_strtolower($initial)) + . "%'"; + return $ret; + } // end function + + /** + * Formats privilege name for a display + * + * @param array $privilege Privilege information + * @param boolean $html Whether to use HTML + * + * @return string + */ + public function formatPrivilege(array $privilege, $html) + { + if ($html) { + return '<dfn title="' . $privilege[2] . '">' + . $privilege[1] . '</dfn>'; + } + + return $privilege[1]; + } + + /** + * Parses privileges into an array, it modifies the array + * + * @param array $row Results row from + * + * @return void + */ + public function fillInTablePrivileges(array &$row) + { + $row1 = $this->dbi->fetchSingleRow( + 'SHOW COLUMNS FROM `mysql`.`tables_priv` LIKE \'Table_priv\';', + 'ASSOC' + ); + // note: in MySQL 5.0.3 we get "Create View', 'Show view'; + // the View for Create is spelled with uppercase V + // the view for Show is spelled with lowercase v + // and there is a space between the words + + $av_grants = explode( + '\',\'', + mb_substr( + $row1['Type'], + mb_strpos($row1['Type'], '(') + 2, + mb_strpos($row1['Type'], ')') + - mb_strpos($row1['Type'], '(') - 3 + ) + ); + + $users_grants = explode(',', $row['Table_priv']); + + foreach ($av_grants as $current_grant) { + $row[$current_grant . '_priv'] + = in_array($current_grant, $users_grants) ? 'Y' : 'N'; + } + unset($row['Table_priv']); + } + + + /** + * Extracts the privilege information of a priv table row + * + * @param array|null $row the row + * @param boolean $enableHTML add <dfn> tag with tooltips + * @param boolean $tablePrivs whether row contains table privileges + * + * @global resource $user_link the database connection + * + * @return array + */ + public function extractPrivInfo($row = null, $enableHTML = false, $tablePrivs = false) + { + if ($tablePrivs) { + $grants = $this->getTableGrantsArray(); + } else { + $grants = $this->getGrantsArray(); + } + + if ($row !== null && isset($row['Table_priv'])) { + $this->fillInTablePrivileges($row); + } + + $privs = []; + $allPrivileges = true; + foreach ($grants as $current_grant) { + if (($row !== null && isset($row[$current_grant[0]])) + || ($row === null && isset($GLOBALS[$current_grant[0]])) + ) { + if (($row !== null && $row[$current_grant[0]] == 'Y') + || ($row === null + && ($GLOBALS[$current_grant[0]] == 'Y' + || (is_array($GLOBALS[$current_grant[0]]) + && count($GLOBALS[$current_grant[0]]) == $_REQUEST['column_count'] + && empty($GLOBALS[$current_grant[0] . '_none'])))) + ) { + $privs[] = $this->formatPrivilege($current_grant, $enableHTML); + } elseif (! empty($GLOBALS[$current_grant[0]]) + && is_array($GLOBALS[$current_grant[0]]) + && empty($GLOBALS[$current_grant[0] . '_none']) + ) { + // Required for proper escaping of ` (backtick) in a column name + $grant_cols = array_map( + function ($val) { + return Util::backquote($val); + }, + $GLOBALS[$current_grant[0]] + ); + + $privs[] = $this->formatPrivilege($current_grant, $enableHTML) + . ' (' . implode(', ', $grant_cols) . ')'; + } else { + $allPrivileges = false; + } + } + } + if (empty($privs)) { + if ($enableHTML) { + $privs[] = '<dfn title="' . __('No privileges.') . '">USAGE</dfn>'; + } else { + $privs[] = 'USAGE'; + } + } elseif ($allPrivileges + && (! isset($_POST['grant_count']) || count($privs) == $_POST['grant_count']) + ) { + if ($enableHTML) { + $privs = ['<dfn title="' + . __('Includes all privileges except GRANT.') + . '">ALL PRIVILEGES</dfn>', + ]; + } else { + $privs = ['ALL PRIVILEGES']; + } + } + return $privs; + } + + /** + * Returns an array of table grants and their descriptions + * + * @return array array of table grants + */ + public function getTableGrantsArray() + { + return [ + [ + 'Delete', + 'DELETE', + $GLOBALS['strPrivDescDelete'], + ], + [ + 'Create', + 'CREATE', + $GLOBALS['strPrivDescCreateTbl'], + ], + [ + 'Drop', + 'DROP', + $GLOBALS['strPrivDescDropTbl'], + ], + [ + 'Index', + 'INDEX', + $GLOBALS['strPrivDescIndex'], + ], + [ + 'Alter', + 'ALTER', + $GLOBALS['strPrivDescAlter'], + ], + [ + 'Create View', + 'CREATE_VIEW', + $GLOBALS['strPrivDescCreateView'], + ], + [ + 'Show view', + 'SHOW_VIEW', + $GLOBALS['strPrivDescShowView'], + ], + [ + 'Trigger', + 'TRIGGER', + $GLOBALS['strPrivDescTrigger'], + ], + ]; + } + + /** + * Get the grants array which contains all the privilege types + * and relevant grant messages + * + * @return array + */ + public function getGrantsArray() + { + return [ + [ + 'Select_priv', + 'SELECT', + __('Allows reading data.'), + ], + [ + 'Insert_priv', + 'INSERT', + __('Allows inserting and replacing data.'), + ], + [ + 'Update_priv', + 'UPDATE', + __('Allows changing data.'), + ], + [ + 'Delete_priv', + 'DELETE', + __('Allows deleting data.'), + ], + [ + 'Create_priv', + 'CREATE', + __('Allows creating new databases and tables.'), + ], + [ + 'Drop_priv', + 'DROP', + __('Allows dropping databases and tables.'), + ], + [ + 'Reload_priv', + 'RELOAD', + __('Allows reloading server settings and flushing the server\'s caches.'), + ], + [ + 'Shutdown_priv', + 'SHUTDOWN', + __('Allows shutting down the server.'), + ], + [ + 'Process_priv', + 'PROCESS', + __('Allows viewing processes of all users.'), + ], + [ + 'File_priv', + 'FILE', + __('Allows importing data from and exporting data into files.'), + ], + [ + 'References_priv', + 'REFERENCES', + __('Has no effect in this MySQL version.'), + ], + [ + 'Index_priv', + 'INDEX', + __('Allows creating and dropping indexes.'), + ], + [ + 'Alter_priv', + 'ALTER', + __('Allows altering the structure of existing tables.'), + ], + [ + 'Show_db_priv', + 'SHOW DATABASES', + __('Gives access to the complete list of databases.'), + ], + [ + 'Super_priv', + 'SUPER', + __( + 'Allows connecting, even if maximum number of connections ' + . 'is reached; required for most administrative operations ' + . 'like setting global variables or killing threads of other users.' + ), + ], + [ + 'Create_tmp_table_priv', + 'CREATE TEMPORARY TABLES', + __('Allows creating temporary tables.'), + ], + [ + 'Lock_tables_priv', + 'LOCK TABLES', + __('Allows locking tables for the current thread.'), + ], + [ + 'Repl_slave_priv', + 'REPLICATION SLAVE', + __('Needed for the replication slaves.'), + ], + [ + 'Repl_client_priv', + 'REPLICATION CLIENT', + __('Allows the user to ask where the slaves / masters are.'), + ], + [ + 'Create_view_priv', + 'CREATE VIEW', + __('Allows creating new views.'), + ], + [ + 'Event_priv', + 'EVENT', + __('Allows to set up events for the event scheduler.'), + ], + [ + 'Trigger_priv', + 'TRIGGER', + __('Allows creating and dropping triggers.'), + ], + // for table privs: + [ + 'Create View_priv', + 'CREATE VIEW', + __('Allows creating new views.'), + ], + [ + 'Show_view_priv', + 'SHOW VIEW', + __('Allows performing SHOW CREATE VIEW queries.'), + ], + // for table privs: + [ + 'Show view_priv', + 'SHOW VIEW', + __('Allows performing SHOW CREATE VIEW queries.'), + ], + [ + 'Delete_history_priv', + 'DELETE HISTORY', + $GLOBALS['strPrivDescDeleteHistoricalRows'], + ], + [ + 'Delete versioning rows_priv', + 'DELETE HISTORY', + $GLOBALS['strPrivDescDeleteHistoricalRows'], + ], + [ + 'Create_routine_priv', + 'CREATE ROUTINE', + __('Allows creating stored routines.'), + ], + [ + 'Alter_routine_priv', + 'ALTER ROUTINE', + __('Allows altering and dropping stored routines.'), + ], + [ + 'Create_user_priv', + 'CREATE USER', + __('Allows creating, dropping and renaming user accounts.'), + ], + [ + 'Execute_priv', + 'EXECUTE', + __('Allows executing stored routines.'), + ], + ]; + } + + /** + * Displays on which column(s) a table-specific privilege is granted + * + * @param array $columns columns array + * @param array $row first row from result or boolean false + * @param string $name_for_select privilege types - Select_priv, Insert_priv + * Update_priv, References_priv + * @param string $priv_for_header privilege for header + * @param string $name privilege name: insert, select, update, references + * @param string $name_for_dfn name for dfn + * @param string $name_for_current name for current + * + * @return string html snippet + */ + public function getHtmlForColumnPrivileges( + array $columns, + array $row, + $name_for_select, + $priv_for_header, + $name, + $name_for_dfn, + $name_for_current + ) { + return $this->template->render('server/privileges/column_privileges', [ + 'columns' => $columns, + 'row' => $row, + 'name_for_select' => $name_for_select, + 'priv_for_header' => $priv_for_header, + 'name' => $name, + 'name_for_dfn' => $name_for_dfn, + 'name_for_current' => $name_for_current, + ]); + } + + /** + * Get sql query for display privileges table + * + * @param string $db the database + * @param string $table the table + * @param string $username username for database connection + * @param string $hostname hostname for database connection + * + * @return string sql query + */ + public function getSqlQueryForDisplayPrivTable($db, $table, $username, $hostname) + { + if ($db == '*') { + return "SELECT * FROM `mysql`.`user`" + . " WHERE `User` = '" . $this->dbi->escapeString($username) . "'" + . " AND `Host` = '" . $this->dbi->escapeString($hostname) . "';"; + } elseif ($table == '*') { + return "SELECT * FROM `mysql`.`db`" + . " WHERE `User` = '" . $this->dbi->escapeString($username) . "'" + . " AND `Host` = '" . $this->dbi->escapeString($hostname) . "'" + . " AND '" . $this->dbi->escapeString(Util::unescapeMysqlWildcards($db)) . "'" + . " LIKE `Db`;"; + } + return "SELECT `Table_priv`" + . " FROM `mysql`.`tables_priv`" + . " WHERE `User` = '" . $this->dbi->escapeString($username) . "'" + . " AND `Host` = '" . $this->dbi->escapeString($hostname) . "'" + . " AND `Db` = '" . $this->dbi->escapeString(Util::unescapeMysqlWildcards($db)) . "'" + . " AND `Table_name` = '" . $this->dbi->escapeString($table) . "';"; + } + + /** + * Displays a dropdown to select the user group + * with menu items configured to each of them. + * + * @param string $username username + * + * @return string html to select the user group + */ + public function getHtmlToChooseUserGroup($username) + { + $cfgRelation = $this->relation->getRelationsParam(); + $groupTable = Util::backquote($cfgRelation['db']) + . "." . Util::backquote($cfgRelation['usergroups']); + $userTable = Util::backquote($cfgRelation['db']) + . "." . Util::backquote($cfgRelation['users']); + + $userGroup = ''; + if (isset($GLOBALS['username'])) { + $sql_query = "SELECT `usergroup` FROM " . $userTable + . " WHERE `username` = '" . $this->dbi->escapeString($username) . "'"; + $userGroup = $this->dbi->fetchValue( + $sql_query, + 0, + 0, + DatabaseInterface::CONNECT_CONTROL + ); + } + + $allUserGroups = ['' => '']; + $sql_query = "SELECT DISTINCT `usergroup` FROM " . $groupTable; + $result = $this->relation->queryAsControlUser($sql_query, false); + if ($result) { + while ($row = $this->dbi->fetchRow($result)) { + $allUserGroups[$row[0]] = $row[0]; + } + } + $this->dbi->freeResult($result); + + return $this->template->render('server/privileges/choose_user_group', [ + 'all_user_groups' => $allUserGroups, + 'user_group' => $userGroup, + 'params' => ['username' => $username], + ]); + } + + /** + * Sets the user group from request values + * + * @param string $username username + * @param string $userGroup user group to set + * + * @return void + */ + public function setUserGroup($username, $userGroup) + { + $userGroup = $userGroup === null ? '' : $userGroup; + $cfgRelation = $this->relation->getRelationsParam(); + if (empty($cfgRelation['db']) || empty($cfgRelation['users']) || empty($cfgRelation['usergroups'])) { + return; + } + + $userTable = Util::backquote($cfgRelation['db']) + . "." . Util::backquote($cfgRelation['users']); + + $sql_query = "SELECT `usergroup` FROM " . $userTable + . " WHERE `username` = '" . $this->dbi->escapeString($username) . "'"; + $oldUserGroup = $this->dbi->fetchValue( + $sql_query, + 0, + 0, + DatabaseInterface::CONNECT_CONTROL + ); + + if ($oldUserGroup === false) { + $upd_query = "INSERT INTO " . $userTable . "(`username`, `usergroup`)" + . " VALUES ('" . $this->dbi->escapeString($username) . "', " + . "'" . $this->dbi->escapeString($userGroup) . "')"; + } else { + if (empty($userGroup)) { + $upd_query = "DELETE FROM " . $userTable + . " WHERE `username`='" . $this->dbi->escapeString($username) . "'"; + } elseif ($oldUserGroup != $userGroup) { + $upd_query = "UPDATE " . $userTable + . " SET `usergroup`='" . $this->dbi->escapeString($userGroup) . "'" + . " WHERE `username`='" . $this->dbi->escapeString($username) . "'"; + } + } + if (isset($upd_query)) { + $this->relation->queryAsControlUser($upd_query); + } + } + + /** + * Displays the privileges form table + * + * @param string $db the database + * @param string $table the table + * @param boolean $submit whether to display the submit button or not + * + * @global array $cfg the phpMyAdmin configuration + * @global resource $user_link the database connection + * + * @return string html snippet + */ + public function getHtmlToDisplayPrivilegesTable( + $db = '*', + $table = '*', + $submit = true + ) { + $html_output = ''; + $sql_query = ''; + + if ($db == '*') { + $table = '*'; + } + $username = ''; + $hostname = ''; + if (isset($GLOBALS['username'])) { + $username = $GLOBALS['username']; + $hostname = $GLOBALS['hostname']; + $sql_query = $this->getSqlQueryForDisplayPrivTable( + $db, + $table, + $username, + $hostname + ); + $row = $this->dbi->fetchSingleRow($sql_query); + } + if (empty($row)) { + if ($table == '*' && $this->dbi->isSuperuser()) { + $row = []; + if ($db == '*') { + $sql_query = 'SHOW COLUMNS FROM `mysql`.`user`;'; + } elseif ($table == '*') { + $sql_query = 'SHOW COLUMNS FROM `mysql`.`db`;'; + } + $res = $this->dbi->query($sql_query); + while ($row1 = $this->dbi->fetchRow($res)) { + if (mb_substr($row1[0], 0, 4) == 'max_') { + $row[$row1[0]] = 0; + } elseif (mb_substr($row1[0], 0, 5) == 'x509_' + || mb_substr($row1[0], 0, 4) == 'ssl_' + ) { + $row[$row1[0]] = ''; + } else { + $row[$row1[0]] = 'N'; + } + } + $this->dbi->freeResult($res); + } elseif ($table == '*') { + $row = []; + } else { + $row = ['Table_priv' => '']; + } + } + if (isset($row['Table_priv'])) { + $this->fillInTablePrivileges($row); + + // get columns + $res = $this->dbi->tryQuery( + 'SHOW COLUMNS FROM ' + . Util::backquote( + Util::unescapeMysqlWildcards($db) + ) + . '.' . Util::backquote($table) . ';' + ); + $columns = []; + if ($res) { + while ($row1 = $this->dbi->fetchRow($res)) { + $columns[$row1[0]] = [ + 'Select' => false, + 'Insert' => false, + 'Update' => false, + 'References' => false, + ]; + } + $this->dbi->freeResult($res); + } + unset($res, $row1); + } + // table-specific privileges + if (! empty($columns)) { + $html_output .= $this->getHtmlForTableSpecificPrivileges( + $username, + $hostname, + $db, + $table, + $columns, + $row + ); + } else { + // global or db-specific + $html_output .= $this->getHtmlForGlobalOrDbSpecificPrivs($db, $table, $row); + } + $html_output .= '</fieldset>' . "\n"; + if ($submit) { + $html_output .= '<fieldset id="fieldset_user_privtable_footer" ' + . 'class="tblFooters">' . "\n" + . '<input type="hidden" name="update_privs" value="1">' . "\n" + . '<input class="btn btn-primary" type="submit" value="' . __('Go') . '">' . "\n" + . '</fieldset>' . "\n"; + } + return $html_output; + } // end of the 'PMA_displayPrivTable()' function + + /** + * Get HTML for "Require" + * + * @param array $row privilege array + * + * @return string html snippet + */ + public function getHtmlForRequires(array $row) + { + $specified = (isset($row['ssl_type']) && $row['ssl_type'] == 'SPECIFIED'); + $require_options = [ + [ + 'name' => 'ssl_type', + 'value' => 'NONE', + 'description' => __( + 'Does not require SSL-encrypted connections.' + ), + 'label' => 'REQUIRE NONE', + 'checked' => isset($row['ssl_type']) + && ($row['ssl_type'] == 'NONE' + || $row['ssl_type'] == '') + ? 'checked="checked"' + : '', + 'disabled' => false, + 'radio' => true, + ], + [ + 'name' => 'ssl_type', + 'value' => 'ANY', + 'description' => __( + 'Requires SSL-encrypted connections.' + ), + 'label' => 'REQUIRE SSL', + 'checked' => isset($row['ssl_type']) && ($row['ssl_type'] == 'ANY') + ? 'checked="checked"' + : '', + 'disabled' => false, + 'radio' => true, + ], + [ + 'name' => 'ssl_type', + 'value' => 'X509', + 'description' => __( + 'Requires a valid X509 certificate.' + ), + 'label' => 'REQUIRE X509', + 'checked' => isset($row['ssl_type']) && ($row['ssl_type'] == 'X509') + ? 'checked="checked"' + : '', + 'disabled' => false, + 'radio' => true, + ], + [ + 'name' => 'ssl_type', + 'value' => 'SPECIFIED', + 'description' => '', + 'label' => 'SPECIFIED', + 'checked' => $specified ? 'checked="checked"' : '', + 'disabled' => false, + 'radio' => true, + ], + [ + 'name' => 'ssl_cipher', + 'value' => isset($row['ssl_cipher']) + ? htmlspecialchars($row['ssl_cipher']) : '', + 'description' => __( + 'Requires that a specific cipher method be used for a connection.' + ), + 'label' => 'REQUIRE CIPHER', + 'checked' => '', + 'disabled' => ! $specified, + 'radio' => false, + ], + [ + 'name' => 'x509_issuer', + 'value' => isset($row['x509_issuer']) + ? htmlspecialchars($row['x509_issuer']) : '', + 'description' => __( + 'Requires that a valid X509 certificate issued by this CA be presented.' + ), + 'label' => 'REQUIRE ISSUER', + 'checked' => '', + 'disabled' => ! $specified, + 'radio' => false, + ], + [ + 'name' => 'x509_subject', + 'value' => isset($row['x509_subject']) + ? htmlspecialchars($row['x509_subject']) : '', + 'description' => __( + 'Requires that a valid X509 certificate with this subject be presented.' + ), + 'label' => 'REQUIRE SUBJECT', + 'checked' => '', + 'disabled' => ! $specified, + 'radio' => false, + ], + ]; + + return $this->template->render('server/privileges/require_options', [ + 'require_options' => $require_options, + ]); + } + + /** + * Get HTML for "Resource limits" + * + * @param array $row first row from result or boolean false + * + * @return string html snippet + */ + public function getHtmlForResourceLimits(array $row) + { + $limits = [ + [ + 'input_name' => 'max_questions', + 'name_main' => 'MAX QUERIES PER HOUR', + 'value' => isset($row['max_questions']) ? $row['max_questions'] : '0', + 'description' => __( + 'Limits the number of queries the user may send to the server per hour.' + ), + ], + [ + 'input_name' => 'max_updates', + 'name_main' => 'MAX UPDATES PER HOUR', + 'value' => isset($row['max_updates']) ? $row['max_updates'] : '0', + 'description' => __( + 'Limits the number of commands that change any table ' + . 'or database the user may execute per hour.' + ), + ], + [ + 'input_name' => 'max_connections', + 'name_main' => 'MAX CONNECTIONS PER HOUR', + 'value' => isset($row['max_connections']) ? $row['max_connections'] : '0', + 'description' => __( + 'Limits the number of new connections the user may open per hour.' + ), + ], + [ + 'input_name' => 'max_user_connections', + 'name_main' => 'MAX USER_CONNECTIONS', + 'value' => isset($row['max_user_connections']) ? + $row['max_user_connections'] : '0', + 'description' => __( + 'Limits the number of simultaneous connections ' + . 'the user may have.' + ), + ], + ]; + + return $this->template->render('server/privileges/resource_limits', [ + 'limits' => $limits, + ]); + } + + /** + * Get the HTML snippet for routine specific privileges + * + * @param string $username username for database connection + * @param string $hostname hostname for database connection + * @param string $db the database + * @param string $routine the routine + * @param string $url_dbname url encoded db name + * + * @return string + */ + public function getHtmlForRoutineSpecificPrivileges( + $username, + $hostname, + $db, + $routine, + $url_dbname + ) { + $header = $this->getHtmlHeaderForUserProperties( + false, + $url_dbname, + $db, + $username, + $hostname, + $routine, + 'routine' + ); + + $sql = "SELECT `Proc_priv`" + . " FROM `mysql`.`procs_priv`" + . " WHERE `User` = '" . $this->dbi->escapeString($username) . "'" + . " AND `Host` = '" . $this->dbi->escapeString($hostname) . "'" + . " AND `Db` = '" + . $this->dbi->escapeString(Util::unescapeMysqlWildcards($db)) . "'" + . " AND `Routine_name` LIKE '" . $this->dbi->escapeString($routine) . "';"; + $res = $this->dbi->fetchValue($sql); + + $privs = $this->parseProcPriv($res); + + $routineArray = [$this->getTriggerPrivilegeTable()]; + $privTableNames = [__('Routine')]; + $privCheckboxes = $this->getHtmlForGlobalPrivTableWithCheckboxes( + $routineArray, + $privTableNames, + $privs + ); + + return $this->template->render('server/privileges/edit_routine_privileges', [ + 'username' => $username, + 'hostname' => $hostname, + 'database' => $db, + 'routine' => $routine, + 'grant_count' => count($privs), + 'priv_checkboxes' => $privCheckboxes, + 'header' => $header, + ]); + } + + /** + * Get routine privilege table as an array + * + * @return array privilege type array + */ + public function getTriggerPrivilegeTable() + { + $routinePrivTable = [ + [ + 'Grant', + 'GRANT', + __( + 'Allows user to give to other users or remove from other users ' + . 'privileges that user possess on this routine.' + ), + ], + [ + 'Alter_routine', + 'ALTER ROUTINE', + __('Allows altering and dropping this routine.'), + ], + [ + 'Execute', + 'EXECUTE', + __('Allows executing this routine.'), + ], + ]; + return $routinePrivTable; + } + + /** + * Get the HTML snippet for table specific privileges + * + * @param string $username username for database connection + * @param string $hostname hostname for database connection + * @param string $db the database + * @param string $table the table + * @param array $columns columns array + * @param array $row current privileges row + * + * @return string + */ + public function getHtmlForTableSpecificPrivileges( + $username, + $hostname, + $db, + $table, + array $columns, + array $row + ) { + $res = $this->dbi->query( + 'SELECT `Column_name`, `Column_priv`' + . ' FROM `mysql`.`columns_priv`' + . ' WHERE `User`' + . ' = \'' . $this->dbi->escapeString($username) . "'" + . ' AND `Host`' + . ' = \'' . $this->dbi->escapeString($hostname) . "'" + . ' AND `Db`' + . ' = \'' . $this->dbi->escapeString( + Util::unescapeMysqlWildcards($db) + ) . "'" + . ' AND `Table_name`' + . ' = \'' . $this->dbi->escapeString($table) . '\';' + ); + + while ($row1 = $this->dbi->fetchRow($res)) { + $row1[1] = explode(',', $row1[1]); + foreach ($row1[1] as $current) { + $columns[$row1[0]][$current] = true; + } + } + $this->dbi->freeResult($res); + unset($res, $row1, $current); + + $html_output = '<input type="hidden" name="grant_count" ' + . 'value="' . count($row) . '">' . "\n" + . '<input type="hidden" name="column_count" ' + . 'value="' . count($columns) . '">' . "\n" + . '<fieldset id="fieldset_user_priv">' . "\n" + . '<legend data-submenu-label="' . __('Table') . '">' . __('Table-specific privileges') + . '</legend>' + . '<p><small><i>' + . __('Note: MySQL privilege names are expressed in English.') + . '</i></small></p>'; + + // privs that are attached to a specific column + $html_output .= $this->getHtmlForAttachedPrivilegesToTableSpecificColumn( + $columns, + $row + ); + + // privs that are not attached to a specific column + $html_output .= '<div class="item">' . "\n" + . $this->getHtmlForNotAttachedPrivilegesToTableSpecificColumn($row) + . '</div>' . "\n"; + + // for Safari 2.0.2 + $html_output .= '<div class="clearfloat"></div>' . "\n"; + + return $html_output; + } + + /** + * Get HTML snippet for privileges that are attached to a specific column + * + * @param array $columns columns array + * @param array $row first row from result or boolean false + * + * @return string + */ + public function getHtmlForAttachedPrivilegesToTableSpecificColumn(array $columns, array $row) + { + $html_output = $this->getHtmlForColumnPrivileges( + $columns, + $row, + 'Select_priv', + 'SELECT', + 'select', + __('Allows reading data.'), + 'Select' + ); + + $html_output .= $this->getHtmlForColumnPrivileges( + $columns, + $row, + 'Insert_priv', + 'INSERT', + 'insert', + __('Allows inserting and replacing data.'), + 'Insert' + ); + + $html_output .= $this->getHtmlForColumnPrivileges( + $columns, + $row, + 'Update_priv', + 'UPDATE', + 'update', + __('Allows changing data.'), + 'Update' + ); + + $html_output .= $this->getHtmlForColumnPrivileges( + $columns, + $row, + 'References_priv', + 'REFERENCES', + 'references', + __('Has no effect in this MySQL version.'), + 'References' + ); + return $html_output; + } + + /** + * Get HTML for privileges that are not attached to a specific column + * + * @param array $row first row from result or boolean false + * + * @return string + */ + public function getHtmlForNotAttachedPrivilegesToTableSpecificColumn(array $row) + { + $html_output = ''; + + foreach ($row as $current_grant => $current_grant_value) { + $grant_type = substr($current_grant, 0, -5); + if (in_array($grant_type, ['Select', 'Insert', 'Update', 'References']) + ) { + continue; + } + // make a substitution to match the messages variables; + // also we must substitute the grant we get, because we can't generate + // a form variable containing blanks (those would get changed to + // an underscore when receiving the POST) + if ($current_grant == 'Create View_priv') { + $tmp_current_grant = 'CreateView_priv'; + $current_grant = 'Create_view_priv'; + } elseif ($current_grant == 'Show view_priv') { + $tmp_current_grant = 'ShowView_priv'; + $current_grant = 'Show_view_priv'; + } elseif ($current_grant == 'Delete versioning rows_priv') { + $tmp_current_grant = 'DeleteHistoricalRows_priv'; + $current_grant = 'Delete_history_priv'; + } else { + $tmp_current_grant = $current_grant; + } + + $html_output .= '<div class="item">' . "\n" + . '<input type="checkbox"' + . ' name="' . $current_grant . '" id="checkbox_' . $current_grant + . '" value="Y" ' + . ($current_grant_value == 'Y' ? 'checked="checked" ' : '') + . 'title="'; + + $privGlobalName = 'strPrivDesc' + . mb_substr( + $tmp_current_grant, + 0, + mb_strlen($tmp_current_grant) - 5 + ); + $html_output .= (isset($GLOBALS[$privGlobalName]) + ? $GLOBALS[$privGlobalName] + : $GLOBALS[$privGlobalName . 'Tbl'] + ) + . '">' . "\n"; + + $privGlobalName1 = 'strPrivDesc' + . mb_substr( + $tmp_current_grant, + 0, + - 5 + ); + $html_output .= '<label for="checkbox_' . $current_grant + . '"><code><dfn title="' + . (isset($GLOBALS[$privGlobalName1]) + ? $GLOBALS[$privGlobalName1] + : $GLOBALS[$privGlobalName1 . 'Tbl'] + ) + . '">' + . mb_strtoupper( + mb_substr( + $current_grant, + 0, + -5 + ) + ) + . '</dfn></code></label>' . "\n" + . '</div>' . "\n"; + } // end foreach () + return $html_output; + } + + /** + * Get HTML for global or database specific privileges + * + * @param string $db the database + * @param string $table the table + * @param array $row first row from result or boolean false + * + * @return string + */ + public function getHtmlForGlobalOrDbSpecificPrivs($db, $table, array $row) + { + $privTable_names = [ + 0 => __('Data'), + 1 => __('Structure'), + 2 => __('Administration'), + ]; + $privTable = []; + $privTable[0] = $this->getDataPrivilegeTable($db); + $privTable[1] = $this->getStructurePrivilegeTable($table, $row); + $privTable[2] = $this->getAdministrationPrivilegeTable($db); + + $html_output = '<input type="hidden" name="grant_count" value="' + . (count($privTable[0]) + + count($privTable[1]) + + count($privTable[2]) + - (isset($row['Grant_priv']) ? 1 : 0) + ) + . '">'; + if ($db == '*') { + $legend = __('Global privileges'); + $menu_label = __('Global'); + } elseif ($table == '*') { + $legend = __('Database-specific privileges'); + $menu_label = __('Database'); + } else { + $legend = __('Table-specific privileges'); + $menu_label = __('Table'); + } + $html_output .= '<fieldset id="fieldset_user_global_rights">' + . '<legend data-submenu-label="' . $menu_label . '">' . $legend + . '<input type="checkbox" id="addUsersForm_checkall" ' + . 'class="checkall_box" title="' . __('Check all') . '"> ' + . '<label for="addUsersForm_checkall">' . __('Check all') . '</label> ' + . '</legend>' + . '<p><small><i>' + . __('Note: MySQL privilege names are expressed in English.') + . '</i></small></p>'; + + // Output the Global privilege tables with checkboxes + $html_output .= $this->getHtmlForGlobalPrivTableWithCheckboxes( + $privTable, + $privTable_names, + $row + ); + + // The "Resource limits" box is not displayed for db-specific privs + if ($db == '*') { + $html_output .= $this->getHtmlForResourceLimits($row); + $html_output .= $this->getHtmlForRequires($row); + } + // for Safari 2.0.2 + $html_output .= '<div class="clearfloat"></div>'; + + return $html_output; + } + + /** + * Get data privilege table as an array + * + * @param string $db the database + * + * @return array data privilege table + */ + public function getDataPrivilegeTable($db) + { + $data_privTable = [ + [ + 'Select', + 'SELECT', + __('Allows reading data.'), + ], + [ + 'Insert', + 'INSERT', + __('Allows inserting and replacing data.'), + ], + [ + 'Update', + 'UPDATE', + __('Allows changing data.'), + ], + [ + 'Delete', + 'DELETE', + __('Allows deleting data.'), + ], + ]; + if ($db == '*') { + $data_privTable[] + = [ + 'File', + 'FILE', + __('Allows importing data from and exporting data into files.'), + ]; + } + return $data_privTable; + } + + /** + * Get structure privilege table as an array + * + * @param string $table the table + * @param array $row first row from result or boolean false + * + * @return array structure privilege table + */ + public function getStructurePrivilegeTable($table, array $row) + { + $structure_privTable = [ + [ + 'Create', + 'CREATE', + $table == '*' + ? __('Allows creating new databases and tables.') + : __('Allows creating new tables.'), + ], + [ + 'Alter', + 'ALTER', + __('Allows altering the structure of existing tables.'), + ], + [ + 'Index', + 'INDEX', + __('Allows creating and dropping indexes.'), + ], + [ + 'Drop', + 'DROP', + $table == '*' + ? __('Allows dropping databases and tables.') + : __('Allows dropping tables.'), + ], + [ + 'Create_tmp_table', + 'CREATE TEMPORARY TABLES', + __('Allows creating temporary tables.'), + ], + [ + 'Show_view', + 'SHOW VIEW', + __('Allows performing SHOW CREATE VIEW queries.'), + ], + [ + 'Create_routine', + 'CREATE ROUTINE', + __('Allows creating stored routines.'), + ], + [ + 'Alter_routine', + 'ALTER ROUTINE', + __('Allows altering and dropping stored routines.'), + ], + [ + 'Execute', + 'EXECUTE', + __('Allows executing stored routines.'), + ], + ]; + // this one is for a db-specific priv: Create_view_priv + if (isset($row['Create_view_priv'])) { + $structure_privTable[] = [ + 'Create_view', + 'CREATE VIEW', + __('Allows creating new views.'), + ]; + } + // this one is for a table-specific priv: Create View_priv + if (isset($row['Create View_priv'])) { + $structure_privTable[] = [ + 'Create View', + 'CREATE VIEW', + __('Allows creating new views.'), + ]; + } + if (isset($row['Event_priv'])) { + // MySQL 5.1.6 + $structure_privTable[] = [ + 'Event', + 'EVENT', + __('Allows to set up events for the event scheduler.'), + ]; + $structure_privTable[] = [ + 'Trigger', + 'TRIGGER', + __('Allows creating and dropping triggers.'), + ]; + } + return $structure_privTable; + } + + /** + * Get administration privilege table as an array + * + * @param string $db the table + * + * @return array administration privilege table + */ + public function getAdministrationPrivilegeTable($db) + { + if ($db == '*') { + $adminPrivTable = [ + [ + 'Grant', + 'GRANT', + __( + 'Allows adding users and privileges ' + . 'without reloading the privilege tables.' + ), + ], + ]; + $adminPrivTable[] = [ + 'Super', + 'SUPER', + __( + 'Allows connecting, even if maximum number ' + . 'of connections is reached; required for ' + . 'most administrative operations like ' + . 'setting global variables or killing threads of other users.' + ), + ]; + $adminPrivTable[] = [ + 'Process', + 'PROCESS', + __('Allows viewing processes of all users.'), + ]; + $adminPrivTable[] = [ + 'Reload', + 'RELOAD', + __('Allows reloading server settings and flushing the server\'s caches.'), + ]; + $adminPrivTable[] = [ + 'Shutdown', + 'SHUTDOWN', + __('Allows shutting down the server.'), + ]; + $adminPrivTable[] = [ + 'Show_db', + 'SHOW DATABASES', + __('Gives access to the complete list of databases.'), + ]; + } else { + $adminPrivTable = [ + [ + 'Grant', + 'GRANT', + __( + 'Allows user to give to other users or remove from other' + . ' users the privileges that user possess yourself.' + ), + ], + ]; + } + $adminPrivTable[] = [ + 'Lock_tables', + 'LOCK TABLES', + __('Allows locking tables for the current thread.'), + ]; + $adminPrivTable[] = [ + 'References', + 'REFERENCES', + __('Has no effect in this MySQL version.'), + ]; + if ($db == '*') { + $adminPrivTable[] = [ + 'Repl_client', + 'REPLICATION CLIENT', + __('Allows the user to ask where the slaves / masters are.'), + ]; + $adminPrivTable[] = [ + 'Repl_slave', + 'REPLICATION SLAVE', + __('Needed for the replication slaves.'), + ]; + $adminPrivTable[] = [ + 'Create_user', + 'CREATE USER', + __('Allows creating, dropping and renaming user accounts.'), + ]; + } + return $adminPrivTable; + } + + /** + * Get HTML snippet for global privileges table with check boxes + * + * @param array $privTable privileges table array + * @param array $privTableNames names of the privilege tables + * (Data, Structure, Administration) + * @param array $row first row from result or boolean false + * + * @return string + */ + public function getHtmlForGlobalPrivTableWithCheckboxes( + array $privTable, + array $privTableNames, + array $row + ) { + return $this->template->render('server/privileges/global_priv_table', [ + 'priv_table' => $privTable, + 'priv_table_names' => $privTableNames, + 'row' => $row, + ]); + } + + /** + * Gets the currently active authentication plugins + * + * @param string $orig_auth_plugin Default Authentication plugin + * @param string $mode are we creating a new user or are we just + * changing one? + * (allowed values: 'new', 'edit', 'change_pw') + * @param string $versions Is MySQL version newer or older than 5.5.7 + * + * @return string + */ + public function getHtmlForAuthPluginsDropdown( + $orig_auth_plugin, + $mode = 'new', + $versions = 'new' + ) { + $select_id = 'select_authentication_plugin' + . ($mode == 'change_pw' ? '_cp' : ''); + + if ($versions == 'new') { + $active_auth_plugins = $this->getActiveAuthPlugins(); + + if (isset($active_auth_plugins['mysql_old_password'])) { + unset($active_auth_plugins['mysql_old_password']); + } + } else { + $active_auth_plugins = [ + 'mysql_native_password' => __('Native MySQL authentication'), + ]; + } + + $html_output = Util::getDropdown( + 'authentication_plugin', + $active_auth_plugins, + $orig_auth_plugin, + $select_id + ); + + return $html_output; + } + + /** + * Gets the currently active authentication plugins + * + * @return array array of plugin names and descriptions + */ + public function getActiveAuthPlugins() + { + $get_plugins_query = "SELECT `PLUGIN_NAME`, `PLUGIN_DESCRIPTION`" + . " FROM `information_schema`.`PLUGINS` " + . "WHERE `PLUGIN_TYPE` = 'AUTHENTICATION';"; + $resultset = $this->dbi->query($get_plugins_query); + + $result = []; + + while ($row = $this->dbi->fetchAssoc($resultset)) { + // if description is known, enable its translation + if ('mysql_native_password' == $row['PLUGIN_NAME']) { + $row['PLUGIN_DESCRIPTION'] = __('Native MySQL authentication'); + } elseif ('sha256_password' == $row['PLUGIN_NAME']) { + $row['PLUGIN_DESCRIPTION'] = __('SHA256 password authentication'); + } + + $result[$row['PLUGIN_NAME']] = $row['PLUGIN_DESCRIPTION']; + } + + return $result; + } + + /** + * Displays the fields used by the "new user" form as well as the + * "change login information / copy user" form. + * + * @param string $mode are we creating a new user or are we just + * changing one? (allowed values: 'new', 'change') + * @param string $username User name + * @param string $hostname Host name + * + * @global array $cfg the phpMyAdmin configuration + * @global resource $user_link the database connection + * + * @return string a HTML snippet + */ + public function getHtmlForLoginInformationFields( + $mode = 'new', + $username = null, + $hostname = null + ) { + list($username_length, $hostname_length) = $this->getUsernameAndHostnameLength(); + + if (isset($GLOBALS['username']) && strlen($GLOBALS['username']) === 0) { + $GLOBALS['pred_username'] = 'any'; + } + $html_output = '<fieldset id="fieldset_add_user_login">' . "\n" + . '<legend>' . __('Login Information') . '</legend>' . "\n" + . '<div class="item">' . "\n" + . '<label for="select_pred_username">' . "\n" + . ' ' . __('User name:') . "\n" + . '</label>' . "\n" + . '<span class="options">' . "\n"; + + $html_output .= '<select name="pred_username" id="select_pred_username" ' + . 'title="' . __('User name') . '">' . "\n"; + + $html_output .= '<option value="any"' + . (isset($GLOBALS['pred_username']) && $GLOBALS['pred_username'] == 'any' + ? ' selected="selected"' + : '') . '>' + . __('Any user') + . '</option>' . "\n"; + + $html_output .= '<option value="userdefined"' + . (! isset($GLOBALS['pred_username']) + || $GLOBALS['pred_username'] == 'userdefined' + ? ' selected="selected"' + : '') . '>' + . __('Use text field') + . ':</option>' . "\n"; + + $html_output .= '</select>' . "\n" + . '</span>' . "\n"; + + $html_output .= '<input type="text" name="username" id="pma_username" class="autofocus"' + . ' maxlength="' . $username_length . '" title="' . __('User name') . '"' + . (empty($GLOBALS['username']) + ? '' + : ' value="' . htmlspecialchars( + isset($GLOBALS['new_username']) + ? $GLOBALS['new_username'] + : $GLOBALS['username'] + ) . '"' + ) + . (! isset($GLOBALS['pred_username']) + || $GLOBALS['pred_username'] == 'userdefined' + ? 'required="required"' + : '') . '>' . "\n"; + + $html_output .= '<div id="user_exists_warning"' + . ' name="user_exists_warning" class="hide">' + . Message::notice( + __( + 'An account already exists with the same username ' + . 'but possibly a different hostname.' + ) + )->getDisplay() + . '</div>'; + $html_output .= '</div>'; + + $html_output .= '<div class="item">' . "\n" + . '<label for="select_pred_hostname">' . "\n" + . ' ' . __('Host name:') . "\n" + . '</label>' . "\n"; + + $html_output .= '<span class="options">' . "\n" + . ' <select name="pred_hostname" id="select_pred_hostname" ' + . 'title="' . __('Host name') . '"' . "\n"; + $_current_user = $this->dbi->fetchValue('SELECT USER();'); + if (! empty($_current_user)) { + $thishost = str_replace( + "'", + '', + mb_substr( + $_current_user, + mb_strrpos($_current_user, '@') + 1 + ) + ); + if ($thishost != 'localhost' && $thishost != '127.0.0.1') { + $html_output .= ' data-thishost="' . htmlspecialchars($thishost) . '" '; + } else { + unset($thishost); + } + } + $html_output .= '>' . "\n"; + unset($_current_user); + + // when we start editing a user, $GLOBALS['pred_hostname'] is not defined + if (! isset($GLOBALS['pred_hostname']) && isset($GLOBALS['hostname'])) { + switch (mb_strtolower($GLOBALS['hostname'])) { + case 'localhost': + case '127.0.0.1': + $GLOBALS['pred_hostname'] = 'localhost'; + break; + case '%': + $GLOBALS['pred_hostname'] = 'any'; + break; + default: + $GLOBALS['pred_hostname'] = 'userdefined'; + break; + } + } + $html_output .= '<option value="any"' + . (isset($GLOBALS['pred_hostname']) + && $GLOBALS['pred_hostname'] == 'any' + ? ' selected="selected"' + : '') . '>' + . __('Any host') + . '</option>' . "\n" + . '<option value="localhost"' + . (isset($GLOBALS['pred_hostname']) + && $GLOBALS['pred_hostname'] == 'localhost' + ? ' selected="selected"' + : '') . '>' + . __('Local') + . '</option>' . "\n"; + if (! empty($thishost)) { + $html_output .= '<option value="thishost"' + . (isset($GLOBALS['pred_hostname']) + && $GLOBALS['pred_hostname'] == 'thishost' + ? ' selected="selected"' + : '') . '>' + . __('This Host') + . '</option>' . "\n"; + } + unset($thishost); + $html_output .= '<option value="hosttable"' + . (isset($GLOBALS['pred_hostname']) + && $GLOBALS['pred_hostname'] == 'hosttable' + ? ' selected="selected"' + : '') . '>' + . __('Use Host Table') + . '</option>' . "\n"; + + $html_output .= '<option value="userdefined"' + . (isset($GLOBALS['pred_hostname']) + && $GLOBALS['pred_hostname'] == 'userdefined' + ? ' selected="selected"' + : '') . '>' + . __('Use text field:') . '</option>' . "\n" + . '</select>' . "\n" + . '</span>' . "\n"; + + $html_output .= '<input type="text" name="hostname" id="pma_hostname" maxlength="' + . $hostname_length . '" value="' + // use default value of '%' to match with the default 'Any host' + . htmlspecialchars(isset($GLOBALS['hostname']) ? $GLOBALS['hostname'] : '%') + . '" title="' . __('Host name') . '" ' + . (isset($GLOBALS['pred_hostname']) + && $GLOBALS['pred_hostname'] == 'userdefined' + ? 'required="required"' + : '') + . '>' . "\n" + . Util::showHint( + __( + 'When Host table is used, this field is ignored ' + . 'and values stored in Host table are used instead.' + ) + ) + . '</div>' . "\n"; + + $html_output .= '<div class="item">' . "\n" + . '<label for="select_pred_password">' . "\n" + . ' ' . __('Password:') . "\n" + . '</label>' . "\n" + . '<span class="options">' . "\n" + . '<select name="pred_password" id="select_pred_password" title="' + . __('Password') . '">' . "\n" + . ($mode == 'change' ? '<option value="keep" selected="selected">' + . __('Do not change the password') + . '</option>' . "\n" : '') + . '<option value="none"'; + + if (isset($GLOBALS['username']) && $mode != 'change') { + $html_output .= ' selected="selected"'; + } + $html_output .= '>' . __('No Password') . '</option>' . "\n" + . '<option value="userdefined"' + . (isset($GLOBALS['username']) ? '' : ' selected="selected"') . '>' + . __('Use text field') + . ':</option>' . "\n" + . '</select>' . "\n" + . '</span>' . "\n" + . '<input type="password" id="text_pma_pw" name="pma_pw" ' + . 'title="' . __('Password') . '" ' + . (isset($GLOBALS['username']) ? '' : 'required="required"') + . '>' . "\n" + . '<span>Strength:</span> ' + . '<meter max="4" id="password_strength_meter" name="pw_meter"></meter> ' + . '<span id="password_strength" name="pw_strength"></span>' . "\n" + . '</div>' . "\n"; + + $html_output .= '<div class="item" ' + . 'id="div_element_before_generate_password">' . "\n" + . '<label for="text_pma_pw2">' . "\n" + . ' ' . __('Re-type:') . "\n" + . '</label>' . "\n" + . '<span class="options"> </span>' . "\n" + . '<input type="password" name="pma_pw2" id="text_pma_pw2" ' + . 'title="' . __('Re-type') . '" ' + . (isset($GLOBALS['username']) ? '' : 'required="required"') + . '>' . "\n" + . '</div>' . "\n" + . '<div class="item" id="authentication_plugin_div">' + . '<label for="select_authentication_plugin" >'; + + $serverType = Util::getServerType(); + $serverVersion = $this->dbi->getVersion(); + $orig_auth_plugin = $this->getCurrentAuthenticationPlugin( + $mode, + $username, + $hostname + ); + + if (($serverType == 'MySQL' + && $serverVersion >= 50507) + || ($serverType == 'MariaDB' + && $serverVersion >= 50200) + ) { + $html_output .= __('Authentication Plugin') + . '</label><span class="options"> </span>' . "\n"; + + $auth_plugin_dropdown = $this->getHtmlForAuthPluginsDropdown( + $orig_auth_plugin, + $mode, + 'new' + ); + } else { + $html_output .= __('Password Hashing Method') + . '</label><span class="options"> </span>' . "\n"; + $auth_plugin_dropdown = $this->getHtmlForAuthPluginsDropdown( + $orig_auth_plugin, + $mode, + 'old' + ); + } + $html_output .= $auth_plugin_dropdown; + + $html_output .= '<div' + . ($orig_auth_plugin != 'sha256_password' ? ' class="hide"' : '') + . ' id="ssl_reqd_warning">' + . Message::notice( + __( + 'This method requires using an \'<i>SSL connection</i>\' ' + . 'or an \'<i>unencrypted connection that encrypts the password ' + . 'using RSA</i>\'; while connecting to the server.' + ) + . Util::showMySQLDocu('sha256-authentication-plugin') + ) + ->getDisplay() + . '</div>'; + + $html_output .= '</div>' . "\n" + // Generate password added here via jQuery + . '</fieldset>' . "\n"; + + return $html_output; + } + + /** + * Get username and hostname length + * + * @return array username length and hostname length + */ + public function getUsernameAndHostnameLength() + { + /* Fallback values */ + $username_length = 16; + $hostname_length = 41; + + /* Try to get real lengths from the database */ + $fields_info = $this->dbi->fetchResult( + 'SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH ' + . 'FROM information_schema.columns ' + . "WHERE table_schema = 'mysql' AND table_name = 'user' " + . "AND COLUMN_NAME IN ('User', 'Host')" + ); + foreach ($fields_info as $val) { + if ($val['COLUMN_NAME'] == 'User') { + $username_length = $val['CHARACTER_MAXIMUM_LENGTH']; + } elseif ($val['COLUMN_NAME'] == 'Host') { + $hostname_length = $val['CHARACTER_MAXIMUM_LENGTH']; + } + } + return [ + $username_length, + $hostname_length, + ]; + } + + /** + * Get current authentication plugin in use - for a user or globally + * + * @param string $mode are we creating a new user or are we just + * changing one? (allowed values: 'new', 'change') + * @param string $username User name + * @param string $hostname Host name + * + * @return string authentication plugin in use + */ + public function getCurrentAuthenticationPlugin( + $mode = 'new', + $username = null, + $hostname = null + ) { + /* Fallback (standard) value */ + $authentication_plugin = 'mysql_native_password'; + $serverVersion = $this->dbi->getVersion(); + + if (isset($username) && isset($hostname) + && $mode == 'change' + ) { + $row = $this->dbi->fetchSingleRow( + 'SELECT `plugin` FROM `mysql`.`user` WHERE ' + . '`User` = "' . $username . '" AND `Host` = "' . $hostname . '" LIMIT 1' + ); + // Table 'mysql'.'user' may not exist for some previous + // versions of MySQL - in that case consider fallback value + if (is_array($row) && isset($row['plugin'])) { + $authentication_plugin = $row['plugin']; + } + } elseif ($mode == 'change') { + list($username, $hostname) = $this->dbi->getCurrentUserAndHost(); + + $row = $this->dbi->fetchSingleRow( + 'SELECT `plugin` FROM `mysql`.`user` WHERE ' + . '`User` = "' . $username . '" AND `Host` = "' . $hostname . '"' + ); + if (is_array($row) && isset($row['plugin'])) { + $authentication_plugin = $row['plugin']; + } + } elseif ($serverVersion >= 50702) { + $row = $this->dbi->fetchSingleRow( + 'SELECT @@default_authentication_plugin' + ); + $authentication_plugin = is_array($row) ? $row['@@default_authentication_plugin'] : null; + } + + return $authentication_plugin; + } + + /** + * Returns all the grants for a certain user on a certain host + * Used in the export privileges for all users section + * + * @param string $user User name + * @param string $host Host name + * + * @return string containing all the grants text + */ + public function getGrants($user, $host) + { + $grants = $this->dbi->fetchResult( + "SHOW GRANTS FOR '" + . $this->dbi->escapeString($user) . "'@'" + . $this->dbi->escapeString($host) . "'" + ); + $response = ''; + foreach ($grants as $one_grant) { + $response .= $one_grant . ";\n\n"; + } + return $response; + } + + /** + * Update password and get message for password updating + * + * @param string $err_url error url + * @param string $username username + * @param string $hostname hostname + * + * @return Message success or error message after updating password + */ + public function updatePassword($err_url, $username, $hostname) + { + // similar logic in user_password.php + $message = null; + + if (empty($_POST['nopass']) + && isset($_POST['pma_pw']) + && isset($_POST['pma_pw2']) + ) { + if ($_POST['pma_pw'] != $_POST['pma_pw2']) { + $message = Message::error(__('The passwords aren\'t the same!')); + } elseif (empty($_POST['pma_pw']) || empty($_POST['pma_pw2'])) { + $message = Message::error(__('The password is empty!')); + } + } + + // here $nopass could be == 1 + if ($message === null) { + $hashing_function = 'PASSWORD'; + $serverType = Util::getServerType(); + $serverVersion = $this->dbi->getVersion(); + $authentication_plugin + = (isset($_POST['authentication_plugin']) + ? $_POST['authentication_plugin'] + : $this->getCurrentAuthenticationPlugin( + 'change', + $username, + $hostname + )); + + // Use 'ALTER USER ...' syntax for MySQL 5.7.6+ + if ($serverType == 'MySQL' + && $serverVersion >= 50706 + ) { + if ($authentication_plugin != 'mysql_old_password') { + $query_prefix = "ALTER USER '" + . $this->dbi->escapeString($username) + . "'@'" . $this->dbi->escapeString($hostname) . "'" + . " IDENTIFIED WITH " + . $authentication_plugin + . " BY '"; + } else { + $query_prefix = "ALTER USER '" + . $this->dbi->escapeString($username) + . "'@'" . $this->dbi->escapeString($hostname) . "'" + . " IDENTIFIED BY '"; + } + + // in $sql_query which will be displayed, hide the password + $sql_query = $query_prefix . "*'"; + + $local_query = $query_prefix + . $this->dbi->escapeString($_POST['pma_pw']) . "'"; + } elseif ($serverType == 'MariaDB' && $serverVersion >= 10000) { + // MariaDB uses "SET PASSWORD" syntax to change user password. + // On Galera cluster only DDL queries are replicated, since + // users are stored in MyISAM storage engine. + $query_prefix = "SET PASSWORD FOR '" + . $this->dbi->escapeString($username) + . "'@'" . $this->dbi->escapeString($hostname) . "'" + . " = PASSWORD ('"; + $sql_query = $local_query = $query_prefix + . $this->dbi->escapeString($_POST['pma_pw']) . "')"; + } elseif ($serverType == 'MariaDB' + && $serverVersion >= 50200 + && $this->dbi->isSuperuser() + ) { + // Use 'UPDATE `mysql`.`user` ...' Syntax for MariaDB 5.2+ + if ($authentication_plugin == 'mysql_native_password') { + // Set the hashing method used by PASSWORD() + // to be 'mysql_native_password' type + $this->dbi->tryQuery('SET old_passwords = 0;'); + } elseif ($authentication_plugin == 'sha256_password') { + // Set the hashing method used by PASSWORD() + // to be 'sha256_password' type + $this->dbi->tryQuery('SET `old_passwords` = 2;'); + } + + $hashedPassword = $this->getHashedPassword($_POST['pma_pw']); + + $sql_query = 'SET PASSWORD FOR \'' + . $this->dbi->escapeString($username) + . '\'@\'' . $this->dbi->escapeString($hostname) . '\' = ' + . ($_POST['pma_pw'] == '' + ? '\'\'' + : $hashing_function . '(\'' + . preg_replace('@.@s', '*', $_POST['pma_pw']) . '\')'); + + $local_query = "UPDATE `mysql`.`user` SET " + . " `authentication_string` = '" . $hashedPassword + . "', `Password` = '', " + . " `plugin` = '" . $authentication_plugin . "'" + . " WHERE `User` = '" . $username . "' AND Host = '" + . $hostname . "';"; + } else { + // USE 'SET PASSWORD ...' syntax for rest of the versions + // Backup the old value, to be reset later + $row = $this->dbi->fetchSingleRow( + 'SELECT @@old_passwords;' + ); + $orig_value = $row['@@old_passwords']; + $update_plugin_query = "UPDATE `mysql`.`user` SET" + . " `plugin` = '" . $authentication_plugin . "'" + . " WHERE `User` = '" . $username . "' AND Host = '" + . $hostname . "';"; + + // Update the plugin for the user + if (! $this->dbi->tryQuery($update_plugin_query)) { + Util::mysqlDie( + $this->dbi->getError(), + $update_plugin_query, + false, + $err_url + ); + } + $this->dbi->tryQuery("FLUSH PRIVILEGES;"); + + if ($authentication_plugin == 'mysql_native_password') { + // Set the hashing method used by PASSWORD() + // to be 'mysql_native_password' type + $this->dbi->tryQuery('SET old_passwords = 0;'); + } elseif ($authentication_plugin == 'sha256_password') { + // Set the hashing method used by PASSWORD() + // to be 'sha256_password' type + $this->dbi->tryQuery('SET `old_passwords` = 2;'); + } + $sql_query = 'SET PASSWORD FOR \'' + . $this->dbi->escapeString($username) + . '\'@\'' . $this->dbi->escapeString($hostname) . '\' = ' + . ($_POST['pma_pw'] == '' + ? '\'\'' + : $hashing_function . '(\'' + . preg_replace('@.@s', '*', $_POST['pma_pw']) . '\')'); + + $local_query = 'SET PASSWORD FOR \'' + . $this->dbi->escapeString($username) + . '\'@\'' . $this->dbi->escapeString($hostname) . '\' = ' + . ($_POST['pma_pw'] == '' ? '\'\'' : $hashing_function + . '(\'' . $this->dbi->escapeString($_POST['pma_pw']) . '\')'); + } + + if (! $this->dbi->tryQuery($local_query)) { + Util::mysqlDie( + $this->dbi->getError(), + $sql_query, + false, + $err_url + ); + } + // Flush privileges after successful password change + $this->dbi->tryQuery("FLUSH PRIVILEGES;"); + + $message = Message::success( + __('The password for %s was changed successfully.') + ); + $message->addParam('\'' . $username . '\'@\'' . $hostname . '\''); + if (isset($orig_value)) { + $this->dbi->tryQuery( + 'SET `old_passwords` = ' . $orig_value . ';' + ); + } + } + return $message; + } + + /** + * Revokes privileges and get message and SQL query for privileges revokes + * + * @param string $dbname database name + * @param string $tablename table name + * @param string $username username + * @param string $hostname host name + * @param string $itemType item type + * + * @return array ($message, $sql_query) + */ + public function getMessageAndSqlQueryForPrivilegesRevoke( + $dbname, + $tablename, + $username, + $hostname, + $itemType + ) { + $db_and_table = $this->wildcardEscapeForGrant($dbname, $tablename); + + $sql_query0 = 'REVOKE ALL PRIVILEGES ON ' . $itemType . ' ' . $db_and_table + . ' FROM \'' + . $this->dbi->escapeString($username) . '\'@\'' + . $this->dbi->escapeString($hostname) . '\';'; + + $sql_query1 = 'REVOKE GRANT OPTION ON ' . $itemType . ' ' . $db_and_table + . ' FROM \'' . $this->dbi->escapeString($username) . '\'@\'' + . $this->dbi->escapeString($hostname) . '\';'; + + $this->dbi->query($sql_query0); + if (! $this->dbi->tryQuery($sql_query1)) { + // this one may fail, too... + $sql_query1 = ''; + } + $sql_query = $sql_query0 . ' ' . $sql_query1; + $message = Message::success( + __('You have revoked the privileges for %s.') + ); + $message->addParam('\'' . $username . '\'@\'' . $hostname . '\''); + + return [ + $message, + $sql_query, + ]; + } + + /** + * Get REQUIRE cluase + * + * @return string REQUIRE clause + */ + public function getRequireClause() + { + $arr = isset($_POST['ssl_type']) ? $_POST : $GLOBALS; + if (isset($arr['ssl_type']) && $arr['ssl_type'] == 'SPECIFIED') { + $require = []; + if (! empty($arr['ssl_cipher'])) { + $require[] = "CIPHER '" + . $this->dbi->escapeString($arr['ssl_cipher']) . "'"; + } + if (! empty($arr['x509_issuer'])) { + $require[] = "ISSUER '" + . $this->dbi->escapeString($arr['x509_issuer']) . "'"; + } + if (! empty($arr['x509_subject'])) { + $require[] = "SUBJECT '" + . $this->dbi->escapeString($arr['x509_subject']) . "'"; + } + if (count($require)) { + $require_clause = " REQUIRE " . implode(" AND ", $require); + } else { + $require_clause = " REQUIRE NONE"; + } + } elseif (isset($arr['ssl_type']) && $arr['ssl_type'] == 'X509') { + $require_clause = " REQUIRE X509"; + } elseif (isset($arr['ssl_type']) && $arr['ssl_type'] == 'ANY') { + $require_clause = " REQUIRE SSL"; + } else { + $require_clause = " REQUIRE NONE"; + } + + return $require_clause; + } + + /** + * Get a WITH clause for 'update privileges' and 'add user' + * + * @return string + */ + public function getWithClauseForAddUserAndUpdatePrivs() + { + $sql_query = ''; + if (((isset($_POST['Grant_priv']) && $_POST['Grant_priv'] == 'Y') + || (isset($GLOBALS['Grant_priv']) && $GLOBALS['Grant_priv'] == 'Y')) + && ! ((Util::getServerType() == 'MySQL' || Util::getServerType() == 'Percona Server') + && $this->dbi->getVersion() >= 80011) + ) { + $sql_query .= ' GRANT OPTION'; + } + if (isset($_POST['max_questions']) || isset($GLOBALS['max_questions'])) { + $max_questions = isset($_POST['max_questions']) + ? (int) $_POST['max_questions'] : (int) $GLOBALS['max_questions']; + $max_questions = max(0, $max_questions); + $sql_query .= ' MAX_QUERIES_PER_HOUR ' . $max_questions; + } + if (isset($_POST['max_connections']) || isset($GLOBALS['max_connections'])) { + $max_connections = isset($_POST['max_connections']) + ? (int) $_POST['max_connections'] : (int) $GLOBALS['max_connections']; + $max_connections = max(0, $max_connections); + $sql_query .= ' MAX_CONNECTIONS_PER_HOUR ' . $max_connections; + } + if (isset($_POST['max_updates']) || isset($GLOBALS['max_updates'])) { + $max_updates = isset($_POST['max_updates']) + ? (int) $_POST['max_updates'] : (int) $GLOBALS['max_updates']; + $max_updates = max(0, $max_updates); + $sql_query .= ' MAX_UPDATES_PER_HOUR ' . $max_updates; + } + if (isset($_POST['max_user_connections']) + || isset($GLOBALS['max_user_connections']) + ) { + $max_user_connections = isset($_POST['max_user_connections']) + ? (int) $_POST['max_user_connections'] + : (int) $GLOBALS['max_user_connections']; + $max_user_connections = max(0, $max_user_connections); + $sql_query .= ' MAX_USER_CONNECTIONS ' . $max_user_connections; + } + return (! empty($sql_query) ? ' WITH' . $sql_query : ''); + } + + /** + * Get HTML for addUsersForm, This function call if isset($_GET['adduser']) + * + * @param string $dbname database name + * + * @return string HTML for addUserForm + */ + public function getHtmlForAddUser($dbname) + { + $html_output = '<h2>' . "\n" + . Util::getIcon('b_usradd') . __('Add user account') . "\n" + . '</h2>' . "\n" + . '<form name="usersForm" id="addUsersForm"' + . ' onsubmit="return checkAddUser(this);"' + . ' action="server_privileges.php" method="post" autocomplete="off" >' . "\n" + . Url::getHiddenInputs('', '') + . $this->getHtmlForLoginInformationFields('new'); + + $html_output .= '<fieldset id="fieldset_add_user_database">' . "\n" + . '<legend>' . __('Database for user account') . '</legend>' . "\n"; + + $html_output .= $this->template->render('checkbox', [ + 'html_field_name' => 'createdb-1', + 'label' => __('Create database with same name and grant all privileges.'), + 'checked' => false, + 'onclick' => false, + 'html_field_id' => 'createdb-1', + ]); + $html_output .= '<br>' . "\n"; + $html_output .= $this->template->render('checkbox', [ + 'html_field_name' => 'createdb-2', + 'label' => __('Grant all privileges on wildcard name (username\\_%).'), + 'checked' => false, + 'onclick' => false, + 'html_field_id' => 'createdb-2', + ]); + $html_output .= '<br>' . "\n"; + + if (! empty($dbname)) { + $html_output .= $this->template->render('checkbox', [ + 'html_field_name' => 'createdb-3', + 'label' => sprintf(__('Grant all privileges on database %s.'), htmlspecialchars($dbname)), + 'checked' => true, + 'onclick' => false, + 'html_field_id' => 'createdb-3', + ]); + $html_output .= '<input type="hidden" name="dbname" value="' + . htmlspecialchars($dbname) . '">' . "\n"; + $html_output .= '<br>' . "\n"; + } + + $html_output .= '</fieldset>' . "\n"; + if ($GLOBALS['is_grantuser']) { + $html_output .= $this->getHtmlToDisplayPrivilegesTable('*', '*', false); + } + $html_output .= '<fieldset id="fieldset_add_user_footer" class="tblFooters">' + . "\n" + . '<input type="hidden" name="adduser_submit" value="1">' . "\n" + . '<input class="btn btn-primary" type="submit" id="adduser_submit" value="' . __('Go') . '">' + . "\n" + . '</fieldset>' . "\n" + . '</form>' . "\n"; + + return $html_output; + } + + /** + * Get the list of privileges and list of compared privileges as strings + * and return a array that contains both strings + * + * @return array $list_of_privileges, $list_of_compared_privileges + */ + public function getListOfPrivilegesAndComparedPrivileges() + { + $list_of_privileges + = '`User`, ' + . '`Host`, ' + . '`Select_priv`, ' + . '`Insert_priv`, ' + . '`Update_priv`, ' + . '`Delete_priv`, ' + . '`Create_priv`, ' + . '`Drop_priv`, ' + . '`Grant_priv`, ' + . '`Index_priv`, ' + . '`Alter_priv`, ' + . '`References_priv`, ' + . '`Create_tmp_table_priv`, ' + . '`Lock_tables_priv`, ' + . '`Create_view_priv`, ' + . '`Show_view_priv`, ' + . '`Create_routine_priv`, ' + . '`Alter_routine_priv`, ' + . '`Execute_priv`'; + + $listOfComparedPrivs + = '`Select_priv` = \'N\'' + . ' AND `Insert_priv` = \'N\'' + . ' AND `Update_priv` = \'N\'' + . ' AND `Delete_priv` = \'N\'' + . ' AND `Create_priv` = \'N\'' + . ' AND `Drop_priv` = \'N\'' + . ' AND `Grant_priv` = \'N\'' + . ' AND `References_priv` = \'N\'' + . ' AND `Create_tmp_table_priv` = \'N\'' + . ' AND `Lock_tables_priv` = \'N\'' + . ' AND `Create_view_priv` = \'N\'' + . ' AND `Show_view_priv` = \'N\'' + . ' AND `Create_routine_priv` = \'N\'' + . ' AND `Alter_routine_priv` = \'N\'' + . ' AND `Execute_priv` = \'N\''; + + $list_of_privileges .= + ', `Event_priv`, ' + . '`Trigger_priv`'; + $listOfComparedPrivs .= + ' AND `Event_priv` = \'N\'' + . ' AND `Trigger_priv` = \'N\''; + return [ + $list_of_privileges, + $listOfComparedPrivs, + ]; + } + + /** + * Get the HTML for routine based privileges + * + * @param string $db database name + * @param string $index_checkbox starting index for rows to be added + * + * @return string + */ + public function getHtmlTableBodyForSpecificDbRoutinePrivs($db, $index_checkbox) + { + $sql_query = 'SELECT * FROM `mysql`.`procs_priv` WHERE Db = \'' . $this->dbi->escapeString($db) . '\';'; + $res = $this->dbi->query($sql_query); + $html_output = ''; + while ($row = $this->dbi->fetchAssoc($res)) { + $html_output .= '<tr>'; + + $html_output .= '<td'; + $value = htmlspecialchars($row['User'] . '&#27;' . $row['Host']); + $html_output .= '>'; + $html_output .= '<input type="checkbox" class="checkall" ' + . 'name="selected_usr[]" ' + . 'id="checkbox_sel_users_' . ($index_checkbox++) . '" ' + . 'value="' . $value . '"></td>'; + + $html_output .= '<td>' . htmlspecialchars($row['User']) + . '</td>' + . '<td>' . htmlspecialchars($row['Host']) + . '</td>' + . '<td>routine' + . '</td>' + . '<td><code>' . htmlspecialchars($row['Routine_name']) . '</code>' + . '</td>' + . '<td>Yes' + . '</td>'; + $current_user = $row['User']; + $current_host = $row['Host']; + $routine = $row['Routine_name']; + $html_output .= '<td>'; + $specific_db = ''; + $specific_table = ''; + if ($GLOBALS['is_grantuser']) { + $specific_db = isset($row['Db']) && $row['Db'] != '*' + ? $row['Db'] : ''; + $specific_table = isset($row['Table_name']) + && $row['Table_name'] != '*' + ? $row['Table_name'] : ''; + $html_output .= $this->getUserLink( + 'edit', + $current_user, + $current_host, + $specific_db, + $specific_table, + $routine + ); + } + $html_output .= '</td>'; + $html_output .= '<td>'; + $html_output .= $this->getUserLink( + 'export', + $current_user, + $current_host, + $specific_db, + $specific_table, + $routine + ); + $html_output .= '</td>'; + + $html_output .= '</tr>'; + } + return $html_output; + } + + /** + * Get the HTML for user form and check the privileges for a particular database. + * + * @param string $db database name + * + * @return string + */ + public function getHtmlForSpecificDbPrivileges($db) + { + $html_output = ''; + + if ($this->dbi->isSuperuser()) { + // check the privileges for a particular database. + $html_output = '<form id="usersForm" action="server_privileges.php">'; + $html_output .= Url::getHiddenInputs($db); + $html_output .= '<div class="width100">'; + $html_output .= '<fieldset>'; + $html_output .= '<legend>' . "\n" + . Util::getIcon('b_usrcheck') + . ' ' + . sprintf( + __('Users having access to "%s"'), + '<a href="' . Util::getScriptNameForOption( + $GLOBALS['cfg']['DefaultTabDatabase'], + 'database' + ) + . Url::getCommon(['db' => $db]) . '">' + . htmlspecialchars($db) + . '</a>' + ) + . "\n" + . '</legend>' . "\n"; + + $html_output .= '<div class="responsivetable jsresponsive">'; + $html_output .= '<table id="dbspecificuserrights" class="data">'; + $html_output .= $this->getHtmlForPrivsTableHead(); + $privMap = $this->getPrivMap($db); + $html_output .= $this->getHtmlTableBodyForSpecificDbOrTablePrivs($privMap, $db); + $html_output .= '</table>'; + $html_output .= '</div>'; + + $html_output .= '<div class="floatleft">'; + $html_output .= $this->template->render('select_all', [ + 'pma_theme_image' => $GLOBALS['pmaThemeImage'], + 'text_dir' => $GLOBALS['text_dir'], + 'form_name' => "usersForm", + ]); + $html_output .= Util::getButtonOrImage( + 'submit_mult', + 'mult_submit', + __('Export'), + 'b_tblexport', + 'export' + ); + + $html_output .= '</fieldset>'; + $html_output .= '</div>'; + $html_output .= '</form>'; + } else { + $html_output .= $this->getHtmlForViewUsersError(); + } + + $response = Response::getInstance(); + if ($response->isAjax() === true + && empty($_REQUEST['ajax_page_request']) + ) { + $message = Message::success(__('User has been added.')); + $response->addJSON('message', $message); + $response->addJSON('user_form', $html_output); + exit; + } else { + // Offer to create a new user for the current database + $html_output .= $this->getAddUserHtmlFieldset($db); + } + return $html_output; + } + + /** + * Get the HTML for user form and check the privileges for a particular table. + * + * @param string $db database name + * @param string $table table name + * + * @return string + */ + public function getHtmlForSpecificTablePrivileges($db, $table) + { + $html_output = ''; + if ($this->dbi->isSuperuser()) { + // check the privileges for a particular table. + $html_output = '<form id="usersForm" action="server_privileges.php">'; + $html_output .= Url::getHiddenInputs($db, $table); + $html_output .= '<fieldset>'; + $html_output .= '<legend>' + . Util::getIcon('b_usrcheck') + . sprintf( + __('Users having access to "%s"'), + '<a href="' . Util::getScriptNameForOption( + $GLOBALS['cfg']['DefaultTabTable'], + 'table' + ) + . Url::getCommon( + [ + 'db' => $db, + 'table' => $table, + ] + ) . '">' + . htmlspecialchars($db) . '.' . htmlspecialchars($table) + . '</a>' + ) + . '</legend>'; + + $html_output .= '<div class="responsivetable jsresponsive">'; + $html_output .= '<table id="tablespecificuserrights" class="data">'; + $html_output .= $this->getHtmlForPrivsTableHead(); + $privMap = $this->getPrivMap($db); + $sql_query = "SELECT `User`, `Host`, `Db`," + . " 't' AS `Type`, `Table_name`, `Table_priv`" + . " FROM `mysql`.`tables_priv`" + . " WHERE '" . $this->dbi->escapeString($db) . "' LIKE `Db`" + . " AND '" . $this->dbi->escapeString($table) . "' LIKE `Table_name`" + . " AND NOT (`Table_priv` = '' AND Column_priv = '')" + . " ORDER BY `User` ASC, `Host` ASC, `Db` ASC, `Table_priv` ASC;"; + $res = $this->dbi->query($sql_query); + $this->mergePrivMapFromResult($privMap, $res); + $html_output .= $this->getHtmlTableBodyForSpecificDbOrTablePrivs($privMap, $db); + $html_output .= '</table></div>'; + + $html_output .= '<div class="floatleft">'; + $html_output .= $this->template->render('select_all', [ + 'pma_theme_image' => $GLOBALS['pmaThemeImage'], + 'text_dir' => $GLOBALS['text_dir'], + 'form_name' => "usersForm", + ]); + $html_output .= Util::getButtonOrImage( + 'submit_mult', + 'mult_submit', + __('Export'), + 'b_tblexport', + 'export' + ); + + $html_output .= '</fieldset>'; + $html_output .= '</form>'; + } else { + $html_output .= $this->getHtmlForViewUsersError(); + } + // Offer to create a new user for the current database + $html_output .= $this->getAddUserHtmlFieldset($db, $table); + return $html_output; + } + + /** + * gets privilege map + * + * @param string $db the database + * + * @return array the privilege map + */ + public function getPrivMap($db) + { + list($listOfPrivs, $listOfComparedPrivs) + = $this->getListOfPrivilegesAndComparedPrivileges(); + $sql_query + = "(" + . " SELECT " . $listOfPrivs . ", '*' AS `Db`, 'g' AS `Type`" + . " FROM `mysql`.`user`" + . " WHERE NOT (" . $listOfComparedPrivs . ")" + . ")" + . " UNION " + . "(" + . " SELECT " . $listOfPrivs . ", `Db`, 'd' AS `Type`" + . " FROM `mysql`.`db`" + . " WHERE '" . $this->dbi->escapeString($db) . "' LIKE `Db`" + . " AND NOT (" . $listOfComparedPrivs . ")" + . ")" + . " ORDER BY `User` ASC, `Host` ASC, `Db` ASC;"; + $res = $this->dbi->query($sql_query); + $privMap = []; + $this->mergePrivMapFromResult($privMap, $res); + return $privMap; + } + + /** + * merge privilege map and rows from resultset + * + * @param array $privMap the privilege map reference + * @param object $result the resultset of query + * + * @return void + */ + public function mergePrivMapFromResult(array &$privMap, $result) + { + while ($row = $this->dbi->fetchAssoc($result)) { + $user = $row['User']; + $host = $row['Host']; + if (! isset($privMap[$user])) { + $privMap[$user] = []; + } + if (! isset($privMap[$user][$host])) { + $privMap[$user][$host] = []; + } + $privMap[$user][$host][] = $row; + } + } + + /** + * Get HTML snippet for privileges table head + * + * @return string + */ + public function getHtmlForPrivsTableHead() + { + return '<thead>' + . '<tr>' + . '<th></th>' + . '<th>' . __('User name') . '</th>' + . '<th>' . __('Host name') . '</th>' + . '<th>' . __('Type') . '</th>' + . '<th>' . __('Privileges') . '</th>' + . '<th>' . __('Grant') . '</th>' + . '<th colspan="2">' . __('Action') . '</th>' + . '</tr>' + . '</thead>'; + } + + /** + * Get HTML error for View Users form + * For non superusers such as grant/create users + * + * @return string + */ + public function getHtmlForViewUsersError() + { + return Message::error( + __('Not enough privilege to view users.') + )->getDisplay(); + } + + /** + * Get HTML snippet for table body of specific database or table privileges + * + * @param array $privMap privilege map + * @param string $db database + * + * @return string + */ + public function getHtmlTableBodyForSpecificDbOrTablePrivs($privMap, $db) + { + $html_output = '<tbody>'; + $index_checkbox = 0; + if (empty($privMap)) { + $html_output .= '<tr>' + . '<td colspan="6">' + . __('No user found.') + . '</td>' + . '</tr>' + . '</tbody>'; + return $html_output; + } + + foreach ($privMap as $current_user => $val) { + foreach ($val as $current_host => $current_privileges) { + $nbPrivileges = count($current_privileges); + $html_output .= '<tr>'; + + $value = htmlspecialchars($current_user . '&#27;' . $current_host); + $html_output .= '<td'; + if ($nbPrivileges > 1) { + $html_output .= ' rowspan="' . $nbPrivileges . '"'; + } + $html_output .= '>'; + $html_output .= '<input type="checkbox" class="checkall" ' + . 'name="selected_usr[]" ' + . 'id="checkbox_sel_users_' . ($index_checkbox++) . '" ' + . 'value="' . $value . '"></td>' . "\n"; + + // user + $html_output .= '<td'; + if ($nbPrivileges > 1) { + $html_output .= ' rowspan="' . $nbPrivileges . '"'; + } + $html_output .= '>'; + if (empty($current_user)) { + $html_output .= '<span style="color: #FF0000">' + . __('Any') . '</span>'; + } else { + $html_output .= htmlspecialchars($current_user); + } + $html_output .= '</td>'; + + // host + $html_output .= '<td'; + if ($nbPrivileges > 1) { + $html_output .= ' rowspan="' . $nbPrivileges . '"'; + } + $html_output .= '>'; + $html_output .= htmlspecialchars($current_host); + $html_output .= '</td>'; + + $html_output .= $this->getHtmlListOfPrivs( + $db, + $current_privileges, + $current_user, + $current_host + ); + } + } + + //For fetching routine based privileges + $html_output .= $this->getHtmlTableBodyForSpecificDbRoutinePrivs($db, $index_checkbox); + $html_output .= '</tbody>'; + + return $html_output; + } + + /** + * Get HTML to display privileges + * + * @param string $db Database name + * @param array $current_privileges List of privileges + * @param string $current_user Current user + * @param string $current_host Current host + * + * @return string HTML to display privileges + */ + public function getHtmlListOfPrivs( + $db, + array $current_privileges, + $current_user, + $current_host + ) { + $nbPrivileges = count($current_privileges); + $html_output = null; + for ($i = 0; $i < $nbPrivileges; $i++) { + $current = $current_privileges[$i]; + + // type + $html_output .= '<td>'; + if ($current['Type'] == 'g') { + $html_output .= __('global'); + } elseif ($current['Type'] == 'd') { + if ($current['Db'] == Util::escapeMysqlWildcards($db)) { + $html_output .= __('database-specific'); + } else { + $html_output .= __('wildcard') . ': ' + . '<code>' + . htmlspecialchars($current['Db']) + . '</code>'; + } + } elseif ($current['Type'] == 't') { + $html_output .= __('table-specific'); + } + $html_output .= '</td>'; + + // privileges + $html_output .= '<td>'; + if (isset($current['Table_name'])) { + $privList = explode(',', $current['Table_priv']); + $privs = []; + $grantsArr = $this->getTableGrantsArray(); + foreach ($grantsArr as $grant) { + $privs[$grant[0]] = 'N'; + foreach ($privList as $priv) { + if ($grant[0] == $priv) { + $privs[$grant[0]] = 'Y'; + } + } + } + $html_output .= '<code>' + . implode( + ',', + $this->extractPrivInfo($privs, true, true) + ) + . '</code>'; + } else { + $html_output .= '<code>' + . implode( + ',', + $this->extractPrivInfo($current, true, false) + ) + . '</code>'; + } + $html_output .= '</td>'; + + // grant + $html_output .= '<td>'; + $containsGrant = false; + if (isset($current['Table_name'])) { + $privList = explode(',', $current['Table_priv']); + foreach ($privList as $priv) { + if ($priv == 'Grant') { + $containsGrant = true; + } + } + } else { + $containsGrant = $current['Grant_priv'] == 'Y'; + } + $html_output .= ($containsGrant ? __('Yes') : __('No')); + $html_output .= '</td>'; + + // action + $html_output .= '<td>'; + $specific_db = isset($current['Db']) && $current['Db'] != '*' + ? $current['Db'] : ''; + $specific_table = isset($current['Table_name']) + && $current['Table_name'] != '*' + ? $current['Table_name'] : ''; + if ($GLOBALS['is_grantuser']) { + $html_output .= $this->getUserLink( + 'edit', + $current_user, + $current_host, + $specific_db, + $specific_table + ); + } + $html_output .= '</td>'; + $html_output .= '<td class="center">' + . $this->getUserLink( + 'export', + $current_user, + $current_host, + $specific_db, + $specific_table + ) + . '</td>'; + + $html_output .= '</tr>'; + if (($i + 1) < $nbPrivileges) { + $html_output .= '<tr class="noclick">'; + } + } + return $html_output; + } + + /** + * Returns edit, revoke or export link for a user. + * + * @param string $linktype The link type (edit | revoke | export) + * @param string $username User name + * @param string $hostname Host name + * @param string $dbname Database name + * @param string $tablename Table name + * @param string $routinename Routine name + * @param string $initial Initial value + * + * @return string HTML code with link + */ + public function getUserLink( + $linktype, + $username, + $hostname, + $dbname = '', + $tablename = '', + $routinename = '', + $initial = '' + ) { + $html = '<a'; + switch ($linktype) { + case 'edit': + $html .= ' class="edit_user_anchor"'; + break; + case 'export': + $html .= ' class="export_user_anchor ajax"'; + break; + } + $params = [ + 'username' => $username, + 'hostname' => $hostname, + ]; + switch ($linktype) { + case 'edit': + $params['dbname'] = $dbname; + $params['tablename'] = $tablename; + $params['routinename'] = $routinename; + break; + case 'revoke': + $params['dbname'] = $dbname; + $params['tablename'] = $tablename; + $params['routinename'] = $routinename; + $params['revokeall'] = 1; + break; + case 'export': + $params['initial'] = $initial; + $params['export'] = 1; + break; + } + + $html .= ' href="server_privileges.php'; + if ($linktype == 'revoke') { + $html .= '" data-post="' . Url::getCommon($params, ''); + } else { + $html .= Url::getCommon($params); + } + $html .= '">'; + + switch ($linktype) { + case 'edit': + $html .= Util::getIcon('b_usredit', __('Edit privileges')); + break; + case 'revoke': + $html .= Util::getIcon('b_usrdrop', __('Revoke')); + break; + case 'export': + $html .= Util::getIcon('b_tblexport', __('Export')); + break; + } + $html .= '</a>'; + + return $html; + } + + /** + * Returns user group edit link + * + * @param string $username User name + * + * @return string HTML code with link + */ + public function getUserGroupEditLink($username) + { + return '<a class="edit_user_group_anchor ajax"' + . ' href="server_privileges.php' + . Url::getCommon(['username' => $username]) + . '">' + . Util::getIcon('b_usrlist', __('Edit user group')) + . '</a>'; + } + + /** + * Returns number of defined user groups + * + * @return integer + */ + public function getUserGroupCount() + { + $cfgRelation = $this->relation->getRelationsParam(); + $user_group_table = Util::backquote($cfgRelation['db']) + . '.' . Util::backquote($cfgRelation['usergroups']); + $sql_query = 'SELECT COUNT(*) FROM ' . $user_group_table; + $user_group_count = $this->dbi->fetchValue( + $sql_query, + 0, + 0, + DatabaseInterface::CONNECT_CONTROL + ); + + return $user_group_count; + } + + /** + * Returns name of user group that user is part of + * + * @param string $username User name + * + * @return mixed usergroup if found or null if not found + */ + public function getUserGroupForUser($username) + { + $cfgRelation = $this->relation->getRelationsParam(); + + if (empty($cfgRelation['db']) + || empty($cfgRelation['users']) + ) { + return null; + } + + $user_table = Util::backquote($cfgRelation['db']) + . '.' . Util::backquote($cfgRelation['users']); + $sql_query = 'SELECT `usergroup` FROM ' . $user_table + . ' WHERE `username` = \'' . $username . '\'' + . ' LIMIT 1'; + + $usergroup = $this->dbi->fetchValue( + $sql_query, + 0, + 0, + DatabaseInterface::CONNECT_CONTROL + ); + + if ($usergroup === false) { + return null; + } + + return $usergroup; + } + + /** + * This function return the extra data array for the ajax behavior + * + * @param string $password password + * @param string $sql_query sql query + * @param string $hostname hostname + * @param string $username username + * + * @return array + */ + public function getExtraDataForAjaxBehavior( + $password, + $sql_query, + $hostname, + $username + ) { + if (isset($GLOBALS['dbname'])) { + //if (preg_match('/\\\\(?:_|%)/i', $dbname)) { + if (preg_match('/(?<!\\\\)(?:_|%)/', $GLOBALS['dbname'])) { + $dbname_is_wildcard = true; + } else { + $dbname_is_wildcard = false; + } + } + + $user_group_count = 0; + if ($GLOBALS['cfgRelation']['menuswork']) { + $user_group_count = $this->getUserGroupCount(); + } + + $extra_data = []; + if (strlen($sql_query) > 0) { + $extra_data['sql_query'] = Util::getMessage(null, $sql_query); + } + + if (isset($_POST['change_copy'])) { + /** + * generate html on the fly for the new user that was just created. + */ + $new_user_string = '<tr>' . "\n" + . '<td> <input type="checkbox" name="selected_usr[]" ' + . 'id="checkbox_sel_users_"' + . 'value="' + . htmlspecialchars($username) + . '&#27;' . htmlspecialchars($hostname) . '">' + . '</td>' . "\n" + . '<td><label for="checkbox_sel_users_">' + . (empty($_POST['username']) + ? '<span style="color: #FF0000">' . __('Any') . '</span>' + : htmlspecialchars($username) ) . '</label></td>' . "\n" + . '<td>' . htmlspecialchars($hostname) . '</td>' . "\n"; + + $new_user_string .= '<td>'; + + if (! empty($password) || isset($_POST['pma_pw'])) { + $new_user_string .= __('Yes'); + } else { + $new_user_string .= '<span style="color: #FF0000">' + . __('No') + . '</span>'; + } + + $new_user_string .= '</td>' . "\n"; + $new_user_string .= '<td>' + . '<code>' . implode(', ', $this->extractPrivInfo(null, true)) . '</code>' + . '</td>'; //Fill in privileges here + + // if $cfg['Servers'][$i]['users'] and $cfg['Servers'][$i]['usergroups'] are + // enabled + $cfgRelation = $this->relation->getRelationsParam(); + if (! empty($cfgRelation['users']) && ! empty($cfgRelation['usergroups'])) { + $new_user_string .= '<td class="usrGroup"></td>'; + } + + $new_user_string .= '<td>'; + if (isset($_POST['Grant_priv']) && $_POST['Grant_priv'] == 'Y') { + $new_user_string .= __('Yes'); + } else { + $new_user_string .= __('No'); + } + $new_user_string .= '</td>'; + + if ($GLOBALS['is_grantuser']) { + $new_user_string .= '<td>' + . $this->getUserLink('edit', $username, $hostname) + . '</td>' . "\n"; + } + + if ($cfgRelation['menuswork'] && $user_group_count > 0) { + $new_user_string .= '<td>' + . $this->getUserGroupEditLink($username) + . '</td>' . "\n"; + } + + $new_user_string .= '<td>' + . $this->getUserLink( + 'export', + $username, + $hostname, + '', + '', + '', + isset($_GET['initial']) ? $_GET['initial'] : '' + ) + . '</td>' . "\n"; + + $new_user_string .= '</tr>'; + + $extra_data['new_user_string'] = $new_user_string; + + /** + * Generate the string for this alphabet's initial, to update the user + * pagination + */ + $new_user_initial = mb_strtoupper( + mb_substr($username, 0, 1) + ); + $newUserInitialString = '<a href="server_privileges.php' + . Url::getCommon(['initial' => $new_user_initial]) . '">' + . $new_user_initial . '</a>'; + $extra_data['new_user_initial'] = $new_user_initial; + $extra_data['new_user_initial_string'] = $newUserInitialString; + } + + if (isset($_POST['update_privs'])) { + $extra_data['db_specific_privs'] = false; + $extra_data['db_wildcard_privs'] = false; + if (isset($dbname_is_wildcard)) { + $extra_data['db_specific_privs'] = ! $dbname_is_wildcard; + $extra_data['db_wildcard_privs'] = $dbname_is_wildcard; + } + $new_privileges = implode(', ', $this->extractPrivInfo(null, true)); + + $extra_data['new_privileges'] = $new_privileges; + } + + if (isset($_GET['validate_username'])) { + $sql_query = "SELECT * FROM `mysql`.`user` WHERE `User` = '" + . $this->dbi->escapeString($_GET['username']) . "';"; + $res = $this->dbi->query($sql_query); + $row = $this->dbi->fetchRow($res); + if (empty($row)) { + $extra_data['user_exists'] = false; + } else { + $extra_data['user_exists'] = true; + } + } + + return $extra_data; + } + + /** + * Get the HTML snippet for change user login information + * + * @param string $username username + * @param string $hostname host name + * + * @return string HTML snippet + */ + public function getChangeLoginInformationHtmlForm($username, $hostname) + { + $choices = [ + '4' => __('… keep the old one.'), + '1' => __('… delete the old one from the user tables.'), + '2' => __( + '… revoke all active privileges from ' + . 'the old one and delete it afterwards.' + ), + '3' => __( + '… delete the old one from the user tables ' + . 'and reload the privileges afterwards.' + ), + ]; + + $html_output = '<form action="server_privileges.php" ' + . 'onsubmit="return checkAddUser(this);" ' + . 'method="post" class="copyUserForm submenu-item">' . "\n" + . Url::getHiddenInputs('', '') + . '<input type="hidden" name="old_username" ' + . 'value="' . htmlspecialchars($username) . '">' . "\n" + . '<input type="hidden" name="old_hostname" ' + . 'value="' . htmlspecialchars($hostname) . '">' . "\n"; + + $usergroup = $this->getUserGroupForUser($username); + if ($usergroup !== null) { + $html_output .= '<input type="hidden" name="old_usergroup" ' + . 'value="' . htmlspecialchars($usergroup) . '">' . "\n"; + } + + $html_output .= '<fieldset id="fieldset_change_copy_user">' . "\n" + . '<legend data-submenu-label="' . __('Login Information') . '">' . "\n" + . __('Change login information / Copy user account') + . '</legend>' . "\n" + . $this->getHtmlForLoginInformationFields('change', $username, $hostname); + + $html_output .= '<fieldset id="fieldset_mode">' . "\n" + . ' <legend>' + . __('Create a new user account with the same privileges and …') + . '</legend>' . "\n"; + $html_output .= Util::getRadioFields( + 'mode', + $choices, + '4', + true + ); + $html_output .= '</fieldset>' . "\n" + . '</fieldset>' . "\n"; + + $html_output .= '<fieldset id="fieldset_change_copy_user_footer" ' + . 'class="tblFooters">' . "\n" + . '<input type="hidden" name="change_copy" value="1">' . "\n" + . '<input class="btn btn-primary" type="submit" value="' . __('Go') . '">' . "\n" + . '</fieldset>' . "\n" + . '</form>' . "\n"; + + return $html_output; + } + + /** + * Provide a line with links to the relevant database and table + * + * @param string $url_dbname url database name that urlencode() string + * @param string $dbname database name + * @param string $tablename table name + * + * @return string HTML snippet + */ + public function getLinkToDbAndTable($url_dbname, $dbname, $tablename) + { + $html_output = '[ ' . __('Database') + . ' <a href="' . Util::getScriptNameForOption( + $GLOBALS['cfg']['DefaultTabDatabase'], + 'database' + ) + . Url::getCommon( + [ + 'db' => $url_dbname, + 'reload' => 1, + ] + ) + . '">' + . htmlspecialchars(Util::unescapeMysqlWildcards($dbname)) . ': ' + . Util::getTitleForTarget( + $GLOBALS['cfg']['DefaultTabDatabase'] + ) + . "</a> ]\n"; + + if (strlen($tablename) > 0) { + $html_output .= ' [ ' . __('Table') . ' <a href="' + . Util::getScriptNameForOption( + $GLOBALS['cfg']['DefaultTabTable'], + 'table' + ) + . Url::getCommon( + [ + 'db' => $url_dbname, + 'table' => $tablename, + 'reload' => 1, + ] + ) + . '">' . htmlspecialchars($tablename) . ': ' + . Util::getTitleForTarget( + $GLOBALS['cfg']['DefaultTabTable'] + ) + . "</a> ]\n"; + } + return $html_output; + } + + /** + * no db name given, so we want all privs for the given user + * db name was given, so we want all user specific rights for this db + * So this function returns user rights as an array + * + * @param string $username username + * @param string $hostname host name + * @param string $type database or table + * @param string $dbname database name + * + * @return array database rights + */ + public function getUserSpecificRights($username, $hostname, $type, $dbname = '') + { + $user_host_condition = " WHERE `User`" + . " = '" . $this->dbi->escapeString($username) . "'" + . " AND `Host`" + . " = '" . $this->dbi->escapeString($hostname) . "'"; + + if ($type == 'database') { + $tables_to_search_for_users = [ + 'tables_priv', + 'columns_priv', + 'procs_priv', + ]; + $dbOrTableName = 'Db'; + } elseif ($type == 'table') { + $user_host_condition .= " AND `Db` LIKE '" + . $this->dbi->escapeString($dbname) . "'"; + $tables_to_search_for_users = ['columns_priv']; + $dbOrTableName = 'Table_name'; + } else { // routine + $user_host_condition .= " AND `Db` LIKE '" + . $this->dbi->escapeString($dbname) . "'"; + $tables_to_search_for_users = ['procs_priv']; + $dbOrTableName = 'Routine_name'; + } + + // we also want privileges for this user not in table `db` but in other table + $tables = $this->dbi->fetchResult('SHOW TABLES FROM `mysql`;'); + + $db_rights_sqls = []; + foreach ($tables_to_search_for_users as $table_search_in) { + if (in_array($table_search_in, $tables)) { + $db_rights_sqls[] = ' + SELECT DISTINCT `' . $dbOrTableName . '` + FROM `mysql`.' . Util::backquote($table_search_in) + . $user_host_condition; + } + } + + $user_defaults = [ + $dbOrTableName => '', + 'Grant_priv' => 'N', + 'privs' => ['USAGE'], + 'Column_priv' => true, + ]; + + // for the rights + $db_rights = []; + + $db_rights_sql = '(' . implode(') UNION (', $db_rights_sqls) . ')' + . ' ORDER BY `' . $dbOrTableName . '` ASC'; + + $db_rights_result = $this->dbi->query($db_rights_sql); + + while ($db_rights_row = $this->dbi->fetchAssoc($db_rights_result)) { + $db_rights_row = array_merge($user_defaults, $db_rights_row); + if ($type == 'database') { + // only Db names in the table `mysql`.`db` uses wildcards + // as we are in the db specific rights display we want + // all db names escaped, also from other sources + $db_rights_row['Db'] = Util::escapeMysqlWildcards( + $db_rights_row['Db'] + ); + } + $db_rights[$db_rights_row[$dbOrTableName]] = $db_rights_row; + } + + $this->dbi->freeResult($db_rights_result); + + if ($type == 'database') { + $sql_query = 'SELECT * FROM `mysql`.`db`' + . $user_host_condition . ' ORDER BY `Db` ASC'; + } elseif ($type == 'table') { + $sql_query = 'SELECT `Table_name`,' + . ' `Table_priv`,' + . ' IF(`Column_priv` = _latin1 \'\', 0, 1)' + . ' AS \'Column_priv\'' + . ' FROM `mysql`.`tables_priv`' + . $user_host_condition + . ' ORDER BY `Table_name` ASC;'; + } else { + $sql_query = "SELECT `Routine_name`, `Proc_priv`" + . " FROM `mysql`.`procs_priv`" + . $user_host_condition + . " ORDER BY `Routine_name`"; + } + + $result = $this->dbi->query($sql_query); + + while ($row = $this->dbi->fetchAssoc($result)) { + if (isset($db_rights[$row[$dbOrTableName]])) { + $db_rights[$row[$dbOrTableName]] + = array_merge($db_rights[$row[$dbOrTableName]], $row); + } else { + $db_rights[$row[$dbOrTableName]] = $row; + } + if ($type == 'database') { + // there are db specific rights for this user + // so we can drop this db rights + $db_rights[$row['Db']]['can_delete'] = true; + } + } + $this->dbi->freeResult($result); + return $db_rights; + } + + /** + * Parses Proc_priv data + * + * @param string $privs Proc_priv + * + * @return array + */ + public function parseProcPriv($privs) + { + $result = [ + 'Alter_routine_priv' => 'N', + 'Execute_priv' => 'N', + 'Grant_priv' => 'N', + ]; + foreach (explode(',', (string) $privs) as $priv) { + if ($priv == 'Alter Routine') { + $result['Alter_routine_priv'] = 'Y'; + } else { + $result[$priv . '_priv'] = 'Y'; + } + } + return $result; + } + + /** + * Get a HTML table for display user's tabel specific or database specific rights + * + * @param string $username username + * @param string $hostname host name + * @param string $type database, table or routine + * @param string $dbname database name + * + * @return string + */ + public function getHtmlForAllTableSpecificRights( + $username, + $hostname, + $type, + $dbname = '' + ) { + $uiData = [ + 'database' => [ + 'form_id' => 'database_specific_priv', + 'sub_menu_label' => __('Database'), + 'legend' => __('Database-specific privileges'), + 'type_label' => __('Database'), + ], + 'table' => [ + 'form_id' => 'table_specific_priv', + 'sub_menu_label' => __('Table'), + 'legend' => __('Table-specific privileges'), + 'type_label' => __('Table'), + ], + 'routine' => [ + 'form_id' => 'routine_specific_priv', + 'sub_menu_label' => __('Routine'), + 'legend' => __('Routine-specific privileges'), + 'type_label' => __('Routine'), + ], + ]; + + /** + * no db name given, so we want all privs for the given user + * db name was given, so we want all user specific rights for this db + */ + $db_rights = $this->getUserSpecificRights($username, $hostname, $type, $dbname); + ksort($db_rights); + + $foundRows = []; + $privileges = []; + foreach ($db_rights as $row) { + $onePrivilege = []; + + $paramTableName = ''; + $paramRoutineName = ''; + + if ($type == 'database') { + $name = $row['Db']; + $onePrivilege['grant'] = $row['Grant_priv'] == 'Y'; + $onePrivilege['table_privs'] = ! empty($row['Table_priv']) + || ! empty($row['Column_priv']); + $onePrivilege['privileges'] = implode(',', $this->extractPrivInfo($row, true)); + + $paramDbName = $row['Db']; + } elseif ($type == 'table') { + $name = $row['Table_name']; + $onePrivilege['grant'] = in_array( + 'Grant', + explode(',', $row['Table_priv']) + ); + $onePrivilege['column_privs'] = ! empty($row['Column_priv']); + $onePrivilege['privileges'] = implode(',', $this->extractPrivInfo($row, true)); + + $paramDbName = $dbname; + $paramTableName = $row['Table_name']; + } else { // routine + $name = $row['Routine_name']; + $onePrivilege['grant'] = in_array( + 'Grant', + explode(',', $row['Proc_priv']) + ); + + $privs = $this->parseProcPriv($row['Proc_priv']); + $onePrivilege['privileges'] = implode( + ',', + $this->extractPrivInfo($privs, true) + ); + + $paramDbName = $dbname; + $paramRoutineName = $row['Routine_name']; + } + + $foundRows[] = $name; + $onePrivilege['name'] = $name; + + $onePrivilege['edit_link'] = ''; + if ($GLOBALS['is_grantuser']) { + $onePrivilege['edit_link'] = $this->getUserLink( + 'edit', + $username, + $hostname, + $paramDbName, + $paramTableName, + $paramRoutineName + ); + } + + $onePrivilege['revoke_link'] = ''; + if ($type != 'database' || ! empty($row['can_delete'])) { + $onePrivilege['revoke_link'] = $this->getUserLink( + 'revoke', + $username, + $hostname, + $paramDbName, + $paramTableName, + $paramRoutineName + ); + } + + $privileges[] = $onePrivilege; + } + + $data = $uiData[$type]; + $data['privileges'] = $privileges; + $data['username'] = $username; + $data['hostname'] = $hostname; + $data['database'] = $dbname; + $data['type'] = $type; + + if ($type == 'database') { + // we already have the list of databases from libraries/common.inc.php + // via $pma = new PMA; + $pred_db_array = $GLOBALS['dblist']->databases; + $databases_to_skip = [ + 'information_schema', + 'performance_schema', + ]; + + $databases = []; + if (! empty($pred_db_array)) { + foreach ($pred_db_array as $current_db) { + if (in_array($current_db, $databases_to_skip)) { + continue; + } + $current_db_escaped = Util::escapeMysqlWildcards($current_db); + // cannot use array_diff() once, outside of the loop, + // because the list of databases has special characters + // already escaped in $foundRows, + // contrary to the output of SHOW DATABASES + if (! in_array($current_db_escaped, $foundRows)) { + $databases[] = $current_db; + } + } + } + $data['databases'] = $databases; + } elseif ($type == 'table') { + $result = @$this->dbi->tryQuery( + "SHOW TABLES FROM " . Util::backquote($dbname), + DatabaseInterface::CONNECT_USER, + DatabaseInterface::QUERY_STORE + ); + + $tables = []; + if ($result) { + while ($row = $this->dbi->fetchRow($result)) { + if (! in_array($row[0], $foundRows)) { + $tables[] = $row[0]; + } + } + $this->dbi->freeResult($result); + } + $data['tables'] = $tables; + } else { // routine + $routineData = $this->dbi->getRoutines($dbname); + + $routines = []; + foreach ($routineData as $routine) { + if (! in_array($routine['name'], $foundRows)) { + $routines[] = $routine['name']; + } + } + $data['routines'] = $routines; + } + + return $this->template->render('server/privileges/privileges_summary', $data); + } + + /** + * Get HTML for display the users overview + * (if less than 50 users, display them immediately) + * + * @param array $result ran sql query + * @param array $db_rights user's database rights array + * @param string $pmaThemeImage a image source link + * @param string $text_dir text directory + * + * @return string HTML snippet + */ + public function getUsersOverview($result, array $db_rights, $pmaThemeImage, $text_dir) + { + while ($row = $this->dbi->fetchAssoc($result)) { + $row['privs'] = $this->extractPrivInfo($row, true); + $db_rights[$row['User']][$row['Host']] = $row; + } + $this->dbi->freeResult($result); + $user_group_count = 0; + if ($GLOBALS['cfgRelation']['menuswork']) { + $user_group_count = $this->getUserGroupCount(); + } + + $html_output + = '<form name="usersForm" id="usersForm" action="server_privileges.php" ' + . 'method="post">' . "\n" + . Url::getHiddenInputs('', '') + . '<div class="responsivetable">' + . '<table id="tableuserrights" class="data">' . "\n" + . '<thead>' . "\n" + . '<tr><th></th>' . "\n" + . '<th>' . __('User name') . '</th>' . "\n" + . '<th>' . __('Host name') . '</th>' . "\n" + . '<th>' . __('Password') . '</th>' . "\n" + . '<th>' . __('Global privileges') . ' ' + . Util::showHint( + __('Note: MySQL privilege names are expressed in English.') + ) + . '</th>' . "\n"; + if ($GLOBALS['cfgRelation']['menuswork']) { + $html_output .= '<th>' . __('User group') . '</th>' . "\n"; + } + $html_output .= '<th>' . __('Grant') . '</th>' . "\n" + . '<th colspan="' . ($user_group_count > 0 ? '3' : '2') . '">' + . __('Action') . '</th>' . "\n" + . '</tr>' . "\n" + . '</thead>' . "\n"; + + $html_output .= '<tbody>' . "\n"; + $html_output .= $this->getHtmlTableBodyForUserRights($db_rights); + $html_output .= '</tbody>' + . '</table></div>' . "\n"; + + $html_output .= '<div class="floatleft">' + . $this->template->render('select_all', [ + 'pma_theme_image' => $pmaThemeImage, + 'text_dir' => $text_dir, + 'form_name' => 'usersForm', + ]) . "\n"; + $html_output .= Util::getButtonOrImage( + 'submit_mult', + 'mult_submit', + __('Export'), + 'b_tblexport', + 'export' + ); + $html_output .= '<input type="hidden" name="initial" ' + . 'value="' . (isset($_GET['initial']) ? htmlspecialchars($_GET['initial']) : '') . '">'; + $html_output .= '</div>' + . '<div class="clearfloat"></div>'; + + // add/delete user fieldset + $html_output .= $this->getFieldsetForAddDeleteUser(); + $html_output .= '</form>' . "\n"; + + return $html_output; + } + + /** + * Get table body for 'tableuserrights' table in userform + * + * @param array $db_rights user's database rights array + * + * @return string HTML snippet + */ + public function getHtmlTableBodyForUserRights(array $db_rights) + { + $cfgRelation = $this->relation->getRelationsParam(); + $user_group_count = 0; + if ($cfgRelation['menuswork']) { + $users_table = Util::backquote($cfgRelation['db']) + . "." . Util::backquote($cfgRelation['users']); + $sql_query = 'SELECT * FROM ' . $users_table; + $result = $this->relation->queryAsControlUser($sql_query, false); + $group_assignment = []; + if ($result) { + while ($row = $this->dbi->fetchAssoc($result)) { + $group_assignment[$row['username']] = $row['usergroup']; + } + } + $this->dbi->freeResult($result); + + $user_group_count = $this->getUserGroupCount(); + } + + $index_checkbox = 0; + $html_output = ''; + foreach ($db_rights as $user) { + ksort($user); + foreach ($user as $host) { + $index_checkbox++; + $html_output .= '<tr>' + . "\n"; + $html_output .= '<td>' + . '<input type="checkbox" class="checkall" name="selected_usr[]" ' + . 'id="checkbox_sel_users_' + . $index_checkbox . '" value="' + . htmlspecialchars($host['User'] . '&#27;' . $host['Host']) + . '"' + . '></td>' . "\n"; + + $html_output .= '<td><label ' + . 'for="checkbox_sel_users_' . $index_checkbox . '">' + . (empty($host['User']) + ? '<span style="color: #FF0000">' . __('Any') . '</span>' + : htmlspecialchars($host['User'])) . '</label></td>' . "\n" + . '<td>' . htmlspecialchars($host['Host']) . '</td>' . "\n"; + + $html_output .= '<td>'; + + $password_column = 'Password'; + + $check_plugin_query = "SELECT * FROM `mysql`.`user` WHERE " + . "`User` = '" . $host['User'] . "' AND `Host` = '" + . $host['Host'] . "'"; + $res = $this->dbi->fetchSingleRow($check_plugin_query); + + if ((isset($res['authentication_string']) + && ! empty($res['authentication_string'])) + || (isset($res['Password']) + && ! empty($res['Password'])) + ) { + $host[$password_column] = 'Y'; + } else { + $host[$password_column] = 'N'; + } + + switch ($host[$password_column]) { + case 'Y': + $html_output .= __('Yes'); + break; + case 'N': + $html_output .= '<span style="color: #FF0000">' . __('No') + . '</span>'; + break; + // this happens if this is a definition not coming from mysql.user + default: + $html_output .= '--'; // in future version, replace by "not present" + break; + } // end switch + + if (! isset($host['Select_priv'])) { + $html_output .= Util::showHint( + __('The selected user was not found in the privilege table.') + ); + } + + $html_output .= '</td>' . "\n"; + + $html_output .= '<td><code>' . "\n" + . '' . implode(',' . "\n" . ' ', $host['privs']) . "\n" + . '</code></td>' . "\n"; + if ($cfgRelation['menuswork']) { + $html_output .= '<td class="usrGroup">' . "\n" + . (isset($group_assignment[$host['User']]) + ? htmlspecialchars($group_assignment[$host['User']]) + : '' + ) + . '</td>' . "\n"; + } + $html_output .= '<td>' + . ($host['Grant_priv'] == 'Y' ? __('Yes') : __('No')) + . '</td>' . "\n"; + + if ($GLOBALS['is_grantuser']) { + $html_output .= '<td class="center">' + . $this->getUserLink( + 'edit', + $host['User'], + $host['Host'] + ) + . '</td>'; + } + if ($cfgRelation['menuswork'] && $user_group_count > 0) { + if (empty($host['User'])) { + $html_output .= '<td class="center"></td>'; + } else { + $html_output .= '<td class="center">' + . $this->getUserGroupEditLink($host['User']) + . '</td>'; + } + } + $html_output .= '<td class="center">' + . $this->getUserLink( + 'export', + $host['User'], + $host['Host'], + '', + '', + '', + isset($_GET['initial']) ? $_GET['initial'] : '' + ) + . '</td>'; + $html_output .= '</tr>'; + } + } + return $html_output; + } + + /** + * Get HTML fieldset for Add/Delete user + * + * @return string HTML snippet + */ + public function getFieldsetForAddDeleteUser() + { + $html_output = $this->getAddUserHtmlFieldset(); + + $html_output .= $this->template->render('server/privileges/delete_user_fieldset'); + + return $html_output; + } + + /** + * Get HTML for Displays the initials + * + * @param array $array_initials array for all initials, even non A-Z + * + * @return string HTML snippet + */ + public function getHtmlForInitials(array $array_initials) + { + // initialize to false the letters A-Z + for ($letter_counter = 1; $letter_counter < 27; $letter_counter++) { + if (! isset($array_initials[mb_chr($letter_counter + 64)])) { + $array_initials[mb_chr($letter_counter + 64)] = false; + } + } + + $initials = $this->dbi->tryQuery( + 'SELECT DISTINCT UPPER(LEFT(`User`,1)) FROM `user`' + . ' ORDER BY UPPER(LEFT(`User`,1)) ASC', + DatabaseInterface::CONNECT_USER, + DatabaseInterface::QUERY_STORE + ); + if ($initials) { + while (list($tmp_initial) = $this->dbi->fetchRow($initials)) { + $array_initials[$tmp_initial] = true; + } + } + + // Display the initials, which can be any characters, not + // just letters. For letters A-Z, we add the non-used letters + // as greyed out. + + uksort($array_initials, "strnatcasecmp"); + + return $this->template->render('server/privileges/initials_row', [ + 'array_initials' => $array_initials, + 'initial' => isset($_GET['initial']) ? $_GET['initial'] : null, + ]); + } + + /** + * Get the database rights array for Display user overview + * + * @return array database rights array + */ + public function getDbRightsForUserOverview() + { + // we also want users not in table `user` but in other table + $tables = $this->dbi->fetchResult('SHOW TABLES FROM `mysql`;'); + + $tablesSearchForUsers = [ + 'user', + 'db', + 'tables_priv', + 'columns_priv', + 'procs_priv', + ]; + + $db_rights_sqls = []; + foreach ($tablesSearchForUsers as $table_search_in) { + if (in_array($table_search_in, $tables)) { + $db_rights_sqls[] = 'SELECT DISTINCT `User`, `Host` FROM `mysql`.`' + . $table_search_in . '` ' + . (isset($_GET['initial']) + ? $this->rangeOfUsers($_GET['initial']) + : ''); + } + } + $user_defaults = [ + 'User' => '', + 'Host' => '%', + 'Password' => '?', + 'Grant_priv' => 'N', + 'privs' => ['USAGE'], + ]; + + // for the rights + $db_rights = []; + + $db_rights_sql = '(' . implode(') UNION (', $db_rights_sqls) . ')' + . ' ORDER BY `User` ASC, `Host` ASC'; + + $db_rights_result = $this->dbi->query($db_rights_sql); + + while ($db_rights_row = $this->dbi->fetchAssoc($db_rights_result)) { + $db_rights_row = array_merge($user_defaults, $db_rights_row); + $db_rights[$db_rights_row['User']][$db_rights_row['Host']] + = $db_rights_row; + } + $this->dbi->freeResult($db_rights_result); + ksort($db_rights); + + return $db_rights; + } + + /** + * Delete user and get message and sql query for delete user in privileges + * + * @param array $queries queries + * + * @return array Message + */ + public function deleteUser(array $queries) + { + $sql_query = ''; + if (empty($queries)) { + $message = Message::error(__('No users selected for deleting!')); + } else { + if ($_POST['mode'] == 3) { + $queries[] = '# ' . __('Reloading the privileges') . ' …'; + $queries[] = 'FLUSH PRIVILEGES;'; + } + $drop_user_error = ''; + foreach ($queries as $sql_query) { + if ($sql_query[0] != '#') { + if (! $this->dbi->tryQuery($sql_query)) { + $drop_user_error .= $this->dbi->getError() . "\n"; + } + } + } + // tracking sets this, causing the deleted db to be shown in navi + unset($GLOBALS['db']); + + $sql_query = implode("\n", $queries); + if (! empty($drop_user_error)) { + $message = Message::rawError($drop_user_error); + } else { + $message = Message::success( + __('The selected users have been deleted successfully.') + ); + } + } + return [ + $sql_query, + $message, + ]; + } + + /** + * Update the privileges and return the success or error message + * + * @param string $username username + * @param string $hostname host name + * @param string $tablename table name + * @param string $dbname database name + * @param string $itemType item type + * + * @return array success message or error message for update + */ + public function updatePrivileges($username, $hostname, $tablename, $dbname, $itemType) + { + $db_and_table = $this->wildcardEscapeForGrant($dbname, $tablename); + + $sql_query0 = 'REVOKE ALL PRIVILEGES ON ' . $itemType . ' ' . $db_and_table + . ' FROM \'' . $this->dbi->escapeString($username) + . '\'@\'' . $this->dbi->escapeString($hostname) . '\';'; + + if (! isset($_POST['Grant_priv']) || $_POST['Grant_priv'] != 'Y') { + $sql_query1 = 'REVOKE GRANT OPTION ON ' . $itemType . ' ' . $db_and_table + . ' FROM \'' . $this->dbi->escapeString($username) . '\'@\'' + . $this->dbi->escapeString($hostname) . '\';'; + } else { + $sql_query1 = ''; + } + + // Should not do a GRANT USAGE for a table-specific privilege, it + // causes problems later (cannot revoke it) + if (! (strlen($tablename) > 0 + && 'USAGE' == implode('', $this->extractPrivInfo())) + ) { + $sql_query2 = 'GRANT ' . implode(', ', $this->extractPrivInfo()) + . ' ON ' . $itemType . ' ' . $db_and_table + . ' TO \'' . $this->dbi->escapeString($username) . '\'@\'' + . $this->dbi->escapeString($hostname) . '\''; + + if (strlen($dbname) === 0) { + // add REQUIRE clause + $sql_query2 .= $this->getRequireClause(); + } + + if ((isset($_POST['Grant_priv']) && $_POST['Grant_priv'] == 'Y') + || (strlen($dbname) === 0 + && (isset($_POST['max_questions']) || isset($_POST['max_connections']) + || isset($_POST['max_updates']) + || isset($_POST['max_user_connections']))) + ) { + $sql_query2 .= $this->getWithClauseForAddUserAndUpdatePrivs(); + } + $sql_query2 .= ';'; + } + if (! $this->dbi->tryQuery($sql_query0)) { + // This might fail when the executing user does not have + // ALL PRIVILEGES himself. + // See https://github.com/phpmyadmin/phpmyadmin/issues/9673 + $sql_query0 = ''; + } + if (! empty($sql_query1) && ! $this->dbi->tryQuery($sql_query1)) { + // this one may fail, too... + $sql_query1 = ''; + } + if (! empty($sql_query2)) { + $this->dbi->query($sql_query2); + } else { + $sql_query2 = ''; + } + $sql_query = $sql_query0 . ' ' . $sql_query1 . ' ' . $sql_query2; + $message = Message::success(__('You have updated the privileges for %s.')); + $message->addParam('\'' . $username . '\'@\'' . $hostname . '\''); + + return [ + $sql_query, + $message, + ]; + } + + /** + * Get List of information: Changes / copies a user + * + * @return array + */ + public function getDataForChangeOrCopyUser() + { + $queries = null; + $password = null; + + if (isset($_POST['change_copy'])) { + $user_host_condition = ' WHERE `User` = ' + . "'" . $this->dbi->escapeString($_POST['old_username']) . "'" + . ' AND `Host` = ' + . "'" . $this->dbi->escapeString($_POST['old_hostname']) . "';"; + $row = $this->dbi->fetchSingleRow( + 'SELECT * FROM `mysql`.`user` ' . $user_host_condition + ); + if (! $row) { + $response = Response::getInstance(); + $response->addHTML( + Message::notice(__('No user found.'))->getDisplay() + ); + unset($_POST['change_copy']); + } else { + foreach ($row as $key => $value) { + $GLOBALS[$key] = $value; + } + $serverVersion = $this->dbi->getVersion(); + // Recent MySQL versions have the field "Password" in mysql.user, + // so the previous extract creates $row['Password'] but this script + // uses $password + if (! isset($row['password']) && isset($row['Password'])) { + $row['password'] = $row['Password']; + } + if (Util::getServerType() == 'MySQL' + && $serverVersion >= 50606 + && $serverVersion < 50706 + && ((isset($row['authentication_string']) + && empty($row['password'])) + || (isset($row['plugin']) + && $row['plugin'] == 'sha256_password')) + ) { + $row['password'] = $row['authentication_string']; + } + + if (Util::getServerType() == 'MariaDB' + && $serverVersion >= 50500 + && isset($row['authentication_string']) + && empty($row['password']) + ) { + $row['password'] = $row['authentication_string']; + } + + // Always use 'authentication_string' column + // for MySQL 5.7.6+ since it does not have + // the 'password' column at all + if (in_array(Util::getServerType(), ['MySQL', 'Percona Server']) + && $serverVersion >= 50706 + && isset($row['authentication_string']) + ) { + $row['password'] = $row['authentication_string']; + } + $password = $row['password']; + $queries = []; + } + } + + return [ + $queries, + $password, + ]; + } + + /** + * Update Data for information: Deletes users + * + * @param array $queries queries array + * + * @return array + */ + public function getDataForDeleteUsers($queries) + { + if (isset($_POST['change_copy'])) { + $selected_usr = [ + $_POST['old_username'] . '&#27;' . $_POST['old_hostname'], + ]; + } else { + $selected_usr = $_POST['selected_usr']; + $queries = []; + } + + // this happens, was seen in https://reports.phpmyadmin.net/reports/view/17146 + if (! is_array($selected_usr)) { + return []; + } + + foreach ($selected_usr as $each_user) { + list($this_user, $this_host) = explode('&#27;', $each_user); + $queries[] = '# ' + . sprintf( + __('Deleting %s'), + '\'' . $this_user . '\'@\'' . $this_host . '\'' + ) + . ' ...'; + $queries[] = 'DROP USER \'' + . $this->dbi->escapeString($this_user) + . '\'@\'' . $this->dbi->escapeString($this_host) . '\';'; + $this->relationCleanup->user($this_user); + + if (isset($_POST['drop_users_db'])) { + $queries[] = 'DROP DATABASE IF EXISTS ' + . Util::backquote($this_user) . ';'; + $GLOBALS['reload'] = true; + } + } + return $queries; + } + + /** + * update Message For Reload + * + * @return Message|null + */ + public function updateMessageForReload(): ?Message + { + $message = null; + if (isset($_GET['flush_privileges'])) { + $sql_query = 'FLUSH PRIVILEGES;'; + $this->dbi->query($sql_query); + $message = Message::success( + __('The privileges were reloaded successfully.') + ); + } + + if (isset($_GET['validate_username'])) { + $message = Message::success(); + } + + return $message; + } + + /** + * update Data For Queries from queries_for_display + * + * @param array $queries queries array + * @param array|null $queries_for_display queries array for display + * + * @return array + */ + public function getDataForQueries(array $queries, $queries_for_display) + { + $tmp_count = 0; + foreach ($queries as $sql_query) { + if ($sql_query[0] != '#') { + $this->dbi->query($sql_query); + } + // when there is a query containing a hidden password, take it + // instead of the real query sent + if (isset($queries_for_display[$tmp_count])) { + $queries[$tmp_count] = $queries_for_display[$tmp_count]; + } + $tmp_count++; + } + + return $queries; + } + + /** + * update Data for information: Adds a user + * + * @param string|array|null $dbname db name + * @param string $username user name + * @param string $hostname host name + * @param string|null $password password + * @param bool $is_menuwork is_menuwork set? + * + * @return array + */ + public function addUser( + $dbname, + $username, + $hostname, + ?string $password, + $is_menuwork + ) { + $_add_user_error = false; + $message = null; + $queries = null; + $queries_for_display = null; + $sql_query = null; + + if (! isset($_POST['adduser_submit']) && ! isset($_POST['change_copy'])) { + return [ + $message, + $queries, + $queries_for_display, + $sql_query, + $_add_user_error, + ]; + } + + $sql_query = ''; + if ($_POST['pred_username'] == 'any') { + $username = ''; + } + switch ($_POST['pred_hostname']) { + case 'any': + $hostname = '%'; + break; + case 'localhost': + $hostname = 'localhost'; + break; + case 'hosttable': + $hostname = ''; + break; + case 'thishost': + $_user_name = $this->dbi->fetchValue('SELECT USER()'); + $hostname = mb_substr( + $_user_name, + mb_strrpos($_user_name, '@') + 1 + ); + unset($_user_name); + break; + } + $sql = "SELECT '1' FROM `mysql`.`user`" + . " WHERE `User` = '" . $this->dbi->escapeString($username) . "'" + . " AND `Host` = '" . $this->dbi->escapeString($hostname) . "';"; + if ($this->dbi->fetchValue($sql) == 1) { + $message = Message::error(__('The user %s already exists!')); + $message->addParam('[em]\'' . $username . '\'@\'' . $hostname . '\'[/em]'); + $_GET['adduser'] = true; + $_add_user_error = true; + + return [ + $message, + $queries, + $queries_for_display, + $sql_query, + $_add_user_error, + ]; + } + + list( + $create_user_real, + $create_user_show, + $real_sql_query, + $sql_query, + $password_set_real, + $password_set_show, + $alter_real_sql_query, + $alter_sql_query + ) = $this->getSqlQueriesForDisplayAndAddUser( + $username, + $hostname, + (isset($password) ? $password : '') + ); + + if (empty($_POST['change_copy'])) { + $_error = false; + + if ($create_user_real !== null) { + if (! $this->dbi->tryQuery($create_user_real)) { + $_error = true; + } + if (isset($password_set_real) && ! empty($password_set_real) + && isset($_POST['authentication_plugin']) + ) { + $this->setProperPasswordHashing( + $_POST['authentication_plugin'] + ); + if ($this->dbi->tryQuery($password_set_real)) { + $sql_query .= $password_set_show; + } + } + $sql_query = $create_user_show . $sql_query; + } + + list($sql_query, $message) = $this->addUserAndCreateDatabase( + $_error, + $real_sql_query, + $sql_query, + $username, + $hostname, + $dbname, + $alter_real_sql_query, + $alter_sql_query + ); + if (! empty($_POST['userGroup']) && $is_menuwork) { + $this->setUserGroup($GLOBALS['username'], $_POST['userGroup']); + } + + return [ + $message, + $queries, + $queries_for_display, + $sql_query, + $_add_user_error, + ]; + } + + // Copy the user group while copying a user + $old_usergroup = + isset($_POST['old_usergroup']) ? $_POST['old_usergroup'] : null; + $this->setUserGroup($_POST['username'], $old_usergroup); + + if ($create_user_real === null) { + $queries[] = $create_user_real; + } + $queries[] = $real_sql_query; + + if (isset($password_set_real) && ! empty($password_set_real) + && isset($_POST['authentication_plugin']) + ) { + $this->setProperPasswordHashing( + $_POST['authentication_plugin'] + ); + + $queries[] = $password_set_real; + } + // we put the query containing the hidden password in + // $queries_for_display, at the same position occupied + // by the real query in $queries + $tmp_count = count($queries); + if (isset($create_user_real)) { + $queries_for_display[$tmp_count - 2] = $create_user_show; + } + if (isset($password_set_real) && ! empty($password_set_real)) { + $queries_for_display[$tmp_count - 3] = $create_user_show; + $queries_for_display[$tmp_count - 2] = $sql_query; + $queries_for_display[$tmp_count - 1] = $password_set_show; + } else { + $queries_for_display[$tmp_count - 1] = $sql_query; + } + + return [ + $message, + $queries, + $queries_for_display, + $sql_query, + $_add_user_error, + ]; + } + + /** + * Sets proper value of `old_passwords` according to + * the authentication plugin selected + * + * @param string $auth_plugin authentication plugin selected + * + * @return void + */ + public function setProperPasswordHashing($auth_plugin) + { + // Set the hashing method used by PASSWORD() + // to be of type depending upon $authentication_plugin + if ($auth_plugin == 'sha256_password') { + $this->dbi->tryQuery('SET `old_passwords` = 2;'); + } elseif ($auth_plugin == 'mysql_old_password') { + $this->dbi->tryQuery('SET `old_passwords` = 1;'); + } else { + $this->dbi->tryQuery('SET `old_passwords` = 0;'); + } + } + + /** + * Update DB information: DB, Table, isWildcard + * + * @return array + */ + public function getDataForDBInfo() + { + $username = null; + $hostname = null; + $dbname = null; + $tablename = null; + $routinename = null; + $dbname_is_wildcard = null; + + if (isset($_REQUEST['username'])) { + $username = $_REQUEST['username']; + } + if (isset($_REQUEST['hostname'])) { + $hostname = $_REQUEST['hostname']; + } + /** + * Checks if a dropdown box has been used for selecting a database / table + */ + if (Core::isValid($_POST['pred_tablename'])) { + $tablename = $_POST['pred_tablename']; + } elseif (Core::isValid($_REQUEST['tablename'])) { + $tablename = $_REQUEST['tablename']; + } else { + unset($tablename); + } + + if (Core::isValid($_POST['pred_routinename'])) { + $routinename = $_POST['pred_routinename']; + } elseif (Core::isValid($_REQUEST['routinename'])) { + $routinename = $_REQUEST['routinename']; + } else { + unset($routinename); + } + + if (isset($_POST['pred_dbname'])) { + $is_valid_pred_dbname = true; + foreach ($_POST['pred_dbname'] as $key => $db_name) { + if (! Core::isValid($db_name)) { + $is_valid_pred_dbname = false; + break; + } + } + } + + if (isset($_REQUEST['dbname'])) { + $is_valid_dbname = true; + if (is_array($_REQUEST['dbname'])) { + foreach ($_REQUEST['dbname'] as $key => $db_name) { + if (! Core::isValid($db_name)) { + $is_valid_dbname = false; + break; + } + } + } else { + if (! Core::isValid($_REQUEST['dbname'])) { + $is_valid_dbname = false; + } + } + } + + if (isset($is_valid_pred_dbname) && $is_valid_pred_dbname) { + $dbname = $_POST['pred_dbname']; + // If dbname contains only one database. + if (count($dbname) === 1) { + $dbname = $dbname[0]; + } + } elseif (isset($is_valid_dbname) && $is_valid_dbname) { + $dbname = $_REQUEST['dbname']; + } else { + unset($dbname); + unset($tablename); + } + + if (isset($dbname)) { + if (is_array($dbname)) { + $db_and_table = $dbname; + foreach ($db_and_table as $key => $db_name) { + $db_and_table[$key] .= '.'; + } + } else { + $unescaped_db = Util::unescapeMysqlWildcards($dbname); + $db_and_table = Util::backquote($unescaped_db) . '.'; + } + if (isset($tablename)) { + $db_and_table .= Util::backquote($tablename); + } else { + if (is_array($db_and_table)) { + foreach ($db_and_table as $key => $db_name) { + $db_and_table[$key] .= '*'; + } + } else { + $db_and_table .= '*'; + } + } + } else { + $db_and_table = '*.*'; + } + + // check if given $dbname is a wildcard or not + if (isset($dbname)) { + //if (preg_match('/\\\\(?:_|%)/i', $dbname)) { + if (! is_array($dbname) && preg_match('/(?<!\\\\)(?:_|%)/', $dbname)) { + $dbname_is_wildcard = true; + } else { + $dbname_is_wildcard = false; + } + } + + return [ + $username, + $hostname, + isset($dbname) ? $dbname : null, + isset($tablename) ? $tablename : null, + isset($routinename) ? $routinename : null, + $db_and_table, + $dbname_is_wildcard, + ]; + } + + /** + * Get title and textarea for export user definition in Privileges + * + * @param string $username username + * @param string $hostname host name + * + * @return array ($title, $export) + */ + public function getListForExportUserDefinition($username, $hostname) + { + $export = '<textarea class="export" cols="60" rows="15">'; + + if (isset($_POST['selected_usr'])) { + // export privileges for selected users + $title = __('Privileges'); + + //For removing duplicate entries of users + $_POST['selected_usr'] = array_unique($_POST['selected_usr']); + + foreach ($_POST['selected_usr'] as $export_user) { + $export_username = mb_substr( + $export_user, + 0, + mb_strpos($export_user, '&') + ); + $export_hostname = mb_substr( + $export_user, + mb_strrpos($export_user, ';') + 1 + ); + $export .= '# ' + . sprintf( + __('Privileges for %s'), + '`' . htmlspecialchars($export_username) + . '`@`' . htmlspecialchars($export_hostname) . '`' + ) + . "\n\n"; + $export .= $this->getGrants($export_username, $export_hostname) . "\n"; + } + } else { + // export privileges for a single user + $title = __('User') . ' `' . htmlspecialchars($username) + . '`@`' . htmlspecialchars($hostname) . '`'; + $export .= $this->getGrants($username, $hostname); + } + // remove trailing whitespace + $export = trim($export); + + $export .= '</textarea>'; + + return [ + $title, + $export, + ]; + } + + /** + * Get HTML for display Add userfieldset + * + * @param string $db the database + * @param string $table the table name + * + * @return string html output + */ + public function getAddUserHtmlFieldset($db = '', $table = '') + { + if (! $GLOBALS['is_createuser']) { + return ''; + } + $rel_params = []; + $url_params = [ + 'adduser' => 1, + ]; + if (! empty($db)) { + $url_params['dbname'] + = $rel_params['checkprivsdb'] + = $db; + } + if (! empty($table)) { + $url_params['tablename'] + = $rel_params['checkprivstable'] + = $table; + } + + return $this->template->render('server/privileges/add_user_fieldset', [ + 'url_params' => $url_params, + 'rel_params' => $rel_params, + ]); + } + + /** + * Get HTML header for display User's properties + * + * @param boolean $dbname_is_wildcard whether database name is wildcard or not + * @param string $url_dbname url database name that urlencode() string + * @param string $dbname database name + * @param string $username username + * @param string $hostname host name + * @param string $entity_name entity (table or routine) name + * @param string $entity_type optional, type of entity ('table' or 'routine') + * + * @return string + */ + public function getHtmlHeaderForUserProperties( + $dbname_is_wildcard, + $url_dbname, + $dbname, + $username, + $hostname, + $entity_name, + $entity_type = 'table' + ) { + $html_output = '<h2>' . "\n" + . Util::getIcon('b_usredit') + . __('Edit privileges:') . ' ' + . __('User account'); + + if (! empty($dbname)) { + $html_output .= ' <i><a class="edit_user_anchor"' + . ' href="server_privileges.php' + . Url::getCommon( + [ + 'username' => $username, + 'hostname' => $hostname, + 'dbname' => '', + 'tablename' => '', + ] + ) + . '">\'' . htmlspecialchars($username) + . '\'@\'' . htmlspecialchars($hostname) + . '\'</a></i>' . "\n"; + + $html_output .= ' - '; + $html_output .= $dbname_is_wildcard + || is_array($dbname) && count($dbname) > 1 + ? __('Databases') : __('Database'); + if (! empty($entity_name) && $entity_type === 'table') { + $html_output .= ' <i><a href="server_privileges.php' + . Url::getCommon( + [ + 'username' => $username, + 'hostname' => $hostname, + 'dbname' => $url_dbname, + 'tablename' => '', + ] + ) + . '">' . htmlspecialchars($dbname) + . '</a></i>'; + + $html_output .= ' - ' . __('Table') + . ' <i>' . htmlspecialchars($entity_name) . '</i>'; + } elseif (! empty($entity_name)) { + $html_output .= ' <i><a href="server_privileges.php' + . Url::getCommon( + [ + 'username' => $username, + 'hostname' => $hostname, + 'dbname' => $url_dbname, + 'routinename' => '', + ] + ) + . '">' . htmlspecialchars($dbname) + . '</a></i>'; + + $html_output .= ' - ' . __('Routine') + . ' <i>' . htmlspecialchars($entity_name) . '</i>'; + } else { + if (! is_array($dbname)) { + $dbname = [$dbname]; + } + $html_output .= ' <i>' + . htmlspecialchars(implode(', ', $dbname)) + . '</i>'; + } + } else { + $html_output .= ' <i>\'' . htmlspecialchars($username) + . '\'@\'' . htmlspecialchars($hostname) + . '\'</i>' . "\n"; + } + $html_output .= '</h2>' . "\n"; + $cur_user = $this->dbi->getCurrentUser(); + $user = $username . '@' . $hostname; + // Add a short notice for the user + // to remind him that he is editing his own privileges + if ($user === $cur_user) { + $html_output .= Message::notice( + __( + 'Note: You are attempting to edit privileges of the ' + . 'user with which you are currently logged in.' + ) + )->getDisplay(); + } + return $html_output; + } + + /** + * Get HTML snippet for display user overview page + * + * @param string $pmaThemeImage a image source link + * @param string $text_dir text directory + * + * @return string + */ + public function getHtmlForUserOverview($pmaThemeImage, $text_dir) + { + $html_output = '<h2>' . "\n" + . Util::getIcon('b_usrlist') + . __('User accounts overview') . "\n" + . '</h2>' . "\n"; + + $password_column = 'Password'; + $server_type = Util::getServerType(); + $serverVersion = $this->dbi->getVersion(); + if (($server_type == 'MySQL' || $server_type == 'Percona Server') + && $serverVersion >= 50706 + ) { + $password_column = 'authentication_string'; + } + // $sql_query is for the initial-filtered, + // $sql_query_all is for counting the total no. of users + + $sql_query = $sql_query_all = 'SELECT *,' . + " IF(`" . $password_column . "` = _latin1 '', 'N', 'Y') AS 'Password'" . + ' FROM `mysql`.`user`'; + + $sql_query .= (isset($_GET['initial']) + ? $this->rangeOfUsers($_GET['initial']) + : ''); + + $sql_query .= ' ORDER BY `User` ASC, `Host` ASC;'; + $sql_query_all .= ' ;'; + + $res = $this->dbi->tryQuery( + $sql_query, + DatabaseInterface::CONNECT_USER, + DatabaseInterface::QUERY_STORE + ); + $res_all = $this->dbi->tryQuery( + $sql_query_all, + DatabaseInterface::CONNECT_USER, + DatabaseInterface::QUERY_STORE + ); + + if (! $res) { + // the query failed! This may have two reasons: + // - the user does not have enough privileges + // - the privilege tables use a structure of an earlier version. + // so let's try a more simple query + + $this->dbi->freeResult($res); + $this->dbi->freeResult($res_all); + $sql_query = 'SELECT * FROM `mysql`.`user`'; + $res = $this->dbi->tryQuery( + $sql_query, + DatabaseInterface::CONNECT_USER, + DatabaseInterface::QUERY_STORE + ); + + if (! $res) { + $html_output .= $this->getHtmlForViewUsersError(); + $html_output .= $this->getAddUserHtmlFieldset(); + } else { + // This message is hardcoded because I will replace it by + // a automatic repair feature soon. + $raw = 'Your privilege table structure seems to be older than' + . ' this MySQL version!<br>' + . 'Please run the <code>mysql_upgrade</code> command' + . ' that should be included in your MySQL server distribution' + . ' to solve this problem!'; + $html_output .= Message::rawError($raw)->getDisplay(); + } + $this->dbi->freeResult($res); + } else { + $db_rights = $this->getDbRightsForUserOverview(); + // for all initials, even non A-Z + $array_initials = []; + + foreach ($db_rights as $right) { + foreach ($right as $account) { + if (empty($account['User']) && $account['Host'] == 'localhost') { + $html_output .= Message::notice( + __( + 'A user account allowing any user from localhost to ' + . 'connect is present. This will prevent other users ' + . 'from connecting if the host part of their account ' + . 'allows a connection from any (%) host.' + ) + . Util::showMySQLDocu('problems-connecting') + )->getDisplay(); + break 2; + } + } + } + + /** + * Displays the initials + * Also not necessary if there is less than 20 privileges + */ + if ($this->dbi->numRows($res_all) > 20) { + $html_output .= $this->getHtmlForInitials($array_initials); + } + + /** + * Display the user overview + * (if less than 50 users, display them immediately) + */ + if (isset($_GET['initial']) + || isset($_GET['showall']) + || $this->dbi->numRows($res) < 50 + ) { + $html_output .= $this->getUsersOverview( + $res, + $db_rights, + $pmaThemeImage, + $text_dir + ); + } else { + $html_output .= $this->getAddUserHtmlFieldset(); + } // end if (display overview) + + $response = Response::getInstance(); + if (! $response->isAjax() + || ! empty($_REQUEST['ajax_page_request']) + ) { + if ($GLOBALS['is_reload_priv']) { + $flushnote = new Message( + __( + 'Note: phpMyAdmin gets the users’ privileges directly ' + . 'from MySQL’s privilege tables. The content of these ' + . 'tables may differ from the privileges the server uses, ' + . 'if they have been changed manually. In this case, ' + . 'you should %sreload the privileges%s before you continue.' + ), + Message::NOTICE + ); + $flushnote->addParamHtml( + '<a href="server_privileges.php' + . Url::getCommon(['flush_privileges' => 1]) + . '" id="reload_privileges_anchor">' + ); + $flushnote->addParamHtml('</a>'); + } else { + $flushnote = new Message( + __( + 'Note: phpMyAdmin gets the users’ privileges directly ' + . 'from MySQL’s privilege tables. The content of these ' + . 'tables may differ from the privileges the server uses, ' + . 'if they have been changed manually. In this case, ' + . 'the privileges have to be reloaded but currently, you ' + . 'don\'t have the RELOAD privilege.' + ) + . Util::showMySQLDocu( + 'privileges-provided', + false, + null, + null, + 'priv_reload' + ), + Message::NOTICE + ); + } + $html_output .= $flushnote->getDisplay(); + } + } + + return $html_output; + } + + /** + * Get HTML snippet for display user properties + * + * @param boolean $dbname_is_wildcard whether database name is wildcard or not + * @param string $url_dbname url database name that urlencode() string + * @param string $username username + * @param string $hostname host name + * @param string $dbname database name + * @param string $tablename table name + * + * @return string + */ + public function getHtmlForUserProperties( + $dbname_is_wildcard, + $url_dbname, + $username, + $hostname, + $dbname, + $tablename + ) { + $html_output = '<div id="edit_user_dialog">'; + $html_output .= $this->getHtmlHeaderForUserProperties( + $dbname_is_wildcard, + $url_dbname, + $dbname, + $username, + $hostname, + $tablename, + 'table' + ); + + $sql = "SELECT '1' FROM `mysql`.`user`" + . " WHERE `User` = '" . $this->dbi->escapeString($username) . "'" + . " AND `Host` = '" . $this->dbi->escapeString($hostname) . "';"; + + $user_does_not_exists = (bool) ! $this->dbi->fetchValue($sql); + + if ($user_does_not_exists) { + $html_output .= Message::error( + __('The selected user was not found in the privilege table.') + )->getDisplay(); + $html_output .= $this->getHtmlForLoginInformationFields(); + } + + $_params = [ + 'username' => $username, + 'hostname' => $hostname, + ]; + if (! is_array($dbname) && strlen($dbname) > 0) { + $_params['dbname'] = $dbname; + if (strlen($tablename) > 0) { + $_params['tablename'] = $tablename; + } + } else { + $_params['dbname'] = $dbname; + } + + $html_output .= '<form class="submenu-item" name="usersForm" ' + . 'id="addUsersForm" action="server_privileges.php" method="post">' . "\n"; + $html_output .= Url::getHiddenInputs($_params); + $html_output .= $this->getHtmlToDisplayPrivilegesTable( + // If $dbname is an array, pass any one db as all have same privs. + Core::ifSetOr($dbname, is_array($dbname) ? $dbname[0] : '*', 'length'), + Core::ifSetOr($tablename, '*', 'length') + ); + + $html_output .= '</form>' . "\n"; + + if (! is_array($dbname) && strlen($tablename) === 0 + && empty($dbname_is_wildcard) + ) { + // no table name was given, display all table specific rights + // but only if $dbname contains no wildcards + if (strlen($dbname) === 0) { + $html_output .= $this->getHtmlForAllTableSpecificRights( + $username, + $hostname, + 'database' + ); + } else { + // unescape wildcards in dbname at table level + $unescaped_db = Util::unescapeMysqlWildcards($dbname); + + $html_output .= $this->getHtmlForAllTableSpecificRights( + $username, + $hostname, + 'table', + $unescaped_db + ); + $html_output .= $this->getHtmlForAllTableSpecificRights( + $username, + $hostname, + 'routine', + $unescaped_db + ); + } + } + + // Provide a line with links to the relevant database and table + if (! is_array($dbname) && strlen($dbname) > 0 && empty($dbname_is_wildcard)) { + $html_output .= $this->getLinkToDbAndTable($url_dbname, $dbname, $tablename); + } + + if (! is_array($dbname) && strlen($dbname) === 0 && ! $user_does_not_exists) { + //change login information + $html_output .= ChangePassword::getHtml( + 'edit_other', + $username, + $hostname + ); + $html_output .= $this->getChangeLoginInformationHtmlForm($username, $hostname); + } + $html_output .= '</div>'; + + return $html_output; + } + + /** + * Get queries for Table privileges to change or copy user + * + * @param string $user_host_condition user host condition to + * select relevant table privileges + * @param array $queries queries array + * @param string $username username + * @param string $hostname host name + * + * @return array + */ + public function getTablePrivsQueriesForChangeOrCopyUser( + $user_host_condition, + array $queries, + $username, + $hostname + ) { + $res = $this->dbi->query( + 'SELECT `Db`, `Table_name`, `Table_priv` FROM `mysql`.`tables_priv`' + . $user_host_condition, + DatabaseInterface::CONNECT_USER, + DatabaseInterface::QUERY_STORE + ); + while ($row = $this->dbi->fetchAssoc($res)) { + $res2 = $this->dbi->query( + 'SELECT `Column_name`, `Column_priv`' + . ' FROM `mysql`.`columns_priv`' + . ' WHERE `User`' + . ' = \'' . $this->dbi->escapeString($_POST['old_username']) . "'" + . ' AND `Host`' + . ' = \'' . $this->dbi->escapeString($_POST['old_username']) . '\'' + . ' AND `Db`' + . ' = \'' . $this->dbi->escapeString($row['Db']) . "'" + . ' AND `Table_name`' + . ' = \'' . $this->dbi->escapeString($row['Table_name']) . "'" + . ';', + DatabaseInterface::CONNECT_USER, + DatabaseInterface::QUERY_STORE + ); + + $tmp_privs1 = $this->extractPrivInfo($row); + $tmp_privs2 = [ + 'Select' => [], + 'Insert' => [], + 'Update' => [], + 'References' => [], + ]; + + while ($row2 = $this->dbi->fetchAssoc($res2)) { + $tmp_array = explode(',', $row2['Column_priv']); + if (in_array('Select', $tmp_array)) { + $tmp_privs2['Select'][] = $row2['Column_name']; + } + if (in_array('Insert', $tmp_array)) { + $tmp_privs2['Insert'][] = $row2['Column_name']; + } + if (in_array('Update', $tmp_array)) { + $tmp_privs2['Update'][] = $row2['Column_name']; + } + if (in_array('References', $tmp_array)) { + $tmp_privs2['References'][] = $row2['Column_name']; + } + } + if (count($tmp_privs2['Select']) > 0 && ! in_array('SELECT', $tmp_privs1)) { + $tmp_privs1[] = 'SELECT (`' . implode('`, `', $tmp_privs2['Select']) . '`)'; + } + if (count($tmp_privs2['Insert']) > 0 && ! in_array('INSERT', $tmp_privs1)) { + $tmp_privs1[] = 'INSERT (`' . implode('`, `', $tmp_privs2['Insert']) . '`)'; + } + if (count($tmp_privs2['Update']) > 0 && ! in_array('UPDATE', $tmp_privs1)) { + $tmp_privs1[] = 'UPDATE (`' . implode('`, `', $tmp_privs2['Update']) . '`)'; + } + if (count($tmp_privs2['References']) > 0 + && ! in_array('REFERENCES', $tmp_privs1) + ) { + $tmp_privs1[] + = 'REFERENCES (`' . implode('`, `', $tmp_privs2['References']) . '`)'; + } + + $queries[] = 'GRANT ' . implode(', ', $tmp_privs1) + . ' ON ' . Util::backquote($row['Db']) . '.' + . Util::backquote($row['Table_name']) + . ' TO \'' . $this->dbi->escapeString($username) + . '\'@\'' . $this->dbi->escapeString($hostname) . '\'' + . (in_array('Grant', explode(',', $row['Table_priv'])) + ? ' WITH GRANT OPTION;' + : ';'); + } + return $queries; + } + + /** + * Get queries for database specific privileges for change or copy user + * + * @param array $queries queries array with string + * @param string $username username + * @param string $hostname host name + * + * @return array + */ + public function getDbSpecificPrivsQueriesForChangeOrCopyUser( + array $queries, + $username, + $hostname + ) { + $user_host_condition = ' WHERE `User`' + . ' = \'' . $this->dbi->escapeString($_POST['old_username']) . "'" + . ' AND `Host`' + . ' = \'' . $this->dbi->escapeString($_POST['old_hostname']) . '\';'; + + $res = $this->dbi->query( + 'SELECT * FROM `mysql`.`db`' . $user_host_condition + ); + + while ($row = $this->dbi->fetchAssoc($res)) { + $queries[] = 'GRANT ' . implode(', ', $this->extractPrivInfo($row)) + . ' ON ' . Util::backquote($row['Db']) . '.*' + . ' TO \'' . $this->dbi->escapeString($username) + . '\'@\'' . $this->dbi->escapeString($hostname) . '\'' + . ($row['Grant_priv'] == 'Y' ? ' WITH GRANT OPTION;' : ';'); + } + $this->dbi->freeResult($res); + + $queries = $this->getTablePrivsQueriesForChangeOrCopyUser( + $user_host_condition, + $queries, + $username, + $hostname + ); + + return $queries; + } + + /** + * Prepares queries for adding users and + * also create database and return query and message + * + * @param boolean $_error whether user create or not + * @param string $real_sql_query SQL query for add a user + * @param string $sql_query SQL query to be displayed + * @param string $username username + * @param string $hostname host name + * @param string $dbname database name + * @param string $alter_real_sql_query SQL query for ALTER USER + * @param string $alter_sql_query SQL query for ALTER USER to be displayed + * + * @return array, $message + */ + public function addUserAndCreateDatabase( + $_error, + $real_sql_query, + $sql_query, + $username, + $hostname, + $dbname, + $alter_real_sql_query, + $alter_sql_query + ) { + if ($_error || (! empty($real_sql_query) + && ! $this->dbi->tryQuery($real_sql_query)) + ) { + $_POST['createdb-1'] = $_POST['createdb-2'] + = $_POST['createdb-3'] = null; + $message = Message::rawError($this->dbi->getError()); + } elseif ($alter_real_sql_query !== '' && ! $this->dbi->tryQuery($alter_real_sql_query)) { + $_POST['createdb-1'] = $_POST['createdb-2'] + = $_POST['createdb-3'] = null; + $message = Message::rawError($this->dbi->getError()); + } else { + $sql_query .= $alter_sql_query; + $message = Message::success(__('You have added a new user.')); + } + + if (isset($_POST['createdb-1'])) { + // Create database with same name and grant all privileges + $q = 'CREATE DATABASE IF NOT EXISTS ' + . Util::backquote( + $this->dbi->escapeString($username) + ) . ';'; + $sql_query .= $q; + if (! $this->dbi->tryQuery($q)) { + $message = Message::rawError($this->dbi->getError()); + } + + /** + * Reload the navigation + */ + $GLOBALS['reload'] = true; + $GLOBALS['db'] = $username; + + $q = 'GRANT ALL PRIVILEGES ON ' + . Util::backquote( + Util::escapeMysqlWildcards( + $this->dbi->escapeString($username) + ) + ) . '.* TO \'' + . $this->dbi->escapeString($username) + . '\'@\'' . $this->dbi->escapeString($hostname) . '\';'; + $sql_query .= $q; + if (! $this->dbi->tryQuery($q)) { + $message = Message::rawError($this->dbi->getError()); + } + } + + if (isset($_POST['createdb-2'])) { + // Grant all privileges on wildcard name (username\_%) + $q = 'GRANT ALL PRIVILEGES ON ' + . Util::backquote( + Util::escapeMysqlWildcards( + $this->dbi->escapeString($username) + ) . '\_%' + ) . '.* TO \'' + . $this->dbi->escapeString($username) + . '\'@\'' . $this->dbi->escapeString($hostname) . '\';'; + $sql_query .= $q; + if (! $this->dbi->tryQuery($q)) { + $message = Message::rawError($this->dbi->getError()); + } + } + + if (isset($_POST['createdb-3'])) { + // Grant all privileges on the specified database to the new user + $q = 'GRANT ALL PRIVILEGES ON ' + . Util::backquote( + $this->dbi->escapeString($dbname) + ) . '.* TO \'' + . $this->dbi->escapeString($username) + . '\'@\'' . $this->dbi->escapeString($hostname) . '\';'; + $sql_query .= $q; + if (! $this->dbi->tryQuery($q)) { + $message = Message::rawError($this->dbi->getError()); + } + } + return [ + $sql_query, + $message, + ]; + } + + /** + * Get the hashed string for password + * + * @param string $password password + * + * @return string + */ + public function getHashedPassword($password) + { + $password = $this->dbi->escapeString($password); + $result = $this->dbi->fetchSingleRow( + "SELECT PASSWORD('" . $password . "') AS `password`;" + ); + + return $result['password']; + } + + /** + * Check if MariaDB's 'simple_password_check' + * OR 'cracklib_password_check' is ACTIVE + * + * @return boolean if atleast one of the plugins is ACTIVE + */ + public function checkIfMariaDBPwdCheckPluginActive() + { + $serverVersion = $this->dbi->getVersion(); + if (! (Util::getServerType() == 'MariaDB' && $serverVersion >= 100002)) { + return false; + } + + $result = $this->dbi->tryQuery( + 'SHOW PLUGINS SONAME LIKE \'%_password_check%\'' + ); + + /* Plugins are not working, for example directory does not exists */ + if ($result === false) { + return false; + } + + while ($row = $this->dbi->fetchAssoc($result)) { + if ($row['Status'] === 'ACTIVE') { + return true; + } + } + + return false; + } + + + /** + * Get SQL queries for Display and Add user + * + * @param string $username username + * @param string $hostname host name + * @param string $password password + * + * @return array ($create_user_real, $create_user_show, $real_sql_query, $sql_query + * $password_set_real, $password_set_show, $alter_real_sql_query, $alter_sql_query) + */ + public function getSqlQueriesForDisplayAndAddUser($username, $hostname, $password) + { + $slashedUsername = $this->dbi->escapeString($username); + $slashedHostname = $this->dbi->escapeString($hostname); + $slashedPassword = $this->dbi->escapeString($password); + $serverType = Util::getServerType(); + $serverVersion = $this->dbi->getVersion(); + + $create_user_stmt = sprintf( + 'CREATE USER \'%s\'@\'%s\'', + $slashedUsername, + $slashedHostname + ); + $isMariaDBPwdPluginActive = $this->checkIfMariaDBPwdCheckPluginActive(); + + // See https://github.com/phpmyadmin/phpmyadmin/pull/11560#issuecomment-147158219 + // for details regarding details of syntax usage for various versions + + // 'IDENTIFIED WITH auth_plugin' + // is supported by MySQL 5.5.7+ + if (($serverType == 'MySQL' || $serverType == 'Percona Server') + && $serverVersion >= 50507 + && isset($_POST['authentication_plugin']) + ) { + $create_user_stmt .= ' IDENTIFIED WITH ' + . $_POST['authentication_plugin']; + } + + // 'IDENTIFIED VIA auth_plugin' + // is supported by MariaDB 5.2+ + if ($serverType == 'MariaDB' + && $serverVersion >= 50200 + && isset($_POST['authentication_plugin']) + && ! $isMariaDBPwdPluginActive + ) { + $create_user_stmt .= ' IDENTIFIED VIA ' + . $_POST['authentication_plugin']; + } + + $create_user_real = $create_user_stmt; + $create_user_show = $create_user_stmt; + + $password_set_stmt = 'SET PASSWORD FOR \'%s\'@\'%s\' = \'%s\''; + $password_set_show = sprintf( + $password_set_stmt, + $slashedUsername, + $slashedHostname, + '***' + ); + + $sql_query_stmt = sprintf( + 'GRANT %s ON *.* TO \'%s\'@\'%s\'', + implode(', ', $this->extractPrivInfo()), + $slashedUsername, + $slashedHostname + ); + $real_sql_query = $sql_query = $sql_query_stmt; + + // Set the proper hashing method + if (isset($_POST['authentication_plugin'])) { + $this->setProperPasswordHashing( + $_POST['authentication_plugin'] + ); + } + + // Use 'CREATE USER ... WITH ... AS ..' syntax for + // newer MySQL versions + // and 'CREATE USER ... VIA .. USING ..' syntax for + // newer MariaDB versions + if ((($serverType == 'MySQL' || $serverType == 'Percona Server') + && $serverVersion >= 50706) + || ($serverType == 'MariaDB' + && $serverVersion >= 50200) + ) { + $password_set_real = null; + + // Required for binding '%' with '%s' + $create_user_stmt = str_replace( + '%', + '%%', + $create_user_stmt + ); + + // MariaDB uses 'USING' whereas MySQL uses 'AS' + // but MariaDB with validation plugin needs cleartext password + if ($serverType == 'MariaDB' + && ! $isMariaDBPwdPluginActive + ) { + $create_user_stmt .= ' USING \'%s\''; + } elseif ($serverType == 'MariaDB') { + $create_user_stmt .= ' IDENTIFIED BY \'%s\''; + } elseif (($serverType == 'MySQL' || $serverType == 'Percona Server') && $serverVersion >= 80011) { + $create_user_stmt .= ' BY \'%s\''; + } else { + $create_user_stmt .= ' AS \'%s\''; + } + + if ($_POST['pred_password'] == 'keep') { + $create_user_real = sprintf( + $create_user_stmt, + $slashedPassword + ); + $create_user_show = sprintf( + $create_user_stmt, + '***' + ); + } elseif ($_POST['pred_password'] == 'none') { + $create_user_real = sprintf( + $create_user_stmt, + null + ); + $create_user_show = sprintf( + $create_user_stmt, + '***' + ); + } else { + if (! (($serverType == 'MariaDB' && $isMariaDBPwdPluginActive) + || ($serverType == 'MySQL' || $serverType == 'Percona Server') && $serverVersion >= 80011)) { + $hashedPassword = $this->getHashedPassword($_POST['pma_pw']); + } else { + // MariaDB with validation plugin needs cleartext password + $hashedPassword = $_POST['pma_pw']; + } + $create_user_real = sprintf( + $create_user_stmt, + $hashedPassword + ); + $create_user_show = sprintf( + $create_user_stmt, + '***' + ); + } + } else { + // Use 'SET PASSWORD' syntax for pre-5.7.6 MySQL versions + // and pre-5.2.0 MariaDB versions + if ($_POST['pred_password'] == 'keep') { + $password_set_real = sprintf( + $password_set_stmt, + $slashedUsername, + $slashedHostname, + $slashedPassword + ); + } elseif ($_POST['pred_password'] == 'none') { + $password_set_real = sprintf( + $password_set_stmt, + $slashedUsername, + $slashedHostname, + null + ); + } else { + $hashedPassword = $this->getHashedPassword($_POST['pma_pw']); + $password_set_real = sprintf( + $password_set_stmt, + $slashedUsername, + $slashedHostname, + $hashedPassword + ); + } + } + + $alter_real_sql_query = ''; + $alter_sql_query = ''; + if (($serverType == 'MySQL' || $serverType == 'Percona Server') && $serverVersion >= 80011) { + $sql_query_stmt = ''; + if ((isset($_POST['Grant_priv']) && $_POST['Grant_priv'] == 'Y') + || (isset($GLOBALS['Grant_priv']) && $GLOBALS['Grant_priv'] == 'Y') + ) { + $sql_query_stmt = ' WITH GRANT OPTION'; + } + $real_sql_query .= $sql_query_stmt; + $sql_query .= $sql_query_stmt; + + $alter_sql_query_stmt = sprintf( + 'ALTER USER \'%s\'@\'%s\'', + $slashedUsername, + $slashedHostname + ); + $alter_real_sql_query = $alter_sql_query_stmt; + $alter_sql_query = $alter_sql_query_stmt; + } + + // add REQUIRE clause + $require_clause = $this->getRequireClause(); + $with_clause = $this->getWithClauseForAddUserAndUpdatePrivs(); + + if (($serverType == 'MySQL' || $serverType == 'Percona Server') && $serverVersion >= 80011) { + $alter_real_sql_query .= $require_clause; + $alter_sql_query .= $require_clause; + $alter_real_sql_query .= $with_clause; + $alter_sql_query .= $with_clause; + } else { + $real_sql_query .= $require_clause; + $sql_query .= $require_clause; + $real_sql_query .= $with_clause; + $sql_query .= $with_clause; + } + + if ($alter_real_sql_query !== '') { + $alter_real_sql_query .= ';'; + $alter_sql_query .= ';'; + } + $create_user_real .= ';'; + $create_user_show .= ';'; + $real_sql_query .= ';'; + $sql_query .= ';'; + // No Global GRANT_OPTION privilege + if (! $GLOBALS['is_grantuser']) { + $real_sql_query = ''; + $sql_query = ''; + } + + // Use 'SET PASSWORD' for pre-5.7.6 MySQL versions + // and pre-5.2.0 MariaDB + if (($serverType == 'MySQL' + && $serverVersion >= 50706) + || ($serverType == 'MariaDB' + && $serverVersion >= 50200) + ) { + $password_set_real = null; + $password_set_show = null; + } else { + if ($password_set_real !== null) { + $password_set_real .= ";"; + } + $password_set_show .= ";"; + } + + return [ + $create_user_real, + $create_user_show, + $real_sql_query, + $sql_query, + $password_set_real, + $password_set_show, + $alter_real_sql_query, + $alter_sql_query, + ]; + } + + /** + * Returns the type ('PROCEDURE' or 'FUNCTION') of the routine + * + * @param string $dbname database + * @param string $routineName routine + * + * @return string type + */ + public function getRoutineType($dbname, $routineName) + { + $routineData = $this->dbi->getRoutines($dbname); + + foreach ($routineData as $routine) { + if ($routine['name'] === $routineName) { + return $routine['type']; + } + } + return ''; + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Server/Select.php b/srcs/phpmyadmin/libraries/classes/Server/Select.php new file mode 100644 index 0000000..bfc1f19 --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Server/Select.php @@ -0,0 +1,128 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * Code for displaying server selection + * + * @package PhpMyAdmin + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Server; + +use PhpMyAdmin\Url; +use PhpMyAdmin\Util; + +/** + * Displays the MySQL servers choice form + * + * @package PhpMyAdmin + */ +class Select +{ + /** + * Renders the server selection in list or selectbox form, or option tags only + * + * @param boolean $not_only_options whether to include form tags or not + * @param boolean $omit_fieldset whether to omit fieldset tag or not + * + * @return string + */ + public static function render($not_only_options, $omit_fieldset) + { + $retval = ''; + + // Show as list? + if ($not_only_options) { + $list = $GLOBALS['cfg']['DisplayServersList']; + $not_only_options = ! $list; + } else { + $list = false; + } + + if ($not_only_options) { + $retval .= '<form method="post" action="' + . Util::getScriptNameForOption( + $GLOBALS['cfg']['DefaultTabServer'], + 'server' + ) + . '" class="disableAjax">'; + + if (! $omit_fieldset) { + $retval .= '<fieldset>'; + } + + $retval .= Url::getHiddenFields([]); + $retval .= '<label for="select_server">' + . __('Current server:') . '</label> '; + + $retval .= '<select name="server" id="select_server" class="autosubmit">'; + $retval .= '<option value="">(' . __('Servers') . ') ...</option>' . "\n"; + } elseif ($list) { + $retval .= __('Current server:') . '<br>'; + $retval .= '<ul id="list_server">'; + } + + foreach ($GLOBALS['cfg']['Servers'] as $key => $server) { + if (empty($server['host'])) { + continue; + } + + if (! empty($GLOBALS['server']) && (int) $GLOBALS['server'] === (int) $key) { + $selected = 1; + } else { + $selected = 0; + } + if (! empty($server['verbose'])) { + $label = $server['verbose']; + } else { + $label = $server['host']; + if (! empty($server['port'])) { + $label .= ':' . $server['port']; + } + } + if (! empty($server['only_db'])) { + if (! is_array($server['only_db'])) { + $label .= ' - ' . $server['only_db']; + // try to avoid displaying a too wide selector + } elseif (count($server['only_db']) < 4) { + $label .= ' - ' . implode(', ', $server['only_db']); + } + } + if (! empty($server['user']) && $server['auth_type'] == 'config') { + $label .= ' (' . $server['user'] . ')'; + } + + if ($list) { + $retval .= '<li>'; + if ($selected) { + $retval .= '<strong>' . htmlspecialchars($label) . '</strong>'; + } else { + $retval .= '<a class="disableAjax item" href="' + . Util::getScriptNameForOption( + $GLOBALS['cfg']['DefaultTabServer'], + 'server' + ) + . Url::getCommon(['server' => $key]) + . '" >' . htmlspecialchars($label) . '</a>'; + } + $retval .= '</li>'; + } else { + $retval .= '<option value="' . $key . '" ' + . ($selected ? ' selected="selected"' : '') . '>' + . htmlspecialchars($label) . '</option>' . "\n"; + } + } // end while + + if ($not_only_options) { + $retval .= '</select>'; + if (! $omit_fieldset) { + $retval .= '</fieldset>'; + } + $retval .= '</form>'; + } elseif ($list) { + $retval .= '</ul>'; + } + + return $retval; + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Server/Status/Data.php b/srcs/phpmyadmin/libraries/classes/Server/Status/Data.php new file mode 100644 index 0000000..7d352f7 --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Server/Status/Data.php @@ -0,0 +1,430 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * PhpMyAdmin\Server\Status\Data class + * Used by server_status_*.php pages + * + * @package PhpMyAdmin + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Server\Status; + +use PhpMyAdmin\Url; + +/** + * This class provides data about the server status + * + * All properties of the class are read-only + * + * TODO: Use lazy initialisation for some of the properties + * since not all of the server_status_*.php pages need + * all the data that this class provides. + * + * @package PhpMyAdmin + */ +class Data +{ + public $status; + public $sections; + public $variables; + public $used_queries; + public $allocationMap; + public $links; + public $db_isLocal; + public $section; + public $sectionUsed; + public $selfUrl; + public $dataLoaded; + + /** + * An empty setter makes the above properties read-only + * + * @param string $a key + * @param mixed $b value + * + * @return void + */ + public function __set($a, $b) + { + // Discard everything + } + + /** + * Gets the allocations for constructor + * + * @return array + */ + private function _getAllocations() + { + return [ + // variable name => section + // variable names match when they begin with the given string + + 'Com_' => 'com', + 'Innodb_' => 'innodb', + 'Ndb_' => 'ndb', + 'Handler_' => 'handler', + 'Qcache_' => 'qcache', + 'Threads_' => 'threads', + 'Slow_launch_threads' => 'threads', + + 'Binlog_cache_' => 'binlog_cache', + 'Created_tmp_' => 'created_tmp', + 'Key_' => 'key', + + 'Delayed_' => 'delayed', + 'Not_flushed_delayed_rows' => 'delayed', + + 'Flush_commands' => 'query', + 'Last_query_cost' => 'query', + 'Slow_queries' => 'query', + 'Queries' => 'query', + 'Prepared_stmt_count' => 'query', + + 'Select_' => 'select', + 'Sort_' => 'sort', + + 'Open_tables' => 'table', + 'Opened_tables' => 'table', + 'Open_table_definitions' => 'table', + 'Opened_table_definitions' => 'table', + 'Table_locks_' => 'table', + + 'Rpl_status' => 'repl', + 'Slave_' => 'repl', + + 'Tc_' => 'tc', + + 'Ssl_' => 'ssl', + + 'Open_files' => 'files', + 'Open_streams' => 'files', + 'Opened_files' => 'files', + ]; + } + + /** + * Gets the sections for constructor + * + * @return array + */ + private function _getSections() + { + return [ + // section => section name (description) + 'com' => 'Com', + 'query' => __('SQL query'), + 'innodb' => 'InnoDB', + 'ndb' => 'NDB', + 'handler' => __('Handler'), + 'qcache' => __('Query cache'), + 'threads' => __('Threads'), + 'binlog_cache' => __('Binary log'), + 'created_tmp' => __('Temporary data'), + 'delayed' => __('Delayed inserts'), + 'key' => __('Key cache'), + 'select' => __('Joins'), + 'repl' => __('Replication'), + 'sort' => __('Sorting'), + 'table' => __('Tables'), + 'tc' => __('Transaction coordinator'), + 'files' => __('Files'), + 'ssl' => 'SSL', + 'other' => __('Other'), + ]; + } + + /** + * Gets the links for constructor + * + * @return array + */ + private function _getLinks() + { + $links = []; + // variable or section name => (name => url) + + $links['table'][__('Flush (close) all tables')] = [ + 'url' => $this->selfUrl, + 'params' => Url::getCommon(['flush' => 'TABLES'], ''), + ]; + $links['table'][__('Show open tables')] = [ + 'url' => 'sql.php', + 'params' => Url::getCommon([ + 'sql_query' => 'SHOW OPEN TABLES', + 'goto' => $this->selfUrl, + ], ''), + ]; + + if ($GLOBALS['replication_info']['master']['status']) { + $links['repl'][__('Show slave hosts')] = [ + 'url' => 'sql.php', + 'params' => Url::getCommon([ + 'sql_query' => 'SHOW SLAVE HOSTS', + 'goto' => $this->selfUrl, + ], ''), + ]; + $links['repl'][__('Show master status')] = [ + 'url' => '#replication_master', + 'params' => '', + ]; + } + if ($GLOBALS['replication_info']['slave']['status']) { + $links['repl'][__('Show slave status')] = [ + 'url' => '#replication_slave', + 'params' => '', + ]; + } + + $links['repl']['doc'] = 'replication'; + + $links['qcache'][__('Flush query cache')] = [ + 'url' => $this->selfUrl, + 'params' => Url::getCommon(['flush' => 'QUERY CACHE'], ''), + ]; + $links['qcache']['doc'] = 'query_cache'; + + $links['threads']['doc'] = 'mysql_threads'; + + $links['key']['doc'] = 'myisam_key_cache'; + + $links['binlog_cache']['doc'] = 'binary_log'; + + $links['Slow_queries']['doc'] = 'slow_query_log'; + + $links['innodb'][__('Variables')] = [ + 'url' => 'server_engines.php', + 'params' => Url::getCommon(['engine' => 'InnoDB'], ''), + ]; + $links['innodb'][__('InnoDB Status')] = [ + 'url' => 'server_engines.php', + 'params' => Url::getCommon([ + 'engine' => 'InnoDB', + 'page' => 'Status', + ], ''), + ]; + $links['innodb']['doc'] = 'innodb'; + + return $links; + } + + /** + * Calculate some values + * + * @param array $server_status contains results of SHOW GLOBAL STATUS + * @param array $server_variables contains results of SHOW GLOBAL VARIABLES + * + * @return array + */ + private function _calculateValues(array $server_status, array $server_variables) + { + // Key_buffer_fraction + if (isset($server_status['Key_blocks_unused']) + && isset($server_variables['key_cache_block_size']) + && isset($server_variables['key_buffer_size']) + && $server_variables['key_buffer_size'] != 0 + ) { + $server_status['Key_buffer_fraction_%'] + = 100 + - $server_status['Key_blocks_unused'] + * $server_variables['key_cache_block_size'] + / $server_variables['key_buffer_size'] + * 100; + } elseif (isset($server_status['Key_blocks_used']) + && isset($server_variables['key_buffer_size']) + && $server_variables['key_buffer_size'] != 0 + ) { + $server_status['Key_buffer_fraction_%'] + = $server_status['Key_blocks_used'] + * 1024 + / $server_variables['key_buffer_size']; + } + + // Ratio for key read/write + if (isset($server_status['Key_writes']) + && isset($server_status['Key_write_requests']) + && $server_status['Key_write_requests'] > 0 + ) { + $key_writes = $server_status['Key_writes']; + $key_write_requests = $server_status['Key_write_requests']; + $server_status['Key_write_ratio_%'] + = 100 * $key_writes / $key_write_requests; + } + + if (isset($server_status['Key_reads']) + && isset($server_status['Key_read_requests']) + && $server_status['Key_read_requests'] > 0 + ) { + $key_reads = $server_status['Key_reads']; + $key_read_requests = $server_status['Key_read_requests']; + $server_status['Key_read_ratio_%'] + = 100 * $key_reads / $key_read_requests; + } + + // Threads_cache_hitrate + if (isset($server_status['Threads_created']) + && isset($server_status['Connections']) + && $server_status['Connections'] > 0 + ) { + $server_status['Threads_cache_hitrate_%'] + = 100 - $server_status['Threads_created'] + / $server_status['Connections'] * 100; + } + return $server_status; + } + + /** + * Sort variables into arrays + * + * @param array $server_status contains results of SHOW GLOBAL STATUS + * @param array $allocations allocations for sections + * @param array $allocationMap map variables to their section + * @param array $sectionUsed is a section used? + * @param array $used_queries used queries + * + * @return array ($allocationMap, $sectionUsed, $used_queries) + */ + private function _sortVariables( + array $server_status, + array $allocations, + array $allocationMap, + array $sectionUsed, + array $used_queries + ) { + foreach ($server_status as $name => $value) { + $section_found = false; + foreach ($allocations as $filter => $section) { + if (mb_strpos($name, $filter) !== false) { + $allocationMap[$name] = $section; + $sectionUsed[$section] = true; + $section_found = true; + if ($section == 'com' && $value > 0) { + $used_queries[$name] = $value; + } + break; // Only exits inner loop + } + } + if (! $section_found) { + $allocationMap[$name] = 'other'; + $sectionUsed['other'] = true; + } + } + return [ + $allocationMap, + $sectionUsed, + $used_queries, + ]; + } + + /** + * Constructor + */ + public function __construct() + { + $this->selfUrl = basename($GLOBALS['PMA_PHP_SELF']); + + // get status from server + $server_status_result = $GLOBALS['dbi']->tryQuery('SHOW GLOBAL STATUS'); + $server_status = []; + if ($server_status_result === false) { + $this->dataLoaded = false; + } else { + $this->dataLoaded = true; + while ($arr = $GLOBALS['dbi']->fetchRow($server_status_result)) { + $server_status[$arr[0]] = $arr[1]; + } + $GLOBALS['dbi']->freeResult($server_status_result); + } + + // for some calculations we require also some server settings + $server_variables = $GLOBALS['dbi']->fetchResult( + 'SHOW GLOBAL VARIABLES', + 0, + 1 + ); + + // cleanup of some deprecated values + $server_status = self::cleanDeprecated($server_status); + + // calculate some values + $server_status = $this->_calculateValues( + $server_status, + $server_variables + ); + + // split variables in sections + $allocations = $this->_getAllocations(); + + $sections = $this->_getSections(); + + // define some needful links/commands + $links = $this->_getLinks(); + + // Variable to contain all com_ variables (query statistics) + $used_queries = []; + + // Variable to map variable names to their respective section name + // (used for js category filtering) + $allocationMap = []; + + // Variable to mark used sections + $sectionUsed = []; + + // sort vars into arrays + list( + $allocationMap, $sectionUsed, $used_queries + ) = $this->_sortVariables( + $server_status, + $allocations, + $allocationMap, + $sectionUsed, + $used_queries + ); + + // admin commands are not queries (e.g. they include COM_PING, + // which is excluded from $server_status['Questions']) + unset($used_queries['Com_admin_commands']); + + // Set all class properties + $this->db_isLocal = false; + $serverHostToLower = mb_strtolower( + $GLOBALS['cfg']['Server']['host'] + ); + if ($serverHostToLower === 'localhost' + || $GLOBALS['cfg']['Server']['host'] === '127.0.0.1' + || $GLOBALS['cfg']['Server']['host'] === '::1' + ) { + $this->db_isLocal = true; + } + $this->status = $server_status; + $this->sections = $sections; + $this->variables = $server_variables; + $this->used_queries = $used_queries; + $this->allocationMap = $allocationMap; + $this->links = $links; + $this->sectionUsed = $sectionUsed; + } + + /** + * cleanup of some deprecated values + * + * @param array $server_status status array to process + * + * @return array + */ + public static function cleanDeprecated(array $server_status) + { + $deprecated = [ + 'Com_prepare_sql' => 'Com_stmt_prepare', + 'Com_execute_sql' => 'Com_stmt_execute', + 'Com_dealloc_sql' => 'Com_stmt_close', + ]; + foreach ($deprecated as $old => $new) { + if (isset($server_status[$old]) && isset($server_status[$new])) { + unset($server_status[$old]); + } + } + return $server_status; + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Server/Status/Monitor.php b/srcs/phpmyadmin/libraries/classes/Server/Status/Monitor.php new file mode 100644 index 0000000..efa9e40 --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Server/Status/Monitor.php @@ -0,0 +1,546 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * functions for displaying server status sub item: monitor + * + * @usedby server_status_monitor.php + * + * @package PhpMyAdmin + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Server\Status; + +use PhpMyAdmin\DatabaseInterface; +use PhpMyAdmin\SysInfo; +use PhpMyAdmin\Util; + +/** + * functions for displaying server status sub item: monitor + * + * @package PhpMyAdmin + */ +class Monitor +{ + /** + * @var DatabaseInterface + */ + private $dbi; + + /** + * Monitor constructor. + * @param DatabaseInterface $dbi DatabaseInterface instance + */ + public function __construct($dbi) + { + $this->dbi = $dbi; + } + + /** + * Returns JSON for real-time charting data + * + * @param string $requiredData Required data + * + * @return array JSON + */ + public function getJsonForChartingData(string $requiredData): array + { + $ret = json_decode($requiredData, true); + $statusVars = []; + $serverVars = []; + $sysinfo = $cpuload = $memory = 0; + + /* Accumulate all required variables and data */ + list($serverVars, $statusVars, $ret) = $this->getJsonForChartingDataGet( + $ret, + $serverVars, + $statusVars, + $sysinfo, + $cpuload, + $memory + ); + + // Retrieve all required status variables + $statusVarValues = []; + if (count($statusVars)) { + $statusVarValues = $this->dbi->fetchResult( + "SHOW GLOBAL STATUS WHERE Variable_name='" + . implode("' OR Variable_name='", $statusVars) . "'", + 0, + 1 + ); + } + + // Retrieve all required server variables + $serverVarValues = []; + if (count($serverVars)) { + $serverVarValues = $this->dbi->fetchResult( + "SHOW GLOBAL VARIABLES WHERE Variable_name='" + . implode("' OR Variable_name='", $serverVars) . "'", + 0, + 1 + ); + } + + // ...and now assign them + $ret = $this->getJsonForChartingDataSet($ret, $statusVarValues, $serverVarValues); + + $ret['x'] = microtime(true) * 1000; + return $ret; + } + + /** + * Assign the variables for real-time charting data + * + * @param array $ret Real-time charting data + * @param array $statusVarValues Status variable values + * @param array $serverVarValues Server variable values + * + * @return array + */ + private function getJsonForChartingDataSet( + array $ret, + array $statusVarValues, + array $serverVarValues + ): array { + foreach ($ret as $chart_id => $chartNodes) { + foreach ($chartNodes as $node_id => $nodeDataPoints) { + foreach ($nodeDataPoints as $point_id => $dataPoint) { + switch ($dataPoint['type']) { + case 'statusvar': + $ret[$chart_id][$node_id][$point_id]['value'] + = $statusVarValues[$dataPoint['name']]; + break; + case 'servervar': + $ret[$chart_id][$node_id][$point_id]['value'] + = $serverVarValues[$dataPoint['name']]; + break; + } + } + } + } + return $ret; + } + + /** + * Get called to get JSON for charting data + * + * @param array $ret Real-time charting data + * @param array $serverVars Server variable values + * @param array $statusVars Status variable values + * @param mixed $sysinfo System info + * @param mixed $cpuload CPU load + * @param mixed $memory Memory + * + * @return array + */ + private function getJsonForChartingDataGet( + array $ret, + array $serverVars, + array $statusVars, + $sysinfo, + $cpuload, + $memory + ) { + // For each chart + foreach ($ret as $chartId => $chartNodes) { + // For each data series + foreach ($chartNodes as $nodeId => $nodeDataPoints) { + // For each data point in the series (usually just 1) + foreach ($nodeDataPoints as $pointId => $dataPoint) { + list($serverVars, $statusVars, $ret[$chartId][$nodeId][$pointId]) + = $this->getJsonForChartingDataSwitch( + $dataPoint['type'], + $dataPoint['name'], + $serverVars, + $statusVars, + $ret[$chartId][$nodeId][$pointId], + $sysinfo, + $cpuload, + $memory + ); + } /* foreach */ + } /* foreach */ + } + return [ + $serverVars, + $statusVars, + $ret, + ]; + } + + /** + * Switch called to get JSON for charting data + * + * @param string $type Type + * @param string $pName Name + * @param array $serverVars Server variable values + * @param array $statusVars Status variable values + * @param array $ret Real-time charting data + * @param mixed $sysinfo System info + * @param mixed $cpuload CPU load + * @param mixed $memory Memory + * + * @return array + */ + private function getJsonForChartingDataSwitch( + $type, + $pName, + array $serverVars, + array $statusVars, + array $ret, + $sysinfo, + $cpuload, + $memory + ) { + switch ($type) { + /* We only collect the status and server variables here to + * read them all in one query, + * and only afterwards assign them. + * Also do some white list filtering on the names + */ + case 'servervar': + if (! preg_match('/[^a-zA-Z_]+/', $pName)) { + $serverVars[] = $pName; + } + break; + + case 'statusvar': + if (! preg_match('/[^a-zA-Z_]+/', $pName)) { + $statusVars[] = $pName; + } + break; + + case 'proc': + $result = $this->dbi->query('SHOW PROCESSLIST'); + $ret['value'] = $this->dbi->numRows($result); + break; + + case 'cpu': + if (! $sysinfo) { + $sysinfo = SysInfo::get(); + } + if (! $cpuload) { + $cpuload = $sysinfo->loadavg(); + } + + if (SysInfo::getOs() == 'Linux') { + $ret['idle'] = $cpuload['idle']; + $ret['busy'] = $cpuload['busy']; + } else { + $ret['value'] = $cpuload['loadavg']; + } + + break; + + case 'memory': + if (! $sysinfo) { + $sysinfo = SysInfo::get(); + } + if (! $memory) { + $memory = $sysinfo->memory(); + } + + $ret['value'] = isset($memory[$pName]) ? $memory[$pName] : 0; + break; + } + + return [ + $serverVars, + $statusVars, + $ret, + ]; + } + + /** + * Returns JSON for log data with type: slow + * + * @param int $start Unix Time: Start time for query + * @param int $end Unix Time: End time for query + * + * @return array + */ + public function getJsonForLogDataTypeSlow(int $start, int $end): array + { + $query = 'SELECT start_time, user_host, '; + $query .= 'Sec_to_Time(Sum(Time_to_Sec(query_time))) as query_time, '; + $query .= 'Sec_to_Time(Sum(Time_to_Sec(lock_time))) as lock_time, '; + $query .= 'SUM(rows_sent) AS rows_sent, '; + $query .= 'SUM(rows_examined) AS rows_examined, db, sql_text, '; + $query .= 'COUNT(sql_text) AS \'#\' '; + $query .= 'FROM `mysql`.`slow_log` '; + $query .= 'WHERE start_time > FROM_UNIXTIME(' . $start . ') '; + $query .= 'AND start_time < FROM_UNIXTIME(' . $end . ') GROUP BY sql_text'; + + $result = $this->dbi->tryQuery($query); + + $return = [ + 'rows' => [], + 'sum' => [], + ]; + + while ($row = $this->dbi->fetchAssoc($result)) { + $type = mb_strtolower( + mb_substr( + $row['sql_text'], + 0, + mb_strpos($row['sql_text'], ' ') + ) + ); + + switch ($type) { + case 'insert': + case 'update': + //Cut off big inserts and updates, but append byte count instead + if (mb_strlen($row['sql_text']) > 220) { + $implodeSqlText = implode( + ' ', + Util::formatByteDown( + mb_strlen($row['sql_text']), + 2, + 2 + ) + ); + $row['sql_text'] = mb_substr($row['sql_text'], 0, 200) + . '... [' . $implodeSqlText . ']'; + } + break; + default: + break; + } + + if (! isset($return['sum'][$type])) { + $return['sum'][$type] = 0; + } + $return['sum'][$type] += $row['#']; + $return['rows'][] = $row; + } + + $return['sum']['TOTAL'] = array_sum($return['sum']); + $return['numRows'] = count($return['rows']); + + $this->dbi->freeResult($result); + return $return; + } + + /** + * Returns JSon for log data with type: general + * + * @param int $start Unix Time: Start time for query + * @param int $end Unix Time: End time for query + * @param bool $isTypesLimited Whether to limit types or not + * @param bool $removeVariables Whether to remove variables or not + * + * @return array + */ + public function getJsonForLogDataTypeGeneral( + int $start, + int $end, + bool $isTypesLimited, + bool $removeVariables + ): array { + $limitTypes = ''; + if ($isTypesLimited) { + $limitTypes = 'AND argument REGEXP \'^(INSERT|SELECT|UPDATE|DELETE)\' '; + } + + $query = 'SELECT TIME(event_time) as event_time, user_host, thread_id, '; + $query .= 'server_id, argument, count(argument) as \'#\' '; + $query .= 'FROM `mysql`.`general_log` '; + $query .= 'WHERE command_type=\'Query\' '; + $query .= 'AND event_time > FROM_UNIXTIME(' . $start . ') '; + $query .= 'AND event_time < FROM_UNIXTIME(' . $end . ') '; + $query .= $limitTypes . 'GROUP by argument'; // HAVING count > 1'; + + $result = $this->dbi->tryQuery($query); + + $return = [ + 'rows' => [], + 'sum' => [], + ]; + $insertTables = []; + $insertTablesFirst = -1; + $i = 0; + + while ($row = $this->dbi->fetchAssoc($result)) { + preg_match('/^(\w+)\s/', $row['argument'], $match); + $type = mb_strtolower($match[1]); + + if (! isset($return['sum'][$type])) { + $return['sum'][$type] = 0; + } + $return['sum'][$type] += $row['#']; + + switch ($type) { + /** @noinspection PhpMissingBreakStatementInspection */ + case 'insert': + // Group inserts if selected + if ($removeVariables + && preg_match( + '/^INSERT INTO (`|\'|"|)([^\s\\1]+)\\1/i', + $row['argument'], + $matches + ) + ) { + $insertTables[$matches[2]]++; + if ($insertTables[$matches[2]] > 1) { + $return['rows'][$insertTablesFirst]['#'] + = $insertTables[$matches[2]]; + + // Add a ... to the end of this query to indicate that + // there's been other queries + $temp = $return['rows'][$insertTablesFirst]['argument']; + $return['rows'][$insertTablesFirst]['argument'] + .= $this->getSuspensionPoints( + $temp[strlen($temp) - 1] + ); + + // Group this value, thus do not add to the result list + continue 2; + } else { + $insertTablesFirst = $i; + $insertTables[$matches[2]] += $row['#'] - 1; + } + } + // No break here + + case 'update': + // Cut off big inserts and updates, + // but append byte count therefor + if (mb_strlen($row['argument']) > 220) { + $row['argument'] = mb_substr($row['argument'], 0, 200) + . '... [' + . implode( + ' ', + Util::formatByteDown( + mb_strlen($row['argument']), + 2, + 2 + ) + ) + . ']'; + } + break; + + default: + break; + } + + $return['rows'][] = $row; + $i++; + } + + $return['sum']['TOTAL'] = array_sum($return['sum']); + $return['numRows'] = count($return['rows']); + + $this->dbi->freeResult($result); + + return $return; + } + + /** + * Return suspension points if needed + * + * @param string $lastChar Last char + * + * @return string Return suspension points if needed + */ + private function getSuspensionPoints(string $lastChar): string + { + if ($lastChar != '.') { + return '<br>...'; + } + + return ''; + } + + /** + * Returns JSON for logging vars + * + * @param string|null $name Variable name + * @param string|null $value Variable value + * + * @return array JSON + */ + public function getJsonForLoggingVars(?string $name, ?string $value): array + { + if (isset($name) && isset($value)) { + $escapedValue = $this->dbi->escapeString($value); + if (! is_numeric($escapedValue)) { + $escapedValue = "'" . $escapedValue . "'"; + } + + if (! preg_match("/[^a-zA-Z0-9_]+/", $name)) { + $this->dbi->query( + 'SET GLOBAL ' . $name . ' = ' . $escapedValue + ); + } + } + + $loggingVars = $this->dbi->fetchResult( + 'SHOW GLOBAL VARIABLES WHERE Variable_name IN' + . ' ("general_log","slow_query_log","long_query_time","log_output")', + 0, + 1 + ); + return $loggingVars; + } + + /** + * Returns JSON for query_analyzer + * + * @param string $database Database name + * @param string $query SQL query + * + * @return array JSON + */ + public function getJsonForQueryAnalyzer( + string $database, + string $query + ): array { + global $cached_affected_rows; + + $return = []; + + if (strlen($database) > 0) { + $this->dbi->selectDb($database); + } + + if ($profiling = Util::profilingSupported()) { + $this->dbi->query('SET PROFILING=1;'); + } + + // Do not cache query + $sqlQuery = preg_replace( + '/^(\s*SELECT)/i', + '\\1 SQL_NO_CACHE', + $query + ); + + $this->dbi->tryQuery($sqlQuery); + $return['affectedRows'] = $cached_affected_rows; + + $result = $this->dbi->tryQuery('EXPLAIN ' . $sqlQuery); + while ($row = $this->dbi->fetchAssoc($result)) { + $return['explain'][] = $row; + } + + // In case an error happened + $return['error'] = $this->dbi->getError(); + + $this->dbi->freeResult($result); + + if ($profiling) { + $return['profiling'] = []; + $result = $this->dbi->tryQuery( + 'SELECT seq,state,duration FROM INFORMATION_SCHEMA.PROFILING' + . ' WHERE QUERY_ID=1 ORDER BY seq' + ); + while ($row = $this->dbi->fetchAssoc($result)) { + $return['profiling'][] = $row; + } + $this->dbi->freeResult($result); + } + return $return; + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Server/UserGroups.php b/srcs/phpmyadmin/libraries/classes/Server/UserGroups.php new file mode 100644 index 0000000..89bc1a3 --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Server/UserGroups.php @@ -0,0 +1,390 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * set of functions for user group handling + * + * @package PhpMyAdmin + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Server; + +use PhpMyAdmin\Relation; +use PhpMyAdmin\Url; +use PhpMyAdmin\Util; + +/** + * PhpMyAdmin\Server\UserGroups class + * + * @package PhpMyAdmin + */ +class UserGroups +{ + /** + * Return HTML to list the users belonging to a given user group + * + * @param string $userGroup user group name + * + * @return string HTML to list the users belonging to a given user group + */ + public static function getHtmlForListingUsersofAGroup($userGroup) + { + $relation = new Relation($GLOBALS['dbi']); + $html_output = '<h2>' + . sprintf(__('Users of \'%s\' user group'), htmlspecialchars($userGroup)) + . '</h2>'; + + $cfgRelation = $relation->getRelationsParam(); + $usersTable = Util::backquote($cfgRelation['db']) + . "." . Util::backquote($cfgRelation['users']); + $sql_query = "SELECT `username` FROM " . $usersTable + . " WHERE `usergroup`='" . $GLOBALS['dbi']->escapeString($userGroup) + . "'"; + $result = $relation->queryAsControlUser($sql_query, false); + if ($result) { + if ($GLOBALS['dbi']->numRows($result) == 0) { + $html_output .= '<p>' + . __('No users were found belonging to this user group.') + . '</p>'; + } else { + $html_output .= '<table>' + . '<thead><tr><th>#</th><th>' . __('User') . '</th></tr></thead>' + . '<tbody>'; + $i = 0; + while ($row = $GLOBALS['dbi']->fetchRow($result)) { + $i++; + $html_output .= '<tr>' + . '<td>' . $i . ' </td>' + . '<td>' . htmlspecialchars($row[0]) . '</td>' + . '</tr>'; + } + $html_output .= '</tbody>' + . '</table>'; + } + } + $GLOBALS['dbi']->freeResult($result); + return $html_output; + } + + /** + * Returns HTML for the 'user groups' table + * + * @return string HTML for the 'user groups' table + */ + public static function getHtmlForUserGroupsTable() + { + $relation = new Relation($GLOBALS['dbi']); + $html_output = '<h2>' . __('User groups') . '</h2>'; + $cfgRelation = $relation->getRelationsParam(); + $groupTable = Util::backquote($cfgRelation['db']) + . "." . Util::backquote($cfgRelation['usergroups']); + $sql_query = "SELECT * FROM " . $groupTable . " ORDER BY `usergroup` ASC"; + $result = $relation->queryAsControlUser($sql_query, false); + + if ($result && $GLOBALS['dbi']->numRows($result)) { + $html_output .= '<form name="userGroupsForm" id="userGroupsForm"' + . ' action="server_privileges.php" method="post">'; + $html_output .= Url::getHiddenInputs(); + $html_output .= '<table id="userGroupsTable">'; + $html_output .= '<thead><tr>'; + $html_output .= '<th style="white-space: nowrap">' + . __('User group') . '</th>'; + $html_output .= '<th>' . __('Server level tabs') . '</th>'; + $html_output .= '<th>' . __('Database level tabs') . '</th>'; + $html_output .= '<th>' . __('Table level tabs') . '</th>'; + $html_output .= '<th>' . __('Action') . '</th>'; + $html_output .= '</tr></thead>'; + $html_output .= '<tbody>'; + + $userGroups = []; + while ($row = $GLOBALS['dbi']->fetchAssoc($result)) { + $groupName = $row['usergroup']; + if (! isset($userGroups[$groupName])) { + $userGroups[$groupName] = []; + } + $userGroups[$groupName][$row['tab']] = $row['allowed']; + } + foreach ($userGroups as $groupName => $tabs) { + $html_output .= '<tr>'; + $html_output .= '<td>' . htmlspecialchars($groupName) . '</td>'; + $html_output .= '<td>' . self::getAllowedTabNames($tabs, 'server') . '</td>'; + $html_output .= '<td>' . self::getAllowedTabNames($tabs, 'db') . '</td>'; + $html_output .= '<td>' . self::getAllowedTabNames($tabs, 'table') . '</td>'; + + $html_output .= '<td>'; + $html_output .= '<a class="" href="server_user_groups.php" data-post="' + . Url::getCommon( + [ + 'viewUsers' => 1, + 'userGroup' => $groupName, + ], + '' + ) + . '">' + . Util::getIcon('b_usrlist', __('View users')) + . '</a>'; + $html_output .= ' '; + $html_output .= '<a class="" href="server_user_groups.php" data-post="' + . Url::getCommon( + [ + 'editUserGroup' => 1, + 'userGroup' => $groupName, + ], + '' + ) + . '">' + . Util::getIcon('b_edit', __('Edit')) . '</a>'; + $html_output .= ' '; + $html_output .= '<a class="deleteUserGroup ajax"' + . ' href="server_user_groups.php" data-post="' + . Url::getCommon( + [ + 'deleteUserGroup' => 1, + 'userGroup' => $groupName, + ], + '' + ) + . '">' + . Util::getIcon('b_drop', __('Delete')) . '</a>'; + $html_output .= '</td>'; + + $html_output .= '</tr>'; + } + + $html_output .= '</tbody>'; + $html_output .= '</table>'; + $html_output .= '</form>'; + } + $GLOBALS['dbi']->freeResult($result); + + $html_output .= '<fieldset id="fieldset_add_user_group">'; + $html_output .= '<a href="server_user_groups.php' + . Url::getCommon(['addUserGroup' => 1]) . '">' + . Util::getIcon('b_usradd') + . __('Add user group') . '</a>'; + $html_output .= '</fieldset>'; + + return $html_output; + } + + /** + * Returns the list of allowed menu tab names + * based on a data row from usergroup table. + * + * @param array $row row of usergroup table + * @param string $level 'server', 'db' or 'table' + * + * @return string comma separated list of allowed menu tab names + */ + public static function getAllowedTabNames(array $row, $level) + { + $tabNames = []; + $tabs = Util::getMenuTabList($level); + foreach ($tabs as $tab => $tabName) { + if (! isset($row[$level . '_' . $tab]) + || $row[$level . '_' . $tab] == 'Y' + ) { + $tabNames[] = $tabName; + } + } + return implode(', ', $tabNames); + } + + /** + * Deletes a user group + * + * @param string $userGroup user group name + * + * @return void + */ + public static function delete($userGroup) + { + $relation = new Relation($GLOBALS['dbi']); + $cfgRelation = $relation->getRelationsParam(); + $userTable = Util::backquote($cfgRelation['db']) + . "." . Util::backquote($cfgRelation['users']); + $groupTable = Util::backquote($cfgRelation['db']) + . "." . Util::backquote($cfgRelation['usergroups']); + $sql_query = "DELETE FROM " . $userTable + . " WHERE `usergroup`='" . $GLOBALS['dbi']->escapeString($userGroup) + . "'"; + $relation->queryAsControlUser($sql_query, true); + $sql_query = "DELETE FROM " . $groupTable + . " WHERE `usergroup`='" . $GLOBALS['dbi']->escapeString($userGroup) + . "'"; + $relation->queryAsControlUser($sql_query, true); + } + + /** + * Returns HTML for add/edit user group dialog + * + * @param string $userGroup name of the user group in case of editing + * + * @return string HTML for add/edit user group dialog + */ + public static function getHtmlToEditUserGroup($userGroup = null) + { + $relation = new Relation($GLOBALS['dbi']); + $html_output = ''; + if ($userGroup == null) { + $html_output .= '<h2>' . __('Add user group') . '</h2>'; + } else { + $html_output .= '<h2>' + . sprintf(__('Edit user group: \'%s\''), htmlspecialchars($userGroup)) + . '</h2>'; + } + + $html_output .= '<form name="userGroupForm" id="userGroupForm"' + . ' action="server_user_groups.php" method="post">'; + $urlParams = []; + if ($userGroup != null) { + $urlParams['userGroup'] = $userGroup; + $urlParams['editUserGroupSubmit'] = '1'; + } else { + $urlParams['addUserGroupSubmit'] = '1'; + } + $html_output .= Url::getHiddenInputs($urlParams); + + $html_output .= '<fieldset id="fieldset_user_group_rights">'; + $html_output .= '<legend>' . __('User group menu assignments') + . ' ' + . '<input type="checkbox" id="addUsersForm_checkall" ' + . 'class="checkall_box" title="Check all">' + . '<label for="addUsersForm_checkall">' . __('Check all') . '</label>' + . '</legend>'; + + if ($userGroup == null) { + $html_output .= '<label for="userGroup">' . __('Group name:') . '</label>'; + $html_output .= '<input type="text" name="userGroup" maxlength="64" autocomplete="off" required="required">'; + $html_output .= '<div class="clearfloat"></div>'; + } + + $allowedTabs = [ + 'server' => [], + 'db' => [], + 'table' => [], + ]; + if ($userGroup != null) { + $cfgRelation = $relation->getRelationsParam(); + $groupTable = Util::backquote($cfgRelation['db']) + . "." . Util::backquote($cfgRelation['usergroups']); + $sql_query = "SELECT * FROM " . $groupTable + . " WHERE `usergroup`='" . $GLOBALS['dbi']->escapeString($userGroup) + . "'"; + $result = $relation->queryAsControlUser($sql_query, false); + if ($result) { + while ($row = $GLOBALS['dbi']->fetchAssoc($result)) { + $key = $row['tab']; + $value = $row['allowed']; + if (substr($key, 0, 7) == 'server_' && $value == 'Y') { + $allowedTabs['server'][] = mb_substr($key, 7); + } elseif (substr($key, 0, 3) == 'db_' && $value == 'Y') { + $allowedTabs['db'][] = mb_substr($key, 3); + } elseif (substr($key, 0, 6) == 'table_' + && $value == 'Y' + ) { + $allowedTabs['table'][] = mb_substr($key, 6); + } + } + } + $GLOBALS['dbi']->freeResult($result); + } + + $html_output .= self::getTabList( + __('Server-level tabs'), + 'server', + $allowedTabs['server'] + ); + $html_output .= self::getTabList( + __('Database-level tabs'), + 'db', + $allowedTabs['db'] + ); + $html_output .= self::getTabList( + __('Table-level tabs'), + 'table', + $allowedTabs['table'] + ); + + $html_output .= '</fieldset>'; + + $html_output .= '<fieldset id="fieldset_user_group_rights_footer"' + . ' class="tblFooters">'; + $html_output .= '<input class="btn btn-primary" type="submit" value="' . __('Go') . '">'; + $html_output .= '</fieldset>'; + + return $html_output; + } + + /** + * Returns HTML for checkbox groups to choose + * tabs of 'server', 'db' or 'table' levels. + * + * @param string $title title of the checkbox group + * @param string $level 'server', 'db' or 'table' + * @param array $selected array of selected allowed tabs + * + * @return string HTML for checkbox groups + */ + public static function getTabList($title, $level, array $selected) + { + $tabs = Util::getMenuTabList($level); + $html_output = '<fieldset>'; + $html_output .= '<legend>' . $title . '</legend>'; + foreach ($tabs as $tab => $tabName) { + $html_output .= '<div class="item">'; + $html_output .= '<input type="checkbox" class="checkall"' + . (in_array($tab, $selected) ? ' checked="checked"' : '') + . ' name="' . $level . '_' . $tab . '" value="Y">'; + $html_output .= '<label for="' . $level . '_' . $tab . '">' + . '<code>' . $tabName . '</code>' + . '</label>'; + $html_output .= '</div>'; + } + $html_output .= '</fieldset>'; + return $html_output; + } + + /** + * Add/update a user group with allowed menu tabs. + * + * @param string $userGroup user group name + * @param boolean $new whether this is a new user group + * + * @return void + */ + public static function edit($userGroup, $new = false) + { + $relation = new Relation($GLOBALS['dbi']); + $tabs = Util::getMenuTabList(); + $cfgRelation = $relation->getRelationsParam(); + $groupTable = Util::backquote($cfgRelation['db']) + . "." . Util::backquote($cfgRelation['usergroups']); + + if (! $new) { + $sql_query = "DELETE FROM " . $groupTable + . " WHERE `usergroup`='" . $GLOBALS['dbi']->escapeString($userGroup) + . "';"; + $relation->queryAsControlUser($sql_query, true); + } + + $sql_query = "INSERT INTO " . $groupTable + . "(`usergroup`, `tab`, `allowed`)" + . " VALUES "; + $first = true; + foreach ($tabs as $tabGroupName => $tabGroup) { + foreach ($tabGroup as $tab => $tabName) { + if (! $first) { + $sql_query .= ", "; + } + $tabName = $tabGroupName . '_' . $tab; + $allowed = isset($_POST[$tabName]) && $_POST[$tabName] == 'Y'; + $sql_query .= "('" . $GLOBALS['dbi']->escapeString($userGroup) . "', '" . $tabName . "', '" + . ($allowed ? "Y" : "N") . "')"; + $first = false; + } + } + $sql_query .= ";"; + $relation->queryAsControlUser($sql_query, true); + } +} diff --git a/srcs/phpmyadmin/libraries/classes/Server/Users.php b/srcs/phpmyadmin/libraries/classes/Server/Users.php new file mode 100644 index 0000000..a497241 --- /dev/null +++ b/srcs/phpmyadmin/libraries/classes/Server/Users.php @@ -0,0 +1,64 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * set of common functions for sub tabs in server level `Users` page + * + * @package PhpMyAdmin + */ +declare(strict_types=1); + +namespace PhpMyAdmin\Server; + +use PhpMyAdmin\Url; + +/** + * PhpMyAdmin\Server\Users class + * + * @package PhpMyAdmin + */ +class Users +{ + /** + * Get HTML for secondary level menu tabs on 'Users' page + * + * @param string $selfUrl Url of the file + * + * @return string HTML for secondary level menu tabs on 'Users' page + */ + public static function getHtmlForSubMenusOnUsersPage($selfUrl) + { + $items = [ + [ + 'name' => __('User accounts overview'), + 'url' => 'server_privileges.php', + 'params' => Url::getCommon(['viewing_mode' => 'server']), + ], + ]; + + if ($GLOBALS['dbi']->isSuperuser()) { + $items[] = [ + 'name' => __('User groups'), + 'url' => 'server_user_groups.php', + 'params' => Url::getCommon(), + ]; + } + + $retval = '<ul id="topmenu2">'; + foreach ($items as $item) { + $class = ''; + if ($item['url'] === $selfUrl) { + $class = ' class="tabactive"'; + } + $retval .= '<li>'; + $retval .= '<a' . $class; + $retval .= ' href="' . $item['url'] . $item['params'] . '">'; + $retval .= $item['name']; + $retval .= '</a>'; + $retval .= '</li>'; + } + $retval .= '</ul>'; + $retval .= '<div class="clearfloat"></div>'; + + return $retval; + } +} |