В статье показан пример применения 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 сервера и порт
   
   
2. Забиваем логин и пароль по SSH
   
   
3. Делаем проброс портов:
   
   
4. Сохраняем в профиль сделанные настройки для будущих использований:
   
   
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. Создаем подключение:
   
   
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';

      
      
      
   
   
3. Вводим логин и пароль к БД и отправляем запрос:
   
   
   
   
   
   
   
4. Сразу даем понятное имя запросу:
   
   
5. Выводим список групп на отдельный лист в табличном виде:
   
   
   

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

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

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

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

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

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

Начнем:

1. Вызовем редактор запросов: Home > Edit Queries
2. Создадим ссылку на исходный запрос “Группы”:
   
   
3. Переименуем новый запрос в “ID групп” и фильтром выберем только те группы, которые интересны с точки зрения безопасности.
   
   
4. Теперь получим строку, содержащую ID групп через запятую:
   
   • Добавляем пользовательский столбец: AddColumn > General > Custom Column
     
     
   • Удалим все колонки кроме ID и Группировка:
     
     
   • Сгруппируем по колонке “Группировка”:
     
     
     
     
   • Добавим еще одну колонку следующим образом:
     
     
   • Раскроем список так, чтобы получились значения через запятую:
     
     
   • И провалимся в получившуюся ячейку:
     
     
   • Power BI после этого преобразует запрос в переменную, которую можно использовать в динамических SQL-запросах:
     
     
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.

   
   Чтобы от него избавиться, нужно изменить уровень конфиденциальности:
   
   
   
   
   
   После этого обновить запросы.
8. Делаем переменную “ID пользователей” аналогично тому, как это сделано для “ID групп” (т.е. делаем ссылку от запроса Пользователи и т.д.). С помощью нее мы сгенерируем SQL-запрос, который позволит выбрать только нужных для анализа пользователей. Предварительно удалим дубликаты user_id:
   
   
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]

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

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

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

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



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

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



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

Далее описываться способ размещения оставшихся таблиц в общем отчете 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 – разархивирование.
   
   Пример батника:
   
   
   
   Файл, содержащий настройки для батника:
   
   
   

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

1. Для удобства создадим параметр, который будет содержать путь к папке, из которой мы извлечем содержимое всех .access.php
   
   
2. Выберем запрос типа “Folder” и выберем наш параметр в качестве пути:
   
   
3. Развернем поле Content:
   
   
   
   ХХХХХХ – это разделитель колонок, нужен чтобы столбец был один после импорта данных из всех файлов.
4. После этого Power BI удалит нужную нам колонку, содержащую путь к .access.php. Поэтому нам нужно отредактировать шаг “Remove other columns1”, выбрав в нем “Folder Path”:
   
   
5. Оставляем колонки: Folder Path и Column1.
6. Чтобы удалить из Folder Path абсолютный путь к локальному файлу воспользуемся заменой:
   
   
7. Файлы .access.php содержат настройки доступа в формате:
   
   

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

   
   Наша задача раскидать по колонкам: Путь, ID группы, Уровень доступа. Делается это с помощью фильтров, разделения по столбцам (Split Column) и пользовательских столбцов (Custom column).
8. В итоге должна получиться следующая таблица:
   
   
   
   Как видно в поле ID группы есть “*” (доступ для всех). Чтобы была возможность задать связь с другими запросами нам нужно сделать это поле целочисленным, при этом не потеряв информации о “*” (что означает для всех групп). Сделаем два запроса, типа “ссылка” на исходный запрос DotAccessPhp:
   
   
   • Первый DotAccessPhpForRels будет содержать только целочисленные ID групп (используем фильтр, убрав * в колонке ID группы) – ее мы и свяжем с остальными запросами:
     
   • Второй – DotAccessPhpForAll – только * (используем фильтр).

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



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



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

Инфо-блоки

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

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

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:
   
   

Формы

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

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. Обновляем схему связей:
   
   

Модули

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. Обновляем связи:
   
   

Табло

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

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



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



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

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

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

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



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


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

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

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



В этом отчете также добавлены ссылки на редактирование всех .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. Спасибо моим соратникам за помощь в подготовке этой статьи: Александру Воронкову, Евгению Шапочкину, Алексею Титову.