[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:

1. Identificar como estão organizados os registros de ações de usuário da aplicação;
2. Propor abordagens que visam uma melhor organização destes registros;
3. Fornecer subsídios para a continuidade do processo de manutenção dos registros de auditoria;

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

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. 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, como criar/abrir/modificar/excluir documentos/processos/blocos dentro da aplicação;
• 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, 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:

1. [L8] Selecionar o identificador de transação de usuário (id_infra_auditoria) mais recente (MAX);
2. [L11~L13] Inserir todos os registros de transação até o identificador selecionado na tabela de expurgo;
3. [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, 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.