André Spadini | MultiCloud | DBA

André Spadini | MultiCloud | DBA

Oracle: Auditoria de tabela no 11g

Oracle: Auditoria de tabela no 11g

Subscribe to my newsletter and never miss my upcoming articles

Segue abaixo como ativar auditoria para comandos de DDL, se for necessário para comandos DMLs por tabela, essa configuração foi realizada no Oracle Database 11g.

--
-- O parâmetro AUDIT_TRAIL deve estar em DB_EXTENDED.
ALTER SYSTEM SET AUDIT_SYS_OPERATIONS = TRUE SCOPE=SPFILE;
ALTER SYSTEM SET AUDIT_TRAIL = DB_EXTENDED SCOPE=SPFILE;

--
-- Verificar se o processo automático de expurgo da auditoria está inicializado.
SET SERVEROUTPUT ON
BEGIN
    IF NOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
    THEN
        DBMS_OUTPUT.PUT_LINE('CLEANUP NOT INITIALIZED' );
    ELSE
        DBMS_OUTPUT.PUT_LINE('CLEANUP INITIALIZED' );
    END IF;
END;
/

--
-- Habilitar o CLEANUP.
EXEC SYS.DBMS_AUDIT_MGMT.INIT_CLEANUP(AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, DEFAULT_CLEANUP_INTERVAL => 24);

--
-- Criar uma tablespace, mover a AUD$ para esta TABLESPACE.]
SELECT FILE_ID, FILE_NAME FROM DBA_DATA_FILES;
CREATE TABLESPACE TS_AUDIT DATAFILE '/u01/oracle/oradata/ORCL/TS_AUDIT_01.DBF' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
SELECT COUNT(*) FROM SYS.AUD$;
NOAUDIT ALL;
NOAUDIT ALL PRIVILEGES;
NOAUDIT EXEMPT ACCESS POLICY;
NOAUDIT ALL ON DEFAULT;
TRUNCATE TABLE SYS.AUD$;
SELECT COUNT(*) FROM SYS.AUD$;
EXEC DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_LOCATION_VALUE => 'TS_AUDIT');

--
-- Criar um Scheduler para marcar diariamente os registros de auditoria conforme a retenção. Nesse exemplo, anteriores a 90 dias.
EXEC DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'AUDIT_LAST_ARCHIVE_TIME');

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
    JOB_NAME        => 'AUDIT_LAST_ARCHIVE_TIME',
    JOB_TYPE        => 'PLSQL_BLOCK',
    JOB_ACTION      => 'BEGIN 
                            DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, LAST_ARCHIVE_TIME => SYSTIMESTAMP-32);
                        END;',
    START_DATE      => SYSTIMESTAMP,
    REPEAT_INTERVAL => 'FREQ=DAILY; BYHOUR=19; BYMINUTE=0; BYSECOND=0;',
    END_DATE        => NULL,
    ENABLED         => TRUE,
    COMMENTS        => 'Automatically set audit last archive time.');
END;
/

-- 
-- Criar o processo de limpeza (roda a cada 24 horas) dos registros que foram marcados pelo scheduler anterior.
BEGIN
    SYS.DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
        AUDIT_TRAIL_TYPE           => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
        AUDIT_TRAIL_PURGE_INTERVAL => 24,
        AUDIT_TRAIL_PURGE_NAME     => 'AUDIT_PURGE_ALL_TRAILS',
        USE_LAST_ARCH_TIMESTAMP    => TRUE
    );
END;
/

-- 
-- Se for necessário alterar o horário, utilize este bloco.
BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE (
        NAME => 'AUDIT_PURGE_ALL_TRAILS',
        ATTRIBUTE => 'REPEAT_INTERVAL',
        VALUE => 'FREQ=DAILY; BYHOUR=04; BYMINUTE=0; BYSECOND=0;');
END;
/

-- 
-- Alterar a quantidade de linhas deletadas no processo batch. Default é 10000. 
BEGIN
    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
      AUDIT_TRAIL_TYPE             => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
      AUDIT_TRAIL_PROPERTY         => DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,
      AUDIT_TRAIL_PROPERTY_VALUE     => 1000000);
END;
/

-- 
-- Limpar agora o que foi marcado como antigo.
EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, USE_LAST_ARCH_TIMESTAMP => TRUE);

-- 
-- Limpar a Auditoria.
SELECT COUNT(*) FROM SYS.AUD$;
NOAUDIT ALL;
NOAUDIT ALL PRIVILEGES;
NOAUDIT EXEMPT ACCESS POLICY;
NOAUDIT ALL ON DEFAULT;
TRUNCATE TABLE SYS.AUD$;
SELECT COUNT(*) FROM SYS.AUD$;

--
-- Auditoria Básica de DDL.
AUDIT ALTER DATABASE BY ACCESS WHENEVER SUCCESSFUL;
AUDIT UPDATE, DELETE ON SYS.AUD$ BY ACCESS;
AUDIT ALTER SYSTEM BY ACCESS WHENEVER SUCCESSFUL;
AUDIT SYSTEM AUDIT BY ACCESS WHENEVER SUCCESSFUL;
AUDIT SYSTEM GRANT BY ACCESS WHENEVER SUCCESSFUL;
AUDIT DIRECTORY BY ACCESS WHENEVER SUCCESSFUL;
AUDIT CREATE ANY DIRECTORY BY ACCESS WHENEVER SUCCESSFUL; 
AUDIT DATABASE LINK BY ACCESS WHENEVER SUCCESSFUL;
AUDIT PUBLIC DATABASE LINK BY ACCESS WHENEVER SUCCESSFUL;
AUDIT INDEX BY ACCESS WHENEVER SUCCESSFUL;
AUDIT MATERIALIZED VIEW BY ACCESS WHENEVER SUCCESSFUL;
AUDIT PROCEDURE BY ACCESS WHENEVER SUCCESSFUL;
AUDIT GRANT PROCEDURE BY ACCESS WHENEVER SUCCESSFUL;
AUDIT PROFILE BY ACCESS WHENEVER SUCCESSFUL;
AUDIT ROLE BY ACCESS WHENEVER SUCCESSFUL;
AUDIT SEQUENCE BY ACCESS WHENEVER SUCCESSFUL;
AUDIT ALTER SEQUENCE BY ACCESS WHENEVER SUCCESSFUL;
AUDIT GRANT SEQUENCE BY ACCESS WHENEVER SUCCESSFUL;
AUDIT SYNONYM BY ACCESS WHENEVER SUCCESSFUL;
AUDIT PUBLIC SYNONYM BY ACCESS WHENEVER SUCCESSFUL;
AUDIT TABLE BY ACCESS WHENEVER SUCCESSFUL;
AUDIT ALTER TABLE BY ACCESS WHENEVER SUCCESSFUL;
AUDIT GRANT TABLE BY ACCESS WHENEVER SUCCESSFUL;
AUDIT TABLESPACE BY ACCESS WHENEVER SUCCESSFUL;
AUDIT TRIGGER BY ACCESS WHENEVER SUCCESSFUL;
AUDIT USER BY ACCESS WHENEVER SUCCESSFUL;
AUDIT ALTER USER BY ACCESS WHENEVER SUCCESSFUL;
AUDIT VIEW BY ACCESS WHENEVER SUCCESSFUL;
AUDIT TYPE BY ACCESS WHENEVER SUCCESSFUL;
AUDIT CREATE SESSION WHENEVER NOT SUCCESSFUL;
AUDIT NETWORK BY ACCESS;
AUDIT NOT EXISTS BY ACCESS WHENEVER SUCCESSFUL;

 --
-- Exemplos expecíficos:
AUDIT SELECT, INSERT, UPDATE, DELETE ON SIGA.SRA010 BY ACCESS;

Abs!

 
Share this