5. SOBRE LA AUDITORIA DE LA BASE DE DATOS DE ORACLE

 5.1 BORRADO DE LA AUDITORIA EN ORACLE 11G

Una de las opciones que nos ofrece la base de datos es la de auditoría. En Oracle 11g viene activada por defecto al realizar la instalación, de modo que no necesita configurarla y reiniciar la instancia de igual manera si en la base de datos la auditoria no es necesaria porque la aplicación asociada tiene su propia auditoria o por algún otro motivo es necesario eliminar o desactivar la misma.

- Para validar que la auditoria esta active se ejecuta lo siguiente con el usuario SYS  o un usuario con rol de DBA

SQL> conn / as sysdba
Connected.
SQL> show parameter audit_trail
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB

- La auditoría está accesible por medio de la vista DBA_AUDIT_TRAIL, o si lo preferimos, por la tabla SYS.AUD$. Esta tabla almacenará toda la auditoría de Oracle, ello implica que crecerá con el tiempo, y dependiendo del número de usuarios y lo que se quiera auditar, puede crecer hasta alcanzar Gb de datos. El crecimiento de esta tabla, si no se revisa y se limpia periódicamente puede llevar a problemas mayores, para evitarnos estos futuros problemas debemos realizar una serie de trabajos tras instalar la base de datos, para ello Oracle nos proporciona un nuevo paquete DBMS_AUDIT_MGMT.

Primero de todo, hay que comprobar el espacio que ocupa la auditoría:

SELECT
    tablespace_name,
    bytes/1024/1024 as mb
FROM dba_segments
WHERE segment_name = 'AUD$'

TABLESPACE_NAME                        MB
------------------------------ ----------
SYSTEM                              .0625

- Por defecto la tabla se almacena en el tablespace de SYSTEM, lo cual no es muy recomendable si va a crecer considerablemente. En Oracle 11g disponemos de un paquete que nos permite moverla a otro tablespace, por ejemplo SYSAUX:

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
    audit_trail_location_value => 'SYSAUX');
END;

Si la tabla ocupa varios cientos de Mb, o incluso Gb, tardará en moverse bastante y afectará al rendimiento de la base de datos, pudiendo incluso impedir que se conecten los usuarios, por ello es recomendable realizar esta operación en horas de poca carga de trabajo.

Una vez movida la tabla, querremos reducir su tamaño y evitar que ocupe demasiado. A menos que necesitemos la auditoría de meses o años, lo más común es reducirla a 30 días, para ello ejecutaremos el siguiente comando:

BEGIN
    DBMS_AUDIT_MGMT.INIT_CLEANUP
    (
        AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
        DEFAULT_CLEANUP_INTERVAL => 30
    );
END;


- De esta forma limpiamos manualmente la auditoría, ahora bien, si queremos que se realice automáticamente deberemos configurarlo.

BEGIN
    DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP
    (
        audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
        last_archive_time => SYSTIMESTAMP - 30
    );
END;


- Con este comando configuramos cuál es la última fecha de auditoría que queremos conservar, en este caso los últimos 30 días.
Podemos ver los parámetros de la auditoría con las siguientes consultas:

SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;


Con la primera veremos la última fecha de archivado de auditoría, con la segunda los parámetros.

- Una vez configurada, podremos programar un job que realiza el borrado periódico, esto se realiza con el siguiente comando:

BEGIN
    DBMS_AUDIT_MGMT.CREATE_PURGE_JOB
    (
        audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
        audit_trail_purge_interval => 24,
        audit_trail_purge_name     => 'LIMPIAR_AUDIT',
        use_last_arch_timestamp    => TRUE
    );
END;


- Además, deberemos crear un job que mueva la última fecha disponible adelante:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    JOB_NAME        => 'MOVER_TIMESTAMP'
   ,START_DATE      => TO_TIMESTAMP_TZ('04/04/2013 06:00:00 +1:00','DD/MM/YYYY HH24:MI:SS TZH:TZM')
   ,REPEAT_INTERVAL => 'FREQ=DAILY;INTERVAL=1'
   ,END_DATE        => NULL
   ,JOB_CLASS       => 'DEFAULT_JOB_CLASS'
   ,JOB_TYPE        => 'PLSQL_BLOCK'
   ,JOB_ACTION      => 'BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    AUDIT_TRAIL_TYPE  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    LAST_ARCHIVE_TIME => SYSTIMESTAMP-30
  );
END;'
   ,COMMENTS        => NULL
  );

  DBMS_SCHEDULER.ENABLE( NAME => 'MOVER_TIMESTAMP');
END;


Para comprobar los dos jobs y ver que se están ejecutando correctamente:

SELECT *
FROM DBA_SCHEDULER_JOBS
WHERE job_name IN ('LIMPIAR_AUDIT','MOVER_TIMESTAMP');


- Finalmente, una vez limpiada la auditoría, podemos querer reducir el espacio que ocupa:

ALTER TABLE AUD$ ENABLE ROW MOVEMENT;
ALTER TABLE AUD$ SHRINK SPACE CASCADE;
ALTER TABLE AUD$ DISABLE ROW MOVEMENT;


De esta forma recuperamos el espacio no utilizado para el tablespace. 

5.2 MANTENIMIENTO DE LAS TABLAS DE AUDITORIA SYS.AUD$

Vamos a ver algo tan básico como es el mantenimiento de las tablas de auditoria. Los registros de auditoria de la base de datos es una de esas cosas que tienden a crecer indiscriminadamente llenándonos los tablespaces del sistema sin que nos demos cuenta. Hoy vamos a ver como limitar estos registros a los de los últimos 100 días (algo más de 3 meses).

En las versiones anteriores (9i, 10g) el mantenimiento de las tablas de auditoria era un poco “por tu cuenta y riesgo”, había documentación de cómo hacerlo pero decía que no era soportada ( por ejemplo la nota Note: 1019377.6 Script to move SYS.AUD$ table out of SYSTEM tablespace ).

Afortunadamente en la versión 11g Oracle ha creado el paquete DBMS_AUDIT_MGMT facilitándonos la labor.

-       Lo primero que tenemos que ver es donde se encuentran las tablas de auditoria, para ello usaremos la consulta.

SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                                    SYSTEM
FGA_LOG$                         SYSTEM

-       O con la llamada al paquete

column parameter_name format a30
column parameter_value format a20
SELECT * FROM dba_audit_mgmt_config_params;

PARAMETER_NAME            PARAMETER_VALUE      AUDIT_TRAIL
------------------------ ------------------ -----------------------------------------------------
DB AUDIT TABLESPACE              TS_AUD         STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE              TS_AUD         FGA AUDIT TRAIL
AUDIT FILE MAX SIZE                                  10000            OS AUDIT TRAIL
AUDIT FILE MAX SIZE                                  10000            XML AUDIT TRAIL
AUDIT FILE MAX AGE                                  5                      OS AUDIT TRAIL
AUDIT FILE MAX AGE                                  5                      XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE                10000            STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE                10000            FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE                      1000             OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE                      1000             XML AUDIT TRAIL

10 filas seleccionadas.

-       Por defecto, estas tablas estarán en el tablespace SYSTEM, es muy importante el mover estas tablas fuera de este tablespace si vamos a trabajar sobre ellas, ya que, sino Oracle usará el tablespace SYSAUX, algo que no queremos que pase bajo ningún concepto.

Lo primero que vamos a hacer es crear un tablespace para la auditoria, así que, vamos a ver el tamaño que necesitamos y a crear un tablespace con suficiente tamaño:

SQL> select sum(bytes)/1024/1024 Mb
from dba_segments
where segment_name in ('AUD$', 'FGA_LOG$');

----------------
200,0625

-       Creamos el tablespace que vamos a usar para la auditoria

SQL> create tablespace TS_AUD datafile '\oracle\oradata\instancia\ts_audit01.dbf' size 250M;

- Una vez tengamos el tablespace creado, podemos usar la llamada DBMS_AUDIT_MGMT.set_audit_trail_location para mover las tablas actuales al nuevo tablespace

BEGIN

 DBMS_AUDIT_MGMT.set_audit_trail_location(
 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'TS_AUD');
END;

BEGIN
 DBMS_AUDIT_MGMT.set_audit_trail_location(
 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, audit_trail_location_value => 'TS_AUD');
END;

-       Con estos pasos, ya tenemos las tablas de auditoria en un tablespace especifico llamado TS_AUD, es en este momento cuando podemos comenzar con las tareas de mantenimiento de los registros de auditoría.

-       Como decíamos al principio, vamos a configurar la base de datos para que se borren los registros anteriores a 100 días.  

Lo primero que tendremos que hacer es inicializar el paquete con DBMS_AUDIT_MGMT.init_cleanup. En nuestro caso lo lanzaremos sobre AUDIT_TRAIL_AUD_STD Ya que lo que queremos limpiar son los registros de las tablas de auditoria de la base de datos, si quisiéramos limpiar otro (por ejemplo los del S.O seguiríamos la tabla Audit Trail Types)

BEGIN
 DBMS_AUDIT_MGMT.init_cleanup(
 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
 default_cleanup_interval => 24 /* horas*/);
END;

-       Tras esto, con la propiedad SET_LAST_ARCHIVE_TIMESTAMP indicamos cual es la fecha del último registro que queremos guardar.(en nuestro caso borraremos todo lo anterior a 100 días)

BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-100 /*100 días*/);
END;

-       Antes de ejecutar el trabajo, vamos a ver cuál es el registro más antiguo:

SQL> select min(ntimestamp#) from sys.aud$;
-------------------------------------------
17/10/13 00:00:50,119000

-       Ejecutamos el purgado con la llamada

BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   use_last_arch_timestamp => TRUE);
END;

-       Tras ejecutar esto, el último registro debería de corresponderse con la fecha SYSDATE-100.

Si lo comprobamos tendremos que:

SQL> select to_date(SYSTIMESTAMP-100) from dual
--------------
11/11/13

SQL> select min(ntimestamp#) from sys.aud$;
------------------------------------------
11/11/13 16:46:08,889000

Con lo que, efectivamente, habremos borrado todos los registros anteriores a 100 días.

-       A partir de ahora, podemos, o bien llevar a cabo los borrados de manera puntual con la llamada anterior, o planificarlos con en un job de purgado con la llamada.

BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_purge_interval => 24 /* horas */,
audit_trail_purge_name => 'PURGADO_DE_TABLAS_AUDITORIA',

use_last_arch_timestamp=> TRUE);
END;

-       Como siempre, podemos encontrar mas información en soporte Oracle en las notas :

Documentación del paquete DBMS_AUDIT_MGMT

Note 72460.1 Moving AUD$ to Another Tablespace and Adding Triggers to AUD$
Note: 1019377.6 Script to move SYS.AUD$ table out of SYSTEM tablespace
Note: 166301.1 How to Reorganize SYS.AUD$ Table
Note: 731908.1 New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information
Note: 73408.1 How to Truncate, Delete, or Purge Rows from the Audit Trail Table SYS.AUD$

5.3 ALGUNAS VALIDACIONES SOBRE LA AUDITORIA DE ORACLE

Dependiendo del tipo de auditoría que queramos consultar utilizaremos una u otra consulta SQL.

Para el caso de la auditoría de inicio de sesión utilizaremos la siguiente consulta SQL:

select OS_Username Usuario_SO,

  Username Usuario_Oracle, Terminal ID_Terminal,

  DECODE (Returncode, '0', 'Conectado', '1005', 'Fallo - Null',

  1017, 'Fallo', Returncode) Tipo_Suceso,

  TO_CHAR(Timestamp, 'DD-MM-YY HH24:MI:SS') Hora_Inicio_Sesion,

  TO_CHAR(Logoff_Time, 'DD-MM-YY HH24:MI:SS') Hora_Fin_Sesion

from DBA_AUDIT_SESSION;

Para el caso de la auditoría de acción utilizaremos la siguiente consulta SQL:

select OS_Username Usuario_SO,

  Username Usuario_Oracle, Terminal ID_Terminal,

  Owner Propietario_Objeto,

  Obj_Name Nombre_Objeto,

  Action_Name Accion,

  DECODE (Returncode, '0', 'Realizado', 'Returncode') Tipo_Suceso,

  TO_CHAR (Timestamp,  'DD-MM-YY HH24:MI:SS') Hora

from DBA_AUDIT_OBJECT;