Mòdul ASGBD

ASGBD - Mecanismes de Seguretat

🔐 Encriptació en Oracle

Què és i per què encriptar?

L’encriptació converteix dades llegibles en format codificat (xifrat) que només pot ser desxifrat amb una clau. És essencial per protegir informació sensible.

Una altra manera d'encriptar és fer un HASH (Hashing). Objectiu: generar una empremta única i irreversible d’un text. Ús típic: Guardar contrasenyes de forma segura, Verificar integritat de fitxers, Firmes digitals.

Raons per aplicar encriptació:

⚠️ Possibles inconvenients:

🛠️ Tipus d’encriptació a Oracle

DBMS_CRYPTO (xifratge manual)

Paquet per fer xifrat, hash i autenticació.

Fora del paquet DBMS_CRYPTO la funció STANDARD_HASH és una funció incorporada en PL/SQL, com SYSDATE, i permet calcular HASH més fàcilment

Exemple: fer un hash SHA-1 d’una contrasenya

(Les funcions HASH no xifren, sols resumeixen. No es poden desxifrar)

Exemple d'ús de la funció STANDARD_HASH, incorparada en PL/SQL 

SELECT STANDARD_HASH('contrasenya', 'SHA1') FROM dual;   --molt dèbil actualment  
-- altre tipus de hash  
SELECT STANDARD_HASH('contrasenya', 'SHA256') FROM dual;

STANDARD_HASH soporta tots els algorismes moderns (MD5, SHA1, SHA256, SHA384, SHA512, SHA3_256, SHA3_512…)

També es pot fer amb la funcio HASH del paquet DBMS_CRYPTO

SELECT RAWTOHEX(  DBMS_CRYPTO.HASH(HEXTORAW('1231'),3) ) FROM dual;
SELECT RAWTOHEX(  DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW('SECRETA'),4) ) FROM dual;  
-- On el num del segon parametre =>  type 3:SHA-1  type 4:SHA256  type 5:SHA384  type 6:SHA512

Per fer una bona gestió de hash de contrasenyes, cal combinar: Usar SHA-512, Afegir salt, i Afegir iteracions (hash lento), o utilitzar funcions externes, des de la capa d'aplicació , com bcrypt, scrypt, argon2 o PBKDF2. Funcions pensades especialment per contrasenyes !

Exemple de xifrat amb AES

SELECT RAWTOHEX(
         DBMS_CRYPTO.ENCRYPT(
           src => UTL_RAW.cast_to_raw('Text secret'),
           typ => 4354, -- AES_CBC_PKCS5
           key => UTL_RAW.cast_to_raw('0123456789ABCDEF0123456789ABCDEF'),
           iv  => UTL_RAW.cast_to_raw('ABCDEF9876543210')
         )
       ) AS text_encriptat_hex
FROM dual;

Oracle determina automàticament quina versió d’AES s’ha d’aplicar segons la longitud del RAW 
  que s'envie al paràmetre key (Clau 16 bytes → AES-128 , Clau 24 bytes → AES-192 , Clau 32 bytes → AES-256)

Convertir entre formats:

💾 TDE – Transparent Data Encryption

Xifrat automàtic a nivell de disc. No cal modificar el codi de les aplicacions.

Components:

🔁 Modalitats:

Bones pràctiques

Conclusió

L’encriptació és essencial per a la protecció de dades en entorns reals i és un requisit legal i de bones pràctiques. Oracle ofereix solucions potents com TDE i DBMS_CRYPTO que cobreixen necessitats tècniques i legals.

🔐 Una bona gestió de claus, un ús moderat del xifrat i l’auditoria d’accés són claus per garantir seguretat real.

🕵️ Auditoria en Oracle

📘 Què és una auditoria?

L’auditoria és una funcionalitat dels sistemes gestors de bases de dades que permet registrar i controlar l’activitat dels usuaris. És especialment útil per motius de seguretat, detecció d’errors, traçabilitat i compliment normatiu (ex. RGPD).

🎯 Objectius de l’auditoria

⚙️ Tipus d’auditoria en Oracle

🔎 Diferències:

CaracterísticaEstàndardUnificada
ImplementacióBases separades (AUD$)Totes les auditories integrades
Activa per defecteNoSí, des de 12c
Control granularLimitatMolt flexible
Millor rendimentNoSí

🛠️ Activar auditoria estàndard (tradicional)

Des d'Oracle23ai no es pot fer servir. (deprecated)

🛠️ Unified Audit (Oracle 12c+)

Unifica tots els registres d’auditoria: privilegis, ordres, errors, logons... S’activa amb ENABLE_UNIFIED_AUDIT = TRUE al paràmetre de sistema. 

SELECT VALUE
FROM V$OPTION
WHERE PARAMETER = 'Unified Auditing';   -- per saber si esta activa

Modes de unified

Mixed Mode: pots usar AUDIT SESSION i Unified Auditing alhora
Only Mode: només Unified Auditing funciona; els antics comandos AUDIT estan bloquejats

Exemple de política unificada:

CREATE AUDIT POLICY aud_logins
  ACTIONS LOGON;

AUDIT POLICY aud_logins;    --   Audita logins d'usuaris

CREATE AUDIT POLICY acces_alumnes
  ACTIONS SELECT ON alumnes;

AUDIT POLICY acces_alumnes;      --   Audita selects a una taula (de qualsevol usuari)

AUDIT POLICY acces_alumnes BY usuari1,usuari2;  --   Audita selects a una taula (per part de usuari1 o usuari2)

📦 Vistes d’auditoria unificada

Com vore les regles creades 

SELECT policy_name, audit_option, condition_eval_opt, audit_condition
FROM audit_unified_policies 
ORDER BY policy_name;

Com vore les regles actives

SELECT * FROM AUDIT_UNIFIED_ENABLED_POLICIES;

Com vore els registres de l'auditoria

SELECT EVENT_TIMESTAMP, DBUSERNAME, USERHOST, ACTION_NAME
      FROM UNIFIED_AUDIT_TRAIL
      WHERE OBJECT_NAME = 'ALUMNES';
      ORDER BY EVENT_TIMESTAMP DESC;   
-- Deshabilitar primer
NOAUDIT POLICY acces_alumnes;

-- Eliminar la política
DROP AUDIT POLICY nom_politica;

Consells de seguretat i gestió

Conclusió

L’auditoria és una eina potent per a la seguretat i el control de bases de dades Oracle. Permet identificar comportaments sospitosos, controlar accés no autoritzat i mantenir la traçabilitat de les operacions. A més, és una pràctica recomanada per normatives legals i de qualitat.

🧬 Integritat en bases de dades Oracle

📘 Què és la integritat?

La integritat d’una base de dades fa referència al conjunt de normes i restriccions que garanteixen que les dades emmagatzemades siguen coherents, correctes i fiables. Aquesta integritat és fonamental per garantir la qualitat de la informació i el correcte funcionament de les aplicacions que la utilitzen.

Tipus d’integritat

Integritat d'entitat

Es garanteix mitjançant l’ús de PRIMARY KEY, que assegura que cada fila siga única i no nul·la. 

CREATE TABLE alumnes (
  id NUMBER PRIMARY KEY,
  nom VARCHAR2(50)
);

Integritat referencial

Manté la coherència entre dues taules relacionades mitjançant una FOREIGN KEY. Evita que una fila faci referència a un valor inexistent. 

CREATE TABLE cursos (
  id NUMBER PRIMARY KEY,
  nom VARCHAR2(50)
);

CREATE TABLE alumnes (
  id NUMBER PRIMARY KEY,
  nom VARCHAR2(50),
  id_curs NUMBER,
  FOREIGN KEY (id_curs) REFERENCES cursos(id)
);

Amb això, no es pot assignar un id_curs a un alumne que no existeixi a la taula cursos.

Integritat de domini

Limita els valors permesos en una columna. Es pot implementar amb tipus de dades, restriccions NOT NULL, CHECK o DEFAULT. 

CREATE TABLE alumnes (
  id NUMBER PRIMARY KEY,
  nom VARCHAR2(50) NOT NULL,
  edat NUMBER CHECK (edat >= 16),
  pais VARCHAR2(30) DEFAULT 'Espanya'
);

Això garanteix que nom sempre tindrà valor, edat siga ≥ 16 i pais tindrà un valor per defecte.

Integritat d’usuari

No s’implementa a nivell de base de dades, sinó via aplicacions o triggers. Controla normes específiques com:

Aquest tipus d’integritat sovint es basa en PL/SQL o lògica de negoci a l’aplicació.

Conclusió

Mantenir la integritat de les dades és essencial per garantir que el sistema siga fiable, coherent i funcional. Oracle ofereix múltiples mecanismes per implementar integritat a nivell físic, lògic i d’aplicació.

🔐 “La seguretat comença amb la confiança en les dades. I la confiança es construeix amb integritat.”

Transaccions en Oracle

📘 Què és una transacció?

Una transacció és una seqüència d’operacions SQL (normalment de manipulació de dades: INSERT, UPDATE, DELETE) que es tracten com una unitat indivisible de treball. L’objectiu d’una transacció és assegurar que les dades es mantinguin coherents i amb integritat.

Una transacció, o es fa tota o no es fa res, però no pot en cap cas quedar a mitges, una part feta i una altra no.

Totes les operacions que en formen part han de completar-se correctament. Si alguna falla, cap de les altres no ha de tenir efecte.

Els interbloquejos ( deadlocks ) són un problema que les transaccions i els mecanismes de control de concurrència intenten evitar o gestionar

Un interbloqueig passa quan dues o més transaccions es bloquegen mútuament, cadascuna esperant un recurs (normalment un registre o una taula) que l’altra té bloquejat.Comm que cap pot continuar, totes queden aturades indefinidament (i a més a més afecten a la resta de sessions)

Exemple senzill:
    Transacció A té bloquejada la fila 1 i vol la fila 2.
    Transacció B té bloquejada la fila 2 i vol la fila 1.
Cap pot continuar → deadlock

Propietats d’una transacció (ACID)

Ordres bàsiques de control de transaccions

ORACLE és un sistema de base de dades purament transaccional, de tal forma, que la instrucció BEGIN TRANSACTION no existeix

En una transacció les dades modificades no són visibles per la resta d'usuaris fins que es confirme la transacció

COMMIT

Fa permanents tots els canvis realitzats en la transacció. No es poden desfer després. En este moment les dades modificades ja son visibles per la resta d'usuaris . 

INSERT INTO alumnes VALUES (10, 'Joan', 20);
COMMIT;

Les sentències de finalització de transacció són COMMIT i ROLLBACK

ROLLBACK

Desfà tots els canvis realitzats des del darrer COMMIT. Torna l’estat de la BD a com estava abans. 

UPDATE alumnes SET edat = 30 WHERE id = 10;
ROLLBACK; -- Anul·la l'actualització

SAVEPOINT

Permet definir punts intermedis dins d’una transacció per fer ROLLBACK parcial. 

SAVEPOINT punt1;
DELETE FROM alumnes WHERE edat < 18;

SAVEPOINT punt2;
UPDATE alumnes SET nom = 'Anònim' WHERE edat > 25;

ROLLBACK TO punt1; -- desfà només fins al punt1

Transaccions automàtiques i manuals

Consells pràctics

Relació amb la seguretat

Una gestió acurada de les transaccions garanteix:

Oracle té mecanismes per detectar deadlocks de manera automàtica. Quan detecta que dos transaccions no poden continuar, avorta una de les dues i retorna un error:

La transacció que Oracle sol avortar és la que porta menys treball fet (la que ha modificat menys dades), també coneguda com la “victim transaction”, per minimitzar la pèrdua de treball.

ORA-00060: deadlock detected while waiting for resource

Així, almenys una pot continuar i el sistema no queda bloquejat.

Quan Oracle mata una transacció per deadlock, la seva part desada es desfà (rollback), mantenint la coherència.

Conclusió

El control de transaccions és una eina fonamental per garantir que el sistema de base de dades siga segur, coherent i robust. Oracle proporciona instruccions clares per controlar de forma manual i segura l’execució de canvis en les dades.

Exemple (vore transacció per completar)

Obrim 2 sessions ( en SQL*PLUS ) amb usuari1 ( dos cmd diferents) 

 
    En la primera sessió (1er CMD)
    SQL> insert into prueba10 (id, nom) values (1000,’valor1 ’);
    1 row created.
        
    En la segon sessió  (2on cmd)    
    SQL> select count(*) from prueba10;
    COUNT(*)
    ----------
    0
    En la primera sessió (1er CMD)
    SQL> commit;            
        
    En la segon sessió (2on CMD)    
    SQL> select count(*) from prueba10;
    COUNT(*)
    ----------
    1

Exemple ( de deadlock )

Suposem que tenim una taula:

CREATE TABLE comptes (
    id NUMBER PRIMARY KEY,
    saldo NUMBER
    );

i dos files

INSERT INTO comptes VALUES (1, 100);
INSERT INTO comptes VALUES (2, 200);
COMMIT;

Obri dos sessios sql diferents, Sessio A i Sessio B

Sessio A
  -- Bloqueja la fila amb id = 1
UPDATE comptes SET saldo = saldo + 10 WHERE id = 1;
Sessio B
-- Bloqueja la fila amb id = 2
UPDATE comptes SET saldo = saldo + 20 WHERE id = 2;

-- SESSION A intenta agafar la fila que té B
UPDATE comptes SET saldo = saldo - 5 WHERE id = 2;
--  → A queda esperant, perquè B té bloquejada la fila 2.

SESSION B intenta agafar la fila que té A
UPDATE comptes SET saldo = saldo - 5 WHERE id = 1;
-- → B queda esperant, perquè A té bloquejada la fila 1.

-- I ací és on Oracle detecta el deadlock
-- Oracle trenca el bloqueig i dona error a una de les dues sessions (normalment la segona que entra al conflicte)

💾 Recuperació i còpies de seguretat en Oracle

Per què cal fer còpies de seguretat?

Les còpies de seguretat són essencials per garantir la continuïtat del servei i la protecció de la informació. En cas de fallada del sistema, error humà, corrupció de dades o atacs, una bona estratègia de backup permet recuperar la base de dades sense pèrdues.

La qüestió de fer còpies de seguretat des de dins d’Oracle (usant eines com RMAN o exportacions lògiques com Data Pump) versus fer-les des de fora (copiant fitxers del sistema operatiu) és molt important perquè afecta la consistència i la recuperabilitat de la base de dades

Fer còpies de seguretat des de dins d’Oracle és crític perquè assegura que la base de dades es pugui recuperar correctament i sense pèrdua de dades, fins i tot si està en funcionament. Fer-ho des del sistema operatiu només és segur si la base està apagada, cosa poc pràctica en entorns de producció

Problemes si es fan des de fora del SGBD: Inconsistència, Recuperació complicada, No registra transaccions

Avantatges si es fan des de dins del SGBD: Consistència de dades, Recuperació puntual, Gestió de logs automàtica, Còpies en calent, Automatització i verificació:

🎯 Objectius d’una còpia de seguretat

Tipus de còpies de seguretat

Estratègies de backup

Mecanismes d'Oralce

Eines principals de backup en Oracle

1. 📤 Exportació i importació legacy (Original Export))

exp / imp
EXEMPLE
exp username/password@ipAddress:portNumber/serviceName file=/recovery_area/export/prueba_export.dmp full=yes buffer=1000000

Si volem fer un exp total (full=yes), l'usuari que l'execute necessita el rol EXP_FULL_DATABASE

Per cridar amb sys
exp \'username/password@instance AS SYSDBA\' parametres

exportacio de dades

Un mecanisme molt semblant i més còmode i intuitiu es pot trobar a SQL Developer, amb l'eina de Exportar. Des del menu Eines-Exportació de base de dades, llança unes finestres per indicar que, com , on fer una exportació.

Les dades es guardarn en la màquina client ( o un lloc on esta puga accedir )

SQL Developer permet exportar / importar dades, esquemes i resultats de consultes a diversos formats

2. 📤 Data Pump

expdp / impdp
-- EXEMPLE    
-- Guardar tot un esquema (tots els objectes d'un usuari)
expdp username/password@ipAddress:portNumber/serviceName directory=dumpdir dumpfile=export.dmp logfile=fichero.log
expdp usuari/password SCHEMAS=usuari DUMPFILE=export.dmp LOGFILE=export.log   --No cal posar path físic !!

-- Recuperar en una altra BD
impdp usuari/password DUMPFILE=export.dmp LOGFILE=import.log
dp full dp tablespace dp schema
dp table dp query

Es necessari que el servidor tinga accés al lloc on farà (el servidor) les còpies

Primer: Definir un directori 

CREATE [OR REPLACE] DIRECTORY directory_name AS 'path_name';

El path deu existir i tindre permisos d'escriptura

Els fitxers de destí NO deuen existir (o donarà error la còpia)

L'usuari que connecta ha de tindre permisos d'accés a les dades per fer còpies

Per fer una còpia completa es necessita un permís (ROL) concret

DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE    
EXEMPLE d'arxiu 'parfile'
TABLESPACES=users
DUMPFILE=exp2.dmp
DIRECTORY=dirdump
LOGFILE=exp2.log

3. 🔄 RMAN (Recovery Manager)

Eina oficial d'Oracle per fer backups i recuperar dades. Pot treballar amb còpies incrementals, verificar integritat i automatitzar tasques.

RMAN necessita ARCHIVELOG activat per funcionar correctament

      -- Exemple: backup complet
      RMAN> BACKUP DATABASE;
      
      -- Backup només de la part SYSTEM
      RMAN> BACKUP TABLESPACE system;
      
      -- Recuperació
      RMAN> RESTORE DATABASE;
      RMAN> RECOVER DATABASE;

📋 Exemple de procediment bàsic amb RMAN

      -- Connexió
      rman target /
      
      -- Comprovació de còpia
      VALIDATE DATABASE;
      
      -- Còpia
      BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;
      
      -- Restauració
      RESTORE DATABASE;
      RECOVER DATABASE;
sql loader

SQL*Loader és una utilitat que permet la inserció de dades des d'un arxiu pla a una o més bases de dades.

Durant una sola de les seves execucions és possible omplir múltiples taules amb dades de múltiples arxius, manejar registres d'ample variable o fix, manipular les dades entrants per a tractar amb valors nuls, delimitadors i espais en blanc, obviar registres o encapçalats i reaccionar enfront de fallades del procés de carregat

Arxius implicats en la recuperació

Escenaris de recuperació

Bones pràctiques de seguretat

Conclusió

Disposar d’un pla de còpies de seguretat fiable i efectiu és fonamental per garantir la seguretat i continuïtat de qualsevol sistema basat en Oracle. Les eines com RMAN i Data Pump permeten adaptar-se a múltiples escenaris, i una bona estratègia de backup ha d’estar acompanyada d’una política de recuperació clara.

⚖️ Normativa vigent en matèria de protecció de dades

📘 Què és la normativa de protecció de dades?

És el conjunt de lleis i regulacions que tenen com a objectiu garantir la privadesa i la seguretat de les dades personals que gestionen les empreses i organitzacions. Afecta directament la gestió dels sistemes d’informació, especialment els que contenen bases de dades amb informació de persones físiques.

🇪🇺 Reglament General de Protecció de Dades (RGPD)

Principis bàsics del RGPD

👥 Drets de les persones usuàries

🇪🇸 LOPDGDD – Llei orgànica 3/2018

A Espanya, la normativa RGPD es complementa amb la LOPDGDD:

Alguns punts destacats de la LOPDGDD

Obligacions tècniques relacionades amb bases de dades

Drets ARCO

Paper del SGBD

Classificar informació

Els procediments han d’estar documentats i supervisats per poder garantir el compliment de la normativa i la llei.

📊 Sancions per incompliment

El RGPD preveu sancions de fins al 4% del volum de negoci anual global o 20 milions d’euros, segons quina siga més elevada.

Conclusió

Tant el RGPD com la LOPDGDD estableixen un marc normatiu estricte i necessari per protegir les dades personals. A l’hora d’administrar bases de dades, cal assegurar-se que:

⚖️ “Una base de dades segura no només és eficient, sinó també legalment responsable.”

Informe de bones pràctiques del CCN-CERT

ccn-cert

CCN-CERT BP/22 Recomendaciones de seguridad para Oracle Database 19C

CCN-CERT BP/22 és un document tècnic que ofereix recomanacions i bones pràctiques en ciberseguretat, elaborat pel CCN-CERT, amb l’objectiu d’ajudar les administracions públiques i altres organitzacions a millorar la protecció dels seus sistemes d’informació

Altres recursos

Repàs...