class.ilRbacReview.php

Go to the documentation of this file.

<?php
/* Copyright (c) 1998-2010 ILIAS open source, Extended GPL, see docs/LICENSE */

class ilRbacReview
{
    const FILTER_ALL = 1;
    const FILTER_ALL_GLOBAL = 2;
    const FILTER_ALL_LOCAL = 3;
    const FILTER_INTERNAL = 4;
    const FILTER_NOT_INTERNAL = 5;
    const FILTER_TEMPLATES = 6;

    // Cache operation ids
    private static $_opsCache = null;

    protected static $assigned_users_cache = [];

    protected static $is_assigned_cache = [];

    protected $log;

    public function __construct()
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];
        $ilErr = $DIC['ilErr'];
        $ilias = $DIC['ilias'];

        $this->log = ilLoggerFactory::getLogger('ac');

        // set db & error handler
        (isset($ilDB)) ? $this->ilDB = &$ilDB : $this->ilDB = &$ilias->db;

        if (!isset($ilErr)) {
            $ilErr = new ilErrorHandling();
            $ilErr->setErrorHandling(PEAR_ERROR_CALLBACK, array($ilErr,'errorHandler'));
        } else {
            $this->ilErr = &$ilErr;
        }
    }

    public function roleExists($a_title, $a_id = 0)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        if (empty($a_title)) {
            $message = get_class($this) . "::roleExists(): No title given!";
            $this->ilErr->raiseError($message, $this->ilErr->WARNING);
        }

        $clause = ($a_id) ? " AND obj_id != " . $ilDB->quote($a_id) . " " : "";

        $q = "SELECT DISTINCT(obj_id) obj_id FROM object_data " .
             "WHERE title =" . $ilDB->quote($a_title) . " " .
             "AND type IN('role','rolt')" .
             $clause . " ";
        $r = $this->ilDB->query($q);

        while ($row = $r->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            return $row->obj_id;
        }
        return false;
    }

    protected function __getParentRoles($a_path, $a_templates)
    {
        if (!isset($a_path) or !is_array($a_path)) {
            $message = get_class($this) . "::getParentRoles(): No path given or wrong datatype!";
            $this->ilErr->raiseError($message, $this->ilErr->WARNING);
        }

        $parent_roles = [];
        $role_hierarchy = [];

        foreach ($a_path as $ref_id) {
            $roles = $this->getRoleListByObject($ref_id, $a_templates);
            foreach ($roles as $role) {
                $id = $role["obj_id"];
                $role["parent"] = $ref_id;
                $parent_roles[$id] = $role;

                if (!array_key_exists($role['obj_id'], $role_hierarchy)) {
                    $role_hierarchy[$id] = $ref_id;
                }
            }
        }
        return $this->__setProtectedStatus($parent_roles, $role_hierarchy, reset($a_path));
    }

    public function getParentRoleIds($a_endnode_id, $a_templates = false)
    {
        global $DIC;

        $tree = $DIC['tree'];

        if (!isset($a_endnode_id)) {
            $GLOBALS['DIC']['ilLog']->logStack();
            $message = get_class($this) . "::getParentRoleIds(): No node_id (ref_id) given!";
            $this->ilErr->raiseError($message, $this->ilErr->WARNING);
        }

        $pathIds = $tree->getPathId($a_endnode_id);

        // add system folder since it may not in the path
        $pathIds[0] = ROLE_FOLDER_ID;
        return $this->__getParentRoles($pathIds, $a_templates);
    }

    public function getRoleListByObject($a_ref_id, $a_templates = false)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        if (!isset($a_ref_id) or !isset($a_templates)) {
            $message = get_class($this) . "::getRoleListByObject(): Missing parameter!" .
                       "ref_id: " . $a_ref_id .
                       "tpl_flag: " . $a_templates;
            $this->ilErr->raiseError($message, $this->ilErr->WARNING);
        }

        $role_list = [];

        $where = $this->__setTemplateFilter($a_templates);

        $query = "SELECT * FROM object_data " .
             "JOIN rbac_fa ON obj_id = rol_id " .
             $where .
             "AND object_data.obj_id = rbac_fa.rol_id " .
             "AND rbac_fa.parent = " . $ilDB->quote($a_ref_id, 'integer') . " ";

        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchAssoc($res)) {
            $row["desc"] = $row["description"];
            $row["user_id"] = $row["owner"];
            $role_list[] = $row;
        }

        $role_list = $this->__setRoleType($role_list);

        return $role_list;
    }

    public function getAssignableRoles($a_templates = false, $a_internal_roles = false, $title_filter = '')
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $role_list = [];

        $where = $this->__setTemplateFilter($a_templates);

        $query = "SELECT * FROM object_data " .
             "JOIN rbac_fa ON obj_id = rol_id " .
             $where .
             "AND rbac_fa.assign = 'y' ";

        if (strlen($title_filter)) {
            $query .= (' AND ' . $ilDB->like(
                'title',
                'text',
                $title_filter . '%'
            ));
        }
        $res = $ilDB->query($query);

        while ($row = $ilDB->fetchAssoc($res)) {
            $row["desc"] = $row["description"];
            $row["user_id"] = $row["owner"];
            $role_list[] = $row;
        }

        $role_list = $this->__setRoleType($role_list);

        return $role_list;
    }

    public function getAssignableRolesInSubtree($ref_id)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $query = 'SELECT rol_id FROM rbac_fa fa ' .
                'JOIN tree t1 ON t1.child = fa.parent ' .
                'JOIN object_data obd ON fa.rol_id = obd.obj_id ' .
                'WHERE assign = ' . $ilDB->quote('y', 'text') . ' ' .
                'AND obd.type = ' . $ilDB->quote('role', 'text') . ' ' .
                'AND t1.child IN (' .
                $GLOBALS['DIC']['tree']->getSubTreeQuery($ref_id, array('child')) . ' ' .
                ') ';


        $res = $ilDB->query($query);

        $role_list = [];
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            $role_list[] = $row->rol_id;
        }
        return $role_list;
    }

    public function getAssignableChildRoles($a_ref_id)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $query = "SELECT fa.*, rd.* " .
             "FROM object_data rd " .
             "JOIN rbac_fa fa ON rd.obj_id = fa.rol_id " .
             "WHERE fa.assign = 'y' " .
             "AND fa.parent = " . $this->ilDB->quote($a_ref_id, 'integer') . " "
            ;

        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchAssoc($res)) {
            $roles_data[] = $row;
        }
        return $roles_data ? $roles_data : [];
    }

    protected function __setTemplateFilter($a_templates)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        if ($a_templates === true) {
            $where = "WHERE " . $ilDB->in('object_data.type', array('role','rolt'), false, 'text') . " ";
        } else {
            $where = "WHERE " . $ilDB->in('object_data.type', array('role'), false, 'text') . " ";
        }

        return $where;
    }

    protected function __setRoleType($a_role_list)
    {
        foreach ($a_role_list as $key => $val) {
            // determine role type
            if ($val["type"] == "rolt") {
                $a_role_list[$key]["role_type"] = "template";
            } else {
                if ($val["assign"] == "y") {
                    if ($val["parent"] == ROLE_FOLDER_ID) {
                        $a_role_list[$key]["role_type"] = "global";
                    } else {
                        $a_role_list[$key]["role_type"] = "local";
                    }
                } else {
                    $a_role_list[$key]["role_type"] = "linked";
                }
            }

            if ($val["protected"] == "y") {
                $a_role_list[$key]["protected"] = true;
            } else {
                $a_role_list[$key]["protected"] = false;
            }
        }

        return $a_role_list;
    }

    public function getNumberOfAssignedUsers(array $a_roles)
    {
        global $DIC;

        $ilDB = $DIC->database();

        $query = 'select count(distinct(ua.usr_id)) as num from rbac_ua ua ' .
            'join object_data on ua.usr_id = obj_id ' .
            'join usr_data ud on ua.usr_id = ud.usr_id ' .
            'where ' . $ilDB->in('rol_id', $a_roles, false, 'integer');

        $res = $ilDB->query($query);
        if ($res->numRows()) {
            $row = $res->fetchRow(\ilDBConstants::FETCHMODE_OBJECT);
            return $row->num;
        }
        return 0;
    }


    public function assignedUsers($a_rol_id)
    {
        global $DIC;

        $ilBench = $DIC['ilBench'];
        $ilDB = $DIC['ilDB'];

        if (!isset($a_rol_id)) {
            $message = get_class($this) . "::assignedUsers(): No role_id given!";
            $this->ilErr->raiseError($message, $this->ilErr->WARNING);
        }
        if (isset(self::$assigned_users_cache[$a_rol_id])) {
            return self::$assigned_users_cache[$a_rol_id];
        }

        $result_arr = [];

        $query = "SELECT usr_id FROM rbac_ua WHERE rol_id= " . $ilDB->quote($a_rol_id, 'integer');
        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchAssoc($res)) {
            array_push($result_arr, $row["usr_id"]);
        }

        self::$assigned_users_cache[$a_rol_id] = $result_arr;

        return $result_arr;
    }


    public function isAssigned($a_usr_id, $a_role_id)
    {
        if (isset(self::$is_assigned_cache[$a_role_id][$a_usr_id])) {
            return self::$is_assigned_cache[$a_role_id][$a_usr_id];
        }
        // Quickly determine if user is assigned to a role
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $ilDB->setLimit(1, 0);
        $query = "SELECT usr_id FROM rbac_ua WHERE " .
                    "rol_id= " . $ilDB->quote($a_role_id, 'integer') . " " .
                    "AND usr_id= " . $ilDB->quote($a_usr_id);
        $res = $ilDB->query($query);

        $is_assigned = $res->numRows() == 1;
        self::$is_assigned_cache[$a_role_id][$a_usr_id] = $is_assigned;

        return $is_assigned;
    }

    public function isAssignedToAtLeastOneGivenRole($a_usr_id, $a_role_ids)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $ilDB->setLimit(1, 0);
        $query = "SELECT usr_id FROM rbac_ua WHERE " .
                    $ilDB->in('rol_id', $a_role_ids, false, 'integer') .
                    " AND usr_id= " . $ilDB->quote($a_usr_id);
        $res = $ilDB->query($query);

        return $ilDB->numRows($res) == 1;
    }

    public function assignedRoles($a_usr_id)
    {
        global $DIC;

        $ilDB = $DIC->database();

        $role_arr = [];
        $query = "SELECT rol_id FROM rbac_ua WHERE usr_id = " . $ilDB->quote($a_usr_id, 'integer');

        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchObject($res)) {
            $role_arr[] = $row->rol_id;
        }
        return $role_arr;
    }

    public function assignedGlobalRoles($a_usr_id)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $query = "SELECT ua.rol_id FROM rbac_ua ua " .
            "JOIN rbac_fa fa ON ua.rol_id = fa.rol_id " .
            "WHERE usr_id = " . $ilDB->quote($a_usr_id, 'integer') . ' ' .
            "AND parent = " . $ilDB->quote(ROLE_FOLDER_ID) . " " .
            "AND assign = 'y' ";

        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchObject($res)) {
            $role_arr[] = $row->rol_id;
        }
        return $role_arr ? $role_arr : [];
    }

    public function isAssignable($a_rol_id, $a_ref_id)
    {
        global $DIC;

        $ilBench = $DIC['ilBench'];
        $ilDB = $DIC['ilDB'];

        $ilBench->start("RBAC", "review_isAssignable");

        // exclude system role from rbac
        if ($a_rol_id == SYSTEM_ROLE_ID) {
            $ilBench->stop("RBAC", "review_isAssignable");
            return true;
        }

        if (!isset($a_rol_id) or !isset($a_ref_id)) {
            $message = get_class($this) . "::isAssignable(): Missing parameter!" .
                       " role_id: " . $a_rol_id . " ,ref_id: " . $a_ref_id;
            $this->ilErr->raiseError($message, $this->ilErr->WARNING);
        }
        $query = "SELECT * FROM rbac_fa " .
             "WHERE rol_id = " . $ilDB->quote($a_rol_id, 'integer') . " " .
             "AND parent = " . $ilDB->quote($a_ref_id, 'integer') . " ";
        $res = $ilDB->query($query);
        $row = $ilDB->fetchObject($res);

        $ilBench->stop("RBAC", "review_isAssignable");
        return $row->assign == 'y' ? true : false;
    }

    public function hasMultipleAssignments($a_role_id)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $query = "SELECT * FROM rbac_fa WHERE rol_id = " . $ilDB->quote($a_role_id, 'integer') . ' ' .
            "AND assign = " . $ilDB->quote('y', 'text');
        $res = $ilDB->query($query);
        return $res->numRows() > 1;
    }

    public function getFoldersAssignedToRole($a_rol_id, $a_assignable = false)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        if (!isset($a_rol_id)) {
            $message = get_class($this) . "::getFoldersAssignedToRole(): No role_id given!";
            $this->ilErr->raiseError($message, $this->ilErr->WARNING);
        }

        if ($a_assignable) {
            $where = " AND assign ='y'";
        }

        $query = "SELECT DISTINCT parent FROM rbac_fa " .
             "WHERE rol_id = " . $ilDB->quote($a_rol_id, 'integer') . " " . $where . " ";

        $res = $ilDB->query($query);
        $folders = [];
        while ($row = $ilDB->fetchObject($res)) {
            $folders[] = $row->parent;
        }
        return $folders;
    }

    public function getRolesOfObject($a_ref_id, $a_assignable_only = false)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        if (!isset($a_ref_id)) {
            $GLOBALS['DIC']['ilLog']->logStack();
            throw new InvalidArgumentException(__METHOD__ . ': No ref_id given!');
        }
        if ($a_assignable_only === true) {
            $and = 'AND assign = ' . $ilDB->quote('y', 'text');
        }
        $query = "SELECT rol_id FROM rbac_fa " .
             "WHERE parent = " . $ilDB->quote($a_ref_id, 'integer') . " " .
             $and;

        $res = $ilDB->query($query);

        $role_ids = [];
        while ($row = $ilDB->fetchObject($res)) {
            $role_ids[] = $row->rol_id;
        }
        return $role_ids;
    }




    public function getRolesOfRoleFolder($a_ref_id, $a_nonassignable = true)
    {
        global $DIC;

        $ilBench = $DIC['ilBench'];
        $ilDB = $DIC['ilDB'];
        $ilLog = $DIC['ilLog'];

        $ilBench->start("RBAC", "review_getRolesOfRoleFolder");

        if (!isset($a_ref_id)) {
            $message = get_class($this) . "::getRolesOfRoleFolder(): No ref_id given!";
            ilLoggerFactory::getLogger('ac')->logStack();
            $this->ilErr->raiseError($message, $this->ilErr->WARNING);
        }

        if ($a_nonassignable === false) {
            $and = " AND assign='y'";
        }

        $query = "SELECT rol_id FROM rbac_fa " .
             "WHERE parent = " . $ilDB->quote($a_ref_id, 'integer') . " " .
             $and;

        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchObject($res)) {
            $rol_id[] = $row->rol_id;
        }

        $ilBench->stop("RBAC", "review_getRolesOfRoleFolder");

        return $rol_id ? $rol_id : [];
    }

    public function getGlobalRoles()
    {
        return $this->getRolesOfRoleFolder(ROLE_FOLDER_ID, false);
    }

    public function getLocalRoles($a_ref_id)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $lroles = [];
        foreach ($this->getRolesOfRoleFolder($a_ref_id) as $role_id) {
            if ($this->isAssignable($role_id, $a_ref_id)) {
                $lroles[] = $role_id;
            }
        }
        return $lroles;
    }

    public function getLocalPolicies($a_ref_id)
    {
        $lroles = [];
        foreach ($this->getRolesOfRoleFolder($a_ref_id) as $role_id) {
            $lroles[] = $role_id;
        }
        return $lroles;
    }

    public function getGlobalRolesArray()
    {
        foreach ($this->getRolesOfRoleFolder(ROLE_FOLDER_ID, false) as $role_id) {
            $ga[] = array('obj_id' => $role_id,
                          'role_type' => 'global');
        }
        return $ga ? $ga : [];
    }

    public function getGlobalAssignableRoles()
    {
        include_once './Services/AccessControl/classes/class.ilObjRole.php';

        foreach ($this->getGlobalRoles() as $role_id) {
            if (ilObjRole::_getAssignUsersStatus($role_id)) {
                $ga[] = array('obj_id' => $role_id,
                              'role_type' => 'global');
            }
        }
        return $ga ? $ga : [];
    }


    public function isRoleAssignedToObject($a_role_id, $a_parent_id)
    {
        global $DIC;

        $rbacreview = $DIC['rbacreview'];
        $ilDB = $DIC['ilDB'];

        $query = 'SELECT * FROM rbac_fa ' .
            'WHERE rol_id = ' . $ilDB->quote($a_role_id, 'integer') . ' ' .
            'AND parent = ' . $ilDB->quote($a_parent_id, 'integer');
        $res = $ilDB->query($query);
        return $res->numRows() ? true : false;
    }

    public function getOperations()
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $query = 'SELECT * FROM rbac_operations ORDER BY ops_id ';
        $res = $this->ilDB->query($query);
        while ($row = $ilDB->fetchObject($res)) {
            $ops[] = array('ops_id' => $row->ops_id,
                           'operation' => $row->operation,
                           'description' => $row->description);
        }

        return $ops ? $ops : [];
    }

    public function getOperation($ops_id)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $query = 'SELECT * FROM rbac_operations WHERE ops_id = ' . $ilDB->quote($ops_id, 'integer');
        $res = $this->ilDB->query($query);
        while ($row = $ilDB->fetchObject($res)) {
            $ops = array('ops_id' => $row->ops_id,
                         'operation' => $row->operation,
                         'description' => $row->description);
        }

        return $ops ? $ops : [];
    }

    public function getAllOperationsOfRole($a_rol_id, $a_parent = 0)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        if (!$a_parent) {
            $a_parent = ROLE_FOLDER_ID;
        }

        $query = "SELECT ops_id,type FROM rbac_templates " .
            "WHERE rol_id = " . $ilDB->quote($a_rol_id, 'integer') . " " .
            "AND parent = " . $ilDB->quote($a_parent, 'integer');
        $res = $ilDB->query($query);

        $ops_arr = [];
        while ($row = $ilDB->fetchObject($res)) {
            $ops_arr[$row->type][] = $row->ops_id;
        }
        return (array) $ops_arr;
    }

    public function getActiveOperationsOfRole($a_ref_id, $a_role_id)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $query = 'SELECT * FROM rbac_pa ' .
            'WHERE ref_id = ' . $ilDB->quote($a_ref_id, 'integer') . ' ' .
            'AND rol_id = ' . $ilDB->quote($a_role_id, 'integer') . ' ';

        $res = $ilDB->query($query);
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_ASSOC)) {
            return unserialize($row['ops_id']);
        }
        return [];
    }


    public function getOperationsOfRole($a_rol_id, $a_type, $a_parent = 0)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];
        $ilLog = $DIC['ilLog'];

        if (!isset($a_rol_id) or !isset($a_type)) {
            $message = get_class($this) . "::getOperationsOfRole(): Missing Parameter!" .
                       "role_id: " . $a_rol_id .
                       "type: " . $a_type .
                       "parent_id: " . $a_parent;
            $ilLog->logStack("Missing parameter! ");
            $this->ilErr->raiseError($message, $this->ilErr->WARNING);
        }

        $ops_arr = [];

        // if no rolefolder id is given, assume global role folder as target
        if ($a_parent == 0) {
            $a_parent = ROLE_FOLDER_ID;
        }

        $query = "SELECT ops_id FROM rbac_templates " .
             "WHERE type =" . $ilDB->quote($a_type, 'text') . " " .
             "AND rol_id = " . $ilDB->quote($a_rol_id, 'integer') . " " .
             "AND parent = " . $ilDB->quote($a_parent, 'integer');
        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchObject($res)) {
            $ops_arr[] = $row->ops_id;
        }

        return $ops_arr;
    }

    public function getRoleOperationsOnObject($a_role_id, $a_ref_id)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $query = "SELECT * FROM rbac_pa " .
            "WHERE rol_id = " . $ilDB->quote($a_role_id, 'integer') . " " .
            "AND ref_id = " . $ilDB->quote($a_ref_id, 'integer') . " ";

        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchObject($res)) {
            $ops = unserialize($row->ops_id);
        }

        return $ops ? $ops : [];
    }

    public function getOperationsOnType($a_typ_id)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        if (!isset($a_typ_id)) {
            $message = get_class($this) . "::getOperationsOnType(): No type_id given!";
            $this->ilErr->raiseError($message, $this->ilErr->WARNING);
        }

        #$query = "SELECT * FROM rbac_ta WHERE typ_id = ".$ilDB->quote($a_typ_id,'integer');

        $query = 'SELECT * FROM rbac_ta ta JOIN rbac_operations o ON ta.ops_id = o.ops_id ' .
            'WHERE typ_id = ' . $ilDB->quote($a_typ_id, 'integer') . ' ' .
            'ORDER BY op_order';

        $res = $ilDB->query($query);

        while ($row = $ilDB->fetchObject($res)) {
            $ops_id[] = $row->ops_id;
        }

        return $ops_id ? $ops_id : [];
    }

    public function getOperationsOnTypeString($a_type)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $query = "SELECT * FROM object_data WHERE type = 'typ' AND title = " . $ilDB->quote($a_type, 'text') . " ";


        $res = $this->ilDB->query($query);
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            return $this->getOperationsOnType($row->obj_id);
        }
        return false;
    }

    public function getOperationsByTypeAndClass($a_type, $a_class)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        if ($a_class != 'create') {
            $condition = "AND class != " . $ilDB->quote('create', 'text');
        } else {
            $condition = "AND class = " . $ilDB->quote('create', 'text');
        }

        $query = "SELECT ro.ops_id FROM rbac_operations ro " .
            "JOIN rbac_ta rt ON  ro.ops_id = rt.ops_id " .
            "JOIN object_data od ON rt.typ_id = od.obj_id " .
            "WHERE type = " . $ilDB->quote('typ', 'text') . " " .
            "AND title = " . $ilDB->quote($a_type, 'text') . " " .
            $condition . " " .
            "ORDER BY op_order ";

        $res = $ilDB->query($query);

        $ops = [];
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            $ops[] = $row->ops_id;
        }
        return $ops;
    }


    public function getObjectsWithStopedInheritance($a_rol_id, $a_filter = [])
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        #$query = 'SELECT t.parent p FROM tree t JOIN rbac_fa fa ON fa.parent = child '.
        #       'WHERE assign = '.$ilDB->quote('n','text').' '.
        #       'AND rol_id = '.$ilDB->quote($a_rol_id,'integer').' ';

        $query = 'SELECT parent p FROM rbac_fa ' .
                'WHERE assign = ' . $ilDB->quote('n', 'text') . ' ' .
                'AND rol_id = ' . $ilDB->quote($a_rol_id, 'integer') . ' ';

        if ($a_filter) {
            $query .= ('AND ' . $ilDB->in('parent', (array) $a_filter, false, 'integer'));
        }

        $res = $ilDB->query($query);
        $parent = [];
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            $parent[] = $row->p;
        }
        return $parent;
    }

    public function isDeleted($a_node_id)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $q = "SELECT tree FROM tree WHERE child =" . $ilDB->quote($a_node_id) . " ";
        $r = $this->ilDB->query($q);

        $row = $r->fetchRow(ilDBConstants::FETCHMODE_OBJECT);

        if (!$row) {
            $message = sprintf(
                '%s::isDeleted(): Role folder with ref_id %s not found!',
                get_class($this),
                $a_node_id
            );
            $this->log->write($message, $this->log->FATAL);

            return true;
        }

        // rolefolder is deleted
        if ($row->tree < 0) {
            return true;
        }

        return false;
    }

    public function isGlobalRole($a_role_id)
    {
        return in_array($a_role_id, $this->getGlobalRoles());
    }

    public function getRolesByFilter($a_filter = 0, $a_user_id = 0, $title_filter = '')
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $assign = "y";

        switch ($a_filter) {
            // all (assignable) roles
            case self::FILTER_ALL:
                return $this->getAssignableRoles(true, true, $title_filter);
                break;

            // all (assignable) global roles
            case self::FILTER_ALL_GLOBAL:
                $where = 'WHERE ' . $ilDB->in('rbac_fa.rol_id', $this->getGlobalRoles(), false, 'integer') . ' ';
                break;

            // all (assignable) local roles
            case self::FILTER_ALL_LOCAL:
            case self::FILTER_INTERNAL:
            case self::FILTER_NOT_INTERNAL:
                $where = 'WHERE ' . $ilDB->in('rbac_fa.rol_id', $this->getGlobalRoles(), true, 'integer');
                break;

            // all role templates
            case self::FILTER_TEMPLATES:
                $where = "WHERE object_data.type = 'rolt'";
                $assign = "n";
                break;

            // only assigned roles, handled by ilObjUserGUI::roleassignmentObject()
            case 0:
            default:
                if (!$a_user_id) {
                    return [];
                }

                $where = 'WHERE ' . $ilDB->in('rbac_fa.rol_id', $this->assignedRoles($a_user_id), false, 'integer') . ' ';
                break;
        }

        $roles = [];

        $query = "SELECT * FROM object_data " .
             "JOIN rbac_fa ON obj_id = rol_id " .
             $where .
             "AND rbac_fa.assign = " . $ilDB->quote($assign, 'text') . " ";

        if (strlen($title_filter)) {
            $query .= (' AND ' . $ilDB->like(
                'title',
                'text',
                '%' . $title_filter . '%'
            ));
        }

        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchAssoc($res)) {
            $prefix = (substr($row["title"], 0, 3) == "il_") ? true : false;

            // all (assignable) internal local roles only
            if ($a_filter == 4 and !$prefix) {
                continue;
            }

            // all (assignable) non internal local roles only
            if ($a_filter == 5 and $prefix) {
                continue;
            }

            $row["desc"] = $row["description"];
            $row["user_id"] = $row["owner"];
            $roles[] = $row;
        }

        $roles = $this->__setRoleType($roles);

        return $roles ? $roles : [];
    }

    public function getTypeId($a_type)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $q = "SELECT obj_id FROM object_data " .
             "WHERE title=" . $ilDB->quote($a_type, 'text') . " AND type='typ'";
        $r = $ilDB->query($q);

        $row = $r->fetchRow(ilDBConstants::FETCHMODE_OBJECT);
        return $row->obj_id;
    }

    public static function _getOperationIdsByName($operations)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        if (!count($operations)) {
            return [];
        }

        $query = 'SELECT ops_id FROM rbac_operations ' .
            'WHERE ' . $ilDB->in('operation', $operations, false, 'text');

        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchObject($res)) {
            $ops_ids[] = $row->ops_id;
        }
        return $ops_ids ? $ops_ids : [];
    }

    public static function _getOperationIdByName($a_operation)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];
        $ilErr = $DIC['ilErr'];

        if (!isset($a_operation)) {
            $message = "perm::getOperationId(): No operation given!";
            $ilErr->raiseError($message, $ilErr->WARNING);
        }

        // Cache operation ids
        if (!is_array(self::$_opsCache)) {
            self::$_opsCache = [];

            $q = "SELECT ops_id, operation FROM rbac_operations";
            $r = $ilDB->query($q);
            while ($row = $r->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
                self::$_opsCache[$row->operation] = $row->ops_id;
            }
        }

        // Get operation ID by name from cache
        if (array_key_exists($a_operation, self::$_opsCache)) {
            return self::$_opsCache[$a_operation];
        }
        return null;
    }

    public static function lookupCreateOperationIds($a_type_arr)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $operations = [];
        foreach ($a_type_arr as $type) {
            $operations[] = ('create_' . $type);
        }

        if (!count($operations)) {
            return [];
        }

        $query = 'SELECT ops_id, operation FROM rbac_operations ' .
            'WHERE ' . $ilDB->in('operation', $operations, false, 'text');

        $res = $ilDB->query($query);

        $ops_ids = [];
        while ($row = $ilDB->fetchObject($res)) {
            $type_arr = explode('_', $row->operation);
            $type = $type_arr[1];

            $ops_ids[$type] = $row->ops_id;
        }
        return $ops_ids;
    }



    public function isProtected($a_ref_id, $a_role_id)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        // ref_id not used yet. protected permission acts 'global' for each role,
        $query = "SELECT protected FROM rbac_fa " .
             "WHERE rol_id = " . $ilDB->quote($a_role_id, 'integer') . " ";
        $res = $ilDB->query($query);
        $row = $ilDB->fetchAssoc($res);

        return ilUtil::yn2tf($row['protected']);
    }

    public function isBlockedAtPosition($a_role_id, $a_ref_id)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $query = 'SELECT blocked from rbac_fa ' .
                'WHERE rol_id = ' . $ilDB->quote($a_role_id, 'integer') . ' ' .
                'AND parent = ' . $ilDB->quote($a_ref_id, 'integer');
        $res = $ilDB->query($query);
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            return (bool) $row->blocked;
        }
        return false;
    }

    public function isBlockedInUpperContext($a_role_id, $a_ref_id)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];
        $tree = $DIC['tree'];

        if ($this->isBlockedAtPosition($a_role_id, $a_ref_id)) {
            return false;
        }
        $query = 'SELECT parent from rbac_fa ' .
                'WHERE rol_id = ' . $ilDB->quote($a_role_id, 'integer') . ' ' .
                'AND blocked = ' . $ilDB->quote(1, 'integer');
        $res = $ilDB->query($query);

        $parent_ids = [];
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            $parent_ids[] = $row->parent;
        }

        foreach ($parent_ids as $parent_id) {
            if ($tree->isGrandChild($parent_id, $a_ref_id)) {
                return true;
            }
        }
        return false;
    }

    // this method alters the protected status of role regarding the current user's role assignment
    // and current postion in the hierarchy.

    protected function __setProtectedStatus($a_parent_roles, $a_role_hierarchy, $a_ref_id)
    {
        global $DIC;

        $rbacsystem = $DIC['rbacsystem'];
        $ilUser = $DIC['ilUser'];
        $log = $DIC['log'];

        if (in_array(SYSTEM_ROLE_ID, $this->assignedRoles($ilUser->getId()))) {
            $leveladmin = true;
        } else {
            $leveladmin = false;
        }

        foreach ($a_role_hierarchy as $role_id => $rolf_id) {
            if ($leveladmin == true) {
                $a_parent_roles[$role_id]['protected'] = false;
                continue;
            }

            if ($a_parent_roles[$role_id]['protected'] == true) {
                $arr_lvl_roles_user = array_intersect($this->assignedRoles($ilUser->getId()), array_keys($a_role_hierarchy, $rolf_id));

                foreach ($arr_lvl_roles_user as $lvl_role_id) {
                    // check if role grants 'edit_permission' to parent
                    $rolf = $a_parent_roles[$role_id]['parent'];
                    if ($rbacsystem->checkPermission($rolf, $lvl_role_id, 'edit_permission')) {
                        // user may change permissions of that higher-ranked role
                        $a_parent_roles[$role_id]['protected'] = false;
                    }
                }
            }
        }
        return $a_parent_roles;
    }

    public static function _getOperationList($a_type = null)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $arr = [];

        if ($a_type) {
            $query = sprintf(
                'SELECT * FROM rbac_operations ' .
                'JOIN rbac_ta ON rbac_operations.ops_id = rbac_ta.ops_id ' .
                'JOIN object_data ON rbac_ta.typ_id = object_data.obj_id ' .
                'WHERE object_data.title = %s ' .
                'AND object_data.type = %s ' .
                'ORDER BY op_order ASC',
                $ilDB->quote($a_type, 'text'),
                $ilDB->quote('typ', 'text')
            );
        } else {
            $query = 'SELECT * FROM rbac_operations ORDER BY op_order ASC';
        }
        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchAssoc($res)) {
            $arr[] = array(
                        "ops_id" => $row['ops_id'],
                        "operation" => $row['operation'],
                        "desc" => $row['description'],
                        "class" => $row['class'],
                        "order" => $row['op_order']
                        );
        }
        return $arr;
    }

    public static function _groupOperationsByClass($a_ops_arr)
    {
        $arr = [];

        foreach ($a_ops_arr as $ops) {
            $arr[$ops['class']][] = array('ops_id' => $ops['ops_id'],
                                           'name' => $ops['operation']
                                         );
        }
        return $arr;
    }

    public function getObjectOfRole($a_role_id)
    {
        // internal cache
        static $obj_cache = [];

        global $DIC;

        $ilDB = $DIC['ilDB'];


        if (isset($obj_cache[$a_role_id]) and $obj_cache[$a_role_id]) {
            return $obj_cache[$a_role_id];
        }

        $query = 'SELECT obr.obj_id FROM rbac_fa rfa ' .
                'JOIN object_reference obr ON rfa.parent = obr.ref_id ' .
                'WHERE assign = ' . $ilDB->quote('y', 'text') . ' ' .
                'AND rol_id = ' . $ilDB->quote($a_role_id, 'integer') . ' ' .
                'AND deleted IS NULL';

        #$query = "SELECT obr.obj_id FROM rbac_fa rfa ".
        #       "JOIN tree ON rfa.parent = tree.child ".
        #       "JOIN object_reference obr ON tree.parent = obr.ref_id ".
        #       "WHERE tree.tree = 1 ".
        #       "AND assign = 'y' ".
        #       "AND rol_id = ".$ilDB->quote($a_role_id,'integer')." ";
        $res = $ilDB->query($query);

        $obj_cache[$a_role_id] = 0;
        while ($row = $ilDB->fetchObject($res)) {
            $obj_cache[$a_role_id] = $row->obj_id;
        }
        return $obj_cache[$a_role_id];
    }

    public function getObjectReferenceOfRole($a_role_id)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $query = 'SELECT parent p_ref FROM rbac_fa ' .
                'WHERE rol_id = ' . $ilDB->quote($a_role_id, 'integer') . ' ' .
                'AND assign = ' . $ilDB->quote('y', 'text');

        $res = $ilDB->query($query);
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            return $row->p_ref;
        }
        return 0;
    }

    public function isRoleDeleted($a_role_id)
    {
        $rolf_list = $this->getFoldersAssignedToRole($a_role_id, false);
        $deleted = true;
        if (count($rolf_list)) {
            foreach ($rolf_list as $rolf) {
                // only list roles that are not set to status "deleted"
                if (!$this->isDeleted($rolf)) {
                    $deleted = false;
                    break;
                }
            }
        }
        return $deleted;
    }


    public function getRolesForIDs($role_ids, $use_templates)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $role_list = [];

        $where = $this->__setTemplateFilter($use_templates);

        $query = "SELECT * FROM object_data " .
             "JOIN rbac_fa ON object_data.obj_id = rbac_fa.rol_id " .
             $where .
             "AND rbac_fa.assign = 'y' " .
             'AND ' . $ilDB->in('object_data.obj_id', $role_ids, false, 'integer');

        $res = $ilDB->query($query);
        while ($row = $ilDB->fetchAssoc($res)) {
            $row["desc"] = $row["description"];
            $row["user_id"] = $row["owner"];
            $role_list[] = $row;
        }

        $role_list = $this->__setRoleType($role_list);
        return $role_list;
    }

    public function getOperationAssignment()
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $query = 'SELECT ta.typ_id, obj.title, ops.ops_id, ops.operation FROM rbac_ta ta ' .
             'JOIN object_data obj ON obj.obj_id = ta.typ_id ' .
             'JOIN rbac_operations ops ON ops.ops_id = ta.ops_id ';
        $res = $ilDB->query($query);

        $counter = 0;
        while ($row = $ilDB->fetchObject($res)) {
            $info[$counter]['typ_id'] = $row->typ_id;
            $info[$counter]['type'] = $row->title;
            $info[$counter]['ops_id'] = $row->ops_id;
            $info[$counter]['operation'] = $row->operation;
            $counter++;
        }
        return $info ? $info : [];
    }

    public function isDeleteable($a_role_id, $a_rolf_id)
    {
        if (!$this->isAssignable($a_role_id, $a_rolf_id)) {
            return false;
        }
        if ($a_role_id == SYSTEM_ROLE_ID or $a_role_id == ANONYMOUS_ROLE_ID) {
            return false;
        }
        if (substr(ilObject::_lookupTitle($a_role_id), 0, 3) == 'il_') {
            return false;
        }
        return true;
    }

    public function isSystemGeneratedRole($a_role_id)
    {
        $title = ilObject::_lookupTitle($a_role_id);
        return substr($title, 0, 3) == 'il_' ? true : false;
    }


    public function getParentOfRole(int $role_id, ?int $object_ref = null) : ?int
    {
        global $DIC;
        $tree = $DIC['tree'];

        if ($object_ref === null || $object_ref === ROLE_FOLDER_ID) {
            return $this->getRoleFolderOfRole($role_id);
        }


        $path_ids = $tree->getPathId($object_ref);
        array_unshift($path_ids, ROLE_FOLDER_ID);

        while ($ref_id = array_pop($path_ids)) {
            $roles = $this->getRoleListByObject($ref_id, false);
            foreach ($roles as $role) {
                if ((int) $role['obj_id'] === $role_id) {
                    return $ref_id;
                }
            }
        }

        return null;
    }


    public function getRoleFolderOfRole($a_role_id)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        if (ilObject::_lookupType($a_role_id) == 'role') {
            $and = ('AND assign = ' . $ilDB->quote('y', 'text'));
        } else {
            $and = '';
        }

        $query = 'SELECT * FROM rbac_fa ' .
            'WHERE rol_id = ' . $ilDB->quote($a_role_id, 'integer') . ' ' .
            $and;
        $res = $ilDB->query($query);
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            return $row->parent;
        }
        return 0;
    }

    public function getUserPermissionsOnObject($a_user_id, $a_ref_id)
    {
        global $DIC;

        $ilDB = $DIC['ilDB'];

        $query = "SELECT ops_id FROM rbac_pa JOIN rbac_ua " .
            "ON (rbac_pa.rol_id = rbac_ua.rol_id) " .
            "WHERE rbac_ua.usr_id = " . $ilDB->quote($a_user_id, 'integer') . " " .
            "AND rbac_pa.ref_id = " . $ilDB->quote($a_ref_id, 'integer') . " ";

        $res = $ilDB->query($query);
        $all_ops = [];
        while ($row = $ilDB->fetchObject($res)) {
            $ops = unserialize($row->ops_id);
            $all_ops = array_merge($all_ops, $ops);
        }
        $all_ops = array_unique($all_ops);

        $set = $ilDB->query("SELECT operation FROM rbac_operations " .
            " WHERE " . $ilDB->in("ops_id", $all_ops, false, "integer"));
        $perms = [];
        while ($rec = $ilDB->fetchAssoc($set)) {
            $perms[] = $rec["operation"];
        }

        return $perms;
    }

    public function setAssignedCacheEntry($a_role_id, $a_user_id, $a_value)
    {
        self::$is_assigned_cache[$a_role_id][$a_user_id] = $a_value;
    }

    public function getAssignedCacheEntry($a_role_id, $a_user_id)
    {
        return self::$is_assigned_cache[$a_role_id][$a_user_id];
    }

    public function clearCaches()
    {
        self::$is_assigned_cache = [];
        self::$assigned_users_cache = [];
    }
} // END class.ilRbacReview