Revisión de los niveles de acceso del usuario utilizando Power BI utilizando el ejemplo de Bitrix CMS (BUS)

El artículo muestra un ejemplo del uso de Power BI para analizar el acceso de los usuarios en un sitio que ejecuta 1C-Bitrix.

El problema

Con el tiempo, cada vez más usuarios están conectados al desarrollo de recursos de Internet de una forma u otra, con derechos avanzados que el usuario promedio del sitio.

En este sentido, cada vez es más difícil controlar el acceso a funciones confidenciales. Bueno, si se escriben reglas que ayuden a controlar el acceso a un nivel más o menos seguro. Pero a menudo sucede que los colegas se mudan a otras unidades, van por decretos :) o se van, y el acceso permanece.

Naturalmente, esto conlleva diferentes amenazas: la fuga de la base de clientes, bueno, hasta sabotaje, etc.
La edad de los proyectos con los que trabajo ya ha sido de 10 años. La base de datos tiene cientos de miles de usuarios, incluidos cientos con derechos privilegiados.

Este artículo muestra un ejemplo de cómo puede simplificar la auditoría de usuarios a varios objetos del sitio bajo el control de Bitrix CMS (BUS).

El problema es que el panel de administración de Bitrix no brinda la oportunidad de obtener una imagen completa con los accesos; Hacer clic en un montón de enlaces y esperar a que se carguen las páginas de administración también es desagradable.

Power BI se utilizará como la herramienta principal para esto (un poco fuera de su propósito principal :)

Se supone que el lector ya está familiarizado con Power BI en un nivel básico, conoce los conceptos básicos de SQL y también sabe cómo usar el panel de administración de Bitrix. Se considerarán las características de accesibilidad estándar de Bitrix.

Desventajas del panel de administración de Bitrix

Es imposible realizar una auditoría en el panel de administración estándar durante un tiempo aceptable debido a la falta de una imagen coherente con los accesos: datos resumidos para todos los módulos / secciones / bloques de información, etc., a los que se otorga acceso.

Rendimiento del administrador:

1. En la sección "Grupos de usuarios" del panel de administración de Bitrix hay una función que genera una consulta SQL para seleccionar todos los grupos con un recuento del número de usuarios. Todo está bien cuando la base es pequeña. Pero con una base de datos de cientos de miles de usuarios, con cientos de grupos de usuarios en un servidor dedicado con 128 GB de RAM, simplemente abrir esta sección lleva 8 segundos.
2. También hay una solicitud en la tarjeta de grupo, que por alguna razón selecciona todos los grupos de usuarios, en lugar de recibir datos solo para el seleccionado. Pérdida en espera 3 seg.

Soluciones

Por lo general, hay varias soluciones al problema.

1. Escriba las reglas para proporcionar acceso a los sitios y sígalas claramente.
2. Realizar periódicamente auditorías de acceso.
3. Esperando lo mejor y no desperdiciando recursos limitados de la compañía.

Este artículo considerará solo el segundo método.

Las tareas

1. Elija herramientas que le permitirán obtener rápidamente datos sobre los niveles de acceso de cada usuario con derechos extendidos.
2. Configure las herramientas para que muestren claramente la imagen con accesos en su conjunto con los detalles y la interactividad necesarios.
3. Realizar auditorías de acceso.

Acceder al almacenamiento en Bitrix

Bitrix le permite configurar con bastante flexibilidad los derechos a través de grupos de usuarios.
La configuración de acceso se almacena principalmente en tablas MySQL. Algunas configuraciones se almacenan en archivos. Por ejemplo, los accesos a archivos y carpetas se almacenan en archivos .access.php.

El análisis del acceso de usuarios y grupos de usuarios a:

• bloques de información
• formularios web con nivel de acceso
• estados de formularios web con nivel de acceso
• secciones del sitio
• Módulos Bitrix con niveles de acceso.

Las herramientas

1. Power BI Desktop, que le permite visualizar bien los datos, obtener datos de numerosas fuentes (casi) fuera de la caja. En realidad, Power BI puede reemplazarse con el Excel 2016 habitual y superior: PowerQuery ya está incluido en su entrega, a través del cual puede seleccionar todos los datos para el análisis. Sin embargo, Power BI le permite mostrar interactivamente datos basados ​​en sus relaciones, y esto le permite encontrar rápidamente dependencias ocultas.
2. Se requiere MySQL Connector para poder crear una consulta a través de Power BI al servidor web MySQL.
3. Kitty o Putty para hacer un túnel a MySql si el acceso a la base de datos está abierto solo a través de SSH.

Se obtiene el siguiente esquema de acceso: Power BI → MySQL Connector → Kitty → MySQL.

Power BI

Power BI Desktop: le permite visualizar bien los datos, obtener datos de numerosas fuentes (casi) fuera de la caja. En realidad, Power BI puede reemplazarse con el Excel 2016 habitual y superior: PowerQuery ya está incluido en su entrega, a través del cual puede seleccionar todos los datos para el análisis. Sin embargo, Power BI le permite mostrar interactivamente datos basados ​​en sus relaciones, y esto le permite encontrar rápidamente dependencias ocultas, que es lo que necesitamos para las revisiones de acceso.

Puedes descargarlo en la página oficial .

Conector MySQL

Ir a la página Descargue e instale. A veces tendrá que reiniciar su PC después de la instalación.

Kitty / masilla

Para ejecutar consultas SQL en la base de datos de Bitrix, deberá configurar el túnel.

1. Ingrese el IP del servidor y el puerto
   
   
2. Martillamos nombre de usuario y contraseña en SSH
   
   
3. Hacemos reenvío de puertos:
   
   
4. Guardamos las configuraciones hechas para uso futuro en el perfil:
   
   
5. Empezamos

También puede descargar Putty y ejecutarlo con el comando:

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

Naturalmente, Kitty / Putty debe estar ejecutándose antes de actualizar los datos en Power BI.

Usuarios y grupos de usuarios

Como en muchos CMS, Bitrix implementa un mecanismo para delimitar los derechos de acceso a través de grupos de usuarios.

Descargue entidades de la base de datos en el modelo de datos de Power BI:

• Grupos
• Los usuarios

... así como las relaciones entre grupos y usuarios.

Grupos

Nos restringimos solo a grupos activos.

La lista de grupos se almacena en la tabla b_group.

1. Crea una conexión:
   
   
2. Ingrese:
   
   
   1. en el campo Servidor: localhost: 3306
   2. en el campo Base de datos: bitrix_db (nombre de la base de datos con la que trabaja Bitrix)
   3. Consulta SQL:
      
      

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

      
      
      
   
   
3. Ingrese el nombre de usuario y la contraseña a la base de datos y envíe una solicitud:
   
   
   
   
   
   
   
4. Inmediatamente dé un nombre amigable a la solicitud:
   
   
5. Enumeramos los grupos en una hoja separada en forma de tabla:
   
   
   

Este método de extracción y presentación de datos será similar para otras consultas relacionadas con la base de datos Bitrix.

Los usuarios

Ahora descargue todos los usuarios que tienen derechos avanzados. Pero no debe descargar usuarios incluidos solo en grupos que no les otorgan ningún derecho adicional, por ejemplo, "Todos los usuarios, incluidos los no registrados" (vale la pena señalar que la conexión de este grupo con los usuarios se almacena para todos los usuarios registrados antes de la versión 12. En versiones más recientes versiones, el grupo se considera sistémico y no almacena datos sobre la comunicación con los usuarios de la base de datos).

Nos restringimos solo a usuarios activados.

Para hacer esto, necesitas:

1. Seleccione todas las ID de los grupos que otorgan derechos extendidos. Esto es necesario para ahorrar en tráfico, porque El número de entradas en b_user_group puede llegar a millones dependiendo de la complejidad del proyecto.
2. Crear una solicitud dinámica para descargar enlaces Usuario - Grupo
3. Descargar usuarios que tienen un enlace de la cláusula 2.

Comencemos:

1. Llame al editor de consultas: Inicio → Editar consultas
2. Creemos un enlace a la solicitud inicial de "Grupo":
   
   
3. Cambie el nombre de la nueva solicitud a "ID de grupo" y seleccione solo aquellos grupos que sean interesantes desde el punto de vista de la seguridad.
   
   
4. Ahora obtenemos una línea que contiene los ID de grupo separados por comas:
   
   • Agregue una columna personalizada: Agregar columna → General → Columna personalizada
     
     
   • Eliminar todas las columnas excepto ID y agrupación:
     
     
   • Agrupe por la columna "Agrupación":
     
     
     
     
   • Agregue otra columna de la siguiente manera:
     
     
   • Expandamos la lista para que los valores estén separados por comas:
     
     
   • Y caer en la celda resultante:
     
     
   • Power BI luego convierte la consulta en una variable que se puede usar en consultas SQL dinámicas:
     
     
5. Creemos la solicitud "Grupo de usuarios" que contiene la relación del usuario con el grupo, de forma similar a como se hace en la sección "Grupos".
   
   Consulta 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 (); 

   
   XXX deberá reemplazarse con ID de grupo, separados por comas.
   
6. Llamaremos a la fuente de la solicitud de edición y la reemplazaremos con lo siguiente:
   
   

    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. Después de eso, puede obtener la siguiente advertencia:
   
   

    Formula.Firewall: Query '-' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. 

   
   Para deshacerse de él, debe cambiar el nivel de privacidad:
   
   
   
   
   
   Después de eso, actualice las consultas.
8. Hacemos la variable "ID de usuario" de la misma manera que se hizo para "ID de grupo" (es decir, hacemos un enlace desde la solicitud de los usuarios, etc.). Al usarlo, generaremos una consulta SQL que nos permite seleccionar solo los usuarios que necesitamos para el análisis. Primero elimine el ID_usuario duplicado:
   
   
9. Creamos una solicitud para una selección de usuarios, similar a cómo se hace para "Grupo de usuarios".
   
   

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

   
   XXX deberá reemplazar las ID de usuario.
   

Establecer relaciones entre solicitudes

Para que Power BI filtre los datos de forma interactiva en diferentes vistas, debe definir las relaciones entre las consultas. En nuestro caso, necesitamos conectar los campos:

• "Grupo de usuarios" [id_grupo] → "Grupos" [id]
• "Grupo de usuarios" [user_id] → "Usuarios" [id]

Del mismo modo, vincularemos otras consultas.

Informe de usuario y grupo de usuarios

En la pestaña Informes, mostramos una lista de usuarios y grupos usando la Tabla como elemento de visualización.

En la solicitud "Usuarios", seleccione los campos: apellido, nombre, último registro, correo electrónico.
Desde la solicitud "Grupo de usuarios", seleccione el campo group_id.
Porque Dado que asignamos conexiones entre solicitudes, Power BI podrá utilizar correctamente la función de agregación Count para contar el número de grupos a los que pertenece cada usuario individual.



Agreguemos otra tabla junto a ella y seleccione el campo de nombre de la solicitud de Grupo, y el campo user_id de la solicitud de Grupo de usuarios: establezca la agregación "Count (Distinct)" para que vea el número de usuarios en el grupo.

Porque Las solicitudes de "Grupo" y "Usuario" se conectan a través de una solicitud asociativa "Grupo de usuarios", luego, cuando hace clic en un usuario en la tabla con una lista de grupos, solo se mostrarán aquellos grupos a los que pertenece el usuario seleccionado. Y viceversa.



De esta manera, puede hacer clic en cada usuario y ver de qué grupos es miembro, o hacer clic en los grupos y ver qué usuarios son parte de un grupo. Bueno, entonces tome decisiones sobre el cambio de acceso para el usuario.

A continuación se describe cómo colocar las tablas restantes en el informe general de Power BI, porque Esto se hace de manera similar.

.access.php

En Bitrix, puede configurar el acceso a carpetas y archivos especificando los números de grupo y el nivel de acceso requerido en los archivos .access.php.

Nuestra tarea es reducir los datos de todos los archivos .access.php dispersos por el servidor del proyecto en una vista tabular.

Para hacer esto:

1. Buscamos y archivamos todos los archivos .access.php del servidor, guardando las rutas a estos archivos.
   Utilicé terminalka para buscar, copiar y archivar archivos encontrados. Ejemplo de comando:
   
   

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

   
   Aquí:
   
   
   • BITRIX_PROJECT_DIR - carpeta con el proyecto en Bitrix.
   • OUTPUT_DIR: ruta a la carpeta en la que se colocará el archivo .access.php.files.txt con una lista de .access.php encontrado, así como el archivo .access.php.files.tar que contiene copias de todos los archivos .access.php encontrados.
   
   Naturalmente, si hay muchos proyectos (se utiliza multisitio), entonces seleccionamos una carpeta que contiene todos los proyectos.
2. Descargue y descomprima el archivo .access.php en algún lugar al lado del proyecto Power BI.
   Escribí un archivo por lotes que hace esto automáticamente: la descarga se implementa a través de wget; a través de 7zip - descomprimir.
   
   Ejemplo de archivo por lotes:
   
   
   
   Archivo que contiene configuraciones para archivo por lotes:
   
   
   

Ahora cree una consulta que resuma el contenido de todos .access.php en forma de tabla.

1. Por conveniencia, cree un parámetro que contendrá la ruta a la carpeta desde la cual extraeremos el contenido de todos los archivos .access.php
   
   
2. Seleccionaremos una solicitud de tipo "Carpeta" y elegiremos nuestro parámetro como la ruta:
   
   
3. Expanda el campo Contenido:
   
   
   
   XXXXXXX es un separador de columnas; necesita una columna después de importar datos de todos los archivos.
4. Después de eso, Power BI eliminará la columna que necesitamos, que contiene la ruta a .access.php. Por lo tanto, necesitamos editar el paso "Eliminar otras columnas1", seleccionando "Ruta de carpeta" en él:
   
   
5. Deje las columnas: Ruta de la carpeta y Columna1.
6. Para eliminar la ruta absoluta al archivo local de la ruta de la carpeta, use el reemplazo:
   
   
7. Los archivos .access.php contienen configuraciones de acceso en el formato:
   
   

    $PERM[""]["ID "] = "< >"; 

   
   Nuestra tarea es dispersar las columnas: Ruta, ID de grupo, Nivel de acceso. Esto se realiza mediante filtros, separación de columnas (columna dividida) y columnas personalizadas (columna personalizada).
8. El resultado debería ser la siguiente tabla:
   
   
   
   Como puede ver en el campo de ID de grupo, hay "*" (acceso para todos). Para poder especificar una conexión con otras solicitudes, necesitamos hacer que este campo sea entero, sin perder información sobre "*" (lo que significa para todos los grupos). Hagamos dos solicitudes, como un "enlace" a la solicitud DotAccessPhp original:
   
   
   • El primer DotAccessPhpForRels contendrá solo ID de grupo de enteros (usamos el filtro eliminando * en la columna de ID de grupo); lo conectaremos con el resto de las solicitudes:
     
   • El segundo - DotAccessPhpForAll - solo * (use el filtro).

Diagrama de conexión:



Para mostrar solo datos relacionados al seleccionar un archivo de DotAccessForRels en otras vistas, cambie el parámetro "Dirección del filtro cruzado" a Ambos:



Para otras solicitudes que se agregarán a continuación, esto también debe hacerse.

Bloques de información

Debe descargar la lista de bloques de información y la tabla de enlaces de bloques de información con grupos.

Solo cargaremos información sobre bloques de información activos.

1. Creamos la solicitud "Bloques de información". Consulta 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. Actualizamos el esquema de comunicación, sin olvidar cambiar el parámetro "Dirección del filtro cruzado" a Ambos:
   
   

Formas

En el caso de los formularios, los derechos de los grupos de usuarios se otorgan tanto a los formularios como a los estados en los que reside el resultado de completar el formulario.

1. Cree una solicitud de "Formularios":
   
   

    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. Cree una solicitud "Grupo de formulario":
   
   

    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. Creamos la solicitud "Estado de formularios".
   
   

    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. Crear una solicitud "Estados de grupo de formularios"
   
   

    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. Actualización del esquema de conexión:
   
   

Módulos

1. Creamos una solicitud "Grupo de módulos".
   
   

    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. Actualización de comunicaciones:
   
   

Marcador

Personalizamos los estilos de tablas, utilizamos el espacio utilizable al máximo.

El resultado debería ser similar al siguiente:



Un pequeño marcador modificado (número de elementos en las tablas):



Por cierto, es conveniente configurar primero la apariencia de una tabla y luego simplemente aplicar su vista a otras tablas usando Inicio → Copiar formato. Esta función actúa de la misma manera que en Word y Excel (Formato por muestra).

Enlaces de administrador

Para que pueda ir rápidamente al sitio y realizar ajustes en el panel de administración, puede agregar una columna personalizada en el lenguaje DAX y convertirlo en un tipo de "URL web". Para hacer esto, seleccione la columna creada y asigne el tipo apropiado (Modelado → Propiedades → Categoría de datos → URL web).

Ejemplo para una solicitud de grupo:



Agregue una columna a la vista:


Ahora puede simplemente hacer clic en la celda de la tabla e ir a la tarjeta de grupo en el panel de administración de Bitrix.

Informe de archivos

Para mayor comodidad, puede hacer un informe separado colocando tablas sobre el acceso a los archivos y secciones del recurso de Internet:



Este informe también agregó enlaces para editar todo .access.php directamente a través del panel de administración de Bitrix.

Resumen

Bitrix es un campeón entre los monstruos cms con ventajas y desventajas obvias, hermoso por fuera y terrible por dentro. No tiene herramientas de administración de acceso convenientes. Pero este problema se resolvió con la ayuda de herramientas gratuitas, sin atraer a los valiosos programadores a este proceso.

Las ventajas de este enfoque también incluyen la capacidad de complementar rápidamente el modelo en Power BI con información adicional de Bitrix, por ejemplo, alguien quiere saber cuándo se crearon o cambiaron .access.php y otros.

Ahora, después de crear el modelo de derechos de acceso y visualizarlo en Power BI, es suficiente:

1. haga clic constantemente en usuarios, grupos, formularios, archivos y en tiempo real vea todas las conexiones relacionadas con el acceso;
2. vaya rápidamente a las páginas de administración necesarias para realizar ediciones;
3. Actualice el modelo de datos con los últimos datos de Bitrix directamente en Power BI.

Como resultado, se realizó una auditoría y se realizó un ajuste en el acceso de los usuarios.

PD: en el mercado hay un módulo gratuito "Centro de control de acceso", pero es muy limitado, y el último comentario tiene más de 5 años. Tal vez a alguien le gustará la idea de construir un tablero de instrumentos en Bitrix y lo implementará como un módulo ...

PS2 Si alguien está interesado en el tema del uso de Power BI para resolver el problema de encontrar dependencias ocultas en varios sistemas de contabilidad, escriba los comentarios. Luego escribiré algunos artículos más sobre este tema.

PS3 Gracias a mis colegas por ayudarme a preparar este artículo: Alexander Voronkov, Evgeny Shapochkin, Alexei Titov.