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