Como auditar e gerar relatórios de auditoria em Oracle Databases

Olá pessoal, um tempo atrás tive que fazer algumas auditorias em um cliente meu, foi então que fiz esse documento para documentar o procedimento de habilitar vários tipos de auditoria e gerar relatórios de tudo que está sendo auditado através de exemplos.

Espero que seja útil para vocês o quanto foi útil para mim, boa leitura.

Como auditar e gerar relatórios de auditoria em Bancos de dado Oracle.

Habilitando Auditoria

O primeiro  passo é  habilitar a auditoria, para isso será  necessário mudar o parâmetro de inicialização audit_trail e definir o tipo de auditoria desejado:

OS : Auditoria Habilitada, os registros vão ser gravados em diretórios do sistema em arquivos de auditoria.
DB ou TRUE : Auditoria é habilitada, os registros de auditoria serão armazenadas no database (SYS.AUD$)
XML : Auditoria é habilitada, os registros serão armazenados em formatos XML.
NONE ou FALSE : Auditoria é desabilitada.
DB_EXTENDED : Trabalha igual ao parâmetro DB, mais as colunas SQL_BIND e SQL_TEXT são preenchidas.

Quando selecionamos os modos OS ou XML, são criados arquivos contendo os registros do auditoria no diretório definido pelo parâmetro audit_file_dest.

Considerando um tipo de auditoria DB, executaremos o seguintes comandos:

SQL> ALTER SYSTEM SET audit_trail = “DB” SCOPE = SPFILE;

SQL> shutdown immediate

SQL> startup

O artigo está divido em:

1. Conexões de auditoria administrativa
2. Auditoria no nível de objeto
3. Auditoria no nível de privilégio
4. Auditoria de Instruções

Mostrarei em cada seção o que pode ser auditado, como verificar o que é auditado e alguns exemplos de relatório de auditoria.

Auditando:
————————————–
A auditoria pode ser definida em nível de instrução, nível de objeto e nível de privilégio.
Existe também auditoria de conexões administrativas, que são registrados através de log.

1. Auditando Conexões administrativas
————————————–
Conexões administrativas são CONNECT INTERNAL e CONNECT / AS SYSDBA.

O usuário SYS é o usuário mais útil e importante para qualquer banco de dados Oracle. Vamos dar uma olhada como um DBA pode impedi-lo se ele está sendo mal utilizado.

Como auditar conta SYS

Há apenas dois parâmetros que precisam ser definidos para auditar qualquer conta SYS, o audit_file_dest e o AUDIT_SYS_OPERATIONS.
O primeiro define o local para arquivos de auditoria e o segundo liga ou deliga a auditoria do SYS com ON / OFF.

Os arquivos de auditoria serão criados no diretório definido pelo parâmetro audit_file_dest com o nome ora_.aud. Esse arquivo geralmente têm seguintes informações:

Arquivo de auditoria / u01/app/oracle/admin/ALEBD/adump/ora_14725.aud
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: hostname.domainname
Release: 2.6.9-34.ELsmp
Version: #1 SMP Fri Apr 12 13:52:53 EST 2011
Machine: i686
Instance name: ALEBD
Redo thread mounted by this instance: 1
Oracle process number: 25
Unix process pid: 134745, image: oracle@hostname.domainname (TNS V1-V3)

Tue Mar 6 13:21:31 2012
ACTION : ‘CONNECT’
DATABASE USER: ‘/’
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/1
STATUS: 0

Com todas as informações relacionadas à sessão como ‘connect’ e todos os comandos válidos, ele fornece informações sobre o servidor, sistema operacional, versão, Maquina, etc. porém ele não armazena os comandos inválidos.

Os arquivos de auditoria também armazenam informações de logins inválidos. Para verificar logins inválidos, execute os seguintes comandos:

grep-i “status:” * aud | grep-i-v “status: 0”.

Parâmetros que devem ser definidos

alter system set audit_file_dest=’/u04/app/oracle/oradata/alex/audit’ scope=spfile;
alter system set audit_sys_operations=true scope=spfile;
Reinicie a banco de dados e pronto, está feito.

As Informações de auditoria podem ser visualizados em formato de texto ou no formato XML, dependendo da configuração do parâmetro AUDIT_TRAIL

2. Auditoria no nível de objeto
—————————
Objetos que podem ser auditados são: tabelas, visões, sequências, pacotes, procedimentos armazenados e funções. Note que, porque alguns objetos pode ser dependente de outrosobjetos (exemplo função -> visão -> tabela), vários registros de auditoria podem ser inseridos quando esses objetos forem auditados.

Opções de auditoria objeto são definidas para todos os usuários do banco de dados e não podem ser definidos
para usuários individuais.

Quais as opções que podem ser definidas? Isto pode ser visto a partir de all_def_audit_opts.

Exemplo opções de auditoria de objetos
——————————-

SQL> connect system / manager
SQL> select * from all_def_audit_opts;

Resultado:

ALT DEL AUD COM GRA IND INS LOC REN SEL UPD REF EXE
—————————————
– / – / – / – / – / – / – / – / – / – / – / – / – / –

Estes correspondem aos seguintes opções de objecto respectivamente:

alter, audit, comment, delete, grant, index, insert, lock, rename, select, update, reference e execute .

Todas as referências, excepto e executar pode ser aplicadas a tabelas, caso contrário você receberá o erro ora-1982 “opção inválida auditoria para tabelas”.

Exemplo de auditoria em SCOTT.EMP
—————————–

SQL> connect system / manager
SQL> audit select on scott.emp by session;

Confira: Quais objetos são auditados

SQL> col owner format a7
SQL> col object_name format a7
SQL> select * from dba_obj_audit_opts
where owner=’SCOTT’ and OBJECT_NAME=’EMP’;

resultado:

OWNER OBJECT_ OBJECT_TY ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE
——- ——- ——— — — — — — — — — — — — — —
SCOTT EMP TABLE -/- -/- -/- -/- -/- -/- -/- -/- -/- S/S -/- -/- -/-

Gera algumas informações de auditoria:

SQL> connect scott / tiger
SQL> select * from emp;
=> Todas as linhas de SCOTT.EMP são mostrados
SQL> connect t / tl
SQL> select * from SCOTT.EMP;
=> ERROR at line 1:
ORA-00942: tabela ou visão não existe
SQL> connect system / manager
SQL> select * from SCOTT.EMP;
=> Todas as linhas de SCOTT.EMP são mostrados

Resultados da auditoria:
SQL> connect system/manager

SQL> col username format a8
SQL> col priv_used format 999
SQL> /

SQL> select username, priv_used, ses_actions from dba_audit_object
where obj_name=’EMP’ and owner=’SCOTT’;

Resultado:
USERNAME PRIV_USED SES_ACTIONS
——– —————————————- —————-
SYSTEM SELECT ANY TABLE ———S—
T ———F—
SCOTT ———S—

3. Auditoria no nível de privilégio
——————————

Todos os privilégios de sistema podem ser auditados.
Diferentes privilégios podem ser selecionados a partir da system_privilege_map.
Se você tentar usar um valor que não pertence à lista, você receberáo seguinte erro:

SQL> audit drop snapshot by access;
audit drop snapshot by access
*
ERROR at line 1:
ORA-00956: opção ausente ou inválido de auditoria

Exemplo: privilégios de sistema auditáveis
————————————
SQL> connect system / manager
SQL> select * from system_privilege_map;

Resultado (este resultado é dependente da versão):

Nome do privilégio
———- —————————————-
-3 ALTER SYSTEM
-4 AUDIT SYSTEM
-5 CREATE SESSION
-6 ALTER SESSION
-7 RESTRICTED SESSION
-10 CREATE TABLESPACE
-11 ALTER TABLESPACE
-12 MANAGE TABLESPACE
-13 DROP TABLESPACE
….. Não estão sendo mostrados todos os resultados ……
-167 GRANT ANY PRIVILEGE
-172 CREATE SNAPSHOT
-173 CREATE ANY SNAPSHOT
-174 ALTER ANY SNAPSHOT
-175 DROP ANY SNAPSHOT
-194 WRITEDOWN DBLOW
-195 READUP DBHIGH
-196 WRITEUP DBHIGH
-197 WRITEDOWN
-198 READUP
-199 WRITEUP

Exemplo de auditoria no CREATE TABLE de Scott e System
————————————————– –

SQL> connect system/manager
SQL> audit create table by scott, system;
=> Audit succeeded.

Isto significa que cada create table é auditado para o system e o scott uma vez por sessão.
Cada um deve criar a tabela ser auditado, em seguida, a cláusula ‘by access’ deve ser adicionada.

verificar: Quais privilégios estão auditados

SQL> col user_name format a7
SQL> col privilege format a15
SQL> connect system/manager
SQL> select * from sys.dba_priv_audit_opts;

Resultado:

USER_NA PRIVILEGE SUCCESS FAILURE
——- ————— ———- ———-
SCOTT CREATE TABLE BY ACCESS BY ACCESS
SYSTEM CREATE TABLE BY ACCESS BY ACCESS

Gere algumas informações de auditoria:

SQL> connect scott / tiger
SQL> create table t1 (c1 number);
create table t1 (c1 number)
*
ERRO na linha 1:
ORA-00955: nome já está sendo usado por um objeto existente
SQL> create table tsc (c1 number);
= Table> criado
SQL> connect t / tl
SQL> create table tsc (c1 number);
=> Tabela criada
SQL> connect system / manager
SQL> create table scott.t1 (c1 number);
=> Create table scott.t1 (c1 number)
ERRO na linha 1:
ORA-00955: nome já está sendo usado por um objeto existente
SQL> create table tsc (c1 number);
=> Tabela criada

Resultados da auditoria:

SQL> connect system/manager

SQL> col username format a8
SQL> col priv_used format 999
SQL> /

SQL> select username, priv_used, ses_actions from dba_audit_object;
Resultado:

USERNAME PRIV_USED SES_ACTIONS
——– —————————————- ——————-
SCOTT CREATE TABLE
SCOTT CREATE TABLE
SYSTEM CREATE TABLE

SQL> connect system/manager

SQL> select action, action_name, username
from dba_audit_trail ;

Resultado:

ACTION ACTION_NAME USERNAME
———- ————————— ——–
1 CREATE TABLE SCOTT
1 CREATE TABLE SCOTT
1 CREATE TABLE SYSTEM

4. Auditoria no nível de instruções
———————-
Auditoria de instruções é uma auditoria seletiva de diversos grupos de comandos sql, esse comandos podem ser comandos ddl (data definition Language) ou comandos DML (Data Manipulation Language).

Por exemplo, quando o comando ‘audit table’ é auditado, comandos create, alter e drop table também são auditados.
Quando a instrução ‘audit select table’ é auditado todas as instruções select em tabelas, visões, Snapshots .. serão auditados.

A diferença com auditoria privilégio é que no último caso, um privilégio de sistema é auditado.
Por exemplo, ” audit create table ” só vai auditar o comando create table e não comandos de alter table ou drop table.

Quando instruções semelhantes e opções de privilégios são definidas apenas um registro de auditoria é gerado.

Exemplo: instruções auditáveis
—————————–
As instruções que podem ser aditadas podem ser visteas da stmt_audit_option_map.

SQL> connect system/manager
SQL> select * from stmt_audit_option_map;

Resultado (este resultado é dependente versão):

OPTION# NAME PROPERTY
———- —————————————- ———-
3 ALTER SYSTEM 0
4 SYSTEM AUDIT 0
5 CREATE SESSION 0
6 ALTER SESSION 0
7 RESTRICTED SESSION 0
8 TABLE 0
9 CLUSTER 0
10 CREATE TABLESPACE 0
11 ALTER TABLESPACE 0
12 MANAGE TABLESPACE 0
13 DROP TABLESPACE 0
….. não estão sendo mostrados todos resultados ……
221 CONTEXT 0
234 ON COMMIT REFRESH 0
235 EXEMPT ACCESS POLICY 0
236 RESUMABLE 0
237 SELECT ANY DICTIONARY 0
238 DEBUG CONNECT SESSION 0
239 DEBUG CONNECT USER 0
240 DEBUG CONNECT ANY 0
241 DEBUG ANY PROCEDURE 0
242 DEBUG PROCEDURE 0

165 rows selected.

SQL> select * from stmt_audit_option_map
where name like ‘%TABLE%’;

Resultado:

OPTION# NAME PROPERTY
———- —————————————- ———-
8 TABLE 0
10 CREATE TABLESPACE 0
11 ALTER TABLESPACE 0
12 MANAGE TABLESPACE 0
13 DROP TABLESPACE 0
14 TABLESPACE 0
15 UNLIMITED TABLESPACE 0
40 CREATE TABLE 0
41 CREATE ANY TABLE 0
42 ALTER ANY TABLE 0
43 BACKUP ANY TABLE 0
44 DROP ANY TABLE 0
45 LOCK ANY TABLE 0
46 COMMENT ANY TABLE 0
47 SELECT ANY TABLE 0
48 INSERT ANY TABLE 0
49 UPDATE ANY TABLE 0
50 DELETE ANY TABLE 0
54 ALTER TABLE 0
57 LOCK TABLE 0
58 COMMENT TABLE 0
65 SELECT TABLE 0
66 INSERT TABLE 0
67 UPDATE TABLE 0
68 DELETE TABLE 0
69 GRANT TABLE 0

Exemplo de Auditoria em TABELAS do Scott e System
———————————————

SQL> connect system/manager
SQL> audit table by scott, system;
=> Audit succeeded.

verifique: Quais declarações estão auditadas

SQL> col user_name format a8
SQL> col proxy_name format a6
SQL> col audit_option format a9
SQL> col privilege format a15
SQL> connect system/manager
SQL> select * from dba_stmt_audit_opts;
Resultado:

USER_NAM PROXY_ AUDIT_OPT SUCCESS FAILURE
——– —— ——— ———- ———-
SYSTEM TABLE BY ACCESS BY ACCESS
SCOTT TABLE BY ACCESS BY ACCESS

Gere algumas informações de auditoria:

SQL> connect scott/tiger
SQL> create table t1 (c1 number);
create table t1 (c1 number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> drop table t1;
=> table dropped
SQL> connect system/manager
SQL> create table scott.t1(c1 number);
=> table created
SQL> drop table no;
=> *
ERROR at line 1:
ORA-00942: table or view does not exist

Resultados da auditoria:

SQL> connect system/manager

SQL> col username format a8
SQL> col priv_used format 999

SQL> select username, priv_used, ses_actions from dba_audit_object;

Resultado:

USERNAME PRIV_USED SES_ACTIONS
——– —————————————- ——————-
SCOTT CREATE TABLE
SCOTT CREATE TABLE
SYSTEM CREATE TABLE
SQL> connect system/manager

SQL> select action, action_name, username
from dba_audit_trail ;

Resultado:

ACTION ACTION_NAME USERNAME
———- ————————— ——–
1 CREATE TABLE SCOTT
12 DROP TABLE SCOTT
1 CREATE TABLE SYSTEM
12 DROP TABLE SYSTEM

SQL> select username, priv_used, ses_actions de dba_audit_object;

Resultado:

USERNAME PRIV_USED SES_ACTIONS
——– —————————————- ——————-
SCOTT CREATE TABLE
SCOTT
SYSTEM CREATE ANY TABLE
SYSTEM

Exemplo de auditoria SELECT TABLE do scott e system
————————————————– –

SQL> connect system/manager
SQL> audit select table by scott, system;
=> Audit succeeded.

Confira: Quais declarações são auditadas

SQL> col user_name format a8
SQL> col proxy_name format a6
SQL> col audit_option format a13
SQL> col privilege format a15
SQL> connect system/manager
SQL> select * from dba_stmt_audit_opts;

Resultado:

USER_NAM PROXY_ AUDIT_OPTION SUCCESS FAILURE
——– —— ————- ———- ———-
SYSTEM SELECT TABLE BY SESSION BY SESSION
SCOTT SELECT TABLE BY SESSION BY SESSION

Gere algumas informações para auditoria:

SQL> connect scott/tiger
SQL> delete from emp where ename=’KING’;

SQL> connect system/manager
SQL> insert into scott.emp (empno, ename) values (1, ‘TEST’);
SQL> select * from scott.emp;

Resultados da auditoria:

SQL> connect system/manager

SQL> col username format a8
SQL> col priv_used format 999

SQL> select username, priv_used, ses_actions from dba_audit_object;
Resultado:

USERNAME PRIV_USED SES_ACTIONS
——– —————————————- ——————-
SCOTT ———S——
SCOTT ———S——
SYSTEM ———S——
SYSTEM SELECT ANY TABLE ———S——
SYSTEM ———S——
SYSTEM ———S——
SYSTEM ———S——
SCOTT ———S——
SYSTEM ———S——
SYSTEM ———S——
SYSTEM ———S——

SQL> connect system/manager

SQL> select action, action_name, username
from dba_audit_trail ;

Resultado:

ACTION ACTION_NAME USERNAME
———- ————————— ——–
103 SESSION REC SCOTT
103 SESSION REC SCOTT
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SCOTT
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM
103 SESSION REC SYSTEM