Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegador Mozilla Firefox


terça-feira, 1 de junho de 2010

Quem deletou os registros da tabela no Oracle?

Por Eduardo Legatti

Olá,



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

Google+

18 comentários:

David Ricardo disse...

Eduardo muito boa noite, bem estou aqui para prestigiar e dar os parabéns pelo artigo, muito util, e muito eficaz também, é um dos problemas mais enfrentados desde sempre, e essa sua implementação é bem mais leve do que habilitar por exemplo o AUDIT_TRAIL do banco. Parabéns mais uma vez.

Abraço

Eduardo Legatti disse...

Olá David,

Valeu pelo comentário ... Eu nem sei se seria uma solução mais leve, mas, com certeza, acho que caiu com uma luva para o meu amigo ;-)

Abraços e até mais ...

Felipe Tavares da Silva disse...

Eduardo bem dia!!!!

É inevitavel não parabenizalo pelo blog muito bom tanto que venho lendo ele a um bom tempo e resolvi me certificar em Oracle, como em um dos posts você indicou a 11g é essa que vou fazer, eu tenho trabalho com oracle tem 2 anos mais com pl/sql.

Minha Duvida é você tem informação de quantas pessoas possuem a certificação OCP no Brasil e quantas tiram a mesma por ano aqui no Brasil.

Outra coisa é Possivel tirar a certiticação OCM aqui no Brasil?

Um Abraço parabéns boa sorte sempre.

Atenciosamente
Felipe Tavares da Silva

Eduardo Legatti disse...

Olá Felipe,

Esta informação é bem capaz de você não encontrar em lugar nenhum. Me lembro que alguns anos atrás a Prometric tinha soltado uma estatística a nível mundial em algo de 180.000 profissionais OCP. Com certeza esse número é bem maior hoje. Em relação ao Brasil, acredito que tenham muitos profissionais certificados, mas não irei chutar um número. Já em relação à certificação OCM, acho que aqui no Brasil ainda não há um centro autorizado que aplica a prova prática. Já fiquei sabendo de pessoas que tiveram que sair do Canadá para realizar o exame nos EUA e até mesmo pessoas que foram para a Índia.

No mais, é só ligando para a Oracle Brasil para ter certeza desta informação.

Enfim, obrigado pelo seu comentário, boa sorte nos seus estudos e até mais ...

Coltro's in USA disse...

Olá, boa tarde e parabéns pela clareza na explanação. Apenas um problema que não estou conseguindo solucionar. Quando você cria a trigger, dentro do values do insert into você chama a variável do package, mas andei lendo que antes essa variável teria que ser atribuída a uma variável local. Para mim, o Oracle fica dizendo o tempo todo 'coluna não permitida aqui'. Obrigado

Eduardo Legatti disse...

Olá Coltros,

Qual versão do Oracle você está usando? Os testes foram realizados no Oracle 10g, mas no Oracle 9i também funcionou.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

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,
10 :old.data_cadastro,
11 :old.nome,
12 pkg_usuario.login_usuario,
13 sysdate);
14 end;
15 /

Trigger created.


Abraços e até maais ...

Coltro's in USA disse...

Estou usando Oracle Database 10g Release 10.2.0.1.0 - 64bit Production. Mas penso que o meu problema pode ser outro. Meu aplicativo está em MS-Access com tabelas Oracle vinculadas por ODBC. Até onde eu entendo, a cada operação feita por ODBC, é criada e depois extinta uma session. Pois bem, quando o meu usuário exclui o registro de uma tabela, ele estará fazendo esta exclusão em uma session diferente daquela na qual o login do usuário foi carregado para dentro do package. Logo, sempre que eu disparo a trigger de audit do delete, a variável do package está vazia e produz erros para mim. Estou errado? Obrigado mais uma vêz.

Eduardo Legatti disse...

Olá Coltros,

Muito estranho ... Realmente pode ser algum tipo de problema pelo fato do seu aplicativo estar em MS-Access com tabelas Oracle vinculadas por ODBC. A questão é: Você consegue popular a variável da package através da função SF_LOGIN_USUARIO como demonstrado no artigo? Faça um teste. No mais, mesmo que a variável na package não tenha sido populada, não haveria motivo para a trigger gerar tal erro, pois simplesmente a variável estaria com um valor nulo, mas a trigger executaria sem maiores problemas como demonstrado no exemplo abaixo:

LEGATTI> select * from cliente;

ID DATA_CAD NOME
---------- -------- --------------------
1 14/07/10 Maria dos Santos
2 14/07/10 Marcelo Azevedo
3 14/07/10 Gustavo Rocha
4 14/07/10 Laura Garcia
5 14/07/10 Renata Rodrigues

LEGATTI> delete from cliente where id=1;

1 linha deletada.

LEGATTI> select id,login_usuario from xcliente;

ID LOGIN_USUA
---------- ----------
1

LEGATTI> select sf_login_usuario('teste') login from dual;

LOGIN
---------
teste

LEGATTI> select id,login_usuario from xcliente;

ID LOGIN_USUA
---------- ----------
1
2 teste

Abraços e até mais ...

Coltro's in USA disse...

Depois de mais algumas lidas no seu artigo e algumas correções, a situação que tenho agora é a seguinte: consigo alimantar a variável do package através de ADODB, mas quando a trigger é disparada, a variável está nula e manda o nulo no lugar onde deveria aparecer o nome do usuário. Você poderia me explicar como funcionam as sessions sob ODBC? Acredito que cada acesso que ocorre abre e fecha uma session. Se for isso, a sua solução não serve para este tipo de aplicativo. No meu caso, eu acabei simulando uma trigger pelo próprio aplicativa, mas queria muito poder fazer da forma que você descreveu em seu artigo. Mais uma vez, muito obrigado.

Eduardo Legatti disse...

Olá Coltros,

Não sei como o ADODB/ODBC trata isso, mas pelo que você esta me relatando, não acredito realmente que a sessão que conecta e popula a variável na package não é a mesma que aciona o gatilho, pois isso seria um tanto estranho já que a sessão que é iniciada por uma conexão tem que ser mantida até o encerramento de uma transação (COMMIT/ROLLBACK).

Imagina este cenário:

1- A sessão conecta e popula a variável da package
2- É realizada a exclusão de registros na tabela (isso aciona a trigger)
3- Neste momento é realizado um ROLLBACK.

No cenário acima, no passo 2, tanto a deleção de registros na tabela, assim como a inserção de registros na tabela audit realizada pela trigger, deveriam de ter sido desfeitas.

No mais, espero que você esteja usando os drivers ODBC da própria Oracle ;-)

Abraços e até mais ...

Coltro's in USA disse...

Estou usando o Microsoft ODBC for Oracle. Pesquisei e pesquisei e acabei trocando a solução que foi proposta no seu artigo por outra, buscando dados dentro de V$SESSION, conforme abaixo:

SELECT osuser || '-' || program || '-' || machine INTO v_usuario
FROM V$SESSION
WHERE audsid = SYS_CONTEXT('userenv','sessionid') AND ROWNUM=1;

Se alguém tiver interesse ou necessidade, estou copiando a seguir como ficou a minha trigger:

CREATE TRIGGER APONT_NB.TRGLOG_PRODUTOS_VALIDACOES AFTER INSERT OR DELETE OR UPDATE ON APONT_NB.TBL_PRODUTOS_VALIDACOES
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
DECLARE
v_operation VARCHAR2(10) := NULL;
v_usuario VARCHAR2(100) :=NULL;
BEGIN
IF INSERTING THEN
v_operation := 'INS';
ELSIF UPDATING THEN
v_operation := 'UPD';
ELSE
v_operation := 'DEL';
END IF;

SELECT osuser || '-' || program || '-' || machine INTO v_usuario
FROM V$SESSION
WHERE audsid = SYS_CONTEXT('userenv','sessionid') AND ROWNUM=1;

IF INSERTING OR UPDATING THEN
INSERT INTO APONT_NB.LOG_PRODUTOS_VALIDACOES
(PROD_ID,PROD_VAL_NOME,PROD_VAL_SGN,PROD_VAL_EM,PROD_VAL_USU,LOG_POR,LOG_TIPO)
VALUES
(:NEW.PROD_ID,:NEW.PROD_VAL_NOME,:NEW.PROD_VAL_SGN,:NEW.PROD_VAL_EM,:NEW.PROD_VAL_USU,v_usuario,v_operation);
ELSE
INSERT INTO APONT_NB.LOG_PRODUTOS_VALIDACOES
(PROD_ID,PROD_VAL_NOME,PROD_VAL_SGN,PROD_VAL_EM,PROD_VAL_USU,LOG_POR,LOG_TIPO)
VALUES
(:OLD.PROD_ID,:OLD.PROD_VAL_NOME,:OLD.PROD_VAL_SGN,:OLD.PROD_VAL_EM,:OLD.PROD_VAL_USU,v_usuario,v_operation);
END IF;
END;

Acho que preciso pesquisar melhor o funcionamento das sessões de bancos de dados quando acessados por ODBC. Grande abraço e muito obrigado.

Eduardo Legatti disse...

Olá Coltros,

De fato para pegar informações da própria sessão, não é necessário selecionar dados da view V$SESSION, pois uma vez logado, estas informações já estarão disponíveis na sessão do usuário.

SQL> select SYS_CONTEXT('USERENV', 'TERMINAL') TERMINAL,
2 SYS_CONTEXT('USERENV','SESSION_USER') SCHEMA,
3 SYS_CONTEXT('USERENV', 'IP_ADDRESS') IP,
4 SYS_CONTEXT('USERENV', 'OS_USER') OS_USER,
5 SYS_CONTEXT('USERENV', 'DB_NAME') BANCO from dual;

TERMINAL SCHEMA IP OS_USER BANCO
--------------- --------------- --------------- --------------- ---------------
MAQ_001 SCOTT 192.168.1.10 eduardo.legatti XE

No mais, a intenção do artigo foi a de demonstrar como gravar e obter informações externas à sessão do usuário (como a informação de login de uma aplicação) ;-)

No mais, caso você queira utilizar a V$SESSION para gerar mais informações específicas do seu sistema, então dê uma olhada no artigo Um pouco do pacote DBMS_APPLICATION_INFO ...

Abraços e até mais ...

Fabricio Cologna disse...

Eduardo Boa Tarde,

Primeiramente parabéns pela postagem muito boa !!
Eu consigo fazer este procedimento mas para quando alguém alterar algo na tabela (update) ?

NO Aguardo,
Fabrício .

Eduardo Legatti disse...

Olá Fabrício,

Obrigado pelo comentário. No mais, seria o mesmo procedimento, ou seja, bastaria alterar a trigger ou criar uma outra.

create or replace trigger trg_cliente_exclusao before delete OR UPDATE ...

Abraços e até mais ..

Marcelo P Vargas disse...

Caro Eduardo,
mais uma vez PARABÉNS pelo EXCELENTE artigo !!!
Gostaria de tirar uma dúvida:
No inicio do artigo, você comenta que não usou o audit por três razões e a 1a. razão é uma situação semelhante a que passo aqui no trabalho, no caso "O sistema conecta no banco de dados através de um único usuário de banco de dados"... sendo assim, como essa solução que você apresentou identifica o usuário, se para o banco a conexão é por um único usuário? A variável passa a ser o login do cliente quando ele acessa a rede?
Obrigado pela ajuda.

Eduardo Legatti disse...

Olá Marcelo,

Não confunda usuário (schema) de banco de dados, com o usuário do sistema (tabela de usuários do sistema em questão) ;-)

Abraços e até mais ...

Marcelo P Vargas disse...

OK Eduardo
Então, será que é possível utilizar esse recurso para o meu caso, pois no ERP que utilizamos na empresa, existe um schema chamado "X" e todos os usuários utilizam um programinha auxiliar com esse usuario (schema) para acessar o banco... com isso, agente enxerga várias conexões desse mesmo usuário no banco e fica complicado, pelo banco, saber quem efetou as operações. Possuímos um log das transações que é do próprio sistema.

Eduardo Legatti disse...

Olá Marcelo,

Se o tal "programinha auxiliar" oferecer algum tipo de login na qual o usuário forneça essa informação, então você poderia inclusive colocar essa informação de login do usuário no próprio log de transações do sistema. Para isso, você utilizaria a solução proposta por mim.

Abraços e até mais ...

Postagens populares