¿Qué es una auditoría en bases de datos?

Una auditoría en bases de datos es un proceso que permite verificar el correcto funcionamiento de una base de datos, así como el cumplimiento de las políticas de seguridad y de las normas de calidad de los datos. Dicho de otra forma, es un proceso que permite medir, demostrar, monitorear y registrar los accesos a la información que se haya almacenada en en una base de datos.

Un administrador puede realizar auditorías con acciones individuales, como el tipo de declaración de lenguaje de consulta estructurado (SQL) ejecutada, o en combinaciones de datos que pueden incluir el nombre del usuario, la aplicación o la marca de tiempo, por ejemplo. Los auditores deben auditar las actividades exitosas y fallidas, e incluir o excluir usuarios específicos de la auditoría.

Ejercicios prácticos

Ejercicio 1

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

Lo primero que debemos hacer es activar la auditoría, que en ORACLE depende de un parámetro de sistema, AUDIT_TRAIL. Este parámetro puede tener los siguientes valores:

  • NONE: No se realiza ninguna auditoría.
  • DB: Se realiza una auditoría en la base de datos.
  • OS: Se realiza una auditoría en el sistema operativo.

Antes de nada, vamos a comprobar el valor actual del parámetro:

SQL> show parameter audit

Como podemos ver, enmi caso, el valor actual es BD. Si por elcontrario, el valor actual hubiese sido NONE, por lo que no se podría realizar ninguna auditoría. Para activar la auditoría, debemos ejecutar el siguiente comando:

SQL> alter system set audit_trail=db scope=spfile;

Para que la modificación sea efectiva, debemos reiniciar la base de datos:

SQL> shutdown immediate
SQL> startup

Si comprobamos de nuevo el valor del parámetro, veremos que ahora es DB:

SQL> show parameter audit_trail

Por lo que, podemos confirmar que los cambios se han realizado correctamente. Pero,ahora deberemos activar la auditoría de intentos fallidos al sistema.

Por ejemplo, imaginemos que nos han contratado para realizar la FCT en una empresa y que, por error, no somos capaces de acceder a la base de datos. Para ver nuestros intentos fallidos, deberá tener habilitada la auditoría de inicio de sesión, pero concretamente, la auditoría de intentos fallidos al sistema. Para ello, debemos ejecutar el siguiente comando:

SQL> audit session ELSIF p_codigo = ever not successful;
SQL> audit session by maria;

Vamos a entrar en la base de datos pero, vaya… ¡nos hemos equivocado de contraseña! Pero a la tercera va la vencida y, ¡por fin! ¡Hemos accedido!

Si ahora comprobamos el contenido de la tabla dba_audit_session, veremos que se ha registrado el intento fallido:

SQL> SELECT USERNAME, OS_USERNAME, TIMESTAMP, ACTION_NAME, RETURNCODE
FROM dba_audit_session
WHERE username = 'MARIA';

En la imagen anterior podemos ver cuando nos hemos conectado a la base de datos y cuando hemos intentado acceder con una contraseña incorrecta. Cuando sucede esto, el valor de returncode es ORA-01017. El código ORA-01017 significa que el usuario no existe o la contraseña es incorrecta. Si el valor de returncode es 0, significa que el intento ha sido exitoso.

Existen otros códigos de error que nos pueden ser de utilidad a la hora de realizar auditorías. Estos códigos de error son:

  • ORA-00911: Caracteres no válidos.
  • ORA-01004: Acceso denegado.
  • ORA-01017: Usuario o contraseña incorrectos.
  • ORA-01045: Sin permiso CREATE SESSION.
  • ORA-28000: Cuenta bloqueada.
  • ORA-28001: Contraseña expirada.
  • ORA-28002: Contraseña en periodo de caducidad.
  • ORA-28003: Contraseña vulnerable.
  • ORA-28011: Contraseña en periodo de caducidad.
  • …etc.

Pero en elsiguiente ejercicio, veremos como podemos traducir estos códigos de error a mensajes de texto comprensibles.

En caso de que queramos desactivar la auditoría, debemos ejecutar el siguiente comando:

SQL> NOAUDIT CREATE SESSION WHENEVER NOT SUCCESSFUL;

Ejercicio 2

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.

Para realizar este ejercicio, vamos a crear un procedimiento en PL/SQL que nos muestre los accesos fallidos por credenciales incorrectas.

  • Creamos el procedimiento que nos mostrará los mensajes de error.

    CREATE OR REPLACE FUNCTION MensajesTraducidos (p_codigo VARCHAR2)
RETURN VARCHAR2
IS
    v_mensaje VARCHAR2(100);
BEGIN
    CASE p_codigo
        WHEN 911 THEN v_mensaje:='Caracteres no validos';
        WHEN 1004 THEN v_mensaje:='Acceso denegado';
        WHEN 1017 THEN v_mensaje:='Usuario o contrasena incorrectos';
        WHEN 1033 THEN v_mensaje:='Usuario inexistente';
        WHEN 1045 THEN v_mensaje:='Sin permiso CREATE SESSION';
        WHEN 28000 THEN v_mensaje:='Cuenta bloqueada';
        WHEN 28001 THEN v_mensaje:='Contraseña expirada';
        WHEN 28002 THEN v_mensaje:='Contraseña en periodo de caducidad';
        WHEN 28003 THEN v_mensaje:='Contraseña vulnerable';
        WHEN 28011 THEN v_mensaje:='Contraseña en periodo de caducidad';
        WHEN 28512 THEN v_mensaje:='Cuenta bloqueada';
        ELSE v_mensaje:='Error desconocido. Por favor, contacte con el administrador.';
    END CASE;
RETURN v_mensaje;
END;
/

  • Creamos la cabecera del procedimiento que nos mostrará los accesos fallidos.

    CREATE OR REPLACE PROCEDURE CabeceraErroresLogin
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('-------------------------------');
    DBMS_OUTPUT.PUT_LINE('Accesos fallidos de conexion');
    DBMS_OUTPUT.PUT_LINE('-------------------------------');
END;
/

  • Creamos el procedimiento que nos mostrará los accesos fallidos.

    CREATE OR REPLACE PROCEDURE AccesosFallidos
IS
    CURSOR c_fallo IS
        SELECT OS_USERNAME,RETURNCODE, USERNAME, TIMESTAMP
        FROM DBA_AUDIT_SESSION
        WHERE ACTION_NAME = 'LOGON'
        AND returncode != 0
        ORDER BY TIMESTAMP DESC;
    v_mensaje VARCHAR2(100);
BEGIN
    CabeceraErroresLogin;
    FOR fallo IN c_fallo LOOP
        v_mensaje:=MensajesTraducidos(fallo.RETURNCODE);
        DBMS_OUTPUT.PUT_LINE('Usuario del sistema: ' || fallo.OS_USERNAME);
        DBMS_OUTPUT.PUT_LINE('Usuario: ' || fallo.USERNAME);
        DBMS_OUTPUT.PUT_LINE('Fecha: ' || TO_CHAR(fallo.TIMESTAMP, 'DD/MM/YYYY HH24:MI:SS'));
        DBMS_OUTPUT.PUT_LINE('Codigo de error: ' || fallo.RETURNCODE);
        DBMS_OUTPUT.PUT_LINE('Mensaje: ' || v_mensaje);
        DBMS_OUTPUT.PUT_LINE('- - - - -');
    END LOOP;
END;
/

  • Ejecutamos el procedimiento.

    SQL> EXEC AccesosFallidos;

Ejercicio 3

Activa la auditoría de las operaciones DML realizadas por SCOTT. Comprueba su funcionamiento.

En DML (Data Manipulation Language) se incluyen las sentencias que permiten modificar los datos de una base de datos. Estas sentencias son:

  • INSERT: Inserta un nuevo registro en una tabla.
  • UPDATE: Modifica los datos de un registro.
  • DELETE: Elimina un registro de una tabla.

Estas sentencias se pueden ejecutar de forma directa o mediante un procedimiento almacenado. En este ejercicio, vamos a realizar una auditoría de las operaciones DML realizadas por el usuario SCOTT. Para ello, debemos ejecutar el siguiente comando:

SQL> AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE BY SCOTT BY ACCESS;

Para comprobar que la auditoría se ha realizado correctamente, vamos a insertar un nuevo registro en la tabla DEPT:

INSERT INTO DEPT VALUES (70, 'VENTAS', 'MADRID');
INSERT INTO DEPT VALUES (50, 'COMPRAS', 'BARCELONA');
INSERT INTO DEPT VALUES (60, 'CONTABILIDAD', 'SEVILLA');
---
DELETE EMP WHERE EMPNO = 7876;
DELETE EMP WHERE EMPNO = 7902;
DELETE EMP WHERE EMPNO = 7934;
---
UPDATE EMP SET COMM = 1000 WHERE EMPNO = 7788;
UPDATE EMP SET COMM = 2000 WHERE EMPNO = 7839;
UPDATE EMP SET COMM = 3000 WHERE EMPNO = 7844;

Si conectándonos con el usuario administrador, o ‘SYSDBA’, ejecutamos la siguiente sentencia:

SQL> SELECT OBJ_NAME, ACTION_NAME, TIMESTAMP
FROM DBA_AUDIT_TRAIL
WHERE USERNAME = 'SCOTT';

Ejercicio 4

Realiza una auditoría de grano fino para almacenar información sobre la inserción de empleados con sueldo superior a 2000 en la tabla emp de scott.

¿Auditoría de grano fino?

¡Pues claro que sí!

De hecho, es la auditoría que vamos a realizar en este ejercicio.

La auditoría de grano fino o FGA (Fine-Grained Auditing) es una característica de Oracle Database que permite registrar los cambios que se producen en los datos de una base de datos. Registrando los cambios que se producen en los datos, podemos realizar este tipo de auditoría que nos permiten conocer qué cambios se han producido en los datos, y por qué usuario se han producido.

Permite que las políticas de auditoría se asocien con columnas en las tablas de la aplicación junto con las condiciones necesarias para que se genere un registro de auditoría. Las políticas de FGA se asignan a las tablas de aplicaciones mediante la API de FGA. Las políticas de auditoría detalladas se pueden usar para crear registros de auditoría cuando se accede a una tabla durante períodos específicos o se accede a columnas específicas. FGA complementa la nueva auditoría unificada de Oracle Database 12c al permitir que las condiciones de auditoría se asocien con columnas específicas.

Los registros de seguimiento de auditoría creados por Fine Grained Auditing se pueden capturar y analizar en Oracle Audit Vault y Database Firewall, alertando automáticamente al equipo de seguridad sobre posibles actividades maliciosas.

El único inconveniente de esta auditoría es que requiere de una licencia adicional de Oracle Database Enterprise Edition y que para el rendimiento de la base de datos, es recomendable que se use únicamente en casos puntuales, ya que lo disminuye.

Pero, dejémonos de cháchara y vamos a realizar la auditoría de grano fino que nos pide el ejercicio.

Debemos tener en cuenta que, deberemos hacerlo en cualquier versión de Oracle, siempre y cuando esta sea superior a la 11g, ya que es a partir de esta versión donde se implementó la auditoría de grano fino.

Lo primero que vamos a hacer es crear un procedimiento para que, un objeto en concreto de una tabla, se audite cuando se realice una inserción en dicha tabla.

BEGIN
    DBMS_FGA.ADD_POLICY (
        object_schema => 'SCOTT',
        object_name => 'EMP',
        policy_name => 'AUDITAR_EMPLEADOS',
        audit_condition => 'SAL > 2000',
        statement_types => 'INSERT');
END;
/

Para la comprobación, vamos a insertar varios empleados, 1 con un salario inferior a 2000 y otro con un salario superior a 2000.

INSERT INTO EMP VALUES (7934, 'FRANK', 'CLERK', 7782, TO_DATE('23-01-1982', 'DD-MM-YYYY'), 2100, NULL, 10);

INSERT INTO EMP VALUES (7935, 'JUAN', 'CLERK', 7782, TO_DATE('23-01-1982', 'DD-MM-YYYY'), 1300, NULL, 10);

Ahora, vamos a comprobar que se ha realizado la auditoría de grano fino correctamente. Para ello, vamos a ejecutar la siguiente sentencia:

SELECT DB_USER, OBJECT_NAME, SQL_TEXT, CURRENT_USER, TIMESTAMP
FROM DBA_FGA_AUDIT_TRAIL
WHERE POLICY_NAME = 'AUDITAR_EMPLEADOS';

Como podemos observar, se muestra la información de la inserción de los empleados, pero solo se muestra la información del empleado que tiene un salario superior a 2000, que era el que se tenía que auditar.

Ejercicio 5

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

Vamos a diferenciar entre estas dos opciones de auditoría:

  • Auditoría by access: Cuando se realiza una auditoría by access, se registra la información de la operación que se ha realizado, pero no se registra la información del usuario que ha realizado la operación. Por ejemplo, si tenemos un usuario que se conecta a la base de datos y ejecuta una sentencia, la auditoría by access, registrará la información de la sentencia que se ha ejecutado, pero no registrará la información del usuario que ha ejecutado la sentencia.

  • Auditoría by session: Cuando se realiza una auditoría by session, se registra la información de la operación que se ha realizado, y se registra la información del usuario que ha realizado la operación. Por ejemplo, si tenemos un usuario que se conecta a la base de datos y ejecuta una sentencia, la auditoría by session, registrará la información de la sentencia que se ha ejecutado, y registrará la información del usuario que ha ejecutado la sentencia.

Pero para probar verdaderamente su funcionamiento, vamos a insertar nuevos registros en la tabla DEPT de la base de datos SCOTT y vamos a comprobar que se ha realizado la auditoría correctamente.

Auditoría by session

  1. Activamosla auditoría en la tabla DEPT;

    AUDIT INSERT,UPDATE,DELETE ON DEPT BY SESSION;

  2. Realizamos la inserción de los registros en la tabla DEPT;

    INSERT INTO DEPT VALUES (80, 'VENTAS', 'MADRID');
INSERT INTO DEPT VALUES (90, 'COMPRAS', 'BARCELONA');

  3. Realizamos la consulta para comprobar que se ha realizado la auditoría correctamente;

    SELECT USERNAME, ACTION_NAME, TIMESTAMP, OBJ_NAME
FROM DBA_AUDIT_TRAIL
WHERE USERNAME = 'SCOTT'
ORDER BY TIMESTAMP DESC;

    13

Auditoría by access

  1. Activamos la auditoría en la tabla EMP;

    AUDIT INSERT,UPDATE,DELETE ON DEPT BY ACCESS;

  2. Realizamos la inserción de los registros en la tabla DEPT;

    INSERT INTO EMP VALUES (7799, 'CHARLES', 'CLERK', 7799, TO_DATE('23-01-1982', 'DD-MM-YYYY'), 2100, NULL, 10);
INSERT INTO EMP VALUES (7987, 'CHRIS', 'CLERK', 7987, TO_DATE('23-01-1982', 'DD-MM-YYYY'), 1300, NULL, 10);

  3. Realizamos la consulta para comprobar que se ha realizado la auditoría correctamente;

    SELECT USERNAME, ACTION_NAME, TIMESTAMP, OBJ_NAME
FROM DBA_AUDIT_OBJECT
WHERE USERNAME = 'SCOTT'
ORDER BY TIMESTAMP DESC;

Para resumir es que la diferencia entre BY ACCESS y BY SESSION radica en cómo la vista del diccionario de datos DBA_AUDIT_TRAIL registra las acciones que capturan:

POR ACCESO: Inserta un registro en la pista de auditoría para cada declaración auditada.

POR SESIÓN: inserta solo un registro de auditoría en la pista de auditoría, para cada usuario y objeto de esquema, durante una sesión que incluye una acción auditada.

Ejercicio 6

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 de Oracle, nos permite controlar el tipo de auditoría que se va a realizar en la base de datos. Este parámetro puede tener varios valores, pero vamos a centrarnos en los siguientes:

  • db: Dirige los registros de auditoría a la pista de auditoría de la base de datos (la tabla SYS.AUD$), excepto los registros que siempre se escriben en la pista de auditoría del sistema operativo. Utilice esta configuración para una base de datos general para facilitar la gestión.

    Si la base de datos se inició en modo de solo lectura con AUDIT_TRAIL establecido en db, Oracle Database establece internamente AUDIT_TRAIL en os. Consulte el registro de alertas para obtener más información.

  • db_extended: Realiza todas las acciones de AUDIT_TRAIL=db y también completa las columnas de tipo CLOB de enlace SQL y texto SQL de la tabla SYS.AUD$, cuando están disponibles. Estas dos columnas se rellenan solo cuando se especifica este parámetro. Cuando se usa la auditoría estándar con DB, EXTENDIDA, los predicados de la base de datos privada virtual (VPD) y los nombres de las políticas también se completan en la tabla SYS.AUD$.

    Si la base de datos se inició en modo de solo lectura con AUDIT_TRAIL establecido en db, extendido, Oracle Database establece internamente AUDIT_TRAIL en os. Consulte el registro de alertas para obtener más información.

Hasta ahora, hemos estado realizando auditoría con el parámetro audit_trail en el valor db, pero vamos a cambiarlo a db_extended y vamos a activarla.

  1. Cambiamos el valor del parámetro audit_trail a db_extended, pero primero vamos a comprbar el valor actual del parámetro;

    SHOW PARAMETER audit_trail;

    ALTER SYSTEM SET audit_trail = db,extended SCOPE=SPFILE;

  2. Reiniciamos la base de datos;

    SHUTDOWN IMMEDIATE;
STARTUP;

  3. Comprobamos que el valor del parámetro audit_trail es db_extended;

    SHOW PARAMETER audit_trail;

Ejercicio 7

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

En PostgreSQL, la auditoría se realiza a travésde extensiones, como pgAudit o Audit Trigger. En este caso, vamos a utilizar la extensión Audit Trigger.

En mi caso, he decidido instalar la extensión Audit Trigger en la base de datos postgres, pero también se puede instalar en cualquier otra base de datos.

Su proceso de instalación es muy sencillo. Lo primero que haremos será descargarnos la extensión desde el siguiente enlace:

wget https://raw.githubusercontent.com/2ndQuadrant/audit-trigger/master/audit.sql

Cuando tengamos la extensión descargada, ingresamos en el servidor de PostgreSQL y ejecutamos el siguiente comando:

sudo -u postgres psql
\i audit.sql

  1. EJERCICIO 1: Auditoría de accesos exitosos.

    Para mostrar los usuarios activos en la base de datos, vamos a hacer uso de la vista pg_stat_activity, y lo haremos realizando la siguiente consulta:

     ```sql
 SELECT distinct usename
 FROM pg_stat_activity
 ```

  2. EJERCICIO 2: Auditoría de accesos fallidos.

    Para realizar la auditoría de accesos fallidos, vamos a hacer uso del log de acceso de PostgreSQL. Para ello, vamos a activar el log de acceso en el fichero de configuración de PostgreSQL, que se encuentra en la ruta /etc/postgresql/9.5/main/postgresql.conf.

     ```bash
 sudo nano /etc/postgresql/9.5/main/postgresql.conf
 ```

    Una vez dentro del fichero de configuración, vamos a buscar la siguiente línea:

     ```bash
 #log_line_prefix = ''            # special values: '%m' for timestamp, '%u' for user,
 ```

    Y la vamos a modificar de la siguiente manera:

     ```bash
 log_line_prefix = '%m %u %d %r %p %h %a %q '
 ```

    Y tambien vamos a modificar la siguiente linea:

     ```bash
 log_statement = ‘all’
 ```

    Para que se nos muestre el query que se esta ejecutando.

    Una vez modificado el fichero de configuración, vamos a reiniciar el servicio de PostgreSQL.

     ```bash
 sudo service postgresql restart
 ```

    Y comprobamos que se ha realizado la auditoría correctamente.

     ```bash
 tail -f /var/log/postgresql/postgresql-13-main.log
 ```

  3. EJERCICIO 3: Auditoría de DML.

    Para realizar la auditoría de DML, vamos a hacer uso de la extensión Audit Trigger. Para ello, vamos a crear una tabla en la base de datos postgres y vamos a insertar, actualizar y borrar datos de la base de datos SCOTT.

    ---INSERTAR DATOS
INSERT INTO DEPT VALUES (70, 'VENTAS', 'MADRID');
INSERT INTO DEPT VALUES (50, 'COMPRAS', 'BARCELONA');
INSERT INTO DEPT VALUES (60, 'CONTABILIDAD', 'SEVILLA');
---BORRAR DATOS
DELETE FROM DEPT WHERE DEPTNO = 70;
DELETE FROM DEPT WHERE DEPTNO = 50;
DELETE FROM DEPT WHERE DEPTNO = 60;
---ACTUALIZAR DATOS
UPDATE DEPT SET LOC = 'BARCELONA' WHERE DEPTNO = 10;
UPDATE DEPT SET LOC = 'SEVILLA' WHERE DEPTNO = 20;
UPDATE DEPT SET LOC = 'MADRID' WHERE DEPTNO = 30;

    Una vez realizadas estas operaciones, vamos a comprobar Audit Trigger para ver si se ha realizado la auditoría correctamente.

    select audit.audit_table('DEPT');

    Para visualizar los datos de la auditoría, nos loguearemos con el usuario postgres y, concretamente, desde la base de datos que estamos auditando, en este caso, la base de datos scott, ejecutamos la siguiente consulta:

    select session_user_name, action, table_name, action_tstamp_clk, client_query 
from audit.logged_actions;

    Como podemos observar en la imagen, podemos ver el usuario, la acción que ha realizado (Insert, Update, Delete), la tabla en la que ha realizado la acción, la fecha y hora en la que ha realizado la acción y el query que ha ejecutado.

Ejercicio 8

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

Para auditar en MySQL, vamos a necesitar dos bases de datos. Para hacer la aditoría vamos a recurrir de un plugin server_audit que nos permite auditar las acciones que se realizan en el servidor de MySQL.

Para instalar el plugin, deberemos ejecutar INSTALL SONAME 'server_audit'; conectados en la base de datos.

  • Ejercicio 1: Auditoría de accesos exitosos. (EXTRA Auditoría de accesos fallidos.)

    Tras la instalación del plugin, vamos a necesitar modificar el fichero de configuración de MySQL, que se encuentra en la ruta /etc/mysql/mariadb.conf.d/50-server.cnf.

    #Descomentamos las siguientes lineas
general_log_file       = /var/log/mysql/mysql.log
general_log            = 1
#Y añadimos la siguiente linea
log_error              = /var/log/mysql/error.log

    Antes de reiniciar el servicio, vamos a cambiar el propietario del fichero de log de MySQL, para que el usuario mysql pueda escribir en el fichero.

    sudo chown mysql: /var/log/mysql/
systemctl restart mysql
systemctl restart mariadb

    Una vez reiniciado el servicio, vamos a comprobar que se ha realizado la auditoría correctamente. Para ello, intentaremos hacer accesos fallidos y accesos exitosos, para controlar que funciona correctamente.

  • Ejercicio 3: Auditoría de DML.

    El siguiente paso es editar el fichero de configuración de MySQL, que se encuentra en la ruta /etc/mysql/mariadb.conf.d/50-server.cnf.

    sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

    Una vez dentro del fichero de configuración, vamos a buscar la siguiente línea:

    server_audit_events=CONNECT,QUERY,TABLE
server_audit_logging=ON
server_audit_incl_users=scott

    Una vez modificado el fichero de configuración, vamos a reiniciar el servicio de MySQL.

    sudo service mysql restart

    Nos conectamos con el usuario que queremos auditar, en este caso, el usuario scott y vamos a realizar las operaciones que queremos auditar.

    INSERT INTO dept VALUES (50, 'COMPRAS', 'BARCELONA');
INSERT INTO dept VALUES (60, 'CONTABILIDAD', 'SEVILLA');
INSERT INTO dept VALUES (70, 'VENTAS', 'MADRID');

    DELETE FROM dept WHERE deptno = 50;
DELETE FROM dept WHERE deptno = 60;
DELETE FROM dept WHERE deptno = 70;

    UPDATE dept SET loc = 'BARCELONA' WHERE deptno = 10;
UPDATE dept SET loc = 'SEVILLA' WHERE deptno = 20;
UPDATE dept SET loc = 'MADRID' WHERE deptno = 30;

  • Ejercicio 4: Sueldo superior a 2000.

    Ahora vamos a auditar la tabla emp para que se nos muestre cuando se realice una inserción o actualización del sueldo de un empleado cuando este sea superior a 2000.

    Nos conectamos con el usuario que queremos auditar, en este caso, el usuario scott y vamos a realizar las operaciones que queremos auditar.

    INSERT INTO emp VALUES (7999, 'JOSH', 'CLERK', 7902, '1980-12-17', 2000, NULL, 20)
INSERT INTO emp VALUES (7987, 'MERY', 'CLERK', 7902, '1980-12-17', 800, NULL, 20)

Ejercicio 9

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

MongoDB Enterprise incluye una capacidad de auditoría para instancias mongod y mongos. La función de auditoría permite a los administradores y usuarios realizar un seguimiento de la actividad del sistema para implementaciones con múltiples usuarios y aplicaciones.

  • Instalación de MongoDB Enterprise en Debian 11

    • Importar la clave pública utilizada por el sistema de gestión de paquetes.

      wget -qO - https://www.mongodb.org/static/pgp/server-6.0.asc | sudo apt-key add -

    • Cree un archivo /etc/apt/sources.list.d/mongodb-enterprise.list para MongoDB.

      echo "deb http://repo.mongodb.com/apt/debian bullseye/mongodb-enterprise/6.0 main" | sudo tee /etc/apt/sources.list.d/mongodb-enterprise.list

    • Vuelva a cargar la base de datos del paquete local.

      sudo apt-get update

    • Instale la última versión estable de MongoDB Enterprise.

      sudo apt-get install -y mongodb-enterprise

  • Habilitar y configurar la salida de auditoría.

    La instalación de auditoría puede escribir eventos de auditoría en la consola, el syslog, un archivo JSON o un archivo BSON. Para habilitar la auditoría en MongoDB Enterprise, establezca un destino de salida de auditoría con --auditDestination.

  • Configuración de auditoría

    MongoDB Enterprise admite la auditoría de varias operaciones. Una solución de auditoría completa debe incluir todos los procesos del servidor mongod y los procesos mongos en el cluster.

    La función de auditoría puede escribir eventos de auditoría en la consola, el syslog (la opción no está disponible en Windows), un archivo JSON o un archivo BSON.

  • Habilitar y configurar la salida de auditoría

    Para habilitar la auditoría en MongoDB Enterprise, establezca un destino de salida de auditoría con --auditDestination.

    • Salida a Syslog

      Para habilitar la auditoría e imprimir eventos de auditoría en el syslog (la opción no está disponible en Windows) en formato JSON, especifique syslog para la configuración –auditDestination. Por ejemplo:

      mongod --dbpath /data/db --auditDestination syslog

      También podemos incluir opciones según nuestras necesidades. Por ejemplo, si deseamos que los clientes remotos se conecten a su implementación o si los miembros de su implementación se ejecutan en diferentes hosts, lo especificaremos con la opción --bind_ip.

      También puede especificar estas opciones en el archivo de configuración:

      storage:
   dbPath: data/db
auditLog:
   destination: syslog

    • Salida a la consola

      Para habilitar la auditoría e imprimir los eventos de auditoría en la salida estándar (es decir, stdout), especifique la consola para la configuración –auditDestination

      mongod --dbpath /data/db --auditDestination console

      A lo igual que en la salida a syslog, podemos especificarle la opción --bind_ip para que los clientes remotos se conecten a su implementación o si los miembros de su implementación se ejecutan en diferentes hosts.

      También puede especificar estas opciones en el archivo de configuración:

      storage:
   dbPath: data/db
auditLog:
   destination: console

    • Salida a un archivo JSON

      Para habilitar la auditoría e imprimir los eventos de auditoría en un archivo JSON, especifique el archivo para la configuración –auditDestination. Por ejemplo:

      Opción Valor
      –auditDestination file
      –auditFormat JSON
      –auditPath El fichero de salida. Acepta el nombre de ruta completo o el nombre de ruta relativo.

      Por ejemplo, lo siguiente habilita la auditoría y registra los eventos de auditoría en un archivo con el nombre de ruta relativa data/db/auditLog.json:

      mongod --dbpath data/db --auditDestination file --auditFormat JSON --auditPath data/db/auditLog.json

      El archivo de auditoría se puede rotar con el comando logRotate, ya sea junto con el registro del servidor o de forma independiente. Los detalles de la rotación se pueden configurar con la opción de archivo de configuración systemLog.logRotate o la opción de línea de comandos --logRotate.

      También puede especificar estas opciones en el archivo de configuración:

      storage:
   dbPath: data/db
auditLog:
   destination: file
   format: JSON
   path: data/db/auditLog.json

    • Salida a un archivo BSON

      Para habilitar la auditoría e imprimir los eventos de auditoría en un archivo BSON, especifique el archivo para la configuración –auditDestination. Por ejemplo:

      Opción Valor
      –auditDestination file
      –auditFormat BSON
      –auditPath El fichero de salida. Acepta el nombre de ruta completo o el nombre de ruta relativo.

      Por ejemplo, lo siguiente habilita la auditoría y registra los eventos de auditoría en un archivo con el nombre de ruta relativa data/db/auditLog.bson:

      mongod --dbpath data/db --auditDestination file --auditFormat BSON --auditPath data/db/auditLog.bson

      A lo igual que en los anteriores casos, podemos especificarle la opción --bind_ip para que los clientes remotos se conecten a su implementación o si los miembros de su implementación se ejecutan en diferentes hosts.

      El archivo de auditoría se puede rotar con el comando logRotate, ya sea junto con el registro del servidor o de forma independiente. Los detalles de la rotación se pueden configurar con la opción de archivo de configuración systemLog.logRotate o la opción de línea de comandos --logRotate.

      También puede especificar estas opciones en el archivo de configuración:

      storage:
   dbPath: data/db
auditLog:
   destination: file
   format: BSON
   path: data/db/auditLog.bson

      El siguiente ejemplo convierte el registro de auditoría en un formato legible mediante bsondump y genera el resultado:

      bsondump --file auditLog.bson

En mi caso, de las opciones anteriores, he decidido utilizar la salida a un archivo JSON. Para ello, hemos ejecutado el siguiente comando en nuestra consola:

mongod --dbpath /var/lib/mongodb/ --auditDestination file --auditFormat JSON --auditPath /var/lib/mongodb/auditLog.json

Una vez configurado, vamos a comprobar que la auditoría funciona de forma correcta mirando los logs que se han generado de MongoDB en el fichero auditLog.json. Pero para leer el fichero, necesitamos un programa que nos permita leerlo. En mi caso, he utilizado el programa jq que nos permite leer ficheros JSON de forma sencilla.

sudo apt install jq

Una vez instalado, vamos a comprobar que la auditoría funciona de forma correcta mirando los logs que se han generado de MongoDB en el fichero auditLog.json.

cat /var/lib/mongodb/auditLog.json | jq

Para darle un poco de movimiento a la auditoría, vamos a crear una base de datos y una colección. Para ello, vamos a utilizar el cliente de MongoDB que hemos instalado en el servidor.

  • Creamos el usuario:

    mongosh 
use admin
db.createUser({user: 'admin', pwd: 'admin', roles: [{role: 'userAdminAnyDatabase', db: 'admin'}, {role: 'readWriteAnyDatabase', db: 'admin'}]})

  • Creamos la colección

    mongosh -u admin -p admin --authenticationDatabase admin
use auditoria
db.createCollection("Alumnos")
db.createCollection("Profesores")

  • Creamos inserciones

    db.Alumnos.insertOne({nombre: "Juan", apellidos: "Pérez", edad: 20})
db.Alumnos.insertOne({nombre: "Ana", apellidos: "García", edad: 21})
db.Alumnos.insertMany([{nombre: "Pedro", apellidos: "González", edad: 22}, {nombre: "María", apellidos: "Martínez", edad: 23}])

    db.Profesores.insertOne({nombre: "Luis", apellidos: "García", edad: 40})
db.Profesores.insertOne({nombre: "María", apellidos: "González", edad: 41})
db.Profesores.insertMany([{nombre: "Antonio", apellidos: "Martínez", edad: 42}, {nombre: "Ana", apellidos: "Pérez", edad: 43}])
db.Profesores.insertMany([{nombre: "Luis", apellidos: "García", edad: 40}, {nombre: "María", apellidos: "González", edad: 41}, {nombre: "Antonio", apellidos: "Martínez", edad: 42}, {nombre: "Ana", apellidos: "Pérez", edad: 43}])

  • Creamos un rol

    db.createRole({ role: "director",privileges: [{ resource: { db: "auditoria", collection: "" }, actions: [ "find", "insert", "update", "remove" ] }], roles: [] })

    Y se lo asignamos a un nuevo usuario:

    db.createUser({user: 'director', pwd: 'director', roles: [{role: 'director', db: 'auditoria'}]})

Para añadir más contenido a nuestra auditoría, vamos a realizar búsquedas con el usuario director que hemos creado.

  • Buscamos todos los alumnos

    db.Alumnos.find()

  • Buscamos todos los profesores

    db.Profesores.find()

  • Buscamos a los alumnos que se llamen Ana

    db.Alumnos.find({nombre: "Ana"})

Ahora, vamos a comprobar que la auditoría funciona de forma correcta mirando los logs que se han generado de MongoDB en el fichero auditLog.json.

cat /var/lib/mongodb/auditLog.json | jq

  • Creación de la colección y su correspondiente índice.

  • Creación del rol y del usuario.

  • Login con el nuevo usuario

  • Desconexion del usuario

Ejercicio 10

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

En MongDB, podemos auditar los accesos a una colección concreta mediante la ejecución del siguiente comando:

db.setLogLevel(level, component)

Con él, podemos ver los accesos a la colección que se esté utilizando en ese momento. Para ello, vamos a crear una base de datos, una coleccion y utilizaremos nuevo usuario administrador para realizar las operaciones.

Con esto, se establece un único nivel de detalle para los mensajes de registro.

db.setLogLevel() toma los siguientes parámetros:

Parámetro Tipo Descripción
level Número entero El nivel de detalle de los mensajes de registro. Los valores posibles son: 0 (ninguno), 1 (errores), 2 (errores y advertencias), 3 (errores, advertencias y mensajes informativos), 4 (errores, advertencias, mensajes informativos y mensajes de depuración).
component Cadena El componente para el que se establece el nivel de detalle. Los valores posibles son: “accessControl”, “command”, “index”, “query”, “replication”, “sharding”, “storage”, “write”, “audit”, “cluster”, “control”, “ftdc”, “geo”, “network”, “query”, “repl”, “security”, “sharding”, “storage”, “write”.

En cuanto a su comportamiento, establece un único nivel de verbosidad. Para establecer varios niveles de detalle en una sola operación, tendremos que utilizar el comando setParameter para establecer el parámetro logComponentVerbosity. También puede especificar la configuración de verbosidad en el archivo de configuración.

Para llevar a cabo el ejercicio, vamos a ejecutar el siguiente comando:

db.setLogLevel(3, "accessControl")

Pero, ¿qué significa esto? Pues que se establece un nivel de detalle (verbosidad) de 3 para el componente accessControl. Esto quiere decir que se mostrarán los mensajes de error, advertencia e información.

Vamos a loguearnos con el usuario admin que hemos creado anteriormente y vamos crear un nuevo usuario administrador.

# Accedemos a la consola de mongo
mongo -u admin -p admin --authenticationDatabase admin
# Accedemos a la base de datos
use audit
# Creamos un nuevo usuario administrador
db.createUser ({user: 'asir', pwd: 'admin', roles: [{role: 'root', db: 'admin'}]})

Ahora nos conectaremos con el nuevo usuario que hemos creado.

mongosh -u asir -p admin --authenticationDatabase admin

Y creamos una dos colecciones, 1curso y 2curso e insertaremos las siguientes asignaturas:

use asir
db.createCollection("Primero")
db.createCollection("Segundo")

db.Primero.insertMany([{nombre: "Sistemas Operativos", horas: "6"}, {nombre: "Lenguajes de Marcas", horas: "4"}, {nombre: "Redes", horas: "5"}, {nombre: "Bases de Datos", horas: "6"}, {nombre: "Formación y Orientación Laboral", horas: "4"}, {nombre: "Fundamento de hardware", horas: "4"}])

db.Segundo.insertMany([{nombre: "Implantación de Aplicaciones Web", horas: "6"}, {nombre: "Cloud Computing", horas: "4"}, {nombre: "Seguridad Informática", horas: "3"}, {nombre: "Empresa e Iniciativa Emprendedora", horas: "4"}, {nombre: "Administración de Sistemas Operativos", horas: "6"}, {nombre: "Administración de Sistemas Gestores de Bases de Datos", horas: "3"}])

Ejecutamos el comando db.setLogLevel(3, "accessControl") para ver los accesos a la colección que se esté utilizando en ese momento.

Para la comprobación de su funcionamiento, he realizado 1 login corecto y otro incorrecto.

Y como podemos comprobar en la siguiente imagen:

…podemos ver que se ha registrado el acceso correcto y el incorrecto.