ROLES EN MYSQL Y MARIADB: CÓMO CREARLOS Y GESTIONARLOS

Sintaxis básica (funciona en ambos)

-- 1. Crear roles
CREATE ROLE 'lector_app', 'escritor_app', 'admin_app', 'backup_operator';

-- 2. Asignar privilegios a los roles
GRANT SELECT ON mi_aplicacion.* TO 'lector_app';
GRANT SELECT, INSERT, UPDATE, DELETE ON mi_aplicacion.* TO 'escritor_app';
GRANT ALL PRIVILEGES ON mi_aplicacion.* TO 'admin_app';

-- Privilegios especiales para backup (MariaDB y MySQL)
GRANT BACKUP_ADMIN, BINLOG_ADMIN, RELOAD, PROCESS ON *.* TO 'backup_operator';

-- 3. Crear usuarios y asignarles roles
CREATE USER 'juan'@'%' IDENTIFIED BY 'PasswordFuerte2026!';
CREATE USER 'backend'@'10.0.0.%' IDENTIFIED BY 'OtroPassSeguro!';

GRANT 'lector_app', 'escritor_app' TO 'juan'@'%';
GRANT 'admin_app' TO 'backend'@'10.0.0.%';

-- 4. Establecer roles por defecto (muy recomendado)
ALTER USER 'juan'@'%' DEFAULT ROLE 'lector_app','escritor_app';
ALTER USER 'backend'@'10.0.0.%' DEFAULT ROLE 'admin_app';

Gestión de roles en una sesión

-- Ver roles disponibles para el usuario actual
SELECT CURRENT_ROLE();

-- Activar roles específicos
SET ROLE 'admin_app';
SET ROLE ALL; -- Activa todos los roles del usuario
SET ROLE NONE;-- Desactiva todos

-- Activar automáticamente todos los roles al conectar
SET GLOBAL activate_all_roles_on_login = ON;

Consultas útiles

-- Ver quién tiene qué roles
SELECT * FROM information_schema.applicable_roles;
SELECT * FROM information_schema.enabled_roles;

-- Ver todos los roles del sistema
SELECT user, host, is_role 
FROM mysql.user 
WHERE is_role = 1;  -- MariaDB
-- En MySQL: SELECT user, host FROM mysql.user WHERE account_locked = 'Y' AND ...

-- Ver privilegios efectivos
SHOW GRANTS FOR 'juan'@'%';
SHOW GRANTS FOR CURRENT_USER();

Mejores prácticas recomendadas

• Roles por función (no por usuario)
  • "frontend_readonly"
    • Propósito: Usuario que utiliza el frontend (web, móvil, API pública, etc.).
    • Permisos típicos: Solo "SELECT".
    • Posiblemente en vistas, "VIEW" específicas o tablas permitidas.
    • Sin permisos de "INSERT", "UPDATE", "DELETE" ni de modificar estructura.
    • Uso recomendado: Conexión desde la aplicación web o app móvil.
  • "backend_full"
    • Propósito: Usuario del backend (API, servicios internos, workers, etc.).
    • Permisos típicos: CRUD completo, "SELECT", "INSERT", "UPDATE", "DELETE").
    • Posiblemente también "CREATE"/"ALTER" en tablas temporales o ciertas tablas de negocio.
    • Puede incluir permisos para procedimientos almacenados: "EXECUTE").
    • Uso recomendado: Aplicación backend (Node.js, Python, Java, etc.).
  • "reportes_analiticos"
    • Propósito: Usuarios o herramientas que generan reportes y análisis (Power BI, Tableau, Metabase, usuarios de negocio, etc.).
    • Permisos típicos: Solo "SELECT".
    • Acceso a vistas optimizadas para reporting o tablas de un esquema analítico.
    • A veces permite acceso a más tablas que "frontend_readonly" (datos históricos, agregados, etc.).
    • Buena práctica: Crear vistas específicas para este rol para evitar exponer tablas crudas.
  • "backup_operator"
    • Propósito: Usuario dedicado exclusivamente a realizar backups (mysqldump, mariabackup, etc.).
    • Permisos típicos (mínimos recomendados): "SELECT", "LOCK TABLES", "RELOAD", "REPLICATION CLIENT", "SHOW DATABASES".
    • En MariaDB también puede necesitar "BINLOG MONITOR" o "FILE".
    • Sin permisos de modificación de datos ni de estructura.
    • Ventaja: Si se compromete esta cuenta, no puede borrar ni modificar datos.
  • "dba_full"
    • Propósito: Administrador de base de datos, o DBA o personal de DevOps/infraestructura.
    • Permisos típicos: Casi todos los privilegios ("ALL PRIVILEGES").
    • Incluye "SUPER", "CREATE USER", "GRANT OPTION", manejo de replicación, configuración global, etc.
    • Uso recomendado: Solo para muy pocas personas y preferiblemente usando autenticación fuerte (no usarlo en aplicaciones).
• Usa "DEFAULT ROLE" casi siempre. Evita que los usuarios tengan que hacer "SET ROLE" manualmente.
• Mandatory Roles para privilegios básicos de todos los usuarios:
  
  

  SET PERSIST mandatory_roles = 'usuario_basico';
  

• Principio de menor privilegio
  • La mayoría de los usuarios solo necesitan "lector_app" o "escritor_app".
  • Solo activar roles potentes cuando sea necesario.
• Auditoría
  
  

  SELECT user, host, privilege_type, is_grantable 
  FROM information_schema.role_column_grants;
  

Diferencias a tener en cuenta

• MariaDB es más estricto: no permite autenticarse directamente con un rol.
• MySQL permite activar varios roles simultáneamente de forma más flexible.
• En entornos mixtos (replicación MySQL y MariaDB) los roles suelen migrarse sin problemas.

Comparativa actualizada