Seguretat i permisos

Principi del mínim privilegi

El principi del mínim privilegi (Principle of Least Privilege) estableix que cada usuari, aplicació o procés ha de tenir només els privilegis estrictament necessaris per realitzar la seva funció, i res mes.

Aplicar aquest principi redueix dràsticament l'impacte d'un incident de seguretat: si un compte d'aplicació és compromès, l'atacant només podrà fer el que aquell compte pot fer (per exemple, llegir la BD de vendes), no tot el que és possible (eliminar totes les BD, llegir dades sensibles d'altres sistemes, crear superusuaris).

Un principi transversal a tota la seguretat informatica

El principi del minim privilegi no es exclusiu de les bases de dades. S'aplica a qualsevol capa del sistema:

• Firewalls i ACLs de xarxa
• Comptes de servei del sistema operatiu
• APIs i microserveis
• Cloud (AWS, Azure, GCP)

En tots els casos l'objectiu es el mateix: limitar el blast radius (abast del dany) en cas que un component sigui compromès.

---

Gestió d'usuaris

Abans de crear usuaris i assignar-los permisos, cal entendre la diferència entre usuaris i rols, i per quin motiu la bona pràctica és assignar privilegis als rols i no directament als usuaris.

Un usuari és un compte que pot autenticar-se al SGBD (té nom i contrasenya). Un rol és un contenidor de privilegis sense capacitat de connexió pròpia. Quan un usuari rep un rol, hereta automàticament tots els privilegis que aquell rol té concedits.

flowchart LR
    subgraph Rols
        RL["rol_lectura\n· SELECT clients\n· SELECT productes"]
        RE["rol_editor\n· INSERT comandes\n· UPDATE comandes\n+ hereta rol_lectura"]
        RA["rol_admin\n· DELETE\n· EXECUTE\n+ hereta rol_editor"]
    end

    subgraph Usuaris
        U1["app_reportes"]
        U2["app_vendes"]
        U3["gestor_bd"]
    end

    RL --> U1
    RE --> U2
    RA --> U3

La raó per preferir rols és el manteniment: si el dia de demà la taula productes necessita un nou permís UPDATE, el canvieu un sol cop al rol i tots els usuaris que tinguin aquell rol el reben de forma immediata. Si haguéssiu assignat els permisos directament a cada usuari, hauríeu de modificar-los un per un.

La regla practica

Creeu l'usuari, assigneu-li un rol, i poseu tots els privilegis al rol. No concediu mai privilegis directament a un usuari llevat d'excepcions molt justificades (comptes de servei únics, privilegis temporals d'emergència, etc.).

Creació i gestió d'usuaris

PostgreSQLMySQL / MariaDBSQL ServerOracle

A PostgreSQL un usuari és internament un ROLE amb l'atribut LOGIN. Les dues formes (CREATE USER i CREATE ROLE ... LOGIN) son equivalents.

CREATE USER nom [ WITH ] [ opcio ... ]

Opcions principals:
  PASSWORD 'contrasenya'           -- contrasenya del compte
  VALID UNTIL 'timestamp'          -- data d'expiracio de la contrasenya
  LOGIN | NOLOGIN                  -- permet o denega la connexio
  SUPERUSER | NOSUPERUSER          -- privilegis de superusuari
  CREATEDB | NOCREATEDB            -- pot crear bases de dades
  CREATEROLE | NOCREATEROLE        -- pot crear altres rols
  CONNECTION LIMIT n               -- maxim de connexions simultànies (-1 = sense limit)
  IN ROLE rol [, ...]              -- afegeix l'usuari als rols indicats en crear-lo

ALTER USER nom [ WITH ] [ opcio ... ]  -- mateixa sintaxi que CREATE USER
ALTER USER nom RENAME TO nou_nom

DROP USER [ IF EXISTS ] nom [, ...]

A MySQL l'usuari s'identifica per la parella 'nom'@'host'. El host controla des d'on es permet la connexio: localhost (connexio local), '%' (qualsevol IP), o una IP/subxarxa concreta.

CREATE USER [ IF NOT EXISTS ] 'nom'@'host' [ opcions ... ]

Opcions principals:
  IDENTIFIED BY 'contrasenya'                -- metode d'autenticacio per contrasenya
  IDENTIFIED WITH plugin BY 'contrasenya'    -- plugin concret (caching_sha2_password, mysql_native_password...)
  PASSWORD EXPIRE [ DEFAULT | NEVER | INTERVAL n DAY ]
  ACCOUNT { LOCK | UNLOCK }                  -- bloqueja o desbloqueja el compte
  REQUIRE { NONE | SSL | X509 }              -- requereix connexio segura
  WITH MAX_QUERIES_PER_HOUR n                -- limitacio de consultes per hora
       MAX_CONNECTIONS_PER_HOUR n            -- limitacio de connexions per hora
       MAX_USER_CONNECTIONS n                -- connexions simultànies maximes

ALTER USER 'nom'@'host' [ opcions ... ]      -- mateixa sintaxi que CREATE USER

DROP USER [ IF EXISTS ] 'nom'@'host' [, ...]

SQL Server separa en dos conceptes: el login (identitat a nivell de servidor, permet connectar-se a la instancia) i l'usuari (identitat a nivell de base de dades, permet operar dins d'una BD concreta). Cal crear els dos.

-- Nivell servidor
CREATE LOGIN nom
    WITH PASSWORD = 'contrasenya'
       [ , DEFAULT_DATABASE = bd ]           -- BD per defecte en connectar
       [ , DEFAULT_LANGUAGE = llengua ]
       [ , CHECK_EXPIRATION = { ON | OFF } ] -- comprova si la contrasenya ha expirat
       [ , CHECK_POLICY = { ON | OFF } ]     -- aplica la politica de contrasenyes de Windows

ALTER LOGIN nom WITH PASSWORD = 'nova'
                     [ OLD_PASSWORD = 'antiga' ]
ALTER LOGIN nom ENABLE | DISABLE
DROP LOGIN nom

-- Nivell base de dades (cal estar dins de la BD)
CREATE USER nom
    { FOR | FROM } LOGIN login_nom
    [ WITH DEFAULT_SCHEMA = esquema ]        -- esquema per defecte per a objectes sense prefix

CREATE USER nom WITHOUT LOGIN                -- usuari sense login (per a connexions de servei)

DROP USER [ IF EXISTS ] nom

A Oracle un usuari i el seu esquema son la mateixa entitat: quan es crea un usuari es crea automaticament un esquema buit amb el mateix nom. Per poder inserir dades, l'usuari necessita QUOTA sobre un tablespace.

CREATE USER nom
    IDENTIFIED BY contrasenya
    [ DEFAULT TABLESPACE tablespace ]        -- on es creen les taules de l'usuari
    [ TEMPORARY TABLESPACE tablespace ]      -- per a operacions temporals (sorts, hash joins)
    [ QUOTA { n { K | M | G } | UNLIMITED }
          ON tablespace ]                    -- espai maxim al tablespace (repetible per a cada ts)
    [ PROFILE perfil ]                       -- perfil de seguretat (intents fallits, caducitat...)
    [ PASSWORD EXPIRE ]                      -- obliga a canviar la contrasenya en el primer login
    [ ACCOUNT { LOCK | UNLOCK } ]            -- bloqueja o desbloqueja el compte

ALTER USER nom IDENTIFIED BY nova_contrasenya
ALTER USER nom QUOTA n ON tablespace
ALTER USER nom ACCOUNT LOCK | UNLOCK
ALTER USER nom PASSWORD EXPIRE

DROP USER nom [ CASCADE ]
-- CASCADE elimina tots els objectes de l'esquema de l'usuari
-- Sense CASCADE falla si l'usuari te objectes propis

Exemples pràctics:

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Crear un usuari (ROL amb LOGIN)
CREATE USER app_vendes WITH PASSWORD 'contrasenya_segura_2025';

-- Crear un usuari amb data d'expiració
CREATE USER usuari_temporal WITH PASSWORD 'temp2025'
    VALID UNTIL '2026-01-01';

-- Canviar la contrasenya
ALTER USER app_vendes WITH PASSWORD 'nova_contrasenya_2025';

-- Deshabilitar el login (sense eliminar l'usuari)
ALTER USER app_vendes NOLOGIN;

-- Habilitar de nou
ALTER USER app_vendes LOGIN;

-- Eliminar un usuari
DROP USER app_vendes;

-- Llistar tots els usuaris
SELECT usename, usecreatedb, usecreaterole, usesuper
FROM pg_user;
-- O:
\du

-- Crear un usuari local
CREATE USER 'app_vendes'@'localhost' IDENTIFIED BY 'contrasenya_segura_2025';

-- Crear un usuari amb accés des de qualsevol IP
CREATE USER 'app_vendes'@'%' IDENTIFIED BY 'contrasenya_segura_2025';

-- Canviar la contrasenya
ALTER USER 'app_vendes'@'localhost' IDENTIFIED BY 'nova_contrasenya';
-- O:
SET PASSWORD FOR 'app_vendes'@'localhost' = PASSWORD('nova_contrasenya');

-- Eliminar
DROP USER 'app_vendes'@'localhost';

-- Llistar usuaris
SELECT user, host, plugin FROM mysql.user;

-- Crear un login a nivell de servidor
CREATE LOGIN app_vendes_login WITH PASSWORD = 'Contrasenya_Segura_2025!';

-- Crear un usuari de BD associat al login
USE gbd_practica;
GO
CREATE USER app_vendes FOR LOGIN app_vendes_login;

-- Canviar la contrasenya
ALTER LOGIN app_vendes_login WITH PASSWORD = 'Nova_Contrasenya_2025!';

-- Desactivar el login
ALTER LOGIN app_vendes_login DISABLE;

-- Llistar logins
SELECT name, type_desc, is_disabled FROM sys.server_principals
WHERE type IN ('S', 'U');

-- A Oracle, un usuari i el seu esquema (schema) son la mateixa cosa.
-- Crear un usuari (CREATE USER requereix el privilegi CREATE USER o DBA)
CREATE USER app_vendes IDENTIFIED BY "Contrasenya_Segura_2025"
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    QUOTA 100M ON users;   -- espai maxim que pot ocupar l'esquema

-- Sense QUOTA l'usuari no pot crear taules ni inserir dades

-- Canviar la contrasenya
ALTER USER app_vendes IDENTIFIED BY "Nova_Contrasenya_2025";

-- Establir data d'expiracio de la contrasenya
ALTER USER app_vendes PASSWORD EXPIRE;

-- Bloquejar el compte (sense eliminar l'usuari)
ALTER USER app_vendes ACCOUNT LOCK;

-- Desbloquejar
ALTER USER app_vendes ACCOUNT UNLOCK;

-- Eliminar l'usuari i tot el seu esquema (taules, vistes, etc.)
DROP USER app_vendes CASCADE;
-- Sense CASCADE, falla si l'usuari te objectes propis

-- Llistar usuaris
SELECT username, account_status, expiry_date, default_tablespace
FROM dba_users
ORDER BY username;

Creació i gestió de rols

PostgreSQLMySQL / MariaDBSQL ServerOracle

A PostgreSQL un rol sense LOGIN és un contenidor de privilegis pur. Pot tenir membres (altres rols o usuaris) i pot heretar els privilegis d'altres rols.

CREATE ROLE nom [ WITH ] [ opcio ... ]

Opcions principals:
  NOLOGIN                          -- per defecte; no pot connectar-se directament
  LOGIN                            -- si es vol que el rol pugui connectar-se (= usuari)
  SUPERUSER | NOSUPERUSER
  CREATEDB | NOCREATEDB
  CREATEROLE | NOCREATEROLE
  INHERIT | NOINHERIT              -- hereta (o no) els privilegis dels rols pare
  PASSWORD 'contrasenya'           -- només rellevant si LOGIN
  VALID UNTIL 'timestamp'
  IN ROLE rol [, ...]              -- el nou rol passa a ser membre dels rols indicats
  ROLE rol [, ...]                 -- afegeix els rols indicats com a membres d'aquest

-- Assignar / revocar membres:
GRANT rol TO usuari_o_rol [ WITH ADMIN OPTION ]
REVOKE rol FROM usuari_o_rol

DROP ROLE [ IF EXISTS ] nom [, ...]

A MySQL/MariaDB els rols son usuaris sense host i sense contrasenya. Es creen amb CREATE ROLE i s'assignen amb GRANT rol TO usuari. Per defecte no s'activen automàticament en fer login: cal SET DEFAULT ROLE o SET ROLE.

CREATE ROLE [ IF NOT EXISTS ] nom [, nom ...]
-- Els rols no tenen opcions addicionals en crear-los

-- Assignar privilegis al rol (igual que a un usuari):
GRANT privilegi ON objecte TO nom_rol

-- Assignar el rol a un usuari:
GRANT nom_rol [, ...] TO 'usuari'@'host'

-- Activar el rol per defecte (sense aixo l'usuari ha de fer SET ROLE cada sessio):
SET DEFAULT ROLE { nom_rol [, ...] | ALL | NONE } TO 'usuari'@'host'

-- Revocar:
REVOKE nom_rol FROM 'usuari'@'host'

DROP ROLE [ IF EXISTS ] nom [, ...]

SQL Server distingeix entre rols de servidor (afecten la instancia sencera) i rols de base de dades (afecten una BD concreta). Els rols personalitzats son sempre de base de dades. A més existeixen rols fixes predefinits que no es poden modificar.

-- Rols de base de dades personalitzats:
CREATE ROLE nom [ AUTHORIZATION propietari ]

-- Gestio de membres:
ALTER ROLE nom ADD MEMBER usuari_o_rol
ALTER ROLE nom DROP MEMBER usuari_o_rol

DROP ROLE [ IF EXISTS ] nom

-- Rols fixes de base de dades predefinits (no modificables):
  db_owner          -- tots els permisos a la BD
  db_securityadmin  -- gestio de permisos i rols
  db_datareader     -- SELECT a totes les taules
  db_datawriter     -- INSERT, UPDATE, DELETE a totes les taules
  db_ddladmin       -- pot executar DDL (CREATE, ALTER, DROP)
  db_backupoperator -- pot fer backups de la BD

-- Rols fixes de servidor predefinits:
  sysadmin          -- control total de la instancia
  securityadmin     -- gestio de logins
  dbcreator         -- pot crear i modificar BD
  bulkadmin         -- pot executar BULK INSERT

A Oracle els rols poden tenir contrasenya pròpia (s'ha d'introduir per activar-los) o ser d'activació automàtica. Els rols predefinits CONNECT, RESOURCE i DBA existeixen per compatibilitat historica però en producció es recomana crear rols propis.

CREATE ROLE nom
    [ NOT IDENTIFIED                    -- s'activa automaticament (mes habitual)
    | IDENTIFIED BY contrasenya         -- l'usuari ha d'introduir la contrasenya per activar-lo
    | IDENTIFIED EXTERNALLY             -- autenticacio via sistema operatiu
    | IDENTIFIED GLOBALLY ]             -- autenticacio via LDAP/directori central

-- Assignar privilegis al rol (igual que a un usuari):
GRANT privilegi ON objecte TO nom_rol
GRANT rol_pare TO nom_rol              -- herencia de rols

-- Assignar el rol a un usuari:
GRANT nom_rol [, ...] TO usuari [ WITH ADMIN OPTION ]

-- Revocar:
REVOKE nom_rol FROM usuari

DROP ROLE nom

-- Rols predefinits destacats:
  CONNECT   -- historicament CREATE SESSION + altres; avui dia nomes CREATE SESSION
  RESOURCE  -- CREATE TABLE, SEQUENCE, PROCEDURE, TRIGGER, TYPE, CLUSTER
  DBA       -- tots els privilegis de sistema amb ADMIN OPTION (equivalent a sysadmin)

Exemples pràctics:

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Crear rols
CREATE ROLE rol_lectura;
CREATE ROLE rol_editor;
CREATE ROLE rol_admin;

-- Assignar privilegis als rols
GRANT CONNECT ON DATABASE gbd_nom_cognom 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_editor;  -- L'editor hereta els privilegis del lector
GRANT INSERT, UPDATE ON TABLE comandes, linies_comanda TO rol_editor;

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

-- Assignar rols a usuaris
GRANT rol_lectura TO app_reportes;
GRANT rol_editor TO app_vendes;
GRANT rol_admin TO gestor_bd;

-- Revocar un rol
REVOKE rol_editor FROM app_vendes;

-- Veure els rols d'un usuari
SELECT r.rolname, m.member
FROM pg_auth_members m
JOIN pg_roles r ON m.roleid = r.oid
JOIN pg_roles u ON m.member = u.oid
WHERE u.rolname = 'app_vendes';

-- Crear rols (MySQL 8.0+, MariaDB 10.0.5+)
CREATE ROLE rol_lectura, rol_editor, rol_admin;

-- Assignar privilegis als rols
GRANT SELECT ON gbd_nom_cognom.* TO rol_lectura;
GRANT SELECT, INSERT, UPDATE ON gbd_nom_cognom.comandes TO rol_editor;
GRANT ALL PRIVILEGES ON gbd_nom_cognom.* TO rol_admin;

-- Assignar rols a usuaris
GRANT rol_lectura TO 'app_reportes'@'localhost';
GRANT rol_lectura, rol_editor TO 'app_vendes'@'localhost';
GRANT rol_admin TO 'gestor_bd'@'localhost';

-- Activar rols per defecte (sense aixo l'usuari ha de fer SET ROLE manualment)
SET DEFAULT ROLE ALL TO 'app_vendes'@'localhost';
SET DEFAULT ROLE ALL TO 'app_reportes'@'localhost';
SET DEFAULT ROLE ALL TO 'gestor_bd'@'localhost';

-- Veure els rols d'un usuari
SHOW GRANTS FOR 'app_vendes'@'localhost';

-- Revocar un rol
REVOKE rol_editor FROM 'app_vendes'@'localhost';

USE gbd_practica;
GO

-- Crear rols de base de dades personalitzats
CREATE ROLE rol_lectura;
CREATE ROLE rol_editor;
CREATE ROLE rol_admin;

-- Assignar permisos als rols
GRANT SELECT ON SCHEMA::dbo TO rol_lectura;

GRANT SELECT, INSERT, UPDATE ON SCHEMA::dbo TO rol_editor;

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO rol_admin;
GRANT EXECUTE ON SCHEMA::dbo TO rol_admin;

-- Assignar rols a usuaris de la BD
ALTER ROLE rol_lectura ADD MEMBER app_reportes;
ALTER ROLE rol_editor  ADD MEMBER app_vendes;
ALTER ROLE rol_admin   ADD MEMBER gestor_bd;

-- SQL Server tambe te rols fixes de BD predefinits:
-- db_datareader  → SELECT a totes les taules
-- db_datawriter  → INSERT, UPDATE, DELETE a totes les taules
-- db_owner       → tots els permisos a la BD
-- db_securityadmin → gestio de permisos
ALTER ROLE db_datareader ADD MEMBER app_reportes;

-- Treure un usuari d'un rol
ALTER ROLE rol_editor DROP MEMBER app_vendes;

-- Veure els membres d'un rol
SELECT dp.name AS membre
FROM sys.database_role_members rm
JOIN sys.database_principals dp ON rm.member_principal_id = dp.principal_id
JOIN sys.database_principals r  ON rm.role_principal_id  = r.principal_id
WHERE r.name = 'rol_editor';

-- Crear rols
CREATE ROLE rol_lectura;
CREATE ROLE rol_editor;
CREATE ROLE rol_admin;

-- Assignar privilegis als rols
GRANT CREATE SESSION TO rol_lectura;
GRANT SELECT ON gbd_user.clients TO rol_lectura;
GRANT SELECT ON gbd_user.comandes TO rol_lectura;
GRANT SELECT ON gbd_user.linies_comanda TO rol_lectura;
GRANT SELECT ON gbd_user.productes TO rol_lectura;

GRANT rol_lectura TO rol_editor;  -- l'editor hereta els privilegis del lector
GRANT INSERT, UPDATE ON gbd_user.comandes TO rol_editor;
GRANT INSERT, UPDATE ON gbd_user.linies_comanda TO rol_editor;

GRANT rol_editor TO rol_admin;
GRANT DELETE ON gbd_user.comandes TO rol_admin;
GRANT DELETE ON gbd_user.linies_comanda TO rol_admin;

-- Oracle tambe te rols predefinits:
-- CONNECT      → CREATE SESSION (historic, no recomanat per a produccio)
-- RESOURCE     → CREATE TABLE, CREATE SEQUENCE, CREATE PROCEDURE, etc.
-- DBA          → tots els privilegis de sistema (equivalent a superuser)

-- Assignar rols a usuaris
GRANT rol_lectura TO app_reportes;
GRANT rol_editor  TO app_vendes;
GRANT rol_admin   TO gestor_bd;

-- Revocar un rol d'un usuari
REVOKE rol_editor FROM app_vendes;

-- Veure els rols concedits a un usuari
SELECT granted_role, admin_option, default_role
FROM dba_role_privs
WHERE grantee = 'APP_VENDES';

-- Veure tots els privilegis efectius d'un usuari (directes + via rols)
SELECT privilege FROM dba_sys_privs WHERE grantee = 'APP_VENDES'
UNION
SELECT privilege FROM dba_sys_privs
WHERE grantee IN (
    SELECT granted_role FROM dba_role_privs WHERE grantee = 'APP_VENDES'
);

Matriu de privilegis per rol

Visualitzar quins privilegis té cada rol sobre quines taules ajuda a dissenyar l'esquema de seguretat:

Taula	rol_lectura	rol_editor	rol_admin
clients	SELECT	SELECT	SELECT · INSERT · UPDATE · DELETE
productes	SELECT	SELECT	SELECT · INSERT · UPDATE · DELETE
comandes	SELECT	SELECT · INSERT · UPDATE	SELECT · INSERT · UPDATE · DELETE
linies_comanda	SELECT	SELECT · INSERT · UPDATE	SELECT · INSERT · UPDATE · DELETE
categories	SELECT	SELECT	SELECT · INSERT · UPDATE · DELETE

Com llegir la matriu

Construïu una matriu com aquesta durant el disseny del sistema. Cada columna és un rol i cada fila és una taula (o vista). A l'hora de fer el GRANT, recorreu la matriu i assigneu els privilegis cel·la a cel·la. Quan un requisit canvia, sabeu exactament quin GRANT o REVOKE heu de fer.

---

GRANT: assignar privilegis

Els privilegis es divideixen en dues categories amb finalitats molt diferents:

• Privilegis de sistema (o de connexió): controlen si un usuari pot connectar-se, crear objectes, o realitzar accions administratives. S'atorguen a nivell de servidor o de base de dades, no sobre objectes concrets.
• Privilegis sobre dades: controlen quines operacions (SELECT, INSERT, UPDATE, DELETE) pot fer un usuari sobre taules, vistes o seqüències concretes.

L'ordre correcte sempre és: primer atorgar els privilegis de sistema (sense ells l'usuari no pot ni connectar-se), i després els privilegis sobre les dades.

GRANT: privilegis de sistema (connexió i accés)

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- 1. Permetre la connexió a la base de dades
GRANT CONNECT ON DATABASE gbd_nom_cognom TO app_vendes;

-- 2. Permetre l'accés a l'esquema (sense aixo, l'usuari no veu cap objecte)
GRANT USAGE ON SCHEMA public TO app_vendes;

-- Privilegis per crear objectes propis (normalment reservats al DBA)
GRANT CREATE ON SCHEMA public TO gestor_bd;  -- pot crear taules a public
GRANT CREATE ON DATABASE gbd_nom_cognom TO gestor_bd;  -- pot crear esquemes

-- Permetre connexions temporals (per a scripts o eines externes)
GRANT TEMPORARY ON DATABASE gbd_nom_cognom TO app_vendes;

-- Verificar els privilegis de sistema concedits
SELECT grantee, privilege_type, is_grantable
FROM information_schema.role_usage_grants
WHERE object_name = 'public';

SELECT datacl FROM pg_database WHERE datname = 'gbd_nom_cognom';

-- A MySQL no hi ha una separacio tan clara entre sistema i dades.
-- Els privilegis globals (sobre *.*) son els equivalents als de sistema.

-- Permetre la connexio (el simple CREATE USER ja permet connectar-se
-- si no hi ha cap restriccio global; USAGE es un pseudo-privilegi que
-- no dona cap permis pero confirma que l'usuari pot connectar-se)
GRANT USAGE ON *.* TO 'app_vendes'@'localhost';

-- Privilegis globals per a administracio
GRANT PROCESS ON *.* TO 'monitor'@'localhost';         -- veure processos actius
GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'localhost'; -- veure estat replicacio
GRANT SHOW DATABASES ON *.* TO 'monitor'@'localhost';  -- llistar BD

-- Permetre crear objectes dins d'una BD (pero no modificar dades)
GRANT CREATE, DROP, ALTER ON gbd_nom_cognom.* TO 'gestor_bd'@'localhost';

-- Aplicar els canvis
FLUSH PRIVILEGES;

-- Verificar privilegis globals de l'usuari
SHOW GRANTS FOR 'app_vendes'@'localhost';

-- Els privilegis de SQL Server s'organitzen en tres nivells:
-- servidor (sys.server_principals), base de dades i esquema/objecte.

-- Permetre la connexio al servidor (necessari per a logins SQL)
GRANT CONNECT SQL TO app_vendes_login;  -- a nivell de servidor

-- Permetre la connexio a la BD (a nivell de base de dades)
USE gbd_practica;
GO
GRANT CONNECT TO app_vendes;

-- Permetre veure l'estat de la BD (util per a eines de monitoratge)
GRANT VIEW DATABASE STATE TO app_vendes;

-- Permetre crear objectes a l'esquema (normalment reservat al DBA)
GRANT CREATE TABLE  TO gestor_bd;
GRANT CREATE VIEW   TO gestor_bd;
GRANT ALTER ON SCHEMA::dbo TO gestor_bd;

-- Verificar els privilegis de sistema d'un login
SELECT sp.name, spe.permission_name, spe.state_desc
FROM sys.server_permissions spe
JOIN sys.server_principals sp ON spe.grantee_principal_id = sp.principal_id
WHERE sp.name = 'app_vendes_login';

-- A Oracle la separacio entre sistema i objecte es explicita.
-- Sense CREATE SESSION, l'usuari no pot fer absolutament res.

-- Permetre la connexio
GRANT CREATE SESSION TO app_vendes;

-- Permetre crear objectes propis (per a usuaris que necessiten el seu esquema)
GRANT CREATE TABLE     TO gestor_bd;
GRANT CREATE VIEW      TO gestor_bd;
GRANT CREATE SEQUENCE  TO gestor_bd;
GRANT CREATE PROCEDURE TO gestor_bd;
GRANT CREATE TRIGGER   TO gestor_bd;

-- Quota d'espai per poder inserir dades a les taules propies
-- (sense QUOTA, CREATE TABLE funciona pero INSERT falla)
ALTER USER gestor_bd QUOTA UNLIMITED ON users;
ALTER USER gestor_bd QUOTA 500M ON users;  -- o un limit concret

-- Privilegis per a monitoratge i administracio
GRANT SELECT ANY DICTIONARY TO monitor_user;  -- veure vistes del diccionari
GRANT SELECT ANY TABLE TO monitor_user;        -- llegir qualsevol taula (perigos!)

-- Verificar els privilegis de sistema d'un usuari
SELECT privilege, admin_option
FROM dba_sys_privs
WHERE grantee = 'APP_VENDES'
ORDER BY privilege;

GRANT: privilegis sobre dades

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Lectura sobre una taula concreta
GRANT SELECT ON TABLE clients TO app_vendes;

-- Lectura i escriptura sobre taules concretes
GRANT SELECT, INSERT, UPDATE ON TABLE comandes TO app_vendes;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE linies_comanda TO app_vendes;

-- Tots els privilegis sobre una taula
GRANT ALL PRIVILEGES ON TABLE productes TO app_vendes;

-- Sobre totes les taules existents de l'esquema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_vendes;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO app_vendes;

-- Privilegis per a taules i seqüencies creades en el futur (DEFAULT PRIVILEGES)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO app_vendes;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE ON SEQUENCES TO app_vendes;

-- Delegar: permetre que app_vendes cedeixi el seu SELECT a altres
GRANT SELECT ON clients TO app_vendes WITH GRANT OPTION;

-- Verificar els privilegis sobre dades d'un usuari
SELECT table_name, privilege_type, is_grantable
FROM information_schema.role_table_grants
WHERE grantee = 'app_vendes'
ORDER BY table_name;

-- Nomes lectura a tota la BD
GRANT SELECT ON gbd_nom_cognom.* TO 'app_vendes'@'localhost';

-- Lectura i escriptura sobre taules concretes
GRANT SELECT, INSERT, UPDATE ON gbd_nom_cognom.comandes
    TO 'app_vendes'@'localhost';
GRANT SELECT, INSERT, UPDATE, DELETE ON gbd_nom_cognom.linies_comanda
    TO 'app_vendes'@'localhost';

-- Tots els privilegis sobre una BD completa
GRANT ALL PRIVILEGES ON gbd_nom_cognom.* TO 'app_vendes'@'localhost';

-- Delegar: permetre que l'usuari cedeixi el permis a altres
GRANT SELECT ON gbd_nom_cognom.clients
    TO 'app_vendes'@'localhost' WITH GRANT OPTION;

-- Aplicar els canvis
FLUSH PRIVILEGES;

-- Verificar els privilegis sobre dades
SHOW GRANTS FOR 'app_vendes'@'localhost';
-- O consultar directament les taules de sistema:
SELECT db, table_name, table_priv, column_priv
FROM mysql.tables_priv
WHERE user = 'app_vendes' AND host = 'localhost';

USE gbd_practica;
GO

-- Lectura sobre una taula concreta
GRANT SELECT ON dbo.clients TO app_vendes;

-- Lectura i escriptura sobre taules concretes
GRANT SELECT, INSERT, UPDATE ON dbo.comandes TO app_vendes;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.linies_comanda TO app_vendes;

-- Sobre tot un esquema (afecta totes les taules actuals i futures de l'esquema)
GRANT SELECT ON SCHEMA::dbo TO app_vendes;
GRANT INSERT, UPDATE ON SCHEMA::dbo TO app_vendes;

-- Permetre execucio de procediments emmagatzemats
GRANT EXECUTE ON dbo.sp_calcular_total TO app_vendes;
GRANT EXECUTE ON SCHEMA::dbo TO gestor_bd;

-- Delegar: WITH GRANT OPTION no existeix a SQL Server;
-- s'usa en canvi la clausula AS per impersonar el concedent
-- (poc usual; millor gestionar-ho via rols)

-- Verificar els privilegis sobre dades
SELECT
    dp.name       AS usuari,
    p.class_desc,
    p.permission_name,
    p.state_desc,
    OBJECT_NAME(p.major_id) AS objecte
FROM sys.database_permissions p
JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
WHERE dp.name = 'app_vendes'
ORDER BY p.class_desc, objecte;

-- Lectura sobre taules concretes (cal indicar l'esquema propietari)
GRANT SELECT ON gbd_user.clients TO app_vendes;
GRANT SELECT ON gbd_user.productes TO app_vendes;

-- Lectura i escriptura sobre taules concretes
GRANT SELECT, INSERT, UPDATE ON gbd_user.comandes TO app_vendes;
GRANT SELECT, INSERT, UPDATE, DELETE ON gbd_user.linies_comanda TO app_vendes;

-- Acces a seqüencies (necessari per a inserts si la clau primaria usa sequences)
GRANT SELECT ON gbd_user.seq_comandes TO app_vendes;

-- Acces a vistes i procediments
GRANT SELECT ON gbd_user.v_resum_vendes TO app_vendes;
GRANT EXECUTE ON gbd_user.sp_tancar_comanda TO app_vendes;

-- Delegar: permetre que app_vendes cedeixi el permis a altres
GRANT SELECT ON gbd_user.clients TO app_vendes WITH GRANT OPTION;

-- Generar els GRANTs per a tot l'esquema d'un usuari (no hi ha GRANT ON SCHEMA)
SELECT 'GRANT SELECT ON ' || owner || '.' || object_name || ' TO app_vendes;'
FROM all_objects
WHERE owner = 'GBD_USER'
  AND object_type IN ('TABLE', 'VIEW', 'SEQUENCE');
-- Executeu les sentencies generades

-- Verificar els privilegis sobre dades
SELECT privilege, owner, table_name, grantable
FROM dba_tab_privs
WHERE grantee = 'APP_VENDES'
ORDER BY owner, table_name;

---

REVOKE: revocar privilegis

REVOKE: privilegis de sistema

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Revocar la connexio a la BD
REVOKE CONNECT ON DATABASE gbd_nom_cognom FROM app_vendes;

-- Revocar l'acces a l'esquema (l'usuari no podra veure cap objecte)
REVOKE USAGE ON SCHEMA public FROM app_vendes;

-- Revocar el permis de crear objectes
REVOKE CREATE ON SCHEMA public FROM gestor_bd;

-- Verificar que el permis ha estat revocat
SELECT datacl FROM pg_database WHERE datname = 'gbd_nom_cognom';
-- La ACL no ha de contenir app_vendes per a 'c' (connect)

-- Revocar privilegis globals
REVOKE PROCESS ON *.* FROM 'monitor'@'localhost';
REVOKE SHOW DATABASES ON *.* FROM 'monitor'@'localhost';

-- Revocar el permis de crear objectes a la BD
REVOKE CREATE, DROP, ALTER ON gbd_nom_cognom.* FROM 'gestor_bd'@'localhost';

-- Aplicar els canvis
FLUSH PRIVILEGES;

-- Verificar
SHOW GRANTS FOR 'monitor'@'localhost';

-- Revocar la connexio al servidor
REVOKE CONNECT SQL FROM app_vendes_login;

-- Revocar la connexio a la BD
USE gbd_practica;
GO
REVOKE CONNECT FROM app_vendes;

-- DENY de connexio: mes restrictiu que REVOKE
-- (impedeix la connexio fins i tot si un rol la concedeix)
DENY CONNECT TO app_vendes;

-- Verificar
SELECT spe.permission_name, spe.state_desc
FROM sys.server_permissions spe
JOIN sys.server_principals sp ON spe.grantee_principal_id = sp.principal_id
WHERE sp.name = 'app_vendes_login';

-- Revocar la connexio (l'usuari queda completament bloquejat)
REVOKE CREATE SESSION FROM app_vendes;

-- Revocar el permis de crear objectes
REVOKE CREATE TABLE     FROM gestor_bd;
REVOKE CREATE VIEW      FROM gestor_bd;
REVOKE CREATE PROCEDURE FROM gestor_bd;

-- Eliminar quota d'espai
ALTER USER gestor_bd QUOTA 0 ON users;
-- (les taules existents no s'eliminen, pero no es poden inserir dades noves)

-- Verificar que el privilegi de sistema ha estat revocat
SELECT privilege FROM dba_sys_privs
WHERE grantee = 'APP_VENDES';
-- No ha d'aparèixer CREATE SESSION

REVOKE: privilegis sobre dades

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Revocar un privilegi concret sobre una taula
REVOKE INSERT ON TABLE comandes FROM app_vendes;

-- Revocar tots els privilegis sobre una taula
REVOKE ALL PRIVILEGES ON TABLE clients FROM app_vendes;

-- Revocar sobre totes les taules de l'esquema
REVOKE SELECT ON ALL TABLES IN SCHEMA public FROM app_vendes;

-- Revocar els DEFAULT PRIVILEGES (per a taules futures)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    REVOKE SELECT ON TABLES FROM app_vendes;

-- Verificar
SELECT table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'app_vendes';
-- Ha de retornar 0 files per als objectes revocats

-- Revocar un privilegi concret sobre una taula
REVOKE INSERT ON gbd_nom_cognom.comandes FROM 'app_vendes'@'localhost';

-- Revocar tots els privilegis sobre la BD
REVOKE ALL PRIVILEGES ON gbd_nom_cognom.* FROM 'app_vendes'@'localhost';

-- Aplicar els canvis
FLUSH PRIVILEGES;

-- Verificar
SHOW GRANTS FOR 'app_vendes'@'localhost';
-- Nomes hauria de quedar USAGE ON *.* (que significa "sense privilegis")

USE gbd_practica;
GO

-- Revocar un permis concret sobre una taula
REVOKE INSERT ON dbo.comandes FROM app_vendes;

-- Revocar tots els permisos sobre un esquema
REVOKE SELECT ON SCHEMA::dbo FROM app_vendes;
REVOKE INSERT, UPDATE, DELETE ON SCHEMA::dbo FROM app_vendes;

-- DENY: nega explicitament el permis amb prioritat maxima
-- Util per bloquejar una operacio concreta fins i tot si l'usuari te un rol que la permet
DENY DELETE ON dbo.clients TO app_vendes;
DENY TRUNCATE ON SCHEMA::dbo TO app_vendes;

-- Treure un DENY (torna l'usuari a l'estat sense permis explicit)
REVOKE DELETE ON dbo.clients FROM app_vendes;

-- Verificar
SELECT p.permission_name, p.state_desc, OBJECT_NAME(p.major_id) AS objecte
FROM sys.database_permissions p
JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
WHERE dp.name = 'app_vendes'
ORDER BY objecte;

-- Revocar un privilegi concret sobre una taula
REVOKE INSERT ON gbd_user.comandes FROM app_vendes;

-- Revocar tots els privilegis sobre una taula
REVOKE ALL ON gbd_user.clients FROM app_vendes;

-- Efecte en cascada amb WITH GRANT OPTION:
-- si app_vendes havia cedit el permis a un tercer, aquell permis
-- tambe queda revocat automaticament (cascada implicita a Oracle)

-- Revocar l'execucio d'un procediment
REVOKE EXECUTE ON gbd_user.sp_tancar_comanda FROM app_vendes;

-- Verificar que els privilegis sobre dades han estat revocats
SELECT privilege, owner, table_name
FROM dba_tab_privs
WHERE grantee = 'APP_VENDES'
ORDER BY owner, table_name;
-- Ha de retornar 0 files per als objectes revocats

AC0372/06/03 — Miniactivitat

RA6 · CA6.5, CA6.6, CA6.7

Dissenyeu i implementeu un esquema de seguretat per a la vostra BD de pràctiques:

1. Creeu tres usuaris: app_web (l'aplicació web), gestor_bd (el DBA), analista (només lectura).
2. Creeu tres rols: rol_lectura, rol_crud, rol_admin.
3. Assigneu els privilegis adequats a cada rol.
4. Assigneu els rols als usuaris.
5. Verifiqueu que cada usuari pot fer el que hauria i no pot fer el que no hauria (intenteu operacions prohibides i documenteu els errors).

---

Bones pràctiques de seguretat

La matriu de permisos

Abans d'executar cap GRANT, cal dissenyar qui necessita accés a què. Una matriu de permisos és una taula on les files son els rols o usuaris i les columnes son les taules (o grups de taules). Cada cel·la indica quines operacions estan permeses.

Rol / Taula	clients	comandes	linies_comanda	productes	preus_cost
rol_lectura	SELECT	SELECT	SELECT	SELECT	—
rol_editor	SELECT	SELECT, INSERT, UPDATE	SELECT, INSERT, UPDATE, DELETE	SELECT	—
rol_admin	ALL	ALL	ALL	ALL	SELECT
rol_financer	—	SELECT	—	—	ALL
backup_user	SELECT	SELECT	SELECT	SELECT	SELECT

La matriu deixa clar d'un cop d'ull:

• Quins rols tenen accés a dades sensibles (preus_cost és invisible per a la majoria).
• On s'aplica la separació de funcions: rol_financer veu els costos però no pot tocar comandes.
• Que cap rol té privilegis innecessaris.

Construïu sempre la matriu abans d'implementar res. Si no podeu omplir una cel·la amb justificació clara, aquell permís no hauria d'existir.

---

Separació de comptes per funció

Un error habitual és usar el mateix compte per a totes les operacions. La bona pràctica és tenir comptes separats per a cada funció, cadascun amb el mínim necessari:

Compte	Funció	Privilegis típics
app_web	Aplicació web en producció	SELECT, INSERT, UPDATE (mai DELETE ni DDL)
app_readonly	Informes, dashboards, BI	SELECT
app_etl	Processos de càrrega de dades	INSERT, UPDATE en taules de staging
backup_user	Scripts de backup	SELECT (còpies lògiques) o REPLICATION (còpies físiques)
monitor_user	Eines de monitoratge	SELECT sobre vistes de sistema, sense accés a dades
gestor_bd	DBA, migracions, manteniment	ALL, però mai usat per aplicacions

Mai useu el superusuari per a les aplicacions

Connectar una aplicació web amb postgres, root, sa o sys és el pitjor error de seguretat possible. Si l'aplicació té una injecció SQL o és compromesa, l'atacant té control total del servidor. Creeu sempre un compte d'aplicació amb el mínim necessari.

---

Auditoria: detectar privilegis excessius

Periòdicament cal revisar qui té accés a què. Aquestes consultes ajuden a detectar privilegis excessius o comptes oblidats:

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Tots els privilegis sobre taules d'un esquema
SELECT grantee, table_name, privilege_type, is_grantable
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
ORDER BY grantee, table_name;

-- Usuaris amb privilegis de superusuari (hauria de ser una llista molt curta)
SELECT usename, usesuper, usecreatedb, usecreaterole
FROM pg_user
WHERE usesuper = true;

-- Rols i els seus membres
SELECT r.rolname AS rol,
       u.rolname AS membre
FROM pg_auth_members m
JOIN pg_roles r ON m.roleid = r.oid
JOIN pg_roles u ON m.member = u.oid
ORDER BY rol, membre;

-- Usuaris que poden connectar-se (LOGIN) sense pertanyer a cap rol
SELECT r.rolname
FROM pg_roles r
WHERE r.rolcanlogin = true
  AND NOT EXISTS (
      SELECT 1 FROM pg_auth_members m WHERE m.member = r.oid
  )
ORDER BY r.rolname;

-- Tots els privilegis de tots els usuaris (vista global)
SELECT user, host, Select_priv, Insert_priv, Update_priv,
       Delete_priv, Super_priv, Grant_priv
FROM mysql.user
ORDER BY user, host;

-- Privilegis sobre BD concretes
SELECT user, host, db, Select_priv, Insert_priv, Update_priv, Delete_priv
FROM mysql.db
ORDER BY user, db;

-- Usuaris amb privilegis globals de superusuari (Super_priv = 'Y')
SELECT user, host FROM mysql.user WHERE Super_priv = 'Y';

-- Comptes sense contrasenya (risc de seguretat)
SELECT user, host, plugin, authentication_string
FROM mysql.user
WHERE authentication_string = '' OR authentication_string IS NULL;

-- Tots els permisos efectius d'un usuari (directes + heretats per rol)
EXECUTE AS USER = 'app_vendes';
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
REVERT;

-- Membres de tots els rols de la BD actual
SELECT r.name AS rol, m.name AS membre, m.type_desc
FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id  = r.principal_id
JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
ORDER BY rol, membre;

-- Logins amb rol sysadmin (llista que hauria de ser minima)
SELECT sp.name, sp.type_desc, spm.role_principal_id
FROM sys.server_principals sp
JOIN sys.server_role_members spm ON sp.principal_id = spm.member_principal_id
JOIN sys.server_principals sr    ON spm.role_principal_id = sr.principal_id
WHERE sr.name = 'sysadmin';

-- Permisos explicits sobre objectes de la BD
SELECT dp.name AS usuari, p.permission_name,
       p.state_desc, OBJECT_NAME(p.major_id) AS objecte
FROM sys.database_permissions p
JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
WHERE dp.type NOT IN ('R')   -- excloure rols, nomes usuaris
ORDER BY usuari, objecte;

-- Tots els privilegis d'objecte concedits a un usuari (directes)
SELECT grantee, owner, table_name, privilege, grantable
FROM dba_tab_privs
ORDER BY grantee, owner, table_name;

-- Tots els rols concedits a usuaris
SELECT grantee, granted_role, admin_option, default_role
FROM dba_role_privs
WHERE grantee NOT IN (SELECT role FROM dba_roles)  -- nomes usuaris, no rols
ORDER BY grantee, granted_role;

-- Usuaris amb DBA o privilegis perillosos
SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE privilege IN ('DBA', 'DROP ANY TABLE', 'SELECT ANY TABLE',
                    'EXECUTE ANY PROCEDURE', 'CREATE ANY TRIGGER')
ORDER BY grantee;

-- Comptes actius i el seu estat
SELECT username, account_status, last_login, expiry_date
FROM dba_users
WHERE account_status = 'OPEN'
ORDER BY username;

---

Recomanacions generals

Disseny:

• Construïu la matriu de permisos abans d'implementar qualsevol GRANT.
• Useu sempre rols, mai assigneu permisos directament a usuaris.
• Seguiu el principi de mínim privilegi a cada capa: BD, sistema operatiu, xarxa.
• Separeu els comptes per funció: aplicació, backup, monitoratge, DBA.

Contrasenyes i autenticació:

• Useu contrasenyes llargues i aleatòries per als comptes de servei (genereu-les amb un gestor de contrasenyes).
• Activeu les politiques de caducitat de contrasenyes per als comptes humans.
• Restringiu els comptes d'aplicació a connectar-se nomes des de la IP del servidor d'aplicacions (MySQL: 'usuari'@'10.0.1.5', no 'usuari'@'%').
• Mai deseu contrasenyes en clar als fitxers de configuració: useu variables d'entorn o un gestor de secrets (HashiCorp Vault, AWS Secrets Manager).

Revisió i manteniment:

• Auditeu els permisos almenys un cop per trimestre amb les consultes de la secció anterior.
• Elimineu immediatament els comptes d'usuaris que han deixat l'empresa o el projecte.
• Revoqueu els permisos temporals concedits per a tasques puntuals tan aviat com la tasca s'hagi completat.
• Documenteu cada canvi de permisos: qui el va demanar, per quin motiu i fins quan és necessari.

AC0372/06/04 — Miniactivitat

RA6 · CA6.5, CA6.6

Feu una auditoria de seguretat de la vostra BD de pràctiques:

1. Executeu les consultes d'auditoria de la secció anterior per al vostre SGBD.
2. Elaboreu la matriu de permisos real de la vostra BD (quins usuaris/rols existeixen i que poden fer).
3. Identifiqueu almenys un permís excessiu o un compte innecessari i documenteu com el corregiríeu.
4. Proposeu un esquema de comptes separat per funció per a una aplicació web hipotetica que usa la vostra BD.