En la administración de bases de datos MySQL/MariaDB, es fundamental poder consultar los permisos asignados a los usuarios. Compartimos varias consultas SQL muy útiles para obtener permisos establecidos y para generar comandos de asignación y de revocación de permisos en MySQL/MariaDB.
- Consideraciones de Seguridad.
- Consulta SQL para mostrar todos los usuarios MySQL/MariaDB con su host asignado.
- Consulta SQL para ver todos los permisos de un usuario.
- Consulta SQL para generar comandos GRANT para replicar permisos.
- Consulta SQL para generar comandos REVOKE para eliminar permisos.
- Comando propio de MySQL/MariaDB para mostrar todos los GRANTS de un usuario.
Consideraciones de Seguridad
- Es conveniente tener copias de seguridad actualizadas de la base de datos antes de realizar cualquier cambio.
- Estas consultas requieren privilegios de superusuario o acceso a las tablas del sistema de MySQL.
- Las consultas SQL no ejecutan comandos de cambios de permisos, solo generan los comandos apropiados. Si decides usarlas, verifica los comandos generados antes de ejecutarlos en producción.
- Si has ejecutado comandos de modificación de permisos, recuerda ejecutar
FLUSH PRIVILEGES;después de modificar permisos manualmente. - Los cambios en
mysql.tables_privse reflejan inmediatamente, pero es buena práctica usar FLUSH PRIVILEGES.
Consulta SQL para mostrar todos los usuarios MySQL/MariaDB con su host asignado
La siguiente consulta SQL muestra todos los usuarios de un servidor de MySQL/MariaDB y su host () asignado:
|
1 2 |
SELECT DISTINCT User, Host FROM mysql.user; |
Funciona tanto en versiones antiguas de MySQL/MariaBD como en versiones modernas.
Consulta SQL para ver todos los permisos de un usuario
Las siguientes consultas SQL muestran todos los permisos que tiene asignados un usuario específico desde cualquier host, incluyendo tanto privilegios a nivel de base de datos como a nivel de tabla.
Consulta para ver todos los permisos de un usuario desde cualquier host
Consulta SQL para obtener todos los permisos asignados a un usuario particular de MySQL/MariaDB. Esta consulta funcionará en versiones de MySQL inferiores a la 8.0 y MariaDB inferiores a la 10.x:
|
1 2 3 |
SELECT User, Host, Db, Table_name, Table_priv, Column_priv FROM mysql.tables_priv WHERE User = 'nombre_usuario'; |
Propósito: esta consulta está diseñada para mostrar todos los permisos específicos de tablas a las que se ha asignado algún permiso (select, insert, update, delete, drop, alter) a un usuario particular en MySQL. Es especialmente útil para auditorías de seguridad o para replicar configuraciones de usuarios.
Tablas consultadas:
mysql.tables_priv: tabla del sistema de MySQL que almacena los privilegios a nivel de tabla.
Campos devueltos:
User: nombre del usuario MySQL.Host: dirección o patrón de host desde el cual el usuario puede conectarse.Db: nombre de la base de datos.Table_name: nombre de la tabla específica.Table_priv: privilegios a nivel de tabla (ej: Select, Insert, Update, Delete).Column_priv: privilegios a nivel de columna (si los hay).
Ejemplo de resultado:
| User | Host | Db | Table_name | Table_priv | Column_priv |
| usproyectoa | 192.168.1.% | bdproyectoa | facturas | Select | NULL |
Cuándo usarla:
- Para auditar permisos de usuarios.
- Antes de eliminar o modificar usuarios.
- Para documentar configuraciones de seguridad.
- Para replicar permisos en otro servidor.
Para versiones modernas de MySQL y MariaDB, se puede usar esta consulta SQL:
|
1 2 3 4 5 |
SELECT User, Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Create_tmp_table_priv, Lock_tables_priv, Create_view_priv, Show_view_priv, Create_routine_priv, lter_routine_priv, Execute_priv, Event_priv, Trigger_priv FROM mysql.db ORDER BY Host, Db; |
Consulta para ver todos los permisos de un usuario a nivel de base de datos
La siguiente consulta SQL muestra todos los permisos que un usuario (si se indica el WHERE User = 'nombre_usuario') tiene a nivel de base de datos:
|
1 2 3 4 |
SELECT User, Host, Db, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Grant_priv FROM mysql.db WHERE User = 'nombre_usuario'; |
Propósito: esta consulta está diseñada para mostrar los privilegios que tiene un usuario específico a nivel de base de datos completa en MySQL/MariaDB. A diferencia de los permisos a nivel de tabla (que se ven en mysql.tables_priv), estos permisos aplican a todas las tablas dentro de una base de datos específica.
Tabla consultada:
mysql.db: tabla del sistema de MySQL que almacena los privilegios a nivel de base de datos.
Consulta universal para cualquier versión de MySQL/MariaDB para mostrar permisos asignados a usuarios
La siguiente consulta SQL obtiene todos los privilegios/permisos asignados a un usuario, tanto a tablas como a base de datos y es válida para cualquier versión de MySQL/MariaDB:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
SELECT 'Global' as Nivel, u.User, u.Host, 'ALL DATABASES' as Db, 'N/A' as Table_name, CONCAT_WS(', ', IF(u.Select_priv = 'Y', 'SELECT', NULL), IF(u.Insert_priv = 'Y', 'INSERT', NULL), IF(u.Update_priv = 'Y', 'UPDATE', NULL), IF(u.Delete_priv = 'Y', 'DELETE', NULL) ) as Privilegios FROM mysql.user u WHERE u.User = 'nombre_usuario' AND (u.Select_priv = 'Y' OR u.Insert_priv = 'Y' OR u.Update_priv = 'Y' OR u.Delete_priv = 'Y') UNION ALL SELECT 'Database' as Nivel, d.User, d.Host, d.Db, 'ALL TABLES' as Table_name, CONCAT_WS(', ', IF(d.Select_priv = 'Y', 'SELECT', NULL), IF(d.Insert_priv = 'Y', 'INSERT', NULL), IF(d.Update_priv = 'Y', 'UPDATE', NULL), IF(d.Delete_priv = 'Y', 'DELETE', NULL) ) as Privilegios FROM mysql.db d WHERE d.User = 'nombre_usuario' AND (d.Select_priv = 'Y' OR d.Insert_priv = 'Y' OR d.Update_priv = 'Y' OR d.Delete_priv = 'Y') UNION ALL SELECT 'Table' as Nivel, t.User, t.Host, t.Db, t.Table_name, t.Table_priv as Privilegios FROM mysql.tables_priv t WHERE t.User = 'nombre_usuario' ORDER BY Nivel, Host, Db, Table_name; |
Consulta SQL para generar comandos GRANT para replicar permisos
Esta consulta transforma los permisos actuales en comandos SQL ejecutables que se pueden usar para recrear los mismos permisos en otro servidor o usuario.
La siguiente consulta SQL devuelve todos los comandos GRANT necesarios para replicar los permisos actuales asignados a un usuario particular MySQL/MariaDB:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT CONCAT( 'GRANT ', UPPER(REPLACE(Table_priv, ',', ', ')), ' ON `', Db, '`.`', Table_name, '` TO '', User, ''@'', Host, '';' ) AS grant_command FROM mysql.tables_priv WHERE User = 'nombre_usuario' AND Table_priv != ''; |
Propósito: genera automáticamente los comandos GRANT necesarios para replicar exactamente los mismos permisos que tiene un usuario. Esto es de gran ayuda para migraciones, backups de configuraciones o para crear usuarios con permisos idénticos.
Funciones MySQL utilizadas:
CONCAT(): une múltiples cadenas de texto.UPPER(): convierte texto a mayúsculas.REPLACE(): reemplaza comas por comas con espacio para mejor formato.
Estructura del comando generado:
|
1 |
GRANT SELECT ON `basedatos`.`tabla` TO 'usuario'@'host'; |
Características importantes:
- Los nombres de base de datos y tabla se encierran en backticks (`) para manejar caracteres especiales.
- Convierte automáticamente a mayúsculas los tipos de permisos.
- Maneja múltiples permisos separados por comas (ej: «Select,Insert,Update»).
- Filtra registros donde no hay permisos de tabla definidos.
Consulta SQL para generar comandos REVOKE para eliminar permisos
El complemento perfecto a la consulta anterior, genera los comandos para revocar permisos específicos.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT CONCAT( 'REVOKE ', UPPER(REPLACE(Table_priv, ',', ', ')), ' ON `', Db, '`.`', Table_name, '` FROM '', User, ''@'', Host, '';' ) AS revoke_command FROM mysql.tables_priv WHERE User = 'nombre_usuario' AND Table_priv != ''; |
Propósito: crea comandos REVOKE que permiten eliminar permisos específicos de manera controlada y precisa.
Ejemplo de resultado:
|
1 |
REVOKE SELECT ON `bdproyectoa`.`facturas` FROM 'usproyectoa'@'192.168.1.%'; |
Comando propio de MySQL/MariaDB para mostrar todos los GRANTS de un usuario
MySQL/MariaDB también cuenta con un comando propio para obtener todos los GRANT (permisos) asignados a un usuario. Para listar todos los GRANT asignados al usuario actual (con el que estamos conectados a MySQL), usaremos el comando:
|
1 |
SHOW GRANTS FOR CURRENT_USER() |
Para mostrar los GRANT de un usuario concreto:
|
1 |
SHOW GRANTS FOR 'root'@'localhost'; |
