Artigo SQL Magazine 38 - Auditoria de Sistemas

ustify>

Clique aqui para ler todos os artigos desta edição

Auditoria de sistemas

Deixe que seu banco de dados faça o trabalho por você

Cristóferson Guimarães Magalhães Bueno e Odilon Corrêa da Silva

O objetivo deste artigo é demonstrar como criar um sistema de auditoria que se ajuste facilmente a qualquer tipo de aplicação que utilize banco de dados relacional sem a necessidade de grandes alterações na aplicação. No sistema proposto, 99% do trabalho de auditoria serão realizados pelo SGBD utilizado.

Conceito de auditoria

Auditoria consiste em um exame cuidadoso, sistemático e independente, cujo objetivo é averiguar se as atividades desenvolvidas em determinada empresa ou setor estão de acordo com as disposições planejadas e/ou estabelecidas previamente, se estas foram implementadas com eficácia e se estão adequadas (em conformidade) à consecução dos objetivos (ver Referência, no final do artigo).

Como irá funcionar o sistema de auditoria?

O conceito aplicado neste artigo é simples, mas bastante eficaz. A auditoria deve ser capaz de registrar quaisquer alterações feitas nos dados de um sistema, o dado original antes da manipulação, quem o manipulou e finalmente quando este dado foi manipulado.

Seguindo esta linha de raciocínio, nosso sistema de auditoria terá que receber algumas informações do usuário que acessa a base. O envio dessas informações é a única parte da auditoria que deve ser implementada no sistema que se deseja auditar.

No caso de nossa aplicação exemplo, será passado como parâmetro o nome do usuário e o IP. Esses parâmetros serão armazenados nas tabelas de auditoria à medida que o usuário efetua as operações no banco.

Os tópicos a seguir descrevem os recursos dos SGBDs que serão necessários para a implementação da auditoria e a explicação de como os parâmetros serão armazenados para serem utilizados posteriormente.

Todos os exemplos deste artigo serão implementados utilizando o MySQL 5 e o mecanismo de armazenamento InnoDB, que provê ao MySQL um sistema de armazenamento seguro com transações (compatível com ACID) com commit, rollback, e recuperação em caso de falhas. Entretanto, todas as soluções apresentadas aqui podem ser facilmente implementadas em outros bancos de dados relacionais, sem a necessidade de grandes mudanças, com exceção do PostgreSQL, devido à forma que as suas triggers são implementadas, mas nada que o impeça de ser utilizado com um pouco mais de esforço.

Na Figura 1 representa um diagrama de caso de uso, que corresponde às interações de um usuário com um sistema que está sendo auditado. A Figura 2, por sua vez, representa as atividades correspondentes a este caso de uso, ou seja, quando um usuário logar no Sistema suas informações pessoais serão armazenadas na sessão corrente do SGBD. Estas informações serão recuperadas sempre que uma ação no banco disparar um gatilho da auditoria. Posteriormente elas e os dados manipulados serão registrados nas tabelas de auditoria.

Figura 1. Diagrama de Caso de Uso do Sistema de Auditoria.

Figura 2. Diagrama de Atividades do Sistema de Auditoria.

Os diagramas descrevem as interações do sistema de auditoria com o SGBD.

Variáveis de sessão, triggers, stored procedures

Estes são os principais recursos necessários para criação do sistema de auditoria proposto neste artigo. Com eles, e um pouco de criatividade, seremos capazes de criar uma solução para auditoria que resida praticamente toda no banco de dados.

Variáveis de Sessão

São variáveis temporárias, criadas para armazenar dados que possam ser posteriormente recuperados e utilizados em rotinas no banco. Essas rotinas podem ser simples consultas ou stored procedures.

Sempre que a aplicação efetuar uma conexão com o banco de dados será necessário criar algumas variáveis de sessão e armazenar nelas os parâmetros da auditoria. Neste artigo, os parâmetros armazenados nas variáveis de sessão serão o nome e o IP.

Trigger

É um recurso de programação presente na maioria dos SGBDs. Ele é utilizado para associar um procedimento a um evento do banco de dados (inclusão, exclusão, atualização de registro, por exemplo) de modo que o procedimento seja executado automaticamente sempre que o evento associado ocorrer.

As triggers serão utilizadas na nossa aplicação para disparar as Stored Procedures que farão os registros dos eventos executados no banco, assim como povoar o dicionário de dados necessário para o sistema de auditoria.

Stored Procedures

São programas ou procedimentos armazenados no banco de dados. Este recurso permite encapsular tarefas repetitivas, aceita a passagem de parâmetros de entrada e retorna um valor de status (para indicar sucesso ou falha na execução). Stored procedures permitem reduzir tráfego de rede, melhorar a performance da aplicação e criar mecanismos de segurança.

Estrutura da aplicação modelo

A estrutura de tabelas mostrado na Figura 3 servirá como modelo para exemplificar a criação e uso do sistema de auditoria.

Figura 3. Diagrama ER da aplicação modelo.

A Listagem 1 corresponde ao script SQL com a estrutura de tabelas que será utilizada para exemplificar o sistema de auditoria ao longo do artigo.

Listagem 1. SQL para a geração das tabelas da aplicação exemplo.

SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `FolhaPagamento`

  CHARACTER SET latin1;

DROP TABLE IF EXISTS `FolhaPagamento`.`departamento`;

CREATE TABLE `FolhaPagamento`.`departamento` (

    `CodDepartamento` INT(10) unsigned NOT NULL AUTO_INCREMENT,

    `NmDepartamento` VARCHAR(50) NOT NULL,

    PRIMARY KEY (`CodDepartamento`))

  ENGINE = InnoDB

  ROW_FORMAT = Compact

  CHARACTER SET latin1 COLLATE latin1_swedish_ci;

DROP TABLE IF EXISTS `FolhaPagamento`.`funcionario`;

CREATE TABLE `FolhaPagamento`.`funcionario` (

    `CodFuncionario` INT(10) unsigned NOT NULL AUTO_INCREMENT,

    `NmFuncionario` VARCHAR(50) NOT NULL,

    `CodDepartamento` INT(10) unsigned NOT NULL DEFAULT '0',

    PRIMARY KEY (`CodFuncionario`),

    INDEX `CodDepartamento` (`CodDepartamento`),

    CONSTRAINT `funcionario_ibfk_1` FOREIGN KEY `funcionario_ibfk_1` (`CodDepartamento`)

    REFERENCES `FolhaPagamento`.`departamento` (`CodDepartamento`)

      ON DELETE RESTRICT

      ON UPDATE RESTRICT)

  ENGINE = InnoDB

  ROW_FORMAT = Compact

  CHARACTER SET latin1 COLLATE latin1_swedish_ci;

DROP TABLE IF EXISTS `FolhaPagamento`.`pagamento`;

CREATE TABLE `FolhaPagamento`.`pagamento` (

    `CodPagamento` INT(10) unsigned NOT NULL AUTO_INCREMENT,

    `CodFuncionario` INT(10) unsigned NOT NULL DEFAULT '0',

    `DataPagamento` DATE NOT NULL DEFAULT '0000-00-00',

    `Historico` VARCHAR(100) NOT NULL,

    `Valor` DECIMAL(10, 0) NOT NULL DEFAULT '0',

    PRIMARY KEY (`CodPagamento`),

    INDEX `CodFuncionario` (`CodFuncionario`),

    CONSTRAINT `pagamento_ibfk_1` FOREIGN KEY `pagamento_ibfk_1` (`CodFuncionario`)

      REFERENCES `FolhaPagamento`.`funcionario` (`CodFuncionario`)

      ON DELETE RESTRICT

      ON UPDATE RESTRICT)

  ENGINE = InnoDB

  ROW_FORMAT = Compact

  CHARACTER SET latin1 COLLATE latin1_swedish_ci;

DROP TABLE IF EXISTS `FolhaPagamento`.`usuario`;

CREATE TABLE `FolhaPagamento`.`usuario` (

    `CodUsuario` INT(10) unsigned NOT NULL AUTO_INCREMENT,

    `NmUsuario` VARCHAR(50) NOT NULL,

    `Login` VARCHAR(20) NOT NULL,

    `Senha` VARCHAR(20) NOT NULL,

    PRIMARY KEY (`CodUsuario`))

  ENGINE = InnoDB

  ROW_FORMAT = Compact

  CHARACTER SET latin1 COLLATE latin1_swedish_ci;

SET FOREIGN_KEY_CHECKS = 1;

Dicionário de dados

O dicionário de dados é uma estrutura de tabelas do sistema de auditoria, geradas a partir de informações obtidas no SGBD.

Este dicionário de dados sempre será composto por duas tabelas. A ...