/** * Who has this capability in this context? * * This can be a very expensive call - use sparingly and keep * the results if you are going to need them again soon. * * Note if $fields is empty this function attempts to get u.* * which can get rather large - and has a serious perf impact * on some DBs. * * @param context $context * @param string|array $capability - capability name(s) * @param string $fields - fields to be pulled. The user table is aliased to 'u'. u.id MUST be included. * @param string $sort - the sort order. Default is lastaccess time. * @param mixed $limitfrom - number of records to skip (offset) * @param mixed $limitnum - number of records to fetch * @param string|array $groups - single group or array of groups - only return * users who are in one of these group(s). * @param string|array $exceptions - list of users to exclude, comma separated or array * @param bool $doanything_ignored not used any more, admin accounts are never returned * @param bool $view_ignored - use get_enrolled_sql() instead * @param bool $useviewallgroups if $groups is set the return users who * have capability both $capability and moodle/site:accessallgroups * in this context, as well as users who have $capability and who are * in $groups. * @return mixed */ function get_users_by_capability(context $context, $capability, $fields = '', $sort = '', $limitfrom = '', $limitnum = '', $groups = '', $exceptions = '', $doanything_ignored = null, $view_ignored = null, $useviewallgroups = false) { global $CFG, $DB; $defaultuserroleid = isset($CFG->defaultuserroleid) ? $CFG->defaultuserroleid : 0; $defaultfrontpageroleid = isset($CFG->defaultfrontpageroleid) ? $CFG->defaultfrontpageroleid : 0; $ctxids = trim($context->path, '/'); $ctxids = str_replace('/', ',', $ctxids); // Context is the frontpage $iscoursepage = false; // coursepage other than fp $isfrontpage = false; if ($context->contextlevel == CONTEXT_COURSE) { if ($context->instanceid == SITEID) { $isfrontpage = true; } else { $iscoursepage = true; } } $isfrontpage = $isfrontpage || is_inside_frontpage($context); $caps = (array) $capability; // construct list of context paths bottom-->top list($contextids, $paths) = get_context_info_list($context); // we need to find out all roles that have these capabilities either in definition or in overrides $defs = array(); list($incontexts, $params) = $DB->get_in_or_equal($contextids, SQL_PARAMS_NAMED, 'con'); list($incaps, $params2) = $DB->get_in_or_equal($caps, SQL_PARAMS_NAMED, 'cap'); $params = array_merge($params, $params2); $sql = "SELECT rc.id, rc.roleid, rc.permission, rc.capability, ctx.path\n FROM {role_capabilities} rc\n JOIN {context} ctx on rc.contextid = ctx.id\n WHERE rc.contextid {$incontexts} AND rc.capability {$incaps}"; $rcs = $DB->get_records_sql($sql, $params); foreach ($rcs as $rc) { $defs[$rc->capability][$rc->path][$rc->roleid] = $rc->permission; } // go through the permissions bottom-->top direction to evaluate the current permission, // first one wins (prohibit is an exception that always wins) $access = array(); foreach ($caps as $cap) { foreach ($paths as $path) { if (empty($defs[$cap][$path])) { continue; } foreach ($defs[$cap][$path] as $roleid => $perm) { if ($perm == CAP_PROHIBIT) { $access[$cap][$roleid] = CAP_PROHIBIT; continue; } if (!isset($access[$cap][$roleid])) { $access[$cap][$roleid] = (int) $perm; } } } } // make lists of roles that are needed and prohibited in this context $needed = array(); // one of these is enough $prohibited = array(); // must not have any of these foreach ($caps as $cap) { if (empty($access[$cap])) { continue; } foreach ($access[$cap] as $roleid => $perm) { if ($perm == CAP_PROHIBIT) { unset($needed[$cap][$roleid]); $prohibited[$cap][$roleid] = true; } else { if ($perm == CAP_ALLOW and empty($prohibited[$cap][$roleid])) { $needed[$cap][$roleid] = true; } } } if (empty($needed[$cap]) or !empty($prohibited[$cap][$defaultuserroleid])) { // easy, nobody has the permission unset($needed[$cap]); unset($prohibited[$cap]); } else { if ($isfrontpage and !empty($prohibited[$cap][$defaultfrontpageroleid])) { // everybody is disqualified on the frontapge unset($needed[$cap]); unset($prohibited[$cap]); } } if (empty($prohibited[$cap])) { unset($prohibited[$cap]); } } if (empty($needed)) { // there can not be anybody if no roles match this request return array(); } if (empty($prohibited)) { // we can compact the needed roles $n = array(); foreach ($needed as $cap) { foreach ($cap as $roleid => $unused) { $n[$roleid] = true; } } $needed = array('any' => $n); unset($n); } /// ***** Set up default fields ****** if (empty($fields)) { if ($iscoursepage) { $fields = 'u.*, ul.timeaccess AS lastaccess'; } else { $fields = 'u.*'; } } else { if (debugging('', DEBUG_DEVELOPER) && strpos($fields, 'u.*') === false && strpos($fields, 'u.id') === false) { debugging('u.id must be included in the list of fields passed to get_users_by_capability().', DEBUG_DEVELOPER); } } /// Set up default sort if (empty($sort)) { // default to course lastaccess or just lastaccess if ($iscoursepage) { $sort = 'ul.timeaccess'; } else { $sort = 'u.lastaccess'; } } // Prepare query clauses $wherecond = array(); $params = array(); $joins = array(); // User lastaccess JOIN if (strpos($sort, 'ul.timeaccess') === false and strpos($fields, 'ul.timeaccess') === false) { // user_lastaccess is not required MDL-13810 } else { if ($iscoursepage) { $joins[] = "LEFT OUTER JOIN {user_lastaccess} ul ON (ul.userid = u.id AND ul.courseid = {$context->instanceid})"; } else { throw new coding_exception('Invalid sort in get_users_by_capability(), ul.timeaccess allowed only for course contexts.'); } } /// We never return deleted users or guest account. $wherecond[] = "u.deleted = 0 AND u.id <> :guestid"; $params['guestid'] = $CFG->siteguest; /// Groups if ($groups) { $groups = (array) $groups; list($grouptest, $grpparams) = $DB->get_in_or_equal($groups, SQL_PARAMS_NAMED, 'grp'); $grouptest = "u.id IN (SELECT userid FROM {groups_members} gm WHERE gm.groupid {$grouptest})"; $params = array_merge($params, $grpparams); if ($useviewallgroups) { $viewallgroupsusers = get_users_by_capability($context, 'moodle/site:accessallgroups', 'u.id, u.id', '', '', '', '', $exceptions); if (!empty($viewallgroupsusers)) { $wherecond[] = "({$grouptest} OR u.id IN (" . implode(',', array_keys($viewallgroupsusers)) . '))'; } else { $wherecond[] = "({$grouptest})"; } } else { $wherecond[] = "({$grouptest})"; } } /// User exceptions if (!empty($exceptions)) { $exceptions = (array) $exceptions; list($exsql, $exparams) = $DB->get_in_or_equal($exceptions, SQL_PARAMS_NAMED, 'exc', false); $params = array_merge($params, $exparams); $wherecond[] = "u.id {$exsql}"; } // now add the needed and prohibited roles conditions as joins if (!empty($needed['any'])) { // simple case - there are no prohibits involved if (!empty($needed['any'][$defaultuserroleid]) or $isfrontpage and !empty($needed['any'][$defaultfrontpageroleid])) { // everybody } else { $joins[] = "JOIN (SELECT DISTINCT userid\n FROM {role_assignments}\n WHERE contextid IN ({$ctxids})\n AND roleid IN (" . implode(',', array_keys($needed['any'])) . ")\n ) ra ON ra.userid = u.id"; } } else { $unions = array(); $everybody = false; foreach ($needed as $cap => $unused) { if (empty($prohibited[$cap])) { if (!empty($needed[$cap][$defaultuserroleid]) or $isfrontpage and !empty($needed[$cap][$defaultfrontpageroleid])) { $everybody = true; break; } else { $unions[] = "SELECT userid\n FROM {role_assignments}\n WHERE contextid IN ({$ctxids})\n AND roleid IN (" . implode(',', array_keys($needed[$cap])) . ")"; } } else { if (!empty($prohibited[$cap][$defaultuserroleid]) or $isfrontpage and !empty($prohibited[$cap][$defaultfrontpageroleid])) { // nobody can have this cap because it is prevented in default roles continue; } else { if (!empty($needed[$cap][$defaultuserroleid]) or $isfrontpage and !empty($needed[$cap][$defaultfrontpageroleid])) { // everybody except the prohibitted - hiding does not matter $unions[] = "SELECT id AS userid\n FROM {user}\n WHERE id NOT IN (SELECT userid\n FROM {role_assignments}\n WHERE contextid IN ({$ctxids})\n AND roleid IN (" . implode(',', array_keys($prohibited[$cap])) . "))"; } else { $unions[] = "SELECT userid\n FROM {role_assignments}\n WHERE contextid IN ({$ctxids})\n AND roleid IN (" . implode(',', array_keys($needed[$cap])) . ")\n AND roleid NOT IN (" . implode(',', array_keys($prohibited[$cap])) . ")"; } } } } if (!$everybody) { if ($unions) { $joins[] = "JOIN (SELECT DISTINCT userid FROM ( " . implode(' UNION ', $unions) . " ) us) ra ON ra.userid = u.id"; } else { // only prohibits found - nobody can be matched $wherecond[] = "1 = 2"; } } } // Collect WHERE conditions and needed joins $where = implode(' AND ', $wherecond); if ($where !== '') { $where = 'WHERE ' . $where; } $joins = implode("\n", $joins); /// Ok, let's get the users! $sql = "SELECT {$fields}\n FROM {user} u\n {$joins}\n {$where}\n ORDER BY {$sort}"; return $DB->get_records_sql($sql, $params, $limitfrom, $limitnum); }
/** * Gets sql joins for finding users with a capability in the given context * * @param context $context * @param string $capability * @param string $useridcolumn e.g. u.id * @return \core\dml\sql_join Contains joins, wheres, params */ function get_with_capability_join(context $context, $capability, $useridcolumn) { global $DB, $CFG; // Use unique prefix just in case somebody makes some SQL magic with the result. static $i = 0; $i++; $prefix = 'eu' . $i . '_'; // First find the course context. $coursecontext = $context->get_course_context(); $isfrontpage = $coursecontext->instanceid == SITEID; $joins = array(); $wheres = array(); $params = array(); list($contextids, $contextpaths) = get_context_info_list($context); list($incontexts, $cparams) = $DB->get_in_or_equal($contextids, SQL_PARAMS_NAMED, 'ctx'); $cparams['cap'] = $capability; $defs = array(); $sql = "SELECT rc.id, rc.roleid, rc.permission, ctx.path\n FROM {role_capabilities} rc\n JOIN {context} ctx on rc.contextid = ctx.id\n WHERE rc.contextid {$incontexts} AND rc.capability = :cap"; $rcs = $DB->get_records_sql($sql, $cparams); foreach ($rcs as $rc) { $defs[$rc->path][$rc->roleid] = $rc->permission; } $access = array(); if (!empty($defs)) { foreach ($contextpaths as $path) { if (empty($defs[$path])) { continue; } foreach ($defs[$path] as $roleid => $perm) { if ($perm == CAP_PROHIBIT) { $access[$roleid] = CAP_PROHIBIT; continue; } if (!isset($access[$roleid])) { $access[$roleid] = (int) $perm; } } } } unset($defs); // Make lists of roles that are needed and prohibited. $needed = array(); // One of these is enough. $prohibited = array(); // Must not have any of these. foreach ($access as $roleid => $perm) { if ($perm == CAP_PROHIBIT) { unset($needed[$roleid]); $prohibited[$roleid] = true; } else { if ($perm == CAP_ALLOW and empty($prohibited[$roleid])) { $needed[$roleid] = true; } } } $defaultuserroleid = isset($CFG->defaultuserroleid) ? $CFG->defaultuserroleid : 0; $defaultfrontpageroleid = isset($CFG->defaultfrontpageroleid) ? $CFG->defaultfrontpageroleid : 0; $nobody = false; if ($isfrontpage) { if (!empty($prohibited[$defaultuserroleid]) or !empty($prohibited[$defaultfrontpageroleid])) { $nobody = true; } else { if (!empty($needed[$defaultuserroleid]) or !empty($needed[$defaultfrontpageroleid])) { // Everybody not having prohibit has the capability. $needed = array(); } else { if (empty($needed)) { $nobody = true; } } } } else { if (!empty($prohibited[$defaultuserroleid])) { $nobody = true; } else { if (!empty($needed[$defaultuserroleid])) { // Everybody not having prohibit has the capability. $needed = array(); } else { if (empty($needed)) { $nobody = true; } } } } if ($nobody) { // Nobody can match so return some SQL that does not return any results. $wheres[] = "1 = 2"; } else { if ($needed) { $ctxids = implode(',', $contextids); $roleids = implode(',', array_keys($needed)); $joins[] = "JOIN {role_assignments} {$prefix}ra3\n ON ({$prefix}ra3.userid = {$useridcolumn}\n AND {$prefix}ra3.roleid IN ({$roleids})\n AND {$prefix}ra3.contextid IN ({$ctxids}))"; } if ($prohibited) { $ctxids = implode(',', $contextids); $roleids = implode(',', array_keys($prohibited)); $joins[] = "LEFT JOIN {role_assignments} {$prefix}ra4\n ON ({$prefix}ra4.userid = {$useridcolumn}\n AND {$prefix}ra4.roleid IN ({$roleids})\n AND {$prefix}ra4.contextid IN ({$ctxids}))"; $wheres[] = "{$prefix}ra4.id IS NULL"; } } $wheres[] = "{$useridcolumn} <> :{$prefix}guestid"; $params["{$prefix}guestid"] = $CFG->siteguest; $joins = implode("\n", $joins); $wheres = "(" . implode(" AND ", $wheres) . ")"; return new \core\dml\sql_join($joins, $wheres, $params); }