Recentemente, um amigo meu me ligou querendo saber o que poderia ser feito para que o sistema cliente-servidor na qual ele é desenvolvedor, pudesse registrar um log contendo os registros excluídos, a data e hora da exclusão e o login de usuário que excluiu os registros de uma determinada tabela do sistema em questão. Bom, de cara a primeira solução que veio à minha mente foi a de habilitar a auditoria do banco de dados através dos comandos AUDIT, mas o mesma não forneceria a solução de armazenar os registros excluídos e muito menos a informação de login do usuário de sistema que excluiu as linhas da tabela. Apesar de a solução de auditoria padrão armazenar na tabela SYS.AUD$ informações do usuário de banco de dados, nome da estação cliente e login de rede do usuário que conectou no banco de dados, três informações foram realmente decisivas para não usar a solução de auditoria padrão:
1- O sistema conecta no banco de dados através de um único usuário de banco de dados.
2- É fundamental que os registros excluídos fiquem gravados.
3- É fundamental que a informação de login do usuário de sistema fique gravado.
Bom, diante destes requisitos, a única solução que pensei foi a de criar uma tabela clone da tabela em questão em conjunto com uma TRIGGER de banco de dados que seria acionada a cada deleção de registro. Bom, aí você perguntaria: Como registrar a informação do login do usuário de sistema responsável por excluir as linhas da tabela? É aí que entra um outro objeto de banco de dados chamado PACKAGE na qual será responsável por armazenar uma variável que guardará a informação do login do usuário na sessão corrente do mesmo, enquanto conectado ao Oracle. Neste caso, para aplicar esta solução, o desenvolvedor deverá realizar uma pequena alteração na aplicação de forma que a cada login realizado via sistema, a variável declarada na PACKAGE seja alimentada com a informação de login do usuário da aplicação.
Em resumo, abaixo está uma pequena demonstração da solução do problema. Neste caso, utilizarei o próprio SQL*Plus para simular a aplicação do meu amigo. Vamos supor que a tabela alvo da minha auditoria seja a tabela CLIENTE na qual irei criar logo abaixo:
SQL> create table cliente (
2 id number constraint pk_cliente primary key,
3 data_cadastro date not null,
4 nome varchar2(100) not null
5 );
Tabela criada.
SQL> insert into cliente values (1,sysdate,'Maria dos Santos');
1 linha criada.
SQL> insert into cliente values (2,sysdate,'Marcelo Azevedo');
1 linha criada.
SQL> insert into cliente values (3,sysdate,'Gustavo Rocha');
1 linha criada.
SQL> insert into cliente values (4,sysdate,'Laura Garcia');
1 linha criada.
SQL> insert into cliente values (5,sysdate,'Renata Rodrigues');
1 linha criada.
SQL> commit;
Commit concluído.
SQL> select * from cliente;
ID DATA_CADASTRO NOME
---------- ------------------- -------------------------------
1 01/06/2010 19:01:31 Maria dos Santos
2 01/06/2010 19:01:32 Marcelo Azevedo
3 01/06/2010 19:01:33 Gustavo Rocha
4 01/06/2010 19:01:34 Laura Garcia
5 01/06/2010 19:01:35 Renata Rodrigues
Pronto. Agora irei criar uma tabela clone da tabela CLIENTE que irei chamar de XCLIENTE e irei adicionar duas colunas que ficarão responsáveis por armazenar a informação de login do usuário de sistema e a data e hora de exclusão da linha da tabela.
SQL> create table xcliente as select * from cliente where 1=2;
Tabela criada.
SQL> alter table xcliente add (login_usuario varchar2(10));
Tabela alterada.
SQL> alter table xcliente add (data_exclusao date);
Tabela alterada.
SQL> desc xcliente
Nome Nulo? Tipo
------------------------- -------- ----------------------------
ID NUMBER
DATA_CADASTRO NOT NULL DATE
NOME NOT NULL VARCHAR2(100)
LOGIN_USUARIO VARCHAR2(10)
DATA_EXCLUSAO DATE
Agora irei criar uma package simples contendo a declaração de uma variável que será responsável por armazenar a informação de login do usuário de sistema.
SQL> create or replace package pkg_usuario as 2 login_usuario varchar2(10); 3 end; 4 / Pacote criado.
Para finalizar, irei criar abaixo uma trigger de banco de dados sobre a tabela CLIENTE que irá replicar o registro excluído para a tabela XCLIENTE. Aliás, você poderá perceber que no corpo da trigger eu irei incluir tanto a variável PKG_USUARIO.LOGIN_USUARIO, como a pseudo-coluna SYSDATE de forma a gravar as informações de quem excluiu o registro e a data e hora da exclusão da linha da tabela, respectivamente.
SQL> create or replace trigger trg_cliente_exclusao
2 before delete
3 on cliente
4 for each row
5 begin
6 insert into xcliente
7 values
8 (:old.id,
9 :old.data_cadastro,
10 :old.nome,
11 pkg_usuario.login_usuario,
12 sysdate);
13 end;
14 /
Gatilho criado.
Vamos agora a um teste prático onde irei simular o login no sistema através de uma sessão aberta pelo SQL*Plus.
C:\>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Ter Jun 1 19:25:37 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SESSAO1> exec pkg_usuario.login_usuario:='Legatti';
Procedimento PL/SQL concluído com sucesso.
SESSAO1> delete from cliente where id in (1,2);
2 linhas deletadas.
SESSAO1> commit;
Commit concluído.
Agora irei realizar a entrada no sistema através de uma outra sessão do SQL*Plus de forma a simular o login de um outro usuário:
C:\>sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Ter Jun 1 19:27:23 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SESSAO2> exec pkg_usuario.login_usuario:='Angela';
Procedimento PL/SQL concluído com sucesso.
SESSAO2> delete from cliente where id=4;
1 linha deletada.
SESSAO2> delete from cliente where id=5;
1 linha deletada.
SESSAO2> commit;
Commit concluído.
Ao final das deleções realizadas pelas duas sessões acima, podemos perceber que apenas um registro restou na tabela CLIENTE.
SQL> select * from cliente;
ID DATA_CADASTRO NOME
---------- ------------------- ---------------------
3 01/06/2010 19:01:33 Gustavo Rocha
Podemos perceber que os demais registros de tabela que foram excluídos, não só foram devidamente armazenados na tabela XCLIENTE, como também tiveram as informações de quem os excluiu e a data e hora da exclusão.
SQL> select * from xcliente;
ID DATA_CADASTRO NOME LOGIN_USUA DATA_EXCLUSAO
---------- ------------------- ----------------------- ---------- -------------------
1 01/06/2010 19:01:31 Maria dos Santos Legatti 01/06/2010 19:26:47
2 01/06/2010 19:01:32 Marcelo Azevedo Legatti 01/06/2010 19:26:47
4 01/06/2010 19:01:34 Laura Garcia Angela 01/06/2010 19:28:25
5 01/06/2010 19:01:35 Renata Rodrigues Angela 01/06/2010 19:28:32
No mais, para que a atribuição da informação de login na variável declarada na package fique mais simples de se implementar no código fonte da aplicação, não existe nada mais apropriado do que uma simples função de banco de dados chamada por uma instrução SELECT.
SQL> create or replace function sf_login_usuario (p_login varchar)
2 return varchar is
3 begin
4 pkg_usuario.login_usuario := p_login;
5 return(p_login);
6 end;
7 /
Função criada.
SQL> select sf_login_usuario('Eduardo') login from dual;
LOGIN
---------------
Eduardo
Após a execução da instrução SELECT acima, poderemos ver abaixo que a variável declarada na package armazenou a informação de login corretamente.
SQL> delete from cliente where id=3;
1 linha deletada.
SQL> select * from xcliente where id=3;
ID DATA_CADASTRO NOME LOGIN_USUA DATA_EXCLUSAO
---------- ------------------- ----------------------- ---------- -------------------
3 01/06/2010 19:01:33 Gustavo Rocha Eduardo 01/06/2010 19:31:52