Tema 18: Usuarios, roles y permisos — el gobierno del altépetl
#PF-018 | XP: 260 | Insignia: Calpixqui de Permisos | Tier: Senior | Tiempo: 3.5 h
Objetivo
Dominarás la gestión de usuarios, roles y permisos en MySQL y PostgreSQL. Aplicarás el principio de menor privilegio, usarás roles para agrupar permisos, configurarás autenticación segura con hashes fuertes, y aprenderás a auditar quién hizo qué.
Mapa del tema
Visión mexica — Por Citlalli
En el sistema tributario mexica, el calpixqui (recaudador) no entregaba tributos al templo mayor sin antes verificar tres veces el registro: una contra el padrón del calpulli, otra contra la lista del tlatoani, y la tercera contra los libros del calmécac. Cada persona con acceso a los tributos tenía un permiso específico: los macehuales entregaban, los pipiltin verificaban, los calpixqui transportaban, los sacerdotes archivaban. Nadie hacía todo.
Esa es la esencia del principio de menor privilegio en bases de datos modernas: cada usuario tiene solo los permisos que necesita para su función, ni uno más. La app web no necesita DROP TABLE. El usuario de reportes no necesita DELETE. El de respaldo no necesita INSERT. Y así.
Un DBA que da `GRANT ALL` a todos los usuarios está renunciando a su trabajo: cualquier fuga, error o ataque se magnifica porque cada cuenta tiene poder ilimitado.
Bloque 1 — Principio de menor privilegio (PoLP)
El principio de menor privilegio dice: cada usuario, programa o proceso tiene solo los permisos estrictamente necesarios para realizar su tarea, y nada más.
Aplicado a bases de datos significa:
- La aplicación web: `SELECT, INSERT, UPDATE, DELETE` en tablas específicas. Nunca `DROP`, `CREATE`, `ALTER`, ni acceso a `mysql.` o `pg_catalog`.
- El usuario de reportes: `SELECT` en todo. Nunca escritura.
- El usuario de respaldo: `SELECT, LOCK TABLES, RELOAD, SHOW VIEW, EVENT, TRIGGER`. Nunca escritura directa.
- El DBA: todo. Pero con cuenta personal, no compartida, y con 2FA.
Bloque 2 — Usuarios en MySQL: `CREATE USER`, `GRANT`, `REVOKE`
Crear usuario dedicado para la app
-- Crear usuario con autenticación fuerte
CREATE USER 'app_tienda'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'ClaveFuerte_2026!';-- Otorgar permisos mínimos sobre la base tienda_tlalli
GRANT SELECT, INSERT, UPDATE, DELETE ON tienda_tlalli. TO 'app_tienda'@'localhost';
-- Aplicar cambios
FLUSH PRIVILEGES;
Verificar permisos otorgados
SHOW GRANTS FOR 'app_tienda'@'localhost';
-- Esperado:
-- GRANT USAGE ON . TO `app_tienda`@`localhost`
-- GRANT SELECT, INSERT, UPDATE, DELETE ON `tienda_tlalli`. TO `app_tienda`@`localhost`Permisos comunes en MySQL
| Privilegio | Uso típico | Riesgo si se abusa | |---|---|---| | `SELECT` | Lectura de datos | Bajo. Permite exfiltrar datos si la tabla es sensible. | | `INSERT` | Insertar filas | Medio. Permite inyectar datos falsos. | | `UPDATE` | Modificar filas | Medio. Permite modificar precios, contraseñas, etc. | | `DELETE` | Borrar filas | Alto. Un `DELETE` sin WHERE puede tirar la tabla. | | `DROP` | Borrar objetos | Crítico. Borra tablas, bases, índices. | | `CREATE` | Crear objetos | Alto. Puede crear bases o tablas no autorizadas. | | `ALTER` | Modificar estructura | Alto. Puede cambiar tipos, romper FK. | | `GRANT OPTION` | Otorgar permisos | Crítico. Permite escalar privilegios. | | `FILE` | Leer/escribir archivos del SO | Crítico. Permite leer `/etc/passwd` o escribir archivos arbitrarios. | | `SUPER` | Operaciones de admin | Crítico. Bypasa muchas restricciones. |
Ejemplo de usuario de reportes (solo lectura)
CREATE USER 'reportes'@'%' IDENTIFIED WITH caching_sha2_password BY 'Reportes2026!';
GRANT SELECT ON tienda_tlalli. TO 'reportes'@'%';Ejemplo de usuario de backup (Tema 16)
CREATE USER 'backup_user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Backup2026!';
GRANT SELECT, LOCK TABLES, RELOAD, SHOW VIEW, EVENT, TRIGGER ON tienda_tlalli. TO 'backup_user'@'localhost';Revocar permisos
REVOKE DELETE ON tienda_tlalli. FROM 'app_tienda'@'localhost';
FLUSH PRIVILEGES;Cambiar contraseña
ALTER USER 'app_tienda'@'localhost' IDENTIFIED BY 'NuevaClave_2026!';Eliminar usuario
DROP USER 'app_tienda'@'localhost';Bloque 3 — Usuarios y roles en PostgreSQL: `CREATE ROLE`, `GRANT`
PostgreSQL unifica los conceptos de usuario y rol: un rol con `LOGIN` puede conectarse, sin `LOGIN` es un grupo.
Crear roles (grupos de permisos)
-- Rol para la aplicación
CREATE ROLE app_tienda WITH LOGIN PASSWORD 'ClaveFuerte_2026!';-- Rol de lectura para reportes
CREATE ROLE reportes WITH LOGIN PASSWORD 'Reportes2026!';
-- Rol de backup
CREATE ROLE backup_user WITH LOGIN PASSWORD 'Backup2026!';
-- Rol de DBA
CREATE ROLE dba WITH LOGIN PASSWORD 'Dba2026!' SUPERUSER;
Otorgar permisos a un rol
-- Conexión a la base
GRANT CONNECT ON DATABASE tienda_tlalli TO app_tienda, reportes, backup_user;-- Uso del esquema
GRANT USAGE ON SCHEMA public TO app_tienda, reportes, backup_user;
-- Permisos por tabla
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_tienda;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_tienda;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reportes;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;
Hacer que los permisos apliquen a tablas futuras
-- Para que las nuevas tablas creadas por el dba hereden estos permisos
ALTER DEFAULT PRIVILEGES FOR ROLE dba IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_tienda;
ALTER DEFAULT PRIVILEGES FOR ROLE dba IN SCHEMA public
GRANT SELECT ON TABLES TO reportes;Roles agrupadores (jerarquía)
PostgreSQL permite que un rol herede de otros:
-- Crear un rol de "lector"
CREATE ROLE rol_lector;-- Otorgar permisos de lectura al rol
GRANT SELECT ON ALL TABLES IN SCHEMA public TO rol_lector;
-- Hacer que "reportes" herede de "rol_lector"
GRANT rol_lector TO reportes;
Esto evita repetir permisos. Si mañana agregas una tabla, solo modificas `rol_lector` y todos los que lo heredan la reciben.
Cambiar contraseña
ALTER ROLE app_tienda WITH PASSWORD 'NuevaClave_2026!';Revocar permisos
REVOKE DELETE ON ALL TABLES IN SCHEMA public FROM app_tienda;Eliminar rol
DROP ROLE reportes;Bloque 4 — Roles vs usuarios: cuándo agrupar
| Caso | MySQL | PostgreSQL | |---|---|---| | Un usuario que necesita permisos únicos | `CREATE USER` | `CREATE ROLE ... LOGIN` | | 3 usuarios que necesitan los mismos permisos | No hay roles nativos pre-8.0; simular con grants repetidos o usar proxy users | `CREATE ROLE` sin login, `GRANT rol TO usuario` | | 30 usuarios con permisos en constante cambio | Crear roles MySQL 8 (`CREATE ROLE 'lectura_app'`) y asignar a usuarios | Roles jerárquicos | | Permisos por tabla/columna | `GRANT SELECT (columna) ON tabla TO usuario` | `GRANT SELECT (columna) ON tabla TO rol` | | Permisos por fila (RLS) | No nativo, usar vistas o app | `ALTER TABLE ... ENABLE ROW LEVEL SECURITY` |
Regla práctica: si tienes más de 5 usuarios con permisos similares, usa roles. Si tienes menos, los grants directos están bien.
Bloque 5 — Autenticación con hashes seguros
MySQL: `caching_sha2_password` (default desde 8.0)
`caching_sha2_password` es más seguro que el viejo `mysql_native_password`:
- Hash SHA-256 + sal aleatorio + iteraciones.
- Cachea en el servidor para evitar handshake pesado en cada conexión.
- Compatible con todos los drivers modernos.
[mysqld]
default_authentication_plugin = caching_sha2_passwordPostgreSQL: `scram-sha-256` (recomendado)
PostgreSQL 10+ soporta SCRAM-SHA-256, que es el estándar moderno:
-- Configurar como método por defecto en pg_hba.conf
-- Reemplazar "md5" o "password" por "scram-sha-256"
-- Ejemplo:
-- host all all 0.0.0.0/0 scram-sha-256# postgresql.conf
password_encryption = scram-sha-256Para verificar que una contraseña usa SCRAM:
SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'app_tienda';
-- Esperado: SCRAM-SHA-256$4096:...pg_hba.conf: control de acceso por host
`pg_hba.conf` (Host-Based Authentication) controla quién puede conectarse desde dónde:
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
host tienda_tlalli app_tienda 127.0.0.1/32 scram-sha-256
host tienda_tlalli reportes 10.0.0.0/8 scram-sha-256
host all all 0.0.0.0/0 rejectOrden importa: PostgreSQL aplica la primera regla que coincida. Pon lo más específico arriba.
Después de modificar `pg_hba.conf`, recarga sin reiniciar:
pg_ctl reload -D /var/lib/postgresql/16/mainAI Mission
Pídele a tu IA que audite tu política de usuarios. Prompt sugerido:
Tengo los siguientes usuarios en tienda_tlalli (MySQL 8):
root (uso solo para admin)
app_tienda (usa la app web)
backup_user (corre mysqldump diario)
jose (mi usuario personal de DBA)
Dame:
¿Cuántos huecos de seguridad tiene esta lista?
¿Qué permisos exactos debería tener cada uno?
¿Cómo aplicaría el principio de menor privilegio?
¿Qué añadirías (auditoría, expiración de contraseñas, etc.)?
Errores típicos
LAB práctico — Roles y permisos para tienda_tlalli
Objetivo: dejar tienda_tlalli con cuatro cuentas bien separadas: app, reportes, backup, DBA. Cada una con sus permisos exactos.
Paso 1 — Crear los roles
-- App
CREATE USER 'app_tienda'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'AppTienda_2026!';
GRANT SELECT, INSERT, UPDATE, DELETE ON tienda_tlalli. TO 'app_tienda'@'localhost';-- Reportes
CREATE USER 'reportes'@'%' IDENTIFIED WITH caching_sha2_password BY 'Reportes_2026!';
GRANT SELECT ON tienda_tlalli.
TO 'reportes'@'%';-- Backup
CREATE USER 'backup_user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Backup_2026!';
GRANT SELECT, LOCK TABLES, RELOAD, SHOW VIEW, EVENT, TRIGGER ON tienda_tlalli. TO 'backup_user'@'localhost';
-- DBA personal (tú)
CREATE USER 'jose'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'DbaJose_2026!';
GRANT ALL PRIVILEGES ON tienda_tlalli. TO 'jose'@'localhost' WITH GRANT OPTION;
Paso 2 — Verificar
SHOW GRANTS FOR 'app_tienda'@'localhost';
SHOW GRANTS FOR 'reportes'@'%';
SHOW GRANTS FOR 'backup_user'@'localhost';
SHOW GRANTS FOR 'jose'@'localhost';Paso 3 — Probar que el principio de menor privilegio se cumple
# Conectarse como app_tienda e intentar hacer DROP
mysql -u app_tienda -p tienda_tlalli
mysql> DROP TABLE clientes;
-- Esperado: ERROR 1142 (42000): DROP command denied to user 'app_tienda'@'localhost'mysql> SELECT COUNT(*) FROM clientes;
-- Esperado: 50 (sí puede leer)
Paso 4 — Configurar pg_hba.conf en PostgreSQL
# Editar pg_hba.conf
sudo nano /etc/postgresql/16/main/pg_hba.confAgregar al inicio:
# Conexiones locales para la app
local tienda_tlalli app_tienda scram-sha-256
host tienda_tlalli app_tienda 127.0.0.1/32 scram-sha-256Reportes desde la red interna
host tienda_tlalli reportes 10.0.0.0/8 scram-sha-256Backup desde localhost
host tienda_tlalli backup_user 127.0.0.1/32 scram-sha-256DBA desde tu IP
host tienda_tlalli jose 192.168.1.50/32 scram-sha-256Bloquear todo lo demás
host all all 0.0.0.0/0 rejectsudo pg_ctlcluster 16 main reloadEntregables del LAB
- [ ] Cuatro usuarios creados con permisos diferenciados.
- [ ] Verificación de que `app_tienda` no puede DROP.
- [ ] `pg_hba.conf` configurado con reglas específicas por rol.
- [ ] Captura de un intento de DROP denegado.
- [ ] Cambio de contraseña personal (DBA) en una variable de entorno, no en texto plano.
Quest
Preguntas de opción múltiple (10)
1. ¿Qué es el principio de menor privilegio?
A. Otorgar todos los permisos a todos los usuarios. B. Otorgar a cada usuario solo los permisos estrictamente necesarios. C. Crear usuarios sin contraseña. D. Usar solo el usuario root.
2. ¿Qué privilegio MySQL permite leer y escribir archivos del sistema operativo?
A. `SELECT` B. `FILE` C. `PROCESS` D. `SUPER`
3. ¿Qué hace `REVOKE`?
A. Crea un usuario. B. Quita permisos otorgados previamente. C. Otorga permisos. D. Cambia la contraseña.
4. ¿Qué método de autenticación es el más seguro en PostgreSQL 10+?
A. `password` B. `md5` C. `scram-sha-256` D. `trust`
5. ¿Qué archivo controla desde qué hosts se puede conectar un usuario PostgreSQL?
A. `postgresql.conf` B. `pg_hba.conf` C. `pg_ident.conf` D. `recovery.conf`
6. ¿Qué hace `ALTER DEFAULT PRIVILEGES` en PostgreSQL?
A. Cambia los permisos de todas las tablas existentes. B. Define permisos que se aplicarán a futuras tablas. C. Borra una tabla. D. Crea un nuevo esquema.
7. ¿Cuál es la diferencia entre `CREATE USER` y `CREATE ROLE ... WITH LOGIN` en PostgreSQL?
A. No hay diferencia: ambos crean un usuario conectable. B. `CREATE USER` es solo para administradores. C. `CREATE ROLE` no permite conexión. D. `CREATE USER` requiere superusuario.
8. ¿Por qué es peligroso usar `root` desde la aplicación?
A. No es peligroso. B. Si la app es comprometida, el atacante tiene control total de la base. C. Es muy lento. D. No permite conexiones remotas.
9. ¿Qué hace `GRANT OPTION`?
A. Otorga el permiso de dar permisos a otros. B. Optimiza las queries. C. Cambia el motor de almacenamiento. D. Activa la compresión.
10. ¿Qué vista MySQL permite ver los permisos otorgados a un usuario?
A. `information_schema.USER_PRIVILEGES` B. `mysql.user` C. `performance_schema.users` D. `sys.user_grants`
Preguntas abiertas (3)
A. Una aplicación usa el usuario `root` para conectarse a MySQL porque "así es más fácil". Lista 5 problemas concretos de seguridad de esta decisión y propón una migración en 3 pasos.
B. Tienes 12 usuarios que necesitan solo `SELECT` sobre `tienda_tlalli` pero algunos también `INSERT` en `pedidos`. Diseña la estructura de roles en PostgreSQL para no repetir permisos.
C. El equipo de desarrollo te pide acceso a `tienda_tlalli` desde sus laptops. ¿Qué información necesitas antes de concederlo? ¿Qué condiciones pondrías?
Respuestas modelo
1. B — Cada usuario solo los permisos necesarios. La A es lo contrario del principio. La C y D son inseguras.
2. B — `FILE` permite `LOAD_FILE()` y `SELECT ... INTO OUTFILE`. Crítico si se abusa. `SUPER` permite otras cosas peligrosas pero no acceso a archivos.
3. B — `REVOKE` quita permisos. `GRANT` los otorga. `CREATE USER` crea cuenta. Cambiar contraseña es `ALTER USER ... IDENTIFIED BY`.
4. C — `scram-sha-256` es el estándar moderno de PostgreSQL. `password` envía en claro. `md5` es mejor que `password` pero vulnerable a replay. `trust` no pide contraseña (solo para desarrollo local).
5. B — `pg_hba.conf` (Host-Based Authentication). `postgresql.conf` es configuración general. `pg_ident.conf` mapea usuarios del SO. `recovery.conf` es para PITR (ya no se usa desde PG 12).
6. B — Aplica permisos a tablas futuras, no a las existentes. Para las existentes usas `GRANT` directo. No borra ni crea.
7. A — Desde PostgreSQL 8.1, no hay diferencia práctica: `CREATE USER` es alias de `CREATE ROLE ... LOGIN`. Ambos crean un rol conectable.
8. B — Si la app es comprometida (SQL injection, XSS, dependencia vulnerable), el atacante tiene control total de la base. La A es falsa. La C es irrelevante. La D es falsa.
9. A — `GRANT OPTION` permite al usuario receptor dar esos permisos a otros. Útil para cadenas de delegación, peligroso si se abusa.
10. B — `mysql.user` tiene los privilegios globales y por tabla. `USER_PRIVILEGES` también pero menos detallado. `performance_schema` no tiene esto. `sys` no es para esto.
Tras las huellas — Por Citlalli
Los mexicas no solo recolectaban tributos: los rendían cuentas. Cada calpixqui debía reportar al calpixqui mayor qué recibió, de qué calpulli, en qué fecha, y dónde se depositó. Si los números no coincidían con el padrón, el calpixqui era sancionado. La transparencia no era cortesía, era supervivencia del sistema.
En bases de datos modernas, el equivalente es la auditoría: el log de quién hizo qué, cuándo y desde dónde. La tabla `general_log` de MySQL (en modo debug) o `pgAudit` en PostgreSQL te dan ese registro. Actívalos en producción, no solo en desarrollo.
Pregunta para el cuaderno: si mañana un auditor te pregunta "¿quién borró la tabla `pedidos` el 15 de agosto a las 14:32?", ¿podrías responder con un log, o tendrías que admitir que no lo sabes?