Ревізія рівнів доступу користувачів за допомогою Power BI на прикладі CMS Бітрікс (БУС)

by Степан Лютий · 30 Червня, 2019

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

Проблема

З плином часу до розвитку інтернет-ресурсів підключається все більше і більше користувачів так чи інакше, володіють розширеними правами ніж пересічний користувач сайту.

У зв’язку з цим все складніше контролювати доступ до конфіденційних функцій. Добре, якщо написані регламенти, які допомагають контролювати доступи на більш-менш безпечному рівні. Але часто буває так, що колеги переходять працювати в інші підрозділи, йдуть у декрети 🙂 або звільняються, а доступи залишаються.

Природно це несе різні загрози: витік бази клієнтів ну і аж до саботажу та ін.
Вік проектів, з якими я працюю вже склав 10 років. База налічує сотні тисяч користувачів, серед яких сотні з привілейованими правами.

У цій статті показаний приклад того, як можна спростити ревізію користувачів до різних об’єктів сайту під управлінням CMS Бітрікс (БУС).

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

В якості основного інструменту для цього буде використаний Power BI (трохи не за своїм основним призначенням 🙂

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

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

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

Продуктивність адмінки:

В розділі “Групи користувачів” адмінки Битрикса є фіча, яка генерує SQL-запит на вибірку всіх груп з підрахунком кількості користувачів. Все добре, коли база невелика. Але з базою на сотні тисяч користувачів, з сотнею користувальницьких груп на виділеному сервері з 128 Гб оперативки просте відкриття цього розділу займає 8 сек.
У картці групи теж є запит, який навіщось усі групи користувачів, замість того, щоб отримати дані тільки по вибраній. Втрати на очікуванні 3 сек.

Способи вирішення

Зазвичай є кілька рішень проблеми.

Написати регламенти з надання доступів до сайтів і чітко їм слідувати.
Періодично проводити ревізію доступів.
Сподіватися на краще і не витрачати обмежені ресурси компанії.

У цій статті буде розглянутий як раз другий спосіб.

Завдання

Вибрати інструменти, які дозволять оперативно отримати дані про рівні доступу кожного користувача з розширеними правами.
Налаштувати інструменти так, щоб вони наочно показували картину з доступами в цілому з необхідною деталізацією та інтерактивністю.
Провести ревізію доступів.

Зберігання доступів в Бітрікс

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

Буде розглянуто аналіз доступів користувачів і користувальницьких груп до:

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

Інструменти

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

Виходить наступна схема доступу: Power BI → MySQL Connector → Kitty → MySQL.

Power BI

Power BI Desktop – дозволяє добре візуалізувати дані, отримувати дані з численних джерел (майже) з коробки. Власне Power BI можна замінити звичайним Excel 2016 і вище – його поставку вже включено PowerQuery, через який можна вибрати всі дані для проведення аналізу. Однак, Power BI дозволяє інтерактивно переглядати дані з урахуванням їх взаємозв’язків, а це дозволяє швидко знаходити приховані залежності, що нам і потрібно для ревізії доступів.

Читайте також Як підрахувати кількість знаків в Microsoft Office Word

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

MySQL Connector

Переходимо на сторінку. Викачуємо і встановлюємо. Іноді доведеться перезавантажити ПК після установки.

Kitty/Putty

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

Вводимо IP сервера і порт
Забиваємо логін і пароль по SSH
Робимо кидок портів:
Зберігаємо в профіль зроблені налаштування для майбутніх використань:
Запускаємо.

Також можна просто завантажити 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. в полі Server: localhost:3306
2. у полі Database: bitrix_db (назва БД, з якою працює Бітрікс)
3. SQL-запит:
```
SELECT id, timestamp_x, active, name, description, anonymous FROM b_group WHERE active = 'Y';
```
Вводимо логін і пароль до БД і відправляємо запит:
Відразу даємо зрозуміле ім’я запиту:
Виводимо список груп на окремий аркуш у табличному вигляді:

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

Користувачі

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

Обмежимося тільки активованими користувачами.

Для цього потрібно:

Вибрати всі ID груп, що дають розширені права. Це потрібно, щоб заощадити на трафіку, т. к. кількість записів в b_user_group може доходити до мільйонів в залежності від складності проекту.
Створити динамічний запит на вивантаження зв’язків Користувач — Група
Вивантажити користувачів, що мають зв’язок з п. 2.

Почнемо:

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

Викличемо на редагування исходники запиту і замінимо його на наступне:

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)g WHERE.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

Після цього можна отримати наступне попередження:
```
Formula.Firewall: Query 'Користувач-група' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
```
Щоб від нього позбутися, потрібно змінити рівень конфіденційності:
Після цього оновити запити.
Робимо змінну “ID користувача” аналогічно тому, як це зроблено для “ID груп” (тобто робимо посилання від запиту Користувачів і т. д.). За допомогою неї ми згенеруємо SQL-запит, який дозволить вибрати тільки потрібні для аналізу користувачів. Попередньо видалити дублікати user_id:
Створюємо запит на вибірку користувачів, аналогічно тому, як це зроблено для “Користувач-група”.
```
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, розкиданих по серверу проекту, в табличний вигляд.

Для цього:

Шукаємо і архівуємо всі файли .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.
Природно, якщо багато проектів (використана багатосайтовість), то вибираємо папку, яка містить всі проекти.
Завантажуємо і розпаковуємо архів з .access.php де-небудь поруч з проектом Power BI.
Я написав батник, який робить це автоматично: через wget реалізовано скачування; через 7zip – розархівація.Приклад батника:
Файл, що містить настройки для батника:

Тепер створюємо запит, який зведе вміст всіх .access.php у табличному вигляді.

Для зручності створимо параметр, який буде містити шлях до папки, з якої ми винесемо вміст всіх .access.php
Виберемо запит типу “Folder” і виберемо наш параметр в якості шляху:
Розгорнемо поле Content:
ХХХХХХ – це роздільник колонок, потрібен, щоб стовпець був один після імпорту даних з файлів.
Після цього Power BI видалить потрібну нам колонку, що містить шлях до .access.php. Тому нам потрібно відредагувати крок “Remove other columns1”, вибравши в ньому “Folder Path”:
Залишаємо колонки: Folder Path і Column1.
Щоб видалити з Folder Path абсолютний шлях до локального файла скористаємося заміною:
Файли .access.php містять налаштування доступу в форматі:
```
$PERM["шлях"]["ID групи"] = "<Рівень доступу>";
```
Наше завдання розкидати по колонках: Шлях, ID групи, Рівень доступу. Робиться це за допомогою фільтрів, поділу по стовпцях (Split Column) і стовпців (Custom column).
У результаті повинна вийти наступна таблиця:
Як видно в полі ID групи є “*” (доступ для всіх). Щоб була можливість задати зв’язок з іншими запитами нам потрібно зробити це поле цілим числом, при цьому не втративши інформації про “*” (що означає для всіх груп). Зробимо два запити, посилання на вихідний запит DotAccessPhp:
- Перший DotAccessPhpForRels буде містити тільки цілочисельні ID груп (використовуємо фільтр, прибравши * в колонці ID групи) – її ми і зв’яжемо з іншими запитами:
- Другий – DotAccessPhpForAll – тільки * (використовуємо фільтр).

Схема зв’язків:

Щоб при виборі файлу з DotAccessForRels в інших поданнях показувалися тільки пов’язані дані, потрібно змінити параметр “Cross filter direction” на Both:

Для інших запитів, які будуть додані нижче це теж потрібно зробити.

Інфо-блоки

Необхідно вивантажити список інфо-блоків і таблицю зв’язків інфо-блоків з групами.

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

Створюємо запит “Инфоблоки”. 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';

Оновлюємо схему зв’язків, не забуваючи змінювати параметр “Cross filter direction” на Both:

Форми

У разі форм права для користувацьких груп видаються як на самі форми, так і на статуси, в яких перебуває результат заповнення форми.

Створюємо запит “Форми”:

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;

Створюємо запит “Форма-група”:

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;

Створюємо запит “Статуси форм”.

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;

Створюємо запит “Статуси форм-група”

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;

Оновлюємо схему зв’язків:

Модулі

Створюємо запит “Модуль-група”.

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;

Оновлюємо зв’язку:

Табло

Налаштовуємо стилі таблиць, використовуємо корисний простір по-максимуму.

У результаті повинно вийти щось схоже на таке:

Трохи доопрацьоване табло (кількість елементів в таблицях):

До речі зручно спочатку налаштувати вигляд однієї таблиці, а потім просто застосувати її вид на інші таблиці з допомогою Home → Format Painter. Дана функція діє так само, як і в Word і Excel (Формат за зразком).

Посилання в адмінку

Щоб можна було швидко переходити на сайт і робити налаштування в адмінці, можна додати власну колонку на мові DAX і зробити її тип “Web URL”. Для цього виберемо створену колонку і призначимо відповідний тип (Modeling → Властивості → Data Category → Web URL).

Приклад для запиту Групи:

Додамо колонку подання:

Тепер можна просто клікати на комірку таблиці і переходити в картку групи в адмінці Битрикса.

Звіт “Файли”

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

У цьому звіті також додані посилання на редагування всіх .access.php безпосередньо через адмінку Битрикса.

Підсумки

Бітрікс – це чемпіон серед cms-монстрів з очевидними плюсами і мінусами, красивий зовні і жахливий всередині. У ньому немає зручних засобів адміністрування доступів. Але дана проблема вирішена за допомогою безкоштовних інструментів, не залучаючи до цього процесу цінний час програмістів.

До переваг наведеного підходу також варто віднести можливість швидко доповнити модель Power BI додатковою інформацією з Битрикса, наприклад, хтось захоче дізнатися, коли були створені або змінені .access.php та ін.

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

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

У підсумку була проведена ревізія і зроблені коригування в доступі користувачів.

P. S. У маркетплейсе є безкоштовний модуль “Центр керування доступом”, але він дуже обмежений, а останнього коментарю до нього більше 5 років. Можливо комусь сподобається ідея побудови такого дашборда прямо в Битриксе і він реалізує її в якості модуля…

P. S. 2. Якщо комусь цікава тема використання Power BI для вирішення проблем пошуку прихованих залежностей в різних облікових системах, то пишіть в коментарях. Я тоді напишу ще кілька статей на цю тему.

P. S. 3. Спасибі моїм соратникам за допомогу в підготовці цієї статті: Олександру Воронкову, Євгену Шапочкину, Олексію Титову.

Читайте також Як зробити гіперпосилання в Word