Usuaris i privilegis

Introducció

La gestió d'usuaris és el primer pas del control d'accés. Cada persona o aplicació que necessiti accedir a la base de dades ha de tenir el seu propi compte, configurat amb les credencials i els privilegis adequats. En aquesta unitat aprendrem a crear comptes d'usuari, a definir polítiques de contrasenyes robustes i a gestionar privilegis amb les instruccions GRANT i REVOKE.

---

Creació d'usuaris

Cada SGBD té la seva pròpia sintaxi per crear usuaris. En alguns motors (PostgreSQL, SQL Server), la distinció entre "login" (accés al servidor) i "usuari" (accés a una base de dades concreta) és explícita.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Crear un rol amb capacitat de login (equivalent a usuari)
CREATE USER app_vendes WITH
    PASSWORD 'Str0ng#Pass2024!'
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    LOGIN
    CONNECTION LIMIT 10;

-- Alternativa equivalent amb CREATE ROLE
CREATE ROLE app_vendes WITH
    LOGIN
    PASSWORD 'Str0ng#Pass2024!'
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    CONNECTION LIMIT 10;

-- Verificar que s'ha creat
SELECT usename, usesuper, usecreatedb, usecreaterole, valuntil
FROM pg_user
WHERE usename = 'app_vendes';

CREATE USER vs CREATE ROLE a PostgreSQL

A PostgreSQL, CREATE USER és equivalent a CREATE ROLE ... LOGIN. La diferència és que CREATE USER activa LOGIN per defecte, mentre que CREATE ROLE no ho fa. En entorns moderns, es prefereix usar CREATE ROLE explícitament per claredat.

-- Crear usuari accessible des de localhost
CREATE USER 'app_vendes'@'localhost'
    IDENTIFIED BY 'Str0ng#Pass2024!';

-- Crear usuari accessible des de qualsevol host (menys segur)
CREATE USER 'app_vendes'@'%'
    IDENTIFIED BY 'Str0ng#Pass2024!';

-- Crear usuari accessible des d'una subxarxa concreta
CREATE USER 'app_vendes'@'192.168.1.%'
    IDENTIFIED BY 'Str0ng#Pass2024!';

-- Verificar usuaris existents
SELECT User, Host, account_locked, password_expired
FROM mysql.user
WHERE User = 'app_vendes';

L'especificació d'host a MySQL

A MySQL i MariaDB, un usuari s'identifica per la parella 'nom'@'host'. Dos usuaris amb el mateix nom però host diferent són comptes independents amb privilegis independents. Sempre que sigui possible, restringiu l'host a l'adreça IP del servidor d'aplicacions.

-- Pas 1: Crear el login al servidor (autenticació SQL)
CREATE LOGIN app_vendes
    WITH PASSWORD = 'Str0ng#Pass2024!',
         DEFAULT_DATABASE = empresa,
         CHECK_EXPIRATION = ON,
         CHECK_POLICY = ON;

-- Pas 2: Crear l'usuari dins la base de dades
USE empresa;
CREATE USER app_vendes FOR LOGIN app_vendes;

-- Crear login amb autenticació de Windows
CREATE LOGIN [DOMINI\usuari_windows]
    FROM WINDOWS
    WITH DEFAULT_DATABASE = empresa;

-- Verificar
SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE name = 'app_vendes';

-- Crear usuari (Oracle crea l'esquema automàticament)
CREATE USER app_vendes
    IDENTIFIED BY "Str0ng#Pass2024!"
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    QUOTA 100M ON users
    ACCOUNT UNLOCK;

-- El nou usuari necessita, com a mínim, el privilegi CREATE SESSION
GRANT CREATE SESSION TO app_vendes;

-- Verificar
SELECT username, account_status, expiry_date, default_tablespace
FROM dba_users
WHERE username = 'APP_VENDES';

---

Modificació i eliminació d'usuaris

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Canviar contrasenya
ALTER USER app_vendes WITH PASSWORD 'NovaContrasenya2024!';

-- Deshabilitar login (bloquejar compte)
ALTER USER app_vendes NOLOGIN;

-- Rehabilitar
ALTER USER app_vendes LOGIN;

-- Eliminar usuari (cal revocar privilegis primer)
REASSIGN OWNED BY app_vendes TO postgres;
DROP OWNED BY app_vendes;
DROP USER app_vendes;

-- Canviar contrasenya
ALTER USER 'app_vendes'@'localhost'
    IDENTIFIED BY 'NovaContrasenya2024!';

-- Bloquejar compte
ALTER USER 'app_vendes'@'localhost' ACCOUNT LOCK;

-- Desbloquejar
ALTER USER 'app_vendes'@'localhost' ACCOUNT UNLOCK;

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

-- Deshabilitar login
ALTER LOGIN app_vendes DISABLE;

-- Habilitar
ALTER LOGIN app_vendes ENABLE;

-- Canviar contrasenya
ALTER LOGIN app_vendes WITH PASSWORD = 'NovaContrasenya2024!';

-- Eliminar usuari i login
USE empresa;
DROP USER app_vendes;
DROP LOGIN app_vendes;

-- Bloquejar compte
ALTER USER app_vendes ACCOUNT LOCK;

-- Desbloquejar
ALTER USER app_vendes ACCOUNT UNLOCK;

-- Canviar contrasenya
ALTER USER app_vendes IDENTIFIED BY "NovaContrasenya2024!";

-- Eliminar usuari i tots els seus objectes
DROP USER app_vendes CASCADE;

---

Polítiques de contrasenyes i caducitat

Una contrasenya forta és necessària però no suficient. Cal establir polítiques que enforteixin la seva gestió al llarg del temps.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Establir data de caducitat de la contrasenya
ALTER USER app_vendes WITH PASSWORD 'Str0ng#Pass2024!'
    VALID UNTIL '2025-03-01';

-- Compte que mai caduca (producció estable)
ALTER USER app_vendes WITH VALID UNTIL 'infinity';

-- Comprovar caducitat
SELECT usename, valuntil FROM pg_user WHERE usename = 'app_vendes';

Polítiques de contrasenyes a PostgreSQL

PostgreSQL no té gestió nativa de polítiques de complexitat de contrasenya. Per implementar-les cal instal·lar l'extensió passwordcheck o solucions externes com supautils. En entorns d'empresa, sovint s'integra amb LDAP o Active Directory que ja apliquen polítiques centralitzades.

-- Activar l'extensió passwordcheck (requereix accés al directori d'extensions)
-- S'afegeix a postgresql.conf:
-- shared_preload_libraries = 'passwordcheck'

-- Establir política de caducitat per a un usuari
ALTER USER 'app_vendes'@'localhost'
    PASSWORD EXPIRE INTERVAL 90 DAY;

-- Forçar canvi en el proper login
ALTER USER 'app_vendes'@'localhost' PASSWORD EXPIRE;

-- Que mai caduqui
ALTER USER 'app_vendes'@'localhost' PASSWORD EXPIRE NEVER;

-- Política global de caducitat (my.cnf)
-- default_password_lifetime = 90

-- Activar i configurar el component de validació (MySQL 8+)
INSTALL COMPONENT 'file://component_validate_password';

SET GLOBAL validate_password.policy = 'STRONG';
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

-- Verificar configuració actual
SHOW VARIABLES LIKE 'validate_password%';

-- Crear login amb caducitat i política de Windows activades
CREATE LOGIN app_vendes
    WITH PASSWORD = 'Str0ng#Pass2024!',
         CHECK_EXPIRATION = ON,   -- Respecta la política de caducitat de Windows
         CHECK_POLICY = ON;       -- Respecta la política de complexitat de Windows

-- Modificar login existent
ALTER LOGIN app_vendes WITH
    CHECK_EXPIRATION = ON,
    CHECK_POLICY = ON;

-- Forçar canvi de contrasenya en el proper login
ALTER LOGIN app_vendes WITH
    PASSWORD = 'Str0ng#Pass2024!' MUST_CHANGE,
    CHECK_EXPIRATION = ON;

-- Crear perfil amb política de contrasenyes
CREATE PROFILE perfil_app LIMIT
    FAILED_LOGIN_ATTEMPTS  5        -- Bloqueig després de 5 intents fallits
    PASSWORD_LOCK_TIME     1/24     -- Bloqueig durant 1 hora (1/24 dia)
    PASSWORD_LIFE_TIME     90       -- Caducitat als 90 dies
    PASSWORD_REUSE_TIME    365      -- No reutilitzar fins passats 365 dies
    PASSWORD_REUSE_MAX     5        -- No reutilitzar les últimes 5 contrasenyes
    PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function
    PASSWORD_GRACE_TIME    7;       -- 7 dies de gràcia en caducar

-- Assignar perfil a l'usuari
ALTER USER app_vendes PROFILE perfil_app;

-- Verificar perfil
SELECT profile, resource_name, limit
FROM dba_profiles
WHERE profile = 'PERFIL_APP'
ORDER BY resource_name;

---

Concessió de privilegis (GRANT)

Compte amb privilegis excessius

Mai concediu ALL PRIVILEGES a comptes d'aplicació. Sempre especifiqueu exactament quins privilegis necessita cada usuari o aplicació. Un compte d'aplicació web normalment necessita només SELECT, INSERT, UPDATE i, en alguns casos, DELETE.

Privilegis a nivell de taula

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Concedir SELECT sobre una taula concreta
GRANT SELECT ON TABLE empresa.comandes TO app_vendes;

-- Concedir múltiples privilegis
GRANT SELECT, INSERT, UPDATE ON TABLE empresa.comandes TO app_vendes;

-- Concedir sobre totes les taules d'un esquema
GRANT SELECT ON ALL TABLES IN SCHEMA empresa TO app_lectura;

-- Concedir per a futures taules (DEFAULT PRIVILEGES)
ALTER DEFAULT PRIVILEGES IN SCHEMA empresa
    GRANT SELECT ON TABLES TO app_lectura;

-- Concedir amb opció de traspassar el privilegi (WITH GRANT OPTION)
GRANT SELECT ON empresa.productes TO gestor_productes WITH GRANT OPTION;

-- Concedir SELECT sobre una taula
GRANT SELECT ON empresa.comandes TO 'app_vendes'@'localhost';

-- Concedir múltiples privilegis sobre una taula
GRANT SELECT, INSERT, UPDATE ON empresa.comandes
    TO 'app_vendes'@'localhost';

-- Concedir sobre tota la base de dades
GRANT SELECT ON empresa.* TO 'app_lectura'@'localhost';

-- Concedir amb opció de traspassar
GRANT SELECT ON empresa.productes
    TO 'gestor_productes'@'localhost'
    WITH GRANT OPTION;

-- Aplicar canvis (no sempre necessari, però recomanat)
FLUSH PRIVILEGES;

USE empresa;

-- Concedir SELECT sobre una taula
GRANT SELECT ON dbo.comandes TO app_vendes;

-- Concedir múltiples privilegis
GRANT SELECT, INSERT, UPDATE ON dbo.comandes TO app_vendes;

-- Concedir sobre tot l'esquema
GRANT SELECT ON SCHEMA::dbo TO app_lectura;

-- Concedir amb opció de traspassar
GRANT SELECT ON dbo.productes TO gestor_productes WITH GRANT OPTION;

-- Negar explícitament un privilegi (té prioritat sobre GRANT)
DENY DELETE ON dbo.comandes TO app_vendes;

-- Concedir SELECT sobre una taula
GRANT SELECT ON empresa.comandes TO app_vendes;

-- Concedir múltiples privilegis
GRANT SELECT, INSERT, UPDATE ON empresa.comandes TO app_vendes;

-- Concedir amb opció de traspassar
GRANT SELECT ON empresa.productes TO gestor_productes WITH GRANT OPTION;

-- Concedir a tots els usuaris (no recomanat)
GRANT SELECT ON empresa.cataleg TO PUBLIC;

Privilegis a nivell de base de dades

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Concedir connexió a la base de dades
GRANT CONNECT ON DATABASE empresa TO app_vendes;

-- Concedir ús d'un esquema
GRANT USAGE ON SCHEMA empresa TO app_vendes;

-- Concedir capacitat de crear objectes en un esquema
GRANT CREATE ON SCHEMA empresa TO dev_backend;

-- Concedir tots els privilegis sobre una base de dades
GRANT ALL PRIVILEGES ON DATABASE empresa TO admin_empresa;

-- Concedir tots els privilegis sobre una base de dades
GRANT ALL PRIVILEGES ON empresa.* TO 'admin_empresa'@'localhost';

-- Concedir CREATE i DROP (DDL)
GRANT CREATE, DROP, ALTER ON empresa.* TO 'dev_backend'@'localhost';

-- Concedir EVENT per a tasques programades
GRANT EVENT ON empresa.* TO 'admin_empresa'@'localhost';

USE empresa;

-- Concedir CREATE TABLE
GRANT CREATE TABLE TO dev_backend;

-- Concedir CREATE VIEW
GRANT CREATE VIEW TO dev_backend;

-- Concedir BACKUP (a nivell de servidor)
USE master;
GRANT BACKUP DATABASE TO backup_operator;

-- Privilegis de sistema habituals
GRANT CREATE TABLE TO dev_backend;
GRANT CREATE VIEW TO dev_backend;
GRANT CREATE SEQUENCE TO dev_backend;
GRANT CREATE PROCEDURE TO dev_backend;
GRANT CREATE TRIGGER TO dev_backend;

-- Concedir quota a un tablespace
ALTER USER dev_backend QUOTA 500M ON users;

Privilegis a nivell de sistema

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Convertir en superusuari (equivalent a root)
ALTER USER admin_dba WITH SUPERUSER;

-- Concedir capacitat de crear bases de dades
ALTER USER dev_lead WITH CREATEDB;

-- Concedir capacitat de crear rols
ALTER USER admin_dba WITH CREATEROLE;

-- Concedir capacitat de replicació
ALTER USER replicator WITH REPLICATION;

-- Privilegis globals (a nivell de servidor)
GRANT SUPER ON *.* TO 'admin_dba'@'localhost';
GRANT PROCESS ON *.* TO 'monitor'@'localhost';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'replica-server';
GRANT RELOAD ON *.* TO 'admin_dba'@'localhost';

-- MySQL 8+: privilegis dinàmics (substitueixen alguns globals)
GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'admin_dba'@'localhost';
GRANT ROLE_ADMIN ON *.* TO 'admin_dba'@'localhost';

-- Afegir a un rol de servidor fix
ALTER SERVER ROLE sysadmin ADD MEMBER admin_dba;
ALTER SERVER ROLE dbcreator ADD MEMBER dev_lead;
ALTER SERVER ROLE bulkadmin ADD MEMBER etl_user;
ALTER SERVER ROLE securityadmin ADD MEMBER sec_admin;

-- Privilegis de sistema bàsics
GRANT CREATE SESSION TO app_vendes;       -- Connectar-se
GRANT CREATE ANY TABLE TO admin_dba;      -- Crear taules en qualsevol esquema
GRANT DROP ANY TABLE TO admin_dba;        -- Eliminar taules de qualsevol esquema
GRANT SELECT ANY TABLE TO auditor;        -- Llegir qualsevol taula

-- Rol DBA (màxims privilegis)
GRANT DBA TO admin_dba;

---

Revocació de privilegis (REVOKE)

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Revocar un privilegi concret
REVOKE INSERT ON empresa.comandes FROM app_vendes;

-- Revocar múltiples privilegis
REVOKE SELECT, INSERT, UPDATE ON empresa.comandes FROM app_vendes;

-- Revocar tots els privilegis sobre una taula
REVOKE ALL PRIVILEGES ON empresa.comandes FROM app_vendes;

-- Revocar en cascada (revoca també els privilegis concedits per app_vendes)
REVOKE SELECT ON empresa.productes FROM gestor_productes CASCADE;

-- Revocar un privilegi concret
REVOKE INSERT ON empresa.comandes FROM 'app_vendes'@'localhost';

-- Revocar tots els privilegis
REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM 'app_vendes'@'localhost';

FLUSH PRIVILEGES;

USE empresa;

-- Revocar un privilegi
REVOKE INSERT ON dbo.comandes FROM app_vendes;

-- Revocar en cascada
REVOKE SELECT ON dbo.productes FROM gestor_productes CASCADE;

-- Revocar un privilegi de taula
REVOKE INSERT ON empresa.comandes FROM app_vendes;

-- Revocar en cascada
REVOKE SELECT ON empresa.productes FROM gestor_productes CASCADE CONSTRAINTS;

---

Consulta dels privilegis concedits

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Llistar rols i atributs d'usuaris (\du al psql)
SELECT rolname, rolsuper, rolcreatedb, rolcreaterole, rolcanlogin
FROM pg_roles
ORDER BY rolname;

-- Privilegis sobre taules per a un usuari concret
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'app_vendes'
ORDER BY table_schema, table_name, privilege_type;

-- Privilegis sobre un objecte concret
SELECT relname, relacl
FROM pg_class
WHERE relname = 'comandes';

-- Des de psql: mostrar privilegis de totes les taules
-- \dp empresa.*

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

-- Veure privilegis de l'usuari actual
SHOW GRANTS;

-- Consultar la taula de privilegis directament
SELECT * FROM information_schema.USER_PRIVILEGES
WHERE GRANTEE = "'app_vendes'@'localhost'";

SELECT * FROM information_schema.TABLE_PRIVILEGES
WHERE GRANTEE = "'app_vendes'@'localhost'";

USE empresa;

-- Permisions d'un usuari concret a la base de dades actual
SELECT
    dp.name AS principal,
    o.name AS objecte,
    p.permission_name,
    p.state_desc
FROM sys.database_permissions p
JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
LEFT JOIN sys.objects o ON p.major_id = o.object_id
WHERE dp.name = 'app_vendes';

-- Membres dels rols de servidor
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;

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

-- Privilegis de taula d'un usuari
SELECT owner, table_name, privilege, grantable
FROM dba_tab_privs
WHERE grantee = 'APP_VENDES'
ORDER BY owner, table_name;

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

-- Tots els privilegis efectius (inclosos via rols)
SELECT privilege
FROM session_privs
ORDER BY privilege;

---

Privilegis a nivell de columna

Quan cal restringir l'accés a columnes concretes d'una taula (per exemple, ocultar salaris o dades mèdiques), podeu usar privilegis de columna o, preferiblement, vistes.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- PostgreSQL no suporta GRANT directament a columnes per a SELECT/INSERT/UPDATE
-- La solució recomanada és una vista:
CREATE VIEW empleats_public AS
    SELECT id, nom, cognoms, departament, correu
    FROM empleats;
    -- La columna 'salari' queda exclosa

GRANT SELECT ON empleats_public TO app_rrhh_basic;

-- Però sí que suporta UPDATE a columna específica:
GRANT UPDATE (correu, telefon) ON empleats TO app_self_service;

-- Concedir SELECT només sobre columnes concretes
GRANT SELECT (id, nom, cognoms, departament, correu)
    ON empresa.empleats
    TO 'app_rrhh_basic'@'localhost';

-- Concedir UPDATE sobre columnes específiques
GRANT UPDATE (correu, telefon)
    ON empresa.empleats
    TO 'app_self_service'@'localhost';

-- Verificar privilegis de columna
SELECT * FROM information_schema.COLUMN_PRIVILEGES
WHERE GRANTEE = "'app_rrhh_basic'@'localhost'";

USE empresa;

-- Concedir SELECT sobre columnes concretes
GRANT SELECT (id, nom, cognoms, departament, correu)
    ON dbo.empleats TO app_rrhh_basic;

-- Negar SELECT sobre una columna sensible
DENY SELECT (salari) ON dbo.empleats TO app_rrhh_basic;

-- Verificar
SELECT column_name, permission_name, state_desc
FROM sys.column_permissions cp
JOIN sys.columns c ON cp.major_id = c.object_id AND cp.minor_id = c.column_id
JOIN sys.objects o ON cp.major_id = o.object_id
WHERE o.name = 'empleats';

-- Concedir SELECT sobre columnes específiques
GRANT SELECT (id, nom, cognoms, departament, correu)
    ON empresa.empleats TO app_rrhh_basic;

-- Concedir UPDATE sobre columnes específiques
GRANT UPDATE (correu, telefon)
    ON empresa.empleats TO app_self_service;

-- Verificar privilegis de columna
SELECT owner, table_name, column_name, privilege, grantable
FROM dba_col_privs
WHERE grantee = 'APP_RRHH_BASIC'
ORDER BY column_name;

---

Comptes especials d'administració

Mai feu servir els comptes d'administrador per a aplicacions

Els comptes postgres (PostgreSQL), root (MySQL/MariaDB), sa (SQL Server) i SYS/SYSTEM (Oracle) són comptes d'administració total del sistema. No s'han de fer servir mai com a credencials de connexió d'aplicacions o scripts de producció.

• Si les credencials root d'una aplicació web es filtren o es comprometen, l'atacant tindrà control total sobre totes les bases de dades del servidor.
• Creeu sempre comptes específics amb el mínim privilegi necessari per a cada aplicació.
• Canvieu la contrasenya per defecte dels comptes d'administrador immediatament després de la instal·lació.
• A MySQL, elimineu o desactiveu l'usuari root@% (accés des de qualsevol host).
• A PostgreSQL, l'usuari postgres hauria de tenir accés restringit a localhost o socket Unix.

---

Miniactivitat — AC0302 · AC0303 · AC0304 · Gestió d'usuaris i privilegis

Miniactivitat — AC0302 · AC0303 · AC0304

Treballeu amb la instància Docker del SGBD que tingueu activa (PostgreSQL o MySQL). Realitzeu les tasques següents i documenteu cada comanda executada i el seu resultat.

Tasca 1 — Creació d'usuaris:

Creeu tres usuaris amb els perfils següents:

Usuari	Descripció	Privilegis necessaris
app_web	Aplicació web de l'empresa	SELECT, INSERT, UPDATE sobre comandes i clients
analista	Analista de dades	SELECT sobre totes les taules
dev_local	Desenvolupador local	Tots els privilegis sobre la BD de proves

Tasca 2 — Polítiques de seguretat:

• Establiu una caducitat de contrasenya de 60 dies per a app_web.
• Bloquegeu el compte dev_local i verifiqueu que no es pot connectar.
• Desbloqueeu el compte i verifiqueu l'accés.

Tasca 3 — Consulta de privilegis:

• Llisteu tots els privilegis de cada usuari creat.
• Revoqueu el privilegi INSERT d'app_web sobre la taula clients i verifiqueu el canvi.

Lliurament: captura de pantalla de cada operació amb el resultat obtingut.