En el mundo de la gestión de bases de datos, controlar el acceso de los usuarios es crucial para mantener la integridad y la seguridad de los datos. Esta publicación de blog lo guiará a través de un escenario real de configuración de permisos de usuario de MySQL, incluido el proceso, los posibles errores y los pasos de depuración.

El escenario

Imagínate que eres administrador de bases de datos de una empresa que tiene varias bases de datos:

• Bases de datos originales: productos, clientes, pedidos, analíticas
• Copiar bases de datos: copia_productos, copia_clientes, copia_pedidos, copia_análisis

Su tarea es configurar permisos para un usuario llamado 'analista' con los siguientes requisitos:

1. El analista debería poder ver bases de datos y tablas de las bases de datos originales.
2. El analista no debería poder editar las bases de datos originales.
3. El analista no debería poder crear nuevas bases de datos.
4. El analista debe tener acceso completo (ver, editar, eliminar, crear tablas) a las bases de datos '_copiar'.

Vamos a profundizar en cómo podemos lograr esto usando las declaraciones GRANT y REVOKE de MySQL.

Paso 1: configuración inicial

Primero, necesitamos conectarnos al servidor MySQL con una cuenta administrativa:

mysql -h hostname -P port -u admin -p

Reemplace 'nombre de host', 'puerto' y 'admin' con los detalles reales de su servidor y el nombre de usuario de administrador.

Paso 2: crear el usuario

Si el usuario aún no existe, debemos crearlo:

CREATE USER 'analyst'@'%' IDENTIFIED BY 'password';

Reemplace 'contraseña' con una contraseña fuerte y segura.

Paso 3: Conceder los permisos necesarios

Ahora, otorguemos los permisos necesarios:

-- Grant SELECT on original databases
GRANT SELECT ON products.* TO 'analyst'@'%';
GRANT SELECT ON customers.* TO 'analyst'@'%';
GRANT SELECT ON orders.* TO 'analyst'@'%';
GRANT SELECT ON analytics.* TO 'analyst'@'%';

-- Grant all privileges on copy databases
GRANT ALL PRIVILEGES ON products_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON customers_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON orders_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON analytics_copy.* TO 'analyst'@'%';

-- Grant global privileges
GRANT PROCESS, SHOW DATABASES ON *.* TO 'analyst'@'%';

-- Apply the changes
FLUSH PRIVILEGES;

Paso 4: verificar los permisos

Después de configurar los permisos, es fundamental verificarlos:

SHOW GRANTS FOR 'analyst'@'%';

Depuración y solución de problemas

Problema 1: Demasiados privilegios

En nuestro escenario, inicialmente encontramos un problema en el que el 'analista' tenía demasiados privilegios:

mysql> SHOW GRANTS FOR 'analyst'@'%';
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| Grants for analyst@%                                                                                                                                                                                                                                                                                                              |
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO "analyst"@"%" WITH GRANT OPTION |
| GRANT REPLICATION_APPLIER,ROLE_ADMIN ON *.* TO "analyst"@"%" WITH GRANT OPTION                                                                                                                                                                                                                                                    |
...
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

Solución:

Para solucionar este problema, revocamos todos los privilegios y luego otorgamos solo los necesarios:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'analyst'@'%';
GRANT PROCESS, SHOW DATABASES ON *.* TO 'analyst'@'%';
-- Then re-grant the specific permissions as shown in Step 3

Problema 2: Faltan permisos para copiar bases de datos

Después de corregir los privilegios excesivos, notamos que faltaban los permisos para copiar las bases de datos:

mysql> SHOW GRANTS FOR 'analyst'@'%';
 ----------------------------------------------------- 
| Grants for analyst@%                                |
 ----------------------------------------------------- 
| GRANT PROCESS, SHOW DATABASES ON *.* TO "analyst"@"%"|
| GRANT SELECT ON "products".* TO "analyst"@"%"       |
| GRANT SELECT ON "customers".* TO "analyst"@"%"      |
| GRANT SELECT ON "orders".* TO "analyst"@"%"         |
| GRANT SELECT ON "analytics".* TO "analyst"@"%"      |
 ----------------------------------------------------- 

Solución:

Agregamos las subvenciones que faltaban para las bases de datos de copia:

GRANT ALL PRIVILEGES ON products_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON customers_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON orders_copy.* TO 'analyst'@'%';
GRANT ALL PRIVILEGES ON analytics_copy.* TO 'analyst'@'%';
FLUSH PRIVILEGES;

Resultado final

Después de aplicar todos estos cambios y correcciones, las subvenciones finales deberían verse así:

mysql> SHOW GRANTS FOR 'analyst'@'%';
 ----------------------------------------------------- 
| Grants for analyst@%                                |
 ----------------------------------------------------- 
| GRANT PROCESS, SHOW DATABASES ON *.* TO "analyst"@"%"|
| GRANT SELECT ON "products".* TO "analyst"@"%"       |
| GRANT SELECT ON "customers".* TO "analyst"@"%"      |
| GRANT SELECT ON "orders".* TO "analyst"@"%"         |
| GRANT SELECT ON "analytics".* TO "analyst"@"%"      |
| GRANT ALL PRIVILEGES ON "products_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "customers_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "orders_copy".* TO "analyst"@"%"|
| GRANT ALL PRIVILEGES ON "analytics_copy".* TO "analyst"@"%"|
 ----------------------------------------------------- 

Conclusión

Configurar permisos de usuario MySQL adecuados puede ser complicado, pero es un aspecto crucial de la gestión de bases de datos. Si utiliza cuidadosamente las declaraciones GRANT y REVOKE y siempre verifica los resultados, puede crear un entorno seguro y funcional para sus usuarios.

Recuerda estos puntos clave:

1. Comience siempre con el principio de privilegio mínimo.
2. Utilice SHOW GRANTS para verificar los permisos después de realizar cambios.
3. Tenga cuidado con los privilegios globales (ON .).
4. No olvides ELIMINAR PRIVILEGIOS después de realizar cambios.

Si sigue estas pautas y los pasos descritos en esta publicación, estará bien equipado para administrar los permisos de usuario de MySQL de manera efectiva.