Rols i model RBAC

Introducció: per qué usar rols?

Imagina una empresa amb 50 empleats que accedeixen a la base de dades: 10 analistes de dades, 15 persones de vendes, 5 administradors i 20 usuaris de lectura. Si gestionem els privilegis usuari per usuari, cal fer GRANT i REVOKE per a cadascun de forma individual. Quan un analista canvia de funció, cal recordar quins privilegis tenia i modificar-los tots. Quan incorporem una nova taula, cal afegir el privilegi a tots els usuaris del grup corresponent.

Els rols solucionen aquest problema: en lloc de concedir privilegis directament a usuaris, definim perfils (rols) i els assignem als usuaris. Qualsevol canvi en el rol s'aplica automàticament a tots els usuaris que el tenen assignat.

Aquest enfocament s'anomena RBAC (Role-Based Access Control — Control d'accés basat en rols) i és el model estàndard en la gestió de seguretat de sistemes d'informació.

---

Model RBAC: diagrama conceptual

graph TD
    subgraph Usuaris
        U1[anna]
        U2[bernat]
        U3[carla]
        U4[david]
        U5[elena]
    end

    subgraph Rols
        R1[rol_lectura]
        R2[rol_vendes]
        R3[rol_analista]
        R4[rol_admin]
    end

    subgraph Privilegis
        P1[SELECT clients]
        P2[SELECT productes]
        P3[INSERT comandes]
        P4[UPDATE comandes]
        P5[SELECT comandes]
        P6[CREATE TABLE]
        P7[DROP TABLE]
        P8[SELECT ALL]
    end

    U1 --> R1
    U2 --> R2
    U3 --> R2
    U4 --> R3
    U5 --> R4

    R1 --> P1
    R1 --> P2
    R2 --> R1
    R2 --> P3
    R2 --> P4
    R2 --> P5
    R3 --> R1
    R3 --> P8
    R4 --> R3
    R4 --> P6
    R4 --> P7

En aquest diagrama veiem que:

• rol_lectura té els privilegis bàsics de SELECT.
• rol_vendes hereta rol_lectura i afegeix INSERT i UPDATE sobre comandes.
• rol_analista hereta rol_lectura i afegeix SELECT sobre totes les taules.
• rol_admin hereta rol_analista i afegeix privilegis DDL.

---

Creació i assignació de rols

PostgreSQLMySQL 8+SQL ServerOracle

-- Crear rols sense capacitat de login (per agrupar privilegis)
CREATE ROLE rol_lectura;
CREATE ROLE rol_vendes;
CREATE ROLE rol_analista;
CREATE ROLE rol_admin;

-- Concedir privilegis als rols
GRANT CONNECT ON DATABASE empresa TO rol_lectura;
GRANT USAGE ON SCHEMA public TO rol_lectura;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO rol_lectura;

GRANT rol_lectura TO rol_vendes;  -- Herència de rols
GRANT INSERT, UPDATE ON comandes TO rol_vendes;
GRANT INSERT, UPDATE ON clients TO rol_vendes;

GRANT rol_lectura TO rol_analista;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO rol_analista;

GRANT rol_analista TO rol_admin;
GRANT CREATE ON SCHEMA public TO rol_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO rol_admin;

-- Crear usuaris i assignar rols
CREATE USER anna WITH PASSWORD 'Pass#Anna2024!';
CREATE USER bernat WITH PASSWORD 'Pass#Bernat2024!';
CREATE USER carla WITH PASSWORD 'Pass#Carla2024!';

GRANT rol_lectura TO anna;
GRANT rol_vendes TO bernat;
GRANT rol_vendes TO carla;

-- Verificar assignació de rols
SELECT r.rolname AS rol, m.rolname AS membre
FROM pg_auth_members am
JOIN pg_roles r ON am.roleid = r.oid
JOIN pg_roles m ON am.member = m.oid
ORDER BY r.rolname, m.rolname;

-- Crear rols (MySQL 8.0+)
CREATE ROLE 'rol_lectura', 'rol_vendes', 'rol_analista', 'rol_admin';

-- Concedir privilegis als rols
GRANT SELECT ON empresa.* TO 'rol_lectura';

GRANT 'rol_lectura' TO 'rol_vendes';
GRANT INSERT, UPDATE ON empresa.comandes TO 'rol_vendes';
GRANT INSERT, UPDATE ON empresa.clients TO 'rol_vendes';

GRANT 'rol_lectura' TO 'rol_analista';
-- rol_analista ja hereta SELECT via rol_lectura

GRANT 'rol_analista' TO 'rol_admin';
GRANT ALL PRIVILEGES ON empresa.* TO 'rol_admin';

-- Crear usuaris i assignar rols
CREATE USER 'anna'@'%' IDENTIFIED BY 'Pass#Anna2024!';
CREATE USER 'bernat'@'%' IDENTIFIED BY 'Pass#Bernat2024!';
CREATE USER 'carla'@'%' IDENTIFIED BY 'Pass#Carla2024!';

GRANT 'rol_lectura' TO 'anna'@'%';
GRANT 'rol_vendes' TO 'bernat'@'%';
GRANT 'rol_vendes' TO 'carla'@'%';

-- Activar el rol per defecte (important a MySQL!)
SET DEFAULT ROLE 'rol_lectura' TO 'anna'@'%';
SET DEFAULT ROLE 'rol_vendes' TO 'bernat'@'%', 'carla'@'%';

-- Verificar rols assignats
SELECT * FROM mysql.role_edges WHERE TO_USER IN ('anna', 'bernat', 'carla');

SET DEFAULT ROLE a MySQL

A MySQL 8+, els rols assignats no s'activen automàticament en connectar-se tret que s'estableixi amb SET DEFAULT ROLE. Sense això, l'usuari es connecta sense cap rol actiu. Podeu activar activate_all_roles_on_login = ON al my.cnf per activar tots els rols automàticament.

USE empresa;

-- Crear rols de base de dades
CREATE ROLE rol_lectura;
CREATE ROLE rol_vendes;
CREATE ROLE rol_analista;
CREATE ROLE rol_admin;

-- Concedir privilegis als rols
GRANT SELECT ON SCHEMA::dbo TO rol_lectura;

GRANT SELECT ON dbo.comandes TO rol_vendes;
GRANT INSERT, UPDATE ON dbo.comandes TO rol_vendes;
GRANT SELECT ON dbo.clients TO rol_vendes;
GRANT INSERT, UPDATE ON dbo.clients TO rol_vendes;

GRANT SELECT ON SCHEMA::dbo TO rol_analista;

GRANT CREATE TABLE TO rol_admin;
GRANT ALTER ON SCHEMA::dbo TO rol_admin;

-- Crear usuaris i assignar rols
CREATE LOGIN anna WITH PASSWORD = 'Pass#Anna2024!';
CREATE USER anna FOR LOGIN anna;
ALTER ROLE rol_lectura ADD MEMBER anna;

CREATE LOGIN bernat WITH PASSWORD = 'Pass#Bernat2024!';
CREATE USER bernat FOR LOGIN bernat;
ALTER ROLE rol_vendes ADD MEMBER bernat;

CREATE LOGIN carla WITH PASSWORD = 'Pass#Carla2024!';
CREATE USER carla FOR LOGIN carla;
ALTER ROLE rol_vendes ADD MEMBER carla;

-- SQL Server no suporta herència directa de rols
-- Solució: afegir un rol com a membre d'un altre
ALTER ROLE rol_vendes ADD MEMBER rol_lectura;  -- Herència via membresíe

-- Verificar membres d'un rol
SELECT r.name AS rol, m.name AS membre
FROM sys.database_role_members dm
JOIN sys.database_principals r ON dm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON dm.member_principal_id = m.principal_id
WHERE r.name LIKE 'rol_%';

-- Crear rols
CREATE ROLE rol_lectura;
CREATE ROLE rol_vendes;
CREATE ROLE rol_analista;
CREATE ROLE rol_admin;

-- Concedir privilegis als rols
GRANT SELECT ON empresa.clients TO rol_lectura;
GRANT SELECT ON empresa.productes TO rol_lectura;
GRANT SELECT ON empresa.comandes TO rol_lectura;

GRANT rol_lectura TO rol_vendes;
GRANT INSERT, UPDATE ON empresa.comandes TO rol_vendes;
GRANT INSERT ON empresa.clients TO rol_vendes;

GRANT rol_lectura TO rol_analista;
GRANT SELECT ANY TABLE TO rol_analista;

GRANT rol_analista TO rol_admin;
GRANT CREATE TABLE TO rol_admin;
GRANT DROP ANY TABLE TO rol_admin;

-- Crear usuaris i assignar rols
CREATE USER anna IDENTIFIED BY "Pass#Anna2024!" ACCOUNT UNLOCK;
GRANT CREATE SESSION TO anna;
GRANT rol_lectura TO anna;

CREATE USER bernat IDENTIFIED BY "Pass#Bernat2024!" ACCOUNT UNLOCK;
GRANT CREATE SESSION TO bernat;
GRANT rol_vendes TO bernat;

-- Establir rol per defecte
ALTER USER bernat DEFAULT ROLE rol_vendes;

-- Verificar rols concedits
SELECT grantee, granted_role, admin_option, default_role
FROM dba_role_privs
WHERE grantee IN ('ANNA', 'BERNAT')
ORDER BY grantee, granted_role;

---

Rols predefinits del sistema

Cada SGBD inclou rols predefinits per a tasques habituals d'administració i monitoratge. Usar rols predefinits és millor que concedir privilegis individuals perquè estan ben documentats i es mantenen entre versions.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Rols predefinits a PostgreSQL 14+
-- Llistar tots els rols predefinits
SELECT rolname, rolsuper, rolcanlogin
FROM pg_roles
WHERE rolname LIKE 'pg_%'
ORDER BY rolname;

-- pg_read_all_data: llegir qualsevol taula, vista o seqüència
GRANT pg_read_all_data TO analista;

-- pg_write_all_data: escriure a qualsevol taula
GRANT pg_write_all_data TO etl_user;

-- pg_monitor: accedir a vistes de monitoratge i estadístiques
GRANT pg_monitor TO monitor_app;

-- pg_signal_backend: enviar senyals a processos backend (cancel·lar consultes)
GRANT pg_signal_backend TO helpdesk;

-- pg_read_server_files: llegir fitxers del servidor (COPY TO)
GRANT pg_read_server_files TO backup_user;

-- pg_write_server_files: escriure fitxers al servidor (COPY FROM)
GRANT pg_write_server_files TO import_user;

-- pg_execute_server_program: executar programes externs
-- (usar amb molta precaució)
GRANT pg_execute_server_program TO trusted_admin;

-- MySQL 8+ inclou rols predefinits via la base de dades sys
-- No té rols predefinits del sistema com PostgreSQL,
-- però és comú crear rols estàndard:

-- Comprovar rols existents
SELECT user, host FROM mysql.user WHERE account_locked = 'N';

-- A MariaDB, alguns plugins afegeixen rols predefinits.
-- Rols comuns que solen crear-se manualment en entorns d'empresa:
CREATE ROLE IF NOT EXISTS 'read_only';
GRANT SELECT ON *.* TO 'read_only';

CREATE ROLE IF NOT EXISTS 'rw_role';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'rw_role';

-- Rols de monitoratge per a eines com Prometheus/Grafana
CREATE ROLE IF NOT EXISTS 'monitoring';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitoring';
GRANT SELECT ON performance_schema.* TO 'monitoring';
GRANT SELECT ON sys.* TO 'monitoring';

-- Rols de servidor fixes (a nivell d'instància)
-- sysadmin: control total del servidor
-- serveradmin: pot canviar configuració del servidor
-- securityadmin: gestiona logins i assignació de privilegis
-- processadmin: pot acabar processos
-- setupadmin: gestiona servidors enllaçats
-- bulkadmin: pot executar BULK INSERT
-- diskadmin: gestiona fitxers de disc
-- dbcreator: pot crear, modificar i eliminar bases de dades
-- public: rol base per a tots els logins

-- Consultar rols de servidor i membres
SELECT r.name AS rol, m.name AS membre
FROM sys.server_role_members rm
JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.server_principals m ON rm.member_principal_id = m.principal_id
ORDER BY r.name, m.name;

-- Rols de base de dades fixes (USE <base_de_dades>)
-- db_owner: tots els privilegis a la BD
-- db_securityadmin: gestiona rols i permisos a la BD
-- db_accessadmin: gestiona usuaris de la BD
-- db_backupoperator: pot fer còpies de seguretat
-- db_ddladmin: pot executar DDL (CREATE, ALTER, DROP)
-- db_datawriter: INSERT, UPDATE, DELETE a totes les taules
-- db_datareader: SELECT a totes les taules
-- db_denydatawriter: DENY INSERT, UPDATE, DELETE
-- db_denydatareader: DENY SELECT

USE empresa;

-- Afegir usuari al rol db_datareader
ALTER ROLE db_datareader ADD MEMBER anna;

-- Afegir usuari al rol db_datawriter
ALTER ROLE db_datawriter ADD MEMBER bernat;

-- Consultar membres dels rols de BD
SELECT r.name AS rol, m.name AS membre
FROM sys.database_role_members dm
JOIN sys.database_principals r ON dm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON dm.member_principal_id = m.principal_id
ORDER BY r.name, m.name;

-- Rols predefinits d'Oracle (selecció dels més importants)

-- CONNECT: privilegi CREATE SESSION (connectar-se)
-- Noti's que a Oracle modern CONNECT ja no inclou CREATE TABLE
GRANT CONNECT TO app_vendes;

-- RESOURCE: CREATE TABLE, SEQUENCE, PROCEDURE, TRIGGER, TYPE, etc.
GRANT RESOURCE TO dev_backend;

-- DBA: tots els privilegis del sistema amb ADMIN OPTION
GRANT DBA TO admin_dba;

-- SELECT_CATALOG_ROLE: SELECT sobre vistes del diccionari de dades (DBA_*)
GRANT SELECT_CATALOG_ROLE TO auditor;

-- EXECUTE_CATALOG_ROLE: EXECUTE sobre paquets del catàleg
GRANT EXECUTE_CATALOG_ROLE TO dev_backend;

-- EXP_FULL_DATABASE / IMP_FULL_DATABASE: exportació/importació completa
GRANT EXP_FULL_DATABASE TO backup_user;
GRANT IMP_FULL_DATABASE TO restore_user;

-- SCHEDULER_ADMIN: gestió de tasques programades (DBMS_SCHEDULER)
GRANT SCHEDULER_ADMIN TO jobs_admin;

-- Llistar tots els rols predefinits del sistema
SELECT role FROM dba_roles ORDER BY role;

-- Verificar privilegis inclosos en un rol
SELECT privilege, admin_option
FROM role_sys_privs
WHERE role = 'RESOURCE'
ORDER BY privilege;

---

Rols niats (herència de rols)

La majoria de SGBD permeten que un rol hereti els privilegis d'un altre rol (rols niats). Això permet construir jerarquies de permisos de forma modular.

PostgreSQLMySQL 8+SQL ServerOracle

-- Exemple de jerarquia de rols
CREATE ROLE base_lectura;
CREATE ROLE analista_junior;
CREATE ROLE analista_senior;
CREATE ROLE cap_analisi;

-- Privilegis bàsics
GRANT CONNECT ON DATABASE empresa TO base_lectura;
GRANT USAGE ON SCHEMA public TO base_lectura;
GRANT SELECT ON taula_vendes, taula_clients TO base_lectura;

-- Herència: analista_junior hereta base_lectura
GRANT base_lectura TO analista_junior;
GRANT SELECT ON taula_productes, taula_estoc TO analista_junior;

-- analista_senior hereta analista_junior
GRANT analista_junior TO analista_senior;
GRANT SELECT ON taula_financera TO analista_senior;
GRANT EXECUTE ON FUNCTION generar_informe() TO analista_senior;

-- cap_analisi hereta analista_senior
GRANT analista_senior TO cap_analisi;
GRANT INSERT, UPDATE ON taula_informes TO cap_analisi;

-- Verificar la cadena d'herència
WITH RECURSIVE herencia AS (
    SELECT r.rolname AS rol, r.rolname AS arrel, 0 AS nivell
    FROM pg_roles r
    WHERE r.rolname = 'cap_analisi'
    UNION ALL
    SELECT r.rolname, h.arrel, h.nivell + 1
    FROM pg_auth_members am
    JOIN pg_roles r ON am.roleid = r.oid
    JOIN herencia h ON am.member = (SELECT oid FROM pg_roles WHERE rolname = h.rol)
)
SELECT * FROM herencia ORDER BY nivell;

-- Jerarquia de rols a MySQL 8+
CREATE ROLE 'base_lectura', 'analista_junior', 'analista_senior', 'cap_analisi';

GRANT SELECT ON empresa.vendes TO 'base_lectura';
GRANT SELECT ON empresa.clients TO 'base_lectura';

-- Herència: analista_junior hereta base_lectura
GRANT 'base_lectura' TO 'analista_junior';
GRANT SELECT ON empresa.productes TO 'analista_junior';

-- analista_senior hereta analista_junior
GRANT 'analista_junior' TO 'analista_senior';
GRANT SELECT ON empresa.financera TO 'analista_senior';

-- cap_analisi hereta analista_senior
GRANT 'analista_senior' TO 'cap_analisi';
GRANT INSERT, UPDATE ON empresa.informes TO 'cap_analisi';

-- Verificar cadena de rols
SELECT * FROM mysql.role_edges
WHERE FROM_USER IN ('base_lectura', 'analista_junior', 'analista_senior');

-- SQL Server gestiona l'herència afegint rols com a membres d'altres rols
USE empresa;

CREATE ROLE base_lectura;
CREATE ROLE analista_junior;
CREATE ROLE analista_senior;
CREATE ROLE cap_analisi;

GRANT SELECT ON dbo.vendes TO base_lectura;
GRANT SELECT ON dbo.clients TO base_lectura;

-- "Herència" afegint base_lectura com a membre d'analista_junior
ALTER ROLE analista_junior ADD MEMBER base_lectura;
GRANT SELECT ON dbo.productes TO analista_junior;

ALTER ROLE analista_senior ADD MEMBER analista_junior;
GRANT SELECT ON dbo.financera TO analista_senior;

ALTER ROLE cap_analisi ADD MEMBER analista_senior;
GRANT INSERT, UPDATE ON dbo.informes TO cap_analisi;

-- Oracle suporta rols niats directament
CREATE ROLE base_lectura;
CREATE ROLE analista_junior;
CREATE ROLE analista_senior;
CREATE ROLE cap_analisi;

GRANT SELECT ON empresa.vendes TO base_lectura;
GRANT SELECT ON empresa.clients TO base_lectura;

GRANT base_lectura TO analista_junior;
GRANT SELECT ON empresa.productes TO analista_junior;

GRANT analista_junior TO analista_senior;
GRANT SELECT ON empresa.financera TO analista_senior;

GRANT analista_senior TO cap_analisi;
GRANT INSERT, UPDATE ON empresa.informes TO cap_analisi;

-- Verificar privilegis efectius via rols niats
SELECT role, privilege
FROM role_sys_privs
START WITH role = 'CAP_ANALISI'
CONNECT BY PRIOR role = privilege;

---

Revocació de rols

PostgreSQLMySQL 8+SQL ServerOracle

-- Revocar un rol d'un usuari
REVOKE rol_vendes FROM bernat;

-- Revocar un rol heretat (trenca la cadena d'herència)
REVOKE rol_lectura FROM rol_vendes;

-- Eliminar un rol (cal revocar-lo de tots els usuaris primer)
DROP ROLE rol_lectura;

-- Verificar que s'ha revocat
SELECT rolname, member
FROM pg_auth_members am
JOIN pg_roles r ON am.roleid = r.oid
WHERE r.rolname = 'rol_vendes';

-- Revocar un rol d'un usuari
REVOKE 'rol_vendes' FROM 'bernat'@'%';

-- Eliminar un rol
DROP ROLE 'rol_lectura';

-- Verificar
SHOW GRANTS FOR 'bernat'@'%';

USE empresa;

-- Treure un usuari d'un rol
ALTER ROLE rol_vendes DROP MEMBER bernat;

-- Eliminar un rol (cal que estigui buit de membres)
DROP ROLE rol_lectura;

-- Revocar un rol d'un usuari
REVOKE rol_vendes FROM bernat;

-- Eliminar un rol
DROP ROLE rol_lectura;

-- Verificar
SELECT granted_role FROM dba_role_privs WHERE grantee = 'BERNAT';

---

Miniactivitat — AC0305 · Rols i RBAC

Miniactivitat — AC0305

Implementeu el model RBAC de la vostra empresa fictícia. L'empresa té els departaments següents:

Departament	Necessitats d'accés
Vendes	Llegir i modificar comandes i clients
Comptabilitat	Llegir factures i pagaments; no modificar
RRHH	Llegir i modificar empleats; no veure salaris
Direcció	Lectura total; crear informes
IT	Accés d'administració

Tasca 1: Definiu l'estructura de rols (quins rols creeu i com s'hereten).

Tasca 2: Implementeu-la al SGBD que tingueu actiu (preferiblement PostgreSQL).

Tasca 3: Creeu almenys un usuari per departament i assigneu-li el rol corresponent.

Tasca 4: Verifiqueu que cada usuari pot fer el que ha de poder fer i no pot fer el que no ha de poder fer. Documenteu les proves amb captures de pantalla.

Reflexió: Quin avantatge té usar rols respecte de gestionar privilegis directament per usuari? Descriviu-ho amb un exemple concret del vostre esquema.