Auditoría de tablas mediante triggers con información sobre el usuario

Uno de los usos de los triggers puede ser el de auditar los cambios que se realizan en los datos de nuestras tablas. Entre la información que podríamos considerar relevante, se encuentra la del autor de dichos cambios. El problema es que, en el contexto de ejecución de un trigger, sólo podemos tener acceso al usuario de base de datos que realiza el cambio, por ejemplo usando una consulta como esta:
SELECT user
INTO vusuario
FROM dual; 
Si nuestra aplicación gestiona sus propios usuarios (los cuales supongamos que se conectan a las aplicaciones mediante uno o varios esquemas de Oracle prefijados) no tendremos acceso al usuario real que realiza un cambio dentro del trigger puesto que estos no tienen parámetros. Hoy veremos cómo conseguir que en el contexto de un trigger podamos acceder de forma sencilla y eficiente a la información del usuario real (no el de base de datos) que está realizando un cambio en nuestra tabla y así podamos auditarlo.

Para empezar, necesitaremos desarrollar un paquete que gestione los usuarios que utilizan las aplicaciones. Debería tener al menos algo como esto:
CREATE OR REPLACE PACKAGE datos_usuario
AS

/*******************************************************************************
-- PROCEDURE set_usuarioTriggers
--
-- Descripción: establece la variable interna de usuario para usar en triggers
--
-- Entrada:    pe_usuario: usuario de la aplicación
--
------------------------------------------------------------------------------
******************************************************************************/
PROCEDURE set_usuarioTriggers(
    pe_usuario IN  VARCHAR2 -- 255
);

   
/******************************************************************************
-- FUNCTION get_usuarioTriggers
--
-- Descripción: establece la variable interna usuario para usar en triggers
--
-- Salida:      usuario actual de la aplicación
--
******************************************************************************/
FUNCTION get_usuarioTriggers RETURN VARCHAR2; -- 255



END datos_usuario;
/

CREATE OR REPLACE PACKAGE BODY datos_usuario
AS

-- Variable que contendrá el usuario para usar en triggers
-- Se debe inicializar cada vez que un usuario se conecta
-- A la base de datos desde una aplicación
usuarioTriggers    VARCHAR2(255);


PROCEDURE set_usuarioTriggers(pe_usuario  IN VARCHAR2) IS

BEGIN
    usuarioTriggers := pe_usuario;
END set_usuarioTriggers;


FUNCTION get_usuarioTriggers RETURN VARCHAR2 IS

BEGIN
    RETURN usuarioTriggers;
END get_usuarioTriggers;


END datos_usuario;
/ 
Al inicio de nuestras aplicaciones habrá que invocar la función  datos_usuario.set_usuarioTriggers pasándole como parámetro el usuario real que la está utilizando.

Para terminar, desde el trigger de auditoría tendremos acceso al valor de dicho usuario llamando a la función datos_usuario.get_usuarioTriggers. Por ejemplo, algo como esto:

CREATE OR REPLACE TRIGGER UPDATE_JOB_HISTORY
AFTER UPDATE OF job_id, department_id ON employees
FOR EACH ROW
BEGIN
    INSERT INTO job_history(
        employee_id, start_date, end_date,
        job_id, department_id, user_id, date_id)
    VALUES(
        :OLD.employee_id, :OLD.start_date, :OLD.end_date,
        OLD.job_id, :OLD.department_id, datos_usuario.get_usuarioTriggers, SYSDATE);
END;
/

Aclaraciones sobre el envío de emails con ficheros adjuntos

En relación a un artículo publicado anteriormente sobre envío de emails, me gustaría hacer las siguientes aclaraciones:

1) Si queremos adjuntar un fichero dentro de un directorio en el servidor, es obligatorio usar un directorio de Oracle previamente creado. No podemos referenciar un directorio del servidor directamente.

Por ejemplo: si tenemos que leer archivos que se encuentren en la ruta /imagenes, habrá que crear el directorio previamente de esta manera:
CREATE OR REPLACE DIRECTORY images_dir AS '/imagenes'
/

Para leer los archivos desde nuestros programas, es necesario otorgar el permiso correspondiente al usuario (siendo usuario el usuario que leerá los adjuntos):
GRANT READ ON DIRECTORY images_dir TO usuario
/ 

También hay que tener el privilegio de sistema CREATE ANY DIRECTORY para crear un directorio de Oracle.

2) Por otra parte, el directorio del cual leer (/imagenes en este caso) debe existir en el servidor de base de datos y tener al menos el permiso de lectura correspondientes, igual que los archivos que contenga. De no ser así, el procedimiento de envío de emails no será capaz de leerlos. Cómo se hace esto ya depende del sistema operativo del servidor.

3) Si inicialmente no tenéis acceso directo al servidor pero queréis hacer pruebas, es posible crear con PLSQL un fichero con información trivial para utilizarlo desde tus programas. Algo así como:
-- Añade permiso de escritura sobre el directorio Oracle, si no lo tenía
GRANT WRITE ON DIRECTORY images_dir TO usuario
/

-- Crea un fichero de prueba con el texto Texto de prueba
DECLARE
    l_output UTL_FILE.file_type;
    v_buffer VARCHAR2(255);

BEGIN
    -- define output directory
    l_output := UTL_FILE.fopen('IMAGES_DIR', 'prueba.txt','a');
    UTL_FILE.PUT_LINE(l_output,'Texto de prueba');
    UTL_FILE.fflush(l_output);
    UTL_FILE.fclose(l_output);

    -- Con esto me aseguro de que el fichero ya lo tengo
    l_output := UTL_FILE.fopen('IMAGES_DIR', 'prueba.txt','r');
    UTL_FILE.GET_LINE(l_output,v_buffer);
    UTL_FILE.fclose(l_output);
  
    DBMS_OUTPUT.PUT_LINE(v_buffer);
END;
/ 

Ahora ya es posible realizar una prueba con adjuntos:
DECLARE
    v_ret NUMBER;
    v_error VARCHAR2(1024);

    v_attachs        Pkg_Emails.t_attachments;
    v_replies        UTL_SMTP.replies;

BEGIN
    v_attachs(0).mimetype := 'text/plain';
    v_attachs(0).filename := 'prueba.txt';
    v_attachs(0).DIRECTORY:='IMAGES_DIR';

    v_ret := Pkg_Emails.send_mail_with_attachments(
        pe_sender            => 'MiEmail@MiDominio',
        pe_recipients        => 'TuEmail@TuDomicio',
        pe_subject            => 'Asunto de la prueba con adjunto',
        pe_message            => 'Texto de la prueba con adjunto',
        pe_attachments        => v_attachs,
        ps_replies            => v_replies);

    -- Ha habido algún error
    IF v_replies.COUNT > 0 THEN
        DBMS_OUTPUT.PUT_LINE(v_replies(0).text);
    END IF;
END;
/ 

Un detalle que puede parecer trivial pero nos puede dar muchos quebraderos de cabeza si no lo hacemos bien: si hemos creado el directorio Oracle usando un identificador sin usar comillas dobles, es obligatorio referirnos al mismo, dentro de nuestros programas, en mayúsculas. Por tanto, no es casual que se hayan usado mayúsculas en estas instrucciones PLSQL anteriores:
l_output := UTL_FILE.fopen('IMAGES_DIR', 'prueba.txt','a');
v_attachs(0).DIRECTORY:='IMAGES_DIR';