Révision des niveaux d'accès utilisateur à l'aide de Power BI à l'aide de l'exemple de Bitrix CMS (BUS)

L'article montre un exemple d'utilisation de Power BI pour analyser l'accès des utilisateurs sur un site exécutant 1C-Bitrix.

Le problème

Au fil du temps, de plus en plus d'utilisateurs sont connectés au développement des ressources Internet d'une manière ou d'une autre, avec des droits avancés que l'utilisateur moyen du site.

À cet égard, il devient de plus en plus difficile de contrôler l'accès aux fonctions confidentielles. Eh bien, si des règles sont écrites qui aident à contrôler l'accès à un niveau plus ou moins sécurisé. Mais il arrive souvent que des collègues déménagent dans d'autres unités, prennent des décrets :) ou partent, et l'accès reste.

Naturellement, cela comporte différentes menaces: la fuite de la clientèle, enfin, jusqu'au sabotage, etc.
L'âge des projets avec lesquels je travaille a déjà 10 ans. La base de données compte des centaines de milliers d'utilisateurs, dont des centaines avec des droits privilégiés.

Cet article montre comment vous pouvez simplifier l'audit des utilisateurs vers divers objets de site sous le contrôle de Bitrix CMS (BUS).

Le problème est que le panneau d'administration Bitrix ne fournit pas la possibilité d'obtenir une image complète avec les accès; Il est également désagréable de cliquer sur un tas de liens et d'attendre le chargement des pages d'administration.

Power BI sera utilisé comme outil principal pour cela (un peu hors de son objectif principal :)

Il est supposé que le lecteur est déjà familiarisé avec Power BI à un niveau de base, connaît les bases de SQL et sait également comment utiliser le panneau d'administration Bitrix. Les fonctionnalités d'accessibilité standard de Bitrix seront prises en compte.

Inconvénients du panneau d'administration Bitrix

Il est impossible d'effectuer un audit dans le panneau d'administration standard pendant un temps acceptable en raison du manque d'une image cohérente avec les accès - données récapitulatives pour tous les modules / sections / blocs d'informations, etc., auxquels l'accès est accordé.

Performances administratives:

1. Dans la section «Groupes d'utilisateurs» du panneau d'administration Bitrix, il existe une fonctionnalité qui génère une requête SQL pour sélectionner tous les groupes avec un décompte du nombre d'utilisateurs. Tout est bon quand la base est petite. Mais avec une base de données de centaines de milliers d'utilisateurs, avec des centaines de groupes d'utilisateurs sur un serveur dédié avec 128 Go de RAM, ouvrir simplement cette section prend 8 secondes.
2. Il existe également une demande dans la carte de groupe, qui pour une raison quelconque sélectionne tous les groupes d'utilisateurs, au lieu de recevoir des données uniquement pour celui sélectionné. Perte en attente 3 sec.

Des solutions

Il existe généralement plusieurs solutions au problème.

1. Écrivez les règles d'accès aux sites et suivez-les clairement.
2. Effectuer périodiquement des audits d'accès.
3. Espérant le meilleur et ne gaspillant pas les ressources limitées de l'entreprise.

Cet article ne considérera que la deuxième méthode.

Les tâches

1. Choisissez des outils qui vous permettront d'obtenir rapidement des données sur les niveaux d'accès de chaque utilisateur avec des droits étendus.
2. Configurez les outils pour qu'ils montrent clairement l'image avec les accès dans leur ensemble avec les détails et l'interactivité nécessaires.
3. Effectuer des audits d'accès.

Accéder au stockage dans Bitrix

Bitrix vous permet de configurer les droits de manière assez flexible via des groupes d'utilisateurs.
Les paramètres d'accès sont stockés principalement dans des tables MySQL. Certains paramètres sont stockés dans des fichiers. Par exemple, les accès aux fichiers et aux dossiers sont stockés dans des fichiers .access.php.

L'analyse de l'accès des utilisateurs et des groupes d'utilisateurs à:

• blocs d'informations
• formulaires Web avec niveau d'accès
• statuts de formulaire Web avec niveau d'accès
• sections du site
• Modules Bitrix avec niveaux d'accès

Les outils

1. Power BI Desktop, qui vous permet de bien visualiser les données, récupère les données de nombreuses sources (presque) hors de la boîte. En fait, Power BI peut être remplacé par Excel 2016 habituel et supérieur - PowerQuery est déjà inclus dans sa livraison, à travers lequel vous pouvez sélectionner toutes les données à analyser. Cependant, Power BI vous permet d'afficher de manière interactive des données en fonction de leurs relations, ce qui vous permet de trouver rapidement des dépendances cachées.
2. Le connecteur MySQL est requis pour pouvoir créer une requête via Power BI sur le serveur Web MySQL.
3. Kitty ou Putty pour le tunneling vers MySql si l'accès à la base de données est ouvert uniquement via SSH.

Le schéma d'accès suivant est obtenu: Power BI → Connecteur MySQL → Kitty → MySQL.

Power BI

Power BI Desktop - vous permet de bien visualiser les données, d'obtenir des données de nombreuses sources (presque) hors de la boîte. En fait, Power BI peut être remplacé par Excel 2016 habituel et supérieur - PowerQuery est déjà inclus dans sa livraison, à travers lequel vous pouvez sélectionner toutes les données à analyser. Cependant, Power BI vous permet d'afficher de manière interactive des données en fonction de leurs relations, ce qui vous permet de trouver rapidement des dépendances cachées, ce dont nous avons besoin pour les révisions d'accès.

Vous pouvez le télécharger sur la page officielle .

Connecteur MySQL

Allez à la page . Téléchargez et installez. Parfois, vous devrez redémarrer votre PC après l'installation.

Kitty / mastic

Pour exécuter des requêtes SQL sur la base de données Bitrix, vous devrez configurer le tunnel.

1. Entrez l'IP et le port du serveur
   
   
2. Nous martelons le nom d'utilisateur et le mot de passe sur SSH
   
   
3. Nous faisons la redirection de port:
   
   
4. Nous enregistrons les paramètres définis pour une utilisation future dans le profil:
   
   
5. Nous commençons.

Vous pouvez également simplement télécharger Putty et l'exécuter avec la commande:

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

Naturellement, Kitty / Putty doit être en cours d'exécution avant de mettre à jour les données dans Power BI.

Utilisateurs et groupes d'utilisateurs

Comme dans de nombreux CMS, Bitrix implémente un mécanisme de délimitation des droits d'accès via des groupes d'utilisateurs.

Déchargez des entités de la base de données dans le modèle de données Power BI:

• Les groupes
• Les utilisateurs

... ainsi que les relations entre les groupes et les utilisateurs.

Les groupes

Nous nous limitons uniquement aux groupes actifs.

La liste des groupes est stockée dans la table b_group.

1. Créez une connexion:
   
   
2. Entrez:
   
   
   1. dans le champ Serveur: localhost: 3306
   2. dans le champ Database: bitrix_db (nom de la base de données avec laquelle Bitrix travaille)
   3. Requête SQL:
      
      

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

      
      
      
   
   
3. Saisissez l'identifiant et le mot de passe de la base de données et envoyez une demande:
   
   
   
   
   
   
   
4. Donnez immédiatement un nom convivial à la demande:
   
   
5. Nous listons les groupes sur une feuille séparée sous forme de tableau:
   
   
   

Cette méthode d'extraction et de présentation des données sera similaire pour les autres requêtes liées à la base de données Bitrix.

Les utilisateurs

Déchargez maintenant tous les utilisateurs qui ont des droits avancés. Mais vous ne devez pas décharger les utilisateurs inclus uniquement dans des groupes qui ne leur accordent aucun droit supplémentaire, par exemple, "Tous les utilisateurs, y compris non enregistrés" (il convient de noter que la connexion de ce groupe avec les utilisateurs est stockée pour tous les utilisateurs enregistrés avant la version 12. Dans les versions plus récentes versions, le groupe est considéré comme systémique et ne stocke pas de données sur la communication avec les utilisateurs de la base de données).

Nous nous limitons aux utilisateurs activés uniquement.

Pour ce faire, vous avez besoin de:

1. Sélectionnez tous les ID des groupes qui accordent des droits étendus. Cela est nécessaire pour économiser sur le trafic, car le nombre d'entrées dans b_user_group peut atteindre des millions selon la complexité du projet.
2. Créer une demande dynamique de déchargement des liens Utilisateur - Groupe
3. Déchargez les utilisateurs qui ont un lien de la clause 2.

Commençons:

1. Appelez l'éditeur de requêtes: Accueil → Modifier les requêtes
2. Créons un lien vers la demande initiale de «Groupe»:
   
   
3. Renommez la nouvelle demande en «ID de groupe» et sélectionnez uniquement les groupes qui sont intéressants du point de vue de la sécurité.
   
   
4. Nous obtenons maintenant une ligne contenant les ID de groupe séparés par des virgules:
   
   • Ajouter une colonne personnalisée: AddColumn → Général → Colonne personnalisée
     
     
   • Supprimer toutes les colonnes sauf l'ID et le regroupement:
     
     
   • Regroupez par la colonne «Groupement»:
     
     
     
     
   • Ajoutez une autre colonne comme suit:
     
     
   • Développons la liste pour que les valeurs soient séparées par des virgules:
     
     
   • Et tomber dans la cellule résultante:
     
     
   • Power BI convertit ensuite la requête en une variable qui peut être utilisée dans les requêtes SQL dynamiques:
     
     
5. Créons la demande "Groupe d'utilisateurs" contenant la relation de l'utilisateur avec le groupe, similaire à la façon dont cela se fait dans la section "Groupes".
   
   Requête 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 devra être remplacé par des identifiants de groupe, séparés par des virgules.
   
6. Nous appellerons la source de la demande de modification et la remplacerons par la suivante:
   
   

    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. Après cela, vous pouvez obtenir l'avertissement suivant:
   
   

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

   
   Pour vous en débarrasser, vous devez modifier le niveau de confidentialité:
   
   
   
   
   
   Après cela, mettez à jour les requêtes.
8. Nous créons la variable «User ID» de la même manière que pour «Group ID» (c'est-à-dire que nous faisons un lien à partir de la demande des utilisateurs, etc.). En l'utilisant, nous générerons une requête SQL qui nous permettra de sélectionner uniquement les utilisateurs dont nous avons besoin pour l'analyse. Tout d'abord, supprimez l'ID utilisateur en double:
   
   
9. Nous créons une demande pour une sélection d'utilisateurs, semblable à la façon dont cela se fait pour le «groupe d'utilisateurs».
   
   

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

   
   XXX devra remplacer les ID utilisateur.
   

Configuration des relations entre les demandes

Pour que Power BI filtre les données de manière interactive dans différentes vues, vous devez définir des relations entre les requêtes. Dans notre cas, nous devons connecter les champs:

• "Groupe d'utilisateurs" [id_groupe] → "Groupes" [id]
• "Groupe d'utilisateurs" [id_utilisateur] → "Utilisateurs" [id]

De même, nous lierons d'autres requêtes.

Rapport utilisateur et groupe d'utilisateurs

Dans l'onglet Rapports, nous affichons une liste d'utilisateurs et de groupes utilisant Table comme élément de visualisation.

Dans la demande «Utilisateurs», sélectionnez les champs: nom, nom, dernière connexion, email.
Dans la demande "User-group", sélectionnez le champ group_id.
Parce que Étant donné que nous avons attribué des connexions entre les demandes, Power BI pourra utiliser correctement la fonction d'agrégation Count pour compter le nombre de groupes auxquels appartient chaque utilisateur individuel.



Ensuite, ajoutez une autre table et sélectionnez le champ de nom dans la demande de groupe et le champ user_id dans la demande de groupe d'utilisateurs - définissez l'agrégation «Count (Distinct)» pour qu'il puisse voir le nombre d'utilisateurs dans le groupe.

Parce que Les requêtes "Groupe" et "Utilisateur" sont connectées via une requête associative "Groupe d'utilisateurs", puis lorsque vous cliquez sur un utilisateur dans le tableau avec une liste de groupes, seuls les groupes auxquels appartient l'utilisateur sélectionné seront affichés. Et vice versa.



De cette façon, vous pouvez cliquer sur chaque utilisateur et voir à quels groupes il appartient, ou cliquer sur les groupes et voir quels utilisateurs font partie d'un groupe. Eh bien, prenez ensuite des décisions sur la modification de l'accès pour l'utilisateur.

Ce qui suit décrit comment placer les tables restantes dans le rapport général Power BI, car cela se fait de manière similaire.

.access.php

Dans Bitrix, vous pouvez définir l'accès aux dossiers et aux fichiers en spécifiant les numéros de groupe et le niveau d'accès requis dans les fichiers .access.php.

Notre tâche consiste à réduire les données de tous les fichiers .access.php dispersés autour du serveur de projet dans une vue tabulaire.

Pour ce faire:

1. Nous recherchons et archivons tous les fichiers .access.php à partir du serveur, en sauvegardant les chemins d'accès à ces fichiers.
   J'ai utilisé terminalka pour rechercher, copier et archiver les fichiers trouvés. Exemple de commande:
   
   

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

   
   Ici:
   
   
   • BITRIX_PROJECT_DIR - dossier avec le projet sur Bitrix.
   • OUTPUT_DIR - chemin d'accès au dossier dans lequel le fichier .access.php.files.txt avec une liste des fichiers .access.php trouvés sera placé, ainsi que l'archive .access.php.files.tar contenant des copies de tous les fichiers .access.php trouvés.
   
   Naturellement, s'il existe de nombreux projets (le multisite est utilisé), nous sélectionnons alors un dossier contenant tous les projets.
2. Téléchargez et décompressez l'archive .access.php quelque part à côté du projet Power BI.
   J'ai écrit un fichier batch qui le fait automatiquement: le téléchargement est implémenté via wget; à travers 7zip - décompression.
   
   Exemple de fichier batch:
   
   
   
   Fichier contenant les paramètres du fichier de commandes:
   
   
   

Créez maintenant une requête qui résumera le contenu de tous les .access.php sous forme de tableau.

1. Pour plus de commodité, créez un paramètre qui contiendra le chemin d'accès au dossier à partir duquel nous allons extraire le contenu de tous les .access.php
   
   
2. Nous allons sélectionner une demande de type "Dossier" et choisir notre paramètre comme chemin:
   
   
3. Développez le champ Contenu:
   
   
   
   XXXXXXX est un séparateur de colonnes; vous avez besoin d'une colonne après l'importation des données de tous les fichiers.
4. Après cela, Power BI supprimera la colonne dont nous avons besoin, contenant le chemin d'accès à .access.php. Par conséquent, nous devons modifier l'étape «Supprimer les autres colonnes1», en sélectionnant «Chemin du dossier»:
   
   
5. Laissez les colonnes: Chemin du dossier et Colonne1.
6. Pour supprimer le chemin absolu du fichier local du chemin du dossier, utilisez le remplacement:
   
   
7. Les fichiers .access.php contiennent des paramètres d'accès au format:
   
   

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

   
   Notre tâche consiste à disperser les colonnes: chemin, ID de groupe, niveau d'accès. Cela se fait à l'aide de filtres, de séparation des colonnes (colonne divisée) et de colonnes personnalisées (colonne personnalisée).
8. Le résultat doit être le tableau suivant:
   
   
   
   Comme vous pouvez le voir dans le champ ID de groupe, il y a «*» (accès pour tout le monde). Afin de pouvoir spécifier une connexion avec d'autres requêtes, nous devons rendre ce champ entier, sans perdre d'informations sur «*» (ce qui signifie pour tous les groupes). Faisons deux demandes, comme un «lien» vers la demande DotAccessPhp d'origine:
   
   
   • Le premier DotAccessPhpForRels ne contiendra que des identifiants de groupe entiers (nous utilisons le filtre en supprimant * dans la colonne ID de groupe) - nous le connecterons avec le reste des requêtes:
     
   • Le second - DotAccessPhpForAll - seulement * (utilisez le filtre).

Schéma de connexion:



Pour afficher uniquement les données associées lors de la sélection d'un fichier à partir de DotAccessForRels dans d'autres vues, changez le paramètre «Cross filter direction» en Both:



Pour les autres demandes qui seront ajoutées ci-dessous, cela doit également être fait.

Blocs d'informations

Vous devez décharger la liste des blocs d'informations et le tableau des liens des blocs d'informations avec les groupes.

Nous ne téléchargerons que des informations sur les blocs d'informations actifs.

1. Nous créons la requête «Infoblocks». Requête 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. Nous mettons à jour le schéma de communication, sans oublier de changer le paramètre «Cross filter direction» en Both:
   
   

Formulaires

Dans le cas des formulaires, les droits des groupes d'utilisateurs sont accordés à la fois aux formulaires eux-mêmes et aux statuts dans lesquels réside le résultat du remplissage du formulaire.

1. Créez une demande de «Formulaires»:
   
   

    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. Créez une demande "Groupe de formulaires":
   
   

    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. Nous créons la demande «Statut des formulaires».
   
   

    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. Créer une demande "Statuts de groupe de formulaires"
   
   

    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. Mise à jour du schéma de connexion:
   
   

Modules

1. Nous créons une requête «Groupe de modules».
   
   

    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. Mise à jour des communications:
   
   

Tableau d'affichage

Nous personnalisons les styles de tables, utilisons l'espace utilisable au maximum.

Le résultat devrait être quelque chose de semblable au suivant:



Un tableau de bord un peu modifié (nombre d'éléments dans les tableaux):



Soit dit en passant, il est pratique de configurer d'abord l'apparence d'une table, puis d'appliquer simplement sa vue à d'autres tables à l'aide de Accueil → Reproduire la mise en forme. Cette fonction agit de la même manière que dans Word et Excel (Format par exemple).

Liens d'administration

Pour pouvoir accéder rapidement au site et effectuer des réglages dans le panneau d'administration, vous pouvez ajouter une colonne personnalisée en langage DAX et en faire un type d '«URL Web». Pour ce faire, sélectionnez la colonne créée et attribuez le type approprié (Modélisation → Propriétés → Catégorie de données → URL Web).

Exemple pour une demande de groupe:



Ajoutez une colonne à la vue:


Maintenant, vous pouvez simplement cliquer sur la cellule du tableau et accéder à la carte de groupe dans le panneau d'administration Bitrix.

Rapport sur les fichiers

Pour plus de commodité, vous pouvez créer un rapport distinct en y plaçant des tableaux concernant l'accès aux fichiers et aux sections de la ressource Internet:



Ce rapport a également ajouté des liens pour modifier tous les .access.php directement via le panneau d'administration Bitrix.

Résumé

Bitrix est un champion parmi les monstres cms avec des avantages et des inconvénients évidents, beau à l'extérieur et terrible à l'intérieur. Il ne dispose pas d'outils d'administration d'accès pratiques. Mais ce problème a été résolu à l'aide d'outils gratuits, sans attirer de temps précieux pour les programmeurs dans ce processus.

Les avantages de cette approche incluent également la possibilité de compléter rapidement le modèle dans Power BI avec des informations supplémentaires de Bitrix, par exemple, quelqu'un veut savoir quand .access.php et d'autres ont été créés ou modifiés.

Maintenant, après la création du modèle de droits d'accès et sa visualisation dans Power BI, il suffit:

1. cliquez constamment sur les utilisateurs, les groupes, les formulaires, les fichiers et voyez en temps réel toutes les connexions concernant l'accès;
2. accédez rapidement aux pages d'administration nécessaires pour apporter des modifications;
3. Mettez à jour le modèle de données avec les dernières données Bitrix directement dans Power BI.

En conséquence, une vérification a été effectuée et un ajustement a été apporté à l'accès des utilisateurs.

PS Sur le marché, il existe un module gratuit "Access Control Center", mais il est très limité, et le dernier commentaire a plus de 5 ans. Peut-être que quelqu'un aimera l'idée de construire un tel tableau de bord directement dans Bitrix et qu'il l'implémentera en tant que module ...

PS2. Si quelqu'un s'intéresse au sujet de l'utilisation de Power BI pour résoudre le problème de la recherche de dépendances cachées dans divers systèmes comptables, écrivez dans les commentaires. J'écrirai ensuite quelques articles supplémentaires sur ce sujet.

PS3 Merci à mes collègues de m'avoir aidé à préparer cet article: Alexander Voronkov, Evgeny Shapochkin, Alexei Titov.