Trazas en SYS.AUD$

Actualizado el viernes 10 de septiembre de 2010.

¿Recuerdas cómo en junio aprendíamos a activar las trazas de sesión en Oracle?

Esas trazas las almacenamos en la tabla del usuario SYS: SYS.AUD$

Pues hoy crearemos un par de vistas para analizar cómodamente las trazas.

A la primera vista le he llamado _A_USUARIOS.
Pongo el guion bajo por delante sólo para que sea la primera vista en aparecer.

Esta vista tiene tres campos en mi ejemplo particular:

• per_ej es el código de usuario Windows
• per_nombre el nombre
• per_apellido el apellido.

Esto es necesario porque en la tabla de las trazas se registrará el usuario Windows y queremos saber los nombres y apellidos del que accede.


Ahora creamos la vista con miga. Se llamará _A_AUDIT.
Servirá para hacer una selección de los campos de la tabla y para añadir los nombres y apellidos de los que acceden o tratan de acceder.

ntimestamp# es la fecha y hora del registro. Cuidado, es la hora UNC. No es la hora local. Más abajo explico cómo solucionar esto.

spare1 tiene el usuario Windows del usuario que accede.

userid es el usuario Oracle al que accede.

host indica el dominio windows y nombre de máquina del PC que accede.

action# es un código que indica la acción que realiza.

returncode es un código que indica el resultado del intento de acceso.

logoff$time indica la fecha y hora de fin de sesión.

La vista que muestra los accesos sería de esta forma:

Ahora bien, ya hemos dicho que la hora que aparece en ntimesstam# es la hora UNC, no la hora local.

Si hacemos "select current_timestamp from dual;" me da la hora actual bien.

Si hago "select systimestamp from dual;" también da la misma hora.

Con "Select dbtimezone from dual;" en mi caso obtengo +2 (por el horario de verano).

Lo que tenemos que hacer es modificar ligeramente la vista para que nos transforme la hora UTC a hora local según el dbtimezone que tengamos.

Esto se hace poniendo en lugar de ntimestam#:

"CAST ((FROM_TZ(ntimestamp#,'00:00') AT LOCAL) AS DATE)"

Para un copy-paste rápido así queda la vista con hora local:

CREATE OR REPLACE FORCE VIEW SYS."_A_AUDIT" (ntimestamp#,

                                             per_nombre,

                                             per_apellidos,

                                             spare1,

                                             userid,

                                             userhost,

                                             action#,

                                             returncode,

                                             logoff$time

                                            )

AS

   SELECT CAST ((FROM_TZ(ntimestamp#,'00:00') AT LOCAL) AS DATE), per_nombre, per_apellidos, spare1, userid, userhost,

          action#, returncode, logoff$time

     FROM aud$, "_A_USUARIOS"

    WHERE "_A_USUARIOS".per_ej = aud$.spare1;

Otra opción para lo mismo sería utilizar una vista de trazas diferente a SYS.AUD$:

La vista que hay en el usuario Oracle SYS llamada DBA_AUDIT_TRAIL hace una select de AUD$ y otras tablas. En esta vista (DBA_AUDIT_TRAIL) la hora que aparece es hora local y no hora UNC.

Saludos.