Auditoria MySQL

Meu primeiro post será dedicado a segurança em banco de dados. Desde já deixarei claro que trabalho administrando bancos de dados Oracle, mas esse primeiro post será destinado principalmente para bancos MySQL, portando possivelmente poderá haver algum erro. A idéia desse blog é abordar sempre que possível assuntos relacionados aos problemas rotineiros que você encontra em seu site na web, logicamente o conteúdo aqui descrito pode ser aplicado para qualquer fim, mas o exemplo abaixo será o mais simples possível com intúido de somente ilustrar o que pode ser feito para você agregar mais segurança para seu site e principalmente ao seu banco de dados.

Sabe-se que atualmente os problemas de segurança se centralizam principalmente nas ameaças internas, alguns estudos revelam que a porcentagem de problemas causados por esse tipo de ameaça corresponde a 70% do total de ameaças e sabendo também que muitas vezes a falta de tempo de desenvolvimento e a ausência de preocupações com segurança em detrimento da facilidade de implantação nos levam a uma frase bem oportuna, que é o slogan de um dos produtos da Oracle: "Trust but Verify".

Esse é o slogam da ferramenta Oracle Audit Vault (que me comprometo a futuramente abordar em um post sobre segurança em sistemas corporativos), "traduzindo" podemos alegar que a confiança (Trust) citada pode ser encarada como a falta de tempo ou de comprometimento com segurança presenciada não somente no dia a dia de "desenvolvedores de fim de semana", mas também de grandes empresas que não se dão conta que o pior inimigo é aquele que está dentro de casa. Como não vamos falar de "confiança" mas sim de segurança, a palavra "Verify" é simplesmente traduzida em uma única preocupação, auditoria.

Analisando o mercado, oportunidades e seu comportamento esse slogan é bem revelador, sabendo que por via de regra são poucos que se preocupam na hora de conceder acesso e privilégios para seus usuários internos uma das saídas pode ser a auditoria, ou seja, saber quem, quando e o que foi feito antes de, por exemplo, alguem executar um update sem a cláusula where e ter comprometido todos os registros de uma tabela (vamos assumir que a transação por algum motivo também foi efetivada).

O exemplo dessa implementação de auditoria foi desenvolvido para bancos MySQL mas pode ser utilizado (com algumas adaptações) a maioria dos bancos de dados. Os pré-requisitos para essa implementação são que seu banco de dados suporte a utilização de triggers e que você tenha privilégios para uma vez modelada essa estrutura você possa criar essas tabelas, constraints e as triggers propriamente ditas.

Cenário Proposto)

Iremos assumir como exemplo uma tabela retirada de um modelo de dados de uma locadora de automóveis. A tabela em questão terá o nome de VEICULO e armazenará todas as principais informações sobre cada automóvel que pode ser alugado. Nesse exemplo abrirei mão das formalidades de normalizações para a idéia de auditoria fique mais clara.

CREATE TABLE VEICULO (
CODIGO INTEGER NOT NULL,
PLACA VARCHAR(20) NOT NULL,
MARCA VARCHAR(30),
MODELO VARCHAR(30),
KM INTEGER,
QUANTIDADE_LOCACOES INTEGER,
ANO INTEGER,
PRIMARY KEY(CLICOD));

Passo 1) Identificar as chaves e demais colunas que sejam importantes para caracterizar cada registro na tabela que será auditada:

Analisando em uma tabela as colunas que melhor se candidatam para identificar unicamente cada um de seus registros sempre escolheremos sua chave primária, que no nosso exemplo é a coluna CODIGO. Caso sua chave primária seja artificial (ou uma "surrogate key") você pode optar por escolher mais alguma coluna nesse caso a coluna que mais se candidata a esse papel é a coluna PLACA.

Passo 2) Modelar a tabela que servirá como destino dos logs de auditoria, bem como seus devidos relacionamentos:

Uma vez selecionadas quais colunas serão as "chaves" da tabela para modelar a tabela que será utilizada como destino das informações auditadas, utilizaremos além dessas chaves e de uma data, também informações do usuário, que pode ser no caso do MySQL o usuário do banco de dados (você pode expandir seu esquema de auditoria para capturar qualquer outra informação que você considerar relevante para identificar quem ou a partir de onde foi feita a manipulação).

Além dessas informações é crucial para sua auditoria que sejam capturadas informações das tabelas que sofreram alterações, minha sugestão é que seja captura os valores anteriores e posteriores a manipulação ocorrida:

CREATE TABLE AUDIT_VEICULO_LOG (
CODIGO INTEGER NOT NULL AUTO_INCREMENT,
PLACA VARCHAR(20) NOT NULL,
DATA_ALTERACAO DATETIME,
USUARIO VARCHAR(30),
NOME_COLUNA VARCHAR(30),
VALOR_ANTERIOR VARCHAR(100),
VALOR_POSTERIOR VARCHAR(100));

Passo 3) Desenvolver as triggers:

Segue a codificação das triggers que são responsáveis pela auditoria de uma tabela. Dependendo de seu banco de dados você poderá desenvolver uma única trigger, mas para o MySQL acabei achando mais fácil montar uma trigger para cada tipo de DML. Para esse caso estou assumindo que existe uma tabela que armazena as informações dos usuários.

DELIMITER //

CREATE TRIGGER AUDIT_VEICULO_TBI BEFORE INSERT ON VEICULO
FOR EACH ROW
/*************************************************************/
/* Nome: AUDIT_VEICULO_TBI */
/* Autor: Caio Spadafora */
/* Objetivo: Gravar as informacoes de auditoria da tabela VEICULO */
/* Historico: 08/03/2009 - Caio Spadafora - Criacao. */
/*************************************************************/
BEGIN

/* Declarando variaveis */
SET @usuario=NULL;
SET @sysdate=NULL;

/* Recuperando o nome do usuario */
SELECT USUARIO
INTO @usuario
FROM USUARIO
WHERE usuario = (SELECT SUBSTR(USER(),1,INSTR(USER(),'@')-1));

/* Recuperando a data atual */
SELECT now()
INTO @sysdate;

/* Inserindo o historico de alteracao */
INSERT INTO AUDIT_VEICULO_LOG (CODIGO, PLACA, DATA_ALTERACAO, USUARIO, NOME_COLUNA, VALOR_ANTERIOR, VALOR_POSTERIOR)
VALUES (NEW.CODIGO,NEW.PLACA,@usuario,'CODIGO',NULL,NEW.CODIGO,@sysdate);

INSERT INTO AUDIT_VEICULO_LOG (CODIGO, PLACA, DATA_ALTERACAO, USUARIO, NOME_COLUNA, VALOR_ANTERIOR, VALOR_POSTERIOR)
VALUES (NEW.CODIGO,NEW.PLACA,@usuario,'PLACA',NULL,NEW.PLACA,@sysdate);


INSERT INTO AUDIT_VEICULO_LOG (CODIGO, PLACA, DATA_ALTERACAO, USUARIO, NOME_COLUNA, VALOR_ANTERIOR, VALOR_POSTERIOR)
VALUES (NEW.CODIGO,NEW.PLACA,@usuario,'PLACA',NULL,NEW.PLACA,@sysdate);


INSERT INTO AUDIT_VEICULO_LOG (CODIGO, PLACA, DATA_ALTERACAO, USUARIO, NOME_COLUNA, VALOR_ANTERIOR, VALOR_POSTERIOR)
VALUES (NEW.CODIGO,NEW.PLACA,@usuario,'MODELO',NULL,NEW.MODELO,@sysdate);


INSERT INTO AUDIT_VEICULO_LOG (CODIGO, PLACA, DATA_ALTERACAO, USUARIO, NOME_COLUNA, VALOR_ANTERIOR, VALOR_POSTERIOR)
VALUES (NEW.CODIGO,NEW.PLACA,@usuario,'KM',NULL,NEW.KM,@sysdate);


INSERT INTO AUDIT_VEICULO_LOG (CODIGO, PLACA, DATA_ALTERACAO, USUARIO, NOME_COLUNA, VALOR_ANTERIOR, VALOR_POSTERIOR)
VALUES (NEW.CODIGO,NEW.PLACA,@usuario,'QUANTIDADE_LOCACOES',NULL,NEW.QUANTIDADE_LOCACOES,@sysdate);


INSERT INTO AUDIT_VEICULO_LOG (CODIGO, PLACA, DATA_ALTERACAO, USUARIO, NOME_COLUNA, VALOR_ANTERIOR, VALOR_POSTERIOR)
VALUES (NEW.CODIGO,NEW.PLACA,@usuario,'ANO',NULL,NEW.ANO,@sysdate);

END//

DELIMITER ;

A idéia para auditar remoções é a mesma, a única alteração é que os valores que serão nulos serão os posteriores, já para a auditoria de atualizações a idéia é checar se o valor anterior é diferente do valor posterior e em caso afirmativo gravar as alterações.

Passo 4) Testar

Para testar essa solução simule inserções, atualizações e remoções de registros nas tabelas auditadas, de preferência com usuários diferentes, atente sempre para o horário em que a manipulação está ocorrendo e se todas as colunas estão sendo devidamente auditadas, afinal um erro de digitação pode ser percebido somente quando já é tarde demais e ai você já não poderá saber o que foi modificado.

Passo 5) Preocupações:

Para que seu esquema de auditoria seja efetivo algumas preocupações devem ser levadas em conta, a primeira delas é a utilização de usuários individuais dentro do seu dia a dia, desenvolvedores, aplicações e mesmo administradores devem sempre que possível possuir seu próprio usuário dentro do banco de dados, assim a informação de qual usuário está conectado ao banco no momento da alteração se torna muito mais valiosa.

A segunda preocupação seria em relação ao volume de dados que esses logs irão ocupar dentro do seu banco de dados, para isso o ideal é desenvoler um mecanismo de limpeza, ou purge, uma dica é a utilização do MySQL Events (disponível a partir da versão 5.1, para versões anteriores recorra ao agendamento do seu sistema operacional): http://dev.mysql.com/tech-resources/articles/mysql-events.html

Passo 6) Novidades

Em relação as últimas coisas que venho lendo sobre segurança da informação como um todo, alguns assuntos que valem a pena caso você queira se aprofundar mais no tema são contextualizar a segurança, ou seja, não tratar a segurança como sendo algo binário como por exemplo um simples password. Outro conceito bastante presente nos dias atuais é a gestão de identidade dentro de uma empresa, ou se preferir, Identity Management. Como última dica recomendo fortemente a leitura do blog de uma das pessoas responsáveis por segurança na Oracle e colunista da revista Oracle Magazine, Mary Ann Davidson, o link para seu blog é: http://blogs.oracle.com/maryanndavidson/

Vou ficando por aqui e qualquer dúvida, crítica ou sugestões estou à disposição pelo e-mail spadafora.caio@gmail.com.

Até uma próxima oportunidade.