Revision of user access levels using Power BI using the example of Bitrix CMS (BUS)

The article shows an example of using Power BI to analyze user access on a site running 1C-Bitrix.

Problem

Over time, more and more users are connected to the development of Internet resources in one way or another, with advanced rights than the average user of the site.

In this regard, it is becoming increasingly difficult to control access to confidential functions. Well, if rules are written that help control access at a more or less secure level. But it often happens that colleagues move to other units, go on decrees :) or leave, and access remains.

Naturally, this carries different threats: the leak of the customer base, well, up to sabotage, etc. The
age of the projects with which I have been working has been 10 years. The database has hundreds of thousands of users, including hundreds with privileged rights.

This article shows an example of how you can simplify the audit of users to various site objects under the control of Bitrix CMS (BUS).

The problem is that the Bitrix admin panel does not provide an opportunity to get a complete picture with accesses; Clicking on a bunch of links and waiting for the admin pages to load is also unpleasant.

Power BI will be used as the main tool for this (a little out of its main purpose :)

It is assumed that the reader is already familiar with Power BI at a basic level, knows the basics of SQL, and also knows how to use the Bitrix admin panel. Bitrix’s standard accessibility features will be considered.

Disadvantages of the Bitrix admin panel

It is impossible to conduct an audit in the standard admin panel for an acceptable time due to the lack of a coherent picture with accesses - summary data for all modules / sections / info blocks, etc., to which access is granted.

Admin Performance:

1. In the “User groups” section of the Bitrix admin panel there is a feature that generates an SQL query to select all groups with a count of the number of users. Everything is good when the base is small. But with a database of hundreds of thousands of users, with hundreds of user groups on a dedicated server with 128 GB of RAM, simply opening this section takes 8 seconds.
2. There is also a request in the group card, which for some reason selects all user groups, instead of receiving data only for the selected one. Loss on hold 3 sec.

Solutions

Usually there are several solutions to the problem.

1. Write the rules for providing access to sites and clearly follow them.
2. Periodically conduct access audits.
3. Hoping for the best and not wasting limited company resources.

This article will consider just the second method.

Tasks

1. Choose tools that will allow you to quickly obtain data on the access levels of each user with extended rights.
2. Set up the tools so that they clearly show the picture with accesses as a whole with the necessary detail and interactivity.
3. Conduct access audits.

Access storage in Bitrix

Bitrix allows you to quite flexibly configure rights through user groups.
Access settings are stored mainly in MySQL tables. Some settings are stored in files. For example, file and folder accesses are stored in .access.php files.

The analysis of user and user group access to:

• info blocks
• web forms with access level
• web form statuses with access level
• sections of the site
• Bitrix modules with access levels

Instruments

1. Power BI Desktop, which allows you to visualize data well, get data from numerous sources (almost) out of the box. Actually, Power BI can be replaced with the usual Excel 2016 and higher - PowerQuery is already included in its delivery, through which you can select all the data for analysis. However, Power BI allows you to interactively display data based on their relationships, and this allows you to quickly find hidden dependencies.
2. The MySQL Connector is required to be able to create a query through Power BI to the MySQL web server.
3. Kitty or Putty for tunneling to MySql if access to the database is open only through SSH.

The following access scheme is obtained: Power BI → MySQL Connector → Kitty → MySQL.

Power BI

Power BI Desktop - allows you to visualize data well, get data from numerous sources (almost) out of the box. Actually, Power BI can be replaced with the usual Excel 2016 and higher - PowerQuery is already included in its delivery, through which you can select all the data for analysis. However, Power BI allows you to interactively display data based on their relationships, and this allows you to quickly find hidden dependencies, which is what we need for access revisions.

You can download it on the official page .

MySQL Connector

Go to the page . Download and install. Sometimes you will have to restart your PC after installation.

Kitty / putty

To execute SQL queries to the Bitrix database, you will need to configure the tunnel.

1. Enter the server IP and port
   
   
2. We hammer username and password on SSH
   
   
3. We make port forwarding:
   
   
4. We save the settings made for future use in the profile:
   
   
5. We start.

You can also just download Putty and run it with the command:

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

Naturally, Kitty / Putty must be running before updating data in Power BI.

Users and user groups

As in many CMS, Bitrix implements a mechanism for delimiting access rights through user groups.

Unload entities from the database into the Power BI data model:

• Groups
• Users

... as well as relationships between groups and users.

Groups

We restrict ourselves only to active groups.

The list of groups is stored in the b_group table.

1. Create a connection:
   
   
2. Enter:
   
   
   1. in the Server: localhost: 3306 field
   2. in the Database: bitrix_db field (name of the database with which Bitrix works)
   3. SQL query:
      
      

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

      
      
      
   
   
3. Enter the login and password to the database and send a request:
   
   
   
   
   
   
   
4. Immediately give a friendly name to the request:
   
   
5. We list the groups on a separate sheet in a table form:
   
   
   

This method of extracting and presenting data will be similar for other queries related to the Bitrix database.

Users

Now unload all users who have advanced rights. But you should not unload users included only in groups that do not give them any additional rights, for example, “All users, including unregistered” (it is worth noting that the connection of this group with users is stored for all users registered before version 12. In newer versions, the group is considered systemic and does not store data on communication with database users).

We restrict ourselves to activated users only.

To do this, you need:

1. Select all IDs of groups that grant extended rights. This is necessary to save on traffic, because the number of entries in b_user_group can reach millions depending on the complexity of the project.
2. Create a dynamic request for unloading links User - Group
3. Unload users who have a link from clause 2.

Let's start:

1. Call the query editor: Home → Edit Queries
2. Let's create a link to the initial request of “Group”:
   
   
3. Rename the new request to “Group ID” and select only those groups that are interesting from a security point of view.
   
   
4. Now we get a line containing the group IDs separated by commas:
   
   • Add a custom column: AddColumn → General → Custom Column
     
     
   • Delete all columns except ID and Grouping:
     
     
   • Group by the “Grouping” column:
     
     
     
     
   • Add another column as follows:
     
     
   • Let's expand the list so that the values ​​are separated by commas:
     
     
   • И провалимся в получившуюся ячейку:
     
     
   • 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. We make the variable “User ID” in the same way as it was done for “Group ID” (that is, we make a link from the Users request, etc.). Using it, we will generate an SQL query that allows us to select only the users we need for analysis. First remove duplicate user_id:
   
   
9. We create a request for a selection of users, similar to how it is done for “User-group”.
   
   

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

   
   XXX will need to replace user IDs.
   

Setting up relationships between requests

In order for Power BI to interactively filter data in different views, you need to define relationships between queries. In our case, we need to connect the fields:

• “User-group” [group_id] → “Groups” [id]
• “User-group” [user_id] → “Users” [id]

Similarly, we will bind other queries.

User and User Group Report

On the Reports tab, we display a list of users and groups using Table as a visualization element.

From the “Users” request, select the fields: last_name, name, last_login, email.
From the “User-group” request, select the group_id field.
Because Since we assigned connections between requests, Power BI will be able to correctly use the aggregating function Count to count the number of groups each individual user belongs to.



Let's add another Table next to it and select the name field from the Group request, and the user_id field from the User-group request - set the “Count (Distinct)” aggregation for it to see the number of users in the group.

Because “Group” and “User” requests are connected through an associative request “User-group”, then when you click on a user in the table with a list of groups, only those groups into which the selected user belongs will be displayed. And vice versa.



In this way, you can click either each user and see which groups he is a member of, or click the groups and see which users are part of a group. Well, then make decisions about changing access for the user.

The following describes how to place the remaining tables in the Power BI general report, because this is done in a similar way.

.access.php

In Bitrix you can set access to folders and files by specifying group numbers and the required access level in .access.php files.

Our task is to reduce the data from all .access.php files scattered around the project server into a tabular view.

For this:

1. We search and archive all .access.php files from the server, saving the paths to these files.
   I used terminalka to search, copy and archive found files. Command example:
   
   

   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 {} \;

   
   Here:
   
   
   • BITRIX_PROJECT_DIR - folder with the project on Bitrix.
   • OUTPUT_DIR - the path to the folder in which the .access.php.files.txt file with a list of .access.php found, as well as the .access.php.files.tar archive containing copies of all found .access.php will be placed.
   
   Naturally, if there are many projects (multisite is used), then we select a folder containing all the projects.
2. Download and unzip the .access.php archive somewhere next to the Power BI project.
   I wrote a batch file that does this automatically: download is implemented through wget; through 7zip - unzipping.
   
   An example of a batch file:
   
   
   
   A file containing settings for a batch file:
   
   
   

Now create a query that will summarize the contents of all .access.php in a table form.

1. For convenience, create a parameter that will contain the path to the folder from which we will extract the contents of all .access.php
   
   
2. We’ll select a request of type “Folder” and choose our parameter as the path:
   
   
3. Expand the Content field:
   
   
   
   XXXXXXX is the column separator, we need the column to be one after importing data from all files.
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 – только * (используем фильтр).

Link scheme: To select only related data when you select a file from DotAccessForRels in other views, you need to change the “Cross filter direction” parameter to Both:



For other requests that will be added below, this also needs to be done.

Info blocks

You need to unload the list of info blocks and the table of links of info blocks with groups.

We will only upload information about active info blocks.

1. We create the request “Infoblocks”. SQL query:
   
   

   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. We update the communication scheme, not forgetting to change the “Cross filter direction” parameter to Both:
   
   

Forms

In the case of forms, rights for user groups are granted both to the forms themselves and to the statuses in which the result of filling out the form resides.

1. Create a request for “Forms”:
   
   

   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. Create a request “Form group”:
   
   

   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. We create the request “Forms Status”.
   
   

   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. Create a request “Forms group statuses”
   
   

   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. Updating the connection scheme:
   
   

Modules

1. We create a request “Module group”.
   
   

   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. Updating communications:
   
   

Scoreboard

We customize the styles of tables, use the usable space to the maximum.

As a result, something similar to the following should turn out:



A slightly modified scoreboard (number of elements in the tables):



By the way, it’s convenient to first set up the appearance of one table, and then just apply its view to other tables using Home → Format Painter. This function acts in the same way as in Word and Excel (Format by sample).

Admin Links

So that you can quickly go to the site in and make settings in the admin panel, you can add a custom column in the DAX language and make it a type of “Web URL”. To do this, select the created column and assign the appropriate type (Modeling → Properties → Data Category → Web URL).

Example for a Group request:



Add a column to the view:


Now you can simply click on the table cell and go to the group card in the Bitrix admin panel.

Files Report

For convenience, you can make a separate report by placing tables on it regarding access to files and sections of the Internet resource:



This report also added links to edit all .access.php directly through the Bitrix admin panel.

Summary

Bitrix is ​​a champion among cms monsters with obvious pros and cons, beautiful on the outside and terrible on the inside. It does not have convenient access administration tools. But this problem was solved with the help of free tools, without attracting valuable programmers time to this process.

The advantages of this approach also include the ability to quickly supplement the model in Power BI with additional information from Bitrix, for example, someone will want to know when .access.php and others were created or changed.

Now, after building the access rights model and visualizing it in Power BI enough:

1. consistently click on users, groups, forms, files and in real time see all the connections regarding access;
2. quickly go to the necessary admin pages to make edits;
3. Update the data model with the latest Bitrix data directly in Power BI.

As a result, an audit was carried out and an adjustment was made in user access.

PS In the marketplace there is a free module “Access Control Center”, but it is very limited, and the last comment to it is more than 5 years old. Perhaps someone will like the idea of ​​building such a dashboard right in Bitrix and he will implement it as a module ...

PS2. If anyone is interested in the topic of using Power BI to solve the problem of finding hidden dependencies in various accounting systems, then write in the comments. I will then write a few more articles on this subject.

PS3 Thanks to my colleagues for helping me prepare this article: Alexander Voronkov, Evgeny Shapochkin, Alexei Titov.