Auditoría de bases de datos

Índice

En este post se recogen algunos ejemplos prácticos de auditoría en bases de datos instaladas en distintos sistemas gestores tanto relacionales como no relacionales.

Activa desde SQL*Plus la auditoría de los intentos de acceso no exitosos al sistema. Comprueba su funcionamiento.

Para activar cualquier tipo de auditoría en la base de datos hay que comprobar primero que la auditoría está habilitada.

SQL> show parameter audit;

NAME				     TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
audit_file_dest 		     string
/opt/oracle/admin/ORCLCDB/adum
p
audit_syslog_level		     string

audit_sys_operations		     boolean
TRUE
audit_trail			     string
DB

NAME				     TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
unified_audit_common_systemlog	     string

unified_audit_systemlog 	     string

El parámetro audit_trail tiene el valor DB, lo que significa que en esta instancia de Oracle, la auditoría está habilitada.

Con la orden AUDIT se puede activar la auditoría para los intentos de inicio de sesión que no tiene éxito.

SQL> AUDIT CREATE SESSION WHENEVER NOT SUCCESSFUL;

Auditoría terminada correctamente.

En la vista del diccionario de datos DBA_PRIV_AUDIT_OPTS se almacena la información sobre las auditorías activas en la base de datos.

SQL> SELECT * FROM DBA_PRIV_AUDIT_OPTS;

USER_NAME
--------------------------------------------------------------------------------
PROXY_NAME
--------------------------------------------------------------------------------
PRIVILEGE
--------------------------------------------------------------------------------
SUCCESS 		       FAILURE
------------------------------ ------------------------------


CREATE SESSION
NOT SET 		       BY ACCESS

Para comprobar el funcionamiento de la auditoría hay que generar un inicio de sesión fallido.

SQL> connect scott/scott
ERROR:
ORA-01017: invalid username/password; logon denied


Advertencia: ¡Ya no está conectado a ORACLE!

La información sobre las acciones auditadas se encuentra en la vista del diccionario de datos DBA_AUDIT_SESSION.

SQL> SELECT USERNAME, TIMESTAMP, ACTION_NAME FROM DBA_AUDIT_SESSION;

USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
ACTION_NAME
--------------------------------------------------------------------------------
SCOTT
16/02/25
LOGON

Realiza un procedimiento en PL/SQL que te muestre los accesos fallidos junto con el motivo de los mismos, transformando el código de error almacenado en un mensaje de texto comprensible. Contempla todos los motivos posibles para que un acceso sea fallido.

SQL> CREATE OR REPLACE FUNCTION BuscarCodigo (p_RETURNCODE 
DBA_AUDIT_SESSION.RETURNCODE%TYPE)
RETURN VARCHAR2
AS
    v_error VARCHAR2(50);
BEGIN
    CASE p_RETURNCODE
        WHEN 01017 THEN
            v_error := 'Usuario o contraseña incorrectos';
        WHEN 28000 THEN
            v_error := 'Cuenta bloqueada';
        WHEN 28001 THEN
            v_error := 'Contraseña caducada';
        WHEN 28009 THEN
            v_error := 'La conexión como SYS tiene que ser con SYSDBA o SYSOPER';
        WHEN 28011 THEN
            v_error := 'Contraseña caducada';
        WHEN 01005 THEN
            v_error := 'No se ha indicado ninguna contraseña';
        WHEN 01034 THEN
            v_error := 'La instancia de Oracle no se está ejecutando';
        WHEN 27101 THEN
            v_error := 'No existe el área de memoria compartida';
    END CASE;
    RETURN v_error;
END;
/

Función creada.

SQL> CREATE OR REPLACE PROCEDURE AccesosFallidos
AS
    CURSOR c_accesos
    IS
    SELECT USERNAME, TIMESTAMP, ACTION_NAME, RETURNCODE
    FROM DBA_AUDIT_SESSION
    WHERE ACTION_NAME = 'LOGON' 
    AND RETURNCODE != 0;
    v_error VARCHAR2(50);
BEGIN
    DBMS_OUTPUT.PUT_LINE(RPAD('USUARIO',10,' ')||RPAD('FECHA/HORA',20,' ')||RPAD('ERROR',50,' '));
    DBMS_OUTPUT.PUT_LINE(RPAD('-',80,'-'));
    FOR v_acceso IN c_accesos LOOP
        v_error:=BuscarCodigo(v_acceso.RETURNCODE);
        DBMS_OUTPUT.PUT_LINE(RPAD(v_acceso.USERNAME,10,' ')||RPAD(TO_CHAR(v_acceso.TIMESTAMP,'DD/MM/YY HH24:MI'),20,' ')||RPAD(v_error, 50,' '));
    END LOOP;
END;
/

Procedimiento creado.

Info

Aunque para comprobar el correcto funcionamiento del procedimiento se han replicado la mayor parte de los errores de acceso que se recogen en la función, algunos de ellos como el error ORA-1005, el error ORA-1034, el error ORA-28009 y el error ORA-27101 no se registran en la vista DBA_AUDIT_SESSION del diccionario de datos de Oracle. En el caso de los errores ORA-1034 y ORA-27101 esto se explica porque se producen al intentar iniciar sesión cuando la instancia de Oracle no está en funcionamiento y, por tanto, no puede registrar este intento de acceso fallido en la vista de auditoría.

debian@bbdd:~$ sqlplus

SQL*Plus: Release 21.0.0.0.0 - Production on Mar Feb 18 18:04:53 2025
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Introduzca el nombre de usuario: scott
Introduzca la contraseña: 

ERROR:
ORA-01005: se ha proporcionado una contraseña nula; conexión denegada

debian@bbdd:~$ sqlplus scott/tiger

SQL*Plus: Release 21.0.0.0.0 - Production on Mar Feb 18 18:05:49 2025
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4775
Additional information: -718068697
Identificador de Proceso: 0
Identificador de Sesión: 0 Número de Serie: 0

debian@bbdd:~$ sqlplus sys/sys

SQL*Plus: Release 21.0.0.0.0 - Production on Mar Feb 18 18:06:58 2025
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

SQL> EXEC ACCESOSFALLIDOS;
USUARIO   FECHA/HORA	        ERROR
--------------------------------------------------------------------------------
SCOTT     16/02/25 16:43      Usuario o contraseña incorrectos
SCOTT	    16/02/25 17:23      Cuenta bloqueada
SCOTT	    16/02/25 17:23      Cuenta bloqueada

Procedimiento PL/SQL terminado correctamente.

Activa la auditoría de las operaciones DML realizadas por el usuario Prueba en tablas de su esquema. Comprueba su funcionamiento.

Se activa la auditoría para el usuario prueba cuando haga una consulta, actualización, inserción de datos o borrado de filas de una tabla.

SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY PRUEBA BY ACCESS;

Auditoría terminada correctamente.

Para comprobar el funcionamiento de la auditoria, primero el usuario prueba realiza las acciones auditadas.

SQL> CONNECT PRUEBA/PRUEBA
SQL> INSERT INTO PRUEBA1 VALUES (1);

1 fila creada.

SQL> INSERT INTO PRUEBA1 VALUES (2);

1 fila creada.

SQL> INSERT INTO PRUEBA1 VALUES (3);

1 fila creada.

SQL> UPDATE PRUEBA1 SET ID=4 WHERE ID=3;

1 fila actualizada.

SQL> SELECT * FROM PRUEBA1;

	ID
----------
	 1
	 2
	 4

SQL> DELETE FROM PRUEBA1 WHERE ID=2;

1 fila suprimida.

La vista del diccionario de datos de Oracle donde se almacena la información de estas auditorías es DBA_AUDIT_TRAIL.

SQL> SELECT USERNAME,TIMESTAMP,OBJ_NAME,ACTION_NAME
  2  FROM   DBA_AUDIT_TRAIL
  3  WHERE OWNER = 'PRUEBA';

USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
SELECT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
SELECT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
SELECT

USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
UPDATE


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
DELETE


15 filas seleccionadas.

Realiza una auditoría de grano fino para almacenar información sobre la inserción de empleados con comisión en la tabla emp de Scott.

Para hacer auditorías de grano fino o Fine Grain Auditing (FGA) en Oracle se usa el paquete DBMS_FGA. Este paquete cuenta con varios métodos para interactuar con él. Por ejemplo, con DBMS_FGA.ADD_POLICY se añade una política de auditoría. En esta política se pueden especificar las condiciones específicas que se quieren auditar. En este caso, se indica el nombre del esquema de la tabla cuyas inserciones se quieren auditar, el nombre de la tabla, el nombre de la política que se está creando, la condición que se quiere auditar (en este caso, que el campo comisión de la tabla emp de Scott no sea nulo), la columna que se audita y el tipo de operación que hay que auditar. Además, hay otro método del paquete que permite habilitar una política de auditoría: DBMS_FGA.ENABLE_POLICY.

SQL> BEGIN
  2     DBMS_FGA.ADD_POLICY(
  3        object_schema      => 'SCOTT',
  4        object_name        => 'EMP',
  5        policy_name        => 'COMISIONES_NO_NULAS',
  6        audit_condition    => 'COMM IS NOT NULL',
  7        audit_column       => 'COMM',
  8        statement_types    => 'INSERT'
  9     );
 10  END;
 11  /

Procedimiento PL/SQL terminado correctamente.

SQL> BEGIN
  2     DBMS_FGA.ENABLE_POLICY(
  3        object_schema      => 'SCOTT',
  4        object_name        => 'EMP',
  5        policy_name        => 'COMISIONES_NO_NULAS'
  6     );
  7  END;
  8  /

Procedimiento PL/SQL terminado correctamente.

Las políticas de auditoría creadas en la base de datos se almacenan en la vista del diccionario de datos DBA_AUDIT_POLICIES.

SQL> SELECT OBJECT_SCHEMA,OBJECT_NAME,POLICY_NAME FROM DBA_AUDIT_POLICIES;

OBJECT_SCHEMA
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
POLICY_NAME
--------------------------------------------------------------------------------
SCOTT
EMP
COMISIONES_NO_NULAS

Para comprobar el funcionamiento de la auditoría de grano fino, el usuario SCOTT añade dos nuevas filas a la tabla EMP: una de ellas con el registro de un nuevo empleado que cobra comisión y otra con el campo COMM vacío.

SQL> INSERT INTO SCOTT.EMP VALUES (7901,'LOLI','SALESMAN',7698,TO_DATE('08/09/81','DD/MM/YY'),1200,20,30);

1 fila creada.

SQL> INSERT INTO SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7903,'JUAN','CLERK',7698,TO_DATE('03/09/81','DD/MM/YY'),1050,30);

1 fila creada.

La información generada por esta auditoría de grano fino se almacena en la vista del diccionario de datos DBA_FGA_AUDIT_TRAIL. En esta consulta se ve cómo de las dos sentencias INSERT ejecutadas anteriormente, sólo se ha almacenado en la vista aquella en la que se han insertado datos en el campo COMM.

SQL> SELECT DB_USER, OBJECT_NAME, SQL_TEXT, TIMESTAMP
  2  FROM DBA_FGA_AUDIT_TRAIL
  3  WHERE POLICY_NAME = 'COMISIONES_NO_NULAS';

DB_USER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
SCOTT
EMP
INSERT INTO SCOTT.EMP VALUES (7901,'LOLI','SALESMAN',7698,TO_DATE('08/09/81','DD
/MM/YY'),1200,20,30)
16/02/25

Explica la diferencia entre auditar una operación by access o by session ilustrándolo con ejemplos.

La diferencia entre auditar una operación by access o by session reside en el nivel de precisión de la información que se almacena. Cuando se audita una operación by access, se guarda una entrada en la tabla de la auditoría por cada vez que se ejecuta la acción. Por ejemplo, tomando como referencia el ejercicio 3, con la opción by access, se crea una entrada en la tabla por cada operación DML que realiza el usuario prueba. En cambio, cuando una operación se audita by session, sólo se genera una entrada en la tabla si el usuario ha realizado esa operación durante la sesión una o varias veces.

Es decir si, en el ejemplo referenciado el usuario Prueba realiza varias operaciones de INSERT en su tabla, una auditoría by access genera tantas entradas como INSERT haya realizado, mientras que una auditoría by session sólo genera una entrada para indicar que se ha realizado una o varias veces la operación INSERT auditada.

Si recordamos el resultado de la consulta cuando se ha auditado la inserción de varios datos en la tabla, la actualización, la consulta a la tabla y la eliminación de datos por parte del usuario Prueba en el ejercicio anterior, se ha almacenado la siguiente información en la vista de la auditoría:

SQL> SELECT USERNAME,TIMESTAMP,OBJ_NAME,ACTION_NAME
  2  FROM   DBA_AUDIT_TRAIL
  3  WHERE OWNER = 'PRUEBA';

USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
INSERT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
SELECT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
SELECT


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
SELECT

USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
UPDATE


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
DELETE


15 filas seleccionadas.

Ahora bien, se puede deshabilitar esa auditoría y crear una nueva auditoría by session

SQL> NOAUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY PRUEBA;

No auditoría terminada correctamente.

SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY PRUEBA BY SESSION;

Auditoría terminada correctamente.

Al repetir, exactamente, las mismas operaciones que en el ejercicio anterior como el usuario Prueba, comprobamos que en la vista de auditoría del diccionario de datos se generan seis filas nuevas que se pueden obtener con la misma consulta.

SQL> SELECT USERNAME,TIMESTAMP,OBJ_NAME,SES_ACTIONS
  2  FROM   DBA_AUDIT_TRAIL
  3  WHERE OWNER = 'PRUEBA'
  4  AND ACTION_NAME = 'SESSION REC';

USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
SES_ACTIONS
---------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
------S---------


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
SES_ACTIONS
---------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
------S---------


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
SES_ACTIONS
---------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
------S---------


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
SES_ACTIONS
---------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
----------S-----


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
SES_ACTIONS
---------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
---S------------


USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
SES_ACTIONS
---------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
---------S------


6 filas seleccionadas.

En este caso, en el campo ACTION_NAME no aparece el nombre de la operación sino el término SESSION REC, que indica que se trata de una entrada generada por una auditoría by session. En cambio, en el campo SES_ACTIONS, que en las entradas generadas por una auditoría by access está vacío, muestra esta información.

Esta campo resume la sesión a través de una cadena de 16 caracteres. Cada uno de ellos representa una acción en este orden:

  1. ALTER
  2. AUDIT
  3. COMMENT
  4. DELETE
  5. GRANT
  6. INDEX
  7. INSERT
  8. LOCK
  9. RENAME
  10. SELECT
  11. UPDATE
  12. REFERENCES
  13. EXECUTE
  14. Posición reservada para usar en el futuro
  15. Posición reservada para usar en el futuro
  16. Posición reservada para usar en el futuro

Estos caracteres pueden tomar los siguientes valores para indicar el resultado de la acción:

Carácter Resultado
- Nada
S Éxito
F Fracaso
B Ambos

Así, el siguiente resultado de la consulta indica que, durante la sesión, el usuario Prueba ejecutó una acción de DELETE exitosamente:

USERNAME
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
SES_ACTIONS
---------------------------------------------------------
PRUEBA
16/02/25
PRUEBA1
---S------------

Documenta las diferencias entre los valores db y db, extended del parámetro audit_trail de ORACLE. Demuéstralas poniendo un ejemplo de la información sobre una operación concreta recopilada con cada uno de ellos.

El parámetro audit_trail habilita o deshabilita la auditoría a nivel de la base de datos en Oracle. Este parámetro puede tomar varios valores. Entre ellos, la auditoría se puede activar indicando el valor db o el valor db, extended.

Cuando el parámetro audit_trail toma el valor db los registros de la auditoría se dirigen a la tabla de registros de auditoría (SYS.AUD$), excepto para aquellos registros que se escriben siempre en el registro de auditoría del sistema operativo. Esta configuración se suele usar en bases de datos de propósito general para facilitar su manejo.

Cuando el parámetro audit_trail toma el valor db, extended la auditoría ejecuta las mismas acciones que cuando toma el valor db pero, además, rellena las columnas SQL BIND y SQL TEXT de la vista SYS.AUD$ cuando sea posible.

Se puede modificar este parámetro en caliente usando la sentencia alter system set AUDIT_TRAIL=db scope=spfile; o alter system set AUDIT_TRAIL=db, extended scope=spfile;.

De nuevo, para ejemplificar la diferencia entre ambos valores del parámetro audit_trail se puede tomar como referencia el ejercicio 3. En este caso, el valor del parámetro audit_trail es db.

SQL> show parameter audit_trail;

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

Por eso, en la vista DBA_AUDIT_TRAIL el campo SQL_TEXT está vacío.

SQL> SELECT SQL_BIND,SQL_TEXT,TIMESTAMP,OBJ_NAME,ACTION_NAME
  2  FROM   DBA_AUDIT_TRAIL
  3  WHERE OWNER = 'PRUEBA'
  4  AND ACTION_NAME != 'SESSION REC';

SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------

16/02/25
PRUEBA1
INSERT


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------

16/02/25
PRUEBA1
INSERT


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------

16/02/25
PRUEBA1
INSERT


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------

16/02/25
PRUEBA1
INSERT


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------

16/02/25
PRUEBA1
INSERT


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------

16/02/25
PRUEBA1
SELECT


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------

16/02/25
PRUEBA1
INSERT


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------

16/02/25
PRUEBA1
INSERT


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------

16/02/25
PRUEBA1
SELECT


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------

16/02/25
PRUEBA1
INSERT


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------

16/02/25
PRUEBA1
INSERT


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------

16/02/25
PRUEBA1
INSERT


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------

16/02/25
PRUEBA1
UPDATE


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------

16/02/25
PRUEBA1
DELETE


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------

16/02/25
PRUEBA1
SELECT


15 filas seleccionadas.

En cambio, si el valor del parámetro audit_trail se cambia a db, extended y se repiten las operaciones DML auditadas, este campo guarda la información sobre las sentencias SQL que se han ejecutado por parte del usuario.

SQL> alter system set AUDIT_TRAIL=db, extended scope=spfile;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> SHOW PARAMETER AUDIT_TRAIL;

NAME				     TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
audit_trail			     string
DB, EXTENDED

SQL> SELECT SQL_TEXT,TIMESTAMP,OBJ_NAME,ACTION_NAME
  2  FROM   DBA_AUDIT_TRAIL
  3  WHERE OWNER = 'PRUEBA'
  4  AND ACTION_NAME != 'SESSION REC';
...
SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
INSERT INTO PRUEBA1 VALUES (1)
17/02/25
PRUEBA1
INSERT


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
INSERT INTO PRUEBA1 VALUES (2)
17/02/25
PRUEBA1
INSERT


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
INSERT INTO PRUEBA1 VALUES (3)
17/02/25
PRUEBA1
INSERT


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
UPDATE PRUEBA1 SET ID=4 WHERE ID=3
17/02/25
PRUEBA1
UPDATE


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
DELETE FROM PRUEBA1 WHERE ID=2
17/02/25
PRUEBA1
DELETE


SQL_TEXT
--------------------------------------------------------------------------------
TIMESTAM
--------
OBJ_NAME
--------------------------------------------------------------------------------
ACTION_NAME
--------------------------------------------------------------------------------
SELECT * FROM PRUEBA1
17/02/25
PRUEBA1
SELECT


27 filas seleccionadas.

Averigua si en Postgres se pueden realizar los cuatro primeros apartados. Si es así, documenta el proceso adecuadamente.

Auditar los intentos de acceso fallidos

Hay varias formas de auditar los intentos fallidos de acceso a una base de datos Postgres. El método más sencillo pero menos preciso consiste en habilitar la auditoría general de la base de datos a nivel de sistema, que almacena una auditoría de todas las sentencias SQL que se ejecutan sobre la base de datos, incluidos los intentos de inicio de sesión. Este método genera un fichero de log en el sistema operativo en el que después se pueden buscar las coincidencias con un intento de acceso fallido con alguna herramienta como grep.

En el fichero /etc/postgresql/15/main/postgresql.conf se habilita el registro de las sentencias SQL y los inicios de sesión en el log.

# This is used when logging to stderr:
logging_collector = on          # Enable capturing of stderr, jsonlog,
                                # and csvlog into log files. Required
                                # to be on for csvlogs and jsonlogs.
                                # (change requires restart)
...
#log_checkpoints = on
log_connections = on
log_disconnections = on
#log_duration = off
#log_error_verbosity = default  # terse, default, or verbose messages
log_hostname = on
log_line_prefix = '%m [%p] %q%u@%d '

Para hacer efectivos los cambios se reinicia el servicio.

sudo systemctl restart postgresql

Tras provocar un intento de acceso fallido a la base de datos, se genera un registro en el fichero de log que se puede filtrar con el comando grep.

root@debian12:/home/debian# cat /var/lib/postgresql/15/main/log/postgresql-2025-02-18_183324.log | grep "password authentication"
2025-02-18 18:33:55.288 UTC [56628] usuario@usuario FATAL:  password authentication failed for user "usuario"
2025-02-18 18:46:05.275 UTC [56988] usuario@usuario FATAL:  password authentication failed for user "usuario"
2025-02-18 18:46:46.910 UTC [56997] usuario@usuario FATAL:  password authentication failed for user "usuario"
2025-02-18 18:48:43.759 UTC [57011] usuario@prueba FATAL:  password authentication failed for user "usuario"

Otra forma, algo más compleja, de activar estos registros en el log es usar la extensión pg_audit. Sin embargo, este método no proporciona una configuración más personalizable que el que ya se ha demostrado anteriormente. También genera registros en el log del sistema operativo.

Procedimiento PL/pgSQL que muestre los accesos fallidos

Puesto que PostgreSQL no almacena los accesos fallidos a la base de datos en una tabla de la misma, como sí hace Oracle, no es posible acceder a esta información desde un procedimiento PL/pgSQL dentro de la base de datos.

Auditar las operaciones DML realizadas por un usuario sobre tablas de su esquema

Para auditar las operaciones DML ejecutadas por un usuario sobre las tablas de su esquema en PostgreSQL se puede usar el complemento pg_audit. Esta extensión del sistema gestor de bases de datos permite almacenar un registro de las sentencias SQL que se ejecutan en la base de datos en los ficheros de log del sistema operativo.

En primer lugar, se instala la extensión pg_audit.

sudo apt install postgresql-15-pgaudit

En el fichero de configuración /etc/postgresql/15/main/postgresql.conf se habilita la extensión y se configura la auditoría de las operaciones DML.

shared_preload_libraries = 'pgaudit'    # (change requires restart)
pgaudit.log = 'write, ddl'
pgaudit.log_catalog = off
pgaudit.log_parameter = on
pgaudit.log_statement_once = off
pgaudit.log_level = 'log'

Posteriormente se reinicia el servicio.

sudo systemctl restart postgresql

Y, en Postgres, se crea la extensión.

postgres=# create extension pgaudit;
CREATE EXTENSION

Para probar el funcionamiento de la auditoría, se ejecutan diferentes sentencias DML.

prueba=> insert into prueba1 values (1);
INSERT 0 1
prueba=> insert into prueba1 values (2);
INSERT 0 1
prueba=> insert into prueba1 values (3);
INSERT 0 1
prueba=> update prueba1 set id=4 where id=3;
UPDATE 1
prueba=> delete from prueba1 where id=2;
DELETE 1
prueba=> select * from prueba1;
 id 
----
  1
  4
(2 rows)

Como se ha comentado previamente, la información de la auditoría de Postgres no se almacena en una tabla de la base de datos, sino que se registra en los ficheros de log del propio sistema operativo. Así, para poder consultar esta información, se puede leer este fichero filtrando con alguna herramienta como grep su contenido.

root@debian12:/home/debian# cat /var/lib/postgresql/15/main/log/postgresql-2025-02-19_000000.log | grep "AUDIT"
2025-02-19 08:26:56.964 UTC [61081] usuario@prueba LOG:  AUDIT: SESSION,1,1,DDL,CREATE TABLE,,,create table prueba1(id int);,<none>
2025-02-19 08:33:02.785 UTC [61122] usuario@prueba LOG:  AUDIT: SESSION,1,1,WRITE,INSERT,,,insert into prueba1 values (1);,<none>
2025-02-19 08:34:01.462 UTC [61136] usuario@prueba LOG:  AUDIT: SESSION,1,1,WRITE,INSERT,,,insert into prueba1 values (2);,<none>
2025-02-19 08:34:03.651 UTC [61136] usuario@prueba LOG:  AUDIT: SESSION,2,1,WRITE,INSERT,,,insert into prueba1 values (3);,<none>
2025-02-19 08:34:15.484 UTC [61136] usuario@prueba LOG:  AUDIT: SESSION,3,1,WRITE,UPDATE,,,update prueba1 set id=4 where id=3;,<none>
2025-02-19 08:34:29.132 UTC [61136] usuario@prueba LOG:  AUDIT: SESSION,4,1,WRITE,DELETE,,,delete from prueba1 where id=2;,<none>

Auditoría de grano fino sobre la inserción de empleados con comisión en la tabla emp de Scott

En Postgres no existe una herramienta que permita realizar una auditoría de grano fino al estilo de las que se pueden realizar en Oracle usando la librería DBMS_FGA. La herramienta de Postgres que permite acercarse lo máximo posible a un resultado parecido son los triggers. En este caso, habría que crear manualmente una tabla de auditoría en la que se almacenase la información recogida por el procedimiento y, por otra parte, crear un trigger que se ejecute cada vez que se inserten o actualicen datos en la tabla emp de Scott en los que el campo comm no esté vacío.

Así, en primer lugar, se crea la tabla de auditoría.

scott=# CREATE TABLE auditoria (
    fecha_hora TIMESTAMP,
    operacion VARCHAR(10),
    usuario VARCHAR(50),
    empno INT,
    comm INT,
    CONSTRAINT pk_auditoria PRIMARY KEY (fecha_hora)
);
CREATE TABLE

A continuación, se crea la función que se va a ejecutar cada vez que se lance el trigger.

scott=# CREATE OR REPLACE FUNCTION auditoria_emp() 
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.comm IS NOT NULL THEN
        INSERT INTO auditoria
        VALUES (TG_OP, CURRENT_USER, NOW(), NEW.empno, NEW.comm);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION

Después se crean los triggers. Tanto para cuando se insertan como para cuando se actualizan datos en la tabla emp de Scott.

scott=# CREATE OR REPLACE TRIGGER trigger_auditoria_emp
AFTER INSERT OR UPDATE ON emp
FOR EACH ROW
EXECUTE FUNCTION auditoria_emp();
CREATE TRIGGER

Para comprobar el funcionamiento de esta solución se insertan diferentes registros en la tabla emp y se actualiza alguno existente.

scott=# INSERT INTO EMP VALUES (7901,'LOLI','SALESMAN',7698,TO_DATE('08/09/81'
,'DD/MM/YY'),1200,20,30);
INSERT 0 1
scott=# INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7903
,'JUAN','CLERK',7698,TO_DATE('03/09/81','DD/MM/YY'),1050,30);
INSERT 0 1
scott=# UPDATE EMP SET COMM = 300 WHERE EMPNO = 7698;
UPDATE 1

En la tabla auditoria se muestra la información recogida por el trigger.

scott=# select * from auditoria;
 operacion | usuario  |         fecha_hora         | empno | comm 
-----------+----------+----------------------------+-------+------
 INSERT    | postgres | 2025-02-19 13:15:10.095491 |  7901 |   20
 UPDATE    | postgres | 2025-02-19 13:17:15.674375 |  7698 |  300
(2 rows)

Averigua si en MySQL se pueden realizar los apartados 1, 3 y 4. Si es así, documenta el proceso adecuadamente.

Auditar los intentos de acceso fallidos

De forma similar a Postgres, MariaDB y MySQL permiten almacenar en los logs del sistema operativo un registro con las acciones ejecutadas en la base de datos. Una de las opciones para auditar acceso fallidos a la base de datos es, precisamente, activar estos logs de errores. Con esta herramienta, MariaDB almacena en un fichero de log del sistema sólo los errores que se producen en la base de datos y no todas las operaciones que se realizan en ella. Así, al filtrar el contenido de este fichero de log se pueden encontrar los registros de los intentos de acceso fallidos.

Esta herramienta de MariaDB y MySQL se activa con el parámetro de configuración log_error en el fichero de configuración principal de la base dedatos, que depende de la versión y distribución en la que se haya instalado el sistema gestor de bases de datos. En este caso, este fichero es /etc/mysql/mariadb.conf.d/50-server.cnf. El valor que toma este parámetro es la ruta al fichero de lo en el que el sistema gestor de bases de datos debe escribir todos los errores que se produzcan durante la ejecucióndel servicio. Cabe destacar que, a diferencia de Postgres, que crea sus propios ficheros de log automáticamente, en el caso de MariaDB, el fichero debe existir previamente y debe ser accesible por el usuario mysql que, además, debe tener permiso de escritura en el fichero.

# When running under systemd, error logging goes via stdout/stderr to journald
# and when running legacy init error logging goes to syslog due to
# /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf
# Enable this if you want to have error logging into a separate file
log_error = /var/log/mysql/error.log

Tras modificar este parámetro en la configuración de MariaDB se reinicia el servicio.

sudo systemctl restart mariadb

Después de intententar realizar varios accesos fallidos para comprobar el funcionamiento de esta auditoría se puede filtrar el fichero de log con alguna herramienta como grep para encontrar los intentos de acceso fallidos.

debian@debian12:~$ sudo cat /var/log/mysql/error.log | grep 'Access denied'
2025-02-19 16:20:39 33 [Warning] Access denied for user 'root'@'localhost'
2025-02-19 16:21:30 35 [Warning] Access denied for user 'usuario'@'localhost' (using password: YES)
2025-02-19 16:21:35 36 [Warning] Access denied for user 'usuario'@'localhost' (using password: NO)

Auditoría de operaciones DML realizadas por un usuario sobre tablas de su esquema

Para auditar las operaciones DML en MariaDB se puede instalar el plugin server_audit en la base de datos. Este plugin, como ocurre en Postgres genera un fichero de log en el sistema operativo con las operaciones que se han ejecutado sobre la base de datos.

MariaDB [prueba]> INSTALL SONAME 'server_audit';
Query OK, 0 rows affected (0.018 sec)

Este plugin se puede configurar desde la línea de comandos de MySQL con sentencias SET GLOBAL COMO SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE'; o desde el fichero de configuración del sistema gestor de bases de datos.

server_audit_events=QUERY_DML,TABLE
server_audit_loggin=ON
server_audit_file_path=/var/log/mysql/audit.log
server_audit_incl_users='usuario'
server_audit_file_rotate_size=1000000
server_audit_file_rotations=5

Esta configuración hace que se auditen las consutlas DML junto al nombre de la tabla a la que afectan del usuario usuario y que esta información se almacene en el fichero de log indicado en el sistema operativo. El tamaño máximo del fichero se indica también en esta configuración. Cabe indicar que si en el parámetro server_audit_events se indicase también la opción CONNECT este plugin podría usarse para auditar los intentos de acceso fallidos a la base de datos de forma muy similar a como se ha mostrado en el punto anterior.

Para aplicar la configuración se reinicia el servicio.

sudo systemctl restart mariadb

Para comprobar el funcionamiento del plugin de auditoría se realizan varias operaciones con el usuario usuario.

MariaDB [prueba]> create table prueba1 (id int);
Query OK, 0 rows affected (0.008 sec)

MariaDB [prueba]> insert into prueba1 values (1);
Query OK, 1 row affected (0.002 sec)

MariaDB [prueba]> insert into prueba1 values (2);
Query OK, 1 row affected (0.002 sec)

MariaDB [prueba]> insert into prueba1 values (3);
Query OK, 1 row affected (0.002 sec)

MariaDB [prueba]> update prueba1 set id=4 where id=3;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [prueba]> delete from prueba1 where id=2;
Query OK, 1 row affected (0.002 sec)

MariaDB [prueba]> select * from prueba1;
+------+
| id   |
+------+
|    1 |
|    4 |
+------+
2 rows in set (0.001 sec)

Tras realizar estas operaciones en la base de datos, con la palabra QUERY se puede filtrar en el fichero la información sobre las operaciones DML realizadas por el usuario.

debian@debian12:~$ sudo cat /var/log/mysql/audit.log | grep 'QUERY'
20250219 16:48:13,debian12,usuario,localhost,32,60,QUERY,prueba,'select @@version_comment limit 1',0
20250219 16:48:32,debian12,usuario,localhost,32,62,QUERY,prueba,'insert into prueba1 values (1)',0
20250219 16:48:35,debian12,usuario,localhost,32,63,QUERY,prueba,'insert into prueba1 values (2)',0
20250219 16:48:38,debian12,usuario,localhost,32,64,QUERY,prueba,'insert into prueba1 values (3)',0
20250219 16:48:49,debian12,usuario,localhost,32,65,QUERY,prueba,'update prueba1 set id=4 where id=3',0
20250219 16:49:00,debian12,usuario,localhost,32,66,QUERY,prueba,'delete from prueba1 where id=2',0
20250219 16:49:06,debian12,usuario,localhost,32,67,QUERY,prueba,'select * from prueba1',0

Auditoría de grano fino sobre la inserción de empleados con comisión en la tabla emp de Scott

Como Postgres, MariaDB no cuenta con una herramienta para configurar auditorías de grano fino al estilo de Oracle. Así que la solución que se puede adoptar es similar a la del caso anterior: crear manualmente una tabla de auditoría, así como un trigger que la rellene a partir de las inserciones y actualizaciones en la tabla emp de Scott.

Para ello, en primer lugar, es necesario crear la tabla de auditoría.

MariaDB [scott]> CREATE TABLE auditoria (
    ->     fecha_hora DATETIME,
    ->     operacion VARCHAR(10),
    ->     usuario VARCHAR(50),
    ->     empno INT,
    ->     comm INT,
    ->     CONSTRAINT pk_auditoria PRIMARY KEY (fecha_hora)
    -> );
Query OK, 0 rows affected, 1 warning (0.008 sec)

A continuación, se crean los triggers para rellenar la tabla de auditoría cada vez que se inserte o actualice alguna comisión de la tabla emp. Como no hay ninguna forma de almacenar la operación que se está realizando en MariaDB, en este caso se hace dos triggers diferentes, uno para las inserciones y otro para las actualizaciones y el campo operacion se indica de forma manual en cada uno de ellos.

MariaDB [scott]> DELIMITER //
MariaDB [scott]> CREATE OR REPLACE TRIGGER trigger_auditoria_emp_insert
    -> AFTER INSERT ON emp
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.comm IS NOT NULL THEN
    ->         INSERT INTO auditoria 
    ->         VALUES (NOW(),'INSERT',CURRENT_USER(),NEW.empno,NEW.comm);
    ->     END IF;
    -> END; //
Query OK, 0 rows affected (0.006 sec)
MariaDB [scott]> DELIMITER ;

MariaDB [scott]> DELIMITER //
MariaDB [scott]> CREATE OR REPLACE TRIGGER trigger_auditoria_emp_update
    -> AFTER UPDATE ON emp
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.comm IS NOT NULL THEN
    ->         INSERT INTO auditoria 
    ->         VALUES (NOW(),'UPDATE',CURRENT_USER(),NEW.empno,NEW.comm);
    ->     END IF;
    -> END; //
Query OK, 0 rows affected (0.009 sec)
MariaDB [scott]> DELIMITER ;

Para probar el funcionamiento de los trigger, se realizan varias inserciones y alguna actualización en la tabla emp de Scott.

MariaDB [scott]> INSERT INTO emp VALUES (7901,'LOLI','SALESMAN',7698,'1981-09-08',1200,20,30);
Query OK, 1 row affected (0.001 sec)

MariaDB [scott]> INSERT INTO emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (7903,'JUAN','CLERK',7698,'1981-09-03',1050,30);
Query OK, 1 row affected (0.002 sec)

MariaDB [scott]> UPDATE emp SET COMM = 300 WHERE EMPNO = 7698;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Al ejecutar estas sentencias, se disparan los triggers y en al tabla auditoria se almacena un registro cada vez que corresponde.

MariaDB [scott]> select * from auditoria;
+---------------------+-----------+----------------+-------+------+
| fecha_hora          | operacion | usuario        | empno | comm |
+---------------------+-----------+----------------+-------+------+
| 2025-02-19 17:45:05 | INSERT    | root@localhost |  7901 |   20 |
| 2025-02-19 17:45:32 | UPDATE    | root@localhost |  7698 |  300 |
+---------------------+-----------+----------------+-------+------+
2 rows in set (0.001 sec)

Averigua las posibilidades que ofrece MongoDB para auditar los cambios que va sufriendo un documento. Demuestra su funcionamiento.

Las posibilidades de auditoría en las bases de datos de MongoDB difieren mucho según el nivel de pago del despliegue. Por ejemplo, los despliegues autogestionados de MongoDB Enterprise y los despliegues de pago en Atlas cuentan con una herramienta de auditoría que permite registrar en los logs del sistema las operaciones realizadas sobre los documentos de las bases de datos del servidor. Esta herramienta, integrada con el sistema de logs de systemd (--auditDestination syslog), permite almacenar este registro de auditoría en formato (--auditFormat) de log, en formato JSON o en formato BSON. También ofrece la opción de mostrar una auditoría de las operaciones efectuadas sobere la base de datos en tiempo real por pantalla.

Puesto que esta herramienta sólo está disponible en despliegues de pago, no se puede demostrar su funcionamiento en este documento.

Además, también existen algunas herramientas de MongoDB que se integran con diferentes lenguajes de programación y permiten llevar un control de las versiones de los documentos cada vez que se modifica uno de ellos en una colección.

En los despligues de MongoDB Community Edition se puede usar la herramienta change streams para auditar las modificaciones sobre los documentos de una colección. Esta herramienta permite a las aplicaciones acceder a los cambios en tiempo real. Aunque la utilidad real de esta herramienta es permitir que las aplicaciones que se conectan a una base de datos MongoDB puedan acceder a la información sobre los cambios en los datos en las colecciones, bases de datos y despliegues de MongoDB para reaccionar a estos cambios de forma rápida, esta change streams también se puede usar para auditar los cambios que se producen sobre los documentos de una o varias colecciones de la base de datos.

Aunque esta herramienta está diseñada para funcionar en aplicaciones que se conectan a una base de datos MongoDB, se puede crear un Change Stream desde mongosh con el método watch().

Atlas atlas-pk6i6y-shard-0 [primary] test> watchCursor = db.eurovision.watch();
ChangeStreamCursor on eurovision

Tras declarar el cursor en mongosh se puede iterar sobre los cambios que se producen en los documentos de la colección.

while (!watchCursor.isClosed()) {
  let next = watchCursor.tryNext()
  while (next !== null) {
    printjson(next);
    next = watchCursor.tryNext()
  }
}

Para demostrar el funcionamiento de esta herramienta de auditoría se inserta un nuevo documento en esta colección, se modifica y se elimina.

Atlas atlas-pk6i6y-shard-0 [primary] test> db.eurovision.insertOne({year: 2024
}) 
{
  acknowledged: true,
  insertedId: ObjectId('67b6296d193f96f504544ca7')
}
Atlas atlas-pk6i6y-shard-0 [primary] test> db.eurovision.updateOne({year: 2024
}, {$set: {year: 2025}})
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
Atlas atlas-pk6i6y-shard-0 [primary] test> db.eurovision.deleteOne({year: 2025
}) 
{ acknowledged: true, deletedCount: 1 }

Así, al realizar estas operaciones en la colección auditada, la salida del cursor Change Stream muestra la información sobre las modificaciones aplicadas.

Atlas atlas-pk6i6y-shard-0 [primary] test> while (!watchCursor.isClosed()) {
...   let next = watchCursor.tryNext()
...   while (next !== null) {
...     printjson(next);
...     next = watchCursor.tryNext()
...   }
... }

{
  _id: {
    _data: '8267B6296D000000012B042C0100296E5A100420292B49D72D4CBAAD5C7DF95F02A1B9463C6F7065726174696F6E54797065003C696E736572740046646F63756D656E744B65790046645F6964006467B6296D193F96F504544CA7000004'
  },
  operationType: 'insert',
  clusterTime: Timestamp({ t: 1739991405, i: 1 }),
  wallTime: ISODate('2025-02-19T18:56:45.878Z'),
  fullDocument: {
    _id: ObjectId('67b6296d193f96f504544ca7'),
    year: 2024
  },
  ns: {
    db: 'test',
    coll: 'eurovision'
  },
  documentKey: {
    _id: ObjectId('67b6296d193f96f504544ca7')
  }
}
{
  _id: {
    _data: '8267B629FF000000012B042C0100296E5A100420292B49D72D4CBAAD5C7DF95F02A1B9463C6F7065726174696F6E54797065003C7570646174650046646F63756D656E744B65790046645F6964006467B6296D193F96F504544CA7000004'
  },
  operationType: 'update',
  clusterTime: Timestamp({ t: 1739991551, i: 1 }),
  wallTime: ISODate('2025-02-19T18:59:11.065Z'),
  ns: {
    db: 'test',
    coll: 'eurovision'
  },
  documentKey: {
    _id: ObjectId('67b6296d193f96f504544ca7')
  },
  updateDescription: {
    updatedFields: {
      year: 2025
    },
    removedFields: [],
    truncatedArrays: []
  }
}
{
  _id: {
    _data: '8267B62A1E000000012B042C0100296E5A100420292B49D72D4CBAAD5C7DF95F02A1B9463C6F7065726174696F6E54797065003C64656C6574650046646F63756D656E744B65790046645F6964006467B6296D193F96F504544CA7000004'
  },
  operationType: 'delete',
  clusterTime: Timestamp({ t: 1739991582, i: 1 }),
  wallTime: ISODate('2025-02-19T18:59:42.047Z'),
  ns: {
    db: 'test',
    coll: 'eurovision'
  },
  documentKey: {
    _id: ObjectId('67b6296d193f96f504544ca7')
  }
}

Averigua si en MongoDB se pueden auditar los accesos a una colección concreta. Demuestra su funcionamiento.

Como ocurre con la auditoría de operaciones sobre los documentos, la auditoría de accesos a una colección es diferente según el nivel de pago del sistema gestor de base de datos. En MongoDB Enterprise y los clusters de pago de AtlasDB, es posible auditar con detalle el acceso a colecciones concretas usando la herramienta auditLog.

En cambio, en los clusters gratuitos de AtlasDB, así como en los despliegues autogestionados de MongoDB Community Edition esta herramienta no se puede usar y, por lo tanto, no existe un mecanismo para auditar los accesos a las colecciones de las bases de datos.

Averigua si en Cassandra se pueden auditar las inserciones de datos.

Cassandra cuenta con una utilidad para generar logs de auditoría llamada Audit Logging. Esta herramietna se configura en el fichero de configuración principal del sistema gestor de bases de datos: /etc/cassandra/cassandra.yaml, en la instalación estándar de Cassandra en Debian.

# Audit logging - Logs every incoming CQL command request, authentication to a
# on audit_logging for full details about the various configuration options.
audit_logging_options:
  enabled: true
  logger:
    - class_name: BinAuditLogger
  audit_logs_dir: "/cassandra/audit/logs/hourly"
  # included_keyspaces:
  # excluded_keyspaces: system, system_schema, system_virtual_schema
  # included_categories:
  # excluded_categories:
  # included_users:
  # excluded_users:
  roll_cycle: HOURLY
  # block: true
  # max_queue_weight: 268435456 # 256 MiB
  # max_log_size: 17179869184 # 16 GiB
  ## archive command is "/path/to/script.sh %path" where %path is replaced with
  # archive_command:
  # max_archive_retries: 10

Para aplicar la configuración, se reinicia el serviicio.

sudo systemctl restart cassandra

Para que Cassandra pueda escribir los logs debe existir el directorio que se indica en el fichero de configuración en el sistema y, además, debe tener permiso de lectura, escritura y ejecución para otros.

sudo mkdir -p /cassandra/audit/logs/hourly
sudo chmod -R a+rwx /cassandra/audit/logs/hourly

Para demostrar el funcionamiento de la herramienta de auditoría en Cassandra, se crea un nuevo keyspace y una tabla en él y se insertan algunos datos.

cassandra@cqlsh> CREATE KEYSPACE prueba
   ... WITH replication = {'class': 'SimpleStrategy', 'replication_factor' : 1};
cassandra@cqlsh> USE prueba;
cassandra@cqlsh:prueba> CREATE TABLE prueba1(id int,
          ... PRIMARY KEY (id)
          ... );
cassandra@cqlsh:prueba> INSERT INTO prueba1 (id) VALUES (1);
cassandra@cqlsh:prueba> INSERT INTO prueba1 (id) VALUES (2);
cassandra@cqlsh:prueba> INSERT INTO prueba1 (id) VALUES (3);
cassandra@cqlsh:prueba> DELETE FROM prueba1 WHERE id=3;
cassandra@cqlsh:prueba> SELECT * FROM prueba1;

 id
----
  1
  2

(2 rows)

Por defecto, estos ficheros de log se almacenan en formato binario. Aunque se pueden guardar en texto plano cambiando el parámetro logger a FileAuditLogger en el fichero de configuración, los logs en formato binario también se pueden leer con la herramienta auditlogviewer de Cassandra.

debian@bbdd:~$ auditlogviewer /cassandra/audit/logs/hourly
...
Type: audit
LogMessage: user:cassandra|host:localhost/127.0.0.1:7000|source:/127.0.0.1|port:35794|timestamp:1739993561209|type:LOGIN_SUCCESS|category:AUTH|operation:LOGIN SUCCESSFUL Type: audit
LogMessage: user:cassandra|host:localhost/127.0.0.1:7000|source:/127.0.0.1|port:35794|timestamp:1739993590190|type:USE_KEYSPACE|category:OTHER|ks:prueba|operation:use prueba ; Type: audit
LogMessage: user:cassandra|host:localhost/127.0.0.1:7000|source:/127.0.0.1|port:35794| imestamp:1739993590194|type:USE_KEYSPACE|category:OTHER|ks:prueba|ope ration:USE "prueba" Type: audit 
LogMessage: user:cassandra|host:localhost/127.0.0.1:7000|source:/127.0.0.1|port:35794|timestamp:1739993606766|type:CREATE_TABLE|category:DDL|ks:prueba|scope:prueba1|operation:CREATE TABLE prueba1(id int, PRIMARY KEY (id) ); Type: audit
LogMessage: user:cassandra|host:localhost/127.0.0.1:7000|source:/127.0.0.1|port:35794|timestamp:1739993628464|type:UPDATE|category:DML|ks:prueba|scope:prueba1|operation:INSERT INTO prueba1 (id) VALUES (1); Type: audit
LogMessage: user:cassandra|host:localhost/127.0.0.1:7000|source:/127.0.0.1|port:35794|timestamp:1739993628470|type:UPDATE|category:DML|ks:prueba|scope:prueba1|operation:INSERT INTO prueba1 (id) VALUES (2); Type: audit
LogMessage: user:cassandra|host:localhost/127.0.0.1:7000|source:/127.0.0.1|port:35794|timestamp:1739993628473|type:UPDATE|category:DML|ks:prueba|scope:prueba1|operation:INSERT INTO prueba1 (id) VALUES (3); Type: audit
LogMessage: user:cassandra|host:localhost/127.0.0.1:7000|source:/127.0.0.1|port:35794|timestamp:1739993628479|type:DELETE|category:DML|ks:prueba|scope:prueba1|operation:DELETE FROM prueba1 WHERE id=3; Type: audit
LogMessage: user:cassandra|host:localhost/127.0.0.1:7000|source:/127.0.0.1|port:35794|timestamp:1739993629069|type:SELECT|category:QUERY|ks:prueba|scope:prueba1|operation:SELECT * FROM prueba1;

Para filtrar el contenido de este log se puede usar una herramienta como grep. Así, por ejemplo, si se quiere llevar una auditoría únicamente de las inserciones en las tablas se puede filtrar el fichero de log por aquellas entradas en las que la operación sea INSERT.

debian@bbdd:~$ auditlogviewer /cassandra/audit/logs/hourly | grep 'operation:INSERT' 
LogMessage: user:cassandra|host:localhost/127.0.0.1:7000|source:/127.0.0.1|port:35794|timestamp:1739993628464|type:UPDATE|category:DML|ks:prueba|scope:prueba1|operation:INSERT INTO prueba1 (id) VALUES (1);
LogMessage: user:cassandra|host:localhost/127.0.0.1:7000|source:/127.0.0.1|port:35794|timestamp:1739993628470|type:UPDATE|category:DML|ks:prueba|scope:prueba1|operation:INSERT INTO prueba1 (id) VALUES (2);
LogMessage: user:cassandra|host:localhost/127.0.0.1:7000|source:/127.0.0.1|port:35794|timestamp:1739993628473|type:UPDATE|category:DML|ks:prueba|scope:prueba1|operation:INSERT INTO prueba1 (id) VALUES (3);

Fuentes

Oracle audit table example

AUDIT (Traditional Auditing)

26 Introduction to Auditing

Enable Oracle Audit of Failed Login Attempts to Shield Your Sensitive Data

5.249 DBA_PRIV_AUDIT_OPTS

4.159 DBA_AUDIT_SESSION

Oracle Database Unified Audit: Best Practice Guidelines

How to track failed logon attempts using Unified Auditing

How to Monitor and Identify Failed Login Attempts in Oracle

Errores ORACLE mas consultados

85 ORA-24280 to ORA-28726

Useful oracle auditing related commands

Audit all insert, delete, update and select statements on a schema in Oracle 12.2

31 Value-Based Auditing with Fine-Grained Audit Policies

71 DBMS_FGA

Steps to implement Oracle Fine Grained Auditing (FGA)

4.118 DBA_AUDIT_TRAIL

1.26 AUDIT_TRAIL

The value of audit_trail=DB,EXTENDED

pgAudit. Open Source PostgreSQL Audit Logging

https://ozwizard.medium.com/postgres-database-audit-policies-de4e6c5f0eb2

Guide to Auditing and Monitoring Access in PostgreSQL

Log MySQL login attempts

MariaDB Audit Plugin

MariaDB Audit Plugin - Log Settings

Trigger Overview

Auditing Self-Managed Deployments

Audit trails with MongoDB’s change stream

How Do Change Streams Work in MongoDB?

Change Streams

Working with Change Streams in MongoDB (with examples)

db.collection.watch()

$changeStream (aggregation)

Audit Logging - Cassandra

Tags :
comments powered by Disqus

Relacionados

Configuración de herramientas antispam en el servidor de correos

Cuando se configura un servidor de correos de Internet es importante configurarlo de manera correcta para que pueda usar los mecanismos disponibles para filtrar el spam que recibe.

Leer

Instalación y configuración del gestor de arranque systemd-boot en Debian

systemd-boot es un gestor de arranque creado de la fusión del proyecto gummiboot con el proyecto systemd. Tanto gummiboot como systemd-boot están desarrollados por Red Hat. Este gestor de arranque pretende ser una alternativa minimalista al gestor de arranque GRUB, desarrollado por GNU y está diseñado para sistemas que usan la interfaz de firmware UEFI.

Leer

Uso de cloud-init con libvirt

OpenStack es una herramienta de infraestructura como servicio que puede gestionarse desde la línea de comandos a través de su cliente.

Leer