AUDITORIA BASEADA EM VALOR COM TRIGGERS

Abaixo, um exemplo bem simples de como utilizar uma trigger para realizar auditoria baseada em valor.

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBDUkVBVEUgT1IgUkVQTEFDRSBUUklHR0VSIHN5c3RlbS50cmdfc2FsYXJpb19hdWRpdApBRlRFUiBVUERBVEUgT0Ygc2FsCk9OIHNjb3R0LmVtcApSRUZFUkVOQ0lORyBORVcgQVMgTkVXIE9MRCBBUyBPTEQKRk9SIEVBQ0ggUk9XCkJFR0lOCiAgSUYgOm9sZC5zYWwgIT0gOm5ldy5zYWwKICAgIFRIRU4KICAgIElOU0VSVCBJTlRPIHN5c3RlbS5zYWxhcmlvX2F1ZGl0CiAgICBWQUxVRVMgKHN5c19jb250ZXh0KCd1c2VyZW52JywgJ29zX3VzZXInKSwKICAgICAgICAgICAgc3lzX2NvbnRleHQoJ3VzZXJlbnYnLCAnaXBfYWRkcmVzcycpLAogICAgICAgICAgICAnTyBmdW5jaW9uw6FyaW8gJ3x8ZW1wbm98fCcgdGV2ZSBvIHNldSBzYWzDoXJpbyBhbHRlcmFkbyAgICAgICAgICAgICAgZGUgJ3x8Om9sZC5zYWx8fCcgcGFyYSAnfHw6bmV3LnNhbCk7CiAgRU5EIElGOwpFTkQ7Ci8=” hover_enabled=”0″]U1FMPiBDUkVBVEUgT1IgUkVQTEFDRSBUUklHR0VSIHN5c3RlbS50cmdfc2FsYXJpb19hdWRpdApBRlRFUiBVUERBVEUgT0Ygc2FsCk9OIHNjb3R0LmVtcApSRUZFUkVOQ0lORyBORVcgQVMgTkVXIE9MRCBBUyBPTEQKRk9SIEVBQ0ggUk9XCkJFR0lOCiAgSUYgOm9sZC5zYWwgIT0gOm5ldy5zYWwKICAgIFRIRU4KICAgIElOU0VSVCBJTlRPIHN5c3RlbS5zYWxhcmlvX2F1ZGl0CiAgICBWQUxVRVMgKHN5c19jb250ZXh0KCd1c2VyZW52JywgJ29zX3VzZXInKSwKICAgICAgICAgICAgc3lzX2NvbnRleHQoJ3VzZXJlbnYnLCAnaXBfYWRkcmVzcycpLAogICAgICAgICAgICAnTyBmdW5jaW9uw6FyaW8gJ3x8ZW1wbm98fCcgdGV2ZSBvIHNldSBzYWzDoXJpbyBhbHRlcmFkbyAgICAgICAgICAgICAgZGUgJ3x8Om9sZC5zYWx8fCcgcGFyYSAnfHw6bmV3LnNhbCk7CiAgRU5EIElGOwpFTkQ7Ci8=[/et_pb_dmb_code_snippet]

Neste exemplo, é monitorada a tabela EMP do schema SCOTT. Provavelmente você leitor já conhece esta tabela e este schema, usados há mais de uma década como exemplo em treinamentos. Esta tabela armazena dados de funcionários (employees), incluindo o valor do seu salário (na coluna SAL).

A trigger acima registra numa tabela chamada salario_audit, no schema system, sempre que algum registro da tabela EMP sofrer update na coluna SAL. Ou seja, se alguém fizer uma atualização no salário de um ou mais funcionários, esta operação será registrada na tabela system.salario_audit. O exemplo é bem simples, e usa a função SYS_CONTEXT para retornar o usuário logado no sistema operacional da máquina onde foi disparado o update, e também o endereço de IP desta máquina. Além disso, armazena em uma coluna um pequeno texto descrevendo qual é o código do funcionário que teve o salário alterado, além dos valores do salário antes e depois do update.

A auditoria por meio de triggers é um processo mais lento do que a auditoria de banco de dados, mas ela fornece mais informações e permite que você implemente regras de negócio sofisticadas.

FINE-GRAINED AUDIT (FGA)

A FGA pode ser configurada para gerar registros de auditoria somente quando certas linhas são acessadas ou quando determinadas colunas de certas linhas são acessadas. Ela também pode executar um bloco de código PL/SQL quando a condição de auditoria é quebrada.

A FGA é configurada com o pacote DBMS_FGA. Para criar uma diretiva de auditoria FGA, use a procedure ADD_POLICY, que recebe os seguintes argumentos:

  • OBJECT_SCHEMA – nome do owner do objeto a ser auditado;
  • OBJECT_NAME – nome da tabela a ser auditada;
  • POLICY_NAME – cada diretiva FGA criada deve receber um nome exclusivo para identificação;
  • AUDIT_CONDITION – expressão para determinar quais linhas vão gerar um registro de auditoria. Se deixado como NULL, todas as linhas serão auditadas;
  • AUDIT_COLUMN – lista das colunas a serem auditadas. NULL audita todas as colunas;
  • HANDLER_SCHEMA – nome do usuário que é owner da procedure a ser executada quando as condições de auditoria forem atendidas;
  • ENABLE– por padrão, seu valor é TRUE. Indica que a diretiva está ativa;
    • As procedures ENABLE_POLICY e DISABLE_POLICY ativam e desativam respectivamente a diretiva.
  • STATEMENT_TYPES – define quais comandos devem ser auditados: SELECT, INSERT, UPDATE ou DELETE. Por padrão, somente SELECT;
  • AUDIT_TRAIL – controla se a instrução SQL efetiva e suas variáveis de bind devem ou não ser gravadas na trilha de auditoria da FGA. O padrão é gravar;
  • AUDIT_COLUMN_OPTS –  determina se uma auditoria deve ou não ser feita caso uma instrução trate de alguma ou todas as colunas listadas no argumento AUDIT_COLUMN. As opções são DBMS_FGA.ANY_COLUMNS (padrão) ou DBMS_FGA_ALL_COLUMNS.

Para ver os resultados da FGA, consulte a visão DBA_FGA_AUDIT_TRAIL.

EXEMPLO

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U1FMPiBleGVjdXRlIGRibXNfZmdhLmFkZF9wb2xpY3koCm9iamVjdF9zY2hlbWE9PidTQ09UVCcsCm9iamVjdF9uYW1lPT4nRU1QJywKcG9saWN5X25hbWU9Pidwb2xfZW1wX3Rlc3RlJywKYXVkaXRfY29uZGl0aW9uPT4nZGVwdG5vPTIwJywKYXVkaXRfY29sdW1uPT4nU0FMJyk7″ hover_enabled=”0″]U1FMPiBleGVjdXRlIGRibXNfZmdhLmFkZF9wb2xpY3koCm9iamVjdF9zY2hlbWE9PidTQ09UVCcsCm9iamVjdF9uYW1lPT4nRU1QJywKcG9saWN5X25hbWU9Pidwb2xfZW1wX3Rlc3RlJywKYXVkaXRfY29uZGl0aW9uPT4nZGVwdG5vPTIwJywKYXVkaXRfY29sdW1uPT4nU0FMJyk7[/et_pb_dmb_code_snippet]

Este exemplo cria uma diretiva chamada POL_EMP_TESTE, que irá capturar todas as instruções SELECT que ler a coluna SAL da tabela SCOTT.EMP se ao menos uma das linhas recuperadas for do departamento 20.

A DBA_AUDIT_TRAIL mostra a auditoria de banco de dados padrão; DBA_FGA_AUDIT_TRAIL mostra a FGA; DBA_COMMON_AUDIT_TRAIL mostra ambos. Para ver os resultados da auditoria com triggers você deve criar suas próprias views que tratam das suas próprias tabelas.

REFERÊNCIA BIBLIOGRÁFICA

Este post, assim como todos os posts sobre Certificação OCA deste blog, são trechos do livro “OCA Oracle Database 11g – Administração I (Guia do Exame 1Z0-052)”, da editora Bookman – www.bookman.com.br
Recomendo este livro a todos que pretendem estudar para o exame. Meus posts são apenas algumas dicas para quem já está estudando por outros materiais, e por isso exige uma base de conhecimento anterior em cada um dos capitulos. Para uma referência completa de estudos é recomendado a compra do livro correspondente, bem como a documentação oficial da Oracle.