1<?php 2/** 3 * Matomo - free/libre analytics platform 4 * 5 * @link https://matomo.org 6 * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later 7 * 8 */ 9namespace Piwik\Plugins\UsersManager; 10 11use Piwik\Auth\Password; 12use Piwik\Common; 13use Piwik\Config; 14use Piwik\Date; 15use Piwik\Db; 16use Piwik\Option; 17use Piwik\Piwik; 18use Piwik\Plugins\SitesManager\SitesManager; 19use Piwik\Plugins\UsersManager\Sql\SiteAccessFilter; 20use Piwik\Plugins\UsersManager\Sql\UserTableFilter; 21use Piwik\SettingsPiwik; 22use Piwik\SettingsServer; 23use Piwik\Validators\BaseValidator; 24use Piwik\Validators\CharacterLength; 25use Piwik\Validators\NotEmpty; 26 27/** 28 * The UsersManager API lets you Manage Users and their permissions to access specific websites. 29 * 30 * You can create users via "addUser", update existing users via "updateUser" and delete users via "deleteUser". 31 * There are many ways to list users based on their login "getUser" and "getUsers", their email "getUserByEmail", 32 * or which users have permission (view or admin) to access the specified websites "getUsersWithSiteAccess". 33 * 34 * Existing Permissions are listed given a login via "getSitesAccessFromUser", or a website ID via "getUsersAccessFromSite", 35 * or you can list all users and websites for a given permission via "getUsersSitesFromAccess". Permissions are set and updated 36 * via the method "setUserAccess". 37 * See also the documentation about <a href='http://piwik.org/docs/manage-users/' rel='noreferrer' target='_blank'>Managing Users</a> in Piwik. 38 */ 39class Model 40{ 41 const MAX_LENGTH_TOKEN_DESCRIPTION = 100; 42 const TOKEN_HASH_ALGO = 'sha512'; 43 44 private static $rawPrefix = 'user'; 45 private $userTable; 46 private $tokenTable; 47 48 /** 49 * @var Password 50 */ 51 private $passwordHelper; 52 53 public function __construct() 54 { 55 $this->passwordHelper = new Password(); 56 $this->userTable = Common::prefixTable(self::$rawPrefix); 57 $this->tokenTable = Common::prefixTable('user_token_auth'); 58 } 59 60 /** 61 * Returns the list of all the users 62 * 63 * @param string[] $userLogins List of users to select. If empty, will return all users 64 * @return array the list of all the users 65 */ 66 public function getUsers(array $userLogins) 67 { 68 $where = ''; 69 $bind = array(); 70 71 if (!empty($userLogins)) { 72 $where = 'WHERE login IN (' . Common::getSqlStringFieldsArray($userLogins) . ')'; 73 $bind = $userLogins; 74 } 75 76 $db = $this->getDb(); 77 $users = $db->fetchAll("SELECT * FROM " . $this->userTable . " 78 $where 79 ORDER BY login ASC", $bind); 80 81 return $users; 82 } 83 84 /** 85 * Returns the list of all the users login 86 * 87 * @return array the list of all the users login 88 */ 89 public function getUsersLogin() 90 { 91 $db = $this->getDb(); 92 $users = $db->fetchAll("SELECT login FROM " . $this->userTable . " ORDER BY login ASC"); 93 94 $return = array(); 95 foreach ($users as $login) { 96 $return[] = $login['login']; 97 } 98 99 return $return; 100 } 101 102 public function getUsersSitesFromAccess($access) 103 { 104 $db = $this->getDb(); 105 $users = $db->fetchAll("SELECT login,idsite FROM " . Common::prefixTable("access") 106 . " WHERE access = ? 107 ORDER BY login, idsite", $access); 108 109 $return = array(); 110 foreach ($users as $user) { 111 $return[$user['login']][] = $user['idsite']; 112 } 113 114 return $return; 115 } 116 117 public function getUsersAccessFromSite($idSite) 118 { 119 $db = $this->getDb(); 120 $users = $db->fetchAll("SELECT login,access FROM " . Common::prefixTable("access") 121 . " WHERE idsite = ?", $idSite); 122 123 $return = array(); 124 foreach ($users as $user) { 125 $return[$user['login']] = $user['access']; 126 } 127 128 return $return; 129 } 130 131 public function getUsersLoginWithSiteAccess($idSite, $access) 132 { 133 $db = $this->getDb(); 134 $users = $db->fetchAll("SELECT login FROM " . Common::prefixTable("access") 135 . " WHERE idsite = ? AND access = ?", array($idSite, $access)); 136 137 $logins = array(); 138 foreach ($users as $user) { 139 $logins[] = $user['login']; 140 } 141 142 return $logins; 143 } 144 145 /** 146 * For each website ID, returns the access level of the given $userLogin. 147 * If the user doesn't have any access to a website ('noaccess'), 148 * this website will not be in the returned array. 149 * If the user doesn't have any access, the returned array will be an empty array. 150 * 151 * @param string $userLogin User that has to be valid 152 * 153 * @return array The returned array has the format 154 * array( 155 * idsite1 => 'view', 156 * idsite2 => 'admin', 157 * idsite3 => 'view', 158 * ... 159 * ) 160 */ 161 public function getSitesAccessFromUser($userLogin) 162 { 163 $accessTable = Common::prefixTable('access'); 164 $siteTable = Common::prefixTable('site'); 165 166 $sql = sprintf("SELECT access.idsite, access.access 167 FROM %s access 168 LEFT JOIN %s site 169 ON access.idsite=site.idsite 170 WHERE access.login = ? and site.idsite is not null", $accessTable, $siteTable); 171 $db = $this->getDb(); 172 $users = $db->fetchAll($sql, $userLogin); 173 $return = array(); 174 foreach ($users as $user) { 175 $return[] = array( 176 'site' => $user['idsite'], 177 'access' => $user['access'], 178 ); 179 } 180 return $return; 181 } 182 183 public function getSitesAccessFromUserWithFilters($userLogin, $limit = null, $offset = 0, $pattern = null, $access = null, $idSites = null) 184 { 185 $siteAccessFilter = new SiteAccessFilter($userLogin, $pattern, $access, $idSites); 186 187 list($joins, $bind) = $siteAccessFilter->getJoins('a'); 188 189 list($where, $whereBind) = $siteAccessFilter->getWhere(); 190 $bind = array_merge($bind, $whereBind); 191 192 $limitSql = ''; 193 $offsetSql = ''; 194 if ($limit) { 195 $limitSql = "LIMIT " . (int)$limit; 196 197 if ($offset) { 198 $offsetSql = "OFFSET " . (int)$offset; 199 } 200 } 201 202 $sql = 'SELECT SQL_CALC_FOUND_ROWS s.idsite as idsite, s.name as site_name, GROUP_CONCAT(a.access SEPARATOR "|") as access 203 FROM ' . Common::prefixTable('access') . " a 204 $joins 205 $where 206 GROUP BY s.idsite 207 ORDER BY s.name ASC, s.idsite ASC 208 $limitSql $offsetSql"; 209 $db = $this->getDb(); 210 211 $access = $db->fetchAll($sql, $bind); 212 foreach ($access as &$entry) { 213 $entry['access'] = explode('|', $entry['access']); 214 } 215 216 $count = $db->fetchOne("SELECT FOUND_ROWS()"); 217 218 return [$access, $count]; 219 } 220 221 public function getIdSitesAccessMatching($userLogin, $filter_search = null, $filter_access = null, $idSites = null) 222 { 223 $siteAccessFilter = new SiteAccessFilter($userLogin, $filter_search, $filter_access, $idSites); 224 225 list($joins, $bind) = $siteAccessFilter->getJoins('a'); 226 227 list($where, $whereBind) = $siteAccessFilter->getWhere(); 228 $bind = array_merge($bind, $whereBind); 229 230 $sql = 'SELECT s.idsite FROM ' . Common::prefixTable('access') . " a $joins $where"; 231 232 $db = $this->getDb(); 233 234 $sites = $db->fetchAll($sql, $bind); 235 $sites = array_column($sites, 'idsite'); 236 return $sites; 237 } 238 239 public function getUser($userLogin) 240 { 241 $db = $this->getDb(); 242 243 $matchedUsers = $db->fetchAll("SELECT * FROM {$this->userTable} WHERE login = ?", $userLogin); 244 245 // for BC in 2.15 LTS, if there is a user w/ an exact match to the requested login, return that user. 246 // this is done since before this change, login was case sensitive. until 3.0, we want to maintain 247 // this behavior. 248 foreach ($matchedUsers as $user) { 249 if ($user['login'] == $userLogin) { 250 return $user; 251 } 252 } 253 254 return reset($matchedUsers); 255 } 256 257 public function hashTokenAuth($tokenAuth) 258 { 259 $salt = SettingsPiwik::getSalt(); 260 return hash(self::TOKEN_HASH_ALGO, $tokenAuth . $salt); 261 } 262 263 public function generateRandomTokenAuth() 264 { 265 $count = 0; 266 267 do { 268 $token = $this->generateTokenAuth(); 269 270 $count++; 271 if ($count > 20) { 272 // something seems wrong as the odds of that happening is basically 0. Only catching it to prevent 273 // endless loop in case there is some bug somewhere 274 throw new \Exception('Failed to generate token'); 275 } 276 277 } while ($this->getUserByTokenAuth($token)); 278 279 return $token; 280 } 281 282 private function generateTokenAuth() 283 { 284 return md5(Common::getRandomString(32, 'abcdef1234567890') . microtime(true) . Common::generateUniqId() . SettingsPiwik::getSalt()); 285 } 286 287 public function addTokenAuth($login, $tokenAuth, $description, $dateCreated, $dateExpired = null, $isSystemToken = false) 288 { 289 if (!$this->getUser($login)) { 290 throw new \Exception('User ' . $login . ' does not exist'); 291 } 292 293 BaseValidator::check('Description', $description, [new NotEmpty(), new CharacterLength(1, self::MAX_LENGTH_TOKEN_DESCRIPTION)]); 294 295 if (empty($dateExpired)) { 296 $dateExpired = null; 297 } 298 299 $isSystemToken = (int) $isSystemToken; 300 301 $insertSql = "INSERT INTO " . $this->tokenTable . ' (login, description, password, date_created, date_expired, system_token, hash_algo) VALUES (?, ?, ?, ?, ?, ?, ?)'; 302 303 $tokenAuth = $this->hashTokenAuth($tokenAuth); 304 305 $db = $this->getDb(); 306 $db->query($insertSql, [$login, $description, $tokenAuth, $dateCreated, $dateExpired, $isSystemToken, self::TOKEN_HASH_ALGO]); 307 308 return $db->lastInsertId(); 309 } 310 311 private function getTokenByTokenAuth($tokenAuth) 312 { 313 $tokenAuth = $this->hashTokenAuth($tokenAuth); 314 $db = $this->getDb(); 315 316 return $db->fetchRow("SELECT * FROM " . $this->tokenTable . " WHERE `password` = ?", $tokenAuth); 317 } 318 319 public function getUserTokenDescriptionByIdTokenAuth($idTokenAuth, $login) 320 { 321 $db = $this->getDb(); 322 323 $token = $db->fetchRow("SELECT description FROM " . $this->tokenTable . " WHERE `idusertokenauth` = ? and login = ? LIMIT 1", array($idTokenAuth, $login)); 324 325 return $token ? $token['description'] : ''; 326 } 327 328 private function getQueryNotExpiredToken() 329 { 330 return array( 331 'sql' => ' (date_expired is null or date_expired > ?)', 332 'bind' => array(Date::now()->getDatetime()) 333 ); 334 } 335 336 private function getTokenByTokenAuthIfNotExpired($tokenAuth) 337 { 338 $tokenAuth = $this->hashTokenAuth($tokenAuth); 339 $db = $this->getDb(); 340 341 $expired = $this->getQueryNotExpiredToken(); 342 $bind = array_merge(array($tokenAuth), $expired['bind']); 343 344 $token = $db->fetchRow("SELECT * FROM " . $this->tokenTable . " WHERE `password` = ? and " . $expired['sql'], $bind); 345 346 return $token; 347 } 348 349 public function deleteExpiredTokens($expiredSince) 350 { 351 $db = $this->getDb(); 352 353 return $db->query("DELETE FROM " . $this->tokenTable . " WHERE `date_expired` is not null and date_expired < ?", $expiredSince); 354 } 355 356 public function deleteAllTokensForUser($login) 357 { 358 $db = $this->getDb(); 359 360 return $db->query("DELETE FROM " . $this->tokenTable . " WHERE `login` = ?", $login); 361 } 362 363 public function getAllNonSystemTokensForLogin($login) 364 { 365 $db = $this->getDb(); 366 367 368 $expired = $this->getQueryNotExpiredToken(); 369 $bind = array_merge(array($login), $expired['bind']); 370 371 return $db->fetchAll("SELECT * FROM " . $this->tokenTable . " WHERE `login` = ? and system_token = 0 and " . $expired['sql'] . ' order by idusertokenauth ASC', $bind); 372 } 373 374 public function getAllHashedTokensForLogins($logins) 375 { 376 if (empty($logins)) { 377 return array(); 378 } 379 380 $db = $this->getDb(); 381 $placeholder = Common::getSqlStringFieldsArray($logins); 382 383 $expired = $this->getQueryNotExpiredToken(); 384 $bind = array_merge($logins, $expired['bind']); 385 386 $tokens = $db->fetchAll("SELECT password FROM " . $this->tokenTable . " WHERE `login` IN (".$placeholder.") and " . $expired['sql'], $bind); 387 return array_column($tokens, 'password'); 388 } 389 390 public function deleteToken($idTokenAuth, $login) 391 { 392 $db = $this->getDb(); 393 394 return $db->query("DELETE FROM " . $this->tokenTable . " WHERE `idusertokenauth` = ? and login = ?", array($idTokenAuth, $login)); 395 } 396 397 public function setTokenAuthWasUsed($tokenAuth, $dateLastUsed) 398 { 399 $token = $this->getTokenByTokenAuth($tokenAuth); 400 if (!empty($token)) { 401 402 $lastUsage = !empty($token['last_used']) ? strtotime($token['last_used']) : 0; 403 $newUsage = strtotime($dateLastUsed); 404 405 // update token usage only every 10 minutes to avoid table locks when multiple requests with the same token are made 406 // see https://github.com/matomo-org/matomo/issues/16924 407 if ($lastUsage > $newUsage - 600) { 408 return; 409 } 410 411 $this->updateTokenAuthTable($token['idusertokenauth'], array( 412 'last_used' => $dateLastUsed 413 )); 414 } 415 } 416 417 private function updateTokenAuthTable($idTokenAuth, $fields) { 418 $set = array(); 419 $bind = array(); 420 foreach ($fields as $key => $val) { 421 $set[] = "`$key` = ?"; 422 $bind[] = $val; 423 } 424 425 $bind[] = $idTokenAuth; 426 427 $db = $this->getDb(); 428 $db->query(sprintf('UPDATE `%s` SET %s WHERE `idusertokenauth` = ?', $this->tokenTable, implode(', ', $set)), $bind); 429 } 430 431 public function getUserByEmail($userEmail) 432 { 433 $db = $this->getDb(); 434 return $db->fetchRow("SELECT * FROM " . $this->userTable . " WHERE email = ?", $userEmail); 435 } 436 437 public function getUserByTokenAuth($tokenAuth) 438 { 439 if ($tokenAuth === 'anonymous') { 440 return $this->getUser('anonymous'); 441 } 442 443 $token = $this->getTokenByTokenAuthIfNotExpired($tokenAuth); 444 if (!empty($token)) { 445 $db = $this->getDb(); 446 return $db->fetchRow("SELECT * FROM " . $this->userTable . " WHERE `login` = ?", $token['login']); 447 } 448 } 449 450 public function addUser($userLogin, $hashedPassword, $email, $dateRegistered) 451 { 452 $user = array( 453 'login' => $userLogin, 454 'password' => $hashedPassword, 455 'email' => $email, 456 'date_registered' => $dateRegistered, 457 'superuser_access' => 0, 458 'ts_password_modified' => Date::now()->getDatetime(), 459 ); 460 461 $db = $this->getDb(); 462 $db->insert($this->userTable, $user); 463 } 464 465 public function setSuperUserAccess($userLogin, $hasSuperUserAccess) 466 { 467 $this->updateUserFields($userLogin, array( 468 'superuser_access' => $hasSuperUserAccess ? 1 : 0 469 )); 470 } 471 472 public function updateUserFields($userLogin, $fields) 473 { 474 $set = array(); 475 $bind = array(); 476 477 foreach ($fields as $key => $val) { 478 $set[] = "`$key` = ?"; 479 $bind[] = $val; 480 } 481 482 if (!empty($fields['password'])) { 483 $set[] = "ts_password_modified = ?"; 484 $bind[] = Date::now()->getDatetime(); 485 } 486 487 $bind[] = $userLogin; 488 489 $db = $this->getDb(); 490 $db->query(sprintf('UPDATE `%s` SET %s WHERE `login` = ?', $this->userTable, implode(', ', $set)), $bind); 491 } 492 493 /** 494 * Note that this returns the token_auth which is as private as the password! 495 * 496 * @return array[] containing login, email and token_auth 497 */ 498 public function getUsersHavingSuperUserAccess() 499 { 500 $db = $this->getDb(); 501 $users = $db->fetchAll("SELECT login, email, superuser_access 502 FROM " . Common::prefixTable("user") . " 503 WHERE superuser_access = 1 504 ORDER BY date_registered ASC"); 505 506 return $users; 507 } 508 509 public function updateUser($userLogin, $hashedPassword, $email) 510 { 511 $fields = array( 512 'email' => $email, 513 ); 514 if (!empty($hashedPassword)) { 515 $fields['password'] = $hashedPassword; 516 } 517 $this->updateUserFields($userLogin, $fields); 518 } 519 520 public function userExists($userLogin) 521 { 522 $db = $this->getDb(); 523 $count = $db->fetchOne("SELECT count(*) FROM " . $this->userTable . " WHERE login = ?", $userLogin); 524 525 return $count != 0; 526 } 527 528 public function userEmailExists($userEmail) 529 { 530 $db = $this->getDb(); 531 $count = $db->fetchOne("SELECT count(*) FROM " . $this->userTable . " WHERE email = ?", $userEmail); 532 533 return $count != 0; 534 } 535 536 public function removeUserAccess($userLogin, $access, $idSites) 537 { 538 $db = $this->getDb(); 539 540 $table = Common::prefixTable("access"); 541 542 foreach ($idSites as $idsite) { 543 $bind = array($userLogin, $idsite, $access); 544 $db->query("DELETE FROM " . $table . " WHERE login = ? and idsite = ? and access = ?", $bind); 545 } 546 } 547 548 public function addUserAccess($userLogin, $access, $idSites) 549 { 550 $db = $this->getDb(); 551 552 $insertSql = "INSERT INTO " . Common::prefixTable("access") . ' (idsite, login, access) VALUES (?, ?, ?)'; 553 foreach ($idSites as $idsite) { 554 $db->query($insertSql, [$idsite, $userLogin, $access]); 555 } 556 } 557 558 /** 559 * @param string $userLogin 560 */ 561 public function deleteUserOnly($userLogin) 562 { 563 $db = $this->getDb(); 564 $db->query("DELETE FROM " . $this->userTable . " WHERE login = ?", $userLogin); 565 $db->query("DELETE FROM " . $this->tokenTable . " WHERE login = ?", $userLogin); 566 567 /** 568 * Triggered after a user has been deleted. 569 * 570 * This event should be used to clean up any data that is related to the now deleted user. 571 * The **Dashboard** plugin, for example, uses this event to remove the user's dashboards. 572 * 573 * @param string $userLogins The login handle of the deleted user. 574 */ 575 Piwik::postEvent('UsersManager.deleteUser', array($userLogin)); 576 } 577 578 public function deleteUserOptions($userLogin) 579 { 580 Option::deleteLike('UsersManager.%.' . $userLogin); 581 } 582 583 /** 584 * @param string $userLogin 585 */ 586 public function deleteUserAccess($userLogin, $idSites = null) 587 { 588 $db = $this->getDb(); 589 590 if (is_null($idSites)) { 591 $db->query("DELETE FROM " . Common::prefixTable("access") . " WHERE login = ?", $userLogin); 592 } else { 593 foreach ($idSites as $idsite) { 594 $db->query("DELETE FROM " . Common::prefixTable("access") . " WHERE idsite = ? AND login = ?", [$idsite, $userLogin]); 595 } 596 } 597 } 598 599 private function getDb() 600 { 601 return Db::get(); 602 } 603 604 public function getUserLoginsMatching($idSite = null, $pattern = null, $access = null, $logins = null) 605 { 606 $filter = new UserTableFilter($access, $idSite, $pattern, $logins); 607 608 list($joins, $bind) = $filter->getJoins('u'); 609 list($where, $whereBind) = $filter->getWhere(); 610 611 $bind = array_merge($bind, $whereBind); 612 613 $sql = 'SELECT u.login FROM ' . $this->userTable . " u $joins $where"; 614 615 $db = $this->getDb(); 616 617 $result = $db->fetchAll($sql, $bind); 618 $result = array_column($result, 'login'); 619 return $result; 620 } 621 622 /** 623 * Returns all users and their access to `$idSite`. 624 * 625 * @param int $idSite 626 * @param int|null $limit 627 * @param int|null $offset 628 * @param string|null $pattern text to search for if any 629 * @param string|null $access 'noaccess','some','view','admin' or 'superuser' 630 * @param string[]|null $logins the logins to limit the search to (if any) 631 * @return array 632 */ 633 public function getUsersWithRole($idSite, $limit = null, $offset = null, $pattern = null, $access = null, $logins = null) 634 { 635 $filter = new UserTableFilter($access, $idSite, $pattern, $logins); 636 637 list($joins, $bind) = $filter->getJoins('u'); 638 list($where, $whereBind) = $filter->getWhere(); 639 640 $bind = array_merge($bind, $whereBind); 641 642 $limitSql = ''; 643 $offsetSql = ''; 644 if ($limit) { 645 $limitSql = "LIMIT " . (int)$limit; 646 647 if ($offset) { 648 $offsetSql = "OFFSET " . (int)$offset; 649 } 650 } 651 652 $sql = 'SELECT SQL_CALC_FOUND_ROWS u.*, GROUP_CONCAT(a.access SEPARATOR "|") as access 653 FROM ' . $this->userTable . " u 654 $joins 655 $where 656 GROUP BY u.login 657 ORDER BY u.login ASC 658 $limitSql $offsetSql"; 659 660 $db = $this->getDb(); 661 662 $users = $db->fetchAll($sql, $bind); 663 foreach ($users as &$user) { 664 $user['access'] = explode('|', $user['access']); 665 } 666 667 $count = $db->fetchOne("SELECT FOUND_ROWS()"); 668 669 return [$users, $count]; 670 } 671 672 public function getSiteAccessCount($userLogin) 673 { 674 $sql = "SELECT COUNT(*) FROM " . Common::prefixTable('access') . " WHERE login = ?"; 675 $bind = [$userLogin]; 676 677 $db = $this->getDb(); 678 return $db->fetchOne($sql, $bind); 679 } 680 681 public function getUsersWithAccessToSites($idSites) 682 { 683 $idSites = array_map('intval', $idSites); 684 685 $loginSql = 'SELECT DISTINCT ia.login FROM ' . Common::prefixTable('access') . ' ia WHERE ia.idsite IN (' 686 . implode(',', $idSites) . ')'; 687 688 $logins = \Piwik\Db::fetchAll($loginSql); 689 $logins = array_column($logins, 'login'); 690 return $logins; 691 } 692 693} 694