Nenhum resultado encontrado

Sua pesquisa não corresponde a nenhum resultado.

Sugerimos que você tente o seguinte para ajudar a encontrar o que procura:

  • Verifique a ortografia da sua pesquisa por palavra-chave.
  • Use sinônimos para a palavra-chave digitada; por exemplo, tente “aplicativo” em vez de “software.”
  • Tente uma das pesquisas populares mostradas abaixo.
  • Inicie uma nova pesquisa.
Perguntas Frequentes

Gerenciamento Automático de Auditoria

Por Fernando Simon,
Postado em Abril 2016

Revisado por Marcelo Pivovar - Solution Architect

Auditoria de bancos de dados Oracle pode ser realizada nativamente através de duas formas, auditoria simples (com AUDIT) ou auditoria fina (com Fine Grant Audit – FGA). De qualquer forma elas armazenam os registros em duas tabelas, aud$ e fga_log$.

Quando você habilita qualquer uma destas duas é importante gerenciar corretamente os registros de auditora, como por exemplo local de armazenamento, tempo de armazenamento e afins. É isso que vamos ver abaixo, como fazer isso automaticamente através do Oracle.

Tablespace O primeiro passo é a tablespaces onde as duas tabelas (aud$ e fga_log$) armazenam os dados. Por padrão elas são armazenadas na tablespace system, mas você pode mudar isso. Quando a tablespace chega ao limite os registros param de serem inseridos e se a auditoria estiver na system, o banco todo congela (inclusive logins). Para evitar isso o método correto para mover a auditoria para outra tablespace é através do comando abaixo:

  
BEGIN
  
   --
   --Modificando para a  auditoria simples, a AUD$
   DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
    , audit_trail_location_value => 'TSAUDIT'
     );
  
   --
   --Modificando para a  auditoria de FGA, a FGA_LOG$
   DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD
    , audit_trail_location_value => 'TSAUDIT'
     );    

END;
 
 

O parâmetro audit_trail_location_value define a tablespace destino e claro que antes de executar o comando acima você deve cria-la. Se você armazena muitos registros de auditoria recomenda-se criar a tablespace como bigfile, assim não terá o limite de 32GB. Se você já tiver dados nas tabelas de auditoria deverá criar a tablespace com tamanho inicial maior do que o tamanho atual das tabelas de auditoria.

No exemplo acima foram movidas as duas tabelas separadamente, mas você pode as duas ao mesmo tempo. Para isso basta mudar o parâmetro audit_trail_type conforme a tabela abaixo:

  • AUDIT_TRAIL_AUD_STD: Faz referência somente a aud$
  • AUDIT_TRAIL_FGA_STD: Faz referência somente a fga_log$
  • AUDIT_TRAIL_DB_STD: Ambas as tabelas, aud$ e fga_log$

Habilitando o Gerenciamento Automático O gerenciamento automático da auditoria deve ser habilitado para permitir a movimentação de registros de auditoria entre tablespaces. Se você não moveu as tabelas aud$ e fga_log$ de tablespaces o Oracle automaticamente move os registros da SYSTEM para a SYSAUX. Caso já tenha movido ele não moverá nada. Observe o comando abaixo.

  
BEGIN
   DBMS_OUTPUT.ENABLE(10000);
   
--
--Verifica se o init foi habilitado para  as tabelas definidas

   IF NOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD)  THEN
    DBMS_OUTPUT.PUT_LINE('Limpeza de auditoria será inicializada');
   
--
--Inicializando a auditoria para a AUD$  e FGA

   DBMS_AUDIT_MGMT.INIT_CLEANUP(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD
     , default_cleanup_interval => 720
   );       

   ELSE

    DBMS_OUTPUT.PUT_LINE('Limpeza de  auditoria já inicializada');
    
   END IF;

END;
 
 

Através dele usamos a procedure DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED para verificar se o gerenciamento já foi habilitado. Caso não tenha sido, usamos a procedure DBMS_AUDIT_MGMT.INIT_CLEANUP para habilitar, e com um intervalo escolhido de 720 horas entre estas movimentações. Para Oracle 12C você pode especificar se deseja fazer isso para um PDB específico ou para todos.

Recomendo mover as tabelas de auditoria para uma tablespace específica, pois deixando no padrão não tem como especificar (através do INIT_CLEANUP) a data e hora que irá executar a movimentação e caso você tenha muitos dados pode impactar no ambiente. Mas caso você tenha poucos registros de auditoria o INIT_CLEANUP pode ser válido.

CREATE_PURGE_JOB A forma mais fácil de habilitar o gerenciamento automático é criar o job de limpeza de auditoria através da procedure DBMS_AUDIT_MGMT.CREATE_PURGE_JOB. Observe:

  
BEGIN
   DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    audit_trail_type =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD
     , audit_trail_purge_interval => 720 --intervalo de limpeza, em  horas
     , audit_trail_purge_name  => 'JOB_Limpeza_Auditoria' --nome do job
     , use_last_arch_timestamp => TRUE 
   );                        
END;
 
 

Os parâmetros são:

  • audit_trail_type define qual o tipo de auditoria que você deseja limpar, segue a mesma tabela do início do texto.
  • audit_trail_purge_interval define o intervalo (em horas) que o job será chamado. Utilizará como base a data e hora que o job foi criado, no final será demonstrado como ajustar isso.
  • audit_trail_purge_name: define o nome do Job.
  • use_last_arch_timestamp: define quais s registro serão excluídos, TRUE indica que será com base em um valor definido (veremos como fazer isso abaixo) e FALSE o job removerá todos os registros.

Para o 12c podemos especificar, através do parâmetro container quais serão os afetados pelo job, todos ou somente o especificado. Quando você define todos, o job é criado no PDB root e este realiza a limpeza em todos os PDB’s.

Seguindo o exemplo acima, o job JOB_Limpeza_Auditoria irá ser executado a cada 720 horas (30 dias) e excluirá todos os registros de auditoria definidos pelo last_archive_time (explicarei no próximo tópico). O interessante do job automático é que o próprio kernel do Oracle se preocupa em localizar os registros a serem removidos e faz isso de forma controlada. Ele não remove todos através de um único delete, faz isso de forma escalonada a cada 10000 registros.

A data e hora que o job será executado depende do momento de execução do CREATE_PURGE_JOB. Como serão criados outros jobs auxiliares, mostrarei no final do artigo como aninhar todos e definir corretamente a execução destes.

Você pode verificar através da tabela DBA_AUDIT_MGMT_CLEANUP_JOBS  como ficou a configuração:

  
SQL>  COL JOB_NAME FORMAT A25 
 SQL>  COL JOB_FREQUENCY FORMAT A30 
 SQL>  COL AUDIT_TRAIL FORMAT A40 
 SQL>  SET LINESIZE 200  
 SQL>  SELECT * FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;
 
 JOB_NAME                 JOB_STAT AUDIT_TRAIL                        JOB_FREQUENCY
------------------------ -------- ---------------------------------- ---------------------------
JOB_LIMPEZA_AUDITORIA    ENABLED  STANDARD AND FGA AUDIT TRAIL       FREQ=HOURLY;INTERVAL=720
SQL>
 
 

Marcando registros a serem excluídos No procedimento acima utilizamos o parâmetro use_last_arch_timestamp como TRUE e isso define que a limpeza somente irá excluir os registros que definirmos e não toda a tabela. Para fazer isso usamos a procedure DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP.

Antes disso uma consideração, geralmente quando precisamos identificar algo na auditoria temos requisitos como: “O que João fez a 15 dias atrás?”, “Quem fez update na tabela salário este mês?”. Estes são exemplos, mas na grande maioria das vezes precisamos buscar o histórico e por isso é importante deixar alguns dias no banco para evitar restaurar backup para consulta auditoria.

Por isso que no procedimento que criou o job foi utilizado use_last_arch_timestamp como TRUE. Mas isso deixou o requisito de definir esse ponto até o momento no qual os dados não podem ser apagados. Pense nas tabelas de auditoria com o uma fila, precisamos marcar nessa fila até onde o job de limpeza pode deletar.

Para isso vamos utilizar a procedure DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP e também criar um job para fazer isso de forma automática (para que o job possa executar, criaremos uma procedure específica). A procedure criada:

  
CREATE  OR REPLACE PROCEDURE AUDIT_DefineMarcacaoAudit

  IS

  BEGIN

  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   audit_trail_type =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
   , last_archive_time => SYSDATE - 45
    );                                                

  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   audit_trail_type =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD
   , last_archive_time => SYSDATE - 45
    );                                                

END;
 
 

A procedure acima utiliza DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP e marca que todos os registros de auditoria mais velhos que 45 dias (na data de execução da procedure) são marcados para exclusão. Também está separada a definição de datas para aud$ e fga_log$, assim fica mais fácil adaptar para requisitos diferentes de datas. Para deixar a marcação automatizada cria-se o job abaixo:

  
BEGIN
   
DBMS_SCHEDULER.CREATE_JOB(
    job_name           =>  'JOB_MarcaExclusaoAudit' --nome do job
    , job_type           =>  'STORED_PROCEDURE'
    , job_action         =>   'AUDIT_DefineMarcacaoAudit'
    , repeat_interval     =>  'FREQ=MONTHLY;INTERVAL=1'  -- uma vez ao mes
    , comments           =>  'Job que executa a procedure que marca ate  que ponto a auditoria pode ser removida'
   );

   --
   --Todo o job vem desabilitado por padrao no  scheduler do 11.2, habilitando

   DBMS_SCHEDULER.ENABLE(
    name => 'JOB_MarcaExclusaoAudit'
    );

END; 
 
 
 

O job acima executará 1 vez por mês e chamará a procedure que criamos antes. Assim, garantimos que 45 dias ficarão nas tabelas de auditoria. Se você observar, no momento que o job de limpeza for executar (ele executa a cada 30 dias também) você terá aproximadamente 75 dias dados armazenados, por isso é importante definir a tablespace de auditoria com um tamanho (ou tipo) condizente com o volume.

Aninhando JOBS Os jobs criados acima estão marcados para executar com base no momento de criação e isso não é interessante. Além disso, não adianta executar o job de marcação de exclusão depois do job de limpeza. Para resolver isso podemos modificar os parâmetros dos jobs através do scheduler. Exemplo:

  
BEGIN

   --ajusta data inicial comum e aninhada
    DBMS_SCHEDULER.SET_ATTRIBUTE(
     name => 'JOB_MARCAEXCLUSAOAUDIT'
     , attribute => 'start_date'
     , value => TO_TIMESTAMP('15-JAN-13  12.00.00', 'DD-MON-RRRR HH24.MI.SS')
   );

   DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'JOB_LIMPEZA_AUDITORIA'
     , attribute => 'start_date'
     , value => TO_TIMESTAMP('15-JAN-13  12.05.00', 'DD-MON-RRRR HH24.MI.SS')
   );
   
   --Segundo sabado do mes
   DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'JOB_MARCAEXCLUSAOAUDIT'
     , attribute => 'repeat_interval'
     , value => 'FREQ=MONTHLY;  BYDAY=2SAT'
   );

   --Segundo sabado do mes
   DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => 'JOB_LIMPEZA_AUDITORIA'
     , attribute => 'repeat_interval'
     , value => 'FREQ=MONTHLY;  BYDAY=2SAT'
   );   

END;
 
 

Com a definição acima temos:

  • JOB_MARCAEXCLUSAOAUDIT: O que importa aqui é a hora de execução, tem que ser antes do job de exclusão. A data inicial foi definida como 15 de Janeiro de 2013 (pode ser qualquer data) com horário para as 12:00. Também foi definido que ele será executado no segundo sábado de cada mês.
  • JOB_LIMPEZA_AUDITORIA: Também tem a data inicial como 15 de Janeiro de 2013, mas o horário é as 12:05. Isso garante que a limpeza sempre executará depois da marcação dos registros. E ele também executa no segundo sábado de cada mês.

Você pode modificar os valores acima com base nos seus requisitos, mas o importante é garantir que a marcação dos registros sempre ocorra antes da limpeza.

Tabelas importantes Algumas tabelas de gerenciamento são importantes de serem acompanhadas. A primeira delas é a DBA_AUDIT_MGMT_CLEAN_EVENTS que registra todos os eventos automáticos de limpeza que ocorreram e quantos registros foram apagados para cada tabela (por isso aparecem separados):

  
SQL> COL CLEANUP_TIME FORMAT  A35
  SQL> COL AUDIT_TRAIL  FORMAT A25 
  SQL> SET PAGESIZE 1000 
  SQL> SET LINESIZE 200  
  SQL> SELECT audit_trail,  cleanup_time, delete_count FROM DBA_AUDIT_MGMT_CLEAN_EVENTS ORDER BY  cleanup_time DESC;
  
  AUDIT_TRAIL               CLEANUP_TIME                        DELETE_COUNT
------------------------- ----------------------------------- ------------
STANDARD AUDIT TRAIL      09-APR-16 10.33.53.867186 AM +00:00        56470
STANDARD AUDIT TRAIL      09-APR-16 10.33.53.865009 AM +00:00     25217082
STANDARD AUDIT TRAIL      20-MAR-16 10.00.40.545453 AM +00:00       742355
STANDARD AUDIT TRAIL      20-MAR-16 10.00.40.542995 AM +00:00     88551571
..
..
STANDARD AUDIT TRAIL      25-JUL-13 02.10.14.479176 PM +00:00       340175
STANDARD AUDIT TRAIL      25-JUL-13 02.10.14.465855 PM +00:00      9362182

68 rows selected.
SQL>
 
 

Outra tabela importante é DBA_AUDIT_MGMT_LAST_ARCH_TS que pode ser consulta para saber até onde foi a marcação de registros a serem limpos. No exemplo abaixo a marcação informa que o job de limpeza somente apagará registros até 26/02/2016 12:00:

  
SQL>  COL LAST_ARCHIVE_TS FORMAT A35
  SQL>  SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;
  AUDIT_TRAIL               RAC_INSTANCE LAST_ARCHIVE_TS
------------------------- ------------ ------------------------------------
STANDARD  AUDIT TRAIL                0 24-FEB-16  12.00.00.000000 AM +00:00
FGA  AUDIT TRAIL                     0 24-FEB-16 12.00.00.000000 AM  +00:00
SQL>
 
 

Outra tabela é a DBA_AUDIT_MGMT_CONFIG_PARAMS que mostra como os estão os parâmetros de gerenciamento da auditoria:

  
SQL>  COL PARAMETER_VALUE FORMAT A25 
 SQL>  COL PARAMETER_NAME FORMAT A25  
 SQL>  SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
 PARAMETER_NAME             PARAMETER_VALUE           AUDIT_TRAIL
-------------------------  ------------------------- -----------------------
DB  AUDIT TABLESPACE       TSAUDITTJ                 STANDARD AUDIT TRAIL
DB  AUDIT TABLESPACE       TSAUDITTJ                 FGA AUDIT TRAIL
AUDIT  FILE MAX SIZE       10000                     OS AUDIT TRAIL
AUDIT  FILE MAX SIZE       10000                     XML AUDIT TRAIL
AUDIT  FILE MAX AGE        5                         OS AUDIT TRAIL
AUDIT  FILE MAX AGE        5                         XML AUDIT TRAIL
DB  AUDIT CLEAN BATCH SIZE 10000                     STANDARD AUDIT TRAIL
DB  AUDIT CLEAN BATCH SIZE 10000                     FGA AUDIT TRAIL
OS  FILE CLEAN BATCH SIZE  1000                      OS AUDIT TRAIL
OS  FILE CLEAN BATCH SIZE  1000                      XML AUDIT TRAIL
DEFAULT  CLEAN UP INTERVAL 720                       STANDARD AUDIT TRAIL
DEFAULT  CLEAN UP INTERVAL 720                       FGA AUDIT TRAIL

12 rows selected.
SQL>
 
 

Como foram criados jobs manualmente é importante verificar a execução deles. Para isso utilizamos a tabela do próprio schedule. Observe os parâmetros (blocos e procedures) bem como as datas de execução aninhadas:

  
SQL>  COL OWNER FORMAT A5  
SQL>  COL JOB_NAME FORMAT A25 
 SQL>  COL JOB_TYPE FORMAT A20 
 SQL>  COL REPEAT_INTERVAL FORMAT A25
 SQL>  COL JOB_ACTION FORMAT A30  
 SQL>  COL START_DATE FORMAT A35  
 SQL>  COL NEXT_RUN_DATE FORMAT A35 
 SQL>  SET LINESIZE 300  
 SQL>  SELECT DSJ.OWNER, DSJ.JOB_NAME, DSJ.JOB_TYPE, DSJ.JOB_ACTION, DSJ.START_DATE, DSJ.REPEAT_INTERVAL,   DSJ.NEXT_RUN_DATE,
 DSJ.NEXT_RUN_DATE, DSJ.RUN_COUNT, DSJ.FAILURE_COUNT
   2  FROM  DBA_SCHEDULER_JOBS DSJ
   3   WHERE JOB_NAME IN ('JOB_LIMPEZA_AUDITORIA', 'JOB_MARCAEXCLUSAOAUDIT');

OWNER JOB_NAME                  JOB_TYPE             JOB_ACTION                                                   
----- ------------------------- -------------------- -------------------------------------------------------------
SYS   JOB_LIMPEZA_AUDITORIA     PLSQL_BLOCK          BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(3,  TRUE);  END;     
SYS   JOB_MARCAEXCLUSAOAUDIT    STORED_PROCEDURE     AUDIT_DefineMarcacaoAudit                                    

START_DATE                           REPEAT_INTERVAL           NEXT_RUN_DATE                        RUN_COUNT 
-----------------------------------  ------------------------- -----------------------------------  --------- 
15-JAN-13 12.05.00.000000 AM -03:00  FREQ=MONTHLY; BYDAY=2SAT  14-MAY-16 12.05.00.700000 AM -03:00        34
15-JAN-13 12.00.00.000000 AM -03:00  FREQ=MONTHLY; BYDAY=2SAT  14-MAY-16 12.00.00.200000 AM -03:00        32

FAILURE_COUNT
-------------
            1
            0
			SQL>
 
 

Outros cuidados Se você desejar pode criar um job para mover as tabelas de auditoria de tempos em tempos (como por exemplo no 3º sábado do mês). Como as tabelas recebem deletes pelo job de limpeza o “início” dela fica desfragmentado e novos registros poderão ser adicionados no fim. Em resumo ela eventualmente pode nunca reduzir de tamanho.

Se você mover elas (através da DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION) você garante que na nova tablespace somente o tamanho necessário é utilizado. Você também pode fazer um shrink se desejar. Se quiser fazer isso, crie uma procedure para mover e um job para automatizar o processo.

Todos os procedimentos acima foram executados com o usuário sys. Com mínimas adaptações você pode executar os procedimentos acima nos bancos Oracle 10.2 até o 12C.

Gerenciamento Com tudo isso você tem um controle automatizado dos seus registros de auditoria. Garantindo que você consiga consultar dados históricos de auditoria sem precisar voltar backup. Além disso garante que as tablespaces system e sysaux não ficarão cheias de registros.

Referências DBMS_AUDIT_MGMT para 11.2 (https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_audit_mgmt.htm) DBMS_AUDIT_MGMT para 12C (https://docs.oracle.com/database/121/ARPLS/d_audit_mgmt.htm)

Fernando Simon DBA do Tribunal de Justiça de Santa Catarina. Trabalha como DBA a diversos anos, desde o Oracle 9i até o Oracle 12c. Tem experiência prática com Oracle Exadata (do V2 ao X5) e soluções que dependem de High Availability como Zero Data Loss, Data Guard, Oracle RAC e replicações diversas. Também atua como palestrante em eventos Oracle no Brasil. Blog: http://www.fernandosimon.com/blog.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.