Auditoria Standard Oracle Database – …::: César DBA :::…

Implementação auditoria Oracle.

Habilitar processo de auditoria no servidor:

show parameter audit;
alter system set audit_trail=db SCOPE=spfile;
shutdown immediate;
startup open;
show parameter audit;

SQL

Criar tablespace para auditoria:

SET linesize 130
SET pagesize 60
SELECT TABLESPACE_NAME, FILE_NAME FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME;
 
CREATE TABLESPACE "TBS_AUDITORIA" DATAFILE
'/u01/app/oracle/oradata/LAB11G/tbs_auditoria.dbf' SIZE 5G AUTOEXTEND ON NEXT 200M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

SQL

Permissões na tablespace:

ALTER USER CESAR QUOTA UNLIMITED ON TBS_AUDITORIA;

SQL

Movendo aud$ para tablespace tbs_auditoria:

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'TBS_AUDITORIA');
END;
/

SQL

Movendo fga_log$ para tablespace tbs_auditoria:

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'TBS_AUDITORIA');
END;
/
 
SET linesize 130
SET pagesize 60
SELECT segment_name, segment_type FROM dba_segments WHERE TABLESPACE_NAME='TBS_AUDITORIA' ORDER BY bytes DESC;

SQL

habilitar auditoria de ddl, auditar sys:

alter system set audit_sys_operations=TRUE SCOPE=spfile;
shutdown immediate;
startup open;
show parameter audit_sys_operations;

SQL

Exemplo de ddl a ser auditado do usuário cesar:

AUDIT ALL BY CESAR BY ACCESS;

SQL

Criando um objeto e verificando a auditoria:

CREATE TABLE CESAR.TB_TESTE (ID NUMBER);

SQL

Tabela de consulta de eventos auditados de ddl:

SET linesize 130
SET pagesize 60
SELECT
TO_CHAR (EXTENDED_TIMESTAMP,'DD-MM-RRRR HH24:MI:SS') DATA,
USERNAME, OS_USERNAME, USERHOST,TERMINAL,
OWNER,ACTION_NAME,OBJ_NAME
FROM
DBA_AUDIT_TRAIL
ORDER BY DATA DESC;
/

SQL

Habilitando auditoria de dml:

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY USUARIO_BANCO BY ACCESS;

SQL

Exemplo de usuário auditado:

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY CESAR BY ACCESS;

SQL

Habilitando auditoria de procedure:

AUDIT EXECUTE PROCEDURE BY USUARIO_AUDITADO BY ACCESS;
AUDIT EXECUTE PROCEDURE BY CESAR BY ACCESS;

SQL

Consultando auditoria de dml:

SELECT TO_CHAR (EXTENDED_TIMESTAMP,'DD-MM-RRRR HH24:MI:SS') DATA,
DB_USER USUARIO,
OS_USER,
USERHOST HOST,
OBJECT_SCHEMA,
OBJECT_NAME,
STATEMENT_TYPE,
SQL_TEXT
FROM DBA_FGA_AUDIT_TRAIL
ORDER BY TIMESTAMP DESC;

SQL

View pronta para ser consultada:

CREATE OR REPLACE VIEW VW_AUDITORIA_DML
AS
SELECT TO_CHAR (EXTENDED_TIMESTAMP,'DD-MM-RRRR HH24:MI:SS') DATA,
DB_USER USUARIO,
OS_USER,
USERHOST HOST,
OBJECT_SCHEMA,
OBJECT_NAME,
STATEMENT_TYPE,
SQL_TEXT
FROM DBA_FGA_AUDIT_TRAIL
ORDER BY TIMESTAMP DESC;

SQL

Remover auditoria de ddl:

show parameter audit;
alter system set audit_trail=none SCOPE=spfile;
shutdown immediate;
startup open;
show parameter audit;

SQL

Limpar dados da auditoria:

BEGIN
DBMS_AUDIT_MGMT.init_cleanup(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
default_cleanup_interval => 12 /* hours */);
END;

SQL

Tipos de auditoria:

DDL (CREATE, ALTER & DROP of objects)
DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
SYSTEM EVENTS (LOGON, LOGOFF etc.)

SQL

Leave a Reply Cancel reply