diff options
Diffstat (limited to 'lib/plugins/db/RolePersistence.php')
| -rw-r--r-- | lib/plugins/db/RolePersistence.php | 721 |
1 files changed, 721 insertions, 0 deletions
diff --git a/lib/plugins/db/RolePersistence.php b/lib/plugins/db/RolePersistence.php new file mode 100644 index 0000000..54a0053 --- /dev/null +++ b/lib/plugins/db/RolePersistence.php @@ -0,0 +1,721 @@ +<?php +/** + * RolePersistence.php + * + * Funktionen für das Rollenmanagement + * + * @author Dennis Reil <dennis.reil@offis.de> + * @author Michael Riehemann <michael.riehemann@uni-oldenburg.de> + * @package pluginengine + * @subpackage db + * @copyright 2009 Stud.IP + * @license http://www.gnu.org/licenses/gpl.html GPL Licence 3 + */ +class RolePersistence +{ + const ROLES_CACHE_KEY = 'roles'; + const USER_ROLES_CACHE_KEY = 'roles/user'; + const PLUGIN_ROLES_CACHE_KEY = 'roles/plugin'; + + protected static $all_roles = null; + + /** + * Returns all available roles. + * + * @param bool $grouped Return the roles grouped by system type or other + * @return Role[]|array{system: Role[], other: Role[]} + */ + public static function getAllRoles(bool $grouped = false): array + { + if (self::$all_roles === null) { + // read cache + $cache = \Studip\Cache\Factory::getCache(); + + // cache miss, retrieve from database + self::$all_roles = $cache->read(self::ROLES_CACHE_KEY); + if (!self::$all_roles) { + $query = "SELECT `roleid`, `rolename`, `system` = 'y' AS `is_system` + FROM `roles` + ORDER BY `rolename`"; + $statement = DBManager::get()->query($query); + $statement->setFetchMode(PDO::FETCH_ASSOC); + + self::$all_roles = []; + foreach ($statement as $row) { + self::$all_roles[$row['roleid']] = new Role($row['roleid'], $row['rolename'], $row['is_system']); + } + + $cache->write(self::ROLES_CACHE_KEY, self::$all_roles); + } + } + + if (!$grouped) { + return self::$all_roles; + } + + $groups = ['system' => [], 'other' => []]; + foreach (self::$all_roles as $id => $role) { + $index = $role->getSystemtype() ? 'system' : 'other'; + $groups[$index][$id] = $role; + } + + return $groups; + } + + public static function getRoleIdByName($name) + { + foreach (self::getAllRoles() as $id => $role) { + if ($role->getRolename() === $name) { + return $id; + } + } + return false; + } + + /** + * Inserts the role into the database or does an update, if it's already there + * + * @param Role $role + * @return int the role id + */ + public static function saveRole($role) + { + $query = "INSERT INTO `roles` (`roleid`, `rolename`, `system`) + VALUES (?, ?, 'n') + ON DUPLICATE KEY UPDATE `rolename` = VALUES(`rolename`)"; + $statement = DBManager::get()->prepare($query); + $statement->execute([$role->getRoleId(), $role->getRolename()]); + + if ($role->getRoleid() === Role::UNKNOWN_ROLE_ID) { + $role_id = DBManager::get()->lastInsertId(); + $role->setRoleid($role_id); + + $event = 'RoleDidCreate'; + } else { + $event = 'RoleDidUpdate'; + } + + // sweep roles cache, see #getAllRoles + self::expireRolesCache(); + + NotificationCenter::postNotification( + $event, + $role->getRoleid(), + $role->getRolename() + ); + + return $role->getRoleid(); + } + + /** + * Delete role if not a permanent role. System roles cannot be deleted. + * + * @param Role $role + */ + public static function deleteRole($role): bool + { + $id = $role->getRoleid(); + $name = $role->getRolename(); + + $query = "SELECT `pluginid` FROM `roles_plugins` WHERE `roleid` = ?"; + $statement = DBManager::get()->prepare($query); + $statement->execute([$id]); + $statement->setFetchMode(PDO::FETCH_COLUMN, 0); + + $result = DBManager::get()->execute( + "DELETE `roles`, `roles_user`, `roles_plugins`, `roles_studipperms` + FROM `roles` + LEFT JOIN `roles_user` USING (`roleid`) + LEFT JOIN `roles_plugins` USING (`roleid`) + LEFT JOIN `roles_studipperms` USING (`roleid`) + WHERE `roleid` = ? AND `system` = 'n'", + [$id] + ); + + if ($result === 0) { + return false; + } + + // sweep roles cache + self::expireRolesCache(); + self::expireUserCache(); + + foreach ($statement as $plugin_id) { + self::expirePluginCache($plugin_id); + } + + NotificationCenter::postNotification('RoleDidDelete', $id, $name); + + return true; + } + + /** + * Delete role by name if not a permanent role. System roles cannot be + * deleted. + * + * @param string $role_name + * + * @return bool + */ + public static function deleteRoleByName(string $role_name): bool + { + foreach (self::getAllRoles() as $role) { + if ($role->getRolename() === $role_name) { + return self::deleteRole($role); + } + } + + return false; + } + + /** + * Saves a role assignment to the database + * + * @param User $user + * @param Role $role + * @param string $institut_id + */ + public static function assignRole(User $user, $role, $institut_id = '') + { + // role is not in database + // save it to the database first + if ($role->getRoleid() === Role::UNKNOWN_ROLE_ID) { + $roleid = self::saveRole($role); + } else { + $roleid = $role->getRoleid(); + } + + $query = "REPLACE INTO `roles_user` (`roleid`, `userid`, `institut_id`) + VALUES (?, ?, ?)"; + $statement = DBManager::get()->prepare($query); + $statement->execute([$roleid, $user->id, $institut_id]); + + unset(self::getUserRolesCache()[$user->id]); + + NotificationCenter::postNotification( + 'RoleAssignmentDidCreate', + $roleid, + $user->id, + $institut_id + ); + } + + /** + * Assigns a role to a stud.ip permission. System roles cannot be assigned + * to permissions. + * + * @param string $perm + * @param Role $role + * + * @return bool + * @throws Exception + */ + public static function assignRoleToPerm(string $perm, Role $role): bool + { + if ($role->getSystemtype()) { + throw new Exception('Cannot assign system roles to permissions.'); + } + + if (!in_array($perm, ['user', 'autor', 'tutor', 'dozent', 'admin', 'root'])) { + throw new Exception("Invalid permission {$perm}"); + } + + $query = "INSERT INTO `roles_studipperms` (`roleid`, `permname`) + VALUES (?, ?)"; + $result = DBManager::get()->execute($query, [$role->getRoleid(), $perm]); + + if ($result === 0) { + return false; + } + + User::findEachByPerms( + function (User $user) { + self::expireUserCache($user->id); + }, + $perm + ); + + return true; + } + + /** + * Gets all assigned roles from the database for a user + * + * @param int $userid + * @param boolean $implicit + * @return array + */ + public static function getAssignedRoles($user_id, $implicit = false) + { + return array_intersect_key( + self::getAllRoles(), + self::loadUserRoles($user_id, $implicit) + ); + } + + /** + * Returns institutes for which the given user has the given role. + * @param string $user_id User id + * @param int $role_id Role id + * @return array of institute ids + */ + public static function getAssignedRoleInstitutes($user_id, $role_id) + { + $roles = self::loadUserRoles($user_id); + return $roles[$role_id] ?? []; + } + + /** + * Checks a role assignment for an user + * optionally check for institute + * + * @param string $userid + * @param string $assignedrole + * @param string $institut_id + * @return boolean + */ + public static function isAssignedRole($userid, $assignedrole, $institut_id = '') + { + if (!$userid) { + return false; + } + + $faculty_id = $institut_id + ? Institute::find($institut_id)->fakultaets_id + : null; + + $role_id = self::getRoleIdByName($assignedrole); + $user_roles = self::loadUserRoles($userid, true); + + return isset($user_roles[$role_id]) + && ( + !$institut_id + || in_array($institut_id, $user_roles[$role_id]) + || in_array($faculty_id, $user_roles[$role_id]) + ); + } + + private static function loadUserRoles($user_id, $implicit = false) + { + $cache = self::getUserRolesCache(); + + if (!isset($cache[$user_id])) { + $query = "SELECT `roleid`, `institut_id`, 1 AS explicit + FROM `roles_user` + WHERE `userid` = :user_id + + UNION ALL + + SELECT `roleid`, '' AS institut_id, 0 AS explicit + FROM `roles_studipperms` + WHERE `permname` = :perm"; + $statement = DBManager::get()->prepare($query); + $statement->bindValue(':user_id', $user_id); + $statement->bindValue(':perm', empty($GLOBALS['perm']) ? 'nobody' : $GLOBALS['perm']->get_perm($user_id)); + $statement->execute(); + $statement->setFetchMode(PDO::FETCH_ASSOC); + + $roles = []; + foreach ($statement as $row) { + if (!isset($roles[$row['roleid']])) { + $roles[$row['roleid']] = [ + 'id' => $row['roleid'], + 'institutes' => [], + 'explicit' => (bool) $row['explicit'], + ]; + } + if ($row['institut_id']) { + $roles[$row['roleid']]['institutes'][] = $row['institut_id']; + } + } + + $cache[$user_id] = $roles; + } + + // Filter implicit roles away if necessary + $roles = array_filter( + $cache[$user_id], + function ($role) use ($implicit) { + return $implicit || $role['explicit']; + } + ); + + return array_column($roles, 'institutes', 'id'); + } + + /** + * Deletes a role assignment from the database + * + * @param User $user + * @param Role $role + * @param String $institut_id + */ + public static function deleteRoleAssignment(User $user, $role, $institut_id = null) + { + $query = "DELETE FROM `roles_user` + WHERE `roleid` = ? + AND `userid` = ? + AND `institut_id` = IFNULL(?, `institut_id`)"; + DBManager::get()->execute( + $query, + [$role->getRoleid(), $user->id, $institut_id] + ); + + unset(self::getUserRolesCache()[$user->id]); + + NotificationCenter::postNotification( + 'RoleAssignmentDidDelete', + $role->getRoleid(), + $user->id, + $institut_id + ); + } + + /** + * Removes a role from a stud.ip permission. System roles cannot be removed + * from permissions. + * + * @param string $perm + * @param Role $role + * + * @return bool + * @throws Exception + */ + public static function deleteRoleAssignmentFromPerm(string $perm, Role $role): bool + { + if ($role->getSystemtype()) { + throw new Exception('Cannot remove system role assignment from permissions.'); + } + + if (!in_array($perm, ['user', 'autor', 'tutor', 'dozent', 'admin', 'root'])) { + throw new Exception("Invalid permission {$perm}"); + } + + $query = "DELETE FROM `roles_studipperms` + WHERE `roleid` = ? + AND `permname` = ?"; + $result = DBManager::get()->execute($query, [$role->getRoleid(), $perm]); + + if ($result === 0) { + return false; + } + + User::findEachByPerms( + function (User $user) { + self::expireUserCache($user->id); + }, + $perm + ); + + return true; + } + + + /** + * Get's all Role-Assignments for a certain user. + * If no user is set, all role assignments are returned. + * + * @param User $user + * @return array with roleids and the assigned userids + * @deprecated seems to be unused (and was corrupt for some versions) + */ + public static function getAllRoleAssignments($user = null) + { + $query = "SELECT `roleid`, `userid` + FROM `roles_user` + WHERE `userid` = IFNULL(?, `userid`)"; + return DBManager::get()->fetchPairs($query, [$user]); + } + + /** + * Enter description here... + * + * @param int $pluginid + * @param array $roleids + */ + public static function assignPluginRoles($plugin_id, $role_ids) + { + $plugin_id = (int) $plugin_id; + + $query = "REPLACE INTO `roles_plugins` (`roleid`, `pluginid`) + VALUES (:role_id, :plugin_id)"; + $statement = DBManager::get()->prepare($query); + $statement->bindValue(':plugin_id', $plugin_id); + + foreach ($role_ids as $role_id) { + $statement->bindValue(':role_id', $role_id); + $statement->execute(); + } + + self::expirePluginCache($plugin_id); + + foreach ($role_ids as $role_id) { + NotificationCenter::postNotification( + 'PluginRoleAssignmentDidCreate', + $role_id, + $plugin_id + ); + } + } + + /** + * Removes the given roles' assignments from the given plugin. + * + * @param int $pluginid + * @param array $roleids + */ + public static function deleteAssignedPluginRoles($plugin_id, $role_ids) + { + $plugin_id = (int) $plugin_id; + + $query = "DELETE FROM `roles_plugins` + WHERE `pluginid` = :plugin_id + AND `roleid` = :role_id"; + $statement = DBManager::get()->prepare($query); + $statement->bindValue(':plugin_id', $plugin_id); + + foreach ($role_ids as $role_id) { + $statement->bindValue(':role_id', $role_id); + $statement->execute(); + } + + self::expirePluginCache($plugin_id); + + foreach ($role_ids as $role_id) { + NotificationCenter::postNotification( + 'PluginRoleAssignmentDidDelete', + $role_id, + $plugin_id + ); + } + } + + /** + * Return all roles assigned to a plugin. + * + * @param int $pluginid + * @return array + */ + public static function getAssignedPluginRoles($plugin_id) + { + $plugin_id = (int) $plugin_id; + + // read plugin roles from cache + $cache = self::getPluginRolesCache(); + + // cache miss, retrieve roles from database + if (!isset($cache[$plugin_id])) { + $query = "SELECT `roleid` FROM `roles_plugins` WHERE `pluginid` = ?"; + $statement = DBManager::get()->prepare($query); + $statement->execute([$plugin_id]); + $role_ids = $statement->fetchAll(PDO::FETCH_COLUMN); + + // write to cache + $cache[$plugin_id] = $role_ids; + } + + $roles = self::getAllRoles(); + return array_filter(array_map( + function ($role_id) use ($roles) { + if (!isset($roles[$role_id])) { + return false; + } + return $roles[$role_id]; + }, + $cache[$plugin_id] + )); + } + + /** + * Returns all users that have a specific role - given by it's name. + * + * @param string $role_name Name of the role + * @param bool $only_explicit Only select explicit assignments from table + * `roles_user` if true, otherwise also select + * by perm defined in table `roles_studipperms` + * + * @return User[] + */ + public static function getUsersWithRoleByName(string $role_name, bool $only_explicit = true): array + { + $role_id = self::getRoleIdByName($role_name); + if ($role_id === false) { + throw new Exception("Unknown role name {$role_name}"); + } + + return self::getUsersWithRoleById($role_id, $only_explicit); + } + + /** + * Returns all users that have a specific role - given by it's id. + * + * @param int $role_id Id of the role + * @param bool $only_explicit Only select explicit assignments from table + * `roles_user` if true, otherwise also select + * by perm defined in table `roles_studipperms` + * + * @return User[] + */ + public static function getUsersWithRoleById(int $role_id, bool $only_explicit = true): array + { + $query = "SELECT `userid` AS `user_id` + FROM `roles_user` + WHERE `roleid` = :role_id"; + + if (!$only_explicit) { + $query = "SELECT DISTINCT `user_id` + FROM ( + {$query} + + UNION ALL + + SELECT `user_id` + FROM `roles_studipperms` AS `rsp` + JOIN `auth_user_md5` AS `aum` + ON (`rsp`.`permname` = `aum`.`perms`) + WHERE `rsp`.`roleid` = :role_id + ) AS tmp"; + } + + $user_ids = DBManager::get()->fetchFirst($query, [':role_id' => $role_id]); + + return User::findMany($user_ids); + } + + /** + * Returns statistic values for each role: + * + * - number of explicitely assigned users + * - number of implicitely assigned users + * - number of assigned plugins + * + * @return array + */ + public static function getStatistics() + { + // Get basic statistics + $query = "SELECT r.`roleid`, + COUNT(DISTINCT ru.`userid`) AS explicit, + COUNT(DISTINCT rp.`pluginid`) AS plugins + FROM roles AS r + -- Explicit assignment + LEFT JOIN `roles_user` AS ru + ON r.`roleid` = ru.`roleid` AND ru.`userid` IN (SELECT `user_id` FROM `auth_user_md5`) + -- Plugins + LEFT JOIN `roles_plugins` AS rp + ON r.`roleid` = rp.`roleid` AND rp.`pluginid` IN (SELECT `pluginid` FROM `plugins`) + GROUP BY r.`roleid`"; + $result = DBManager::get()->fetchGrouped($query); + + // Fetch implicit assignments in a second query due to performance + // reasons + foreach (self::countImplicitUsers(array_keys($result)) as $id => $count) { + $result[$id]['implicit'] = $count; + } + + return $result; + } + + /** + * Counts the implicitely assigned users for a role. + * @param mixed $role_id Role id or array of role ids + * @return mixed number of implictit for the role (if one role id is given) + * or associative array [role id => number of implicit users] + * when given a list of role ids + */ + public static function countImplicitUsers($role_id) + { + // Ensure that the result array has an entry for every role id + $result = array_fill_keys((array) $role_id, 0); + + $query = "SELECT rsp.`roleid`, COUNT(*) AS implicit + FROM `roles_studipperms` AS rsp + JOIN `auth_user_md5` AS a ON rsp.`permname` = a.`perms` + LEFT JOIN `roles_user` AS ru + ON a.`user_id` = ru.`userid` AND rsp.`roleid` = ru.`roleid` + WHERE rsp.`roleid` IN (?) + AND ru.`userid` IS NULL + GROUP BY rsp.`roleid`"; + $statement = DBManager::get()->prepare($query); + $statement->execute([$role_id]); + $statement->setFetchMode(PDO::FETCH_ASSOC); + + foreach ($statement as $row) { + $result[$row['roleid']] = (int) $row['implicit']; + } + + return is_array($role_id) + ? $result + : $result[$role_id]; + } + + // Cache operations + private static $user_roles_cache = null; + private static $plugin_roles_cache = null; + + private static function getUserRolesCache(): StudipCachedArray + { + if (self::$user_roles_cache === null) { + self::$user_roles_cache = new StudipCachedArray(self::USER_ROLES_CACHE_KEY); + } + return self::$user_roles_cache; + } + + private static function getPluginRolesCache(): StudipCachedArray + { + if (self::$plugin_roles_cache === null) { + self::$plugin_roles_cache = new StudipCachedArray(self::PLUGIN_ROLES_CACHE_KEY); + } + return self::$plugin_roles_cache; + } + + /** + * Expires all cached roles. + */ + public static function expireRolesCache() + { + self::$all_roles = null; + \Studip\Cache\Factory::getCache()->expire(self::ROLES_CACHE_KEY); + } + + /** + * Expires all cached user role assignments. + * + * @param string|null $user_id Optional user id to expire the cache for. + * If none is given, the whole cache is cleared. + */ + public static function expireUserCache($user_id = null) + { + if ($user_id === null) { + self::getUserRolesCache()->expire(); + } else { + unset(self::getUserRolesCache()[$user_id]); + } + } + + /** + * Expires all cached plugin role assignments. + * + * @param string|int|null $plugin_id Optional plugin id to expire the cache + * for. If none is given, the whole cache + * is cleared. + */ + public static function expirePluginCache($plugin_id = null) + { + if ($plugin_id === null) { + self::getPluginRolesCache()->expire(); + } else { + unset(self::getPluginRolesCache()[$plugin_id]); + } + } + + /** + * Expires all caches + */ + public static function expireCaches(): void + { + self::expireRolesCache(); + self::expireUserCache(); + self::expirePluginCache(); + } +} |