libraries/server_privileges.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * set of functions with the Privileges section in pma
   5  *
   6  * @package PhpMyAdmin
   7  */
   8 use PMA\libraries\DatabaseInterface;
   9 use PMA\libraries\Message;
  10 use PMA\libraries\Response;
  11 use PMA\libraries\Template;
  12 use PMA\libraries\Util;
  13 use PMA\libraries\URL;
  14
  15 /**
  16  * Get Html for User Group Dialog
  17  *
  18  * @param string $username     username
  19  * @param bool   $is_menuswork Is menuswork set in configuration
  20  *
  21  * @return string html
  22  */
  23 function PMA_getHtmlForUserGroupDialog($username, $is_menuswork)
  24 {
  25     $html = '';
  26     if (! empty($_REQUEST['edit_user_group_dialog']) && $is_menuswork) {
  27         $dialog = PMA_getHtmlToChooseUserGroup($username);
  28         $response = Response::getInstance();
  29         if ($response->isAjax()) {
  30             $response->addJSON('message', $dialog);
  31             exit;
  32         } else {
  33             $html .= $dialog;
  34         }
  35     }
  36
  37     return $html;
  38 }
  39
  40 /**
  41  * Escapes wildcard in a database+table specification
  42  * before using it in a GRANT statement.
  43  *
  44  * Escaping a wildcard character in a GRANT is only accepted at the global
  45  * or database level, not at table level; this is why I remove
  46  * the escaping character. Internally, in mysql.tables_priv.Db there are
  47  * no escaping (for example test_db) but in mysql.db you'll see test\_db
  48  * for a db-specific privilege.
  49  *
  50  * @param string $dbname    Database name
  51  * @param string $tablename Table name
  52  *
  53  * @return string the escaped (if necessary) database.table
  54  */
  55 function PMA_wildcardEscapeForGrant($dbname, $tablename)
  56 {
  57     if (strlen($dbname) === 0) {
  58         $db_and_table = '*.*';
  59     } else {
  60         if (strlen($tablename) > 0) {
  61             $db_and_table = Util::backquote(
  62                 Util::unescapeMysqlWildcards($dbname)
  63             )
  64             . '.' . Util::backquote($tablename);
  65         } else {
  66             $db_and_table = Util::backquote($dbname) . '.*';
  67         }
  68     }
  69     return $db_and_table;
  70 }
  71
  72 /**
  73  * Generates a condition on the user name
  74  *
  75  * @param string $initial the user's initial
  76  *
  77  * @return string   the generated condition
  78  */
  79 function PMA_rangeOfUsers($initial = '')
  80 {
  81     // strtolower() is used because the User field
  82     // might be BINARY, so LIKE would be case sensitive
  83     if ($initial === null || $initial === '') {
  84         return '';
  85     }
  86
  87     $ret = " WHERE `User` LIKE '"
  88         . $GLOBALS['dbi']->escapeString($initial) . "%'"
  89         . " OR `User` LIKE '"
  90         . $GLOBALS['dbi']->escapeString(mb_strtolower($initial))
  91         . "%'";
  92     return $ret;
  93 } // end function
  94
  95 /**
  96  * Formats privilege name for a display
  97  *
  98  * @param array   $privilege Privilege information
  99  * @param boolean $html      Whether to use HTML
 100  *
 101  * @return string
 102  */
 103 function PMA_formatPrivilege($privilege, $html)
 104 {
 105     if ($html) {
 106         return '<dfn title="' . $privilege[2] . '">'
 107             . $privilege[1] . '</dfn>';
 108     } else {
 109         return $privilege[1];
 110     }
 111 }
 112
 113 /**
 114  * Parses privileges into an array, it modifies the array
 115  *
 116  * @param array &$row Results row from
 117  *
 118  * @return void
 119  */
 120 function PMA_fillInTablePrivileges(&$row)
 121 {
 122     $row1 = $GLOBALS['dbi']->fetchSingleRow(
 123         'SHOW COLUMNS FROM `mysql`.`tables_priv` LIKE \'Table_priv\';',
 124         'ASSOC', $GLOBALS['userlink']
 125     );
 126     // note: in MySQL 5.0.3 we get "Create View', 'Show view';
 127     // the View for Create is spelled with uppercase V
 128     // the view for Show is spelled with lowercase v
 129     // and there is a space between the words
 130
 131     $av_grants = explode(
 132         '\',\'',
 133         mb_substr(
 134             $row1['Type'],
 135             mb_strpos($row1['Type'], '(') + 2,
 136             mb_strpos($row1['Type'], ')')
 137             - mb_strpos($row1['Type'], '(') - 3
 138         )
 139     );
 140
 141     $users_grants = explode(',', $row['Table_priv']);
 142
 143     foreach ($av_grants as $current_grant) {
 144         $row[$current_grant . '_priv']
 145             = in_array($current_grant, $users_grants) ? 'Y' : 'N';
 146     }
 147     unset($row['Table_priv']);
 148 }
 149
 150
 151 /**
 152  * Extracts the privilege information of a priv table row
 153  *
 154  * @param array|null $row        the row
 155  * @param boolean    $enableHTML add <dfn> tag with tooltips
 156  * @param boolean    $tablePrivs whether row contains table privileges
 157  *
 158  * @global  resource $user_link the database connection
 159  *
 160  * @return array
 161  */
 162 function PMA_extractPrivInfo($row = null, $enableHTML = false, $tablePrivs = false)
 163 {
 164     if ($tablePrivs) {
 165         $grants = PMA_getTableGrantsArray();
 166     } else {
 167         $grants = PMA_getGrantsArray();
 168     }
 169
 170     if (! is_null($row) && isset($row['Table_priv'])) {
 171         PMA_fillInTablePrivileges($row);
 172     }
 173
 174     $privs = array();
 175     $allPrivileges = true;
 176     foreach ($grants as $current_grant) {
 177         if ((! is_null($row) && isset($row[$current_grant[0]]))
 178             || (is_null($row) && isset($GLOBALS[$current_grant[0]]))
 179         ) {
 180             if ((! is_null($row) && $row[$current_grant[0]] == 'Y')
 181                 || (is_null($row)
 182                 && ($GLOBALS[$current_grant[0]] == 'Y'
 183                 || (is_array($GLOBALS[$current_grant[0]])
 184                 && count($GLOBALS[$current_grant[0]]) == $_REQUEST['column_count']
 185                 && empty($GLOBALS[$current_grant[0] . '_none']))))
 186             ) {
 187                 $privs[] = PMA_formatPrivilege($current_grant, $enableHTML);
 188             } elseif (! empty($GLOBALS[$current_grant[0]])
 189                 && is_array($GLOBALS[$current_grant[0]])
 190                 && empty($GLOBALS[$current_grant[0] . '_none'])
 191             ) {
 192                 // Required for proper escaping of ` (backtick) in a column name
 193                 $grant_cols = array_map(
 194                     function($val) {
 195                         return Util::backquote($val);
 196                     },
 197                     $GLOBALS[$current_grant[0]]
 198                 );
 199
 200                 $privs[] = PMA_formatPrivilege($current_grant, $enableHTML)
 201                     . ' (' . join(', ', $grant_cols) . ')';
 202             } else {
 203                 $allPrivileges = false;
 204             }
 205         }
 206     }
 207     if (empty($privs)) {
 208         if ($enableHTML) {
 209             $privs[] = '<dfn title="' . __('No privileges.') . '">USAGE</dfn>';
 210         } else {
 211             $privs[] = 'USAGE';
 212         }
 213     } elseif ($allPrivileges
 214         && (! isset($_POST['grant_count']) || count($privs) == $_POST['grant_count'])
 215     ) {
 216         if ($enableHTML) {
 217             $privs = array('<dfn title="'
 218                 . __('Includes all privileges except GRANT.')
 219                 . '">ALL PRIVILEGES</dfn>'
 220             );
 221         } else {
 222             $privs = array('ALL PRIVILEGES');
 223         }
 224     }
 225     return $privs;
 226 } // end of the 'PMA_extractPrivInfo()' function
 227
 228 /**
 229  * Returns an array of table grants and their descriptions
 230  *
 231  * @return array array of table grants
 232  */
 233 function PMA_getTableGrantsArray()
 234 {
 235     return array(
 236         array(
 237             'Delete',
 238             'DELETE',
 239             $GLOBALS['strPrivDescDelete']
 240         ),
 241         array(
 242             'Create',
 243             'CREATE',
 244             $GLOBALS['strPrivDescCreateTbl']
 245         ),
 246         array(
 247             'Drop',
 248             'DROP',
 249             $GLOBALS['strPrivDescDropTbl']
 250         ),
 251         array(
 252             'Index',
 253             'INDEX',
 254             $GLOBALS['strPrivDescIndex']
 255         ),
 256         array(
 257             'Alter',
 258             'ALTER',
 259             $GLOBALS['strPrivDescAlter']
 260         ),
 261         array(
 262             'Create View',
 263             'CREATE_VIEW',
 264             $GLOBALS['strPrivDescCreateView']
 265         ),
 266         array(
 267             'Show view',
 268             'SHOW_VIEW',
 269             $GLOBALS['strPrivDescShowView']
 270         ),
 271         array(
 272             'Trigger',
 273             'TRIGGER',
 274             $GLOBALS['strPrivDescTrigger']
 275         ),
 276     );
 277 }
 278
 279 /**
 280  * Get the grants array which contains all the privilege types
 281  * and relevant grant messages
 282  *
 283  * @return array
 284  */
 285 function PMA_getGrantsArray()
 286 {
 287     return array(
 288         array(
 289             'Select_priv',
 290             'SELECT',
 291             __('Allows reading data.')
 292         ),
 293         array(
 294             'Insert_priv',
 295             'INSERT',
 296             __('Allows inserting and replacing data.')
 297         ),
 298         array(
 299             'Update_priv',
 300             'UPDATE',
 301             __('Allows changing data.')
 302         ),
 303         array(
 304             'Delete_priv',
 305             'DELETE',
 306             __('Allows deleting data.')
 307         ),
 308         array(
 309             'Create_priv',
 310             'CREATE',
 311             __('Allows creating new databases and tables.')
 312         ),
 313         array(
 314             'Drop_priv',
 315             'DROP',
 316             __('Allows dropping databases and tables.')
 317         ),
 318         array(
 319             'Reload_priv',
 320             'RELOAD',
 321             __('Allows reloading server settings and flushing the server\'s caches.')
 322         ),
 323         array(
 324             'Shutdown_priv',
 325             'SHUTDOWN',
 326             __('Allows shutting down the server.')
 327         ),
 328         array(
 329             'Process_priv',
 330             'PROCESS',
 331             __('Allows viewing processes of all users.')
 332         ),
 333         array(
 334             'File_priv',
 335             'FILE',
 336             __('Allows importing data from and exporting data into files.')
 337         ),
 338         array(
 339             'References_priv',
 340             'REFERENCES',
 341             __('Has no effect in this MySQL version.')
 342         ),
 343         array(
 344             'Index_priv',
 345             'INDEX',
 346             __('Allows creating and dropping indexes.')
 347         ),
 348         array(
 349             'Alter_priv',
 350             'ALTER',
 351             __('Allows altering the structure of existing tables.')
 352         ),
 353         array(
 354             'Show_db_priv',
 355             'SHOW DATABASES',
 356             __('Gives access to the complete list of databases.')
 357         ),
 358         array(
 359             'Super_priv',
 360             'SUPER',
 361             __(
 362                 'Allows connecting, even if maximum number of connections '
 363                 . 'is reached; required for most administrative operations '
 364                 . 'like setting global variables or killing threads of other users.'
 365             )
 366         ),
 367         array(
 368             'Create_tmp_table_priv',
 369             'CREATE TEMPORARY TABLES',
 370             __('Allows creating temporary tables.')
 371         ),
 372         array(
 373             'Lock_tables_priv',
 374             'LOCK TABLES',
 375             __('Allows locking tables for the current thread.')
 376         ),
 377         array(
 378             'Repl_slave_priv',
 379             'REPLICATION SLAVE',
 380             __('Needed for the replication slaves.')
 381         ),
 382         array(
 383             'Repl_client_priv',
 384             'REPLICATION CLIENT',
 385             __('Allows the user to ask where the slaves / masters are.')
 386         ),
 387         array(
 388             'Create_view_priv',
 389             'CREATE VIEW',
 390             __('Allows creating new views.')
 391         ),
 392         array(
 393             'Event_priv',
 394             'EVENT',
 395             __('Allows to set up events for the event scheduler.')
 396         ),
 397         array(
 398             'Trigger_priv',
 399             'TRIGGER',
 400             __('Allows creating and dropping triggers.')
 401         ),
 402         // for table privs:
 403         array(
 404             'Create View_priv',
 405             'CREATE VIEW',
 406             __('Allows creating new views.')
 407         ),
 408         array(
 409             'Show_view_priv',
 410             'SHOW VIEW',
 411             __('Allows performing SHOW CREATE VIEW queries.')
 412         ),
 413         // for table privs:
 414         array(
 415             'Show view_priv',
 416             'SHOW VIEW',
 417             __('Allows performing SHOW CREATE VIEW queries.')
 418         ),
 419         array(
 420             'Create_routine_priv',
 421             'CREATE ROUTINE',
 422             __('Allows creating stored routines.')
 423         ),
 424         array(
 425             'Alter_routine_priv',
 426             'ALTER ROUTINE',
 427             __('Allows altering and dropping stored routines.')
 428         ),
 429         array(
 430             'Create_user_priv',
 431             'CREATE USER',
 432             __('Allows creating, dropping and renaming user accounts.')
 433         ),
 434         array(
 435             'Execute_priv',
 436             'EXECUTE',
 437             __('Allows executing stored routines.')
 438         ),
 439     );
 440 }
 441
 442 /**
 443  * Displays on which column(s) a table-specific privilege is granted
 444  *
 445  * @param array  $columns          columns array
 446  * @param array  $row              first row from result or boolean false
 447  * @param string $name_for_select  privilege types - Select_priv, Insert_priv
 448  *                                 Update_priv, References_priv
 449  * @param string $priv_for_header  privilege for header
 450  * @param string $name             privilege name: insert, select, update, references
 451  * @param string $name_for_dfn     name for dfn
 452  * @param string $name_for_current name for current
 453  *
 454  * @return string $html_output html snippet
 455  */
 456 function PMA_getHtmlForColumnPrivileges($columns, $row, $name_for_select,
 457     $priv_for_header, $name, $name_for_dfn, $name_for_current
 458 ) {
 459     $data = array(
 460         'columns'          => $columns,
 461         'row'              => $row,
 462         'name_for_select'  => $name_for_select,
 463         'priv_for_header'  => $priv_for_header,
 464         'name'             => $name,
 465         'name_for_dfn'     => $name_for_dfn,
 466         'name_for_current' => $name_for_current
 467     );
 468
 469     $html_output = Template::get('privileges/column_privileges')
 470         ->render($data);
 471
 472     return $html_output;
 473 } // end function
 474
 475 /**
 476  * Get sql query for display privileges table
 477  *
 478  * @param string $db       the database
 479  * @param string $table    the table
 480  * @param string $username username for database connection
 481  * @param string $hostname hostname for database connection
 482  *
 483  * @return string sql query
 484  */
 485 function PMA_getSqlQueryForDisplayPrivTable($db, $table, $username, $hostname)
 486 {
 487     if ($db == '*') {
 488         return "SELECT * FROM `mysql`.`user`"
 489             . " WHERE `User` = '" . $GLOBALS['dbi']->escapeString($username) . "'"
 490             . " AND `Host` = '" . $GLOBALS['dbi']->escapeString($hostname) . "';";
 491     } elseif ($table == '*') {
 492         return "SELECT * FROM `mysql`.`db`"
 493             . " WHERE `User` = '" . $GLOBALS['dbi']->escapeString($username) . "'"
 494             . " AND `Host` = '" . $GLOBALS['dbi']->escapeString($hostname) . "'"
 495             . " AND '" . $GLOBALS['dbi']->escapeString(Util::unescapeMysqlWildcards($db)) . "'"
 496             . " LIKE `Db`;";
 497     }
 498     return "SELECT `Table_priv`"
 499         . " FROM `mysql`.`tables_priv`"
 500         . " WHERE `User` = '" . $GLOBALS['dbi']->escapeString($username) . "'"
 501         . " AND `Host` = '" . $GLOBALS['dbi']->escapeString($hostname) . "'"
 502         . " AND `Db` = '" . $GLOBALS['dbi']->escapeString(Util::unescapeMysqlWildcards($db)) . "'"
 503         . " AND `Table_name` = '" . $GLOBALS['dbi']->escapeString($table) . "';";
 504 }
 505
 506 /**
 507  * Displays a dropdown to select the user group
 508  * with menu items configured to each of them.
 509  *
 510  * @param string $username username
 511  *
 512  * @return string html to select the user group
 513  */
 514 function PMA_getHtmlToChooseUserGroup($username)
 515 {
 516     $cfgRelation = PMA_getRelationsParam();
 517     $groupTable = Util::backquote($cfgRelation['db'])
 518         . "." . Util::backquote($cfgRelation['usergroups']);
 519     $userTable = Util::backquote($cfgRelation['db'])
 520         . "." . Util::backquote($cfgRelation['users']);
 521
 522     $userGroup = '';
 523     if (isset($GLOBALS['username'])) {
 524         $sql_query = "SELECT `usergroup` FROM " . $userTable
 525             . " WHERE `username` = '" . $GLOBALS['dbi']->escapeString($username) . "'";
 526         $userGroup = $GLOBALS['dbi']->fetchValue(
 527             $sql_query, 0, 0, $GLOBALS['controllink']
 528         );
 529     }
 530
 531     $allUserGroups = array('' => '');
 532     $sql_query = "SELECT DISTINCT `usergroup` FROM " . $groupTable;
 533     $result = PMA_queryAsControlUser($sql_query, false);
 534     if ($result) {
 535         while ($row = $GLOBALS['dbi']->fetchRow($result)) {
 536             $allUserGroups[$row[0]] = $row[0];
 537         }
 538     }
 539     $GLOBALS['dbi']->freeResult($result);
 540
 541     // render the template
 542     $data = array(
 543         'allUserGroups'   => $allUserGroups,
 544         'userGroup'       => $userGroup,
 545         'params'          => array('username' => $username)
 546     );
 547     $html_output = Template::get('privileges/choose_user_group')
 548         ->render($data);
 549
 550     return $html_output;
 551 }
 552
 553 /**
 554  * Sets the user group from request values
 555  *
 556  * @param string $username  username
 557  * @param string $userGroup user group to set
 558  *
 559  * @return void
 560  */
 561 function PMA_setUserGroup($username, $userGroup)
 562 {
 563     $cfgRelation = PMA_getRelationsParam();
 564     if (empty($cfgRelation['db']) || empty($cfgRelation['users']) || empty($cfgRelation['usergroups'])) {
 565         return;
 566     }
 567
 568     $userTable = Util::backquote($cfgRelation['db'])
 569         . "." . Util::backquote($cfgRelation['users']);
 570
 571     $sql_query = "SELECT `usergroup` FROM " . $userTable
 572         . " WHERE `username` = '" . $GLOBALS['dbi']->escapeString($username) . "'";
 573     $oldUserGroup = $GLOBALS['dbi']->fetchValue(
 574         $sql_query, 0, 0, $GLOBALS['controllink']
 575     );
 576
 577     if ($oldUserGroup === false) {
 578         $upd_query = "INSERT INTO " . $userTable . "(`username`, `usergroup`)"
 579             . " VALUES ('" . $GLOBALS['dbi']->escapeString($username) . "', "
 580             . "'" . $GLOBALS['dbi']->escapeString($userGroup) . "')";
 581     } else {
 582         if (empty($userGroup)) {
 583             $upd_query = "DELETE FROM " . $userTable
 584                 . " WHERE `username`='" . $GLOBALS['dbi']->escapeString($username) . "'";
 585         } elseif ($oldUserGroup != $userGroup) {
 586             $upd_query = "UPDATE " . $userTable
 587                 . " SET `usergroup`='" . $GLOBALS['dbi']->escapeString($userGroup) . "'"
 588                 . " WHERE `username`='" . $GLOBALS['dbi']->escapeString($username) . "'";
 589         }
 590     }
 591     if (isset($upd_query)) {
 592         PMA_queryAsControlUser($upd_query);
 593     }
 594 }
 595
 596 /**
 597  * Displays the privileges form table
 598  *
 599  * @param string  $db     the database
 600  * @param string  $table  the table
 601  * @param boolean $submit whether to display the submit button or not
 602  *
 603  * @global  array     $cfg         the phpMyAdmin configuration
 604  * @global  resource  $user_link   the database connection
 605  *
 606  * @return string html snippet
 607  */
 608 function PMA_getHtmlToDisplayPrivilegesTable($db = '*',
 609     $table = '*', $submit = true
 610 ) {
 611     $html_output = '';
 612     $sql_query = '';
 613
 614     if ($db == '*') {
 615         $table = '*';
 616     }
 617
 618     if (isset($GLOBALS['username'])) {
 619         $username = $GLOBALS['username'];
 620         $hostname = $GLOBALS['hostname'];
 621         $sql_query = PMA_getSqlQueryForDisplayPrivTable(
 622             $db, $table, $username, $hostname
 623         );
 624         $row = $GLOBALS['dbi']->fetchSingleRow($sql_query);
 625     }
 626     if (empty($row)) {
 627         if ($table == '*' && $GLOBALS['is_superuser']) {
 628             $row = array();
 629             if ($db == '*') {
 630                 $sql_query = 'SHOW COLUMNS FROM `mysql`.`user`;';
 631             } elseif ($table == '*') {
 632                 $sql_query = 'SHOW COLUMNS FROM `mysql`.`db`;';
 633             }
 634             $res = $GLOBALS['dbi']->query($sql_query);
 635             while ($row1 = $GLOBALS['dbi']->fetchRow($res)) {
 636                 if (mb_substr($row1[0], 0, 4) == 'max_') {
 637                     $row[$row1[0]] = 0;
 638                 } elseif (mb_substr($row1[0], 0, 5) == 'x509_'
 639                     || mb_substr($row1[0], 0, 4) == 'ssl_'
 640                 ) {
 641                     $row[$row1[0]] = '';
 642                 } else {
 643                     $row[$row1[0]] = 'N';
 644                 }
 645             }
 646             $GLOBALS['dbi']->freeResult($res);
 647         } elseif ($table == '*') {
 648             $row = array();
 649         } else {
 650             $row = array('Table_priv' => '');
 651         }
 652     }
 653     if (isset($row['Table_priv'])) {
 654         PMA_fillInTablePrivileges($row);
 655
 656         // get columns
 657         $res = $GLOBALS['dbi']->tryQuery(
 658             'SHOW COLUMNS FROM '
 659             . Util::backquote(
 660                 Util::unescapeMysqlWildcards($db)
 661             )
 662             . '.' . Util::backquote($table) . ';'
 663         );
 664         $columns = array();
 665         if ($res) {
 666             while ($row1 = $GLOBALS['dbi']->fetchRow($res)) {
 667                 $columns[$row1[0]] = array(
 668                     'Select' => false,
 669                     'Insert' => false,
 670                     'Update' => false,
 671                     'References' => false
 672                 );
 673             }
 674             $GLOBALS['dbi']->freeResult($res);
 675         }
 676         unset($res, $row1);
 677     }
 678     // table-specific privileges
 679     if (! empty($columns)) {
 680         $html_output .= PMA_getHtmlForTableSpecificPrivileges(
 681             $username, $hostname, $db, $table, $columns, $row
 682         );
 683     } else {
 684         // global or db-specific
 685         $html_output .= PMA_getHtmlForGlobalOrDbSpecificPrivs($db, $table, $row);
 686     }
 687     $html_output .= '</fieldset>' . "\n";
 688     if ($submit) {
 689         $html_output .= '<fieldset id="fieldset_user_privtable_footer" '
 690             . 'class="tblFooters">' . "\n"
 691             . '<input type="hidden" name="update_privs" value="1" />' . "\n"
 692             . '<input type="submit" value="' . __('Go') . '" />' . "\n"
 693             . '</fieldset>' . "\n";
 694     }
 695     return $html_output;
 696 } // end of the 'PMA_displayPrivTable()' function
 697
 698 /**
 699  * Get HTML for "Require"
 700  *
 701  * @param array $row privilege array
 702  *
 703  * @return string html snippet
 704  */
 705 function PMA_getHtmlForRequires($row)
 706 {
 707     $specified = (isset($row['ssl_type']) && $row['ssl_type'] == 'SPECIFIED');
 708     $require_options = array(
 709         array(
 710             'name'        => 'ssl_type',
 711             'value'       => 'NONE',
 712             'description' => __(
 713                 'Does not require SSL-encrypted connections.'
 714             ),
 715             'label'       => 'REQUIRE NONE',
 716             'checked'     => ((isset($row['ssl_type'])
 717                 && ($row['ssl_type'] == 'NONE'
 718                     || $row['ssl_type'] == ''))
 719                 ? 'checked="checked"'
 720                 : ''
 721             ),
 722             'disabled'    => false,
 723             'radio'       => true
 724         ),
 725         array(
 726             'name'        => 'ssl_type',
 727             'value'       => 'ANY',
 728             'description' => __(
 729                 'Requires SSL-encrypted connections.'
 730             ),
 731             'label'       => 'REQUIRE SSL',
 732             'checked'     => (isset($row['ssl_type']) && ($row['ssl_type'] == 'ANY')
 733                 ? 'checked="checked"'
 734                 : ''
 735             ),
 736             'disabled'    => false,
 737             'radio'       => true
 738         ),
 739         array(
 740             'name'        => 'ssl_type',
 741             'value'       => 'X509',
 742             'description' => __(
 743                 'Requires a valid X509 certificate.'
 744             ),
 745             'label'       => 'REQUIRE X509',
 746             'checked'     => (isset($row['ssl_type']) && ($row['ssl_type'] == 'X509')
 747                 ? 'checked="checked"'
 748                 : ''
 749             ),
 750             'disabled'    => false,
 751             'radio'       => true
 752         ),
 753         array(
 754             'name'        => 'ssl_type',
 755             'value'       => 'SPECIFIED',
 756             'description' => '',
 757             'label'       => 'SPECIFIED',
 758             'checked'     => ($specified ? 'checked="checked"' : ''),
 759             'disabled'    => false,
 760             'radio'       => true
 761         ),
 762         array(
 763             'name'        => 'ssl_cipher',
 764             'value'       => (isset($row['ssl_cipher'])
 765                 ? htmlspecialchars($row['ssl_cipher']) : ''
 766             ),
 767             'description' => __(
 768                 'Requires that a specific cipher method be used for a connection.'
 769             ),
 770             'label'       => 'REQUIRE CIPHER',
 771             'checked'     => '',
 772             'disabled'    => ! $specified,
 773             'radio'       => false
 774         ),
 775         array(
 776             'name'        => 'x509_issuer',
 777             'value'       => (isset($row['x509_issuer'])
 778                 ? htmlspecialchars($row['x509_issuer']) : ''
 779             ),
 780             'description' => __(
 781                 'Requires that a valid X509 certificate issued by this CA be presented.'
 782             ),
 783             'label'       => 'REQUIRE ISSUER',
 784             'checked'     => '',
 785             'disabled'    => ! $specified,
 786             'radio'       => false
 787         ),
 788         array(
 789             'name'        => 'x509_subject',
 790             'value'       => (isset($row['x509_subject'])
 791                 ? htmlspecialchars($row['x509_subject']) : ''
 792             ),
 793             'description' => __(
 794                 'Requires that a valid X509 certificate with this subject be presented.'
 795             ),
 796             'label'       => 'REQUIRE SUBJECT',
 797             'checked'     => '',
 798             'disabled'    => ! $specified,
 799             'radio'       => false
 800         ),
 801     );
 802
 803     $html_output = Template::get('privileges/require_options')
 804         ->render(array('require_options' => $require_options));
 805
 806     return $html_output;
 807 }
 808
 809 /**
 810  * Get HTML for "Resource limits"
 811  *
 812  * @param array $row first row from result or boolean false
 813  *
 814  * @return string html snippet
 815  */
 816 function PMA_getHtmlForResourceLimits($row)
 817 {
 818     $limits = array(
 819         array(
 820             'input_name'  => 'max_questions',
 821             'name_main'   => 'MAX QUERIES PER HOUR',
 822             'value'       => (isset($row['max_questions']) ? $row['max_questions'] : '0'),
 823             'description' => __(
 824                 'Limits the number of queries the user may send to the server per hour.'
 825             )
 826         ),
 827         array(
 828             'input_name'  => 'max_updates',
 829             'name_main'   => 'MAX UPDATES PER HOUR',
 830             'value'       => (isset($row['max_updates']) ? $row['max_updates'] : '0'),
 831             'description' => __(
 832                 'Limits the number of commands that change any table '
 833                 . 'or database the user may execute per hour.'
 834             )
 835         ),
 836         array(
 837             'input_name'  => 'max_connections',
 838             'name_main'   => 'MAX CONNECTIONS PER HOUR',
 839             'value'       => (isset($row['max_connections']) ? $row['max_connections'] : '0'),
 840             'description' => __(
 841                 'Limits the number of new connections the user may open per hour.'
 842             )
 843         ),
 844         array(
 845             'input_name'  => 'max_user_connections',
 846             'name_main'   => 'MAX USER_CONNECTIONS',
 847             'value'       => (isset($row['max_user_connections']) ?
 848                 $row['max_user_connections'] : '0'),
 849             'description' => __(
 850                 'Limits the number of simultaneous connections '
 851                 . 'the user may have.'
 852             )
 853         )
 854     );
 855
 856     $html_output = Template::get('privileges/resource_limits')
 857         ->render(array('limits' => $limits));
 858
 859     $html_output .= '</fieldset>' . "\n";
 860
 861     return $html_output;
 862 }
 863
 864 /**
 865  * Get the HTML snippet for routine specific privileges
 866  *
 867  * @param string $username   username for database connection
 868  * @param string $hostname   hostname for database connection
 869  * @param string $db         the database
 870  * @param string $routine    the routine
 871  * @param string $url_dbname url encoded db name
 872  *
 873  * @return string $html_output
 874  */
 875 function PMA_getHtmlForRoutineSpecificPrivilges(
 876     $username, $hostname, $db, $routine, $url_dbname
 877 ) {
 878     $header = PMA_getHtmlHeaderForUserProperties(
 879         false, $url_dbname, $db, $username, $hostname,
 880         $routine, 'routine'
 881     );
 882
 883     $sql = "SELECT `Proc_priv`"
 884         . " FROM `mysql`.`procs_priv`"
 885         . " WHERE `User` = '" . $GLOBALS['dbi']->escapeString($username) . "'"
 886         . " AND `Host` = '" . $GLOBALS['dbi']->escapeString($hostname) . "'"
 887         . " AND `Db` = '"
 888         . $GLOBALS['dbi']->escapeString(Util::unescapeMysqlWildcards($db)) . "'"
 889         . " AND `Routine_name` LIKE '" . $GLOBALS['dbi']->escapeString($routine) . "';";
 890     $res = $GLOBALS['dbi']->fetchValue($sql);
 891
 892     $privs = PMA_parseProcPriv($res);
 893
 894     $routineArray   = array(PMA_getTriggerPrivilegeTable());
 895     $privTableNames = array(__('Routine'));
 896     $privCheckboxes = PMA_getHtmlForGlobalPrivTableWithCheckboxes(
 897         $routineArray, $privTableNames, $privs
 898     );
 899
 900     $data = array(
 901         'username'       => $username,
 902         'hostname'       => $hostname,
 903         'database'       => $db,
 904         'routine'        => $routine,
 905         'grantCount'     => count($privs),
 906         'privCheckboxes' => $privCheckboxes,
 907         'header'         => $header,
 908     );
 909     $html_output = Template::get('privileges/edit_routine_privileges')
 910         ->render($data);
 911
 912     return $html_output;
 913 }
 914
 915 /**
 916  * Get routine privilege table as an array
 917  *
 918  * @return privilege type array
 919  */
 920 function PMA_getTriggerPrivilegeTable()
 921 {
 922     $routinePrivTable = array(
 923         array(
 924             'Grant',
 925             'GRANT',
 926             __(
 927                 'Allows user to give to other users or remove from other users '
 928                 . 'privileges that user possess on this routine.'
 929             )
 930         ),
 931         array(
 932             'Alter_routine',
 933             'ALTER ROUTINE',
 934             __('Allows altering and dropping this routine.')
 935         ),
 936         array(
 937             'Execute',
 938             'EXECUTE',
 939             __('Allows executing this routine.')
 940         )
 941     );
 942     return $routinePrivTable;
 943 }
 944
 945 /**
 946  * Get the HTML snippet for table specific privileges
 947  *
 948  * @param string $username username for database connection
 949  * @param string $hostname hostname for database connection
 950  * @param string $db       the database
 951  * @param string $table    the table
 952  * @param array  $columns  columns array
 953  * @param array  $row      current privileges row
 954  *
 955  * @return string $html_output
 956  */
 957 function PMA_getHtmlForTableSpecificPrivileges(
 958     $username, $hostname, $db, $table, $columns, $row
 959 ) {
 960     $res = $GLOBALS['dbi']->query(
 961         'SELECT `Column_name`, `Column_priv`'
 962         . ' FROM `mysql`.`columns_priv`'
 963         . ' WHERE `User`'
 964         . ' = \'' . $GLOBALS['dbi']->escapeString($username) . "'"
 965         . ' AND `Host`'
 966         . ' = \'' . $GLOBALS['dbi']->escapeString($hostname) . "'"
 967         . ' AND `Db`'
 968         . ' = \'' . $GLOBALS['dbi']->escapeString(
 969             Util::unescapeMysqlWildcards($db)
 970         ) . "'"
 971         . ' AND `Table_name`'
 972         . ' = \'' . $GLOBALS['dbi']->escapeString($table) . '\';'
 973     );
 974
 975     while ($row1 = $GLOBALS['dbi']->fetchRow($res)) {
 976         $row1[1] = explode(',', $row1[1]);
 977         foreach ($row1[1] as $current) {
 978             $columns[$row1[0]][$current] = true;
 979         }
 980     }
 981     $GLOBALS['dbi']->freeResult($res);
 982     unset($res, $row1, $current);
 983
 984     $html_output = '<input type="hidden" name="grant_count" '
 985         . 'value="' . count($row) . '" />' . "\n"
 986         . '<input type="hidden" name="column_count" '
 987         . 'value="' . count($columns) . '" />' . "\n"
 988         . '<fieldset id="fieldset_user_priv">' . "\n"
 989         . '<legend data-submenu-label="' . __('Table') . '">' . __('Table-specific privileges')
 990         . '</legend>'
 991         . '<p><small><i>'
 992         . __('Note: MySQL privilege names are expressed in English.')
 993         . '</i></small></p>';
 994
 995     // privs that are attached to a specific column
 996     $html_output .= PMA_getHtmlForAttachedPrivilegesToTableSpecificColumn(
 997         $columns, $row
 998     );
 999
1000     // privs that are not attached to a specific column
1001     $html_output .= '<div class="item">' . "\n"
1002         . PMA_getHtmlForNotAttachedPrivilegesToTableSpecificColumn($row)
1003         . '</div>' . "\n";
1004
1005     // for Safari 2.0.2
1006     $html_output .= '<div class="clearfloat"></div>' . "\n";
1007
1008     return $html_output;
1009 }
1010
1011 /**
1012  * Get HTML snippet for privileges that are attached to a specific column
1013  *
1014  * @param array $columns columns array
1015  * @param array $row     first row from result or boolean false
1016  *
1017  * @return string $html_output
1018  */
1019 function PMA_getHtmlForAttachedPrivilegesToTableSpecificColumn($columns, $row)
1020 {
1021     $html_output = PMA_getHtmlForColumnPrivileges(
1022         $columns, $row, 'Select_priv', 'SELECT',
1023         'select', __('Allows reading data.'), 'Select'
1024     );
1025
1026     $html_output .= PMA_getHtmlForColumnPrivileges(
1027         $columns, $row, 'Insert_priv', 'INSERT',
1028         'insert', __('Allows inserting and replacing data.'), 'Insert'
1029     );
1030
1031     $html_output .= PMA_getHtmlForColumnPrivileges(
1032         $columns, $row, 'Update_priv', 'UPDATE',
1033         'update', __('Allows changing data.'), 'Update'
1034     );
1035
1036     $html_output .= PMA_getHtmlForColumnPrivileges(
1037         $columns, $row, 'References_priv', 'REFERENCES', 'references',
1038         __('Has no effect in this MySQL version.'), 'References'
1039     );
1040     return $html_output;
1041 }
1042
1043 /**
1044  * Get HTML for privileges that are not attached to a specific column
1045  *
1046  * @param array $row first row from result or boolean false
1047  *
1048  * @return string $html_output
1049  */
1050 function PMA_getHtmlForNotAttachedPrivilegesToTableSpecificColumn($row)
1051 {
1052     $html_output = '';
1053
1054     foreach ($row as $current_grant => $current_grant_value) {
1055         $grant_type = substr($current_grant, 0, -5);
1056         if (in_array($grant_type, array('Select', 'Insert', 'Update', 'References'))
1057         ) {
1058             continue;
1059         }
1060         // make a substitution to match the messages variables;
1061         // also we must substitute the grant we get, because we can't generate
1062         // a form variable containing blanks (those would get changed to
1063         // an underscore when receiving the POST)
1064         if ($current_grant == 'Create View_priv') {
1065             $tmp_current_grant = 'CreateView_priv';
1066             $current_grant = 'Create_view_priv';
1067         } elseif ($current_grant == 'Show view_priv') {
1068             $tmp_current_grant = 'ShowView_priv';
1069             $current_grant = 'Show_view_priv';
1070         } else {
1071             $tmp_current_grant = $current_grant;
1072         }
1073
1074         $html_output .= '<div class="item">' . "\n"
1075            . '<input type="checkbox"'
1076            . ' name="' . $current_grant . '" id="checkbox_' . $current_grant
1077            . '" value="Y" '
1078            . ($current_grant_value == 'Y' ? 'checked="checked" ' : '')
1079            . 'title="';
1080
1081         $privGlobalName = 'strPrivDesc'
1082             . mb_substr(
1083                 $tmp_current_grant,
1084                 0,
1085                 (mb_strlen($tmp_current_grant) - 5)
1086             );
1087         $html_output .= (isset($GLOBALS[$privGlobalName])
1088                 ? $GLOBALS[$privGlobalName]
1089                 : $GLOBALS[$privGlobalName . 'Tbl']
1090             )
1091             . '"/>' . "\n";
1092
1093         $privGlobalName1 = 'strPrivDesc'
1094             . mb_substr(
1095                 $tmp_current_grant,
1096                 0,
1097                 - 5
1098             );
1099         $html_output .= '<label for="checkbox_' . $current_grant
1100             . '"><code><dfn title="'
1101             . (isset($GLOBALS[$privGlobalName1])
1102                 ? $GLOBALS[$privGlobalName1]
1103                 : $GLOBALS[$privGlobalName1 . 'Tbl']
1104             )
1105             . '">'
1106             . mb_strtoupper(
1107                 mb_substr(
1108                     $current_grant,
1109                     0,
1110                     -5
1111                 )
1112             )
1113             . '</dfn></code></label>' . "\n"
1114             . '</div>' . "\n";
1115     } // end foreach ()
1116     return $html_output;
1117 }
1118
1119 /**
1120  * Get HTML for global or database specific privileges
1121  *
1122  * @param string $db    the database
1123  * @param string $table the table
1124  * @param array  $row   first row from result or boolean false
1125  *
1126  * @return string $html_output
1127  */
1128 function PMA_getHtmlForGlobalOrDbSpecificPrivs($db, $table, $row)
1129 {
1130     $privTable_names = array(0 => __('Data'),
1131         1 => __('Structure'),
1132         2 => __('Administration')
1133     );
1134     $privTable = array();
1135     // d a t a
1136     $privTable[0] = PMA_getDataPrivilegeTable($db);
1137
1138     // s t r u c t u r e
1139     $privTable[1] = PMA_getStructurePrivilegeTable($table, $row);
1140
1141     // a d m i n i s t r a t i o n
1142     $privTable[2] = PMA_getAdministrationPrivilegeTable($db);
1143
1144     $html_output = '<input type="hidden" name="grant_count" value="'
1145         . (count($privTable[0])
1146             + count($privTable[1])
1147             + count($privTable[2])
1148             - (isset($row['Grant_priv']) ? 1 : 0)
1149         )
1150         . '" />';
1151     if ($db == '*') {
1152         $legend     = __('Global privileges');
1153         $menu_label = __('Global');
1154     } else if ($table == '*') {
1155         $legend     = __('Database-specific privileges');
1156         $menu_label = __('Database');
1157     } else {
1158         $legend     = __('Table-specific privileges');
1159         $menu_label = __('Table');
1160     }
1161     $html_output .= '<fieldset id="fieldset_user_global_rights">'
1162         . '<legend data-submenu-label="' . $menu_label . '">' . $legend
1163         . '<input type="checkbox" id="addUsersForm_checkall" '
1164         . 'class="checkall_box" title="' . __('Check all') . '" /> '
1165         . '<label for="addUsersForm_checkall">' . __('Check all') . '</label> '
1166         . '</legend>'
1167         . '<p><small><i>'
1168         . __('Note: MySQL privilege names are expressed in English.')
1169         . '</i></small></p>';
1170
1171     // Output the Global privilege tables with checkboxes
1172     $html_output .= PMA_getHtmlForGlobalPrivTableWithCheckboxes(
1173         $privTable, $privTable_names, $row
1174     );
1175
1176     // The "Resource limits" box is not displayed for db-specific privs
1177     if ($db == '*') {
1178         $html_output .= PMA_getHtmlForResourceLimits($row);
1179         $html_output .= PMA_getHtmlForRequires($row);
1180     }
1181     // for Safari 2.0.2
1182     $html_output .= '<div class="clearfloat"></div>';
1183
1184     return $html_output;
1185 }
1186
1187 /**
1188  * Get data privilege table as an array
1189  *
1190  * @param string $db the database
1191  *
1192  * @return string data privilege table
1193  */
1194 function PMA_getDataPrivilegeTable($db)
1195 {
1196     $data_privTable = array(
1197         array('Select', 'SELECT', __('Allows reading data.')),
1198         array('Insert', 'INSERT', __('Allows inserting and replacing data.')),
1199         array('Update', 'UPDATE', __('Allows changing data.')),
1200         array('Delete', 'DELETE', __('Allows deleting data.'))
1201     );
1202     if ($db == '*') {
1203         $data_privTable[]
1204             = array('File',
1205                 'FILE',
1206                 __('Allows importing data from and exporting data into files.')
1207             );
1208     }
1209     return $data_privTable;
1210 }
1211
1212 /**
1213  * Get structure privilege table as an array
1214  *
1215  * @param string $table the table
1216  * @param array  $row   first row from result or boolean false
1217  *
1218  * @return string structure privilege table
1219  */
1220 function PMA_getStructurePrivilegeTable($table, $row)
1221 {
1222     $structure_privTable = array(
1223         array('Create',
1224             'CREATE',
1225             ($table == '*'
1226                 ? __('Allows creating new databases and tables.')
1227                 : __('Allows creating new tables.')
1228             )
1229         ),
1230         array('Alter',
1231             'ALTER',
1232             __('Allows altering the structure of existing tables.')
1233         ),
1234         array('Index', 'INDEX', __('Allows creating and dropping indexes.')),
1235         array('Drop',
1236             'DROP',
1237             ($table == '*'
1238                 ? __('Allows dropping databases and tables.')
1239                 : __('Allows dropping tables.')
1240             )
1241         ),
1242         array('Create_tmp_table',
1243             'CREATE TEMPORARY TABLES',
1244             __('Allows creating temporary tables.')
1245         ),
1246         array('Show_view',
1247             'SHOW VIEW',
1248             __('Allows performing SHOW CREATE VIEW queries.')
1249         ),
1250         array('Create_routine',
1251             'CREATE ROUTINE',
1252             __('Allows creating stored routines.')
1253         ),
1254         array('Alter_routine',
1255             'ALTER ROUTINE',
1256             __('Allows altering and dropping stored routines.')
1257         ),
1258         array('Execute', 'EXECUTE', __('Allows executing stored routines.')),
1259     );
1260     // this one is for a db-specific priv: Create_view_priv
1261     if (isset($row['Create_view_priv'])) {
1262         $structure_privTable[] = array('Create_view',
1263             'CREATE VIEW',
1264             __('Allows creating new views.')
1265         );
1266     }
1267     // this one is for a table-specific priv: Create View_priv
1268     if (isset($row['Create View_priv'])) {
1269         $structure_privTable[] = array('Create View',
1270             'CREATE VIEW',
1271             __('Allows creating new views.')
1272         );
1273     }
1274     if (isset($row['Event_priv'])) {
1275         // MySQL 5.1.6
1276         $structure_privTable[] = array('Event',
1277             'EVENT',
1278             __('Allows to set up events for the event scheduler.')
1279         );
1280         $structure_privTable[] = array('Trigger',
1281             'TRIGGER',
1282             __('Allows creating and dropping triggers.')
1283         );
1284     }
1285     return $structure_privTable;
1286 }
1287
1288 /**
1289  * Get administration privilege table as an array
1290  *
1291  * @param string $db the table
1292  *
1293  * @return string administration privilege table
1294  */
1295 function PMA_getAdministrationPrivilegeTable($db)
1296 {
1297     if ($db == '*') {
1298         $adminPrivTable = array(
1299             array('Grant',
1300                 'GRANT',
1301                 __(
1302                     'Allows adding users and privileges '
1303                     . 'without reloading the privilege tables.'
1304                 )
1305             ),
1306         );
1307         $adminPrivTable[] = array('Super',
1308             'SUPER',
1309             __(
1310                 'Allows connecting, even if maximum number '
1311                 . 'of connections is reached; required for '
1312                 . 'most administrative operations like '
1313                 . 'setting global variables or killing threads of other users.'
1314             )
1315         );
1316         $adminPrivTable[] = array('Process',
1317             'PROCESS',
1318             __('Allows viewing processes of all users.')
1319         );
1320         $adminPrivTable[] = array('Reload',
1321             'RELOAD',
1322             __('Allows reloading server settings and flushing the server\'s caches.')
1323         );
1324         $adminPrivTable[] = array('Shutdown',
1325             'SHUTDOWN',
1326             __('Allows shutting down the server.')
1327         );
1328         $adminPrivTable[] = array('Show_db',
1329             'SHOW DATABASES',
1330             __('Gives access to the complete list of databases.')
1331         );
1332     }
1333     else {
1334         $adminPrivTable = array(
1335             array('Grant',
1336                 'GRANT',
1337                 __(
1338                     'Allows user to give to other users or remove from other'
1339                     . ' users the privileges that user possess yourself.'
1340                 )
1341            ),
1342         );
1343     }
1344     $adminPrivTable[] = array('Lock_tables',
1345         'LOCK TABLES',
1346         __('Allows locking tables for the current thread.')
1347     );
1348     $adminPrivTable[] = array('References',
1349         'REFERENCES',
1350         __('Has no effect in this MySQL version.')
1351     );
1352     if ($db == '*') {
1353         $adminPrivTable[] = array('Repl_client',
1354             'REPLICATION CLIENT',
1355             __('Allows the user to ask where the slaves / masters are.')
1356         );
1357         $adminPrivTable[] = array('Repl_slave',
1358             'REPLICATION SLAVE',
1359             __('Needed for the replication slaves.')
1360         );
1361         $adminPrivTable[] = array('Create_user',
1362             'CREATE USER',
1363             __('Allows creating, dropping and renaming user accounts.')
1364         );
1365     }
1366     return $adminPrivTable;
1367 }
1368
1369 /**
1370  * Get HTML snippet for global privileges table with check boxes
1371  *
1372  * @param array $privTable       privileges table array
1373  * @param array $privTable_names names of the privilege tables
1374  *                               (Data, Structure, Administration)
1375  * @param array $row             first row from result or boolean false
1376  *
1377  * @return string $html_output
1378  */
1379 function PMA_getHtmlForGlobalPrivTableWithCheckboxes(
1380     $privTable, $privTable_names, $row
1381 ) {
1382     $html_output = '';
1383     $html_output = Template::get('privileges/global_priv_table')
1384         ->render(
1385             array(
1386                 'privTable'       => $privTable,
1387                 'privTable_names' => $privTable_names,
1388                 'row'             => $row
1389             )
1390         );
1391
1392     return $html_output;
1393 }
1394
1395 /**
1396  * Gets the currently active authentication plugins
1397  *
1398  * @param string $orig_auth_plugin Default Authentication plugin
1399  * @param string $mode             are we creating a new user or are we just
1400  *                                 changing  one?
1401  *                                 (allowed values: 'new', 'edit', 'change_pw')
1402  * @param string $versions         Is MySQL version newer or older than 5.5.7
1403  *
1404  * @return string $html_output
1405  */
1406 function PMA_getHtmlForAuthPluginsDropdown(
1407     $orig_auth_plugin,
1408     $mode = 'new',
1409     $versions = 'new'
1410 ) {
1411     $select_id = 'select_authentication_plugin'
1412         . ($mode =='change_pw' ? '_cp' : '');
1413
1414     if ($versions == 'new') {
1415         $active_auth_plugins = PMA_getActiveAuthPlugins();
1416
1417         if (isset($active_auth_plugins['mysql_old_password'])) {
1418             unset($active_auth_plugins['mysql_old_password']);
1419         }
1420     } else {
1421         $active_auth_plugins = array(
1422             'mysql_native_password' => __('Native MySQL authentication')
1423         );
1424     }
1425
1426     $html_output = Util::getDropdown(
1427         'authentication_plugin',
1428         $active_auth_plugins,
1429         $orig_auth_plugin,
1430         $select_id
1431     );
1432
1433     return $html_output;
1434 }
1435
1436 /**
1437  * Gets the currently active authentication plugins
1438  *
1439  * @return array $result  array of plugin names and descriptions
1440  */
1441 function PMA_getActiveAuthPlugins()
1442 {
1443     $get_plugins_query = "SELECT `PLUGIN_NAME`, `PLUGIN_DESCRIPTION`"
1444         . " FROM `information_schema`.`PLUGINS` "
1445         . "WHERE `PLUGIN_TYPE` = 'AUTHENTICATION';";
1446     $resultset = $GLOBALS['dbi']->query($get_plugins_query);
1447
1448     $result = array();
1449
1450     while ($row = $GLOBALS['dbi']->fetchAssoc($resultset)) {
1451         // if description is known, enable its translation
1452         if ('mysql_native_password' == $row['PLUGIN_NAME']) {
1453             $row['PLUGIN_DESCRIPTION'] = __('Native MySQL authentication');
1454         } elseif ('sha256_password' == $row['PLUGIN_NAME']) {
1455             $row['PLUGIN_DESCRIPTION'] = __('SHA256 password authentication');
1456         }
1457
1458         $result[$row['PLUGIN_NAME']] = $row['PLUGIN_DESCRIPTION'];
1459     }
1460
1461     return $result;
1462 }
1463
1464 /**
1465  * Displays the fields used by the "new user" form as well as the
1466  * "change login information / copy user" form.
1467  *
1468  * @param string $mode     are we creating a new user or are we just
1469  *                         changing  one? (allowed values: 'new', 'change')
1470  * @param string $username User name
1471  * @param string $hostname Host name
1472  *
1473  * @global  array      $cfg     the phpMyAdmin configuration
1474  * @global  resource   $user_link the database connection
1475  *
1476  * @return string $html_output  a HTML snippet
1477  */
1478 function PMA_getHtmlForLoginInformationFields(
1479     $mode = 'new',
1480     $username = null,
1481     $hostname = null
1482 ) {
1483     list($username_length, $hostname_length) = PMA_getUsernameAndHostnameLength();
1484
1485     if (isset($GLOBALS['username']) && strlen($GLOBALS['username']) === 0) {
1486         $GLOBALS['pred_username'] = 'any';
1487     }
1488     $html_output = '<fieldset id="fieldset_add_user_login">' . "\n"
1489         . '<legend>' . __('Login Information') . '</legend>' . "\n"
1490         . '<div class="item">' . "\n"
1491         . '<label for="select_pred_username">' . "\n"
1492         . '    ' . __('User name:') . "\n"
1493         . '</label>' . "\n"
1494         . '<span class="options">' . "\n";
1495
1496     $html_output .= '<select name="pred_username" id="select_pred_username" '
1497         . 'title="' . __('User name') . '">' . "\n";
1498
1499     $html_output .= '<option value="any"'
1500         . ((isset($GLOBALS['pred_username']) && $GLOBALS['pred_username'] == 'any')
1501             ? ' selected="selected"'
1502             : '') . '>'
1503         . __('Any user')
1504         . '</option>' . "\n";
1505
1506     $html_output .= '<option value="userdefined"'
1507         . ((! isset($GLOBALS['pred_username'])
1508                 || $GLOBALS['pred_username'] == 'userdefined'
1509             )
1510             ? ' selected="selected"'
1511             : '') . '>'
1512         . __('Use text field')
1513         . ':</option>' . "\n";
1514
1515     $html_output .= '</select>' . "\n"
1516         . '</span>' . "\n";
1517
1518     $html_output .= '<input type="text" name="username" id="pma_username" class="autofocus"'
1519         . ' maxlength="' . $username_length . '" title="' . __('User name') . '"'
1520         . (empty($GLOBALS['username'])
1521            ? ''
1522            : ' value="' . htmlspecialchars(
1523                isset($GLOBALS['new_username'])
1524                ? $GLOBALS['new_username']
1525                : $GLOBALS['username']
1526            ) . '"'
1527         )
1528         . ((! isset($GLOBALS['pred_username'])
1529                 || $GLOBALS['pred_username'] == 'userdefined'
1530             )
1531             ? 'required="required"'
1532             : '') . ' />' . "\n";
1533
1534     $html_output .= '<div id="user_exists_warning"'
1535         . ' name="user_exists_warning" style="display:none;">'
1536         . Message::notice(
1537             __(
1538                 'An account already exists with the same username '
1539                 . 'but possibly a different hostname.'
1540             )
1541         )->getDisplay()
1542         . '</div>';
1543     $html_output .= '</div>';
1544
1545     $html_output .= '<div class="item">' . "\n"
1546         . '<label for="select_pred_hostname">' . "\n"
1547         . '    ' . __('Host name:') . "\n"
1548         . '</label>' . "\n";
1549
1550     $html_output .= '<span class="options">' . "\n"
1551         . '    <select name="pred_hostname" id="select_pred_hostname" '
1552         . 'title="' . __('Host name') . '"' . "\n";
1553     $_current_user = $GLOBALS['dbi']->fetchValue('SELECT USER();');
1554     if (! empty($_current_user)) {
1555         $thishost = str_replace(
1556             "'",
1557             '',
1558             mb_substr(
1559                 $_current_user,
1560                 (mb_strrpos($_current_user, '@') + 1)
1561             )
1562         );
1563         if ($thishost != 'localhost' && $thishost != '127.0.0.1') {
1564             $html_output .= ' data-thishost="' . htmlspecialchars($thishost) . '" ';
1565         } else {
1566             unset($thishost);
1567         }
1568     }
1569     $html_output .= '>' . "\n";
1570     unset($_current_user);
1571
1572     // when we start editing a user, $GLOBALS['pred_hostname'] is not defined
1573     if (! isset($GLOBALS['pred_hostname']) && isset($GLOBALS['hostname'])) {
1574         switch (mb_strtolower($GLOBALS['hostname'])) {
1575         case 'localhost':
1576         case '127.0.0.1':
1577             $GLOBALS['pred_hostname'] = 'localhost';
1578             break;
1579         case '%':
1580             $GLOBALS['pred_hostname'] = 'any';
1581             break;
1582         default:
1583             $GLOBALS['pred_hostname'] = 'userdefined';
1584             break;
1585         }
1586     }
1587     $html_output .=  '<option value="any"'
1588         . ((isset($GLOBALS['pred_hostname'])
1589                 && $GLOBALS['pred_hostname'] == 'any'
1590             )
1591             ? ' selected="selected"'
1592             : '') . '>'
1593         . __('Any host')
1594         . '</option>' . "\n"
1595         . '<option value="localhost"'
1596         . ((isset($GLOBALS['pred_hostname'])
1597                 && $GLOBALS['pred_hostname'] == 'localhost'
1598             )
1599             ? ' selected="selected"'
1600             : '') . '>'
1601         . __('Local')
1602         . '</option>' . "\n";
1603     if (! empty($thishost)) {
1604         $html_output .= '<option value="thishost"'
1605             . ((isset($GLOBALS['pred_hostname'])
1606                     && $GLOBALS['pred_hostname'] == 'thishost'
1607                 )
1608                 ? ' selected="selected"'
1609                 : '') . '>'
1610             . __('This Host')
1611             . '</option>' . "\n";
1612     }
1613     unset($thishost);
1614     $html_output .= '<option value="hosttable"'
1615         . ((isset($GLOBALS['pred_hostname'])
1616                 && $GLOBALS['pred_hostname'] == 'hosttable'
1617             )
1618             ? ' selected="selected"'
1619             : '') . '>'
1620         . __('Use Host Table')
1621         . '</option>' . "\n";
1622
1623     $html_output .= '<option value="userdefined"'
1624         . ((isset($GLOBALS['pred_hostname'])
1625                 && $GLOBALS['pred_hostname'] == 'userdefined'
1626             )
1627             ? ' selected="selected"'
1628             : '') . '>'
1629         . __('Use text field:') . '</option>' . "\n"
1630         . '</select>' . "\n"
1631         . '</span>' . "\n";
1632
1633     $html_output .= '<input type="text" name="hostname" id="pma_hostname" maxlength="'
1634         . $hostname_length . '" value="'
1635         // use default value of '%' to match with the default 'Any host'
1636         . htmlspecialchars(isset($GLOBALS['hostname']) ? $GLOBALS['hostname'] : '%')
1637         . '" title="' . __('Host name')
1638         . ((isset($GLOBALS['pred_hostname'])
1639                 && $GLOBALS['pred_hostname'] == 'userdefined'
1640             )
1641             ? 'required="required"'
1642             : '')
1643         . ' />' . "\n"
1644         . Util::showHint(
1645             __(
1646                 'When Host table is used, this field is ignored '
1647                 . 'and values stored in Host table are used instead.'
1648             )
1649         )
1650         . '</div>' . "\n";
1651
1652     $html_output .= '<div class="item">' . "\n"
1653         . '<label for="select_pred_password">' . "\n"
1654         . '    ' . __('Password:') . "\n"
1655         . '</label>' . "\n"
1656         . '<span class="options">' . "\n"
1657         . '<select name="pred_password" id="select_pred_password" title="'
1658         . __('Password') . '">' . "\n"
1659         . ($mode == 'change' ? '<option value="keep" selected="selected">'
1660             . __('Do not change the password')
1661             . '</option>' . "\n" : '')
1662         . '<option value="none"';
1663
1664     if (isset($GLOBALS['username']) && $mode != 'change') {
1665         $html_output .= '  selected="selected"';
1666     }
1667     $html_output .= '>' . __('No Password') . '</option>' . "\n"
1668         . '<option value="userdefined"'
1669         . (isset($GLOBALS['username']) ? '' : ' selected="selected"') . '>'
1670         . __('Use text field')
1671         . ':</option>' . "\n"
1672         . '</select>' . "\n"
1673         . '</span>' . "\n"
1674         . '<input type="password" id="text_pma_pw" name="pma_pw" '
1675         . 'title="' . __('Password') . '" '
1676         . (isset($GLOBALS['username']) ? '' : 'required="required"')
1677         . '/>' . "\n"
1678         . '<span>Strength:</span> '
1679         . '<meter max="4" id="password_strength_meter" name="pw_meter"></meter> '
1680         . '<span id="password_strength" name="pw_strength"></span>' . "\n"
1681         . '</div>' . "\n";
1682
1683     $html_output .= '<div class="item" '
1684         . 'id="div_element_before_generate_password">' . "\n"
1685         . '<label for="text_pma_pw2">' . "\n"
1686         . '    ' . __('Re-type:') . "\n"
1687         . '</label>' . "\n"
1688         . '<span class="options">&nbsp;</span>' . "\n"
1689         . '<input type="password" name="pma_pw2" id="text_pma_pw2" '
1690         . 'title="' . __('Re-type') . '" '
1691         . (isset($GLOBALS['username']) ? '' : 'required="required"')
1692         . '/>' . "\n"
1693         . '</div>' . "\n"
1694         . '<div class="item" id="authentication_plugin_div">'
1695         . '<label for="select_authentication_plugin" >';
1696
1697     $serverType = Util::getServerType();
1698     $orig_auth_plugin = PMA_getCurrentAuthenticationPlugin(
1699         $mode,
1700         $username,
1701         $hostname
1702     );
1703
1704     if (($serverType == 'MySQL'
1705         && PMA_MYSQL_INT_VERSION >= 50507)
1706         || ($serverType == 'MariaDB'
1707         && PMA_MYSQL_INT_VERSION >= 50200)
1708     ) {
1709         $html_output .= __('Authentication Plugin')
1710         . '</label><span class="options">&nbsp;</span>' . "\n";
1711
1712         $auth_plugin_dropdown = PMA_getHtmlForAuthPluginsDropdown(
1713             $orig_auth_plugin, $mode, 'new'
1714         );
1715     } else {
1716         $html_output .= __('Password Hashing Method')
1717             . '</label><span class="options">&nbsp;</span>' . "\n";
1718         $auth_plugin_dropdown = PMA_getHtmlForAuthPluginsDropdown(
1719             $orig_auth_plugin, $mode, 'old'
1720         );
1721     }
1722     $html_output .= $auth_plugin_dropdown;
1723
1724     $html_output .= '<div '
1725         . ($orig_auth_plugin != 'sha256_password' ? 'style="display:none"' : '')
1726         . ' id="ssl_reqd_warning">'
1727         . Message::notice(
1728             __(
1729                 'This method requires using an \'<i>SSL connection</i>\' '
1730                 . 'or an \'<i>unencrypted connection that encrypts the password '
1731                 . 'using RSA</i>\'; while connecting to the server.'
1732             )
1733             . Util::showMySQLDocu('sha256-authentication-plugin')
1734         )
1735             ->getDisplay()
1736         . '</div>';
1737
1738     $html_output .= '</div>' . "\n"
1739         // Generate password added here via jQuery
1740        . '</fieldset>' . "\n";
1741
1742     return $html_output;
1743 } // end of the 'PMA_getHtmlForLoginInformationFields()' function
1744
1745 /**
1746  * Get username and hostname length
1747  *
1748  * @return array username length and hostname length
1749  */
1750 function PMA_getUsernameAndHostnameLength()
1751 {
1752     /* Fallback values */
1753     $username_length = 16;
1754     $hostname_length = 41;
1755
1756     /* Try to get real lengths from the database */
1757     $fields_info = $GLOBALS['dbi']->fetchResult(
1758         'SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH '
1759         . 'FROM information_schema.columns '
1760         . "WHERE table_schema = 'mysql' AND table_name = 'user' "
1761         . "AND COLUMN_NAME IN ('User', 'Host')"
1762     );
1763     foreach ($fields_info as $val) {
1764         if ($val['COLUMN_NAME'] == 'User') {
1765             $username_length = $val['CHARACTER_MAXIMUM_LENGTH'];
1766         } elseif ($val['COLUMN_NAME'] == 'Host') {
1767             $hostname_length = $val['CHARACTER_MAXIMUM_LENGTH'];
1768         }
1769     }
1770     return array($username_length, $hostname_length);
1771 }
1772
1773 /**
1774  * Get current authentication plugin in use - for a user or globally
1775  *
1776  * @param string $mode     are we creating a new user or are we just
1777  *                         changing  one? (allowed values: 'new', 'change')
1778  * @param string $username User name
1779  * @param string $hostname Host name
1780  *
1781  * @return string authentication plugin in use
1782  */
1783 function PMA_getCurrentAuthenticationPlugin(
1784     $mode = 'new',
1785     $username = null,
1786     $hostname = null
1787 ) {
1788     /* Fallback (standard) value */
1789     $authentication_plugin = 'mysql_native_password';
1790
1791     if (isset($username) && isset($hostname)
1792         && $mode == 'change'
1793     ) {
1794         $row = $GLOBALS['dbi']->fetchSingleRow(
1795             'SELECT `plugin` FROM `mysql`.`user` WHERE '
1796             . '`User` = "' . $username . '" AND `Host` = "' . $hostname . '" LIMIT 1'
1797         );
1798         // Table 'mysql'.'user' may not exist for some previous
1799         // versions of MySQL - in that case consider fallback value
1800         if (isset($row) && $row) {
1801             $authentication_plugin = $row['plugin'];
1802         }
1803     } elseif ($mode == 'change') {
1804         list($username, $hostname) = $GLOBALS['dbi']->getCurrentUserAndHost();
1805
1806         $row = $GLOBALS['dbi']->fetchSingleRow(
1807             'SELECT `plugin` FROM `mysql`.`user` WHERE '
1808             . '`User` = "' . $username . '" AND `Host` = "' . $hostname . '"'
1809         );
1810         if (isset($row) && $row && ! empty($row['plugin'])) {
1811             $authentication_plugin = $row['plugin'];
1812         }
1813     } elseif (PMA_MYSQL_INT_VERSION >= 50702) {
1814         $row = $GLOBALS['dbi']->fetchSingleRow(
1815             'SELECT @@default_authentication_plugin'
1816         );
1817         $authentication_plugin = $row['@@default_authentication_plugin'];
1818     }
1819
1820     return $authentication_plugin;
1821 }
1822
1823 /**
1824  * Returns all the grants for a certain user on a certain host
1825  * Used in the export privileges for all users section
1826  *
1827  * @param string $user User name
1828  * @param string $host Host name
1829  *
1830  * @return string containing all the grants text
1831  */
1832 function PMA_getGrants($user, $host)
1833 {
1834     $grants = $GLOBALS['dbi']->fetchResult(
1835         "SHOW GRANTS FOR '"
1836         . $GLOBALS['dbi']->escapeString($user) . "'@'"
1837         . $GLOBALS['dbi']->escapeString($host) . "'"
1838     );
1839     $response = '';
1840     foreach ($grants as $one_grant) {
1841         $response .= $one_grant . ";\n\n";
1842     }
1843     return $response;
1844 } // end of the 'PMA_getGrants()' function
1845
1846 /**
1847  * Update password and get message for password updating
1848  *
1849  * @param string $err_url  error url
1850  * @param string $username username
1851  * @param string $hostname hostname
1852  *
1853  * @return string $message  success or error message after updating password
1854  */
1855 function PMA_updatePassword($err_url, $username, $hostname)
1856 {
1857     // similar logic in user_password.php
1858     $message = '';
1859     $is_superuser = $GLOBALS['dbi']->isSuperuser();
1860
1861     if (empty($_REQUEST['nopass'])
1862         && isset($_POST['pma_pw'])
1863         && isset($_POST['pma_pw2'])
1864     ) {
1865         if ($_POST['pma_pw'] != $_POST['pma_pw2']) {
1866             $message = Message::error(__('The passwords aren\'t the same!'));
1867         } elseif (empty($_POST['pma_pw']) || empty($_POST['pma_pw2'])) {
1868             $message = Message::error(__('The password is empty!'));
1869         }
1870     }
1871
1872     // here $nopass could be == 1
1873     if (empty($message)) {
1874         $hashing_function = 'PASSWORD';
1875         $serverType = Util::getServerType();
1876         $authentication_plugin
1877             = (isset($_REQUEST['authentication_plugin'])
1878             ? $_REQUEST['authentication_plugin']
1879             : PMA_getCurrentAuthenticationPlugin(
1880                 'change',
1881                 $username,
1882                 $hostname
1883             ));
1884
1885         // Use 'ALTER USER ...' syntax for MySQL 5.7.6+
1886         if ($serverType == 'MySQL'
1887             && PMA_MYSQL_INT_VERSION >= 50706
1888         ) {
1889             if ($authentication_plugin != 'mysql_old_password') {
1890                 $query_prefix = "ALTER USER '"
1891                     . $GLOBALS['dbi']->escapeString($username)
1892                     . "'@'" . $GLOBALS['dbi']->escapeString($hostname) . "'"
1893                     . " IDENTIFIED WITH "
1894                     . $authentication_plugin
1895                     . " BY '";
1896             } else {
1897                 $query_prefix = "ALTER USER '"
1898                     . $GLOBALS['dbi']->escapeString($username)
1899                     . "'@'" . $GLOBALS['dbi']->escapeString($hostname) . "'"
1900                     . " IDENTIFIED BY '";
1901             }
1902
1903             // in $sql_query which will be displayed, hide the password
1904             $sql_query = $query_prefix . "*'";
1905
1906             $local_query = $query_prefix
1907                 . $GLOBALS['dbi']->escapeString($_POST['pma_pw']) . "'";
1908         } else if ($serverType == 'MariaDB'
1909             && PMA_MYSQL_INT_VERSION >= 50200
1910             && $is_superuser
1911         ) {
1912             // Use 'UPDATE `mysql`.`user` ...' Syntax for MariaDB 5.2+
1913             if ($authentication_plugin == 'mysql_native_password') {
1914                 // Set the hashing method used by PASSWORD()
1915                 // to be 'mysql_native_password' type
1916                 $GLOBALS['dbi']->tryQuery('SET old_passwords = 0;');
1917
1918             } else if ($authentication_plugin == 'sha256_password') {
1919                 // Set the hashing method used by PASSWORD()
1920                 // to be 'sha256_password' type
1921                 $GLOBALS['dbi']->tryQuery('SET `old_passwords` = 2;');
1922             }
1923
1924             $hashedPassword = PMA_getHashedPassword($_POST['pma_pw']);
1925
1926             $sql_query        = 'SET PASSWORD FOR \''
1927                 . $GLOBALS['dbi']->escapeString($username)
1928                 . '\'@\'' . $GLOBALS['dbi']->escapeString($hostname) . '\' = '
1929                 . (($_POST['pma_pw'] == '')
1930                     ? '\'\''
1931                     : $hashing_function . '(\''
1932                     . preg_replace('@.@s', '*', $_POST['pma_pw']) . '\')');
1933
1934             $local_query = "UPDATE `mysql`.`user` SET "
1935                 . " `authentication_string` = '" . $hashedPassword
1936                 . "', `Password` = '', "
1937                 . " `plugin` = '" . $authentication_plugin . "'"
1938                 . " WHERE `User` = '" . $username . "' AND Host = '"
1939                 . $hostname . "';";
1940         } else {
1941             // USE 'SET PASSWORD ...' syntax for rest of the versions
1942             // Backup the old value, to be reset later
1943             $row = $GLOBALS['dbi']->fetchSingleRow(
1944                 'SELECT @@old_passwords;'
1945             );
1946             $orig_value = $row['@@old_passwords'];
1947             $update_plugin_query = "UPDATE `mysql`.`user` SET"
1948                 . " `plugin` = '" . $authentication_plugin . "'"
1949                 . " WHERE `User` = '" . $username . "' AND Host = '"
1950                 . $hostname . "';";
1951
1952             // Update the plugin for the user
1953             if (!($GLOBALS['dbi']->tryQuery($update_plugin_query))) {
1954                 Util::mysqlDie(
1955                     $GLOBALS['dbi']->getError(),
1956                     $update_plugin_query,
1957                     false, $err_url
1958                 );
1959             }
1960             $GLOBALS['dbi']->tryQuery("FLUSH PRIVILEGES;");
1961
1962             if ($authentication_plugin == 'mysql_native_password') {
1963                 // Set the hashing method used by PASSWORD()
1964                 // to be 'mysql_native_password' type
1965                 $GLOBALS['dbi']->tryQuery('SET old_passwords = 0;');
1966             } else if ($authentication_plugin == 'sha256_password') {
1967                 // Set the hashing method used by PASSWORD()
1968                 // to be 'sha256_password' type
1969                 $GLOBALS['dbi']->tryQuery('SET `old_passwords` = 2;');
1970             }
1971             $sql_query        = 'SET PASSWORD FOR \''
1972                 . $GLOBALS['dbi']->escapeString($username)
1973                 . '\'@\'' . $GLOBALS['dbi']->escapeString($hostname) . '\' = '
1974                 . (($_POST['pma_pw'] == '')
1975                     ? '\'\''
1976                     : $hashing_function . '(\''
1977                     . preg_replace('@.@s', '*', $_POST['pma_pw']) . '\')');
1978
1979             $local_query      = 'SET PASSWORD FOR \''
1980                 . $GLOBALS['dbi']->escapeString($username)
1981                 . '\'@\'' . $GLOBALS['dbi']->escapeString($hostname) . '\' = '
1982                 . (($_POST['pma_pw'] == '') ? '\'\'' : $hashing_function
1983                 . '(\'' . $GLOBALS['dbi']->escapeString($_POST['pma_pw']) . '\')');
1984         }
1985
1986         if (!($GLOBALS['dbi']->tryQuery($local_query))) {
1987             Util::mysqlDie(
1988                 $GLOBALS['dbi']->getError(), $sql_query, false, $err_url
1989             );
1990         }
1991         // Flush privileges after successful password change
1992         $GLOBALS['dbi']->tryQuery("FLUSH PRIVILEGES;");
1993
1994         $message = Message::success(
1995             __('The password for %s was changed successfully.')
1996         );
1997         $message->addParam('\'' . $username . '\'@\'' . $hostname . '\'');
1998         if (isset($orig_value)) {
1999             $GLOBALS['dbi']->tryQuery(
2000                 'SET `old_passwords` = ' . $orig_value . ';'
2001             );
2002         }
2003     }
2004     return $message;
2005 }
2006
2007 /**
2008  * Revokes privileges and get message and SQL query for privileges revokes
2009  *
2010  * @param string $dbname    database name
2011  * @param string $tablename table name
2012  * @param string $username  username
2013  * @param string $hostname  host name
2014  * @param string $itemType  item type
2015  *
2016  * @return array ($message, $sql_query)
2017  */
2018 function PMA_getMessageAndSqlQueryForPrivilegesRevoke($dbname,
2019     $tablename, $username, $hostname, $itemType
2020 ) {
2021     $db_and_table = PMA_wildcardEscapeForGrant($dbname, $tablename);
2022
2023     $sql_query0 = 'REVOKE ALL PRIVILEGES ON ' . $itemType . ' ' . $db_and_table
2024         . ' FROM \''
2025         . $GLOBALS['dbi']->escapeString($username) . '\'@\''
2026         . $GLOBALS['dbi']->escapeString($hostname) . '\';';
2027
2028     $sql_query1 = 'REVOKE GRANT OPTION ON ' . $itemType . ' ' . $db_and_table
2029         . ' FROM \'' . $GLOBALS['dbi']->escapeString($username) . '\'@\''
2030         . $GLOBALS['dbi']->escapeString($hostname) . '\';';
2031
2032     $GLOBALS['dbi']->query($sql_query0);
2033     if (! $GLOBALS['dbi']->tryQuery($sql_query1)) {
2034         // this one may fail, too...
2035         $sql_query1 = '';
2036     }
2037     $sql_query = $sql_query0 . ' ' . $sql_query1;
2038     $message = Message::success(
2039         __('You have revoked the privileges for %s.')
2040     );
2041     $message->addParam('\'' . $username . '\'@\'' . $hostname . '\'');
2042
2043     return array($message, $sql_query);
2044 }
2045
2046 /**
2047  * Get REQUIRE cluase
2048  *
2049  * @return string REQUIRE clause
2050  */
2051 function PMA_getRequireClause()
2052 {
2053     $arr = isset($_POST['ssl_type']) ? $_POST : $GLOBALS;
2054     if (isset($arr['ssl_type']) && $arr['ssl_type'] == 'SPECIFIED') {
2055         $require = array();
2056         if (! empty($arr['ssl_cipher'])) {
2057             $require[] = "CIPHER '"
2058                     . $GLOBALS['dbi']->escapeString($arr['ssl_cipher']) . "'";
2059         }
2060         if (! empty($arr['x509_issuer'])) {
2061             $require[] = "ISSUER '"
2062                     . $GLOBALS['dbi']->escapeString($arr['x509_issuer']) . "'";
2063         }
2064         if (! empty($arr['x509_subject'])) {
2065             $require[] = "SUBJECT '"
2066                     . $GLOBALS['dbi']->escapeString($arr['x509_subject']) . "'";
2067         }
2068         if (count($require)) {
2069             $require_clause = " REQUIRE " . implode(" AND ", $require);
2070         } else {
2071             $require_clause = " REQUIRE NONE";
2072         }
2073     } elseif (isset($arr['ssl_type']) && $arr['ssl_type'] == 'X509') {
2074         $require_clause = " REQUIRE X509";
2075     } elseif (isset($arr['ssl_type']) && $arr['ssl_type'] == 'ANY') {
2076         $require_clause = " REQUIRE SSL";
2077     } else {
2078         $require_clause = " REQUIRE NONE";
2079     }
2080
2081     return $require_clause;
2082 }
2083
2084 /**
2085  * Get a WITH clause for 'update privileges' and 'add user'
2086  *
2087  * @return string $sql_query
2088  */
2089 function PMA_getWithClauseForAddUserAndUpdatePrivs()
2090 {
2091     $sql_query = '';
2092     if ((isset($_POST['Grant_priv']) && $_POST['Grant_priv'] == 'Y')
2093         || (isset($GLOBALS['Grant_priv']) && $GLOBALS['Grant_priv'] == 'Y')
2094     ) {
2095         $sql_query .= ' GRANT OPTION';
2096     }
2097     if (isset($_POST['max_questions']) || isset($GLOBALS['max_questions'])) {
2098         $max_questions = isset($_POST['max_questions'])
2099             ? (int)$_POST['max_questions'] : (int)$GLOBALS['max_questions'];
2100         $max_questions = max(0, $max_questions);
2101         $sql_query .= ' MAX_QUERIES_PER_HOUR ' . $max_questions;
2102     }
2103     if (isset($_POST['max_connections']) || isset($GLOBALS['max_connections'])) {
2104         $max_connections = isset($_POST['max_connections'])
2105             ? (int)$_POST['max_connections'] : (int)$GLOBALS['max_connections'];
2106         $max_connections = max(0, $max_connections);
2107         $sql_query .= ' MAX_CONNECTIONS_PER_HOUR ' . $max_connections;
2108     }
2109     if (isset($_POST['max_updates']) || isset($GLOBALS['max_updates'])) {
2110         $max_updates = isset($_POST['max_updates'])
2111             ? (int)$_POST['max_updates'] : (int)$GLOBALS['max_updates'];
2112         $max_updates = max(0, $max_updates);
2113         $sql_query .= ' MAX_UPDATES_PER_HOUR ' . $max_updates;
2114     }
2115     if (isset($_POST['max_user_connections'])
2116         || isset($GLOBALS['max_user_connections'])
2117     ) {
2118         $max_user_connections = isset($_POST['max_user_connections'])
2119             ? (int)$_POST['max_user_connections']
2120             : (int)$GLOBALS['max_user_connections'];
2121         $max_user_connections = max(0, $max_user_connections);
2122         $sql_query .= ' MAX_USER_CONNECTIONS ' . $max_user_connections;
2123     }
2124     return ((!empty($sql_query)) ? ' WITH' . $sql_query : '');
2125 }
2126
2127 /**
2128  * Get HTML for addUsersForm, This function call if isset($_REQUEST['adduser'])
2129  *
2130  * @param string $dbname database name
2131  *
2132  * @return string HTML for addUserForm
2133  */
2134 function PMA_getHtmlForAddUser($dbname)
2135 {
2136     $html_output = '<h2>' . "\n"
2137        . Util::getIcon('b_usradd.png') . __('Add user account') . "\n"
2138        . '</h2>' . "\n"
2139        . '<form name="usersForm" id="addUsersForm"'
2140        . ' onsubmit="return checkAddUser(this);"'
2141        . ' action="server_privileges.php" method="post" autocomplete="off" >' . "\n"
2142        . URL::getHiddenInputs('', '')
2143        . PMA_getHtmlForLoginInformationFields('new');
2144
2145     $html_output .= '<fieldset id="fieldset_add_user_database">' . "\n"
2146         . '<legend>' . __('Database for user account') . '</legend>' . "\n";
2147
2148     $html_output .= Template::get('checkbox')
2149         ->render(
2150             array(
2151                 'html_field_name'   => 'createdb-1',
2152                 'label'             => __('Create database with same name and grant all privileges.'),
2153                 'checked'           => false,
2154                 'onclick'           => false,
2155                 'html_field_id'     => 'createdb-1',
2156             )
2157         );
2158     $html_output .= '<br />' . "\n";
2159     $html_output .= Template::get('checkbox')
2160         ->render(
2161             array(
2162                 'html_field_name'   => 'createdb-2',
2163                 'label'             => __('Grant all privileges on wildcard name (username\\_%).'),
2164                 'checked'           => false,
2165                 'onclick'           => false,
2166                 'html_field_id'     => 'createdb-2',
2167             )
2168         );
2169     $html_output .= '<br />' . "\n";
2170
2171     if (! empty($dbname) ) {
2172         $html_output .= Template::get('checkbox')
2173             ->render(
2174                 array(
2175                     'html_field_name'   => 'createdb-3',
2176                     'label'             => sprintf(__('Grant all privileges on database %s.'), htmlspecialchars($dbname)),
2177                     'checked'           => true,
2178                     'onclick'           => false,
2179                     'html_field_id'     => 'createdb-3',
2180                 )
2181             );
2182         $html_output .= '<input type="hidden" name="dbname" value="'
2183             . htmlspecialchars($dbname) . '" />' . "\n";
2184         $html_output .= '<br />' . "\n";
2185     }
2186
2187     $html_output .= '</fieldset>' . "\n";
2188     if ($GLOBALS['is_grantuser']) {
2189         $html_output .= PMA_getHtmlToDisplayPrivilegesTable('*', '*', false);
2190     }
2191     $html_output .= '<fieldset id="fieldset_add_user_footer" class="tblFooters">'
2192         . "\n"
2193         . '<input type="hidden" name="adduser_submit" value="1" />' . "\n"
2194         . '<input type="submit" id="adduser_submit" value="' . __('Go') . '" />'
2195         . "\n"
2196         . '</fieldset>' . "\n"
2197         . '</form>' . "\n";
2198
2199     return $html_output;
2200 }
2201
2202 /**
2203  * Get the list of privileges and list of compared privileges as strings
2204  * and return a array that contains both strings
2205  *
2206  * @return array $list_of_privileges, $list_of_compared_privileges
2207  */
2208 function PMA_getListOfPrivilegesAndComparedPrivileges()
2209 {
2210     $list_of_privileges
2211         = '`User`, '
2212         . '`Host`, '
2213         . '`Select_priv`, '
2214         . '`Insert_priv`, '
2215         . '`Update_priv`, '
2216         . '`Delete_priv`, '
2217         . '`Create_priv`, '
2218         . '`Drop_priv`, '
2219         . '`Grant_priv`, '
2220         . '`Index_priv`, '
2221         . '`Alter_priv`, '
2222         . '`References_priv`, '
2223         . '`Create_tmp_table_priv`, '
2224         . '`Lock_tables_priv`, '
2225         . '`Create_view_priv`, '
2226         . '`Show_view_priv`, '
2227         . '`Create_routine_priv`, '
2228         . '`Alter_routine_priv`, '
2229         . '`Execute_priv`';
2230
2231     $listOfComparedPrivs
2232         = '`Select_priv` = \'N\''
2233         . ' AND `Insert_priv` = \'N\''
2234         . ' AND `Update_priv` = \'N\''
2235         . ' AND `Delete_priv` = \'N\''
2236         . ' AND `Create_priv` = \'N\''
2237         . ' AND `Drop_priv` = \'N\''
2238         . ' AND `Grant_priv` = \'N\''
2239         . ' AND `References_priv` = \'N\''
2240         . ' AND `Create_tmp_table_priv` = \'N\''
2241         . ' AND `Lock_tables_priv` = \'N\''
2242         . ' AND `Create_view_priv` = \'N\''
2243         . ' AND `Show_view_priv` = \'N\''
2244         . ' AND `Create_routine_priv` = \'N\''
2245         . ' AND `Alter_routine_priv` = \'N\''
2246         . ' AND `Execute_priv` = \'N\'';
2247
2248     $list_of_privileges .=
2249         ', `Event_priv`, '
2250         . '`Trigger_priv`';
2251     $listOfComparedPrivs .=
2252         ' AND `Event_priv` = \'N\''
2253         . ' AND `Trigger_priv` = \'N\'';
2254     return array($list_of_privileges, $listOfComparedPrivs);
2255 }
2256
2257 /**
2258  * Get the HTML for routine based privileges
2259  *
2260  * @param string $db             database name
2261  * @param string $index_checkbox starting index for rows to be added
2262  *
2263  * @return string $html_output
2264  */
2265 function PMA_getHtmlTableBodyForSpecificDbRoutinePrivs($db, $index_checkbox)
2266 {
2267     $sql_query = 'SELECT * FROM `mysql`.`procs_priv` WHERE Db = \'' . $GLOBALS['dbi']->escapeString($db) . '\';';
2268     $res = $GLOBALS['dbi']->query($sql_query);
2269     $html_output = '';
2270     while ($row = $GLOBALS['dbi']->fetchAssoc($res)) {
2271
2272         $html_output .= '<tr>';
2273
2274         $html_output .= '<td';
2275         $value = htmlspecialchars($row['User'] . '&amp;#27;' . $row['Host']);
2276         $html_output .= '>';
2277         $html_output .= '<input type="checkbox" class="checkall" '
2278             . 'name="selected_usr[]" '
2279             . 'id="checkbox_sel_users_' . ($index_checkbox++) . '" '
2280             . 'value="' . $value . '" /></td>';
2281
2282         $html_output .= '<td>' . htmlspecialchars($row['User'])
2283             . '</td>'
2284             . '<td>' . htmlspecialchars($row['Host'])
2285             . '</td>'
2286             . '<td>' . 'routine'
2287             . '</td>'
2288             . '<td>' . '<code>' . htmlspecialchars($row['Routine_name']) . '</code>'
2289             . '</td>'
2290             . '<td>' . 'Yes'
2291             . '</td>';
2292         $current_user = $row['User'];
2293         $current_host = $row['Host'];
2294         $routine = $row['Routine_name'];
2295         $html_output .= '<td>';
2296         if ($GLOBALS['is_grantuser']) {
2297             $specific_db = (isset($row['Db']) && $row['Db'] != '*')
2298                 ? $row['Db'] : '';
2299             $specific_table = (isset($row['Table_name'])
2300                 && $row['Table_name'] != '*')
2301                 ? $row['Table_name'] : '';
2302             $html_output .= PMA_getUserLink(
2303                 'edit',
2304                 $current_user,
2305                 $current_host,
2306                 $specific_db,
2307                 $specific_table,
2308                 $routine
2309             );
2310         }
2311         $html_output .= '</td>';
2312
2313         $html_output .= '</tr>';
2314
2315     }
2316     return $html_output;
2317 }
2318
2319 /**
2320  * Get the HTML for user form and check the privileges for a particular database.
2321  *
2322  * @param string $db database name
2323  *
2324  * @return string $html_output
2325  */
2326 function PMA_getHtmlForSpecificDbPrivileges($db)
2327 {
2328     $html_output = '';
2329     if ($GLOBALS['is_superuser']) {
2330         // check the privileges for a particular database.
2331         $html_output  = '<form id="usersForm" action="server_privileges.php">';
2332         $html_output .= URL::getHiddenInputs($db);
2333         $html_output .= '<fieldset>';
2334         $html_output .= '<legend>' . "\n"
2335             . Util::getIcon('b_usrcheck.png')
2336             . '    '
2337             . sprintf(
2338                 __('Users having access to "%s"'),
2339                 '<a href="' . Util::getScriptNameForOption(
2340                     $GLOBALS['cfg']['DefaultTabDatabase'], 'database'
2341                 )
2342                 . URL::getCommon(array('db' => $db)) . '">'
2343                 .  htmlspecialchars($db)
2344                 . '</a>'
2345             )
2346             . "\n"
2347             . '</legend>' . "\n";
2348
2349         $html_output .= '<table id="dbspecificuserrights" class="data">';
2350         $html_output .= PMA_getHtmlForPrivsTableHead();
2351         $privMap = PMA_getPrivMap($db);
2352         $html_output .= PMA_getHtmlTableBodyForSpecificDbOrTablePrivs($privMap, $db);
2353         $html_output .= '</table>';
2354
2355         $html_output .= '<div class="floatleft">';
2356         $html_output .= Template::get('select_all')
2357             ->render(
2358                 array(
2359                     'pmaThemeImage' => $GLOBALS['pmaThemeImage'],
2360                     'text_dir'      => $GLOBALS['text_dir'],
2361                     'formName'      => "usersForm",
2362                 )
2363             );
2364         $html_output .= Util::getButtonOrImage(
2365             'submit_mult', 'mult_submit',
2366             __('Export'), 'b_tblexport.png', 'export'
2367         );
2368
2369         $html_output .= '</fieldset>';
2370         $html_output .= '</form>';
2371     } else {
2372         $html_output .= PMA_getHtmlForViewUsersError();
2373     }
2374
2375     $response = Response::getInstance();
2376     if ($response->isAjax() == true
2377         && empty($_REQUEST['ajax_page_request'])
2378     ) {
2379         $message = Message::success(__('User has been added.'));
2380         $response->addJSON('message', $message);
2381         $response->addJSON('user_form', $html_output);
2382         exit;
2383     } else {
2384         // Offer to create a new user for the current database
2385         $html_output .= PMA_getAddUserHtmlFieldset($db);
2386     }
2387     return $html_output;
2388 }
2389
2390 /**
2391  * Get the HTML for user form and check the privileges for a particular table.
2392  *
2393  * @param string $db    database name
2394  * @param string $table table name
2395  *
2396  * @return string $html_output
2397  */
2398 function PMA_getHtmlForSpecificTablePrivileges($db, $table)
2399 {
2400     $html_output = '';
2401     if ($GLOBALS['is_superuser']) {
2402         // check the privileges for a particular table.
2403         $html_output  = '<form id="usersForm" action="server_privileges.php">';
2404         $html_output .= URL::getHiddenInputs($db, $table);
2405         $html_output .= '<fieldset>';
2406         $html_output .= '<legend>'
2407             . Util::getIcon('b_usrcheck.png')
2408             . sprintf(
2409                 __('Users having access to "%s"'),
2410                 '<a href="' . Util::getScriptNameForOption(
2411                     $GLOBALS['cfg']['DefaultTabTable'], 'table'
2412                 )
2413                 . URL::getCommon(
2414                     array(
2415                         'db' => $db,
2416                         'table' => $table,
2417                     )
2418                 ) . '">'
2419                 .  htmlspecialchars($db) . '.' . htmlspecialchars($table)
2420                 . '</a>'
2421             )
2422             . '</legend>';
2423
2424         $html_output .= '<table id="tablespecificuserrights" class="data">';
2425         $html_output .= PMA_getHtmlForPrivsTableHead();
2426         $privMap = PMA_getPrivMap($db);
2427         $sql_query = "SELECT `User`, `Host`, `Db`,"
2428             . " 't' AS `Type`, `Table_name`, `Table_priv`"
2429             . " FROM `mysql`.`tables_priv`"
2430             . " WHERE '" . $GLOBALS['dbi']->escapeString($db) . "' LIKE `Db`"
2431             . "     AND '" . $GLOBALS['dbi']->escapeString($table) . "' LIKE `Table_name`"
2432             . "     AND NOT (`Table_priv` = '' AND Column_priv = '')"
2433             . " ORDER BY `User` ASC, `Host` ASC, `Db` ASC, `Table_priv` ASC;";
2434         $res = $GLOBALS['dbi']->query($sql_query);
2435         PMA_mergePrivMapFromResult($privMap, $res);
2436         $html_output .= PMA_getHtmlTableBodyForSpecificDbOrTablePrivs($privMap, $db);
2437         $html_output .= '</table>';
2438
2439         $html_output .= '<div class="floatleft">';
2440         $html_output .= Template::get('select_all')
2441             ->render(
2442                 array(
2443                     'pmaThemeImage' => $GLOBALS['pmaThemeImage'],
2444                     'text_dir'      => $GLOBALS['text_dir'],
2445                     'formName'      => "usersForm",
2446                 )
2447             );
2448         $html_output .= Util::getButtonOrImage(
2449             'submit_mult', 'mult_submit',
2450             __('Export'), 'b_tblexport.png', 'export'
2451         );
2452
2453         $html_output .= '</fieldset>';
2454         $html_output .= '</form>';
2455     } else {
2456         $html_output .= PMA_getHtmlForViewUsersError();
2457     }
2458     // Offer to create a new user for the current database
2459     $html_output .= PMA_getAddUserHtmlFieldset($db, $table);
2460     return $html_output;
2461 }
2462
2463 /**
2464  * gets privilege map
2465  *
2466  * @param string $db the database
2467  *
2468  * @return array $privMap the privilege map
2469  */
2470 function PMA_getPrivMap($db)
2471 {
2472     list($listOfPrivs, $listOfComparedPrivs)
2473         = PMA_getListOfPrivilegesAndComparedPrivileges();
2474     $sql_query
2475         = "("
2476         . " SELECT " . $listOfPrivs . ", '*' AS `Db`, 'g' AS `Type`"
2477         . " FROM `mysql`.`user`"
2478         . " WHERE NOT (" . $listOfComparedPrivs . ")"
2479         . ")"
2480         . " UNION "
2481         . "("
2482         . " SELECT " . $listOfPrivs . ", `Db`, 'd' AS `Type`"
2483         . " FROM `mysql`.`db`"
2484         . " WHERE '" . $GLOBALS['dbi']->escapeString($db) . "' LIKE `Db`"
2485         . "     AND NOT (" . $listOfComparedPrivs . ")"
2486         . ")"
2487         . " ORDER BY `User` ASC, `Host` ASC, `Db` ASC;";
2488     $res = $GLOBALS['dbi']->query($sql_query);
2489     $privMap = array();
2490     PMA_mergePrivMapFromResult($privMap, $res);
2491     return $privMap;
2492 }
2493
2494 /**
2495  * merge privilege map and rows from resultset
2496  *
2497  * @param array  &$privMap the privilege map reference
2498  * @param object $result   the resultset of query
2499  *
2500  * @return void
2501  */
2502 function PMA_mergePrivMapFromResult(&$privMap, $result)
2503 {
2504     while ($row = $GLOBALS['dbi']->fetchAssoc($result)) {
2505         $user = $row['User'];
2506         $host = $row['Host'];
2507         if (! isset($privMap[$user])) {
2508             $privMap[$user] = array();
2509         }
2510         if (! isset($privMap[$user][$host])) {
2511             $privMap[$user][$host] = array();
2512         }
2513         $privMap[$user][$host][] = $row;
2514     }
2515 }
2516
2517 /**
2518  * Get HTML snippet for privileges table head
2519  *
2520  * @return string $html_output
2521  */
2522 function PMA_getHtmlForPrivsTableHead()
2523 {
2524     return '<thead>'
2525         . '<tr>'
2526         . '<th></th>'
2527         . '<th>' . __('User name') . '</th>'
2528         . '<th>' . __('Host name') . '</th>'
2529         . '<th>' . __('Type') . '</th>'
2530         . '<th>' . __('Privileges') . '</th>'
2531         . '<th>' . __('Grant') . '</th>'
2532         . '<th>' . __('Action') . '</th>'
2533         . '</tr>'
2534         . '</thead>';
2535 }
2536
2537 /**
2538  * Get HTML error for View Users form
2539  * For non superusers such as grant/create users
2540  *
2541  * @return string $html_output
2542  */
2543 function PMA_getHtmlForViewUsersError()
2544 {
2545     return Message::error(
2546         __('Not enough privilege to view users.')
2547     )->getDisplay();
2548 }
2549
2550 /**
2551  * Get HTML snippet for table body of specific database or table privileges
2552  *
2553  * @param array  $privMap privilege map
2554  * @param string $db      database
2555  *
2556  * @return string $html_output
2557  */
2558 function PMA_getHtmlTableBodyForSpecificDbOrTablePrivs($privMap, $db)
2559 {
2560     $html_output = '<tbody>';
2561     $index_checkbox = 0;
2562     if (empty($privMap)) {
2563         $html_output .= '<tr>'
2564             . '<td colspan="6">'
2565             . __('No user found.')
2566             . '</td>'
2567             . '</tr>'
2568             . '</tbody>';
2569         return $html_output;
2570     }
2571
2572     foreach ($privMap as $current_user => $val) {
2573         foreach ($val as $current_host => $current_privileges) {
2574             $nbPrivileges = count($current_privileges);
2575             $html_output .= '<tr>';
2576
2577             $value = htmlspecialchars($current_user . '&amp;#27;' . $current_host);
2578             $html_output .= '<td';
2579             if ($nbPrivileges > 1) {
2580                 $html_output .= ' rowspan="' . $nbPrivileges . '"';
2581             }
2582             $html_output .= '>';
2583             $html_output .= '<input type="checkbox" class="checkall" '
2584                 . 'name="selected_usr[]" '
2585                 . 'id="checkbox_sel_users_' . ($index_checkbox++) . '" '
2586                 . 'value="' . $value . '" /></td>' . "\n";
2587
2588             // user
2589             $html_output .= '<td';
2590             if ($nbPrivileges > 1) {
2591                 $html_output .= ' rowspan="' . $nbPrivileges . '"';
2592             }
2593             $html_output .= '>';
2594             if (empty($current_user)) {
2595                 $html_output .= '<span style="color: #FF0000">'
2596                     . __('Any') . '</span>';
2597             } else {
2598                 $html_output .= htmlspecialchars($current_user);
2599             }
2600             $html_output .= '</td>';
2601
2602             // host
2603             $html_output .= '<td';
2604             if ($nbPrivileges > 1) {
2605                 $html_output .= ' rowspan="' . $nbPrivileges . '"';
2606             }
2607             $html_output .= '>';
2608             $html_output .= htmlspecialchars($current_host);
2609             $html_output .= '</td>';
2610
2611             $html_output .= PMA_getHtmlListOfPrivs(
2612                 $db, $current_privileges, $current_user,
2613                 $current_host
2614             );
2615         }
2616     }
2617
2618     //For fetching routine based privileges
2619     $html_output .= PMA_getHtmlTableBodyForSpecificDbRoutinePrivs($db, $index_checkbox);
2620     $html_output .= '</tbody>';
2621
2622     return $html_output;
2623 }
2624
2625 /**
2626  * Get HTML to display privileges
2627  *
2628  * @param string  $db                 Database name
2629  * @param array   $current_privileges List of privileges
2630  * @param string  $current_user       Current user
2631  * @param string  $current_host       Current host
2632  *
2633  * @return string HTML to display privileges
2634  */
2635 function PMA_getHtmlListOfPrivs(
2636     $db, $current_privileges, $current_user,
2637     $current_host
2638 ) {
2639     $nbPrivileges = count($current_privileges);
2640     $html_output = null;
2641     for ($i = 0; $i < $nbPrivileges; $i++) {
2642         $current = $current_privileges[$i];
2643
2644         // type
2645         $html_output .= '<td>';
2646         if ($current['Type'] == 'g') {
2647             $html_output .= __('global');
2648         } elseif ($current['Type'] == 'd') {
2649             if ($current['Db'] == Util::escapeMysqlWildcards($db)) {
2650                 $html_output .= __('database-specific');
2651             } else {
2652                 $html_output .= __('wildcard') . ': '
2653                     . '<code>'
2654                     . htmlspecialchars($current['Db'])
2655                     . '</code>';
2656             }
2657         } elseif ($current['Type'] == 't') {
2658             $html_output .= __('table-specific');
2659         }
2660         $html_output .= '</td>';
2661
2662         // privileges
2663         $html_output .= '<td>';
2664         if (isset($current['Table_name'])) {
2665             $privList = explode(',', $current['Table_priv']);
2666             $privs = array();
2667             $grantsArr = PMA_getTableGrantsArray();
2668             foreach ($grantsArr as $grant) {
2669                 $privs[$grant[0]] = 'N';
2670                 foreach ($privList as $priv) {
2671                     if ($grant[0] == $priv) {
2672                         $privs[$grant[0]] = 'Y';
2673                     }
2674                 }
2675             }
2676             $html_output .= '<code>'
2677                 . join(
2678                     ',',
2679                     PMA_extractPrivInfo($privs, true, true)
2680                 )
2681                 . '</code>';
2682         } else {
2683             $html_output .= '<code>'
2684                 . join(
2685                     ',',
2686                     PMA_extractPrivInfo($current, true, false)
2687                 )
2688                 . '</code>';
2689         }
2690         $html_output .= '</td>';
2691
2692         // grant
2693         $html_output .= '<td>';
2694         $containsGrant = false;
2695         if (isset($current['Table_name'])) {
2696             $privList = explode(',', $current['Table_priv']);
2697             foreach ($privList as $priv) {
2698                 if ($priv == 'Grant') {
2699                     $containsGrant = true;
2700                 }
2701             }
2702         } else {
2703             $containsGrant = $current['Grant_priv'] == 'Y';
2704         }
2705         $html_output .= ($containsGrant ? __('Yes') : __('No'));
2706         $html_output .= '</td>';
2707
2708         // action
2709         $html_output .= '<td>';
2710         if ($GLOBALS['is_grantuser']) {
2711             $specific_db = (isset($current['Db']) && $current['Db'] != '*')
2712                 ? $current['Db'] : '';
2713             $specific_table = (isset($current['Table_name'])
2714                 && $current['Table_name'] != '*')
2715                 ? $current['Table_name'] : '';
2716             $html_output .= PMA_getUserLink(
2717                 'edit',
2718                 $current_user,
2719                 $current_host,
2720                 $specific_db,
2721                 $specific_table
2722             );
2723         }
2724         $html_output .= '</td>';
2725
2726         $html_output .= '</tr>';
2727         if (($i + 1) < $nbPrivileges) {
2728             $html_output .= '<tr class="noclick">';
2729         }
2730     }
2731     return $html_output;
2732 }
2733
2734 /**
2735  * Returns edit, revoke or export link for a user.
2736  *
2737  * @param string $linktype    The link type (edit | revoke | export)
2738  * @param string $username    User name
2739  * @param string $hostname    Host name
2740  * @param string $dbname      Database name
2741  * @param string $tablename   Table name
2742  * @param string $routinename Routine name
2743  * @param string $initial     Initial value
2744  *
2745  * @return string HTML code with link
2746  */
2747 function PMA_getUserLink(
2748     $linktype, $username, $hostname, $dbname = '',
2749     $tablename = '', $routinename = '', $initial = ''
2750 ) {
2751     $html = '<a';
2752     switch($linktype) {
2753     case 'edit':
2754         $html .= ' class="edit_user_anchor"';
2755         break;
2756     case 'export':
2757         $html .= ' class="export_user_anchor ajax"';
2758         break;
2759     }
2760     $params = array(
2761         'username' => $username,
2762         'hostname' => $hostname
2763     );
2764     switch($linktype) {
2765     case 'edit':
2766         $params['dbname'] = $dbname;
2767         $params['tablename'] = $tablename;
2768         $params['routinename'] = $routinename;
2769         break;
2770     case 'revoke':
2771         $params['dbname'] = $dbname;
2772         $params['tablename'] = $tablename;
2773         $params['routinename'] = $routinename;
2774         $params['revokeall'] = 1;
2775         break;
2776     case 'export':
2777         $params['initial'] = $initial;
2778         $params['export'] = 1;
2779         break;
2780     }
2781
2782     $html .= ' href="server_privileges.php'
2783         . URL::getCommon($params)
2784         . '">';
2785
2786     switch($linktype) {
2787     case 'edit':
2788         $html .= Util::getIcon('b_usredit.png', __('Edit privileges'));
2789         break;
2790     case 'revoke':
2791         $html .= Util::getIcon('b_usrdrop.png', __('Revoke'));
2792         break;
2793     case 'export':
2794         $html .= Util::getIcon('b_tblexport.png', __('Export'));
2795         break;
2796     }
2797     $html .= '</a>';
2798
2799     return $html;
2800 }
2801
2802 /**
2803  * Returns user group edit link
2804  *
2805  * @param string $username User name
2806  *
2807  * @return string HTML code with link
2808  */
2809 function PMA_getUserGroupEditLink($username)
2810 {
2811      return '<a class="edit_user_group_anchor ajax"'
2812         . ' href="server_privileges.php'
2813         . URL::getCommon(array('username' => $username))
2814         . '">'
2815         . Util::getIcon('b_usrlist.png', __('Edit user group'))
2816         . '</a>';
2817 }
2818
2819 /**
2820  * Returns number of defined user groups
2821  *
2822  * @return integer $user_group_count
2823  */
2824 function PMA_getUserGroupCount()
2825 {
2826     $cfgRelation = PMA_getRelationsParam();
2827     $user_group_table = Util::backquote($cfgRelation['db'])
2828         . '.' . Util::backquote($cfgRelation['usergroups']);
2829     $sql_query = 'SELECT COUNT(*) FROM ' . $user_group_table;
2830     $user_group_count = $GLOBALS['dbi']->fetchValue(
2831         $sql_query, 0, 0, $GLOBALS['controllink']
2832     );
2833
2834     return $user_group_count;
2835 }
2836
2837 /**
2838  * Returns name of user group that user is part of
2839  *
2840  * @param string $username User name
2841  *
2842  * @return mixed usergroup if found or null if not found
2843  */
2844 function PMA_getUserGroupForUser($username)
2845 {
2846     $cfgRelation = PMA_getRelationsParam();
2847
2848     if (empty($cfgRelation['db'])
2849         || empty($cfgRelation['users'])
2850     ) {
2851         return null;
2852     }
2853
2854     $user_table = Util::backquote($cfgRelation['db'])
2855         . '.' . Util::backquote($cfgRelation['users']);
2856     $sql_query = 'SELECT `usergroup` FROM ' . $user_table
2857         . ' WHERE `username` = \'' . $username . '\''
2858         . ' LIMIT 1';
2859
2860     $usergroup = $GLOBALS['dbi']->fetchValue(
2861         $sql_query, 0, 0, $GLOBALS['controllink']
2862     );
2863
2864     if ($usergroup === false) {
2865         return null;
2866     }
2867
2868     return $usergroup;
2869 }
2870
2871 /**
2872  * This function return the extra data array for the ajax behavior
2873  *
2874  * @param string $password  password
2875  * @param string $sql_query sql query
2876  * @param string $hostname  hostname
2877  * @param string $username  username
2878  *
2879  * @return array $extra_data
2880  */
2881 function PMA_getExtraDataForAjaxBehavior(
2882     $password, $sql_query, $hostname, $username
2883 ) {
2884     if (isset($GLOBALS['dbname'])) {
2885         //if (preg_match('/\\\\(?:_|%)/i', $dbname)) {
2886         if (preg_match('/(?<!\\\\)(?:_|%)/i', $GLOBALS['dbname'])) {
2887             $dbname_is_wildcard = true;
2888         } else {
2889             $dbname_is_wildcard = false;
2890         }
2891     }
2892
2893     $user_group_count = 0;
2894     if ($GLOBALS['cfgRelation']['menuswork']) {
2895         $user_group_count = PMA_getUserGroupCount();
2896     }
2897
2898     $extra_data = array();
2899     if (strlen($sql_query) > 0) {
2900         $extra_data['sql_query'] = Util::getMessage(null, $sql_query);
2901     }
2902
2903     if (isset($_REQUEST['change_copy'])) {
2904         /**
2905          * generate html on the fly for the new user that was just created.
2906          */
2907         $new_user_string = '<tr>' . "\n"
2908             . '<td> <input type="checkbox" name="selected_usr[]" '
2909             . 'id="checkbox_sel_users_"'
2910             . 'value="'
2911             . htmlspecialchars($username)
2912             . '&amp;#27;' . htmlspecialchars($hostname) . '" />'
2913             . '</td>' . "\n"
2914             . '<td><label for="checkbox_sel_users_">'
2915             . (empty($_REQUEST['username'])
2916                     ? '<span style="color: #FF0000">' . __('Any') . '</span>'
2917                     : htmlspecialchars($username) ) . '</label></td>' . "\n"
2918             . '<td>' . htmlspecialchars($hostname) . '</td>' . "\n";
2919
2920         $new_user_string .= '<td>';
2921
2922         if (! empty($password) || isset($_POST['pma_pw'])) {
2923             $new_user_string .= __('Yes');
2924         } else {
2925             $new_user_string .= '<span style="color: #FF0000">'
2926                 . __('No')
2927             . '</span>';
2928         };
2929
2930         $new_user_string .= '</td>' . "\n";
2931         $new_user_string .= '<td>'
2932             . '<code>' . join(', ', PMA_extractPrivInfo(null, true)) . '</code>'
2933             . '</td>'; //Fill in privileges here
2934
2935         // if $cfg['Servers'][$i]['users'] and $cfg['Servers'][$i]['usergroups'] are
2936         // enabled
2937         $cfgRelation = PMA_getRelationsParam();
2938         if (!empty($cfgRelation['users']) && !empty($cfgRelation['usergroups'])) {
2939             $new_user_string .= '<td class="usrGroup"></td>';
2940         }
2941
2942         $new_user_string .= '<td>';
2943         if ((isset($_POST['Grant_priv']) && $_POST['Grant_priv'] == 'Y')) {
2944             $new_user_string .= __('Yes');
2945         } else {
2946             $new_user_string .= __('No');
2947         }
2948         $new_user_string .='</td>';
2949
2950         if ($GLOBALS['is_grantuser']) {
2951             $new_user_string .= '<td>'
2952                 . PMA_getUserLink('edit', $username, $hostname)
2953                 . '</td>' . "\n";
2954         }
2955
2956         if ($cfgRelation['menuswork'] && $user_group_count > 0) {
2957             $new_user_string .= '<td>'
2958                 . PMA_getUserGroupEditLink($username)
2959                 . '</td>' . "\n";
2960         }
2961
2962         $new_user_string .= '<td>'
2963             . PMA_getUserLink(
2964                 'export',
2965                 $username,
2966                 $hostname,
2967                 '',
2968                 '',
2969                 '',
2970                 isset($_GET['initial']) ? $_GET['initial'] : ''
2971             )
2972             . '</td>' . "\n";
2973
2974         $new_user_string .= '</tr>';
2975
2976         $extra_data['new_user_string'] = $new_user_string;
2977
2978         /**
2979          * Generate the string for this alphabet's initial, to update the user
2980          * pagination
2981          */
2982         $new_user_initial = mb_strtoupper(
2983             mb_substr($username, 0, 1)
2984         );
2985         $newUserInitialString = '<a href="server_privileges.php'
2986             . URL::getCommon(array('initial' => $new_user_initial)) . '">'
2987             . $new_user_initial . '</a>';
2988         $extra_data['new_user_initial'] = $new_user_initial;
2989         $extra_data['new_user_initial_string'] = $newUserInitialString;
2990     }
2991
2992     if (isset($_POST['update_privs'])) {
2993         $extra_data['db_specific_privs'] = false;
2994         $extra_data['db_wildcard_privs'] = false;
2995         if (isset($dbname_is_wildcard)) {
2996             $extra_data['db_specific_privs'] = ! $dbname_is_wildcard;
2997             $extra_data['db_wildcard_privs'] = $dbname_is_wildcard;
2998         }
2999         $new_privileges = join(', ', PMA_extractPrivInfo(null, true));
3000
3001         $extra_data['new_privileges'] = $new_privileges;
3002     }
3003
3004     if (isset($_REQUEST['validate_username'])) {
3005         $sql_query = "SELECT * FROM `mysql`.`user` WHERE `User` = '"
3006             . $_REQUEST['username'] . "';";
3007         $res = $GLOBALS['dbi']->query($sql_query);
3008         $row = $GLOBALS['dbi']->fetchRow($res);
3009         if (empty($row)) {
3010             $extra_data['user_exists'] = false;
3011         } else {
3012             $extra_data['user_exists'] = true;
3013         }
3014     }
3015
3016     return $extra_data;
3017 }
3018
3019 /**
3020  * Get the HTML snippet for change user login information
3021  *
3022  * @param string $username username
3023  * @param string $hostname host name
3024  *
3025  * @return string HTML snippet
3026  */
3027 function PMA_getChangeLoginInformationHtmlForm($username, $hostname)
3028 {
3029     $choices = array(
3030         '4' => __('… keep the old one.'),
3031         '1' => __('… delete the old one from the user tables.'),
3032         '2' => __(
3033             '… revoke all active privileges from '
3034             . 'the old one and delete it afterwards.'
3035         ),
3036         '3' => __(
3037             '… delete the old one from the user tables '
3038             . 'and reload the privileges afterwards.'
3039         )
3040     );
3041
3042     $html_output = '<form action="server_privileges.php" '
3043         . 'onsubmit="return checkAddUser(this);" '
3044         . 'method="post" class="copyUserForm submenu-item">' . "\n"
3045         . URL::getHiddenInputs('', '')
3046         . '<input type="hidden" name="old_username" '
3047         . 'value="' . htmlspecialchars($username) . '" />' . "\n"
3048         . '<input type="hidden" name="old_hostname" '
3049         . 'value="' . htmlspecialchars($hostname) . '" />' . "\n";
3050
3051     $usergroup = PMA_getUserGroupForUser($username);
3052     if ($usergroup !== null) {
3053         $html_output .= '<input type="hidden" name="old_usergroup" '
3054         . 'value="' . htmlspecialchars($usergroup) . '" />' . "\n";
3055     }
3056
3057     $html_output .= '<fieldset id="fieldset_change_copy_user">' . "\n"
3058         . '<legend data-submenu-label="' . __('Login Information') . '">' . "\n"
3059         . __('Change login information / Copy user account')
3060         . '</legend>' . "\n"
3061         . PMA_getHtmlForLoginInformationFields('change', $username, $hostname);
3062
3063     $html_output .= '<fieldset id="fieldset_mode">' . "\n"
3064         . ' <legend>'
3065         . __('Create a new user account with the same privileges and …')
3066         . '</legend>' . "\n";
3067     $html_output .= Util::getRadioFields(
3068         'mode', $choices, '4', true
3069     );
3070     $html_output .= '</fieldset>' . "\n"
3071        . '</fieldset>' . "\n";
3072
3073     $html_output .= '<fieldset id="fieldset_change_copy_user_footer" '
3074         . 'class="tblFooters">' . "\n"
3075         . '<input type="hidden" name="change_copy" value="1" />' . "\n"
3076         . '<input type="submit" value="' . __('Go') . '" />' . "\n"
3077         . '</fieldset>' . "\n"
3078         . '</form>' . "\n";
3079
3080     return $html_output;
3081 }
3082
3083 /**
3084  * Provide a line with links to the relevant database and table
3085  *
3086  * @param string $url_dbname url database name that urlencode() string
3087  * @param string $dbname     database name
3088  * @param string $tablename  table name
3089  *
3090  * @return string HTML snippet
3091  */
3092 function PMA_getLinkToDbAndTable($url_dbname, $dbname, $tablename)
3093 {
3094     $html_output = '[ ' . __('Database')
3095         . ' <a href="' . Util::getScriptNameForOption(
3096             $GLOBALS['cfg']['DefaultTabDatabase'], 'database'
3097         )
3098         . URL::getCommon(
3099             array(
3100                 'db' => $url_dbname,
3101                 'reload' => 1
3102             )
3103         )
3104         . '">'
3105         . htmlspecialchars($dbname) . ': '
3106         . Util::getTitleForTarget(
3107             $GLOBALS['cfg']['DefaultTabDatabase']
3108         )
3109         . "</a> ]\n";
3110
3111     if (strlen($tablename) > 0) {
3112         $html_output .= ' [ ' . __('Table') . ' <a href="'
3113             . Util::getScriptNameForOption(
3114                 $GLOBALS['cfg']['DefaultTabTable'], 'table'
3115             )
3116             . URL::getCommon(
3117                 array(
3118                     'db' => $url_dbname,
3119                     'table' => $tablename,
3120                     'reload' => 1,
3121                 )
3122             )
3123             . '">' . htmlspecialchars($tablename) . ': '
3124             . Util::getTitleForTarget(
3125                 $GLOBALS['cfg']['DefaultTabTable']
3126             )
3127             . "</a> ]\n";
3128     }
3129     return $html_output;
3130 }
3131
3132 /**
3133  * no db name given, so we want all privs for the given user
3134  * db name was given, so we want all user specific rights for this db
3135  * So this function returns user rights as an array
3136  *
3137  * @param string $username username
3138  * @param string $hostname host name
3139  * @param string $type     database or table
3140  * @param string $dbname   database name
3141  *
3142  * @return array $db_rights database rights
3143  */
3144 function PMA_getUserSpecificRights($username, $hostname, $type, $dbname = '')
3145 {
3146     $user_host_condition = " WHERE `User`"
3147         . " = '" . $GLOBALS['dbi']->escapeString($username) . "'"
3148         . " AND `Host`"
3149         . " = '" . $GLOBALS['dbi']->escapeString($hostname) . "'";
3150
3151     if ($type == 'database') {
3152         $tables_to_search_for_users = array(
3153             'tables_priv', 'columns_priv', 'procs_priv'
3154         );
3155         $dbOrTableName = 'Db';
3156     } elseif ($type == 'table') {
3157         $user_host_condition .= " AND `Db` LIKE '"
3158             . $GLOBALS['dbi']->escapeString($dbname) . "'";
3159         $tables_to_search_for_users = array('columns_priv',);
3160         $dbOrTableName = 'Table_name';
3161     } else { // routine
3162         $user_host_condition .= " AND `Db` LIKE '"
3163             . $GLOBALS['dbi']->escapeString($dbname) . "'";
3164         $tables_to_search_for_users = array('procs_priv',);
3165         $dbOrTableName = 'Routine_name';
3166     }
3167
3168     // we also want privileges for this user not in table `db` but in other table
3169     $tables = $GLOBALS['dbi']->fetchResult('SHOW TABLES FROM `mysql`;');
3170
3171     $db_rights_sqls = array();
3172     foreach ($tables_to_search_for_users as $table_search_in) {
3173         if (in_array($table_search_in, $tables)) {
3174             $db_rights_sqls[] = '
3175                 SELECT DISTINCT `' . $dbOrTableName . '`
3176                 FROM `mysql`.' . Util::backquote($table_search_in)
3177                . $user_host_condition;
3178         }
3179     }
3180
3181     $user_defaults = array(
3182         $dbOrTableName  => '',
3183         'Grant_priv'    => 'N',
3184         'privs'         => array('USAGE'),
3185         'Column_priv'   => true,
3186     );
3187
3188     // for the rights
3189     $db_rights = array();
3190
3191     $db_rights_sql = '(' . implode(') UNION (', $db_rights_sqls) . ')'
3192         . ' ORDER BY `' . $dbOrTableName . '` ASC';
3193
3194     $db_rights_result = $GLOBALS['dbi']->query($db_rights_sql);
3195
3196     while ($db_rights_row = $GLOBALS['dbi']->fetchAssoc($db_rights_result)) {
3197         $db_rights_row = array_merge($user_defaults, $db_rights_row);
3198         if ($type == 'database') {
3199             // only Db names in the table `mysql`.`db` uses wildcards
3200             // as we are in the db specific rights display we want
3201             // all db names escaped, also from other sources
3202             $db_rights_row['Db'] = Util::escapeMysqlWildcards(
3203                 $db_rights_row['Db']
3204             );
3205         }
3206         $db_rights[$db_rights_row[$dbOrTableName]] = $db_rights_row;
3207     }
3208
3209     $GLOBALS['dbi']->freeResult($db_rights_result);
3210
3211     if ($type == 'database') {
3212         $sql_query = 'SELECT * FROM `mysql`.`db`'
3213             . $user_host_condition . ' ORDER BY `Db` ASC';
3214     } elseif ($type == 'table') {
3215         $sql_query = 'SELECT `Table_name`,'
3216             . ' `Table_priv`,'
3217             . ' IF(`Column_priv` = _latin1 \'\', 0, 1)'
3218             . ' AS \'Column_priv\''
3219             . ' FROM `mysql`.`tables_priv`'
3220             . $user_host_condition
3221             . ' ORDER BY `Table_name` ASC;';
3222     } else {
3223         $sql_query = "SELECT `Routine_name`, `Proc_priv`"
3224             . " FROM `mysql`.`procs_priv`"
3225             . $user_host_condition
3226             . " ORDER BY `Routine_name`";
3227
3228     }
3229
3230     $result = $GLOBALS['dbi']->query($sql_query);
3231
3232     while ($row = $GLOBALS['dbi']->fetchAssoc($result)) {
3233         if (isset($db_rights[$row[$dbOrTableName]])) {
3234             $db_rights[$row[$dbOrTableName]]
3235                 = array_merge($db_rights[$row[$dbOrTableName]], $row);
3236         } else {
3237             $db_rights[$row[$dbOrTableName]] = $row;
3238         }
3239         if ($type == 'database') {
3240             // there are db specific rights for this user
3241             // so we can drop this db rights
3242             $db_rights[$row['Db']]['can_delete'] = true;
3243         }
3244     }
3245     $GLOBALS['dbi']->freeResult($result);
3246     return $db_rights;
3247 }
3248
3249 /**
3250  * Parses Proc_priv data
3251  *
3252  * @param string $privs Proc_priv
3253  *
3254  * @return array
3255  */
3256 function PMA_parseProcPriv($privs)
3257 {
3258     $result = array(
3259         'Alter_routine_priv' => 'N',
3260         'Execute_priv'       => 'N',
3261         'Grant_priv'         => 'N',
3262     );
3263     foreach (explode(',', $privs) as $priv) {
3264         if ($priv == 'Alter Routine') {
3265             $result['Alter_routine_priv'] = 'Y';
3266         } else {
3267             $result[$priv . '_priv'] = 'Y';
3268         }
3269     }
3270     return $result;
3271 }
3272
3273 /**
3274  * Get a HTML table for display user's tabel specific or database specific rights
3275  *
3276  * @param string $username username
3277  * @param string $hostname host name
3278  * @param string $type     database, table or routine
3279  * @param string $dbname   database name
3280  *
3281  * @return array $html_output
3282  */
3283 function PMA_getHtmlForAllTableSpecificRights(
3284     $username, $hostname, $type, $dbname = ''
3285 ) {
3286     $uiData = array(
3287         'database' => array(
3288             'formId'       => 'database_specific_priv',
3289             'subMenuLabel' => __('Database'),
3290             'legend'       => __('Database-specific privileges'),
3291             'typeLabel'    => __('Database'),
3292         ),
3293         'table' => array(
3294             'formId'       => 'table_specific_priv',
3295             'subMenuLabel' => __('Table'),
3296             'legend'       => __('Table-specific privileges'),
3297             'typeLabel'    => __('Table'),
3298         ),
3299         'routine' => array(
3300             'formId'       => 'routine_specific_priv',
3301             'subMenuLabel' => __('Routine'),
3302             'legend'       => __('Routine-specific privileges'),
3303             'typeLabel'    => __('Routine'),
3304         ),
3305     );
3306
3307     /**
3308      * no db name given, so we want all privs for the given user
3309      * db name was given, so we want all user specific rights for this db
3310      */
3311     $db_rights = PMA_getUserSpecificRights($username, $hostname, $type, $dbname);
3312     ksort($db_rights);
3313
3314     $foundRows = array();
3315     $privileges = array();
3316     foreach ($db_rights as $row) {
3317         $onePrivilege = array();
3318
3319         $paramTableName = '';
3320         $paramRoutineName = '';
3321
3322         if ($type == 'database') {
3323             $name = $row['Db'];
3324             $onePrivilege['grant']        = $row['Grant_priv'] == 'Y';
3325             $onePrivilege['tablePrivs']   = ! empty($row['Table_priv'])
3326                 || ! empty($row['Column_priv']);
3327             $onePrivilege['privileges'] = join(',', PMA_extractPrivInfo($row, true));
3328
3329             $paramDbName = $row['Db'];
3330
3331         } elseif ($type == 'table') {
3332             $name = $row['Table_name'];
3333             $onePrivilege['grant'] = in_array(
3334                 'Grant',
3335                 explode(',', $row['Table_priv'])
3336             );
3337             $onePrivilege['columnPrivs']  = ! empty($row['Column_priv']);
3338             $onePrivilege['privileges'] = join(',', PMA_extractPrivInfo($row, true));
3339
3340             $paramDbName = $dbname;
3341             $paramTableName = $row['Table_name'];
3342
3343         } else { // routine
3344             $name = $row['Routine_name'];
3345             $onePrivilege['grant'] = in_array(
3346                 'Grant',
3347                 explode(',', $row['Proc_priv'])
3348             );
3349
3350             $privs = PMA_parseProcPriv($row['Proc_priv']);
3351             $onePrivilege['privileges'] = join(
3352                 ',',
3353                 PMA_extractPrivInfo($privs, true)
3354             );
3355
3356             $paramDbName = $dbname;
3357             $paramRoutineName = $row['Routine_name'];
3358         }
3359
3360         $foundRows[] = $name;
3361         $onePrivilege['name'] = $name;
3362
3363         $onePrivilege['editLink'] = '';
3364         if ($GLOBALS['is_grantuser']) {
3365             $onePrivilege['editLink'] = PMA_getUserLink(
3366                 'edit',
3367                 $username,
3368                 $hostname,
3369                 $paramDbName,
3370                 $paramTableName,
3371                 $paramRoutineName
3372             );
3373         }
3374
3375         $onePrivilege['revokeLink'] = '';
3376         if ($type != 'database' || ! empty($row['can_delete'])) {
3377             $onePrivilege['revokeLink'] = PMA_getUserLink(
3378                 'revoke',
3379                 $username,
3380                 $hostname,
3381                 $paramDbName,
3382                 $paramTableName,
3383                 $paramRoutineName
3384             );
3385         }
3386
3387         $privileges[] = $onePrivilege;
3388     }
3389
3390     $data = $uiData[$type];
3391     $data['privileges'] = $privileges;
3392     $data['userName']   = $username;
3393     $data['hostName']   = $hostname;
3394     $data['database']   = $dbname;
3395     $data['type']       = $type;
3396
3397     if ($type == 'database') {
3398
3399         // we already have the list of databases from libraries/common.inc.php
3400         // via $pma = new PMA;
3401         $pred_db_array = $GLOBALS['dblist']->databases;
3402         $databases_to_skip = array('information_schema', 'performance_schema');
3403
3404         $databases = array();
3405         if (! empty($pred_db_array)) {
3406             foreach ($pred_db_array as $current_db) {
3407                 if (in_array($current_db, $databases_to_skip)) {
3408                     continue;
3409                 }
3410                 $current_db_escaped = Util::escapeMysqlWildcards($current_db);
3411                 // cannot use array_diff() once, outside of the loop,
3412                 // because the list of databases has special characters
3413                 // already escaped in $foundRows,
3414                 // contrary to the output of SHOW DATABASES
3415                 if (! in_array($current_db_escaped, $foundRows)) {
3416                     $databases[] = $current_db;
3417                 }
3418             }
3419         }
3420         $data['databases'] = $databases;
3421
3422     } elseif ($type == 'table') {
3423         $result = @$GLOBALS['dbi']->tryQuery(
3424             "SHOW TABLES FROM " . Util::backquote($dbname),
3425             null,
3426             DatabaseInterface::QUERY_STORE
3427         );
3428
3429         $tables = array();
3430         if ($result) {
3431             while ($row = $GLOBALS['dbi']->fetchRow($result)) {
3432                 if (! in_array($row[0], $foundRows)) {
3433                     $tables[] = $row[0];
3434                 }
3435             }
3436             $GLOBALS['dbi']->freeResult($result);
3437         }
3438         $data['tables'] = $tables;
3439
3440     } else { // routine
3441         $routineData = $GLOBALS['dbi']->getRoutines($dbname);
3442
3443         $routines = array();
3444         foreach ($routineData as $routine) {
3445             if (! in_array($routine['name'], $foundRows)) {
3446                 $routines[] = $routine['name'];
3447             }
3448         }
3449         $data['routines'] = $routines;
3450     }
3451
3452     $html_output = Template::get('privileges/privileges_summary')
3453         ->render($data);
3454
3455     return $html_output;
3456 }
3457
3458 /**
3459  * Get HTML for display the users overview
3460  * (if less than 50 users, display them immediately)
3461  *
3462  * @param array  $result        ran sql query
3463  * @param array  $db_rights     user's database rights array
3464  * @param string $pmaThemeImage a image source link
3465  * @param string $text_dir      text directory
3466  *
3467  * @return string HTML snippet
3468  */
3469 function PMA_getUsersOverview($result, $db_rights, $pmaThemeImage, $text_dir)
3470 {
3471     while ($row = $GLOBALS['dbi']->fetchAssoc($result)) {
3472         $row['privs'] = PMA_extractPrivInfo($row, true);
3473         $db_rights[$row['User']][$row['Host']] = $row;
3474     }
3475     $GLOBALS['dbi']->freeResult($result);
3476     $user_group_count = 0;
3477     if ($GLOBALS['cfgRelation']['menuswork']) {
3478         $user_group_count = PMA_getUserGroupCount();
3479     }
3480
3481     $html_output
3482         = '<form name="usersForm" id="usersForm" action="server_privileges.php" '
3483         . 'method="post">' . "\n"
3484         . URL::getHiddenInputs('', '')
3485         . '<table id="tableuserrights" class="data">' . "\n"
3486         . '<thead>' . "\n"
3487         . '<tr><th></th>' . "\n"
3488         . '<th>' . __('User name') . '</th>' . "\n"
3489         . '<th>' . __('Host name') . '</th>' . "\n"
3490         . '<th>' . __('Password') . '</th>' . "\n"
3491         . '<th>' . __('Global privileges') . ' '
3492         . Util::showHint(
3493             __('Note: MySQL privilege names are expressed in English.')
3494         )
3495         . '</th>' . "\n";
3496     if ($GLOBALS['cfgRelation']['menuswork']) {
3497         $html_output .= '<th>' . __('User group') . '</th>' . "\n";
3498     }
3499     $html_output .= '<th>' . __('Grant') . '</th>' . "\n"
3500         . '<th colspan="' . ($user_group_count > 0 ? '3' : '2') . '">'
3501         . __('Action') . '</th>' . "\n"
3502         . '</tr>' . "\n"
3503         . '</thead>' . "\n";
3504
3505     $html_output .= '<tbody>' . "\n";
3506     $html_output .= PMA_getHtmlTableBodyForUserRights($db_rights);
3507     $html_output .= '</tbody>'
3508         . '</table>' . "\n";
3509
3510     $html_output .= '<div class="floatleft">'
3511         . Template::get('select_all')
3512             ->render(
3513                 array(
3514                     'pmaThemeImage' => $pmaThemeImage,
3515                     'text_dir'      => $text_dir,
3516                     'formName'      => 'usersForm',
3517                 )
3518             ) . "\n";
3519     $html_output .= Util::getButtonOrImage(
3520         'submit_mult', 'mult_submit',
3521         __('Export'), 'b_tblexport.png', 'export'
3522     );
3523     $html_output .= '<input type="hidden" name="initial" '
3524         . 'value="' . (isset($_GET['initial']) ? htmlspecialchars($_GET['initial']) : '') . '" />';
3525     $html_output .= '</div>'
3526         . '<div class="clear_both" style="clear:both"></div>';
3527
3528     // add/delete user fieldset
3529     $html_output .= PMA_getFieldsetForAddDeleteUser();
3530     $html_output .= '</form>' . "\n";
3531
3532     return $html_output;
3533 }
3534
3535 /**
3536  * Get table body for 'tableuserrights' table in userform
3537  *
3538  * @param array $db_rights user's database rights array
3539  *
3540  * @return string HTML snippet
3541  */
3542 function PMA_getHtmlTableBodyForUserRights($db_rights)
3543 {
3544     $cfgRelation = PMA_getRelationsParam();
3545     if ($cfgRelation['menuswork']) {
3546         $users_table = Util::backquote($cfgRelation['db'])
3547             . "." . Util::backquote($cfgRelation['users']);
3548         $sql_query = 'SELECT * FROM ' . $users_table;
3549         $result = PMA_queryAsControlUser($sql_query, false);
3550         $group_assignment = array();
3551         if ($result) {
3552             while ($row = $GLOBALS['dbi']->fetchAssoc($result)) {
3553                 $group_assignment[$row['username']] = $row['usergroup'];
3554             }
3555         }
3556         $GLOBALS['dbi']->freeResult($result);
3557
3558         $user_group_count = PMA_getUserGroupCount();
3559     }
3560
3561     $index_checkbox = 0;
3562     $html_output = '';
3563     foreach ($db_rights as $user) {
3564         ksort($user);
3565         foreach ($user as $host) {
3566             $index_checkbox++;
3567             $html_output .= '<tr>'
3568                 . "\n";
3569             $html_output .= '<td>'
3570                 . '<input type="checkbox" class="checkall" name="selected_usr[]" '
3571                 . 'id="checkbox_sel_users_'
3572                 . $index_checkbox . '" value="'
3573                 . htmlspecialchars($host['User'] . '&amp;#27;' . $host['Host'])
3574                 . '"'
3575                 . ' /></td>' . "\n";
3576
3577             $html_output .= '<td><label '
3578                 . 'for="checkbox_sel_users_' . $index_checkbox . '">'
3579                 . (empty($host['User'])
3580                     ? '<span style="color: #FF0000">' . __('Any') . '</span>'
3581                     : htmlspecialchars($host['User'])) . '</label></td>' . "\n"
3582                 . '<td>' . htmlspecialchars($host['Host']) . '</td>' . "\n";
3583
3584             $html_output .= '<td>';
3585
3586             $password_column = 'Password';
3587
3588             $check_plugin_query = "SELECT * FROM `mysql`.`user` WHERE "
3589                 . "`User` = '" . $host['User'] . "' AND `Host` = '"
3590                 . $host['Host'] . "'";
3591             $res = $GLOBALS['dbi']->fetchSingleRow($check_plugin_query);
3592
3593             if ((isset($res['authentication_string'])
3594                 && ! empty($res['authentication_string']))
3595                 || (isset($res['Password'])
3596                 && ! empty($res['Password']))
3597             ) {
3598                 $host[$password_column] = 'Y';
3599             } else {
3600                 $host[$password_column] = 'N';
3601             }
3602
3603             switch ($host[$password_column]) {
3604             case 'Y':
3605                 $html_output .= __('Yes');
3606                 break;
3607             case 'N':
3608                 $html_output .= '<span style="color: #FF0000">' . __('No')
3609                     . '</span>';
3610                 break;
3611             // this happens if this is a definition not coming from mysql.user
3612             default:
3613                 $html_output .= '--'; // in future version, replace by "not present"
3614                 break;
3615             } // end switch
3616
3617             $html_output .= '</td>' . "\n";
3618
3619             $html_output .= '<td><code>' . "\n"
3620                 . '' . implode(',' . "\n" . '            ', $host['privs']) . "\n"
3621                 . '</code></td>' . "\n";
3622             if ($cfgRelation['menuswork']) {
3623                 $html_output .= '<td class="usrGroup">' . "\n"
3624                     . (isset($group_assignment[$host['User']])
3625                         ? htmlspecialchars($group_assignment[$host['User']])
3626                         : ''
3627                     )
3628                     . '</td>' . "\n";
3629             }
3630             $html_output .= '<td>'
3631                 . ($host['Grant_priv'] == 'Y' ? __('Yes') : __('No'))
3632                 . '</td>' . "\n";
3633
3634             if ($GLOBALS['is_grantuser']) {
3635                 $html_output .= '<td class="center">'
3636                     . PMA_getUserLink(
3637                         'edit',
3638                         $host['User'],
3639                         $host['Host']
3640                     )
3641                     . '</td>';
3642             }
3643             if ($cfgRelation['menuswork'] && $user_group_count > 0) {
3644                 if (empty($host['User'])) {
3645                     $html_output .= '<td class="center"></td>';
3646                 } else {
3647                     $html_output .= '<td class="center">'
3648                         . PMA_getUserGroupEditLink($host['User'])
3649                         . '</td>';
3650                 }
3651             }
3652             $html_output .= '<td class="center">'
3653                 . PMA_getUserLink(
3654                     'export',
3655                     $host['User'],
3656                     $host['Host'],
3657                     '',
3658                     '',
3659                     '',
3660                     isset($_GET['initial']) ? $_GET['initial'] : ''
3661                 )
3662                 . '</td>';
3663             $html_output .= '</tr>';
3664         }
3665     }
3666     return $html_output;
3667 }
3668
3669 /**
3670  * Get HTML fieldset for Add/Delete user
3671  *
3672  * @return string HTML snippet
3673  */
3674 function PMA_getFieldsetForAddDeleteUser()
3675 {
3676     $html_output = PMA_getAddUserHtmlFieldset();
3677
3678     $html_output .= Template::get('privileges/delete_user_fieldset')
3679         ->render(array());
3680
3681     return $html_output;
3682 }
3683
3684 /**
3685  * Get HTML for Displays the initials
3686  *
3687  * @param array $array_initials array for all initials, even non A-Z
3688  *
3689  * @return string HTML snippet
3690  */
3691 function PMA_getHtmlForInitials($array_initials)
3692 {
3693     // initialize to false the letters A-Z
3694     for ($letter_counter = 1; $letter_counter < 27; $letter_counter++) {
3695         if (! isset($array_initials[mb_chr($letter_counter + 64)])) {
3696             $array_initials[mb_chr($letter_counter + 64)] = false;
3697         }
3698     }
3699
3700     $initials = $GLOBALS['dbi']->tryQuery(
3701         'SELECT DISTINCT UPPER(LEFT(`User`,1)) FROM `user`'
3702         . ' ORDER BY UPPER(LEFT(`User`,1)) ASC',
3703         null,
3704         PMA\libraries\DatabaseInterface::QUERY_STORE
3705     );
3706     if ($initials) {
3707         while (list($tmp_initial) = $GLOBALS['dbi']->fetchRow($initials)) {
3708             $array_initials[$tmp_initial] = true;
3709         }
3710     }
3711
3712     // Display the initials, which can be any characters, not
3713     // just letters. For letters A-Z, we add the non-used letters
3714     // as greyed out.
3715
3716     uksort($array_initials, "strnatcasecmp");
3717
3718     $html_output = Template::get('privileges/initials_row')
3719         ->render(
3720             array(
3721                 'array_initials' => $array_initials
3722             )
3723         );
3724
3725     return $html_output;
3726 }
3727
3728 /**
3729  * Get the database rights array for Display user overview
3730  *
3731  * @return array  $db_rights    database rights array
3732  */
3733 function PMA_getDbRightsForUserOverview()
3734 {
3735     // we also want users not in table `user` but in other table
3736     $tables = $GLOBALS['dbi']->fetchResult('SHOW TABLES FROM `mysql`;');
3737
3738     $tablesSearchForUsers = array(
3739         'user', 'db', 'tables_priv', 'columns_priv', 'procs_priv',
3740     );
3741
3742     $db_rights_sqls = array();
3743     foreach ($tablesSearchForUsers as $table_search_in) {
3744         if (in_array($table_search_in, $tables)) {
3745             $db_rights_sqls[] = 'SELECT DISTINCT `User`, `Host` FROM `mysql`.`'
3746                 . $table_search_in . '` '
3747                 . (isset($_GET['initial'])
3748                 ? PMA_rangeOfUsers($_GET['initial'])
3749                 : '');
3750         }
3751     }
3752     $user_defaults = array(
3753         'User'       => '',
3754         'Host'       => '%',
3755         'Password'   => '?',
3756         'Grant_priv' => 'N',
3757         'privs'      => array('USAGE'),
3758     );
3759
3760     // for the rights
3761     $db_rights = array();
3762
3763     $db_rights_sql = '(' . implode(') UNION (', $db_rights_sqls) . ')'
3764         . ' ORDER BY `User` ASC, `Host` ASC';
3765
3766     $db_rights_result = $GLOBALS['dbi']->query($db_rights_sql);
3767
3768     while ($db_rights_row = $GLOBALS['dbi']->fetchAssoc($db_rights_result)) {
3769         $db_rights_row = array_merge($user_defaults, $db_rights_row);
3770         $db_rights[$db_rights_row['User']][$db_rights_row['Host']]
3771             = $db_rights_row;
3772     }
3773     $GLOBALS['dbi']->freeResult($db_rights_result);
3774     ksort($db_rights);
3775
3776     return $db_rights;
3777 }
3778
3779 /**
3780  * Delete user and get message and sql query for delete user in privileges
3781  *
3782  * @param array $queries queries
3783  *
3784  * @return array Message
3785  */
3786 function PMA_deleteUser($queries)
3787 {
3788     $sql_query = '';
3789     if (empty($queries)) {
3790         $message = Message::error(__('No users selected for deleting!'));
3791     } else {
3792         if ($_REQUEST['mode'] == 3) {
3793             $queries[] = '# ' . __('Reloading the privileges') . ' …';
3794             $queries[] = 'FLUSH PRIVILEGES;';
3795         }
3796         $drop_user_error = '';
3797         foreach ($queries as $sql_query) {
3798             if ($sql_query{0} != '#') {
3799                 if (! $GLOBALS['dbi']->tryQuery($sql_query, $GLOBALS['userlink'])) {
3800                     $drop_user_error .= $GLOBALS['dbi']->getError() . "\n";
3801                 }
3802             }
3803         }
3804         // tracking sets this, causing the deleted db to be shown in navi
3805         unset($GLOBALS['db']);
3806
3807         $sql_query = join("\n", $queries);
3808         if (! empty($drop_user_error)) {
3809             $message = Message::rawError($drop_user_error);
3810         } else {
3811             $message = Message::success(
3812                 __('The selected users have been deleted successfully.')
3813             );
3814         }
3815     }
3816     return array($sql_query, $message);
3817 }
3818
3819 /**
3820  * Update the privileges and return the success or error message
3821  *
3822  * @param string $username  username
3823  * @param string $hostname  host name
3824  * @param string $tablename table name
3825  * @param string $dbname    database name
3826  * @param string $itemType  item type
3827  *
3828  * @return Message success message or error message for update
3829  */
3830 function PMA_updatePrivileges($username, $hostname, $tablename, $dbname, $itemType)
3831 {
3832     $db_and_table = PMA_wildcardEscapeForGrant($dbname, $tablename);
3833
3834     $sql_query0 = 'REVOKE ALL PRIVILEGES ON ' . $itemType . ' ' . $db_and_table
3835         . ' FROM \'' . $GLOBALS['dbi']->escapeString($username)
3836         . '\'@\'' . $GLOBALS['dbi']->escapeString($hostname) . '\';';
3837
3838     if (! isset($_POST['Grant_priv']) || $_POST['Grant_priv'] != 'Y') {
3839         $sql_query1 = 'REVOKE GRANT OPTION ON ' . $itemType . ' ' . $db_and_table
3840             . ' FROM \'' . $GLOBALS['dbi']->escapeString($username) . '\'@\''
3841             . $GLOBALS['dbi']->escapeString($hostname) . '\';';
3842     } else {
3843         $sql_query1 = '';
3844     }
3845
3846     // Should not do a GRANT USAGE for a table-specific privilege, it
3847     // causes problems later (cannot revoke it)
3848     if (! (strlen($tablename) > 0
3849         && 'USAGE' == implode('', PMA_extractPrivInfo()))
3850     ) {
3851         $sql_query2 = 'GRANT ' . join(', ', PMA_extractPrivInfo())
3852             . ' ON ' . $itemType . ' ' . $db_and_table
3853             . ' TO \'' . $GLOBALS['dbi']->escapeString($username) . '\'@\''
3854             . $GLOBALS['dbi']->escapeString($hostname) . '\'';
3855
3856         if (strlen($dbname) === 0) {
3857             // add REQUIRE clause
3858             $sql_query2 .= PMA_getRequireClause();
3859         }
3860
3861         if ((isset($_POST['Grant_priv']) && $_POST['Grant_priv'] == 'Y')
3862             || (strlen($dbname) === 0
3863             && (isset($_POST['max_questions']) || isset($_POST['max_connections'])
3864             || isset($_POST['max_updates'])
3865             || isset($_POST['max_user_connections'])))
3866         ) {
3867             $sql_query2 .= PMA_getWithClauseForAddUserAndUpdatePrivs();
3868         }
3869         $sql_query2 .= ';';
3870     }
3871     if (! $GLOBALS['dbi']->tryQuery($sql_query0)) {
3872         // This might fail when the executing user does not have
3873         // ALL PRIVILEGES himself.
3874         // See https://sourceforge.net/p/phpmyadmin/bugs/3270/
3875         $sql_query0 = '';
3876     }
3877     if (! empty($sql_query1) && ! $GLOBALS['dbi']->tryQuery($sql_query1)) {
3878         // this one may fail, too...
3879         $sql_query1 = '';
3880     }
3881     if (! empty($sql_query2)) {
3882         $GLOBALS['dbi']->query($sql_query2);
3883     } else {
3884         $sql_query2 = '';
3885     }
3886     $sql_query = $sql_query0 . ' ' . $sql_query1 . ' ' . $sql_query2;
3887     $message = Message::success(__('You have updated the privileges for %s.'));
3888     $message->addParam('\'' . $username . '\'@\'' . $hostname . '\'');
3889
3890     return array($sql_query, $message);
3891 }
3892
3893 /**
3894  * Get List of information: Changes / copies a user
3895  *
3896  * @return array
3897  */
3898 function PMA_getDataForChangeOrCopyUser()
3899 {
3900     $queries = null;
3901     $password = null;
3902
3903     if (isset($_REQUEST['change_copy'])) {
3904         $user_host_condition = ' WHERE `User` = '
3905             . "'" . $GLOBALS['dbi']->escapeString($_REQUEST['old_username']) . "'"
3906             . ' AND `Host` = '
3907             . "'" . $GLOBALS['dbi']->escapeString($_REQUEST['old_hostname']) . "';";
3908         $row = $GLOBALS['dbi']->fetchSingleRow(
3909             'SELECT * FROM `mysql`.`user` ' . $user_host_condition
3910         );
3911         if (! $row) {
3912             $response = Response::getInstance();
3913             $response->addHTML(
3914                 Message::notice(__('No user found.'))->getDisplay()
3915             );
3916             unset($_REQUEST['change_copy']);
3917         } else {
3918             extract($row, EXTR_OVERWRITE);
3919             foreach ($row as $key => $value) {
3920                 $GLOBALS[$key] = $value;
3921             }
3922             // Recent MySQL versions have the field "Password" in mysql.user,
3923             // so the previous extract creates $Password but this script
3924             // uses $password
3925             if (! isset($password) && isset($Password)) {
3926                 $password = $Password;
3927             }
3928             if (Util::getServerType() == 'MySQL'
3929                 && PMA_MYSQL_INT_VERSION >= 50606
3930                 && PMA_MYSQL_INT_VERSION < 50706
3931                 && ((isset($authentication_string)
3932                 && empty($password))
3933                 || (isset($plugin)
3934                 && $plugin == 'sha256_password'))
3935             ) {
3936                 $password = $authentication_string;
3937             }
3938
3939             if (Util::getServerType() == 'MariaDB'
3940                 && PMA_MYSQL_INT_VERSION >= 50500
3941                 && isset($authentication_string)
3942                 && empty($password)
3943             ) {
3944                 $password = $authentication_string;
3945             }
3946
3947             // Always use 'authentication_string' column
3948             // for MySQL 5.7.6+ since it does not have
3949             // the 'password' column at all
3950             if (Util::getServerType() == 'MySQL'
3951                 && PMA_MYSQL_INT_VERSION >= 50706
3952                 && isset($authentication_string)
3953             ) {
3954                 $password = $authentication_string;
3955             }
3956
3957             $queries = array();
3958         }
3959     }
3960
3961     return array($queries, $password);
3962 }
3963
3964 /**
3965  * Update Data for information: Deletes users
3966  *
3967  * @param array $queries queries array
3968  *
3969  * @return array
3970  */
3971 function PMA_getDataForDeleteUsers($queries)
3972 {
3973     if (isset($_REQUEST['change_copy'])) {
3974         $selected_usr = array(
3975             $_REQUEST['old_username'] . '&amp;#27;' . $_REQUEST['old_hostname']
3976         );
3977     } else {
3978         $selected_usr = $_REQUEST['selected_usr'];
3979         $queries = array();
3980     }
3981
3982     // this happens, was seen in https://reports.phpmyadmin.net/reports/view/17146
3983     if (! is_array($selected_usr)) {
3984         return array();
3985     }
3986
3987     foreach ($selected_usr as $each_user) {
3988         list($this_user, $this_host) = explode('&amp;#27;', $each_user);
3989         $queries[] = '# '
3990             . sprintf(
3991                 __('Deleting %s'),
3992                 '\'' . $this_user . '\'@\'' . $this_host . '\''
3993             )
3994             . ' ...';
3995         $queries[] = 'DROP USER \''
3996             . $GLOBALS['dbi']->escapeString($this_user)
3997             . '\'@\'' . $GLOBALS['dbi']->escapeString($this_host) . '\';';
3998         PMA_relationsCleanupUser($this_user);
3999
4000         if (isset($_REQUEST['drop_users_db'])) {
4001             $queries[] = 'DROP DATABASE IF EXISTS '
4002                 . Util::backquote($this_user) . ';';
4003             $GLOBALS['reload'] = true;
4004         }
4005     }
4006     return $queries;
4007 }
4008
4009 /**
4010  * update Message For Reload
4011  *
4012  * @return array
4013  */
4014 function PMA_updateMessageForReload()
4015 {
4016     $message = null;
4017     if (isset($_REQUEST['flush_privileges'])) {
4018         $sql_query = 'FLUSH PRIVILEGES;';
4019         $GLOBALS['dbi']->query($sql_query);
4020         $message = Message::success(
4021             __('The privileges were reloaded successfully.')
4022         );
4023     }
4024
4025     if (isset($_REQUEST['validate_username'])) {
4026         $message = Message::success();
4027     }
4028
4029     return $message;
4030 }
4031
4032 /**
4033  * update Data For Queries from queries_for_display
4034  *
4035  * @param array      $queries             queries array
4036  * @param array|null $queries_for_display queries array for display
4037  *
4038  * @return null
4039  */
4040 function PMA_getDataForQueries($queries, $queries_for_display)
4041 {
4042     $tmp_count = 0;
4043     foreach ($queries as $sql_query) {
4044         if ($sql_query{0} != '#') {
4045             $GLOBALS['dbi']->query($sql_query);
4046         }
4047         // when there is a query containing a hidden password, take it
4048         // instead of the real query sent
4049         if (isset($queries_for_display[$tmp_count])) {
4050             $queries[$tmp_count] = $queries_for_display[$tmp_count];
4051         }
4052         $tmp_count++;
4053     }
4054
4055     return $queries;
4056 }
4057
4058 /**
4059  * update Data for information: Adds a user
4060  *
4061  * @param string $dbname      db name
4062  * @param string $username    user name
4063  * @param string $hostname    host name
4064  * @param string $password    password
4065  * @param bool   $is_menuwork is_menuwork set?
4066  *
4067  * @return array
4068  */
4069 function PMA_addUser(
4070     $dbname, $username, $hostname,
4071     $password, $is_menuwork
4072 ) {
4073     $_add_user_error = false;
4074     $message = null;
4075     $queries = null;
4076     $queries_for_display = null;
4077     $sql_query = null;
4078
4079     if (!isset($_REQUEST['adduser_submit']) && !isset($_REQUEST['change_copy'])) {
4080         return array(
4081             $message, $queries, $queries_for_display, $sql_query, $_add_user_error
4082         );
4083     }
4084
4085     $sql_query = '';
4086     if ($_POST['pred_username'] == 'any') {
4087         $username = '';
4088     }
4089     switch ($_POST['pred_hostname']) {
4090     case 'any':
4091         $hostname = '%';
4092         break;
4093     case 'localhost':
4094         $hostname = 'localhost';
4095         break;
4096     case 'hosttable':
4097         $hostname = '';
4098         break;
4099     case 'thishost':
4100         $_user_name = $GLOBALS['dbi']->fetchValue('SELECT USER()');
4101         $hostname = mb_substr(
4102             $_user_name,
4103             (mb_strrpos($_user_name, '@') + 1)
4104         );
4105         unset($_user_name);
4106         break;
4107     }
4108     $sql = "SELECT '1' FROM `mysql`.`user`"
4109         . " WHERE `User` = '" . $GLOBALS['dbi']->escapeString($username) . "'"
4110         . " AND `Host` = '" . $GLOBALS['dbi']->escapeString($hostname) . "';";
4111     if ($GLOBALS['dbi']->fetchValue($sql) == 1) {
4112         $message = Message::error(__('The user %s already exists!'));
4113         $message->addParam('[em]\'' . $username . '\'@\'' . $hostname . '\'[/em]');
4114         $_REQUEST['adduser'] = true;
4115         $_add_user_error = true;
4116
4117         return array(
4118             $message,
4119             $queries,
4120             $queries_for_display,
4121             $sql_query,
4122             $_add_user_error
4123         );
4124     }
4125
4126     list(
4127         $create_user_real, $create_user_show, $real_sql_query, $sql_query,
4128         $password_set_real, $password_set_show
4129     ) = PMA_getSqlQueriesForDisplayAndAddUser(
4130         $username, $hostname, (isset($password) ? $password : '')
4131     );
4132
4133     if (empty($_REQUEST['change_copy'])) {
4134         $_error = false;
4135
4136         if (isset($create_user_real)) {
4137             if (!$GLOBALS['dbi']->tryQuery($create_user_real)) {
4138                 $_error = true;
4139             }
4140             if (isset($password_set_real) && !empty($password_set_real)
4141                 && isset($_REQUEST['authentication_plugin'])
4142             ) {
4143                 PMA_setProperPasswordHashing(
4144                     $_REQUEST['authentication_plugin']
4145                 );
4146                 if ($GLOBALS['dbi']->tryQuery($password_set_real)) {
4147                     $sql_query .= $password_set_show;
4148                 }
4149             }
4150             $sql_query = $create_user_show . $sql_query;
4151         }
4152
4153         list($sql_query, $message) = PMA_addUserAndCreateDatabase(
4154             $_error,
4155             $real_sql_query,
4156             $sql_query,
4157             $username,
4158             $hostname,
4159             isset($dbname) ? $dbname : null
4160         );
4161         if (!empty($_REQUEST['userGroup']) && $is_menuwork) {
4162             PMA_setUserGroup($GLOBALS['username'], $_REQUEST['userGroup']);
4163         }
4164
4165         return array(
4166             $message,
4167             $queries,
4168             $queries_for_display,
4169             $sql_query,
4170             $_add_user_error
4171         );
4172     }
4173
4174     // Copy the user group while copying a user
4175     $old_usergroup =
4176         isset($_REQUEST['old_usergroup']) ? $_REQUEST['old_usergroup'] : null;
4177     PMA_setUserGroup($_REQUEST['username'], $old_usergroup);
4178
4179     if (isset($create_user_real)) {
4180         $queries[] = $create_user_real;
4181     }
4182     $queries[] = $real_sql_query;
4183
4184     if (isset($password_set_real) && ! empty($password_set_real)
4185         && isset($_REQUEST['authentication_plugin'])
4186     ) {
4187         PMA_setProperPasswordHashing(
4188             $_REQUEST['authentication_plugin']
4189         );
4190
4191         $queries[] = $password_set_real;
4192     }
4193     // we put the query containing the hidden password in
4194     // $queries_for_display, at the same position occupied
4195     // by the real query in $queries
4196     $tmp_count = count($queries);
4197     if (isset($create_user_real)) {
4198         $queries_for_display[$tmp_count - 2] = $create_user_show;
4199     }
4200     if (isset($password_set_real) && ! empty($password_set_real)) {
4201         $queries_for_display[$tmp_count - 3] = $create_user_show;
4202         $queries_for_display[$tmp_count - 2] = $sql_query;
4203         $queries_for_display[$tmp_count - 1] = $password_set_show;
4204     } else {
4205         $queries_for_display[$tmp_count - 1] = $sql_query;
4206     }
4207
4208     return array(
4209         $message, $queries, $queries_for_display, $sql_query, $_add_user_error
4210     );
4211 }
4212
4213 /**
4214  * Sets proper value of `old_passwords` according to
4215  * the authentication plugin selected
4216  *
4217  * @param string $auth_plugin authentication plugin selected
4218  *
4219  * @return void
4220  */
4221 function PMA_setProperPasswordHashing($auth_plugin)
4222 {
4223     // Set the hashing method used by PASSWORD()
4224     // to be of type depending upon $authentication_plugin
4225     if ($auth_plugin == 'sha256_password') {
4226         $GLOBALS['dbi']->tryQuery('SET `old_passwords` = 2');
4227     } else if ($auth_plugin == 'mysql_old_password') {
4228         $GLOBALS['dbi']->tryQuery('SET `old_passwords` = 1');
4229     } else {
4230         $GLOBALS['dbi']->tryQuery('SET `old_passwords` = 0');
4231     }
4232 }
4233
4234 /**
4235  * Update DB information: DB, Table, isWildcard
4236  *
4237  * @return array
4238  */
4239 function PMA_getDataForDBInfo()
4240 {
4241     $username = null;
4242     $hostname = null;
4243     $dbname = null;
4244     $tablename = null;
4245     $routinename = null;
4246     $dbname_is_wildcard = null;
4247
4248     if (isset($_REQUEST['username'])) {
4249         $username = $_REQUEST['username'];
4250     }
4251     if (isset($_REQUEST['hostname'])) {
4252         $hostname = $_REQUEST['hostname'];
4253     }
4254     /**
4255      * Checks if a dropdown box has been used for selecting a database / table
4256      */
4257     if (PMA_isValid($_REQUEST['pred_tablename'])) {
4258         $tablename = $_REQUEST['pred_tablename'];
4259     } elseif (PMA_isValid($_REQUEST['tablename'])) {
4260         $tablename = $_REQUEST['tablename'];
4261     } else {
4262         unset($tablename);
4263     }
4264
4265     if (PMA_isValid($_REQUEST['pred_routinename'])) {
4266         $routinename = $_REQUEST['pred_routinename'];
4267     } elseif (PMA_isValid($_REQUEST['routinename'])) {
4268         $routinename = $_REQUEST['routinename'];
4269     } else {
4270         unset($routinename);
4271     }
4272
4273     if (isset($_REQUEST['pred_dbname'])) {
4274         $is_valid_pred_dbname = true;
4275         foreach ($_REQUEST['pred_dbname'] as $key => $db_name) {
4276             if (! PMA_isValid($db_name)) {
4277                 $is_valid_pred_dbname = false;
4278                 break;
4279             }
4280         }
4281     }
4282
4283     if (isset($_REQUEST['dbname'])) {
4284         $is_valid_dbname = true;
4285         if (is_array($_REQUEST['dbname'])) {
4286             foreach ($_REQUEST['dbname'] as $key => $db_name) {
4287                 if (! PMA_isValid($db_name)) {
4288                     $is_valid_dbname = false;
4289                     break;
4290                 }
4291             }
4292         } else {
4293             if (! PMA_isValid($_REQUEST['dbname'])) {
4294                 $is_valid_dbname = false;
4295             }
4296         }
4297     }
4298
4299     if (isset($is_valid_pred_dbname) && $is_valid_pred_dbname) {
4300         $dbname = $_REQUEST['pred_dbname'];
4301         // If dbname contains only one database.
4302         if (count($dbname) == 1) {
4303             $dbname = $dbname[0];
4304         }
4305     } elseif (isset($is_valid_dbname) && $is_valid_dbname) {
4306         $dbname = $_REQUEST['dbname'];
4307     } else {
4308         unset($dbname);
4309         unset($tablename);
4310     }
4311
4312     if (isset($dbname)) {
4313         if (is_array($dbname)) {
4314             $db_and_table = $dbname;
4315             foreach ($db_and_table as $key => $db_name) {
4316                 $db_and_table[$key] .= '.';
4317             }
4318         } else {
4319             $unescaped_db = Util::unescapeMysqlWildcards($dbname);
4320             $db_and_table = Util::backquote($unescaped_db) . '.';
4321         }
4322         if (isset($tablename)) {
4323             $db_and_table .= Util::backquote($tablename);
4324         } else {
4325             if (is_array($db_and_table)) {
4326                 foreach ($db_and_table as $key => $db_name) {
4327                     $db_and_table[$key] .= '*';
4328                 }
4329             } else {
4330                 $db_and_table .= '*';
4331             }
4332         }
4333     } else {
4334         $db_and_table = '*.*';
4335     }
4336
4337     // check if given $dbname is a wildcard or not
4338     if (isset($dbname)) {
4339         //if (preg_match('/\\\\(?:_|%)/i', $dbname)) {
4340         if (! is_array($dbname) && preg_match('/(?<!\\\\)(?:_|%)/i', $dbname)) {
4341             $dbname_is_wildcard = true;
4342         } else {
4343             $dbname_is_wildcard = false;
4344         }
4345     }
4346
4347     return array(
4348         $username, $hostname,
4349         isset($dbname)? $dbname : null,
4350         isset($tablename)? $tablename : null,
4351         isset($routinename) ? $routinename : null,
4352         $db_and_table,
4353         $dbname_is_wildcard,
4354     );
4355 }
4356
4357 /**
4358  * Get title and textarea for export user definition in Privileges
4359  *
4360  * @param string $username username
4361  * @param string $hostname host name
4362  *
4363  * @return array ($title, $export)
4364  */
4365 function PMA_getListForExportUserDefinition($username, $hostname)
4366 {
4367     $export = '<textarea class="export" cols="60" rows="15">';
4368
4369     if (isset($_REQUEST['selected_usr'])) {
4370         // export privileges for selected users
4371         $title = __('Privileges');
4372
4373         //For removing duplicate entries of users
4374         $_REQUEST['selected_usr'] = array_unique($_REQUEST['selected_usr']);
4375
4376         foreach ($_REQUEST['selected_usr'] as $export_user) {
4377             $export_username = mb_substr(
4378                 $export_user, 0, mb_strpos($export_user, '&')
4379             );
4380             $export_hostname = mb_substr(
4381                 $export_user, mb_strrpos($export_user, ';') + 1
4382             );
4383             $export .= '# '
4384                 . sprintf(
4385                     __('Privileges for %s'),
4386                     '`' . htmlspecialchars($export_username)
4387                     . '`@`' . htmlspecialchars($export_hostname) . '`'
4388                 )
4389                 . "\n\n";
4390             $export .= PMA_getGrants($export_username, $export_hostname) . "\n";
4391         }
4392     } else {
4393         // export privileges for a single user
4394         $title = __('User') . ' `' . htmlspecialchars($username)
4395             . '`@`' . htmlspecialchars($hostname) . '`';
4396         $export .= PMA_getGrants($username, $hostname);
4397     }
4398     // remove trailing whitespace
4399     $export = trim($export);
4400
4401     $export .= '</textarea>';
4402
4403     return array($title, $export);
4404 }
4405
4406 /**
4407  * Get HTML for display Add userfieldset
4408  *
4409  * @param string $db    the database
4410  * @param string $table the table name
4411  *
4412  * @return string html output
4413  */
4414 function PMA_getAddUserHtmlFieldset($db = '', $table = '')
4415 {
4416     if (!$GLOBALS['is_createuser']) {
4417         return '';
4418     }
4419     $rel_params = array();
4420     $url_params = array(
4421         'adduser' => 1
4422     );
4423     if (!empty($db)) {
4424         $url_params['dbname']
4425             = $rel_params['checkprivsdb']
4426                 = $db;
4427     }
4428     if (!empty($table)) {
4429         $url_params['tablename']
4430             = $rel_params['checkprivstable']
4431                 = $table;
4432     }
4433
4434     return Template::get('privileges/add_user_fieldset')
4435         ->render(
4436             array(
4437                 'url_params' => $url_params,
4438                 'rel_params' => $rel_params
4439             )
4440         );
4441 }
4442
4443 /**
4444  * Get HTML header for display User's properties
4445  *
4446  * @param boolean $dbname_is_wildcard whether database name is wildcard or not
4447  * @param string  $url_dbname         url database name that urlencode() string
4448  * @param string  $dbname             database name
4449  * @param string  $username           username
4450  * @param string  $hostname           host name
4451  * @param string  $entity_name        entity (table or routine) name
4452  * @param string  $entity_type        optional, type of entity ('table' or 'routine')
4453  *
4454  * @return string $html_output
4455  */
4456 function PMA_getHtmlHeaderForUserProperties(
4457     $dbname_is_wildcard, $url_dbname, $dbname,
4458     $username, $hostname, $entity_name, $entity_type='table'
4459 ) {
4460     $html_output = '<h2>' . "\n"
4461        . Util::getIcon('b_usredit.png')
4462        . __('Edit privileges:') . ' '
4463        . __('User account');
4464
4465     if (! empty($dbname)) {
4466         $html_output .= ' <i><a class="edit_user_anchor"'
4467             . ' href="server_privileges.php'
4468             . URL::getCommon(
4469                 array(
4470                     'username' => $username,
4471                     'hostname' => $hostname,
4472                     'dbname' => '',
4473                     'tablename' => '',
4474                 )
4475             )
4476             . '">\'' . htmlspecialchars($username)
4477             . '\'@\'' . htmlspecialchars($hostname)
4478             . '\'</a></i>' . "\n";
4479
4480         $html_output .= ' - ';
4481         $html_output .= ($dbname_is_wildcard
4482             || is_array($dbname) && count($dbname) > 1)
4483             ? __('Databases') : __('Database');
4484         if (! empty($entity_name) && $entity_type === 'table') {
4485             $html_output .= ' <i><a href="server_privileges.php'
4486                 . URL::getCommon(
4487                     array(
4488                         'username' => $username,
4489                         'hostname' => $hostname,
4490                         'dbname' => $url_dbname,
4491                         'tablename' => '',
4492                     )
4493                 )
4494                 . '">' . htmlspecialchars($dbname)
4495                 . '</a></i>';
4496
4497             $html_output .= ' - ' . __('Table')
4498                 . ' <i>' . htmlspecialchars($entity_name) . '</i>';
4499         } elseif (! empty($entity_name)) {
4500             $html_output .= ' <i><a href="server_privileges.php'
4501                 . URL::getCommon(
4502                     array(
4503                         'username' => $username,
4504                         'hostname' => $hostname,
4505                         'dbname' => $url_dbname,
4506                         'routinename' => '',
4507                     )
4508                 )
4509                 . '">' . htmlspecialchars($dbname)
4510                 . '</a></i>';
4511
4512             $html_output .= ' - ' . __('Routine')
4513                 . ' <i>' . htmlspecialchars($entity_name) . '</i>';
4514         } else {
4515             if (! is_array($dbname)) {
4516                 $dbname = array($dbname);
4517             }
4518             $html_output .= ' <i>'
4519                 . htmlspecialchars(implode(', ', $dbname))
4520                 . '</i>';
4521         }
4522
4523     } else {
4524         $html_output .= ' <i>\'' . htmlspecialchars($username)
4525             . '\'@\'' . htmlspecialchars($hostname)
4526             . '\'</i>' . "\n";
4527
4528     }
4529     $html_output .= '</h2>' . "\n";
4530     $cur_user = $GLOBALS['dbi']->getCurrentUser();
4531     $user = $username . '@' . $hostname;
4532     // Add a short notice for the user
4533     // to remind him that he is editing his own privileges
4534     if ($user === $cur_user) {
4535         $html_output .= Message::notice(
4536             __(
4537                 'Note: You are attempting to edit privileges of the '
4538                 . 'user with which you are currently logged in.'
4539             )
4540         )->getDisplay();
4541     }
4542     return $html_output;
4543 }
4544
4545 /**
4546  * Get HTML snippet for display user overview page
4547  *
4548  * @param string $pmaThemeImage a image source link
4549  * @param string $text_dir      text directory
4550  *
4551  * @return string $html_output
4552  */
4553 function PMA_getHtmlForUserOverview($pmaThemeImage, $text_dir)
4554 {
4555     $html_output = '<h2>' . "\n"
4556        . Util::getIcon('b_usrlist.png')
4557        . __('User accounts overview') . "\n"
4558        . '</h2>' . "\n";
4559
4560     $password_column = 'Password';
4561     $server_type = Util::getServerType();
4562     if (($server_type == 'MySQL' || $server_type == 'Percona Server')
4563         && PMA_MYSQL_INT_VERSION >= 50706
4564     ) {
4565         $password_column = 'authentication_string';
4566     }
4567     // $sql_query is for the initial-filtered,
4568     // $sql_query_all is for counting the total no. of users
4569
4570     $sql_query = $sql_query_all = 'SELECT *,' .
4571         " IF(`" . $password_column . "` = _latin1 '', 'N', 'Y') AS 'Password'" .
4572         ' FROM `mysql`.`user`';
4573
4574     $sql_query .= (isset($_REQUEST['initial'])
4575         ? PMA_rangeOfUsers($_REQUEST['initial'])
4576         : '');
4577
4578     $sql_query .= ' ORDER BY `User` ASC, `Host` ASC;';
4579     $sql_query_all .= ' ;';
4580
4581     $res = $GLOBALS['dbi']->tryQuery(
4582         $sql_query, null, PMA\libraries\DatabaseInterface::QUERY_STORE
4583     );
4584     $res_all = $GLOBALS['dbi']->tryQuery(
4585         $sql_query_all, null, PMA\libraries\DatabaseInterface::QUERY_STORE
4586     );
4587
4588     if (! $res) {
4589         // the query failed! This may have two reasons:
4590         // - the user does not have enough privileges
4591         // - the privilege tables use a structure of an earlier version.
4592         // so let's try a more simple query
4593
4594         $GLOBALS['dbi']->freeResult($res);
4595         $GLOBALS['dbi']->freeResult($res_all);
4596         $sql_query = 'SELECT * FROM `mysql`.`user`';
4597         $res = $GLOBALS['dbi']->tryQuery(
4598             $sql_query, null, PMA\libraries\DatabaseInterface::QUERY_STORE
4599         );
4600
4601         if (! $res) {
4602             $html_output .= PMA_getHtmlForViewUsersError();
4603             $html_output .= PMA_getAddUserHtmlFieldset();
4604         } else {
4605             // This message is hardcoded because I will replace it by
4606             // a automatic repair feature soon.
4607             $raw = 'Your privilege table structure seems to be older than'
4608                 . ' this MySQL version!<br />'
4609                 . 'Please run the <code>mysql_upgrade</code> command'
4610                 . '(<code>mysql_fix_privilege_tables</code> on older systems)'
4611                 . ' that should be included in your MySQL server distribution'
4612                 . ' to solve this problem!';
4613             $html_output .= Message::rawError($raw)->getDisplay();
4614         }
4615         $GLOBALS['dbi']->freeResult($res);
4616     } else {
4617         $db_rights = PMA_getDbRightsForUserOverview();
4618         // for all initials, even non A-Z
4619         $array_initials = array();
4620
4621         foreach ($db_rights as $right) {
4622             foreach ($right as $account) {
4623                 if (empty($account['User']) && $account['Host'] == 'localhost') {
4624                     $html_output .= Message::notice(
4625                         __(
4626                             'A user account allowing any user from localhost to '
4627                             . 'connect is present. This will prevent other users '
4628                             . 'from connecting if the host part of their account '
4629                             . 'allows a connection from any (%) host.'
4630                         )
4631                         . Util::showMySQLDocu('problems-connecting')
4632                     )->getDisplay();
4633                     break 2;
4634                 }
4635             }
4636         }
4637
4638         /**
4639          * Displays the initials
4640          * Also not necessary if there is less than 20 privileges
4641          */
4642         if ($GLOBALS['dbi']->numRows($res_all) > 20) {
4643             $html_output .= PMA_getHtmlForInitials($array_initials);
4644         }
4645
4646         /**
4647         * Display the user overview
4648         * (if less than 50 users, display them immediately)
4649         */
4650         if (isset($_REQUEST['initial'])
4651             || isset($_REQUEST['showall'])
4652             || $GLOBALS['dbi']->numRows($res) < 50
4653         ) {
4654             $html_output .= PMA_getUsersOverview(
4655                 $res, $db_rights, $pmaThemeImage, $text_dir
4656             );
4657         } else {
4658             $html_output .= PMA_getAddUserHtmlFieldset();
4659         } // end if (display overview)
4660
4661         $response = Response::getInstance();
4662         if (! $response->isAjax()
4663             || ! empty($_REQUEST['ajax_page_request'])
4664         ) {
4665             if ($GLOBALS['is_reload_priv']) {
4666                 $flushnote = new Message(
4667                     __(
4668                         'Note: phpMyAdmin gets the users\' privileges directly '
4669                         . 'from MySQL\'s privilege tables. The content of these '
4670                         . 'tables may differ from the privileges the server uses, '
4671                         . 'if they have been changed manually. In this case, '
4672                         . 'you should %sreload the privileges%s before you continue.'
4673                     ),
4674                     Message::NOTICE
4675                 );
4676                 $flushnote->addParamHtml(
4677                     '<a href="server_privileges.php'
4678                     . URL::getCommon(array('flush_privileges' => 1))
4679                     . '" id="reload_privileges_anchor">'
4680                 );
4681                 $flushnote->addParamHtml('</a>');
4682             } else {
4683                 $flushnote = new Message(
4684                     __(
4685                         'Note: phpMyAdmin gets the users\' privileges directly '
4686                         . 'from MySQL\'s privilege tables. The content of these '
4687                         . 'tables may differ from the privileges the server uses, '
4688                         . 'if they have been changed manually. In this case, '
4689                         . 'the privileges have to be reloaded but currently, you '
4690                         . 'don\'t have the RELOAD privilege.'
4691                     )
4692                     . Util::showMySQLDocu(
4693                         'privileges-provided',
4694                         false,
4695                         'priv_reload'
4696                     ),
4697                     Message::NOTICE
4698                 );
4699             }
4700             $html_output .= $flushnote->getDisplay();
4701         }
4702     }
4703
4704     return $html_output;
4705 }
4706
4707 /**
4708  * Get HTML snippet for display user properties
4709  *
4710  * @param boolean $dbname_is_wildcard whether database name is wildcard or not
4711  * @param string  $url_dbname         url database name that urlencode() string
4712  * @param string  $username           username
4713  * @param string  $hostname           host name
4714  * @param string  $dbname             database name
4715  * @param string  $tablename          table name
4716  *
4717  * @return string $html_output
4718  */
4719 function PMA_getHtmlForUserProperties($dbname_is_wildcard,$url_dbname,
4720     $username, $hostname, $dbname, $tablename
4721 ) {
4722     $html_output  = '<div id="edit_user_dialog">';
4723     $html_output .= PMA_getHtmlHeaderForUserProperties(
4724         $dbname_is_wildcard, $url_dbname, $dbname, $username, $hostname,
4725         $tablename, 'table'
4726     );
4727
4728     $sql = "SELECT '1' FROM `mysql`.`user`"
4729         . " WHERE `User` = '" . $GLOBALS['dbi']->escapeString($username) . "'"
4730         . " AND `Host` = '" . $GLOBALS['dbi']->escapeString($hostname) . "';";
4731
4732     $user_does_not_exists = (bool) ! $GLOBALS['dbi']->fetchValue($sql);
4733
4734     if ($user_does_not_exists) {
4735         $html_output .= Message::error(
4736             __('The selected user was not found in the privilege table.')
4737         )->getDisplay();
4738         $html_output .= PMA_getHtmlForLoginInformationFields();
4739     }
4740
4741     $_params = array(
4742         'username' => $username,
4743         'hostname' => $hostname,
4744     );
4745     if (! is_array($dbname) && strlen($dbname) > 0) {
4746         $_params['dbname'] = $dbname;
4747         if (strlen($tablename) > 0) {
4748             $_params['tablename'] = $tablename;
4749         }
4750     } else {
4751         $_params['dbname'] = $dbname;
4752     }
4753
4754     $html_output .= '<form class="submenu-item" name="usersForm" '
4755         . 'id="addUsersForm" action="server_privileges.php" method="post">' . "\n";
4756     $html_output .= URL::getHiddenInputs($_params);
4757     $html_output .= PMA_getHtmlToDisplayPrivilegesTable(
4758         // If $dbname is an array, pass any one db as all have same privs.
4759         PMA_ifSetOr($dbname, (is_array($dbname)) ? $dbname[0] : '*', 'length'),
4760         PMA_ifSetOr($tablename, '*', 'length')
4761     );
4762
4763     $html_output .= '</form>' . "\n";
4764
4765     if (! is_array($dbname) && strlen($tablename) === 0
4766         && empty($dbname_is_wildcard)
4767     ) {
4768         // no table name was given, display all table specific rights
4769         // but only if $dbname contains no wildcards
4770         if (strlen($dbname) === 0) {
4771             $html_output .= PMA_getHtmlForAllTableSpecificRights(
4772                 $username, $hostname, 'database'
4773             );
4774         } else {
4775             // unescape wildcards in dbname at table level
4776             $unescaped_db = Util::unescapeMysqlWildcards($dbname);
4777
4778             $html_output .= PMA_getHtmlForAllTableSpecificRights(
4779                 $username, $hostname, 'table', $unescaped_db
4780             );
4781             $html_output .= PMA_getHtmlForAllTableSpecificRights(
4782                 $username, $hostname, 'routine', $unescaped_db
4783             );
4784         }
4785     }
4786
4787     // Provide a line with links to the relevant database and table
4788     if (! is_array($dbname) && strlen($dbname) > 0 && empty($dbname_is_wildcard)) {
4789         $html_output .= PMA_getLinkToDbAndTable($url_dbname, $dbname, $tablename);
4790
4791     }
4792
4793     if (! is_array($dbname) && strlen($dbname) === 0 && ! $user_does_not_exists) {
4794         //change login information
4795         $html_output .= PMA_getHtmlForChangePassword(
4796             'edit_other',
4797             $username,
4798             $hostname
4799         );
4800         $html_output .= PMA_getChangeLoginInformationHtmlForm($username, $hostname);
4801     }
4802     $html_output .= '</div>';
4803
4804     return $html_output;
4805 }
4806
4807 /**
4808  * Get queries for Table privileges to change or copy user
4809  *
4810  * @param string $user_host_condition user host condition to
4811  *                                    select relevant table privileges
4812  * @param array  $queries             queries array
4813  * @param string $username            username
4814  * @param string $hostname            host name
4815  *
4816  * @return array  $queries
4817  */
4818 function PMA_getTablePrivsQueriesForChangeOrCopyUser($user_host_condition,
4819     $queries, $username, $hostname
4820 ) {
4821     $res = $GLOBALS['dbi']->query(
4822         'SELECT `Db`, `Table_name`, `Table_priv` FROM `mysql`.`tables_priv`'
4823         . $user_host_condition,
4824         $GLOBALS['userlink'],
4825         PMA\libraries\DatabaseInterface::QUERY_STORE
4826     );
4827     while ($row = $GLOBALS['dbi']->fetchAssoc($res)) {
4828
4829         $res2 = $GLOBALS['dbi']->query(
4830             'SELECT `Column_name`, `Column_priv`'
4831             . ' FROM `mysql`.`columns_priv`'
4832             . ' WHERE `User`'
4833             . ' = \'' . $GLOBALS['dbi']->escapeString($_REQUEST['old_username']) . "'"
4834             . ' AND `Host`'
4835             . ' = \'' . $GLOBALS['dbi']->escapeString($_REQUEST['old_username']) . '\''
4836             . ' AND `Db`'
4837             . ' = \'' . $GLOBALS['dbi']->escapeString($row['Db']) . "'"
4838             . ' AND `Table_name`'
4839             . ' = \'' . $GLOBALS['dbi']->escapeString($row['Table_name']) . "'"
4840             . ';',
4841             null,
4842             PMA\libraries\DatabaseInterface::QUERY_STORE
4843         );
4844
4845         $tmp_privs1 = PMA_extractPrivInfo($row);
4846         $tmp_privs2 = array(
4847             'Select' => array(),
4848             'Insert' => array(),
4849             'Update' => array(),
4850             'References' => array()
4851         );
4852
4853         while ($row2 = $GLOBALS['dbi']->fetchAssoc($res2)) {
4854             $tmp_array = explode(',', $row2['Column_priv']);
4855             if (in_array('Select', $tmp_array)) {
4856                 $tmp_privs2['Select'][] = $row2['Column_name'];
4857             }
4858             if (in_array('Insert', $tmp_array)) {
4859                 $tmp_privs2['Insert'][] = $row2['Column_name'];
4860             }
4861             if (in_array('Update', $tmp_array)) {
4862                 $tmp_privs2['Update'][] = $row2['Column_name'];
4863             }
4864             if (in_array('References', $tmp_array)) {
4865                 $tmp_privs2['References'][] = $row2['Column_name'];
4866             }
4867         }
4868         if (count($tmp_privs2['Select']) > 0 && ! in_array('SELECT', $tmp_privs1)) {
4869             $tmp_privs1[] = 'SELECT (`' . join('`, `', $tmp_privs2['Select']) . '`)';
4870         }
4871         if (count($tmp_privs2['Insert']) > 0 && ! in_array('INSERT', $tmp_privs1)) {
4872             $tmp_privs1[] = 'INSERT (`' . join('`, `', $tmp_privs2['Insert']) . '`)';
4873         }
4874         if (count($tmp_privs2['Update']) > 0 && ! in_array('UPDATE', $tmp_privs1)) {
4875             $tmp_privs1[] = 'UPDATE (`' . join('`, `', $tmp_privs2['Update']) . '`)';
4876         }
4877         if (count($tmp_privs2['References']) > 0
4878             && ! in_array('REFERENCES', $tmp_privs1)
4879         ) {
4880             $tmp_privs1[]
4881                 = 'REFERENCES (`' . join('`, `', $tmp_privs2['References']) . '`)';
4882         }
4883
4884         $queries[] = 'GRANT ' . join(', ', $tmp_privs1)
4885             . ' ON ' . Util::backquote($row['Db']) . '.'
4886             . Util::backquote($row['Table_name'])
4887             . ' TO \'' . $GLOBALS['dbi']->escapeString($username)
4888             . '\'@\'' . $GLOBALS['dbi']->escapeString($hostname) . '\''
4889             . (in_array('Grant', explode(',', $row['Table_priv']))
4890             ? ' WITH GRANT OPTION;'
4891             : ';');
4892     }
4893     return $queries;
4894 }
4895
4896 /**
4897  * Get queries for database specific privileges for change or copy user
4898  *
4899  * @param array  $queries  queries array with string
4900  * @param string $username username
4901  * @param string $hostname host name
4902  *
4903  * @return array $queries
4904  */
4905 function PMA_getDbSpecificPrivsQueriesForChangeOrCopyUser(
4906     $queries, $username, $hostname
4907 ) {
4908     $user_host_condition = ' WHERE `User`'
4909         . ' = \'' . $GLOBALS['dbi']->escapeString($_REQUEST['old_username']) . "'"
4910         . ' AND `Host`'
4911         . ' = \'' . $GLOBALS['dbi']->escapeString($_REQUEST['old_hostname']) . '\';';
4912
4913     $res = $GLOBALS['dbi']->query(
4914         'SELECT * FROM `mysql`.`db`' . $user_host_condition
4915     );
4916
4917     while ($row = $GLOBALS['dbi']->fetchAssoc($res)) {
4918         $queries[] = 'GRANT ' . join(', ', PMA_extractPrivInfo($row))
4919             . ' ON ' . Util::backquote($row['Db']) . '.*'
4920             . ' TO \'' . $GLOBALS['dbi']->escapeString($username)
4921             . '\'@\'' . $GLOBALS['dbi']->escapeString($hostname) . '\''
4922             . ($row['Grant_priv'] == 'Y' ? ' WITH GRANT OPTION;' : ';');
4923     }
4924     $GLOBALS['dbi']->freeResult($res);
4925
4926     $queries = PMA_getTablePrivsQueriesForChangeOrCopyUser(
4927         $user_host_condition, $queries, $username, $hostname
4928     );
4929
4930     return $queries;
4931 }
4932
4933 /**
4934  * Prepares queries for adding users and
4935  * also create database and return query and message
4936  *
4937  * @param boolean $_error         whether user create or not
4938  * @param string  $real_sql_query SQL query for add a user
4939  * @param string  $sql_query      SQL query to be displayed
4940  * @param string  $username       username
4941  * @param string  $hostname       host name
4942  * @param string  $dbname         database name
4943  *
4944  * @return array  $sql_query, $message
4945  */
4946 function PMA_addUserAndCreateDatabase($_error, $real_sql_query, $sql_query,
4947     $username, $hostname, $dbname
4948 ) {
4949     if ($_error || (!empty($real_sql_query)
4950         && !$GLOBALS['dbi']->tryQuery($real_sql_query))
4951     ) {
4952         $_REQUEST['createdb-1'] = $_REQUEST['createdb-2']
4953             = $_REQUEST['createdb-3'] = null;
4954         $message = Message::rawError($GLOBALS['dbi']->getError());
4955     } else {
4956         $message = Message::success(__('You have added a new user.'));
4957     }
4958
4959     if (isset($_REQUEST['createdb-1'])) {
4960         // Create database with same name and grant all privileges
4961         $q = 'CREATE DATABASE IF NOT EXISTS '
4962             . Util::backquote(
4963                 $GLOBALS['dbi']->escapeString($username)
4964             ) . ';';
4965         $sql_query .= $q;
4966         if (! $GLOBALS['dbi']->tryQuery($q)) {
4967             $message = Message::rawError($GLOBALS['dbi']->getError());
4968         }
4969
4970         /**
4971          * Reload the navigation
4972          */
4973         $GLOBALS['reload'] = true;
4974         $GLOBALS['db'] = $username;
4975
4976         $q = 'GRANT ALL PRIVILEGES ON '
4977             . Util::backquote(
4978                 Util::escapeMysqlWildcards(
4979                     $GLOBALS['dbi']->escapeString($username)
4980                 )
4981             ) . '.* TO \''
4982             . $GLOBALS['dbi']->escapeString($username)
4983             . '\'@\'' . $GLOBALS['dbi']->escapeString($hostname) . '\';';
4984         $sql_query .= $q;
4985         if (! $GLOBALS['dbi']->tryQuery($q)) {
4986             $message = Message::rawError($GLOBALS['dbi']->getError());
4987         }
4988     }
4989
4990     if (isset($_REQUEST['createdb-2'])) {
4991         // Grant all privileges on wildcard name (username\_%)
4992         $q = 'GRANT ALL PRIVILEGES ON '
4993             . Util::backquote(
4994                 Util::escapeMysqlWildcards(
4995                     $GLOBALS['dbi']->escapeString($username)
4996                 ) . '\_%'
4997             ) . '.* TO \''
4998             . $GLOBALS['dbi']->escapeString($username)
4999             . '\'@\'' . $GLOBALS['dbi']->escapeString($hostname) . '\';';
5000         $sql_query .= $q;
5001         if (! $GLOBALS['dbi']->tryQuery($q)) {
5002             $message = Message::rawError($GLOBALS['dbi']->getError());
5003         }
5004     }
5005
5006     if (isset($_REQUEST['createdb-3'])) {
5007         // Grant all privileges on the specified database to the new user
5008         $q = 'GRANT ALL PRIVILEGES ON '
5009         . Util::backquote(
5010             $GLOBALS['dbi']->escapeString($dbname)
5011         ) . '.* TO \''
5012         . $GLOBALS['dbi']->escapeString($username)
5013         . '\'@\'' . $GLOBALS['dbi']->escapeString($hostname) . '\';';
5014         $sql_query .= $q;
5015         if (! $GLOBALS['dbi']->tryQuery($q)) {
5016             $message = Message::rawError($GLOBALS['dbi']->getError());
5017         }
5018     }
5019     return array($sql_query, $message);
5020 }
5021
5022 /**
5023  * Get the hashed string for password
5024  *
5025  * @param string $password password
5026  *
5027  * @return string $hashedPassword
5028  */
5029 function PMA_getHashedPassword($password)
5030 {
5031     $result = $GLOBALS['dbi']->fetchSingleRow(
5032         "SELECT PASSWORD('" . $password . "') AS `password`;"
5033     );
5034
5035     $hashedPassword = $result['password'];
5036
5037     return $hashedPassword;
5038 }
5039
5040 /**
5041  * Check if MariaDB's 'simple_password_check'
5042  * OR 'cracklib_password_check' is ACTIVE
5043  *
5044  * @return boolean if atleast one of the plugins is ACTIVE
5045  */
5046 function PMA_checkIfMariaDBPwdCheckPluginActive()
5047 {
5048     if (!(Util::getServerType() == 'MariaDB' && PMA_MYSQL_INT_VERSION >= 100002)) {
5049         return false;
5050     }
5051
5052     $result = $GLOBALS['dbi']->query(
5053         'SHOW PLUGINS SONAME LIKE \'%_password_check%\''
5054     );
5055
5056     while ($row = $GLOBALS['dbi']->fetchAssoc($result)) {
5057         if ($row['Status'] === 'ACTIVE') {
5058             return true;
5059         }
5060     }
5061
5062     return false;
5063 }
5064
5065
5066 /**
5067  * Get SQL queries for Display and Add user
5068  *
5069  * @param string $username username
5070  * @param string $hostname host name
5071  * @param string $password password
5072  *
5073  * @return array ($create_user_real, $create_user_show,$real_sql_query, $sql_query
5074  *                $password_set_real, $password_set_show)
5075  */
5076 function PMA_getSqlQueriesForDisplayAndAddUser($username, $hostname, $password)
5077 {
5078     $slashedUsername = $GLOBALS['dbi']->escapeString($username);
5079     $slashedHostname = $GLOBALS['dbi']->escapeString($hostname);
5080     $slashedPassword = $GLOBALS['dbi']->escapeString($password);
5081     $serverType = Util::getServerType();
5082
5083     $create_user_stmt = sprintf(
5084         'CREATE USER \'%s\'@\'%s\'',
5085         $slashedUsername,
5086         $slashedHostname
5087     );
5088     $isMariaDBPwdPluginActive = PMA_checkIfMariaDBPwdCheckPluginActive();
5089
5090     // See https://github.com/phpmyadmin/phpmyadmin/pull/11560#issuecomment-147158219
5091     // for details regarding details of syntax usage for various versions
5092
5093     // 'IDENTIFIED WITH auth_plugin'
5094     // is supported by MySQL 5.5.7+
5095     if (($serverType == 'MySQL' || $serverType == 'Percona Server')
5096         && PMA_MYSQL_INT_VERSION >= 50507
5097         && isset($_REQUEST['authentication_plugin'])
5098     ) {
5099         $create_user_stmt .= ' IDENTIFIED WITH '
5100             . $_REQUEST['authentication_plugin'];
5101     }
5102
5103     // 'IDENTIFIED VIA auth_plugin'
5104     // is supported by MariaDB 5.2+
5105     if ($serverType == 'MariaDB'
5106         && PMA_MYSQL_INT_VERSION >= 50200
5107         && isset($_REQUEST['authentication_plugin'])
5108         && ! $isMariaDBPwdPluginActive
5109     ) {
5110         $create_user_stmt .= ' IDENTIFIED VIA '
5111             . $_REQUEST['authentication_plugin'];
5112     }
5113
5114     $create_user_real = $create_user_show = $create_user_stmt;
5115
5116     $password_set_stmt = 'SET PASSWORD FOR \'%s\'@\'%s\' = \'%s\'';
5117     $password_set_show = sprintf(
5118         $password_set_stmt,
5119         $slashedUsername,
5120         $slashedHostname,
5121         '***'
5122     );
5123
5124     $sql_query_stmt = sprintf(
5125         'GRANT %s ON *.* TO \'%s\'@\'%s\'',
5126         join(', ', PMA_extractPrivInfo()),
5127         $slashedUsername,
5128         $slashedHostname
5129     );
5130     $real_sql_query = $sql_query = $sql_query_stmt;
5131
5132     // Set the proper hashing method
5133     if (isset($_REQUEST['authentication_plugin'])) {
5134         PMA_setProperPasswordHashing(
5135             $_REQUEST['authentication_plugin']
5136         );
5137     }
5138
5139     // Use 'CREATE USER ... WITH ... AS ..' syntax for
5140     // newer MySQL versions
5141     // and 'CREATE USER ... VIA .. USING ..' syntax for
5142     // newer MariaDB versions
5143     if ((($serverType == 'MySQL' || $serverType == 'Percona Server')
5144         && PMA_MYSQL_INT_VERSION >= 50706)
5145         || ($serverType == 'MariaDB'
5146         && PMA_MYSQL_INT_VERSION >= 50200)
5147     ) {
5148         $password_set_real = null;
5149
5150         // Required for binding '%' with '%s'
5151         $create_user_stmt = str_replace(
5152             '%', '%%', $create_user_stmt
5153         );
5154
5155         // MariaDB uses 'USING' whereas MySQL uses 'AS'
5156         // but MariaDB with validation plugin needs cleartext password
5157         if ($serverType == 'MariaDB'
5158             && ! $isMariaDBPwdPluginActive
5159         ) {
5160             $create_user_stmt .= ' USING \'%s\'';
5161         } elseif ($serverType == 'MariaDB') {
5162             $create_user_stmt .= ' IDENTIFIED BY \'%s\'';
5163         } else {
5164             $create_user_stmt .= ' AS \'%s\'';
5165         }
5166
5167         if ($_POST['pred_password'] == 'keep') {
5168             $create_user_real = sprintf(
5169                 $create_user_stmt,
5170                 $slashedPassword
5171             );
5172             $create_user_show = sprintf(
5173                 $create_user_stmt,
5174                 '***'
5175             );
5176         } else if ($_POST['pred_password'] == 'none') {
5177             $create_user_real = sprintf(
5178                 $create_user_stmt,
5179                 null
5180             );
5181             $create_user_show = sprintf(
5182                 $create_user_stmt,
5183                 '***'
5184             );
5185         } else {
5186             if (! ($serverType == 'MariaDB'
5187                 && $isMariaDBPwdPluginActive)
5188             ) {
5189                 $hashedPassword = PMA_getHashedPassword($_POST['pma_pw']);
5190             } else {
5191                 // MariaDB with validation plugin needs cleartext password
5192                 $hashedPassword = $_POST['pma_pw'];
5193             }
5194             $create_user_real = sprintf(
5195                 $create_user_stmt,
5196                 $hashedPassword
5197             );
5198             $create_user_show = sprintf(
5199                 $create_user_stmt,
5200                 '***'
5201             );
5202         }
5203     } else {
5204         // Use 'SET PASSWORD' syntax for pre-5.7.6 MySQL versions
5205         // and pre-5.2.0 MariaDB versions
5206         if ($_POST['pred_password'] == 'keep') {
5207             $password_set_real = sprintf(
5208                 $password_set_stmt,
5209                 $slashedUsername,
5210                 $slashedHostname,
5211                 $slashedPassword
5212             );
5213         } else if ($_POST['pred_password'] == 'none') {
5214             $password_set_real = sprintf(
5215                 $password_set_stmt,
5216                 $slashedUsername,
5217                 $slashedHostname,
5218                 null
5219             );
5220         } else {
5221             $hashedPassword = PMA_getHashedPassword($_POST['pma_pw']);
5222             $password_set_real = sprintf(
5223                 $password_set_stmt,
5224                 $slashedUsername,
5225                 $slashedHostname,
5226                 $hashedPassword
5227             );
5228         }
5229     }
5230
5231     // add REQUIRE clause
5232     $require_clause = PMA_getRequireClause();
5233     $real_sql_query .= $require_clause;
5234     $sql_query .= $require_clause;
5235
5236     $with_clause = PMA_getWithClauseForAddUserAndUpdatePrivs();
5237     $real_sql_query .= $with_clause;
5238     $sql_query .= $with_clause;
5239
5240     if (isset($create_user_real)) {
5241         $create_user_real .= ';';
5242         $create_user_show .= ';';
5243     }
5244     $real_sql_query .= ';';
5245     $sql_query .= ';';
5246     // No Global GRANT_OPTION privilege
5247     if (!$GLOBALS['is_grantuser']) {
5248         $real_sql_query = '';
5249         $sql_query = '';
5250     }
5251
5252     // Use 'SET PASSWORD' for pre-5.7.6 MySQL versions
5253     // and pre-5.2.0 MariaDB
5254     if (($serverType == 'MySQL'
5255         && PMA_MYSQL_INT_VERSION >= 50706)
5256         || ($serverType == 'MariaDB'
5257         && PMA_MYSQL_INT_VERSION >= 50200)
5258     ) {
5259         $password_set_real = null;
5260         $password_set_show = null;
5261     } else {
5262         $password_set_real .= ";";
5263         $password_set_show .= ";";
5264     }
5265
5266     return array($create_user_real,
5267         $create_user_show,
5268         $real_sql_query,
5269         $sql_query,
5270         $password_set_real,
5271         $password_set_show
5272     );
5273 }
5274
5275 /**
5276  * Returns the type ('PROCEDURE' or 'FUNCTION') of the routine
5277  *
5278  * @param string $dbname      database
5279  * @param string $routineName routine
5280  *
5281  * @return string type
5282  */
5283 function PMA_getRoutineType($dbname, $routineName)
5284 {
5285     $routineData = $GLOBALS['dbi']->getRoutines($dbname);
5286
5287     foreach ($routineData as $routine) {
5288         if ($routine['name'] === $routineName) {
5289             return $routine['type'];
5290         }
5291     }
5292     return '';
5293 }