Auditoria de bases de dades

Per qué cal auditar?

L'auditoria de bases de dades és el procés de registrar i monitorar les activitats que es produeixen sobre el sistema gestor: qui s'ha connectat, quines consultes ha executat, quines taules ha modificat, quins privilegis ha usat. Aquests registres (logs d'auditoria) serveixen per a tres finalitats principals:

1. Compliment normatiu (Compliance)

Nombroses regulacions exigeixen explícitament la traçabilitat de l'accés a dades:

RGPD / GDPR: requereix poder demostrar qui ha accedit a dades personals i quan.
PCI-DSS: exigeix auditar totes les operacions sobre dades de targetes de pagament.
SOX (Sarbanes-Oxley): traçabilitat de canvis en dades financeres.
ISO 27001: registre d'accés com a control de seguretat obligatori.
HIPAA (EUA, dades mèdiques): registre d'accés a informació sanitària.

2. Seguretat activa

Els logs d'auditoria permeten:

Detectar accessos inusuals o fora d'horari.
Identificar intents d'accés a taules sensibles.
Alertar sobre canvis de privilegis no autoritzats.
Detectar comportaments anòmals (exfiltració de dades, SELECTs massius).

3. Anàlisi forense

En cas d'incident de seguretat, els registres d'auditoria permeten:

Reconstruir la seqüència d'esdeveniments que van portar a la bretxa.
Identificar quines dades s'han consultat o modificat.
Determinar si l'atacant va usar credencials legítimes o compreses.
Proporcionar evidències per a accions legals.

Mecanismes d'auditoria nadius per SGBD

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- PostgreSQL no té auditoria nativa avançada per defecte.
-- S'usa l'extensió pgaudit (recomanada i disponible a la majoria de distribucions).

-- Pas 1: Instal·lar l'extensió (com a root del sistema operatiu)
-- Ubuntu/Debian:
-- sudo apt install postgresql-16-pgaudit

-- Pas 2: Activar l'extensió a postgresql.conf:
-- shared_preload_libraries = 'pgaudit'

-- Pas 3: Configurar pgaudit a postgresql.conf o per sessió:
-- pgaudit.log = 'read,write,ddl,role,connection,misc'
-- pgaudit.log_catalog = on
-- pgaudit.log_client = off
-- pgaudit.log_level = 'log'
-- pgaudit.log_parameter = on
-- pgaudit.log_relation = on
-- pgaudit.log_statement_once = off

-- Pas 4: Recarregar la configuració
SELECT pg_reload_conf();

-- Pas 5: Habilitar pgaudit a la base de dades
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Configurar auditoria a nivell de sessió (temporal)
SET pgaudit.log = 'read,write';

-- Configurar auditoria a nivell de base de dades (permanent)
ALTER DATABASE empresa SET pgaudit.log = 'read,write,ddl';

-- Configurar auditoria a nivell d'usuari
ALTER USER app_vendes SET pgaudit.log = 'write';

-- Auditoria d'objectes concrets (object auditing)
-- Requereix que pgaudit.role estigui configurat a postgresql.conf:
-- pgaudit.role = 'auditor'

CREATE ROLE auditor;
GRANT SELECT ON empleats TO auditor;
GRANT SELECT ON comandes TO auditor;
-- Ara, qualsevol SELECT sobre empleats o comandes queda registrat

-- Verificar la configuració
SHOW pgaudit.log;
SELECT name, setting FROM pg_settings WHERE name LIKE 'pgaudit%';

-- MySQL Community Edition: log general de consultes
-- Habilitar el log general (registra TOTES les consultes, alt impacte)
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- Consultar configuració actual
SHOW VARIABLES LIKE '%general_log%';

-- Log de consultes lentes (útil per rendiment i auditoria parcial)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 2;  -- Registra consultes > 2 segons

-- MySQL Enterprise: Audit Log Plugin (solució professional)
-- INSTALL PLUGIN audit_log SONAME 'audit_log.so';
-- SET GLOBAL audit_log_file = '/var/log/mysql/audit.log';
-- SET GLOBAL audit_log_format = 'JSON';
-- SET GLOBAL audit_log_policy = 'ALL';  -- ALL, LOGINS, QUERIES, NONE

-- MariaDB: Audit Plugin (disponible a Community Edition!)
INSTALL SONAME 'server_audit';
SET GLOBAL server_audit_logging = ON;
SET GLOBAL server_audit_file_path = '/var/log/mysql/mariadb_audit.log';
SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE';
-- Events: CONNECT, QUERY, TABLE, QUERY_DDL, QUERY_DML, QUERY_DML_NO_SELECT

-- Filtrar per usuaris concrets (MariaDB)
SET GLOBAL server_audit_incl_users = 'app_vendes,admin_dba';
-- o excloure usuaris menys rellevants:
SET GLOBAL server_audit_excl_users = 'monitor_ro,backup_user';

-- Verificar
SHOW GLOBAL STATUS LIKE 'server_audit%';
SHOW VARIABLES LIKE 'server_audit%';

-- SQL Server té un sistema d'auditoria molt complet i natiu.

-- Opció 1: C2 Audit Mode (deprecated, registra tot, molta informació)
-- sp_configure 'c2 audit mode', 1;
-- RECONFIGURE;

-- Opció 2 (recomanada): SQL Server Audit

-- Pas 1: Crear l'especificació d'auditoria a nivell de servidor
-- (primer cal crear l'objecte Audit que defineix la destinació)
USE master;

CREATE SERVER AUDIT AuditEmpresa
TO FILE (
    FILEPATH = 'C:\AuditLogs\',
    MAXSIZE = 100 MB,
    MAX_ROLLOVER_FILES = 10,
    RESERVE_DISK_SPACE = OFF
)
WITH (
    QUEUE_DELAY = 1000,        -- ms de retard (0 = síncron, segur però lent)
    ON_FAILURE = CONTINUE      -- CONTINUE o SHUTDOWN (si no pot escriure el log)
);

-- Habilitar l'auditoria del servidor
ALTER SERVER AUDIT AuditEmpresa WITH (STATE = ON);

-- Pas 2: Crear especificació a nivell de servidor
CREATE SERVER AUDIT SPECIFICATION AuditServer_Logins
FOR SERVER AUDIT AuditEmpresa
ADD (FAILED_LOGIN_GROUP),           -- Intents de login fallits
ADD (SUCCESSFUL_LOGIN_GROUP),       -- Logins correctes
ADD (LOGOUT_GROUP),                 -- Logouts
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),  -- Canvis de rols
ADD (LOGIN_CHANGE_PASSWORD_GROUP)   -- Canvis de contrasenya
WITH (STATE = ON);

-- Pas 3: Crear especificació a nivell de base de dades
USE empresa;

CREATE DATABASE AUDIT SPECIFICATION AuditDB_Empresa
FOR SERVER AUDIT AuditEmpresa
ADD (SELECT ON dbo.empleats BY public),     -- SELECT sobre taula sensible
ADD (INSERT, UPDATE, DELETE ON dbo.comandes BY public),  -- DML sobre comandes
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),    -- Canvis de rols a la BD
ADD (SCHEMA_OBJECT_CHANGE_GROUP)            -- DDL (CREATE, ALTER, DROP)
WITH (STATE = ON);

-- Consultar els logs d'auditoria
SELECT
    event_time,
    action_id,
    succeeded,
    server_principal_name AS usuari,
    database_name,
    object_name,
    statement
FROM sys.fn_get_audit_file('C:\AuditLogs\AuditEmpresa_*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;

-- Oracle: Unified Auditing (des d'Oracle 12c)
-- Verificar si Unified Auditing està actiu
SELECT value FROM v$option WHERE parameter = 'Unified Auditing';

-- Habilitar auditoria unificada (requereix recompilació del binari)
-- Per a entorns nous, ja ve activada per defecte a 12c+.

-- Crear polítiques d'auditoria

-- Política 1: auditar tots els intents de login
CREATE AUDIT POLICY pol_logins
ACTIONS
    LOGON,
    LOGOFF
WHENEVER NOT SUCCESSFUL;  -- Opcional: només fallits

-- Política 2: auditar DDL sobre taules
CREATE AUDIT POLICY pol_ddl
ACTIONS
    CREATE TABLE,
    DROP TABLE,
    ALTER TABLE,
    CREATE VIEW,
    DROP VIEW,
    CREATE INDEX,
    DROP INDEX;

-- Política 3: auditar DML sobre taula sensible
CREATE AUDIT POLICY pol_empleats_sensible
ACTIONS
    SELECT ON empresa.empleats,
    INSERT ON empresa.empleats,
    UPDATE ON empresa.empleats,
    DELETE ON empresa.empleats;

-- Política 4: auditar canvis de privilegis
CREATE AUDIT POLICY pol_privilegis
ACTIONS
    GRANT,
    REVOKE,
    ALTER USER,
    CREATE USER,
    DROP USER;

-- Habilitar les polítiques
AUDIT POLICY pol_logins;
AUDIT POLICY pol_ddl;
AUDIT POLICY pol_empleats_sensible;
AUDIT POLICY pol_privilegis;

-- Habilitar auditoria per a un usuari concret
AUDIT POLICY pol_empleats_sensible BY app_vendes;

-- Verificar polítiques actives
SELECT policy_name, enabled_option, success, failure
FROM audit_unified_enabled_policies
ORDER BY policy_name;

-- Consultar els registres d'auditoria
SELECT
    event_timestamp,
    dbusername,
    action_name,
    object_schema,
    object_name,
    sql_text,
    return_code
FROM unified_audit_trail
WHERE event_timestamp > SYSDATE - 1
ORDER BY event_timestamp DESC;

Localització i format dels logs d'auditoria

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Localització dels logs de PostgreSQL
-- Configuració a postgresql.conf:
-- log_directory = '/var/log/postgresql'
-- log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
-- log_rotation_age = 1d
-- log_rotation_size = 100MB

-- Consultar configuració actual des de psql
SHOW log_directory;
SHOW log_filename;
SHOW logging_collector;

-- Format d'una línia de log pgaudit:
-- 2024-11-15 14:23:45 CET [12345]: [1-1] user=app_vendes,db=empresa,
-- app=psql,client=127.0.0.1 LOG:  AUDIT: SESSION,1,1,READ,SELECT,TABLE,
-- public.empleats,"SELECT * FROM empleats",<not logged>

-- Veure logs des de la vista de sistema (PostgreSQL 15+)
SELECT * FROM pg_log_backend_memory_contexts;

-- Llegir el log actual des de SQL (requereix pg_read_file)
SELECT *
FROM pg_read_file(
    pg_current_logfile(),
    0,
    10000
);

-- Localització dels logs
SHOW VARIABLES LIKE 'general_log_file';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'server_audit_file_path';

-- Format d'una línia del log general de MySQL:
-- 2024-11-15T14:23:45.123456Z    42 Query    SELECT * FROM empleats

-- Format JSON del MariaDB Audit Plugin (server_audit_output_type=syslog):
-- {"timestamp":"2024-11-15 14:23:45","user":"app_vendes[app_vendes]
-- @ [192.168.1.10]","host":"192.168.1.10","connection_id":42,
-- "query_id":100,"operation":"QUERY","database":"empresa",
-- "object":"","retcode":0}

-- Consultar fitxer de log des de MySQL (requereix FILE_PRIV)
-- SELECT LOAD_FILE('/var/log/mysql/mariadb_audit.log');

-- Consultar logs d'auditoria des de T-SQL
SELECT TOP 100
    event_time,
    server_principal_name AS login,
    database_name,
    schema_name,
    object_name,
    action_id,
    succeeded,
    statement
FROM sys.fn_get_audit_file(
    'C:\AuditLogs\AuditEmpresa*.sqlaudit',
    DEFAULT,
    DEFAULT
)
ORDER BY event_time DESC;

-- Consultar via el visor d'events de SQL Server Management Studio:
-- Clic dret a "Security > Audits > AuditEmpresa > View Audit Logs"

-- Veure l'estat de les auditories actives
SELECT
    a.name AS audit_name,
    a.type_desc AS tipus,
    a.on_failure_desc,
    a.is_state_enabled,
    f.path AS ruta_log
FROM sys.server_audits a
LEFT JOIN sys.server_file_audits f ON a.audit_id = f.audit_id;

-- Oracle: Unified Audit Trail emmagatzemat a la BD (taula SYS.AUD$)
-- o a fitxers XML (si es configura així)

-- Consultar ubicació i gestió del trail
SELECT parameter, value FROM v$parameter
WHERE name IN ('audit_trail', 'audit_file_dest');

-- Estat de les polítiques d'auditoria
SELECT policy_name, enabled_option, success, failure
FROM audit_unified_enabled_policies
ORDER BY policy_name;

-- Neteja del trail (gestió d'espai)
EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
    DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    FALSE  -- TRUE elimina fins la data de tall; FALSE elimina tot
);

-- Configurar retenció automàtica
EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    SYSTIMESTAMP - INTERVAL '90' DAY
);

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- A postgresql.conf, assegureu-vos que:
-- log_connections = on
-- log_disconnections = on
-- log_failed_connections = on  (PostgreSQL 14+, o usar log_connections)

-- Amb pgaudit:
ALTER DATABASE empresa SET pgaudit.log = 'connection';

-- El log mostrarà línies com:
-- LOG: connection received: host=192.168.1.50 port=54321
-- FATAL: password authentication failed for user "app_vendes"
-- DETAIL: Connection matched pg_hba.conf line 85: ...

-- Script per analitzar intents fallits al log:
-- grep "authentication failed" /var/log/postgresql/postgresql-*.log | \
-- awk '{print $1, $2, $NF}' | sort | uniq -c | sort -rn | head -20

-- Habilitar log d'errors (inclou intents fallits per defecte)
SHOW VARIABLES LIKE 'log_error';

-- MariaDB Audit Plugin: registrar CONNECT events
SET GLOBAL server_audit_events = 'CONNECT';

-- El log mostrarà:
-- 20241115 14:23:45,server1,app_vendes,192.168.1.50,42,0,FAILED_CONNECT,,,0

-- Consultar intents fallits des de performance_schema (MySQL 8+)
SELECT
    event_name,
    COUNT_STAR AS total,
    SUM_ERRORS AS errors
FROM performance_schema.events_statements_summary_by_digest
WHERE event_name = 'statement/sql/error'
ORDER BY COUNT_STAR DESC
LIMIT 20;

-- Comptes bloquejats per intents fallits
SELECT User, Host, account_locked, password_expired
FROM mysql.user
WHERE account_locked = 'Y';

-- Via SQL Server Audit (configurat anteriorment)
-- Els intents fallits es registren automàticament amb FAILED_LOGIN_GROUP

-- Consultar intents de login fallits recents
SELECT
    event_time,
    server_principal_name AS login_intentat,
    client_ip,
    application_name,
    statement
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE action_id = 'LGIF'  -- LGIF = Login Failed
   OR action_id = 'LGF'
ORDER BY event_time DESC;

-- Alternativa: visor d'events de Windows (SQL Server escriu al Windows Event Log)
-- Via xp_readerrorlog
EXEC sp_readerrorlog 0, 1, 'Login failed';

-- Oracle: auditoria de logins fallits ja configurada a pol_logins

-- Consultar intents de login fallits (últimes 24 hores)
SELECT
    event_timestamp,
    dbusername,
    userhost AS host_client,
    os_username,
    action_name,
    return_code,
    authentication_type
FROM unified_audit_trail
WHERE action_name IN ('LOGON', 'LOGOFF')
  AND return_code != 0  -- Codis != 0 indiquen error
  AND event_timestamp > SYSDATE - 1
ORDER BY event_timestamp DESC;

-- Codis d'error habituals:
-- 1017: ORA-01017 invalid username/password
-- 28000: ORA-28000 account is locked
-- 28001: ORA-28001 password has expired

-- Verificar comptes amb molts errors de login
SELECT
    dbusername,
    COUNT(*) AS intents_fallits
FROM unified_audit_trail
WHERE action_name = 'LOGON'
  AND return_code != 0
  AND event_timestamp > SYSDATE - 7
GROUP BY dbusername
HAVING COUNT(*) > 10
ORDER BY intents_fallits DESC;

Exemple: auditar SELECT sobre taula sensible

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Configurar pgaudit per a auditoria d'objectes concrets
-- A postgresql.conf:
-- pgaudit.role = 'auditor'

-- Concedir el rol auditor sobre la taula sensible
GRANT SELECT ON empleats TO auditor;

-- Ara, QUALSEVOL usuari que faci SELECT sobre empleats
-- quedarà registrat al log de PostgreSQL.

-- Format del log:
-- LOG: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.empleats,
-- "SELECT * FROM empleats WHERE departament = 'RRHH'",<not logged>

-- Auditoria selectiva per a un usuari concret
ALTER USER app_vendes SET pgaudit.log = 'read';
ALTER USER analista SET pgaudit.log = 'read,write';

-- Verificar configuració per usuari
SELECT rolname, rolconfig
FROM pg_roles
WHERE rolconfig IS NOT NULL AND rolconfig::TEXT LIKE '%pgaudit%';

-- MariaDB Audit Plugin: auditar consultes DML sobre taules concretes
SET GLOBAL server_audit_events = 'QUERY_DML,TABLE';
-- TABLE: registra accés a taules (quin usuari accedeix a quina taula)
-- QUERY_DML: registra INSERT, UPDATE, DELETE, REPLACE, SELECT

-- Per filtrar per taules concretes cal usar server_audit_query_log_limit
-- o post-processar el log amb eines externes (Logstash, Elasticsearch...).

-- Consultar les taules més accedides (performance_schema)
SELECT
    object_schema,
    object_name,
    count_read,
    count_write,
    count_fetch
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema = 'empresa'
ORDER BY count_read DESC;

-- Afegir auditoria de SELECT sobre la taula empleats a l'especificació
USE empresa;

ALTER DATABASE AUDIT SPECIFICATION AuditDB_Empresa
WITH (STATE = OFF);

ALTER DATABASE AUDIT SPECIFICATION AuditDB_Empresa
ADD (SELECT ON dbo.empleats BY public);

ALTER DATABASE AUDIT SPECIFICATION AuditDB_Empresa
WITH (STATE = ON);

-- Consultar qui ha accedit a la taula empleats
SELECT
    event_time,
    server_principal_name AS usuari,
    action_id,
    object_name,
    statement,
    client_ip
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
WHERE object_name = 'empleats'
  AND action_id = 'SL'  -- SL = SELECT
ORDER BY event_time DESC;

-- Auditoria de SELECT sobre empleats (ja configurada a pol_empleats_sensible)

-- Consultar accesos a la taula empleats
SELECT
    event_timestamp,
    dbusername,
    os_username,
    userhost,
    action_name,
    object_name,
    sql_text,
    return_code
FROM unified_audit_trail
WHERE object_name = 'EMPLEATS'
  AND action_name = 'SELECT'
  AND event_timestamp > SYSDATE - 30
ORDER BY event_timestamp DESC;

-- Estadístiques d'accés per usuari (últim mes)
SELECT
    dbusername,
    COUNT(*) AS num_consultes,
    MIN(event_timestamp) AS primera_consulta,
    MAX(event_timestamp) AS ultima_consulta
FROM unified_audit_trail
WHERE object_name = 'EMPLEATS'
  AND action_name = 'SELECT'
  AND event_timestamp > SYSDATE - 30
GROUP BY dbusername
ORDER BY num_consultes DESC;

Gestió de l'espai dels logs d'auditoria

L'auditoria intensiva consumeix molt espai en disc

Registrar totes les consultes SQL en un sistema de producció actiu pot generar gigabytes de logs per dia. Heu de planejar cuidadosament:

Granularitat de l'auditoria: no cal auditar tot. Centreu-vos en taules sensibles, operacions DDL i events de seguretat (logins fallits, canvis de privilegis).
Rotació de logs: configureu la rotació automàtica (per data o per mida) i l'arxivament a sistemes externs (S3, NAS, SIEM).
Retenció: definiu un perióde de retenció clar (habitualment 90 dies en línia, 1-3 anys en arxiu fred), seguint la normativa aplicable.
Rendiment: l'auditoria síncron (QUEUE_DELAY = 0 a SQL Server, pgaudit.log_level = error a PostgreSQL) pot impactar el rendiment. Useu mode asíncron en producció.
Integritat dels logs: els logs d'auditoria han de ser immutables. Emmagatzemeu-los en un sistema separat on els usuaris de base de dades no tinguin accés d'escriptura.

Miniactivitat — AC0307 · Auditoria

Miniactivitat — AC0307

Treballeu sobre la vostra instància Docker de PostgreSQL (o MariaDB si preferiu).

Tasca 1 — Configuració de pgaudit (PostgreSQL):

Instal·leu l'extensió pgaudit al contenidor Docker (afegiu-la al Dockerfile o instal·leu-la manualment).
Configureu pgaudit.log = 'read,write,ddl,connection' a postgresql.conf.
Reinicieu el servidor i verifiqueu que l'extensió és activa.

Tasca 2 — Generació d'events auditables:

Executeu les operacions següents i verifiqueu que queden al log:

Intents de connexió amb contrasenya incorrecta (3 intents).
SELECT * FROM empleats amb un usuari de proves.
INSERT INTO comandes ... amb un usuari de proves.
DROP TABLE amb un usuari que no té permís (ha de fallar).
GRANT SELECT ON empleats TO algu_nou.

Tasca 3 — Anàlisi del log:

Localitzeu el fitxer de log de PostgreSQL.
Cerqueu les línies corresponents a les operacions de la Tasca 2.
Identifiqueu les parts de cada línia: timestamp, usuari, base de dades, tipus d'operació.

Tasca 4 — Reflexió escrita:

Responeu les preguntes següents (5-10 línies):

Quins riscos comporta guardar els logs d'auditoria a la mateixa màquina que la base de dades?
Quin impacte en el rendiment creieu que té activar pgaudit.log = 'all'?
En quin cas activaríeu l'auditoria de SELECT i en quin no?

Lliurament: script de configuració + captures del log + respostes escrites.

Auditoria de bases de dades

Per qué cal auditar?

Mecanismes d'auditoria nadius per SGBD

Localització i format dels logs d'auditoria

Exemple: auditar intents de login fallits

Exemple: auditar SELECT sobre taula sensible

Gestió de l'espai dels logs d'auditoria

Miniactivitat — AC0307 · Auditoria