Auditoria

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

Introducció¶

L'auditoria d'una base de dades consisteix a registrar les operacions que duen a terme els usuaris dins del sistema gestor. Gràcies a això, l'administrador de la base de dades pot conéixer amb detall com s'utilitza la informació i quines accions fa cada usuari.

Es tracta d'una mesura de seguretat passiva, ja que normalment s'utilitza quan ja s'ha produït un accés indegut o una operació incorrecta, per identificar el responsable o determinar quina acció ha provocat l'error. És a dir, l'auditoria no impedeix aquests accessos o operacions no autoritzades, sinó que permet analitzar què ha ocorregut per aplicar les mesures correctores adequades.

Mitjançant l'auditoria és possible detectar diversos tipus d'anomalies, com ara:

Accessos no autoritzats al sistema: accessos d'usuaris que no haurien de tindre permís o que es fan en franges horàries inusuals.
Atacs de desbordament de memòria (buffer overflow): consisteixen a introduir més dades de les que un programa espera, cosa que pot permetre a un atacant escriure en zones de memòria no reservades.
Injecció de codi SQL: atac que consisteix a inserir ordres no autoritzades dins del codi que executen les aplicacions que accedeixen a la base de dades.

Segons el detall de la informació que es registra, es distingeixen dos nivells d'auditoria:

A nivell de sessió: permet obtindre informació de cada sessió, com ara els usuaris connectats a les bases de dades, les taules a les quals accedeixen o el lloc des d'on s'han connectat.
A nivell d'objectes: és un nivell més detallat, que permet conéixer els accessos i les operacions efectuades sobre objectes concrets de la base de dades, habitualment taules o columnes. Aquest tipus d'auditoria permet saber quins usuaris accedeixen a determinats objectes, en quin moment ho fan i quines operacions fan. Per això, és especialment útil per aplicar polítiques de control d'accés a dades sensibles, com les protegides per la LOPD.

La informació d'auditoria es pot guardar tant en fitxers com en taules del diccionari de dades del mateix sistema gestor. Quan s'emmagatzema en el diccionari de dades, cada accés a un objecte auditat es registra com una fila en una taula, cosa que facilita la consulta i la revisió de la informació.

No obstant això, aquest mètode implica una càrrega més gran per al sistema gestor i un consum elevat d'espai, especialment si la política d'auditoria és molt restrictiva. Per això, és recomanable limitar els objectes auditats per no comprometre el rendiment ni els recursos del sistema.

La informació generada per l'auditoria no s'ha d'emmagatzemar mai en el mateix disc on es troben el sistema operatiu del servidor o el SGBD.

Si el sistema gestor patira un atac i registrara tots els intents d'accés, podria arribar a ocupar tot l'espai disponible en el disc. En aquest cas, fins i tot seria impossible iniciar sessió al servidor per dur a terme les tasques de correcció necessàries.

L'opció més segura és utilitzar un disc independent per emmagatzemar la informació d'auditoria o bé sistemes d'emmagatzematge en xarxa.

Auditoria en Oracle DB¶

L'auditoria tradicional d'Oracle DB s'activa mitjançant el paràmetre AUDIT_TRAIL, que indica on es guarden els registres d'auditoria. El valor actual es pot consultar a la vista v$parameter:

SELECT name, value FROM v$parameter WHERE name = 'audit_trail';

Valors de AUDIT_TRAIL

Valor	Significat
`NONE`	L'auditoria està desactivada
`OS`	Els esdeveniments es redirigeixen al sistema operatiu, que s'encarrega de gestionar-los
`DB`	Els registres es guarden en la taula del diccionari de dades `SYS.AUD$`
`XML`	Els registres es guarden en fitxers XML en el directori indicat pel paràmetre `AUDIT_FILE_DEST`

Com que és un paràmetre estàtic, es modifica amb SCOPE=SPFILE i cal reiniciar la base de dades perquè tinga efecte:

ALTER SYSTEM SET AUDIT_TRAIL = 'NONE'|'DB'|'OS'|'XML' SCOPE=SPFILE;

Les accions de l'usuari SYS no es registren en els registres d'auditoria.

Oracle DB ofereix tres ferramentes principals per gestionar l'auditoria:

AUDIT: permet auditar els accessos al sistema, les accions de la base de dades i les operacions sobre objectes. A més, distingeix entre operacions correctes i incorrectes, de manera que es poden registrar, per exemple, només els intents fallits d'inici de sessió.
Paquet DBMS_FGA (Fine Grained Auditing): permet una auditoria més detallada, a nivell de fila i de columna. És especialment útil per auditar dades sensibles, ja que permet definir regles específiques per a cada taula o columna.
Auditoria unificada: introduïda en Oracle 12c, permet crear polítiques d'auditoria que es poden aplicar a múltiples objectes i operacions. Simplifica la gestió de l'auditoria i millora el rendiment del sistema.

Documentació oficial: Introduction to Auditing – Oracle DB

Auditoria tradicional (`AUDIT` i `NOAUDIT`)¶

L'ordre AUDIT activa l'auditoria tradicional per a tres tipus diferents d'accions: els inicis de sessió, les accions generals sobre un tipus d'objecte i les operacions sobre objectes concrets.

Per utilitzar les ordres AUDIT i NOAUDIT, l'usuari ha de tindre el privilegi AUDIT SYSTEM.

A més, per auditar operacions sobre objectes concrets, l'usuari ha de ser propietari de l'objecte o tindre el privilegi AUDIT ANY.

Documentació oficial: AUDIT (Traditional Auditing) – Oracle DB

Els exemples d'aquesta secció continuen l'un a l'altre: utilitzen el mateix usuari audit_test i la mateixa taula audit_table_test.

Inici de sessió al sistema¶

Per auditar els intents d'inici de sessió, s'utilitza AUDIT SESSION. Es poden limitar a un usuari concret o només als accessos correctes o incorrectes:

AUDIT SESSION [BY <user>] [WHENEVER [NOT] SUCCESSFUL];

Exemple: AUDIT SESSION

Es crea un usuari de prova:

CREATE USER audit_test IDENTIFIED BY audit_test;
GRANT CREATE SESSION TO audit_test;

S'auditen tots els intents d'inici de sessió, tant correctes com incorrectes:
```
AUDIT SESSION;
```

Es comproven les regles d'auditoria configurades:

SELECT * FROM DBA_STMT_AUDIT_OPTS;

USER_NAME   PROXY_NAME    AUDIT_OPTION     SUCCESS     FAILURE
---------   ----------    ------------     -------     -------
(null)      (null)        CREATE SESSION   BY ACCESS   BY ACCESS

S'inicia sessió amb l'usuari AUDIT_TEST:
```
CONNECT audit_test/audit_test;
```

Es consulten els registres d'auditoria a la vista DBA_AUDIT_SESSION:

SELECT username,
       os_username,
       userhost,
       action_name,
       returncode,
       timestamp
FROM DBA_AUDIT_SESSION
WHERE username = 'AUDIT_TEST'
ORDER BY timestamp DESC;

USERNAME    OS_USERNAME  USERHOST        ACTION_NAME     RETURNCODE  TIMESTAMP
----------- ------------ --------------- --------------- ----------- -------------------
AUDIT_TEST  jpuigcerver  fp              LOGON           0           2026-01-15 12:15:01

S'observa que l'inici de sessió s'ha registrat amb RETURNCODE 0, és a dir, correcte.

Accions de la base de dades¶

També es poden auditar accions generals de la base de dades que afecten un tipus d'objecte, com ara la creació de qualsevol taula:

AUDIT <action> ON <object_type> [BY <user>] [WHENEVER [NOT] SUCCESSFUL];

<action>: acció que es vol auditar (CREATE, DROP, ALTER, SELECT, INSERT, UPDATE, DELETE…).
<object_type>: tipus d'objecte de la base de dades (TABLE, VIEW, INDEX, PROCEDURE…).
[BY <user>]: (opcional) usuari al qual s'aplica l'auditoria. Si no s'especifica, s'aplica a tots els usuaris.
[WHENEVER [NOT] SUCCESSFUL]: (opcional) indica si s'auditen només les operacions correctes o només les incorrectes. Si no s'especifica, s'auditen totes dues.

Exemple: AUDIT CREATE TABLE

S'auditen totes les operacions de creació de taules:
```
AUDIT CREATE TABLE;
AUDIT CREATE ANY TABLE;
```

Es comproven les regles d'auditoria configurades:

SELECT * FROM DBA_STMT_AUDIT_OPTS WHERE AUDIT_OPTION LIKE '%TABLE%';

USER_NAME   PROXY_NAME    AUDIT_OPTION         SUCCESS     FAILURE
---------   ----------    ------------         -------     -------
(null)      (null)        CREATE TABLE         BY ACCESS   BY ACCESS
(null)      (null)        CREATE ANY TABLE     BY ACCESS   BY ACCESS

Es dona permís de creació de taules a l'usuari AUDIT_TEST:
```
GRANT CREATE TABLE TO audit_test;
```
Es connecta amb l'usuari AUDIT_TEST:
```
CONNECT audit_test/audit_test;
```

Es crea una taula amb l'usuari AUDIT_TEST:

CREATE TABLE audit_table_test (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50)
);

Es consulten els registres d'auditoria a la vista DBA_AUDIT_TRAIL:

SELECT username,
       obj_name,
       action_name,
       returncode,
       timestamp
FROM DBA_AUDIT_TRAIL
WHERE action_name = 'CREATE TABLE'
ORDER BY timestamp DESC;

USERNAME    OBJ_NAME            ACTION_NAME     RETURNCODE  TIMESTAMP
----------- ------------------  --------------- ----------- -------------------
AUDIT_TEST  AUDIT_TABLE_TEST    CREATE TABLE    0           2026-01-15T12:20:45

S'observa que s'ha registrat la creació de la taula AUDIT_TABLE_TEST.

Operacions sobre objectes¶

Finalment, es poden auditar operacions concretes sobre un objecte determinat, com una taula o una vista:

AUDIT <operation> ON <object_name> [BY <user>] [WHENEVER [NOT] SUCCESSFUL];

<operation>: operació que es vol auditar (SELECT, INSERT, UPDATE, DELETE o ALL).
<object_name>: nom de l'objecte de la base de dades, com ara una taula o una vista.
[BY <user>] i [WHENEVER [NOT] SUCCESSFUL]: (opcionals) igual que en les accions de la base de dades.

Exemple: AUDIT INSERT ON audit_table_test

S'auditen totes les operacions d'inserció a la taula AUDIT_TABLE_TEST:
```
AUDIT INSERT ON audit_table_test;
```
Aquesta operació l'ha de realitzar un usuari amb permisos sobre audit_table_test.

Es comproven les regles d'auditoria configurades:

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, INS
    FROM DBA_OBJ_AUDIT_OPTS
    WHERE object_name = 'AUDIT_TABLE_TEST';

OWNER       OBJECT_NAME        OBJECT_TYPE         INS    
---------   ----------         ------------        ----
SYS         AUDIT_TABLE_TEST   TABLE               A/A

A/A indica Audit All, tant per a operacions correctes com incorrectes.

Es connecta amb l'usuari AUDIT_TEST:
```
CONNECT audit_test/audit_test;
```

S'insereix una fila a la taula AUDIT_TABLE_TEST:

INSERT INTO audit_table_test (id, name) VALUES (1, 'Test Name');

Es consulten els registres d'auditoria a la vista DBA_AUDIT_TRAIL:

SELECT username,
       obj_name,
       action_name,
       returncode,
       timestamp
FROM DBA_AUDIT_TRAIL
WHERE action_name = 'INSERT'
ORDER BY timestamp DESC;

USERNAME    OBJ_NAME            ACTION_NAME     RETURNCODE  TIMESTAMP
----------- ------------------  --------------- ----------- -------------------
AUDIT_TEST  AUDIT_TABLE_TEST    INSERT          0           2026-01-15T12:25:30

S'observa que s'ha registrat la inserció, amb l'usuari que l'ha feta i el moment exacte.

Desactivació de l'auditoria (`NOAUDIT`)¶

Per desactivar l'auditoria, s'utilitza l'ordre NOAUDIT amb la mateixa sintaxi que AUDIT:

NOAUDIT SESSION [BY <user>] [WHENEVER [NOT] SUCCESSFUL];
NOAUDIT <action> ON <object_type> [BY <user>] [WHENEVER [NOT] SUCCESSFUL];
NOAUDIT <operation> ON <object_name> [BY <user>] [WHENEVER [NOT] SUCCESSFUL];

Les regles d'auditoria configurades es poden consultar a la vista DBA_STMT_AUDIT_OPTS:

SELECT * FROM DBA_STMT_AUDIT_OPTS;

Auditoria de gra fi (Fine Grained Auditing)¶

El paquet DBMS_FGA permet crear polítiques d'auditoria amb condicions específiques per auditar operacions sobre taules concretes. Aquestes polítiques poden incloure filtres basats en els valors de les columnes, de manera que només s'auditen les operacions que compleixen certs criteris, com ara els accessos a una taula entre les 21:00 i les 06:00 o en cap de setmana, els accessos des d'una IP de fora de la xarxa corporativa o les consultes sobre una columna amb dades sensibles.

Documentació oficial: Auditing Specific Activities with Fine-Grained Auditing – Oracle DB

Per crear una política d'auditoria de gra fi, s'utilitza el procediment DBMS_FGA.ADD_POLICY:

DBMS_FGA.ADD_POLICY (
    object_schema IN VARCHAR2,
    object_name   IN VARCHAR2,
    policy_name   IN VARCHAR2,
    audit_condition IN VARCHAR2,
    audit_column  IN VARCHAR2,
    statement_types IN VARCHAR2,
    audit_trail   IN VARCHAR2
);

object_schema: esquema on es troba la taula a auditar.
object_name: nom de la taula a auditar.
policy_name: nom de la política d'auditoria.
audit_condition: condició SQL que determina quan s'ha d'auditar l'operació.
audit_column: llista de columnes, separades per comes, que s'han d'auditar.
statement_types: llista d'operacions a auditar (SELECT, INSERT, UPDATE, DELETE).
audit_trail: on s'emmagatzemaran els registres d'auditoria (DB, OS, XML).

Documentació oficial: Creating a Fine-Grained Audit Policy – Oracle DB

Exemple: Auditoria de gra fi amb DBMS_FGA

Es crea una taula de prova des de l'usuari audit_test:

CREATE TABLE fga_audit_test (
    id NUMBER PRIMARY KEY,
    sensitive_data VARCHAR2(100)
);

S'afig una política d'auditoria que registre totes les operacions SELECT que accedisquen a la columna sensitive_data:

BEGIN
    DBMS_FGA.ADD_POLICY (
        object_schema => 'audit_test',
        object_name   => 'FGA_AUDIT_TEST',
        policy_name   => 'SENSITIVE_DATA_AUDIT',
        audit_condition => NULL,
        audit_column  => 'SENSITIVE_DATA',
        statement_types => 'SELECT'
    );
END;
/

Es comprova que la política s'ha creat correctament:
```
SELECT * FROM DBA_AUDIT_POLICIES;
```
Es fa una consulta SELECT sobre la taula FGA_AUDIT_TEST:
```
SELECT * FROM fga_audit_test;
```

Es consulten els registres d'auditoria a la vista DBA_FGA_AUDIT_TRAIL:

SELECT DB_USER, OS_USER, USERHOST, TIMESTAMP, OBJECT_SCHEMA, OBJECT_NAME, SQL_TEXT
    FROM DBA_FGA_AUDIT_TRAIL
    WHERE object_name = 'FGA_AUDIT_TEST'
    ORDER BY timestamp DESC;

Les regles d'auditoria de gra fi es poden consultar a la vista DBA_AUDIT_POLICIES:

SELECT * FROM DBA_AUDIT_POLICIES;

Les regles es poden eliminar amb el procediment DBMS_FGA.DROP_POLICY. A més, es poden desactivar i reactivar amb els procediments DBMS_FGA.DISABLE_POLICY i DBMS_FGA.ENABLE_POLICY, respectivament.

Auditoria unificada¶

L'auditoria unificada permet crear polítiques d'auditoria que inclouen diverses accions i que es poden aplicar a múltiples objectes o usuaris. D'aquesta manera, en lloc de gestionar moltes regles AUDIT soltes, s'agrupen en una sola política amb nom.

Per crear una política d'auditoria unificada, s'utilitza l'ordre CREATE AUDIT POLICY:

CREATE AUDIT POLICY <policy_name>
    ACTIONS <action_list>
    [WHENEVER [NOT] SUCCESSFUL]
    [ROLES <role_list>];

<policy_name>: nom de la política d'auditoria.
<action_list>: llista d'accions a auditar (CREATE TABLE, DROP TABLE, SELECT ON schema.table…).
[ROLES <role_list>]: (opcional) llista de rols als quals s'aplica la política.

Una vegada creada la política, cal activar-la amb l'ordre AUDIT POLICY:

AUDIT POLICY <policy_name>
    [WHENEVER [NOT] SUCCESSFUL]
    [BY USER];

[WHENEVER [NOT] SUCCESSFUL]: (opcional) indica si s'auditen només les operacions correctes o només les incorrectes.
[BY USER]: (opcional) indica l'usuari sobre el qual s'aplicarà la política.

Exemple: Auditoria unificada

Es crea una política d'auditoria unificada que registre totes les operacions de creació i eliminació de taules:
```
CREATE AUDIT POLICY table_management_audit
    ACTIONS CREATE TABLE, DROP TABLE;
```

S'activa la política d'auditoria per a tots els usuaris:

AUDIT POLICY table_management_audit WHENEVER SUCCESSFUL;

Es comproven les regles d'auditoria configurades:

SELECT * FROM AUDIT_UNIFIED_POLICIES;

POLICY_NAME               ENABLED
------------------------  -------
TABLE_MANAGEMENT_AUDIT    YES

Es connecta amb l'usuari AUDIT_TEST:
```
CONNECT audit_test/audit_test;
```

Es crea i s'elimina una taula per generar registres d'auditoria:

CREATE TABLE unified_audit_test (id NUMBER);
DROP TABLE unified_audit_test;

Es consulten els registres d'auditoria a la vista UNIFIED_AUDIT_TRAIL:

SELECT DBUSERNAME,
       OBJECT_NAME,
       ACTION_NAME,
       RETURN_CODE,
       EVENT_TIMESTAMP
FROM UNIFIED_AUDIT_TRAIL
ORDER BY EVENT_TIMESTAMP DESC;

DBUSERNAME  OBJECT_NAME             ACTION_NAME     RETURN_CODE EVENT_TIMESTAMP
----------- ----------------------  --------------- ----------- -------------------
AUDIT_TEST  UNIFIED_AUDIT_TEST      DROP TABLE      0           2026-01-15T12:55:00
AUDIT_TEST  UNIFIED_AUDIT_TEST      CREATE TABLE    0           2026-01-15T12:54:45

S'observa que la política registra tant la creació com l'eliminació de la taula.

Per eliminar una política d'auditoria unificada, s'utilitza l'ordre DROP AUDIT POLICY:

DROP AUDIT POLICY <policy_name>;

Les polítiques d'auditoria unificada es poden consultar a la vista AUDIT_UNIFIED_POLICIES:

SELECT * FROM AUDIT_UNIFIED_POLICIES;

Resum: registres d'auditoria en el diccionari de dades¶

Els registres d'auditoria en Oracle DB es poden consultar a través de diverses vistes del diccionari de dades, segons el tipus d'auditoria que s'haja configurat.

`AUDIT` i `NOAUDIT`¶

Vista	Descripció
`DBA_STMT_AUDIT_OPTS`	Regles d'auditoria configurades amb `AUDIT` i `NOAUDIT`.
`DBA_OBJ_AUDIT_OPTS`	Regles d'auditoria sobre objectes específics.
`DBA_AUDIT_SESSION`	Registres d'inici de sessió i tancament de sessió.
`DBA_AUDIT_OBJECT`	Registres d'operacions sobre objectes específics.
`DBA_AUDIT_TRAIL`	Registres generals d'auditoria.

Auditoria de gra fi (Fine Grained Auditing)¶

Vista	Descripció
`DBA_AUDIT_POLICIES`	Polítiques d'auditoria de gra fi configurades.
`DBA_FGA_AUDIT_TRAIL`	Registres d'auditoria de gra fi.

Auditoria unificada¶

Vista	Descripció
`AUDIT_UNIFIED_POLICIES`	Polítiques d'auditoria unificada configurades.
`UNIFIED_AUDIT_TRAIL`	Registres d'auditoria unificada

📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web.

🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.

Auditoria

Introducció¶

Auditoria en Oracle DB¶

Auditoria tradicional (AUDIT i NOAUDIT)¶

Inici de sessió al sistema¶

Accions de la base de dades¶

Operacions sobre objectes¶

Desactivació de l'auditoria (NOAUDIT)¶

Auditoria de gra fi (Fine Grained Auditing)¶

Auditoria unificada¶

Resum: registres d'auditoria en el diccionari de dades¶

AUDIT i NOAUDIT¶

Auditoria de gra fi (Fine Grained Auditing)¶

Auditoria unificada¶

Auditoria tradicional (`AUDIT` i `NOAUDIT`)¶

Desactivació de l'auditoria (`NOAUDIT`)¶

`AUDIT` i `NOAUDIT`¶