Esquemes (Schemas)

Què és un esquema?

Un esquema és un espai de noms (namespace) que agrupa objectes de base de dades relacionats: taules, vistes, funcions, seqüències, índexs i procediments. Actua com una carpeta lògica dins d'una base de dades.

La separació en esquemes permet:

• Organitzar objectes per domini, aplicació o equip (vendes, rrhh, comptabilitat).
• Controlar l'accés de forma granular: un usuari pot tenir accés complet a un esquema i cap privilegi sobre la resta.
• Evitar col·lisions de noms: dues taules clients poden coexistir si estan en esquemes diferents (vendes.clients i rrhh.clients).
• Facilitar el multitenancy: cada inquilí (tenant) té el seu propi esquema dins la mateixa BD.

---

Esquemes per motor

La implementació d'esquemes varia considerablement entre motors.

PostgreSQLMySQL / MariaDBSQL ServerOracle

PostgreSQL té la implementació d'esquemes més completa. Cada base de dades conté múltiples esquemes, i cada esquema conté objectes.

• L'esquema per defecte és public. Tots els usuaris hi poden crear objectes (a partir de PostgreSQL 15 cal GRANT explícit).
• L'ordre de cerca d'objectes el determina el search_path. Si crides SELECT * FROM clients, PostgreSQL busca clients als esquemes del search_path en ordre.
• Cada usuari té automàticament un esquema privat amb el seu nom si es crea amb CREATE SCHEMA AUTHORIZATION.

-- Veure els esquemes actuals i els seus propietaris
SELECT schema_name, schema_owner
FROM information_schema.schemata
ORDER BY schema_name;

-- Veure el search_path actual
SHOW search_path;

-- Canviar el search_path de la sessió
SET search_path TO vendes, public;

-- Canviar el search_path permanent per a un usuari
ALTER ROLE app_user SET search_path = vendes, public;

Jerarquia: Servidor → Base de dades → Esquema → Objecte

A MySQL i MariaDB, esquema i base de dades són sinònims. CREATE SCHEMA és equivalent a CREATE DATABASE. No existeix un nivell de namespace addicional dins d'una base de dades.

-- A MySQL, CREATE SCHEMA = CREATE DATABASE
CREATE SCHEMA vendes CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Equivalent exacte
CREATE DATABASE vendes CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Veure tots els esquemes/bases de dades
SHOW SCHEMAS;
-- equivalent a:
SHOW DATABASES;

-- Seleccionar l'esquema actiu
USE vendes;

-- Referenciar objectes d'un altre esquema
SELECT * FROM rrhh.empleats;

Organització alternativa a MySQL

Com que MySQL no suporta sub-esquemes, l'organització per domini es fa amb prefixos de taula (vendes_clients, vendes_comandes) o amb múltiples bases de dades separades.

Jerarquia: Servidor → Base de dades (= Esquema) → Objecte

SQL Server usa un model de 4 parts: servidor.basedades.esquema.objecte. L'esquema és un nivell real entre la BD i els objectes.

• L'esquema per defecte és dbo (database owner). Tots els objectes creats sense especificar esquema van a dbo.
• Cada login té un esquema per defecte assignable amb DEFAULT_SCHEMA.
• L'esquema és independent de l'usuari (a diferència d'Oracle), cosa que facilita la transferència de propietat sense reanomenar objectes.

-- Veure esquemes de la BD actual
SELECT name, schema_id, USER_NAME(principal_id) AS owner
FROM sys.schemas
ORDER BY name;

-- Veure l'esquema per defecte d'un usuari
SELECT name, default_schema_name
FROM sys.database_principals
WHERE type IN ('S', 'U');

-- Canviar l'esquema per defecte d'un usuari
ALTER USER app_user WITH DEFAULT_SCHEMA = vendes;

-- Referenciar amb esquema explícit
SELECT * FROM vendes.clients;
SELECT * FROM dbo.empleats;

Jerarquia: Servidor → Base de dades → Esquema → Objecte

A Oracle, un esquema és exactament l'espai de noms propietat d'un usuari. Quan crees un usuari, Oracle crea automàticament un esquema amb el mateix nom. No es pot crear un esquema sense un usuari associat.

-- Crear un usuari = crear un esquema
CREATE USER vendes IDENTIFIED BY "Pwd_Segur1"
    DEFAULT TABLESPACE users
    QUOTA 500M ON users;

-- Veure tots els esquemes (usuaris)
SELECT username, account_status, default_tablespace
FROM dba_users
ORDER BY username;

-- Veure objectes d'un esquema concret
SELECT object_name, object_type, status
FROM dba_objects
WHERE owner = 'VENDES'
ORDER BY object_type, object_name;

-- Accedir a objectes d'un altre esquema
SELECT * FROM vendes.clients;

-- Crear un sinònim privat per evitar el prefix
CREATE SYNONYM clients FOR vendes.clients;
SELECT * FROM clients;  -- ara funciona sense prefix

Usuari ≠ Esquema a Oracle?

A efectes pràctics, usuari i esquema s'identifiquen. No es pot tenir un esquema sense l'usuari corresponent ni viceversa. Quan es parla de "l'esquema SCOTT", es refereix a tots els objectes propietat de l'usuari SCOTT.

Jerarquia: Instància → Esquema (= Usuari) → Objecte (dins d'un tablespace)

---

Crear i gestionar esquemes

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Crear un esquema
CREATE SCHEMA vendes;

-- Crear un esquema amb propietari específic
CREATE SCHEMA vendes AUTHORIZATION app_owner;

-- Crear un esquema i objectes en una sola instrucció
CREATE SCHEMA vendes
    CREATE TABLE clients (id SERIAL PRIMARY KEY, nom TEXT)
    CREATE VIEW clients_actius AS SELECT * FROM clients WHERE actiu = true;

-- Canviar el propietari d'un esquema
ALTER SCHEMA vendes OWNER TO nou_propietari;

-- Reanomenar un esquema
ALTER SCHEMA vendes RENAME TO comercial;

-- Eliminar un esquema buit
DROP SCHEMA vendes;

-- Eliminar un esquema i tots els seus objectes (atenció!)
DROP SCHEMA vendes CASCADE;

-- Crear un esquema/base de dades
CREATE SCHEMA IF NOT EXISTS vendes
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

-- Veure la definició
SHOW CREATE SCHEMA vendes;

-- Canviar el joc de caràcters
ALTER SCHEMA vendes CHARACTER SET utf8mb4;

-- Eliminar
DROP SCHEMA IF EXISTS vendes;

-- Crear un esquema amb propietari
CREATE SCHEMA vendes AUTHORIZATION app_owner;

-- Moure una taula existent a un altre esquema
ALTER SCHEMA vendes TRANSFER dbo.clients;

-- Canviar el propietari d'un esquema
ALTER AUTHORIZATION ON SCHEMA::vendes TO nou_propietari;

-- Eliminar un esquema (ha d'estar buit)
DROP SCHEMA vendes;

-- A Oracle, crear l'esquema = crear l'usuari
CREATE USER vendes IDENTIFIED BY "Pwd_Segur1"
    DEFAULT TABLESPACE app_data
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON app_data
    ACCOUNT UNLOCK;

-- Bloquejar un esquema (sense eliminar-lo)
ALTER USER vendes ACCOUNT LOCK;

-- Canviar el tablespace per defecte
ALTER USER vendes DEFAULT TABLESPACE app_data2;

-- Eliminar l'usuari i tots els seus objectes
DROP USER vendes CASCADE;

---

Privilegis sobre esquemes

El control d'accés als esquemes és una capa fonamental de seguretat. Els privilegis permeten determinar qui pot veure, crear o modificar objectes dins d'un esquema.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Privilegis d'esquema a PostgreSQL:
-- USAGE  → veure i accedir als objectes de l'esquema
-- CREATE → crear objectes nous dins l'esquema

-- Donar accés de lectura a un esquema
GRANT USAGE ON SCHEMA vendes TO analista;

-- Donar accés de creació d'objectes
GRANT CREATE ON SCHEMA vendes TO developer;

-- Donar accés a tots els objectes actuals d'un esquema
GRANT SELECT ON ALL TABLES IN SCHEMA vendes TO analista;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA vendes TO developer;

-- Donar accés automàtic als objectes futurs (DEFAULT PRIVILEGES)
ALTER DEFAULT PRIVILEGES IN SCHEMA vendes
    GRANT SELECT ON TABLES TO analista;

ALTER DEFAULT PRIVILEGES IN SCHEMA vendes
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

-- Revocar accés a l'esquema public (recomanat a producció)
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

Revocar public a producció

Per defecte, qualsevol usuari pot crear objectes a public. A PostgreSQL 15+ aquest comportament ja canvia, però en versions anteriors cal revocar-ho explícitament per evitar que usuaris no autoritzats contaminin l'esquema públic.

-- A MySQL els privilegis s'atorguen sobre la BD (= esquema)
-- Nivells: global (*.*), base de dades (bd.*), taula (bd.taula), columna

-- Accés de lectura a tot l'esquema
GRANT SELECT ON vendes.* TO 'analista'@'%';

-- Accés complet a l'esquema
GRANT ALL PRIVILEGES ON vendes.* TO 'developer'@'%';

-- Accés de lectura i escriptura, sense DDL
GRANT SELECT, INSERT, UPDATE, DELETE ON vendes.* TO 'app_user'@'%';

-- Veure privilegis d'un usuari sobre esquemes
SHOW GRANTS FOR 'analista'@'%';

-- Revocar accés
REVOKE SELECT ON vendes.* FROM 'analista'@'%';

-- Privilegis sobre esquema: CONTROL, ALTER, EXECUTE, SELECT, INSERT,
--                           UPDATE, DELETE, VIEW DEFINITION, REFERENCES

-- Accés de lectura a tot l'esquema
GRANT SELECT ON SCHEMA::vendes TO analista;

-- Accés d'escriptura
GRANT INSERT, UPDATE, DELETE ON SCHEMA::vendes TO app_user;

-- Control total (propietari efectiu)
GRANT CONTROL ON SCHEMA::vendes TO schema_owner;

-- Denegar explícitament (té prioritat sobre els GRANTs)
DENY DELETE ON SCHEMA::vendes TO developer;

-- Veure privilegis sobre esquemes
SELECT
    pr.name AS principal,
    pe.permission_name,
    pe.state_desc
FROM sys.database_permissions pe
JOIN sys.database_principals pr ON pe.grantee_principal_id = pr.principal_id
WHERE pe.class_desc = 'SCHEMA';

-- A Oracle els privilegis principals sobre esquema aliè:
-- CREATE SESSION    → connectar-se
-- CREATE ANY TABLE  → crear taules a qualsevol esquema
-- SELECT ANY TABLE  → llegir qualsevol taula
-- (convé evitar els "ANY" en producció)

-- Accés selectiu: donar privilegi sobre objectes concrets
GRANT SELECT ON vendes.clients TO analista;
GRANT SELECT, INSERT ON vendes.comandes TO app_user;

-- Delegar la capacitat de transferir el privilegi
GRANT SELECT ON vendes.clients TO analista WITH GRANT OPTION;

-- Accés a tot l'esquema via rol personalitzat
CREATE ROLE rol_vendes_lectura;
GRANT SELECT ON vendes.clients   TO rol_vendes_lectura;
GRANT SELECT ON vendes.comandes  TO rol_vendes_lectura;
GRANT SELECT ON vendes.productes TO rol_vendes_lectura;
GRANT rol_vendes_lectura TO analista;

-- Veure tots els privilegis sobre objectes d'un esquema
SELECT grantee, table_name, privilege, grantable
FROM dba_tab_privs
WHERE owner = 'VENDES'
ORDER BY grantee, table_name;

---

El search_path de PostgreSQL

El search_path és un concepte exclusiu de PostgreSQL (similar al PATH del sistema operatiu) que determina en quins esquemes busca el motor quan un objecte es referencia sense prefix.

-- Configuració típica
SET search_path TO vendes, public;

-- Si fas: SELECT * FROM clients
-- PostgreSQL busca en ordre: vendes.clients → public.clients → ERROR si no existeix

-- search_path recomanat a producció: no incloure public
SET search_path TO vendes;

-- Fixar per a tots els usuaris d'una base de dades
ALTER DATABASE app_db SET search_path = vendes, utils;

-- Fixar per a un rol concret
ALTER ROLE app_user SET search_path = vendes, public;

-- Veure quin és l'esquema "actual" per defecte
SELECT current_schema();

-- Veure tots els esquemes del search_path actiu
SELECT current_schemas(true);   -- true = inclou els implícits (pg_catalog)
SELECT current_schemas(false);  -- false = només els explícits

Risc de seguretat: search_path injection

Si un atacant pot crear objectes a public i el teu search_path inclou public abans del teu esquema, podria crear una funció public.lower() que intercepti les crides a lower(). Per evitar-ho:

• Posa sempre el teu esquema primer al search_path.
• A producció: SET search_path = vendes; sense public.
• O usa qualificació explícita (vendes.clients) per a tot.

---

Patrons d'ús habituals

Un esquema per aplicació

Cada aplicació té el seu propi esquema. L'usuari de l'aplicació té accés únicament al seu esquema.

-- PostgreSQL — isolació per aplicació
CREATE SCHEMA crm;
CREATE SCHEMA erp;
CREATE SCHEMA analytics;

CREATE USER crm_app WITH PASSWORD '...';
GRANT USAGE, CREATE ON SCHEMA crm TO crm_app;
GRANT SELECT ON ALL TABLES IN SCHEMA erp TO crm_app;  -- lectura creuada si cal

Un esquema per entorn

Dins la mateixa BD, esquemes separats per entorn o versió.

-- SQL Server — entorns dins la mateixa BD
CREATE SCHEMA prod;
CREATE SCHEMA staging;
CREATE SCHEMA dev;

-- Migrar objectes d'staging a prod quan hi ha release
ALTER SCHEMA prod TRANSFER staging.clients;
ALTER SCHEMA prod TRANSFER staging.comandes;

Un esquema per tenant (multitenancy)

Cada client d'un SaaS té el seu propi esquema amb la mateixa estructura de taules.

-- PostgreSQL — multitenancy per esquema
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;

-- Cada tenant té les mateixes taules però aïllades
CREATE TABLE tenant_acme.comandes  (LIKE public.comandes_template INCLUDING ALL);
CREATE TABLE tenant_globex.comandes (LIKE public.comandes_template INCLUDING ALL);

-- L'aplicació canvia el search_path per sessió
SET search_path TO tenant_acme;  -- ara "comandes" apunta a tenant_acme.comandes

---

Bones pràctiques

Bones pràctiques de gestió d'esquemes

• Evita posar objectes directament a public (PostgreSQL) o a dbo (SQL Server) en producció. Crea esquemes amb noms significatius.
• Un usuari d'aplicació no hauria de ser mai el propietari de l'esquema: el propietari (schema_owner) ha de ser un rol administrador separat. L'aplicació només necessita USAGE + DML.
• Usa DEFAULT PRIVILEGES (PostgreSQL) per garantir que els objectes futurs hereten els mateixos permisos que els actuals.
• Documenta l'estructura d'esquemes igual que documentes l'arquitectura: qui és el propietari, quins rols hi accedeixen i amb quins privilegis.
• Qualifica sempre els objectes en els scripts de producció (vendes.clients, no clients): és més segur i elimina la dependència del search_path.

Miniactivitat — AC0308

Analitza la teva instància de PostgreSQL o SQL Server:

1. Llista tots els esquemes existents i els seus propietaris.
2. Identifica quins usuaris / rols tenen accés a cada esquema i amb quins privilegis.
3. Comprova si hi ha objectes a public (PostgreSQL) o dbo (SQL Server) que haurien d'estar en un esquema específic.
4. Proposa i implementa una reorganització en esquemes per a una BD fictícia amb tres dominis: vendes, rrhh i inventari.