class.ilRbacReview.php

Go to the documentation of this file.

<?php

declare(strict_types=1);

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

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

    protected static array $assigned_users_cache = [];
    protected static array $is_assigned_cache = [];

    protected ilLogger $log;
    protected ilDBInterface $db;

    public function __construct()
    {
        global $DIC;

        $this->log = ilLoggerFactory::getLogger('ac');
        $this->db = $DIC->database();
    }

    public function roleExists(string $a_title, int $a_id = 0): ?int
    {
        $clause = ($a_id) ? " AND obj_id != " . $this->db->quote($a_id, ilDBConstants::T_TEXT) . " " : "";

        $q = "SELECT DISTINCT(obj_id) obj_id FROM object_data " .
            "WHERE title =" . $this->db->quote($a_title, ilDBConstants::T_TEXT) . " " .
            "AND type IN('role','rolt')" .
            $clause . " ";
        $r = $this->db->query($q);
        while ($row = $r->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            return (int) $row->obj_id;
        }
        return null;
    }

    protected function __getParentRoles(array $a_path, bool $a_templates): array
    {
        $parent_roles = [];
        $role_hierarchy = [];
        foreach ($a_path as $ref_id) {
            $roles = $this->getRoleListByObject($ref_id, $a_templates);
            foreach ($roles as $role) {
                $id = (int) $role["obj_id"];
                $role["parent"] = (int) $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, (int) reset($a_path));
    }

    public function getParentRoleIds(int $a_endnode_id, bool $a_templates = false): array
    {
        global $DIC;

        $tree = $DIC->repositoryTree();

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

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

    public function getRoleListByObject(int $a_ref_id, bool $a_templates = false): array
    {
        $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 = " . $this->db->quote($a_ref_id, 'integer') . " ";

        $res = $this->db->query($query);
        while ($row = $this->db->fetchAssoc($res)) {
            $row["desc"] = $row["description"];
            $row["user_id"] = (int) $row["owner"];
            $row['obj_id'] = (int) $row['obj_id'];
            $row['rol_id'] = (int) $row['rol_id'];
            $row['parent'] = (int) $row['parent'];
            $role_list[] = $row;
        }

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

    public function getAssignableRoles(
        bool $a_templates = false,
        bool $a_internal_roles = false,
        string $title_filter = ''
    ): array {
        return iterator_to_array(
            $this->getAssignableRolesGenerator(
                $a_templates,
                $a_internal_roles,
                $title_filter
            )
        );
    }

    private function getAssignableRolesGenerator(
        bool $a_templates = false,
        bool $a_internal_roles = false,
        string $title_filter = ''
    ): Generator {
        $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 ' . $this->db->like(
                'title',
                'text',
                $title_filter . '%'
            ));
        }
        $res = $this->db->query($query);

        while ($row = $this->db->fetchAssoc($res)) {
            $row["description"] = (string) $row["description"];
            $row["desc"] = $row["description"];
            $row["user_id"] = (int) $row["owner"];
            $row['obj_id'] = (int) $row['obj_id'];
            $row['parent'] = (int) $row['parent'];
            yield $this->setRoleTypeAndProtection($row);
        }
    }

    public function getAssignableRolesInSubtree(int $ref_id): array
    {
        global $DIC;

        $tree = $DIC->repositoryTree();
        $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 = ' . $this->db->quote('y', 'text') . ' ' .
            'AND obd.type = ' . $this->db->quote('role', 'text') . ' ' .
            'AND t1.child IN (' .
            $tree->getSubTreeQuery($ref_id, ['child']) . ' ' .
            ') ';

        $res = $this->db->query($query);

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

    public function getAssignableChildRoles(int $a_ref_id): array
    {
        $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->db->quote($a_ref_id, 'integer') . " ";

        $res = $this->db->query($query);
        $roles_data = [];
        while ($row = $this->db->fetchAssoc($res)) {
            $row['rol_id'] = (int) $row['rol_id'];
            $row['obj_id'] = (int) $row['obj_id'];

            $roles_data[] = $row;
        }

        return $roles_data;
    }

    protected function __setTemplateFilter(bool $a_templates): string
    {
        if ($a_templates) {
            $where = "WHERE " . $this->db->in('object_data.type', ['role', 'rolt'], false, 'text') . " ";
        } else {
            $where = "WHERE " . $this->db->in('object_data.type', ['role'], false, 'text') . " ";
        }
        return $where;
    }

    protected function __setRoleType(array $a_role_list): array
    {
        foreach ($a_role_list as $key => $val) {
            $a_role_list[$key] = $this->setRoleTypeAndProtection($val);
        }
        return $a_role_list;
    }

    private function setRoleTypeAndProtection(array $role_list_entry): array
    {
        $role_list_entry['role_type'] = $this->buildRoleType($role_list_entry);
        $role_list_entry['protected'] = $this->buildProtectionByStringValue($role_list_entry['protected']);
        return $role_list_entry;
    }

    private function buildRoleType(array $role_list_entry): string
    {
        if ($role_list_entry['type'] === 'rolt') {
            return 'template';
        }

        if ($role_list_entry['assign'] !== 'y') {
            return 'linked';
        }

        if ($role_list_entry['parent'] === ROLE_FOLDER_ID) {
            return 'global';
        }

        return 'local';
    }

    private function buildProtectionByStringValue(string $value): bool
    {
        if ($value === 'y') {
            return true;
        }
        return false;
    }

    public function getNumberOfAssignedUsers(array $a_roles): int
    {
        $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 ' . $this->db->in('rol_id', $a_roles, false, 'integer');

        $res = $this->db->query($query);
        if ($res->numRows() > 0) {
            $row = $res->fetchRow(\ilDBConstants::FETCHMODE_OBJECT);
            return isset($row->num) && is_numeric($row->num) ? (int) $row->num : 0;
        }
        return 0;
    }

    public function assignedUsers(int $a_rol_id): array
    {
        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= " . $this->db->quote($a_rol_id, 'integer');
        $res = $this->db->query($query);
        while ($row = $this->db->fetchAssoc($res)) {
            $result_arr[] = (int) $row["usr_id"];
        }
        self::$assigned_users_cache[$a_rol_id] = $result_arr;
        return $result_arr;
    }

    public function isAssigned(int $a_usr_id, int $a_role_id): bool
    {
        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
        $this->db->setLimit(1, 0);
        $query = "SELECT usr_id FROM rbac_ua WHERE " .
            "rol_id= " . $this->db->quote($a_role_id, 'integer') . " " .
            "AND usr_id= " . $this->db->quote($a_usr_id, ilDBConstants::T_INTEGER);
        $res = $this->db->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(int $a_usr_id, array $a_role_ids): bool
    {
        global $DIC;

        $this->db = $DIC['ilDB'];

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

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

    public function assignedRoles(int $a_usr_id): array
    {
        $query = "SELECT rol_id FROM rbac_ua WHERE usr_id = " . $this->db->quote($a_usr_id, 'integer');

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

    public function assignedGlobalRoles(int $a_usr_id): array
    {
        $query = "SELECT ua.rol_id FROM rbac_ua ua " .
            "JOIN rbac_fa fa ON ua.rol_id = fa.rol_id " .
            "WHERE usr_id = " . $this->db->quote($a_usr_id, 'integer') . ' ' .
            "AND parent = " . $this->db->quote(ROLE_FOLDER_ID, ilDBConstants::T_INTEGER) . " " .
            "AND assign = 'y' ";

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

    public function isAssignable(int $a_rol_id, int $a_ref_id): bool
    {
        // exclude system role from rbac
        if ($a_rol_id == SYSTEM_ROLE_ID) {
            return true;
        }

        $query = "SELECT * FROM rbac_fa " .
            "WHERE rol_id = " . $this->db->quote($a_rol_id, 'integer') . " " .
            "AND parent = " . $this->db->quote($a_ref_id, 'integer') . " ";
        $res = $this->db->query($query);
        while ($row = $this->db->fetchObject($res)) {
            return $row->assign == 'y';
        }
        return false;
    }

    public function hasMultipleAssignments(int $a_role_id): bool
    {
        $query = "SELECT * FROM rbac_fa WHERE rol_id = " . $this->db->quote($a_role_id, 'integer') . ' ' .
            "AND assign = " . $this->db->quote('y', 'text');
        $res = $this->db->query($query);
        return $res->numRows() > 1;
    }

    public function getFoldersAssignedToRole(int $a_rol_id, bool $a_assignable = false): array
    {
        $where = '';
        if ($a_assignable) {
            $where = " AND assign ='y'";
        }

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

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

    public function getRolesOfObject(int $a_ref_id, bool $a_assignable_only = false): array
    {
        $and = '';
        if ($a_assignable_only === true) {
            $and = 'AND assign = ' . $this->db->quote('y', 'text');
        }
        $query = "SELECT rol_id FROM rbac_fa " .
            "WHERE parent = " . $this->db->quote($a_ref_id, 'integer') . " " .
            $and;

        $res = $this->db->query($query);

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

    public function getRolesOfRoleFolder(int $a_ref_id, bool $a_nonassignable = true): array
    {
        $and = '';
        if ($a_nonassignable === false) {
            $and = " AND assign='y'";
        }

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

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

        return $rol_id;
    }

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

    public function getLocalRoles(int $a_ref_id): array
    {
        $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(int $a_ref_id): array
    {
        $lroles = [];
        foreach ($this->getRolesOfRoleFolder($a_ref_id) as $role_id) {
            $lroles[] = $role_id;
        }
        return $lroles;
    }

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

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

    public function isRoleAssignedToObject(int $a_role_id, int $a_parent_id): bool
    {
        $query = 'SELECT * FROM rbac_fa ' .
            'WHERE rol_id = ' . $this->db->quote($a_role_id, 'integer') . ' ' .
            'AND parent = ' . $this->db->quote($a_parent_id, 'integer');
        $res = $this->db->query($query);
        return (bool) $res->numRows();
    }

    public function getOperations(): array
    {
        $query = 'SELECT * FROM rbac_operations ORDER BY ops_id ';
        $res = $this->db->query($query);
        $ops = [];
        while ($row = $this->db->fetchObject($res)) {
            $ops[] = ['ops_id' => (int) $row->ops_id,
                           'operation' => $row->operation,
                           'description' => $row->description
            ];
        }
        return $ops;
    }

    public function getOperation(int $ops_id): array
    {
        $query = 'SELECT * FROM rbac_operations WHERE ops_id = ' . $this->db->quote($ops_id, 'integer');
        $res = $this->db->query($query);
        $ops = [];
        while ($row = $this->db->fetchObject($res)) {
            $ops = ['ops_id' => (int) $row->ops_id,
                         'operation' => $row->operation,
                         'description' => $row->description
            ];
        }
        return $ops;
    }

    public function getAllOperationsOfRole(int $a_rol_id, int $a_parent = 0): array
    {
        if (!$a_parent) {
            $a_parent = ROLE_FOLDER_ID;
        }
        $query = "SELECT ops_id,type FROM rbac_templates " .
            "WHERE rol_id = " . $this->db->quote($a_rol_id, 'integer') . " " .
            "AND parent = " . $this->db->quote($a_parent, 'integer');
        $res = $this->db->query($query);

        $ops_arr = [];
        while ($row = $this->db->fetchObject($res)) {
            $ops_arr[$row->type][] = (int) $row->ops_id;
        }
        return $ops_arr;
    }

    public function getActiveOperationsOfRole(int $a_ref_id, int $a_role_id): array
    {
        $query = 'SELECT * FROM rbac_pa ' .
            'WHERE ref_id = ' . $this->db->quote($a_ref_id, 'integer') . ' ' .
            'AND rol_id = ' . $this->db->quote($a_role_id, 'integer') . ' ';

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

    public function getOperationsOfRole(int $a_rol_id, string $a_type, int $a_parent = 0): array
    {
        $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 =" . $this->db->quote($a_type, 'text') . " " .
            "AND rol_id = " . $this->db->quote($a_rol_id, 'integer') . " " .
            "AND parent = " . $this->db->quote($a_parent, 'integer');
        $res = $this->db->query($query);
        while ($row = $this->db->fetchObject($res)) {
            $ops_arr[] = $row->ops_id;
        }
        return $ops_arr;
    }

    public function getRoleOperationsOnObject(int $a_role_id, int $a_ref_id): array
    {
        $query = "SELECT ops_id FROM rbac_pa " .
            "WHERE rol_id = " . $this->db->quote($a_role_id, 'integer') . " " .
            "AND ref_id = " . $this->db->quote($a_ref_id, 'integer') . " ";

        $res = $this->db->query($query);
        $ops = [];
        while ($row = $this->db->fetchObject($res)) {
            if ($row->ops_id !== ':') {
                $ops = unserialize($row->ops_id);
            }
        }
        return $ops;
    }

    public function getOperationsOnType(int $a_typ_id): array
    {
        $query = 'SELECT ta.ops_id FROM rbac_ta ta JOIN rbac_operations o ON ta.ops_id = o.ops_id ' .
            'WHERE typ_id = ' . $this->db->quote($a_typ_id, 'integer') . ' ' .
            'ORDER BY op_order';

        $res = $this->db->query($query);
        $ops_id = [];
        while ($row = $this->db->fetchObject($res)) {
            $ops_id[] = (int) $row->ops_id;
        }
        return $ops_id;
    }

    public function getOperationsOnTypeString(string $a_type): array
    {
        $query = "SELECT * FROM object_data WHERE type = 'typ' AND title = " . $this->db->quote($a_type, 'text') . " ";
        $res = $this->db->query($query);
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            return $this->getOperationsOnType((int) $row->obj_id);
        }
        return [];
    }

    public function getOperationsByTypeAndClass(string $a_type, string $a_class): array
    {
        if ($a_class != 'create') {
            $condition = "AND class != " . $this->db->quote('create', 'text');
        } else {
            $condition = "AND class = " . $this->db->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 = " . $this->db->quote('typ', 'text') . " " .
            "AND title = " . $this->db->quote($a_type, 'text') . " " .
            $condition . " " .
            "ORDER BY op_order ";

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

    public function getObjectsWithStopedInheritance(int $a_rol_id, array $a_filter = []): array
    {
        $query = 'SELECT parent p FROM rbac_fa ' .
            'WHERE assign = ' . $this->db->quote('n', 'text') . ' ' .
            'AND rol_id = ' . $this->db->quote($a_rol_id, 'integer') . ' ';

        if ($a_filter !== []) {
            $query .= ('AND ' . $this->db->in('parent', (array) $a_filter, false, 'integer'));
        }

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

    public function isDeleted(int $a_node_id): bool
    {
        $q = "SELECT tree FROM tree WHERE child =" . $this->db->quote($a_node_id, ilDBConstants::T_INTEGER) . " ";
        $r = $this->db->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->warning($message);
            return true;
        }
        return $row->tree < 0;
    }

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

    public function getRolesByFilter(int $a_filter = 0, int $a_user_id = 0, string $title_filter = ''): Generator
    {
        $assign = "y";
        switch ($a_filter) {
            case self::FILTER_ALL:
                return yield from $this->getAssignableRolesGenerator(true, true, $title_filter);

            case self::FILTER_ALL_GLOBAL:
                $where = 'WHERE ' . $this->db->in('rbac_fa.rol_id', $this->getGlobalRoles(), false, 'integer') . ' ';
                break;

            case self::FILTER_ALL_LOCAL:
            case self::FILTER_INTERNAL:
            case self::FILTER_NOT_INTERNAL:
                $where = 'WHERE ' . $this->db->in('rbac_fa.rol_id', $this->getGlobalRoles(), true, 'integer');
                break;

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

            case 0:
            default:
                if (!$a_user_id) {
                    return;
                }

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

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

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

        $res = $this->db->query($query);
        while ($row = $this->db->fetchAssoc($res)) {
            $row['title'] = $row['title'] ?? '';
            $prefix = substr($row['title'], 0, 3) == "il_";

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

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

            $row['description'] = $row['description'] ?? '';
            $row["desc"] = $row["description"];
            $row["user_id"] = (int) $row["owner"];
            $row['obj_id'] = (int) $row['obj_id'];
            $row['rol_id'] = (int) $row['rol_id'];
            $row['parent'] = (int) $row['parent'];

            yield $this->setRoleTypeAndProtection($row);
        }
    }

    public function getTypeId(string $a_type): int
    {
        $q = "SELECT obj_id FROM object_data " .
            "WHERE title=" . $this->db->quote($a_type, 'text') . " AND type='typ'";
        $r = $this->db->query($q);
        while ($row = $r->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            return (int) $row->obj_id;
        }
        return 0;
    }

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

        $ilDB = $DIC->database();
        if ($operations === []) {
            return [];
        }

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

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

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

        $ilDB = $DIC->database();

        // 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] = (int) $row->ops_id;
            }
        }

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

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

        $ilDB = $DIC->database();

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

        if ($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] = (int) $row->ops_id;
        }
        return $ops_ids;
    }

    public function isProtected(int $a_ref_id, int $a_role_id): bool
    {
        $query = 'SELECT protected FROM rbac_fa ' .
            'WHERE rol_id = ' . $this->db->quote($a_role_id, ilDBConstants::T_INTEGER);
        $res = $this->db->query($query);
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            if ($row->protected === 'y') {
                return true;
            }
        }
        return false;
    }

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

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

        $tree = $DIC['tree'];

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

        $parent_ids = [];
        while ($row = $res->fetchRow(ilDBConstants::FETCHMODE_OBJECT)) {
            $parent_ids[] = (int) $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(array $a_parent_roles, array $a_role_hierarchy, int $a_ref_id): array
    {
        global $DIC;

        $rbacsystem = $DIC->rbac()->system();
        $ilUser = $DIC->user();
        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')) {
                        $a_parent_roles[$role_id]['protected'] = false;
                    }
                }
            }
        }
        return $a_parent_roles;
    }

    public static function _getOperationList(string $a_type = ''): array
    {
        global $DIC;

        $ilDB = $DIC->database();
        $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[] = [
                "ops_id" => (int) $row['ops_id'],
                "operation" => $row['operation'],
                "desc" => $row['description'],
                "class" => $row['class'],
                "order" => (int) $row['op_order']
            ];
        }
        return $arr;
    }

    public static function _groupOperationsByClass(array $a_ops_arr): array
    {
        $arr = [];
        foreach ($a_ops_arr as $ops) {
            $arr[$ops['class']][] = ['ops_id' => (int) $ops['ops_id'],
                                          'name' => $ops['operation']
            ];
        }
        return $arr;
    }

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

        if (isset($obj_cache[$a_role_id]) && $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 = ' . $this->db->quote('y', 'text') . ' ' .
            'AND rol_id = ' . $this->db->quote($a_role_id, 'integer') . ' ' .
            'AND deleted IS NULL';

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

    public function getObjectReferenceOfRole(int $a_role_id): int
    {
        $query = 'SELECT parent p_ref FROM rbac_fa ' .
            'WHERE rol_id = ' . $this->db->quote($a_role_id, 'integer') . ' ' .
            'AND assign = ' . $this->db->quote('y', 'text');

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

    public function isRoleDeleted(int $a_role_id): bool
    {
        $rolf_list = $this->getFoldersAssignedToRole($a_role_id, false);
        $deleted = true;
        if ($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(array $role_ids, bool $use_templates): array
    {
        $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 ' . $this->db->in('object_data.obj_id', $role_ids, false, 'integer');

        $res = $this->db->query($query);
        $role_list = [];
        while ($row = $this->db->fetchAssoc($res)) {
            $row["desc"] = $row["description"];
            $row["user_id"] = (int) $row["owner"];
            $role_list[] = $row;
        }
        return $this->__setRoleType($role_list);
    }

    public function getOperationAssignment(): array
    {
        global $DIC;

        $this->db = $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 = $this->db->query($query);

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

    public function isDeleteable(int $a_role_id, int $a_rolf_id): bool
    {
        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(int $a_role_id): bool
    {
        $title = ilObject::_lookupTitle($a_role_id);
        return substr($title, 0, 3) == 'il_';
    }

    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(int $a_role_id): int
    {
        if (ilObject::_lookupType($a_role_id) == 'role') {
            $and = ('AND assign = ' . $this->db->quote('y', 'text'));
        } else {
            $and = '';
        }

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

    public function getUserPermissionsOnObject(int $a_user_id, int $a_ref_id): array
    {
        $query = "SELECT ops_id FROM rbac_pa JOIN rbac_ua " .
            "ON (rbac_pa.rol_id = rbac_ua.rol_id) " .
            "WHERE rbac_ua.usr_id = " . $this->db->quote($a_user_id, 'integer') . " " .
            "AND rbac_pa.ref_id = " . $this->db->quote($a_ref_id, 'integer') . " ";

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

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

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

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

    public function clearCaches(): void
    {
        self::$is_assigned_cache = [];
        self::$assigned_users_cache = [];
    }

    public static function _getCustomRBACOperationId(string $operation, \ilDBInterface $ilDB = null): ?int
    {
        if (!$ilDB) {
            global $DIC;
            $ilDB = $DIC->database();
        }

        $sql =
            "SELECT ops_id" . PHP_EOL
            . "FROM rbac_operations" . PHP_EOL
            . "WHERE operation = " . $ilDB->quote($operation, "text") . PHP_EOL
        ;

        $res = $ilDB->query($sql);
        if ($ilDB->numRows($res) == 0) {
            return null;
        }

        $row = $ilDB->fetchAssoc($res);
        return (int) $row["ops_id"] ?? null;
    }

    public static function _isRBACOperation(int $type_id, int $ops_id, \ilDBInterface $ilDB = null): bool
    {
        if (!$ilDB) {
            global $DIC;
            $ilDB = $DIC->database();
        }

        $sql =
            "SELECT typ_id" . PHP_EOL
            . "FROM rbac_ta" . PHP_EOL
            . "WHERE typ_id = " . $ilDB->quote($type_id, "integer") . PHP_EOL
            . "AND ops_id = " . $ilDB->quote($ops_id, "integer") . PHP_EOL
        ;

        return (bool) $ilDB->numRows($ilDB->query($sql));
    }
} // END class.ilRbacReview