image

В статье показан пример применения Power BI для анализа доступов пользователей на сайте под управлением 1С-Битрикс.

Проблема


С течением времени к развитию интернет-ресурсов подключается все больше и больше пользователей так или иначе, обладающих расширенными правами нежели рядовой пользователь сайта.

В связи с этим все сложнее контролировать доступы к конфиденциальным функциям. Хорошо, если написаны регламенты, которые помогают контролировать доступы на более-менее безопасном уровне. Но часто бывает так, что коллеги переходят работать в другие подразделения, уходят в декреты :) или увольняются, а доступы остаются.

Естественно это несет разные угрозы: утечка базы клиентов ну и вплоть до саботажа и др.
Возраст проектов, с которыми я работаю уже составил 10 лет. База насчитывает сотни тысяч пользователей, среди которых сотни с привилегированными правами.

В данной статье показан пример того, как можно упростить ревизию пользователей к различным объектам сайта под управлением CMS Битрикс (БУС).

Проблема в том, что админка Битрикса не дает возможности получить целостную картину с доступами; прокликивать кучу ссылок и ждать, пока загрузятся страницы админки тоже неприятно.

В качестве основного инструмента для этого будет использован Power BI (немного не по своему основному назначению :)

Предполагается, что читатель уже знаком на базовом уровне с Power BI, знает основы SQL, ну и пользоваться админкой Битрикса тоже умеет. Будут рассмотрены стандартные возможности Битрикса в плане предоставления доступов.

Недостатки админки Битрикса


Невозможно провести ревизию в стандартной админке за приемлемое время по причине отсутствия целостной картины с доступами – сводных данных по всем модулям/разделам/инфо-блокам и т.д., к которым предоставлен доступ.

Производительность админки:

  1. В разделе “Группы пользователей” админки Битрикса есть фича, которая генерирует SQL-запрос на выборку всех групп с подсчетом кол-ва пользователей. Все хорошо, когда база небольшая. Но с базой на сотни тысяч пользователей, с сотней пользовательских групп на выделенном сервере с 128 Гб оперативки простое открытие этого раздела занимает 8 сек.
  2. В карточке группы тоже есть запрос, который зачем-то выбирает все группы пользователей, вместо того, чтобы получить данные только по выбранной. Потери на ожидании 3 сек.

Способы решения


Обычно есть несколько решений проблемы.

  1. Написать регламенты по предоставлению доступов к сайтам и четко им следовать.
  2. Периодически проводить ревизию доступов.
  3. Надеяться на лучшее и не тратить ограниченные ресурсы компании.

В данной статье будет рассмотрен как раз второй способ.

Задачи


  1. Выбрать инструменты, которые позволят оперативно получить данные об уровнях доступа каждого пользователя с расширенными правами.
  2. Настроить инструменты так, чтобы они наглядно показывали картину с доступами в целом с необходимой детализацией и интерактивностью.
  3. Провести ревизию доступов.

Хранение доступов в Битрикс


Битрикс позволяет достаточно гибко настроить права через пользовательские группы.
Настройки доступов хранятся в основном в таблицах MySQL. Часть настроек хранится в файлах. Например, доступы к файлам и папкам хранятся в файлах .access.php.

Будет рассмотрен анализ доступов пользователей и пользовательских групп к:

  • инфо-блокам
  • веб-формам с указанием уровня доступа
  • статусам веб-формы с указанием уровня доступа
  • разделам сайта
  • модулям Битрикс с указанием уровней доступа

Инструменты


  1. Power BI Desktop, позволяющий хорошо визуализировать данные, получать данные из многочисленных источников (почти) из коробки. Собственно Power BI можно заменить обычным Excel 2016 и выше – в его поставку уже включен PowerQuery, через который можно выбрать все данные для проведения анализа. Однако, Power BI позволяет интерактивно отображать данные с учетом их взаимосвязей, а это позволяет быстро находить скрытые зависимости.
  2. MySQL Connector потребуется для возможности создать запрос через Power BI к MySQL веб-сервера.
  3. Kitty или Putty для организации туннеля к MySql, если доступ к БД открыт только через SSH.

Получается следующая схема доступа: Power BI > MySQL Connector > Kitty > MySQL.

Power BI


Power BI Desktop – позволяет хорошо визуализировать данные, получать данные из многочисленных источников (почти) из коробки. Собственно Power BI можно заменить обычным Excel 2016 и выше – в его поставку уже включен PowerQuery, через который можно выбрать все данные для проведения анализа. Однако, Power BI позволяет интерактивно отображать данные с учетом их взаимосвязей, а это позволяет быстро находить скрытые зависимости, что нам и нужно для ревизии доступов.

Скачать можно на официальной странице.

MySQL Connector


Переходим на страницу. Скачиваем и устанавливаем. Иногда придется перезагрузить ПК после установки.

Kitty/Putty


Для выполнения SQL-запросов к БД Битрикса потребуется настроить туннель.

  1. Вводим IP сервера и порт

    image
  2. Забиваем логин и пароль по SSH

    image
  3. Делаем проброс портов:

    image
  4. Сохраняем в профиль сделанные настройки для будущих использований:

    image
  5. Запускаем.

Можно также просто скачать Putty и запустить его командой:

putty.exe -ssh "USER@HOST" -pw "PASSWORD" -2 -v -P 22 -L 3306:127.0.0.1:3306

Естественно, Kitty/Putty должен быть запущен до обновления данных в Power BI.

Пользователи и пользовательские группы


Как и во многих CMS в Битриксе реализован механизм разграничения прав доступа через пользовательские группы.

Выгружаем в модель данных Power BI сущности из БД:

  • Группы
  • Пользователи

… а также отношения групп и пользователей.

Группы


Ограничимся только активными группами.

Список групп хранит таблица b_group.

  1. Создаем подключение:

    image
  2. Вводим:

    1. в поле Server: localhost:3306
    2. в поле Database: bitrix_db (название БД, с которой работает Битрикс)
    3. SQL-запрос:

      SELECT id, timestamp_x, active, name, description, anonymous FROM b_group WHERE active = 'Y';

      image

  3. Вводим логин и пароль к БД и отправляем запрос:

    image

    image

    image
  4. Сразу даем понятное имя запросу:

    image
  5. Выводим список групп на отдельный лист в табличном виде:

    image

Данный способ извлечения и представления данных будет аналогичен и для других запросов, связанных с БД Битрикса.

Пользователи


Теперь выгрузим всех пользователей, которые имеют расширенные права. Но не стоит выгружать пользователей, включенных только в группы, которые не дают им никаких дополнительных прав, например “Все пользователи, включая незарегистрированные” (стоит отметить, что связь данной группы с пользователями хранится для всех пользователей, зарегистрированных до версии 12. В более новых версиях группа считается системной и данных о связи пользователями БД уже не хранит).

Ограничимся только активированными пользователями.

Для этого нужно:

  1. Выбрать все ID групп, дающих расширенные права. Это нужно, чтобы сэкономить на трафике, т.к. количество записей в b_user_group может доходить до миллионов в зависимости от сложности проекта.
  2. Создать динамический запрос на выгрузку связей Пользователь — Группа
  3. Выгрузить пользователей, имеющих связь из п.2.

Начнем:

  1. Вызовем редактор запросов: Home > Edit Queries
  2. Создадим ссылку на исходный запрос “Группы”:

    image
  3. Переименуем новый запрос в “ID групп” и фильтром выберем только те группы, которые интересны с точки зрения безопасности.

    image
  4. Теперь получим строку, содержащую ID групп через запятую:
    • Добавляем пользовательский столбец: AddColumn > General > Custom Column

      image
    • Удалим все колонки кроме ID и Группировка:

      image
    • Сгруппируем по колонке “Группировка”:

      image

      image
    • Добавим еще одну колонку следующим образом:

      image
    • Раскроем список так, чтобы получились значения через запятую:

      image
    • И провалимся в получившуюся ячейку:

      image
    • Power BI после этого преобразует запрос в переменную, которую можно использовать в динамических SQL-запросах:

      image
  5. Создадим запрос “Пользователь-группа”, содержащий связь пользователя с группой, аналогично тому, как это сделано в разделе “Группы”.

    SQL-запрос:

    SELECT ug.user_id, ug.group_id
    FROM b_user_group ug
    JOIN b_group g ON g.id = ug.group_id
    JOIN b_user u ON u.id = ug.user_id
    WHERE g.ACTIVE = 'Y'
      AND u.ACTIVE = 'Y'
      AND ug.group_id IN (ХХХ);

    ХХХ нужно будет заменить на ID групп через запятую.
  6. Вызовем на редактирование исходники запроса и заменим его на следующее:

    let
        sql = "SELECT ug.user_id, ug.group_id #(lf)FROM b_user_group ug #(lf)JOIN b_group g ON g.id = ug.group_id #(lf)JOIN b_user u ON u.id = ug.user_id #(lf)WHERE g.ACTIVE = 'Y' #(lf)  AND u.ACTIVE = 'Y' #(lf)  AND ug.group_id IN ("&#"ID групп"&");",
        Source = MySQL.Database("localhost:3306", "bitrix_db", [ReturnSingleDatabase=true, Query=sql, CreateNavigationProperties=false])
    in
        Source

  7. После этого можно получить следующее предупреждение:

    Formula.Firewall: Query 'Пользователь-группа' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

    Чтобы от него избавиться, нужно изменить уровень конфиденциальности:

    image

    image

    После этого обновить запросы.
  8. Делаем переменную “ID пользователей” аналогично тому, как это сделано для “ID групп” (т.е. делаем ссылку от запроса Пользователи и т.д.). С помощью нее мы сгенерируем SQL-запрос, который позволит выбрать только нужных для анализа пользователей. Предварительно удалим дубликаты user_id:

    image
  9. Создаем запрос на выборку пользователей, аналогично тому, как это сделано для “Пользователь-группа”.

    SQL: SELECT id, last_name, NAME, email, date_register, last_login FROM b_user WHERE active = 'Y' AND id IN (ХХХ );

    ХХХ нужно будет заменить ID пользователей.

Настройка связей между запросами


Чтобы Power BI мог интерактивно фильтровать данные в разных представлениях, нужно задать связи между запросами. В нашем случае нужно связать поля:

  • “Пользователь-группа”[group_id] > “Группы”[id]
  • “Пользователь-группа”[user_id] > “Пользователи”[id]

image

Аналогичным образом мы будем связывать другие запросы.

Отчет о пользователях и пользовательских группах


На вкладке Reports (Отчеты) выведем список пользователей и групп, используя в качестве элемента визуализации Table (Таблица).

Из запроса “Пользователи” выбираем поля: last_name, name, last_login, email.
Из запроса “Пользователь-группа” выбираем поле group_id.
Т.к. мы назначили связи между запросами, то Power BI сможет корректно использовать агрегирующую функцию Count для подсчета количества групп, в которые входит каждый конкретный пользователь.

image

Добавим рядом еще один Table и выберем из запроса «Группа» поле name, а из запроса “Пользователь-группа” поле user_id – для него выставим агрегацию “Count (Distinct)”, чтобы увидеть количество пользователей, входящих в группу.

Т.к. запросы “Группа” и “Пользователь” связаны через ассоциативный запрос “Пользователь-группа”, то при клике на пользователе в таблице со списком групп отобразятся только те группы, в которые входит выбранный пользователь. И наоборот.

image

Таким образом можно прощелкать либо каждого пользователя и посмотреть, в какие группы он входит, либо прощелкать группы и посмотреть, какие пользователи входят в группу. Ну а затем уже принимать решения относительно изменения доступов для пользователя.

Далее описываться способ размещения оставшихся таблиц в общем отчете Power BI не будет, т.к. это делается аналогичным образом.

.access.php


В Битриксе имеется возможность задать доступ к папкам и файлам, указав в файлах .access.php номера групп и необходимый уровень доступа.

Наша задача свести данные из всех файлов .access.php, раскиданных по серверу проекта, в табличный вид.

Для этого:

  1. Ищем и архивируем все файлы .access.php с сервера, сохраняя пути к этим файлам.
    Я использовал терминалку для поиска, копирования и архивирования найденных файлов. Пример команды:

    find “BITRIX_PROJECT_DIR” -name '.access.php' -type f > “OUTPUT_DIR/.access.php.files.txt”&&tar cvfpz “OUTPUT_DIR/.access.php.files.tar”  -T “OUTPUT_DIR/.access.php.files.txt”&&find “OUTPUT_DIR” -type d -exec chmod 775 {} \; && find “OUTPUT_DIR” -type f -exec chmod 775 {} \;&&find “OUTPUT_DIR” -type d -exec chown bitrix:bitrix {} \; && find “OUTPUT_DIR”/ -type f -exec chown bitrix:bitrix {} \;

    Здесь:

    • BITRIX_PROJECT_DIR – папка с проектом на Битриксе.
    • OUTPUT_DIR – путь к папке, в которой будут размещен файл .access.php.files.txt со списком найденных .access.php, а также архив .access.php.files.tar, содержащий копии всех найденных .access.php.

    Естественно, если проектов много (использована многосайтовость), то выбираем папку, содержащую все проекты.
  2. Скачиваем и распаковываем архив с .access.php где-нибудь рядом с проектом Power BI.
    Я написал батник, который делает это автоматически: через wget реализовано скачивание; через 7zip – разархивирование.

    Пример батника:

    image

    Файл, содержащий настройки для батника:

    image

Теперь создаем запрос, который сведет содержимое всех .access.php в табличном виде.

  1. Для удобства создадим параметр, который будет содержать путь к папке, из которой мы извлечем содержимое всех .access.php

    image
  2. Выберем запрос типа “Folder” и выберем наш параметр в качестве пути:

    image
  3. Развернем поле Content:

    image

    ХХХХХХ – это разделитель колонок, нужен чтобы столбец был один после импорта данных из всех файлов.
  4. После этого Power BI удалит нужную нам колонку, содержащую путь к .access.php. Поэтому нам нужно отредактировать шаг “Remove other columns1”, выбрав в нем “Folder Path”:

    image
  5. Оставляем колонки: Folder Path и Column1.
  6. Чтобы удалить из Folder Path абсолютный путь к локальному файлу воспользуемся заменой:

    image
  7. Файлы .access.php содержат настройки доступа в формате:

    $PERM["путь"]["ID группы"] = "<Уровень доступа>";

    Наша задача раскидать по колонкам: Путь, ID группы, Уровень доступа. Делается это с помощью фильтров, разделения по столбцам (Split Column) и пользовательских столбцов (Custom column).
  8. В итоге должна получиться следующая таблица:

    image

    Как видно в поле ID группы есть “*” (доступ для всех). Чтобы была возможность задать связь с другими запросами нам нужно сделать это поле целочисленным, при этом не потеряв информации о “*” (что означает для всех групп). Сделаем два запроса, типа “ссылка” на исходный запрос DotAccessPhp:

    • Первый DotAccessPhpForRels будет содержать только целочисленные ID групп (используем фильтр, убрав * в колонке ID группы) – ее мы и свяжем с остальными запросами:
      image
    • Второй – DotAccessPhpForAll – только * (используем фильтр).

Схема связей:

image

Чтобы при выборе файла из DotAccessForRels в других представлениях показывались только связанные данные, нужно изменить параметр “Cross filter direction” на Both:

image

Для остальных запросов, которые будут добавлены ниже это тоже нужно сделать.

Инфо-блоки


Необходимо выгрузить список инфо-блоков и таблицу связей инфо-блоков с группами.

Будем выгружать информацию только об активных инфо-блоках.

  1. Создаем запрос “Инфоблоки”. SQL-запрос:

    SELECT i.id, i.NAME 'Инфоблок', i.TIMESTAMP_X 'Дата изменения', GROUP_CONCAT(ist.SITE_ID SEPARATOR ', ') 'Сайты'
    FROM b_iblock i
    JOIN b_iblock_site ist ON ist.IBLOCK_ID = i.id
    GROUP BY 1,2,3;
    Создаем запрос “Инфоблок-группа”:
    SELECT ig.iblock_id, ig.group_id, ig.permission
    FROM b_iblock_group ig
    JOIN b_group g ON g.id = ig.group_id
    JOIN b_iblock i ON i.ID = ig.IBLOCK_ID
    WHERE g.ACTIVE = 'Y'
      AND i.ACTIVE = 'Y';
  2. Обновляем схему связей, не забывая изменять параметр “Cross filter direction” на Both:

    image

Формы


В случае форм права для пользовательских групп выдаются как на сами формы, так и на статусы, в которых пребывает результат заполнения формы.

  1. Создаем запрос “Формы”:

    SELECT
      f.ID,
      f.name 'Форма',
      GROUP_CONCAT(f2s.SITE_ID SEPARATOR ', ') 'Сайты'
    FROM b_form f
    JOIN b_form_2_site f2s ON f2s.FORM_ID = f.ID
    GROUP BY 1, 2
    ORDER BY 2;
  2. Создаем запрос “Форма-группа”:

    SELECT DISTINCT
      f2g.group_id,
      f2g.form_id,
      f2g.PERMISSION 'Код разрешения'
    FROM b_form_2_site f2s
    JOIN b_form_2_group f2g ON f2g.FORM_ID = f2s.FORM_ID
    JOIN b_group g ON g.ID = f2g.group_ID
    WHERE g.ACTIVE = 'Y'
    ORDER BY 1, 2, 3;
  3. Создаем запрос “Статусы форм”.

    SELECT  fs.ID,  fs.TITLE 'Статус',  fs.form_id
    FROM b_form_status fs
    JOIN b_form f ON f.ID = fs.FORM_ID
    WHERE fs.ACTIVE = 'Y'
      AND EXISTS (SELECT f2s.FORM_ID FROM b_form_2_site f2s WHERE f2s.FORM_ID = f.ID LIMIT 1)
    ORDER BY 3, 2;
  4. Создаем запрос “Статусы форм-группа”

    SELECT  fs2g.status_id,  fs2g.group_id,  fs2g.PERMISSION 'Разрешение'
    FROM b_form_status_2_group fs2g
    JOIN b_form_status fs ON fs.ID = fs2g.STATUS_ID
    JOIN b_group g ON g.ID = fs2g.group_ID
    JOIN b_form f ON f.ID = fs2g.GROUP_ID
    JOIN b_form_2_site f2s ON f2s.FORM_ID = f.ID
    WHERE fs.ACTIVE = 'Y'
      AND (g.ACTIVE = 'Y')
    ORDER BY 1, 2, 3;
  5. Обновляем схему связей:

    image

Модули


  1. Создаем запрос “Модуль-группа”.

    SELECT mg.MODULE_ID 'Модуль', mg.group_id, mg.G_ACCESS 'Разрешение', t.LETTER, t.NAME
    FROM b_module_group mg
    JOIN b_group g ON g.id = mg.GROUP_ID
    LEFT JOIN b_task t ON t.MODULE_ID = mg.MODULE_ID AND t.BINDING = 'module'
    WHERE g.active = 'Y'
      AND mg.G_ACCESS = t.LETTER;
  2. Обновляем связи:

    image

Табло


Настраиваем стили таблиц, используем полезное пространство по-максимуму.

В итоге должно получиться что-то похожее на следующее:

image

Немного доработанное табло (кол-во элементов в таблицах):

image

Кстати удобно сначала настроить вид одной таблицы, а потом просто применить ее вид на другие таблицы с помощью Home > Format Painter. Данная функция действует так же, как и в Word и Excel (Формат по образцу).

Ссылки в админку


Чтобы можно было быстро переходить на сайт в и делать настройки в админке, можно добавить пользовательскую колонку на языке DAX и сделать ее тип “Web URL”. Для этого выберем созданную колонку и назначим соответствующий тип (Modeling > Properties > Data Category > Web URL).

Пример для запроса Группы:

image

Добавим колонку в представление:

image
Теперь можно просто кликать на ячейку таблицы и переходить в карточку группы в админке Битрикса.

Отчет “Файлы”


Для удобства можно сделать отдельный отчет разместив на нем таблицы, касающиеся доступов к файлам и разделам интернет-ресурса:

image

В этом отчете также добавлены ссылки на редактирование всех .access.php непосредственно через админку Битрикса.

Итоги


Битрикс – это чемпион среди cms-монстров с очевидными плюсами и минусами, красивый снаружи и ужасный внутри. В нем нет удобных средств администрирования доступов. Но данная проблема решена с помощью бесплатных инструментов, не привлекая к этому процессу ценное время программистов.

К преимуществам приведенного подхода также стоит отнести возможность быстро дополнить модель в Power BI дополнительной информацией из Битрикса, например, кто-то захочет узнать когда были созданы или изменены .access.php и др.

Теперь после построения модели прав доступа и ее визуализации в Power BI достаточно:

  1. последовательно прощелкать пользователей, группы, формы, файлы и в реальном времени увидеть все связи, касательно доступов;
  2. быстро перейти на необходимые страницы админки, чтобы внести правки;
  3. обновить модель данных актуальными данными из Битрикса прямо в Power BI.

В итоге была проведена ревизия и сделаны корректировка в доступах пользователей.

P.S. В маркетплейсе есть бесплатный модуль “Центр управления доступом”, но он весьма ограничен, а последнему комментарию к нему более 5 лет. Возможно кому-то понравится идея построения такого дашборда прямо в Битриксе и он реализует ее в качестве модуля…

P.S.2. Если кому интересна тема использования Power BI для решения проблем поиска скрытых зависимостей в различных учетных системах, то пишите в комментариях. Я тогда напишу еще несколько статей на эту тему.

P.S.3. Спасибо моим соратникам за помощь в подготовке этой статьи: Александру Воронкову, Евгению Шапочкину, Алексею Титову.

Комментарии (8)


  1. murzix
    26.06.2019 11:01

    Хм, у нас больше 200 000 пользователей сейчас и страница открывается быстрее секунды. Вы проводили мониторинг медленных запросов (slow_query_log)? С индексами всё нормально?


    1. SergeyRock Автор
      26.06.2019 15:46

      Индексы на полях, которые участвуют в запросе стоят.
      Страница:
      image
      Запрос, который генерирует Битрикс:

      SELECT G.ID, G.ACTIVE, G.C_SORT, G.ANONYMOUS, G.NAME, G.DESCRIPTION, 
      G.STRING_ID, count(distinct U.USER_ID) USERS, G.ID REFERENCE_ID, 
      concat(G.NAME, ' [', G.ID, ']') REFERENCE, 
      DATE_FORMAT(G.TIMESTAMP_X, '%d.%m.%Y %H:%i:%s') TIMESTAMP_X
      FROM
      b_group G
      LEFT JOIN b_user_group U ON (U.GROUP_ID=G.ID AND ((U.DATE_ACTIVE_FROM IS NULL) OR (U.DATE_ACTIVE_FROM <= now())) AND ((U.DATE_ACTIVE_TO IS NULL) OR (U.DATE_ACTIVE_TO >= now())))
      WHERE
      (1=1)
      GROUP BY
      G.ID, G.ACTIVE, G.C_SORT, G.TIMESTAMP_X, G.ANONYMOUS, G.NAME, G.STRING_ID, G.DESCRIPTION
      HAVING
      1=1
      ORDER BY
      G.C_SORT asc


      Такой explain:
      image


      1. murzix
        26.06.2019 16:00

        Значит, у вас что-то не так с настройками БД =) У нас очень сложно всё с группами и мы тоже писали себе отдельные инструменты для боле удобной работы с ними, но этот же запрос отрабатывает вот так

        image


      1. murzix
        26.06.2019 16:06

        Судя по плану исполнения, там могут быть проблемы только при десятке тысяч групп.


      1. murzix
        26.06.2019 16:10

        Всё понятно. У вас там 111 921 строка перебирается, потому и долго. Мы до такого количества отношений пользователей к группам не дошли.


        1. SergeyRock Автор
          26.06.2019 19:08

          Да, записей много — более 2 000 000.
          Сократил на несколько сот тысяч:
          1. Отключил систему рейтингов и авторитетов, т.к. не используется, чтобы пользователи не добавлялись автоматом в группы:
          image
          2. Удалил связи с этими группами из b_user_group.

          • Пользователи имеющие право голосовать за рейтинг
          • Пользователи имеющие право голосовать за авторитет

          Запрос стал выполняться за 5 сек.


      1. murzix
        26.06.2019 16:14

        Но если не выводить в столбце количество пользователей, то проблемы нет =)


        1. SergeyRock Автор
          26.06.2019 18:34

          К сожалению, это не помогает… (по крайней мере в версиях 15-18)