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