Trigger de auditoría

Hoy añadiremos un trigger de auditoría en nuestra BBDD a nivel de logon.
El objetivo es que los usuarios de desarrollo no puedan logearse en la BBDD de producción, con programas como el TOAD, que pueden hacer mucha mella en el rendimiento, además añadiremos una tabla en la que iremos registrando los usuarios que se conectan a la BBDD.

Esta auditoría nos sirve para ver las DDL y las DDL que están ejecutando los usuarios sobre la BBDD (para ello deberemos activar el parámetro audit_file_dest), sino únicamente para ver quién se conecta.

Primero deberemos crear una tabla en la que se recojan los datos de los usuarios, esta tabla la tendremos que crear en función de los datos que queramos recoger de los usuarios que se conectan. Utilizaremos el paquete SYS_CONTEXT (en versiones antiguas se utiliza en USERENV('SESSIONID')), que recoge datos del usuario actual

create table login_info (fecha date,  usuario_db varchar(50), usuario_so varchar(50), ip varchar(15),  maquina varchar(50), aplicacion varchar2(100));;
y luego utilizaremos este insert dentro del código del trigger:
insert into login_info values(sysdate,SYS_CONTEXT('USERENV','SESSION_USER'),   SYS_CONTEXT('USERENV','os_user'),SYS_CONTEXT('USERENV','IP_ADDRESS'),     SYS_CONTEXT('USERENV','HOST'), SYS_CONTEXT('USERENV','MODULE') );

Aunque podemos recoger muchos datos mas, ver información de la función SYS_CONTEXT de la versión de tu BBDD pues a lo largo de las versiones el paquete ha ido introduciendo y quitando funciones.
Por ejemplo para la versión 10.2 tenemos estas:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm

por otra parte para detectar los usuarios del TOAD o similar
los podemos detectar con la condición

IF UPPER(SYS_CONTEXT('USERENV','MODULE') ) LIKE '%TOAD%' OR UPPER(SYS_CONTEXT('USERENV','MODULE') ) LIKE '%T.O.A.D%'

entonces lanzaríamos un error que quedaría registrado en el alert (aunque también podríamos crear otra tabla para registrar estos eventos):

THEN
RAISE_APPLICATION_ERROR(-20000, 'No está permitido el toad"".');

con este sistema podríamos detectar usuarios de otros programas como SQL_Deverloper , SQL Navigator o el que nos interese, aunque debemos saber que con simplemente, cambiar el nombre del ejecutable de, por ejemplo, toad.exe a otro nombre cualquiera se podrían saltar nuestro bloqueos :)

En resumen:

Creamos la tabla para albergar los datos:
create table login_info (fecha date, usuario_db varchar(50), usuario_so varchar(50), ip varchar(15), maquina varchar(50), aplicacion varchar2(100));
Creamos el disparador que insertará los datos en la tabla:

CREATE OR REPLACE TRIGGER audit_mas_toad
AFTER LOGON ON DATABASE
DECLARE
BEGIN

IF UPPER(SYS_CONTEXT('USERENV','MODULE') ) LIKE '%TOAD%' OR UPPER(SYS_CONTEXT('USERENV','MODULE') ) LIKE '%T.O.A.D%' OR --toad
UPPER(SYS_CONTEXT('USERENV','MODULE') ) LIKE '%SQLNAV%'   -- SQL Navigator
THEN
RAISE_APPLICATION_ERROR(-20001, 'No está permitido el toad en la BBDD de producción"".');
else
insert into login_info values(sysdate,SYS_CONTEXT('USERENV','SESSION_USER'),   
SYS_CONTEXT('USERENV','os_user'),SYS_CONTEXT('USERENV','IP_ADDRESS'),     
SYS_CONTEXT('USERENV','HOST'), SYS_CONTEXT('USERENV','MODULE') );
END IF;
END;
/

No os olvidéis de poner un job que periódicamente purgue los datos de la tabla.