1. Cenário
Imagine uma aplicação PDV para plataforma Win32 e que agora precisa implementar uma rotina de auditoria (segurança) para algumas tabelas do sistema. É verdade que existem diversos caminhos para implementar essa funcionalidade. Você poderia adicionar um método a sua classe de segurança e chamá-lo a cada evento que você achar relevante dentro do seu sistema. Mas eu, Renato Matos, prefiro adicioná-la dentro do próprio banco de dados por meio de Triggers e Procedures. Dessa forma terei total controle se alguma rotina modificou o conteúdo da minha tabela, podendo assim ficar despreocupado se a equipe de desenvolvimento está esquecendo de chamar o método de registro de Log da minha Classe de segurança.
2. Entendendo Context
Podemos entender Contexto como sendo o cenário onde a sua aplicação está sendo executada. Por meio dele você poderá compartilhar informações como se fossem variáveis globais entre as tabelas do seu banco de dados. Por exemplo: Existem duas variáveis User e Now que disponibilizam o nome do usuário logado e a hora atual. Mas se por exemplo você precisar pegar o nome da máquina, o código da empresa, as configurações que foram parametrizadas para que um determinado ambiente ou sessão do seu aplicativo possa ser executado, não há variáveis suficientes para desempenhar essa funcionalidade.
Na versão 2.1 do banco Firebird foi disponibilizado dois métodos RDB$GET_CONTEXT e RDB$SET_CONTEXT para ler e gravar informações dentro do contexto ou cenário da sua aplicação.
RDB$SET_CONTEXT (Namespace, Variavel, Valor) => Grava o valor para uma variável específica, caso ela não seja somente leitura como acontece com as variáveis do namespace System. O primeiro parâmetro contém o escopo de visualização ou acesso da variável, podendo ser: USER_SESSION (disponibiliza a variável por toda a sessão do banco de dados por enquanto que a conexão for estabelecida) USER_TRANSACTION (disponibiliza a variável até que um commit ou rollback seja processado) SYSTEM (disponibiliza a variável por toda a sessão do banco de dados, mais o seu contéudo é Read-Only. Esse namespace contém variáveis pré-definidas como: CLIENT_ADDRESS, DB_NAME, CURRENT_USER, CURRENT_ROLE, SESSION_ID, TRANSACTION_ID e ISOLATION_LEVEL). O segundo parâmetro contém o nome da variável que você deseja criar e o terceiro o valor que será definido para ela.
RDB$GET_CONTEXT (Namespace, Variavel) => Recebe o valor atual da variável. Se a variável não for encontrada dentro do Namespace essa função irá retornar NULL.
3. Criando as procedures para a auditoria
Vamos começar criando uma procedure chamada Set_Contexto que irá gravar as informações como código da empresa e nome do usuário logado. Quando falo de usuário logado não estou falando de SYSDBA ou outro usuário criando pelo aplicativo GSEC e sim dos nossos usuários do nosso sistema de PDV. Observe o script abaixo:
CREATE PROCEDURE SET_CONTEXTO(P_USUARIO VARCHAR(40), P_EMPRESA INTEGER) AS
BEGIN
RDB$SET_CONTEXT('USER_SESSION', 'USUARIO', P_USUARIO);
RDB$SET_CONTEXT('USER_SESSION', 'EMPRESA', P_EMPRESA);
END
BEGIN
RDB$SET_CONTEXT('USER_SESSION', 'USUARIO', P_USUARIO);
RDB$SET_CONTEXT('USER_SESSION', 'EMPRESA', P_EMPRESA);
END
Observe que a nossa procedure recebe dois parâmetros: um para o login e o outro para o ID da empresa. Agora vamos criar a nossa tabela de auditoria.
CREATE GENERATOR GEN_LOG
CREATE TABLE LOG
(ID INTEGER NOT NULL,
TABELA VARCHAR(50) NOT NULL,
EVENTO CHAR(1) NOT NULL,
(ID INTEGER NOT NULL,
TABELA VARCHAR(50) NOT NULL,
EVENTO CHAR(1) NOT NULL,
TERMINAL VARCHAR(40) NOT NULL,
USUARIO VARCHAR(40) NOT NULL,
EMPRESA INTEGER NOT NULL,
TEXTO BLOB,
DATA_ALTERACAO TIMESTAMP NOT NULL,
PRIMARY KEY(ID),
CHECK (EVENTO IN ('I','A','E'))
)
USUARIO VARCHAR(40) NOT NULL,
EMPRESA INTEGER NOT NULL,
TEXTO BLOB,
DATA_ALTERACAO TIMESTAMP NOT NULL,
PRIMARY KEY(ID),
CHECK (EVENTO IN ('I','A','E'))
)
Para exemplificar a utilização do contexto, criei a tabela com os seguintes campos ID (um unique id que servirá como chave primária), TABELA (nome da tabela do banco de dados que sofreu a alteração), EVENTO (que poderá ser Inclusão, Alteração ou Exclusão) TERMINAL (o nome da maquina do cliente) USUARIO (o login do usuário na aplicação), EMPRESA (o código da empresa que o usuário está logado) TEXTO (uma obs), DATA_ALTERACAO (a data em que a alteração foi realizada). Agora vamos criar uma procedure para armazenar as informações de auditoria.
CREATE PROCEDURE SET_LOG (P_TABELA VARCHAR(50), P_EVENTO CHAR(1), P_OBS BLOB) AS
DECLARE V_ID INTEGER;
DECLARE V_MAQUINA VARCHAR(40);
DECLARE V_USUARIO VARCHAR(40);
DECLARE V_EMPRESA INTEGER;
DECLARE V_ID INTEGER;
DECLARE V_MAQUINA VARCHAR(40);
DECLARE V_USUARIO VARCHAR(40);
DECLARE V_EMPRESA INTEGER;
BEGIN
V_ID = GEN_ID(GEN_LOG,1);
V_MAQUINA = RDB$GET_CONTEXT('SYSTEM', 'CLIENT_ADDRESS');
V_USUARIO = RDB$GET_CONTEXT('USER_SESSION', 'USUARIO');
V_EMPRESA = RDB$GET_CONTEXT('USER_SESSION', 'EMPRESA');
INSERT INTO LOG (ID, TABELA, EVENTO, TERMINAL, USUARIO, EMPRESA, TEXTO, DATA_ALTERACAO)
VALUES (:V_ID, :P_TABELA, :P_EVENTO, :V_MAQUINA, :V_USUARIO, :V_EMPRESA, :P_OBS, 'NOW');
END
V_ID = GEN_ID(GEN_LOG,1);
V_MAQUINA = RDB$GET_CONTEXT('SYSTEM', 'CLIENT_ADDRESS');
V_USUARIO = RDB$GET_CONTEXT('USER_SESSION', 'USUARIO');
V_EMPRESA = RDB$GET_CONTEXT('USER_SESSION', 'EMPRESA');
INSERT INTO LOG (ID, TABELA, EVENTO, TERMINAL, USUARIO, EMPRESA, TEXTO, DATA_ALTERACAO)
VALUES (:V_ID, :P_TABELA, :P_EVENTO, :V_MAQUINA, :V_USUARIO, :V_EMPRESA, :P_OBS, 'NOW');
END
Observe que o conteúdo dos campos TERMINAL, DATA, USUARIO e EMPRESA estão sendo preenchidos pelos valores capturados dentro do contexto do nosso banco de dados. O campo ID que é a nossa chave primária está usando o gerador GEN_LOG para idenfificar as transações realizadas dentro da nossa aplicação.
4. Implantando auditoria na tabela de produtos
Agora que as nossas procedures já estão prontas, por meio de triggers vamos gerenciar todas as alterações em nossa tabela de Produtos. A tabela PRODUTOS está descrita logo abaixo:
CREATE TABLE PRODUTOS
(ID INTEGER NOT NULL PRIMARY KEY,
NOME VARCHAR(50) NOT NULL,
PRECO NUMERIC(15,2) NOT NULL)
O próximo passo é criar a trigger para realizar o controle das alterações.
CREATE TRIGGER TRG_PRODUTOS_BIUD FOR PRODUTOS BEFORE INSERT OR UPDATE OR DELETE AS
DECLARE V_EVENTO CHAR(1);
DECLARE V_TEXTO BLOB;
BEGIN
IF (INSERTING) THEN
BEGIN
V_EVENTO = 'I';
V_TEXTO = 'ID: ' || NEW.ID || ' NOME: ' || NEW.NOME || ' PRECO: ' || NEW.PRECO;
END
DECLARE V_EVENTO CHAR(1);
DECLARE V_TEXTO BLOB;
BEGIN
IF (INSERTING) THEN
BEGIN
V_EVENTO = 'I';
V_TEXTO = 'ID: ' || NEW.ID || ' NOME: ' || NEW.NOME || ' PRECO: ' || NEW.PRECO;
END
IF (UPDATING) THEN
BEGIN
V_EVENTO = 'U';
V_TEXTO = 'ID: ' || NEW.ID || ' NOME: ' || NEW.NOME || ' PRECO: ' || NEW.PRECO;
END
BEGIN
V_EVENTO = 'U';
V_TEXTO = 'ID: ' || NEW.ID || ' NOME: ' || NEW.NOME || ' PRECO: ' || NEW.PRECO;
END
IF (DELETING) THEN
BEGIN
V_EVENTO = 'D';
V_TEXTO = 'ID: ' || OLD.ID || ' NOME: ' || OLD.NOME || ' PRECO: ' || OLD.PRECO;
END
BEGIN
V_EVENTO = 'D';
V_TEXTO = 'ID: ' || OLD.ID || ' NOME: ' || OLD.NOME || ' PRECO: ' || OLD.PRECO;
END
EXECUTE PROCEDURE SET_LOG('PRODUTOS', V_EVENTO, V_TEXTO);
END
END
Obs: Galera para que o exemplo acima possa funcionar dois detalhes são importantíssimos: Versão do Firebird 2.1 e após a conexão com o banco de dados, na sua aplicação você tem que chamar a Stored Procedure Set_Contexto passando como parâmetro o nome do usuário e o código da empresa.
1. Inicializando as variáveis de Contexto
EXECUTE PROCEDURE SET_CONTEXTO ('RENATO', 1)
2. Cadastrando um Produto
INSERT INTO PRODUTOS (ID, NOME, PRECO) VALUES (1, 'MACA', 1.20)
3. Vendo o Log
SELECT ID, TABELA, EVENTO, USUARIO, CAST(TEXTO AS VARCHAR(200)) LOG, DATA_ALTERACAO FROM LOG
Obs: Tá liberado! O artigo está de graça para todo mundo. Esse é mais um presente MEU para vocês.
Obrigado pelos comentários e espero que possam tirar o maior proveito possível do material.
