Revisão dos níveis de acesso do usuário usando o Power BI usando o exemplo do Bitrix CMS (BUS)

O artigo mostra um exemplo de uso do Power BI para analisar o acesso do usuário em um site executando o 1C-Bitrix.

O problema

Com o tempo, mais e mais usuários estão conectados ao desenvolvimento de recursos da Internet, de uma maneira ou de outra, com direitos avançados que o usuário médio do site.

Nesse sentido, está se tornando cada vez mais difícil controlar o acesso a funções confidenciais. Bem, se forem escritas regras que ajudem a controlar o acesso em um nível mais ou menos seguro. Mas muitas vezes acontece que os colegas se mudam para outras unidades, fazem decretos :) ou saem, e o acesso permanece.

Naturalmente, isso traz ameaças diferentes: o vazamento da base de clientes, bem, até a sabotagem, etc.
A idade dos projetos com os quais trabalho já é de 10 anos. O banco de dados possui centenas de milhares de usuários, incluindo centenas com direitos privilegiados.

Este artigo mostra um exemplo de como você pode simplificar a auditoria de usuários para vários objetos do site sob o controle do Bitrix CMS (BUS).

O problema é que o painel de administração do Bitrix não oferece uma oportunidade de obter uma imagem completa dos acessos; Clicar em vários links e aguardar o carregamento das páginas de administrador também é desagradável.

O Power BI será usado como a principal ferramenta para isso (um pouco fora do seu objetivo principal :)

Supõe-se que o leitor já esteja familiarizado com o Power BI em um nível básico, saiba o básico do SQL e também saiba como usar o painel de administração do Bitrix. Os recursos de acessibilidade padrão da Bitrix serão considerados.

Desvantagens do painel de administração do Bitrix

É impossível realizar uma auditoria no painel de administração padrão por um tempo aceitável devido à falta de uma imagem coerente com acessos - dados resumidos de todos os módulos / seções / blocos de informações, etc., aos quais o acesso é concedido.

Desempenho do administrador:

1. Na seção "Grupos de usuários" do painel de administração do Bitrix, há um recurso que gera uma consulta SQL para selecionar todos os grupos com uma contagem do número de usuários. Tudo fica bem quando a base é pequena. Porém, com um banco de dados de centenas de milhares de usuários, com centenas de grupos de usuários em um servidor dedicado com 128 GB de RAM, basta abrir esta seção em 8 segundos.
2. Há também uma solicitação no cartão do grupo, que por algum motivo seleciona todos os grupos de usuários, em vez de receber dados apenas do cartão selecionado. Perda em espera 3 seg.

Soluções

Geralmente, existem várias soluções para o problema.

1. Escreva as regras para fornecer acesso aos sites e siga-as claramente.
2. Realize periodicamente auditorias de acesso.
3. Esperando o melhor e não desperdiçando recursos limitados da empresa.

Este artigo considerará apenas o segundo método.

As tarefas

1. Escolha ferramentas que permitirão obter rapidamente dados sobre os níveis de acesso de cada usuário com direitos estendidos.
2. Configure as ferramentas para que elas mostrem claramente a imagem com acessos como um todo, com os detalhes e interatividade necessários.
3. Realize auditorias de acesso.

Acessar armazenamento no Bitrix

O Bitrix permite que você configure direitos de maneira bastante flexível através de grupos de usuários.
As configurações de acesso são armazenadas principalmente nas tabelas MySQL. Algumas configurações são armazenadas em arquivos. Por exemplo, os acessos a arquivos e pastas são armazenados em arquivos .access.php.

A análise do acesso do usuário e do grupo de usuários a:

• blocos de informação
• formulários da web com nível de acesso
• status do formulário da web com nível de acesso
• seções do site
• Módulos Bitrix com níveis de acesso

As ferramentas

1. O Power BI Desktop, que permite visualizar bem os dados, obtém dados de várias fontes (quase) imediatamente. Na verdade, o Power BI pode ser substituído pelo habitual Excel 2016 e superior - o PowerQuery já está incluído em sua entrega, através do qual você pode selecionar todos os dados para análise. No entanto, o Power BI permite exibir dados interativamente com base em seus relacionamentos, e isso permite que você encontre rapidamente dependências ocultas.
2. O MySQL Connector é necessário para poder criar uma consulta através do Power BI para o servidor da Web MySQL.
3. Kitty ou Putty para encapsular no MySql se o acesso ao banco de dados estiver aberto apenas através do SSH.

O seguinte esquema de acesso é obtido: Power BI → MySQL Connector → Kitty → MySQL.

Power BI

Power BI Desktop - permite visualizar bem os dados, obter dados de várias fontes (quase) imediatamente. Na verdade, o Power BI pode ser substituído pelo habitual Excel 2016 e superior - o PowerQuery já está incluído em sua entrega, através do qual você pode selecionar todos os dados para análise. No entanto, o Power BI permite exibir dados interativamente com base em seus relacionamentos, e isso permite que você encontre rapidamente dependências ocultas, que é o que precisamos para revisões de acesso.

Você pode baixá-lo na página oficial .

MySQL Connector

Vá para a página . Baixe e instale. Às vezes, você terá que reiniciar o seu PC após a instalação.

Vaquinha / massa de vidraceiro

Para executar consultas SQL no banco de dados Bitrix, você precisará configurar o túnel.

1. Digite o IP e a porta do servidor
   
   
2. Martelamos nome de usuário e senha no SSH
   
   
3. Fazemos o encaminhamento de portas:
   
   
4. Salvamos as configurações feitas para uso futuro no perfil:
   
   
5. Começamos.

Você também pode fazer o download do Putty e executá-lo com o comando:

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

Naturalmente, Kitty / Putty deve estar em execução antes de atualizar os dados no Power BI.

Usuários e grupos de usuários

Como em muitos CMS, o Bitrix implementa um mecanismo para delimitar direitos de acesso por meio de grupos de usuários.

Descarregue entidades do banco de dados no modelo de dados do Power BI:

• Grupos
• Utilizadores

... bem como relacionamentos entre grupos e usuários.

Grupos

Nós nos restringimos apenas a grupos ativos.

A lista de grupos é armazenada na tabela b_group.

1. Crie uma conexão:
   
   
2. Digite:
   
   
   1. no campo Servidor: localhost: 3306
   2. no campo Banco de dados: bitrix_db (nome do banco de dados com o qual o Bitrix trabalha)
   3. Consulta SQL:
      
      

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

      
      
      
   
   
3. Digite o login e a senha no banco de dados e envie uma solicitação:
   
   
   
   
   
   
   
4. Dê imediatamente um nome amigável à solicitação:
   
   
5. Listamos os grupos em uma planilha separada em forma de tabela:
   
   
   

Esse método de extrair e apresentar dados será semelhante para outras consultas relacionadas ao banco de dados Bitrix.

Utilizadores

Agora descarregue todos os usuários que possuem direitos avançados. Mas você não deve descarregar usuários incluídos apenas em grupos que não lhes concedem direitos adicionais, por exemplo, “Todos os usuários, inclusive não registrados” (é importante notar que a conexão desse grupo com os usuários é armazenada para todos os usuários registrados antes da versão 12. versões, o grupo é considerado sistêmico e não armazena dados de comunicação com os usuários do banco de dados).

Nós nos restringimos apenas a usuários ativados.

Para fazer isso, você precisa:

1. Selecione todos os IDs de grupos que concedem direitos estendidos. Isso é necessário para economizar tráfego, porque o número de entradas no b_user_group pode atingir milhões, dependendo da complexidade do projeto.
2. Crie uma solicitação dinâmica para descarregar links Usuário - Grupo
3. Descarregue os usuários que possuem um link da cláusula 2.

Vamos começar:

1. Ligue para o editor de consultas: Página inicial → Editar consultas
2. Vamos criar um link para a solicitação inicial de "Grupo":
   
   
3. Renomeie a nova solicitação para "ID do grupo" e selecione apenas os grupos que são interessantes do ponto de vista da segurança.
   
   
4. Agora temos uma linha contendo os IDs de grupo separados por vírgulas:
   
   • Adicione uma coluna customizada: AddColumn → General → Custom Column
     
     
   • Exclua todas as colunas, exceto ID e agrupamento:
     
     
   • Agrupe pela coluna "Agrupamento":
     
     
     
     
   • Adicione outra coluna da seguinte maneira:
     
     
   • Vamos expandir a lista para que os valores sejam separados por vírgulas:
     
     
   • E caia na célula resultante:
     
     
   • O Power BI converte a consulta em uma variável que pode ser usada em consultas SQL dinâmicas:
     
     
5. Vamos criar a solicitação "Grupo de usuários" que contém o relacionamento do usuário com o grupo, semelhante à maneira como isso é feito na seção "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 precisará ser substituído por códigos de grupo, separados por vírgulas.
   
6. Chamaremos a fonte da solicitação de edição e a substituiremos pelo seguinte:
   
   

    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. Depois disso, você pode receber o seguinte aviso:
   
   

    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 se livrar dele, você precisa alterar o nível de privacidade:
   
   
   
   
   
   Depois disso, atualize as consultas.
8. Criamos a variável “ID do usuário” da mesma maneira que foi feita para a “ID do grupo” (ou seja, criamos um link a partir da solicitação de usuários, etc.) Utilizando-o, geraremos uma consulta SQL que nos permitirá selecionar apenas os usuários que precisamos para análise. Primeiro remova user_id duplicado:
   
   
9. Criamos uma solicitação para uma seleção de usuários, semelhante à maneira como é feita para "Grupo de usuários".
   
   

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

   
   XXX precisará substituir os IDs de usuário.
   

Configurando relacionamentos entre solicitações

Para que o Power BI filtre interativamente os dados em diferentes modos de exibição, você precisa definir relacionamentos entre as consultas. No nosso caso, precisamos conectar os campos:

• “Grupo de usuários” [id_grupo] → “Grupos” [id]
• “Grupo de usuários” [id_do_usuário] → “Usuários” [id]

Da mesma forma, vincularemos outras consultas.

Relatório de usuário e grupo de usuários

Na guia Relatórios, exibimos uma lista de usuários e grupos usando a Tabela como um elemento de visualização.

Na solicitação "Usuários", selecione os campos: sobrenome, nome, sobrenome, e-mail.
Na solicitação "Grupo de usuários", selecione o campo group_id.
Porque Como atribuímos conexões entre solicitações, o Power BI poderá usar corretamente a função agregadora Count para contar o número de grupos aos quais cada usuário individual pertence.



Em seguida, adicione outra tabela e selecione o campo de nome na solicitação de grupo e o campo user_id na solicitação de grupo de usuários - defina a agregação “Contagem (distinta)” para ver o número de usuários no grupo.

Porque As solicitações de "Grupo" e "Usuário" são conectadas por meio de uma solicitação associativa "Grupo de usuários" e, quando você clica em um usuário na tabela com uma lista de grupos, apenas os grupos nos quais o usuário selecionado está incluído serão exibidos. E vice-versa.



Dessa forma, você pode clicar em cada usuário e ver de quais grupos ele é membro, ou clicar nos grupos e ver quais usuários fazem parte de um grupo. Bem, então tome decisões sobre como alterar o acesso do usuário.

A seguir, descreve como colocar as tabelas restantes no relatório geral do Power BI, porque isso é feito de maneira semelhante.

.access.php

No Bitrix, você pode definir o acesso a pastas e arquivos especificando números de grupo e o nível de acesso necessário nos arquivos .access.php.

Nossa tarefa é reduzir os dados de todos os arquivos .access.php espalhados pelo servidor do projeto em uma exibição tabular.

Para fazer isso:

1. Pesquisamos e arquivamos todos os arquivos .access.php do servidor, salvando os caminhos para esses arquivos.
   Eu usei o terminalka para pesquisar, copiar e arquivar arquivos encontrados. Exemplo 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 {} \; 

   
   Aqui:
   
   
   • BITRIX_PROJECT_DIR - pasta com o projeto no Bitrix.
   • OUTPUT_DIR - o caminho para a pasta na qual o arquivo .access.php.files.txt com uma lista de .access.php encontrado, bem como o arquivo .access.php.files.tar contendo cópias de todos os .access.php encontrados, serão colocados.
   
   Naturalmente, se houver muitos projetos (o multisite é usado), selecionamos uma pasta que contém todos os projetos.
2. Baixe e descompacte o arquivo .access.php em algum lugar próximo ao projeto do Power BI.
   Eu escrevi um arquivo em lotes que faz isso automaticamente: o download é implementado através do wget; através do 7zip - descompactando.
   
   Arquivo em lote de exemplo:
   
   
   
   Arquivo contendo configurações para arquivo em lote:
   
   
   

Agora crie uma consulta que resumirá o conteúdo de todos os .access.php em um formulário de tabela.

1. Por conveniência, crie um parâmetro que contenha o caminho para a pasta da qual extrairemos o conteúdo de todos os .access.php
   
   
2. Selecionaremos uma solicitação do tipo "Pasta" e escolheremos nosso parâmetro como o caminho:
   
   
3. Expanda o campo Conteúdo:
   
   
   
   XXXXXXX é um separador de colunas; você precisa de uma coluna após importar os dados de todos os arquivos.
4. Depois disso, o Power BI excluirá a coluna que precisamos, contendo o caminho para .access.php. Portanto, precisamos editar a etapa "Remover outras colunas1", selecionando "Caminho da pasta":
   
   
5. Deixe as colunas: Caminho da Pasta e Coluna1.
6. Para remover o caminho absoluto para o arquivo local do caminho da pasta, use a substituição:
   
   
7. Os arquivos .access.php contêm configurações de acesso no formato:
   
   

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

   
   Nossa tarefa é espalhar as colunas: Caminho, ID do grupo, Nível de acesso. Isso é feito usando filtros, separação de colunas (Dividir coluna) e colunas personalizadas (coluna Personalizada).
8. O resultado deve ser a seguinte tabela:
   
   
   
   Como você pode ver no campo ID do grupo, há "*" (acesso para todos). Para poder especificar uma conexão com outras solicitações, precisamos tornar esse campo inteiro, sem perder informações sobre "*" (o que significa para todos os grupos). Vamos fazer duas solicitações, como um "link" para a solicitação DotAccessPhp original:
   
   
   • O primeiro DotAccessPhpForRels conterá apenas IDs de grupo inteiro (usamos o filtro removendo * na coluna ID do grupo) - vamos conectá-lo aos demais pedidos:
     
   • O segundo - DotAccessPhpForAll - somente * (use o filtro).

Esquema de conexão:



Para exibir apenas dados relacionados ao selecionar um arquivo de DotAccessForRels em outras visualizações, altere o parâmetro "Direção do filtro cruzado" para Ambos:



Para outros pedidos que serão adicionados abaixo, isso também precisa ser feito.

Blocos de informação

Você precisa descarregar a lista de blocos de informações e a tabela de links de blocos de informações com grupos.

Apenas enviaremos informações sobre os blocos de informações ativas.

1. Criamos a solicitação "Infoblocks". 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. Atualizamos o esquema de comunicação, não esquecendo de alterar o parâmetro "Direção do filtro cruzado" para Ambos:
   
   

Formulários

No caso de formulários, os direitos dos grupos de usuários são concedidos aos próprios formulários e aos status nos quais reside o resultado do preenchimento do formulário.

1. Crie uma solicitação para "Formulários":
   
   

    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. Crie uma solicitação "Grupo de formulários":
   
   

    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. Criamos a solicitação "Status dos formulários".
   
   

    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. Crie uma solicitação "Status do grupo de formulários"
   
   

    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. Atualizando o esquema de conexão:
   
   

Módulos

1. Criamos uma solicitação "Módulo de grupo".
   
   

    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. Atualizando comunicações:
   
   

Placar

Nós personalizamos os estilos de tabelas, usamos o espaço utilizável ao máximo.

O resultado deve ser algo semelhante ao seguinte:



Um pequeno placar modificado (número de elementos nas tabelas):



A propósito, é conveniente primeiro configurar a aparência de uma tabela e, em seguida, simplesmente aplicar sua visualização a outras tabelas usando a Página inicial → Pincel. Essa função atua da mesma maneira que no Word e Excel (Formatar por amostra).

Links do administrador

Para poder acessar rapidamente o site e fazer configurações no painel de administração, você pode adicionar uma coluna personalizada no idioma DAX e torná-lo um tipo de "URL da Web". Para fazer isso, selecione a coluna criada e atribua o tipo apropriado (Modelagem → Propriedades → Categoria de Dados → URL da Web).

Exemplo para uma solicitação de grupo:



Adicione uma coluna à visualização:


Agora você pode simplesmente clicar na célula da tabela e acessar a placa de grupo no painel de administração do Bitrix.

Relatório de arquivos

Por conveniência, você pode fazer um relatório separado, colocando tabelas sobre o acesso a arquivos e seções do recurso da Internet:



Este relatório também adicionou links para editar todo o .access.php diretamente através do painel de administração do Bitrix.

Sumário

Bitrix é um campeão entre os monstros cms com prós e contras óbvios, bonitos por fora e terríveis por dentro. Não possui ferramentas de administração de acesso convenientes. Mas esse problema foi resolvido com a ajuda de ferramentas gratuitas, sem atrair tempo valioso para os programadores nesse processo.

As vantagens dessa abordagem também incluem a capacidade de complementar rapidamente o modelo no Power BI com informações adicionais do Bitrix, por exemplo, alguém quer saber quando .access.php e outros foram criados ou alterados.

Agora, depois de criar o modelo de direitos de acesso e visualizá-lo no Power BI, basta:

1. clique constantemente em usuários, grupos, formulários, arquivos e em tempo real veja todas as conexões relacionadas ao acesso;
2. vá rapidamente para as páginas de administração necessárias para fazer edições;
3. Atualize o modelo de dados com os dados mais recentes do Bitrix diretamente no Power BI.

Como resultado, uma auditoria foi realizada e um ajuste foi feito no acesso do usuário.

PS No mercado, existe um módulo gratuito "Access Control Center", mas é muito limitado, e o último comentário a ele é superior a 5 anos. Talvez alguém goste da ideia de criar um painel assim no Bitrix e o implemente como um módulo ...

PS2. Se alguém estiver interessado no tópico do uso do Power BI para resolver o problema de encontrar dependências ocultas em vários sistemas de contabilidade, escreva nos comentários. Escreverei mais alguns artigos sobre esse assunto.

PS3 Agradeço aos meus colegas por me ajudarem a preparar este artigo: Alexander Voronkov, Evgeny Shapochkin, Alexei Titov.