[SEI][SPIKE] Análise da estrutura de Auditoria de Ações dos Usuários na Aplicação
CENÁRIO ATUAL
O Sistema Eletrônico de Informação é parte do projeto Governo Sem Papel, mantido pelo Estado de Rondônia, sendo utilizado para a geração e tramitação de processos entre secretarias. Economizando milhões em papel e impressão e acelerando o fluxo dos trâmites.
PROBLEMA
Com a ampla adoção pelas secretarias uma grande quantidade diária de dados é gerada. A versão utilizada possuí problemas conhecidos de performance e otimização e não pode ser atualizada devido a entraves burocráticos. O crescimento da utilização de recursos, principalmente de disco, gera dificuldades para a manutenção de logs, rotinas de backup, lentidão na aplicação e até mesmo alto custo para armazenamento.
OBJETIVO
Considerando a dificuldade da manutenção e sustentabilidade dos logs de auditoria gerados pela aplicação, este estudo tem como objetivo principal demonstrar como tornar sustentável o processo de auditoria no SEI, atendendo a estória de usuário registrada sob link https://app.pipefy.com/open-cards/458858849 .
RESULTADOS ESPERADOS
Para que seja possível atender ao objetivo principal, com este estudo espera-se:
- Identificar como estão organizados os registros de ações de usuário da aplicação;
- Propor abordagens que visam uma melhor organização destes registros;
- Fornecer subsídios para a continuidade do processo de manutenção dos registros de auditoria;
GLOSSÁRIO
A seguir, são descritos termos relacionados a este estudo e como eles podem ser definidos no escopo do mesmo, sendo desconsideradas definições não relacionadas.
- Log - registro de eventos de uma aplicação;
- Evento - considera-se evento toda a ação do usuário que demanda de um recurso do sistema;
- Recurso - uma funcionalidade da aplicação, como criar, editar, excluir, visualizar, copiar, mover, assinar, anexar, ... , algum dos objetos do sistema;
- Objeto - um item no qual o usuário pode executar uma ação a partir dos recursos fornecidos pela aplicação, como documentos, processos, blocos, etc;
Auditoria-- Transação - conjunto de instruções/operações que descrevem uma sequência que deve ser executada com sucesso em sua totalidade afim de completar uma tarefa lógica;
- Auditoria - processo de identificação de um conjunto de recursos utilizados por um usuário OU de um conjunto de usuários que demandaram recursos em determinado objeto;
- Expurgo - remover as informações mais antigas com o objetivo de obter melhor performance na aplicação;
PREMISSAS
P1) Com os entraves burocráticos, hoje não é possível realizar correção via software, sendo necessárias a utilização de rotinas de banco;
P2) Os dados de ações de usuário são inseridos na tabela [dbo].[infra_auditoria], no database da aplicação SEI;
P3) Devido ao volume gigantesco de ações diárias realizadas ao se levar em conta a operacionalização por todos os utilizadores, esta tabela excede os limites do sistema gerenciador de banco de dados, fazendo com que a aplicação caia caso não sejam realizadas as remoções periódicas dos registros;
P4) Por se tratarem de dados de auditoria, utilizados por órgãos fiscalizadores, estes dados não podem ser excluídos, sendo realizado o expurgo dos dados.
CONDUÇÃO DO ESTUDO
Durante a condução do estudo, alguns desafios foram identificados, sendo necessária a exploração e o entendimento de algumas estruturas auxiliares. Neste caso, considera-se como exploração apenas a análise das estruturas que atendem aos fins propostos pelo estudo. Os desafios identificados e conceitos de entendimento necessário, neste estudo, são considerados Pré-Exploratórios e os resultados da exploração são definidos como execução.
ANÁLISE PRÉ-EXPLORATÓRIA
A seguir são descritos os processos e informações obtidas necessárias para a efetivação deste estudo.
IDENTIFICAÇÃO DA ORGANIZAÇÃO DOS REGISTROS DE AUDITORIA
Como primeira etapa deste estudo, torna-se necessário o aprofundamento do conteúdo presente na tabela sei.dbo.infra_auditoria.infra_auditoria. A consulta a seguir permite descrever a organização da tabela em questão.
SELECT
ORDINAL_POSITION as num,
CONCAT(TABLE_CATALOG,'.',TABLE_SCHEMA,'.',TABLE_NAME) as 'schema',
COLUMN_NAME as nome,
CONCAT(DATA_TYPE,' ', NULLIF(CHARACTER_MAXIMUM_LENGTH,-1)) as tipo
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME like 'infra_auditoria';Como resultado desta consulta temos as seguintes informações:
|
num |
schema |
nome |
tipo |
|
1 |
sei.dbo.infra_auditoria |
id_infra_auditoria |
bigint |
|
2 |
sei.dbo.infra_auditoria |
recurso |
varchar 50 |
|
3 |
sei.dbo.infra_auditoria |
dth_acesso |
datetime |
|
4 |
sei.dbo.infra_auditoria |
ip |
varchar 39 |
|
5 |
sei.dbo.infra_auditoria |
id_usuario |
int |
|
6 |
sei.dbo.infra_auditoria |
sigla_usuario |
varchar 100 |
|
7 |
sei.dbo.infra_auditoria |
nome_usuario |
varchar 100 |
|
8 |
sei.dbo.infra_auditoria |
id_orgao_usuario |
int |
|
9 |
sei.dbo.infra_auditoria |
sigla_orgao_usuario |
varchar 30 |
|
10 |
sei.dbo.infra_auditoria |
id_usuario_emulador |
int |
|
11 |
sei.dbo.infra_auditoria |
sigla_usuario_emulador |
varchar 100 |
|
12 |
sei.dbo.infra_auditoria |
nome_usuario_emulador |
varchar 100 |
|
13 |
sei.dbo.infra_auditoria |
id_orgao_usuario_emulador |
int |
|
14 |
sei.dbo.infra_auditoria |
sigla_orgao_usuario_emulador |
varchar 30 |
|
15 |
sei.dbo.infra_auditoria |
id_unidade |
int |
|
16 |
sei.dbo.infra_auditoria |
sigla_unidade |
varchar 30 |
|
17 |
sei.dbo.infra_auditoria |
descricao_unidade |
varchar 250 |
|
18 |
sei.dbo.infra_auditoria |
id_orgao_unidade |
int |
|
19 |
sei.dbo.infra_auditoria |
sigla_orgao_unidade |
varchar 30 |
|
20 |
sei.dbo.infra_auditoria |
servidor |
varchar 250 |
|
21 |
sei.dbo.infra_auditoria |
user_agent |
varchar |
|
22 |
sei.dbo.infra_auditoria |
requisicao |
varchar |
|
23 |
sei.dbo.infra_auditoria |
operacao |
varchar |
Destacam-se em negrito as colunas mais utilizadas nos processos de auditoria, sendo descritas a seguir:
- id_infra_auditoria - identificador incremental utilizado como chave principal para localizar registros de ações de utilizadores no SEI;
- recurso - trata-se de um classificador que descreve qual ação foi realizada pelo usuário, conforme descrito no glossário;
- dth_acesso - data e hora da transação;
- requisicao - este campo armazena os detalhes da transação que descrevem a ação do usuário na aplicação. Associado ao campo recurso, é possível identificar em qual documento/processo/bloco foi realizada uma ação.
IDENTIFICAÇÃO DAS ROTINAS DE EXPURGO
Foi identificada a rotina que realiza a remoção periódica dos registros da tabela infra_auditoria,infra_auditoria, sendo os comandos demonstrados e explicados a seguir:
ALTER procedure [dbo].[expurgo_infra_auditoria] as
declare @maxid int
declare @minid int
begin;
set @maxid = (select max(id_infra_auditoria) from sei.dbo.infra_auditoria)
set @minid = (select min(id_infra_auditoria) from sei.dbo.infra_auditoria)
insert into [aud_infra].[dbo].[aud_expurgo]
select * from sei.dbo.infra_auditoria
where id_infra_auditoria < @maxid;
delete from sei.dbo.infra_auditoria
where id_infra_auditoria < @maxid;
END;Por se tratar de uma tabela com dados transacionais, as linhas 8 e 9, asseguram de que o expurgo será realizado no intervalo determinado, sem que ocorra a perda de informações durante o processo. Nas linhas 11 a 17, é possível analisar que a tarefa de expurgo consiste basicamente em três etapas:
- [L8] Selecionar o identificador de transação de usuário (id_infra_auditoria) mais recente (MAX);
- [L11~L13] Inserir todos os registros de transação até o identificador selecionado na tabela de expurgo;
- [L16~L17] Excluir os registros de transação até o identificador selecionado em 1.
É possível observar que esta rotina está sendo executada diariamente, sem histórico recente de qualquer tipo de alertas ou falhas. É interessante destacar que esta solução sana a premissa P3 e parcialmente a premissa P4, pois este volume de dados não mais impactará na aplicação, porém, a tabela [aud_infra].[dbo].[aud_expurgo] está setada para receber todos os dados de transações, sendo possível que a mesma sofra um crescimento considerável, tornando-a insustentável.
De posse dessas informações, o próximo desafio é identificar se existe uma rotina para que a tabela que receba os dados de expurgo não apresente os mesmos problemas da tabela sei.dbo.infra_auditoria,infra_auditoria, da aplicação.
IDENTIFICAÇÃO DAS ROTINAS DE CONSOLIDAÇÃO DOS DADOS EXPURGADOS
Após analise, não foram identificadas rotinas definidas para consolidação dos dados expurgados, sendo este o primeiro encaminhamento deste estudos.
ANÁLISE EXPLORATÓRIA
A seguir são descritos os processos e demonstrados os resultados da análise exploratória deste estudo, sendo destacadas as descobertas e apontamentos.
IDENTIFICAÇÃO DAS TABELAS QUE POSSUEM LOGS DE AUDITORIA
A seguir são apresentados os nomes das tabelas, menores e maiores id_infra_auditoria e dth_acesso , além da contagem do número de linhas e tamanho em Mb dos recursos de armazenamento utilizados.
|
TABLENAME |
MIN_ID |
MIN_DATE |
MAX_ID |
MAX_DATE |
COUNT |
Tamanho (Mb) |
|
audbkp_1 |
1 |
2017-02-01 16:58:12.000 |
72072674 |
2019-01-22 12:47:52.000 |
71470183 |
171648,97 |
|
audbkp_3 |
98637221 |
2019-05-09 12:37:38.000 |
100241685 |
2019-05-15 23:44:12.000 |
1595704 |
2000,41 |
|
audbkp_4 |
100241689 |
2019-05-15 23:46:13.000 |
103260808 |
2019-05-28 10:55:36.000 |
3003604 |
3762,24 |
|
aud24082019 |
103260809 |
2019-05-28 10:59:49.000 |
127682818 |
2019-08-24 00:59:34.000 |
24268639 |
31190,88 |
|
aud_expurgo_5 |
127682819 |
2019-08-25 12:19:54.000 |
170524666 |
2020-01-25 02:02:24.000 |
42560678 |
54952,05 |
|
aud_expurgo_6 |
170524667 |
2020-01-25 02:02:34.000 |
180281829 |
2020-03-03 01:17:15.000 |
9681663 |
12586,31 |
|
aud_expurgo_7 |
180281830 |
2020-03-03 01:16:13.000 |
200231366 |
2020-05-13 01:15:50.000 |
19815502 |
25612,82 |
|
aud_expurgo_8 |
200231367 |
2020-05-13 01:15:31.000 |
212622097 |
2020-06-29 01:15:24.000 |
12310593 |
15968,15 |
|
aud_expurgo_12 |
212622098 |
2020-06-29 01:15:06.000 |
224473434 |
2020-08-07 01:15:06.000 |
11779194 |
15414,01 |
|
aud_expurgo_9 |
224473435 |
2020-08-07 01:14:11.000 |
244825439 |
2020-10-12 21:08:43.000 |
34786771 |
45682,7 |
|
aud_expurgo_10 |
244825440 |
2020-10-12 21:08:53.000 |
246620480 |
2020-10-17 11:07:18.000 |
1784529 |
2337,15 |
|
aud_expurgo_11 |
246620481 |
2020-10-17 11:07:09.000 |
261836927 |
2020-12-03 09:45:08.000 |
15087784 |
19974,16 |
|
aud_expurgo_aux_03-12-2020_09-12-2020 |
261836928 |
2020-12-03 09:44:58.000 |
263708273 |
2020-12-09 00:00:59.000 |
1827692 |
2401,09 |
|
aud_expurgo |
263708274 |
2020-12-09 00:00:49.000 |
277586362 |
2021-01-29 00:00:46.000 |
13801364 |
18460,01 |
|
aud_expurgo_aux_29-01-2021_29-01-2021 |
277586363 |
2021-01-29 00:00:04.000 |
277586363 |
2021-01-29 00:00:04.000 |
1 |
0,23 |
|
aud_29-01-2021_x_26-04-2021 |
277586364 |
2021-01-29 00:00:08.000 |
306716512 |
2021-04-26 12:28:12.000 |
28962560 |
39247,29 |
|
aud_expurgo_26-04-2021_28-10-2021 |
306726536 |
2021-04-26 13:19:27.000 |
378764156 |
2021-10-28 22:00:17.000 |
71621948 |
100569,85 |
|
prod.aud_expurgo |
378764157 |
2021-10-28 21:59:59.000 |
379403627 |
2021-11-01 22:00:28.000 |
636165 |
930,29 |
