Vistes de seguretat

Introducció: les vistes com a mecanisme de seguretat

Una vista (view) és una consulta SQL emmagatzemada al catàleg del SGBD que es comporta com una taula virtual. Des del punt de vista de l'usuari, una vista és indistingible d'una taula real: es pot consultar amb SELECT, i en molts casos també es pot modificar.

Les vistes ofereixen un potent mecanisme de seguretat per dos motius principals:

1. Restricció de columnes: la vista exposa només les columnes que volem fer visibles, ocultant dades sensibles com salaris, contrasenyes hash, dades mèdiques o informació personal.
2. Restricció de files: la vista inclou una clàusula WHERE que filtra les files accessibles, de manera que cada usuari veu únicament les dades que li corresponen.

flowchart LR
    subgraph "Taula base (empleats)"
        T["id | nom | dept | salari | nif | correu"]
    end
    subgraph "Vista (empleats_public)"
        V["id | nom | dept | correu"]
    end
    subgraph Usuaris
        U1[app_rrhh_basic]
        U2[Gestor]
    end

    T -->|"SELECT id, nom, dept, correu"| V
    V -->|"GRANT SELECT"| U1
    T -->|"Accés directe"| U2

    style T fill:#f08080
    style V fill:#90ee90

---

Creació de vistes de seguretat

Vista que oculta columnes sensibles

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Taula base amb dades sensibles
CREATE TABLE empleats (
    id          SERIAL PRIMARY KEY,
    nom         VARCHAR(100) NOT NULL,
    cognoms     VARCHAR(100) NOT NULL,
    departament VARCHAR(50),
    correu      VARCHAR(100),
    telefon     VARCHAR(20),
    salari      NUMERIC(10, 2),
    nif         VARCHAR(12),
    data_alta   DATE
);

-- Vista que oculta salari i NIF
CREATE VIEW empleats_public AS
SELECT
    id,
    nom,
    cognoms,
    departament,
    correu,
    telefon,
    data_alta
FROM empleats;

-- Concedir accés a la vista, NO a la taula base
GRANT SELECT ON empleats_public TO rol_lectura;

-- Verificar que el rol no té accés directe a la taula base
REVOKE ALL ON empleats FROM rol_lectura;

CREATE TABLE empleats (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    nom         VARCHAR(100) NOT NULL,
    cognoms     VARCHAR(100) NOT NULL,
    departament VARCHAR(50),
    correu      VARCHAR(100),
    telefon     VARCHAR(20),
    salari      DECIMAL(10, 2),
    nif         VARCHAR(12),
    data_alta   DATE
);

-- Vista que oculta salari i NIF
CREATE VIEW empleats_public AS
SELECT
    id,
    nom,
    cognoms,
    departament,
    correu,
    telefon,
    data_alta
FROM empleats;

-- Concedir accés a la vista
GRANT SELECT ON empresa.empleats_public TO 'rol_lectura'@'%';
-- Assegurar-se que no té accés directe a la taula
REVOKE ALL ON empresa.empleats FROM 'rol_lectura'@'%';
FLUSH PRIVILEGES;

CREATE TABLE dbo.empleats (
    id          INT IDENTITY(1,1) PRIMARY KEY,
    nom         NVARCHAR(100) NOT NULL,
    cognoms     NVARCHAR(100) NOT NULL,
    departament NVARCHAR(50),
    correu      NVARCHAR(100),
    telefon     NVARCHAR(20),
    salari      DECIMAL(10, 2),
    nif         NVARCHAR(12),
    data_alta   DATE
);

-- Vista que oculta salari i NIF
CREATE VIEW dbo.empleats_public AS
SELECT
    id,
    nom,
    cognoms,
    departament,
    correu,
    telefon,
    data_alta
FROM dbo.empleats;

-- Concedir accés a la vista
GRANT SELECT ON dbo.empleats_public TO rol_lectura;
-- Negar accés directe a la taula base
DENY SELECT ON dbo.empleats TO rol_lectura;

CREATE TABLE empresa.empleats (
    id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nom         VARCHAR2(100) NOT NULL,
    cognoms     VARCHAR2(100) NOT NULL,
    departament VARCHAR2(50),
    correu      VARCHAR2(100),
    telefon     VARCHAR2(20),
    salari      NUMBER(10, 2),
    nif         VARCHAR2(12),
    data_alta   DATE
);

-- Vista que oculta salari i NIF
CREATE OR REPLACE VIEW empresa.empleats_public AS
SELECT
    id,
    nom,
    cognoms,
    departament,
    correu,
    telefon,
    data_alta
FROM empresa.empleats;

-- Concedir accés
GRANT SELECT ON empresa.empleats_public TO rol_lectura;
REVOKE SELECT ON empresa.empleats FROM rol_lectura;

Vista que filtra files per usuari

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Vista que mostra les comandes de cada comercial
-- (filtra per l'usuari de sessió actual)
CREATE VIEW les_meves_comandes AS
SELECT
    c.id,
    c.data_comanda,
    c.estat,
    c.total,
    cl.nom AS client
FROM comandes c
JOIN clients cl ON c.client_id = cl.id
WHERE c.comercial = current_user;

-- Cada comercial veu només les seves comandes
GRANT SELECT ON les_meves_comandes TO rol_comercials;

-- Vista parametritzada per departament (més flexible)
CREATE VIEW empleats_del_departament AS
SELECT e.*
FROM empleats e
JOIN usuaris_departaments ud
    ON e.departament = ud.departament
    AND ud.usuari = current_user;

GRANT SELECT ON empleats_del_departament TO rol_caps_dept;

-- Vista que filtra per usuari actual
CREATE VIEW les_meves_comandes AS
SELECT
    c.id,
    c.data_comanda,
    c.estat,
    c.total,
    cl.nom AS client
FROM comandes c
JOIN clients cl ON c.client_id = cl.id
WHERE c.comercial = CURRENT_USER();
-- CURRENT_USER() retorna 'usuari@host'
-- Per obtenir només el nom d'usuari:
-- WHERE c.comercial = SUBSTRING_INDEX(CURRENT_USER(), '@', 1)

GRANT SELECT ON empresa.les_meves_comandes TO 'rol_comercials'@'%';

-- Vista que filtra per usuari actual
CREATE VIEW dbo.les_meves_comandes AS
SELECT
    c.id,
    c.data_comanda,
    c.estat,
    c.total,
    cl.nom AS client
FROM dbo.comandes c
JOIN dbo.clients cl ON c.client_id = cl.id
WHERE c.comercial = SYSTEM_USER;
-- o: WHERE c.comercial = USER_NAME()

GRANT SELECT ON dbo.les_meves_comandes TO rol_comercials;

-- Vista que filtra per usuari actual
CREATE OR REPLACE VIEW empresa.les_meves_comandes AS
SELECT
    c.id,
    c.data_comanda,
    c.estat,
    c.total,
    cl.nom AS client
FROM empresa.comandes c
JOIN empresa.clients cl ON c.client_id = cl.id
WHERE c.comercial = SYS_CONTEXT('USERENV', 'SESSION_USER');

GRANT SELECT ON empresa.les_meves_comandes TO rol_comercials;

---

WITH CHECK OPTION

La clàusula WITH CHECK OPTION garanteix que les operacions d'inserció i actualització a través d'una vista respectin la condició WHERE de la vista. Si no s'inclou, un usuari podria inserir files que no serien visibles a través de la vista (però sí a la taula base).

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Vista de comandes pendents amb CHECK OPTION
CREATE VIEW comandes_pendents AS
SELECT id, data_comanda, total, client_id, comercial
FROM comandes
WHERE estat = 'pendent'
WITH CHECK OPTION;

-- Això funcionarà (estat = 'pendent')
INSERT INTO comandes_pendents (data_comanda, total, client_id, comercial)
VALUES (CURRENT_DATE, 150.00, 5, 'bernat');

-- Això fallarà: violació de CHECK OPTION (estat seria 'enviat')
-- Si intentem fer un UPDATE que canviï l'estat fora del filtre:
UPDATE comandes_pendents SET estat = 'enviat' WHERE id = 10;
-- ERROR: new row violates check option for view "comandes_pendents"

-- WITH LOCAL CHECK OPTION: comprova només la condició d'aquesta vista
-- WITH CASCADED CHECK OPTION (per defecte): comprova també les vistes pare
CREATE VIEW comandes_pendents_locals AS
SELECT * FROM comandes_pendents
WHERE total > 100
WITH LOCAL CHECK OPTION;

CREATE VIEW comandes_pendents AS
SELECT id, data_comanda, total, client_id, comercial
FROM comandes
WHERE estat = 'pendent'
WITH CHECK OPTION;

-- Inserció vàlida
INSERT INTO comandes_pendents (data_comanda, total, client_id, comercial)
VALUES (CURDATE(), 150.00, 5, 'bernat');

-- Actualització invàlida (fallarà)
UPDATE comandes_pendents SET estat = 'enviat' WHERE id = 10;
-- ERROR 1369: CHECK OPTION failed 'empresa.comandes_pendents'

CREATE VIEW dbo.comandes_pendents AS
SELECT id, data_comanda, total, client_id, comercial
FROM dbo.comandes
WHERE estat = 'pendent'
WITH CHECK OPTION;

-- Inserció vàlida
INSERT INTO dbo.comandes_pendents (data_comanda, total, client_id, comercial)
VALUES (GETDATE(), 150.00, 5, 'bernat');

-- Actualització invàlida
UPDATE dbo.comandes_pendents SET estat = 'enviat' WHERE id = 10;
-- Error: The attempted insert or update failed because the target view
-- either specifies WITH CHECK OPTION or spans a view that specifies
-- WITH CHECK OPTION...

CREATE OR REPLACE VIEW empresa.comandes_pendents AS
SELECT id, data_comanda, total, client_id, comercial
FROM empresa.comandes
WHERE estat = 'pendent'
WITH CHECK OPTION CONSTRAINT ck_comandes_pendents;

-- Inserció vàlida
INSERT INTO empresa.comandes_pendents (data_comanda, total, client_id, comercial)
VALUES (SYSDATE, 150.00, 5, 'BERNAT');

-- Actualització invàlida (ORA-01402)
UPDATE empresa.comandes_pendents SET estat = 'ENVIAT' WHERE id = 10;
-- ORA-01402: view WITH CHECK OPTION where-clause violation

---

Vistes actualitzables vs. vistes de només lectura

No totes les vistes es poden actualitzar. Una vista és actualitzable si:

• Consulta una sola taula base.
• No conté DISTINCT, GROUP BY, HAVING, UNION, funcions d'agregació ni subconsultes a la clàusula SELECT.
• Totes les columnes NOT NULL de la taula base sense valor per defecte estan incloses a la vista.

Quan la vista no és actualitzable

Si la vista no és actualitzable, podeu usar regles (RULE a PostgreSQL) o triggers INSTEAD OF (SQL Server, Oracle, MariaDB) per gestionar les operacions DML a través de la vista.

PostgreSQLSQL ServerOracle

-- Vista actualitzable (taula única, sense agregació)
CREATE VIEW empleats_vendes AS
SELECT id, nom, cognoms, correu, data_alta
FROM empleats
WHERE departament = 'Vendes';

-- UPDATE a través de la vista (vàlid)
UPDATE empleats_vendes SET correu = 'nou@empresa.com' WHERE id = 5;

-- Vista NO actualitzable (JOIN, agregació...)
CREATE VIEW resum_dept AS
SELECT departament, COUNT(*) AS total, AVG(salari) AS salari_mig
FROM empleats
GROUP BY departament;
-- No es pot fer INSERT/UPDATE/DELETE sobre resum_dept

-- Trigger INSTEAD OF per a vistes no actualitzables
CREATE OR REPLACE FUNCTION tr_empleats_vendes_insert()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO empleats (nom, cognoms, correu, departament, data_alta)
    VALUES (NEW.nom, NEW.cognoms, NEW.correu, 'Vendes', CURRENT_DATE);
    RETURN NEW;
END;
$$;

CREATE TRIGGER empleats_vendes_insert
INSTEAD OF INSERT ON empleats_vendes
FOR EACH ROW EXECUTE FUNCTION tr_empleats_vendes_insert();

-- Trigger INSTEAD OF per a vistes no actualitzables
CREATE VIEW dbo.resum_dept AS
SELECT departament, COUNT(*) AS total, AVG(salari) AS salari_mig
FROM dbo.empleats
GROUP BY departament;

-- No es pot INSERT directament, cal trigger INSTEAD OF
CREATE TRIGGER tr_resum_dept_insert
ON dbo.resum_dept
INSTEAD OF INSERT
AS
BEGIN
    -- Lògica personalitzada per gestionar la inserció
    RAISERROR('No es pot inserir directament a resum_dept', 16, 1);
END;

-- Trigger INSTEAD OF a Oracle
CREATE OR REPLACE VIEW empresa.resum_dept AS
SELECT departament, COUNT(*) AS total, AVG(salari) AS salari_mig
FROM empresa.empleats
GROUP BY departament;

CREATE OR REPLACE TRIGGER tr_resum_dept_insert
INSTEAD OF INSERT ON empresa.resum_dept
FOR EACH ROW
BEGIN
    -- Lògica per gestionar inserció a vista complexa
    RAISE_APPLICATION_ERROR(-20001,
        'No es pot inserir directament a resum_dept');
END;
/

---

Vistes materialitzades

Les vistes materialitzades (materialized views) emmagatzemen físicament el resultat de la consulta, a diferència de les vistes normals que l'executen cada cop que es consulten. Ofereixen un rendiment molt superior per a consultes pesades, però requereixen una operació d'actualització (REFRESH) per mantenir-les al dia.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Crear vista materialitzada
CREATE MATERIALIZED VIEW mv_resum_vendes AS
SELECT
    DATE_TRUNC('month', data_comanda) AS mes,
    comercial,
    COUNT(*) AS num_comandes,
    SUM(total) AS total_vendes,
    AVG(total) AS ticket_mig
FROM comandes
WHERE estat = 'completada'
GROUP BY DATE_TRUNC('month', data_comanda), comercial
WITH DATA;  -- Ometre WITH DATA per crear buida (NO DATA)

-- Crear índex sobre la vista materialitzada (gran avantatge vs vista normal)
CREATE INDEX idx_mv_vendes_mes ON mv_resum_vendes (mes);
CREATE INDEX idx_mv_vendes_comercial ON mv_resum_vendes (comercial);

-- Actualitzar la vista materialitzada
REFRESH MATERIALIZED VIEW mv_resum_vendes;

-- Actualitzar sense bloqueig (concurrent, cal índex únic)
CREATE UNIQUE INDEX idx_mv_vendes_pk
    ON mv_resum_vendes (mes, comercial);

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_resum_vendes;

-- Automatitzar el refresc amb pg_cron (extensió)
-- SELECT cron.schedule('0 2 * * *', 'REFRESH MATERIALIZED VIEW mv_resum_vendes');

-- Consultar l'estat
SELECT schemaname, matviewname, ispopulated, definition
FROM pg_matviews
WHERE matviewname = 'mv_resum_vendes';

-- MySQL i MariaDB NO tenen vistes materialitzades natives.
-- La solució habitual és una taula resum actualitzada per un EVENT:

CREATE TABLE mv_resum_vendes (
    mes          DATE,
    comercial    VARCHAR(100),
    num_comandes INT,
    total_vendes DECIMAL(15, 2),
    ticket_mig   DECIMAL(10, 2),
    PRIMARY KEY (mes, comercial)
);

-- Procediment de refresc
DELIMITER //
CREATE PROCEDURE refrescar_mv_resum_vendes()
BEGIN
    TRUNCATE TABLE mv_resum_vendes;
    INSERT INTO mv_resum_vendes
    SELECT
        DATE_FORMAT(data_comanda, '%Y-%m-01') AS mes,
        comercial,
        COUNT(*) AS num_comandes,
        SUM(total) AS total_vendes,
        AVG(total) AS ticket_mig
    FROM comandes
    WHERE estat = 'completada'
    GROUP BY DATE_FORMAT(data_comanda, '%Y-%m-01'), comercial;
END //
DELIMITER ;

-- Programar l'actualització diària
CREATE EVENT ev_refresc_vendes
ON SCHEDULE EVERY 1 DAY
STARTS '2025-01-01 02:00:00'
DO CALL refrescar_mv_resum_vendes();

-- SQL Server té "Indexed Views" (vistes indexades) que s'assemblen
-- a les vistes materialitzades

CREATE VIEW dbo.mv_resum_vendes
WITH SCHEMABINDING  -- Requerit per a vistes indexades
AS
SELECT
    YEAR(data_comanda)  AS any_comanda,
    MONTH(data_comanda) AS mes_comanda,
    comercial,
    COUNT_BIG(*)        AS num_comandes,
    SUM(total)          AS total_vendes
FROM dbo.comandes
WHERE estat = 'completada'
GROUP BY YEAR(data_comanda), MONTH(data_comanda), comercial;

-- Crear l'índex clúster fa que la vista s'emmagatzemi físicament
CREATE UNIQUE CLUSTERED INDEX idx_mv_vendes
    ON dbo.mv_resum_vendes (any_comanda, mes_comanda, comercial);

-- SQL Server actualitza la vista indexada automàticament en fer DML

-- Oracle té vistes materialitzades natives i molt avançades
CREATE MATERIALIZED VIEW mv_resum_vendes
BUILD IMMEDIATE          -- Poblar en crear-la
REFRESH COMPLETE         -- Estratègia de refresc: COMPLETE o FAST
ON DEMAND                -- Refresc manual (o START WITH ... NEXT per automàtic)
ENABLE QUERY REWRITE     -- Permet al optimitzador usar la MV automàticament
AS
SELECT
    TRUNC(data_comanda, 'MM') AS mes,
    comercial,
    COUNT(*)  AS num_comandes,
    SUM(total) AS total_vendes,
    AVG(total) AS ticket_mig
FROM empresa.comandes
WHERE estat = 'COMPLETADA'
GROUP BY TRUNC(data_comanda, 'MM'), comercial;

-- Refresc manual
EXEC DBMS_MVIEW.REFRESH('MV_RESUM_VENDES', 'C');  -- 'C' = Complete

-- Refresc automàtic cada nit a les 2:00
CREATE MATERIALIZED VIEW mv_resum_vendes
REFRESH COMPLETE
START WITH SYSDATE
NEXT TRUNC(SYSDATE + 1) + 2/24
AS ...;

-- Verificar estat
SELECT mview_name, last_refresh_date, staleness
FROM user_mviews;

---

Security definer vs. security invoker

A PostgreSQL (i parcialment a altres SGBD), les vistes i funcions poden executar-se amb els privilegis del creador (SECURITY DEFINER) o amb els privilegis de l'usuari que la crida (SECURITY INVOKER).

PostgreSQL

-- SECURITY INVOKER (per defecte): la vista s'executa amb els privilegis
-- de l'usuari que fa el SELECT
CREATE VIEW empleats_dept_invoker
WITH (security_invoker = true)
AS
SELECT * FROM empleats;
-- Si l'usuari no té accés a 'empleats', la vista fallarà

-- SECURITY DEFINER: la vista s'executa amb els privilegis del creador
-- Útil per permetre accés controlat a taules sense donar accés directe
CREATE VIEW empleats_dept_definer
WITH (security_barrier = true)  -- Evita optimitzacions que podrien filtrar dades
AS
SELECT id, nom, departament, correu
FROM empleats
WHERE departament = current_setting('myapp.dept', true);

-- La clàusula security_barrier és important!
-- Sense ella, PostgreSQL podria reordenar filtres i exposar files no desitjades
-- a través de funcions malicioses passades per l'usuari.

-- Exemple de funció SECURITY DEFINER per a operació privilegiada
CREATE OR REPLACE FUNCTION inserir_empleat_rrhh(
    p_nom VARCHAR, p_cognoms VARCHAR, p_dept VARCHAR, p_salari NUMERIC
)
RETURNS INT
SECURITY DEFINER
SET search_path = public  -- Evitar substitució maliciosa del search_path
LANGUAGE plpgsql AS $$
DECLARE
    v_id INT;
BEGIN
    INSERT INTO empleats (nom, cognoms, departament, salari, data_alta)
    VALUES (p_nom, p_cognoms, p_dept, p_salari, CURRENT_DATE)
    RETURNING id INTO v_id;
    RETURN v_id;
END;
$$;

-- Concedir execució de la funció (no de la taula base!)
GRANT EXECUTE ON FUNCTION inserir_empleat_rrhh TO rol_rrhh;

Precaució amb SECURITY DEFINER

Una funció o vista SECURITY DEFINER s'executa amb els privilegis del creador. Si el creador és un superusuari, qualsevol usuari amb permís d'execució tindrà accés potencial a tots els objectes. Useu-la amb precaució i sempre fixeu el search_path.

---

Eliminació i modificació de vistes

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Modificar una vista (PostgreSQL permet CREATE OR REPLACE
-- si les primeres columnes coincideixen i no s'eliminen)
CREATE OR REPLACE VIEW empleats_public AS
SELECT id, nom, cognoms, departament, correu, telefon, data_alta
FROM empleats
WHERE actiu = TRUE;  -- Afegim filtre

-- Eliminar una vista
DROP VIEW IF EXISTS empleats_public;

-- Eliminar en cascada (elimina objectes dependents)
DROP VIEW IF EXISTS empleats_public CASCADE;

-- Vistes materialitzades
DROP MATERIALIZED VIEW IF EXISTS mv_resum_vendes;

-- Modificar una vista (cal eliminar i recrear, o usar ALTER VIEW)
ALTER VIEW empleats_public AS
SELECT id, nom, cognoms, departament, correu, data_alta
FROM empleats
WHERE actiu = 1;

-- Eliminar
DROP VIEW IF EXISTS empleats_public;

-- Modificar una vista
ALTER VIEW dbo.empleats_public AS
SELECT id, nom, cognoms, departament, correu, data_alta
FROM dbo.empleats
WHERE actiu = 1;

-- Eliminar
DROP VIEW IF EXISTS dbo.empleats_public;

-- Modificar una vista
CREATE OR REPLACE VIEW empresa.empleats_public AS
SELECT id, nom, cognoms, departament, correu, data_alta
FROM empresa.empleats
WHERE actiu = 1;

-- Eliminar
DROP VIEW empresa.empleats_public;

-- Vista materialitzada
DROP MATERIALIZED VIEW empresa.mv_resum_vendes;

---

Miniactivitat — AC0306 · Disseny de vistes de seguretat

Miniactivitat — AC0306

L'empresa TecnoSapa SL té la taula empleats amb les columnes següents:

id, nom, cognoms, departament, lloc_treball,
data_alta, data_baixa, salari_brut, salari_net,
nif, numero_ss, correu_corporatiu, telefon_directe, actiu

Tasca 1 — Vista de directori corporatiu:

Dissenyeu i creeu una vista directori_empresa accessible a tots els empleats que mostri el nom, cognoms, departament, lloc de treball, correu corporatiu i telèfon directe, però en cap cas el salari, NIF, número de la Seguretat Social ni la data de baixa.

Tasca 2 — Vista de RRHH sense salaris:

Creeu una vista empleats_rrhh_basica per al rol rol_rrhh_junior que mostri totes les columnes excepte salari_brut, salari_net i numero_ss. Afegiu WITH CHECK OPTION.

Tasca 3 — Vista personalitzada per departament:

Creeu una vista empleats_departament_propi que cada cap de departament pugui consultar i que mostri únicament els empleats del seu departament. Useu current_user o equivalent per al filtratge dinàmic.

Tasca 4 — Vista materialitzada (PostgreSQL o Oracle):

Creeu una vista materialitzada mv_estadistiques_dept que mostri, per a cada departament: nombre d'empleats actius, salari mitjà, salari màxim i salari mínim. Programeu o documenteu com es refrescaria diàriament.

Lliurament: script SQL complet creat + captures de pantalla de les proves d'accés amb usuaris de prova.