Auditando novas conexões

setembro 21, 2011 - Alta disponibilidade, Oracle Database, Segurança - Tagged: audit_trail, auditoria, conexões, segurança, trigger - no comments

Existem duas maneiras de auditar conexões ao banco de dados.

Uma seria via trigger, que nos servidir muito bem, mas não serve para descobrir se você consguiu ou não conectar, ou seja você conseguiria cadastrar todas as conexões em seu banco, mas se alguem não conseguiu efetuar a conexão, por senha ou por qualquer outro motivo não saberemos.

A outra forma seria via auditoria do banco, a famosa “audit_trail”, mas se você habilitar a auditoria você pode auditar muitas coisa desnecessária e com isso multiplicar o tamanho de seu banco de dados.

Vou mostrar para vocês uma forma bem simplificada de auditar as conexões no banco de dados sem auditar “lixo”. Primeiramente teremos de verificar o parâmetro de auditoria no banco.

SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
audit_trail                          string      DB

Este parâmetro deverá ser db ou db_extended, caso estiver como xml ou xml_extended até funcionará mas você não poderá verificar via banco conforme faremos neste post.

Para alterar este parâmetro você terá de reiniciar seu banco para que as alterações tenham efeito.


SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
audit_trail                          string      NONE
SQL> alter system set audit_trail='DB' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  451964928 bytes
Fixed Size                  1344812 bytes
Variable Size             281021140 bytes
Database Buffers          163577856 bytes
Redo Buffers                6021120 bytes
Database mounted.
Database opened.
SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
audit_trail                          string      DB

Agora vamos auditar quem conecta ou quem não consegue conectar.

Para efetuar tal procedimento resolvi criar um novo owner para efetuar tal processo.

SQL> create user lana_audit identified by lana;

User created.

SQL> grant connect to lana_audit;

Grant succeeded.

Agora vamos auditalo.

SQL> audit create session by lana_audit;

Audit succeeded.

Vamos conectar com este owner.

SQL> conn lana_audit/lana
Connected.
SQL> conn lana_audit/lana
Connected.
SQL> conn lana_audit/lana
Connected.
SQL> conn lana_audit/lana
Connected.
SQL> conn / as sysdba
Connected.
SQL>

Agora vamos rodar o select para ver estas conexões.

SQL> col OS_USERNAME for a30
SQL> col USERHOST for a30
SQL> select to_char(timestamp,'mm/dd/yy hh24:mi') ts,
  2  os_username, userhost, returncode
  3  from dba_audit_trail
  4  where username = 'LANA_AUDIT'
  5  order by timestamp;

TS             OS_USERNAME               USERHOST                  RETURNCODE
-------------- ------------------------- ------------------------- ----------
09/20/11 18:02 oracle                    producao                           0
09/20/11 18:02 oracle                    producao                           0
09/20/11 18:02 oracle                    producao                           0
09/20/11 18:02 oracle                    producao                           0

4 rows selected.

Todos os resultados alem do “RETURNCODE” 0 é porque ocorreu algum erro ou falha de conexão.

SQL> conn lana_audit/lana1
ERROR:
ORA-01017: senha/nome do usuário inválido; log-on negado


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> alter user lana_audit account lock;

User altered.

SQL> conn lana_audit/lana1
ERROR:
ORA-28000: a conta está bloqueada


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> select to_char(timestamp,'mm/dd/yy hh24:mi') ts,
  2  os_username, userhost, returncode
  3  from dba_audit_trail
  4  where username = 'LANA_AUDIT'
  5  and RETURNCODE<>0
  6  order by timestamp;

TS             OS_USERNAME               USERHOST                 RETURNCODE
-------------- ------------------------- ------------------------ ----------
09/20/11 18:11 oracle                    producao                       1017
09/20/11 18:11 oracle                    producao                      28000

Com isso podemos identificar quem esta errando a senha de seu sistema.
Returncode 1017 = senha inválida “ORA-01017”
Returncode 28000 = Usuário inválido “ORA-28000”