Uma Solução de Auditoria de Modificações no PostgreSQL

A segurança em uma base de dados é uma das coisas mais importantes que se deve ter em mente nos dias de hoje. Saber quem modificou o quê, quando, onde, são informações cada vez mais necessárias para gestores de negócios e DBAs.

Para contemplar essa necessidade, são utilizadas técnicas de auditoria nas bases de dados que registram todas as informações necessárias para esse objetivo.

Os scripts e códigos aqui apresentados necessitam que a versão instalada do PostgreSQL seja a 8.2 ou superior.

Apresentação do Estudo de Caso

A base de dados que utilizaremos faz parte de um sistema de gerenciamento de acesso, onde utilizaremos apenas algumas tabelas que controlam qual página cada usuário pode acessar em um determinado sistema através dos perfis vinculados.

A Figura 1 mostra o diagrama da base de dados com cinco tabelas: tb_usuario, onde ficarão os usuários do sistema; tb_perfil, que armazenará os perfis (gerente, funcionário, diretor, etc.); tb_pagina, onde serão registradas as páginas do sistema; tb_perfil_usuario, que associa os perfis aos usuários e tb_perfil_pagina, que associa as páginas que cada perfil pode acessar. O script SQL para criação da base está disponível para download no site da revista com o nome “script_criacao_base_dados.sql”. Durante a criação da base, foram realizadas algumas inserções para popular a mesma.

Solução para Auditar as Modificações nos Dados

Para organizar a base de dados, será criado um novo esquema para conter as tabelas, funções e outros objetos necessários para o funcionamento da auditoria. O comando abaixo criará o esquema auditoria:

 CREATE SCHEMA auditoria; 

A criação do esquema é uma boa prática para a organização lógica da base de dados. Se as tabelas de auditoria forem ficar no mesmo esquema das tabelas de produção, é opcional a criação do mesmo.

Após esse passo, é necessário incluir na variável search_path, do arquivo postgresql.conf, o nome do novo esquema. O postgresql.conf é um arquivo de configuração do PostgreSQL e normalmente se encontra no diretório “data” do cluster de banco de dados utilizado, como pode ser visto na Figura 2.

A variável search_path encontra-se na seção CLIENT CONNECTION DEFAULTS do postgresql.conf e pode estar com a configuração default mostrada na Listagem 1, caso não existam outros esquemas criados nas bases de dados. Se existirem outros esquemas, esta variável não estará comentada (ou seja, sem o símbolo “#”) e com a lista dos esquemas separados por vírgula.

Caso a variável esteja comentada, devemos retirar o símbolo de comentário (“#”) e incluir o nome do novo esquema. Depois das alterações, a variável search_path ficará como descrita abaixo. Feito isso, basta reiniciar o PostgreSQL.

search_path = '"$user",public,auditoria'

Assim, o esquema “auditoria” poderá ser visto no “psql”, cliente default do PostgreSQL. Com essa alteração também poderemos ver a lista de tabelas e objetos criados no esquema de auditoria

 
#---------------------------------------------------------------------------

# CLIENT CONNECTION DEFAULTS

#---------------------------------------------------------------------------

 

# - Statement Behavior -

 

#search_path = '"$user",public'                                     # schema names
            

Nesta solução de auditoria, cada alteração de dados realizada pelos comandos de INSERT, UPDATE e DELETE deve ser registrada, gerando-se uma coleção de alterações para cada registro modificado. Para isso acontecer é preciso ter a mesma estrutura das tabelas em produção dentro do esquema “auditoria”.

Criação da Estrutura de Auditoria

Para a criação da estrutura de auditoria devemos realizar uma seqüência de passos, onde o primeiro deles é criar tabelas com estrutura semelhante às de produção. Para isso é necessário realizar pequenas modificações nestas tabelas, como a retirada das chaves estrangeiras, primárias, índices, restrições (check constraint, NOT NULL, etc.), valores default, comentários, triggers, criações de seqüências, atribuições de incrementos das seqüências, etc.

Devemos deixar apenas os comandos CREATE TABLE das estruturas das tabelas, tomando cuidado para que a ordem das colunas nas tabelas de auditoria sejam a mesma das tabelas de produção. Ou seja, se na tabela tb_usuario a ordem das colunas é id_usuario, nm_usuario e nm_login, devemos ter a mesma ordem na tabela de auditoria que irá registrar os dados modificados desta tabela.

A explicação para a modificação da estrutura das tabelas é a seguinte: como a auditoria tem a utilidade de rastrear as modificações realizadas sobre os dados, não tem sentido termos as mesmas restrições e regras de uma tabela de produção, como verificação de valores (check constraint), integridade referencial, etc. Isso provoca uma carga a mais de verificação nas tabelas criadas para a auditoria durante a inserção/modificação/exclusão dos dados. Em resumo, as tabelas de auditoria devem ter uma estrutura simples para que as inserções sejam realizadas com eficiência e não prejudiquem o desempenho do banco.

Para realizar essa limpeza na estrutura das tabelas de auditoria será necessário gerar um script da estrutura da base de dados através do utilitário pg_dump do PostgreSQL utilizando o comando abaixo no prompt do DOS. O script da estrutura, após a execução do “pg_dump”, está disponível para download no site da revista com o nome “script_estrutura_antes.sql”

 
            pg_dump -U postgres sql_magazine_auditoria -sOx -n public > C:\Temp\script_estrutura_antes.sql
            

Observe que o resultado desse comando é um script completo com toda a estrutura da base de dados. A partir dele é que iremos modificar a estrutura das tabelas para criar as tabelas de auditoria. Depois de realizar a limpeza do script é interessante incluir nos nomes das tabelas algum prefixo para diferenciar as tabelas da auditoria das tabelas em uso pelo sistema. Em nosso exemplo, usamos o prefixo “tb_audit_”. Também devemos incluir o nome do esquema nos nomes das tabelas, por exemplo, auditoria.tb_audit_tb_pagina.

Para uma auditoria ser eficaz, não basta apenas sabermos o histórico de modificações dos dados. Devemos saber também algumas informações a mais, como quem realizou a mudança, o tipo de alteração realizada, de onde partiu o comando, data, etc. Para isso, incluímos algumas colunas nas tabelas de auditoria como pode ser visto na Tabela 1. O script com todas as alterações realizadas está disponível para download no site da revista com o nome “script_estrutura_depois.sql”. Execute-o na base “sql_magazine_auditoria”.