Simulacre 2a avaluació

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

Lliurament¶

Si l'entrega no compleix aquests criteris, no és qualificarà.

Cal utilitzar el repositori creat en l'organització del mòdul: CognomNom-ASGBD.
Tots els exercicis han d'estar situats en el directori corresponent: simulacre2
S'ha d'entregar a Aules:
- Un fitxer .zip amb el contingut del directori.
- L'enllaç al repositori de GitHub a l'etiqueta simulacre2

CognomNom-ASGBD/
├── README.md
├── docker-compose.yml
└── simulacre2/
    ├── README.md
    └── img/
        ├── captura-1.png
        ...
        └── captura-N.png

Preparació¶

Dins del contenidor XEPDB1:

-- 1. Crea l'espai de taules `SIMULACRE_USERS`
CREATE TABLESPACE SIMULACRE_USERS
    DATAFILE '/opt/oracle/oradata/XE/XEPDB1/encrypted_users01.dbf' SIZE 1M
        AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

-- 2. Crea l'usuari `simulacre2` amb la contrasenya `simulacre2`
CREATE USER simulacre2 IDENTIFIED BY simulacre2
    DEFAULT TABLESPACE SIMULACRE_USERS
    QUOTA UNLIMITED ON SIMULACRE_USERS;

-- 3. Crea la taula `productes` i `compres`
CREATE TABLE simulacre2.productes (
    id NUMBER PRIMARY KEY,
    nom VARCHAR2(100),
    preu NUMBER
);

CREATE TABLE simulacre2.compres (
    id NUMBER PRIMARY KEY,
    producte_id NUMBER,
    quantitat NUMBER,
    data_compra DATE,
    FOREIGN KEY (producte_id) REFERENCES simulacre2.productes(id)
);

Exercici 1: Encriptació¶

Ha de quedar documentat explícitament amb quina connexió s'ha realitzat cada pas

Habilita l'encriptació Transparent Data Encryption (TDE) en la base de dades.
Encripta l'espai de taules SIMULACRE_USERS utilitzant l'algorisme AES256.
Verifica en el diccionari de dades que l'espai de taules està encriptat.

Solució

Des del contenidor de Docker, crea la carpeta per al del magatzem de claus:

docker exec -it oracleX213 bash
mkdir -p /opt/oracle/admin/XE/wallet

Connectat com a sys@XE, crea el magatzem de claus:

ADMINISTER KEY MANAGEMENT
CREATE KEYSTORE '/opt/oracle/admin/XE/wallet'
IDENTIFIED BY "wallet_password";

SELECT wrl_parameter, status, wallet_type FROM V$ENCRYPTION_WALLET;

Connectat com a sys@XE i sys@XEPDB1 obri el magatzem de claus al contenidor CDB i PDB1:

ADMINISTER KEY MANAGEMENT
    SET KEYSTORE OPEN
    IDENTIFIED BY "wallet_password";

    SELECT wrl_parameter, status, wallet_type FROM V$ENCRYPTION_WALLET;

Connectat com a sys@XE i sys@XEPDB1 crea la clau mestra del contenidor CDB i PDB1:

ADMINISTER KEY MANAGEMENT
    SET KEY
    IDENTIFIED BY "wallet_password"
    WITH BACKUP;

SELECT wrl_parameter, status, wallet_type FROM V$ENCRYPTION_WALLET;

Modifica l'espai de taules SIMULACRE_USERS per encriptar-lo amb AES256:

ALTER TABLESPACE SIMULACRE_USERS
    ENCRYPTION ONLINE USING 'AES256' ENCRYPT;

Exercici 2: Auditoria¶

Important

Treballarem sempre en el contenidor PDB1.
Comprova el diccionari de dades per assegurar-te que les polítiques s'han aplicat correctament.
Mostra els registres d'auditoria relacionats amb les polítiques creades després d'haver realitzat algunes accions que les desencadenen.

Audita totes les operacions d'inserció, actualització i eliminació en la taula productes que no s'hagen realitzat correctament.

Solució

Des de sys@XEPDB1:

Auditoria unificada

-- Creem la política
CREATE AUDIT POLICY pol_productes_errors
ACTIONS INSERT ON simulacre2.productes,
        UPDATE ON simulacre2.productes,
        DELETE ON simulacre2.productes;

-- Activem la política
AUDIT POLICY pol_productes_errors
    WHENEVER NOT SUCCESSFUL;

-- Comprovem que la política està activa
SELECT * FROM audit_unified_policies WHERE policy_name LIKE 'POL_PRODUCTES%';

Des de simulacre2@XEPDB1:

-- Insertem dues vegades un producte amb el mateix ID per provocar un error
INSERT INTO simulacre2.productes (id, nom, preu) VALUES (1, 'Producte 1', 10.50);
INSERT INTO simulacre2.productes (id, nom, preu) VALUES (1, 'Producte 1', 10.50);

Des de sys@XEPDB1:

Auditoria unificada

-- Comprovem els registres d'auditoria
SELECT DBUSERNAME, ACTION_NAME, RETURN_CODE, OBJECT_SCHEMA, OBJECT_NAME, SQL_TEXT
    FROM UNIFIED_AUDIT_TRAIL WHERE OBJECT_SCHEMA = 'SIMULACRE2';

Audita totes les operacions de eliminació correctes en la taula productes on el preu del producte siga superior a 100.

Solució

Des de sys@XEPDB1:

-- Creem la política
BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema   => 'SIMULACRE2',
    object_name     => 'PRODUCTES',
    policy_name     => 'AUDIT_PRODUCTES_CARS',
    audit_condition => 'PREU > 100',
    statement_types => 'DELETE'
  );
END;
/

-- Comprovem que la política està activa
SELECT * FROM DBA_AUDIT_POLICIES;

Des de simulacre2@XEPDB1:

INSERT INTO simulacre2.productes (id, nom, preu) VALUES (2, 'Producte 2', 10.50);
INSERT INTO simulacre2.productes (id, nom, preu) VALUES (3, 'Producte 3', 100.50);

-- Eliminem els productes
DELETE FROM simulacre2.productes
    WHERE preu < 50; -- No s'auditarà
DELETE FROM simulacre2.productes
    WHERE preu > 50; -- S'auditarà

Des de simulacre2@XEPDB1:

-- Comprovem els registres d'auditoria
SELECT DB_USER, OS_USER, USERHOST, TIMESTAMP, OBJECT_SCHEMA, OBJECT_NAME, SQL_TEXT
    FROM DBA_FGA_AUDIT_TRAIL
    WHERE object_name = 'PRODUCTES'
    ORDER BY timestamp DESC;

Exercici 3: Màxim productes¶

Crea un disparador sobre la taula compres que limite el número de productes comprats d'un mateix tipus a un màxim de 10.

El disparador també ha de tindre en compte els productes que ja s'han comprat anteriorment, no només els que s'estan intentant comprar en la transacció actual.

En cas d'error, ha de mostrar un missatge informatiu amb informació significativa.

Solució

CREATE OR REPLACE TRIGGER simulacre2.trg_limit_compres
BEFORE INSERT OR UPDATE ON simulacre2.compres
FOR EACH ROW
DECLARE
    v_total_actual NUMBER;
BEGIN
    -- Sumem la quantitat ja comprada d'eixe producte
    SELECT COALESCE(SUM(quantitat), 0)
    INTO v_total_actual
    FROM simulacre2.compres
    WHERE producte_id = :NEW.producte_id;

    -- Comprovem si el total (existent + nou) supera 10
    IF (v_total_actual + :NEW.quantitat) > 10 THEN
        RAISE_APPLICATION_ERROR(-20001, 
            'Error: El producte ' || :NEW.producte_id || 
            ' ja té ' || v_total_actual || ' unitats. ' ||
            'No pots afegir ' || :NEW.quantitat || ' perquè superaria el límit de 10.');
    END IF;
END;
/

INSERT INTO compres VALUES (1, 1, 8, SYSDATE); -- OK
INSERT INTO compres VALUES (2, 1, 3, SYSDATE); -- ERROR

📌 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.