El mundo de MySQL & MariaDB

Creación, Gestión y Eliminación de Usuarios MariaDB: Guía para Principiantes

Operaciones básicas en MariaDB
目次

Introducción

1.1 ¿Quieres saber cómo crear un usuario en MariaDB?

¿Alguna vez has querido crear un nuevo usuario en MariaDB y establecer los permisos adecuados?

Por ejemplo, podrías haber encontrado problemas en situaciones como las siguientes.

  • CREATE USER ejecutado pero no se puede iniciar sesión
  • GRANT usado para otorgar permisos, pero no se aplican
  • No sabes cómo establecer los permisos adecuados en un entorno de producción
  • Quieres saber cómo eliminar de forma segura usuarios innecesarios

Si te enfrentas a estos problemas, al leer este artículo podrás comprender todos los pasos, desde la creación de usuarios en MariaDB hasta la gestión de permisos y los métodos de eliminación.

1.2 ¿Qué puede resolver este artículo?

Este artículo ofrece una explicación detallada de los siguientes temas relacionados con la gestión de usuarios en MariaDB.

  • Métodos para crear y eliminar usuarios en MariaDB
  • Otorgar y gestionar permisos (puntos clave para configuraciones de permisos adecuadas)
  • Errores comunes y sus soluciones
  • Métodos de operación adecuados para entornos de desarrollo y producción

Estos temas se explican con ejemplos de SQL fáciles de entender para principiantes, para que puedas aprender y practicar al mismo tiempo.

1.3 Lectores objetivo

Este artículo está dirigido a las siguientes personas.

  • Principiantes: Personas que aprenden la gestión de usuarios en MariaDB por primera vez
  • Ingenieros: Personas que desean establecer permisos adecuados en un entorno de desarrollo
  • Administradores de bases de datos: Personas que quieren reforzar la seguridad en un entorno de producción

Para proporcionar información útil para las operaciones reales, incluso quienes manejan MariaDB por primera vez pueden abordarlo con confianza.

Creación de usuarios en MariaDB

2.1 Comando básico de creación de usuarios

En MariaDB, utiliza el comando CREATE USER para crear un nuevo usuario.

Puedes especificar el nombre de usuario y la contraseña con la siguiente sintaxis.

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';

Por ejemplo, para crear un usuario llamado yamada en localhost y establecer la contraseña a password123, sería así.

CREATE USER 'yamada'@'localhost' IDENTIFIED BY 'password123';

Ejecutar este comando crea el usuario yamada para localhost (solo se puede iniciar sesión en el servidor).

2.2 Ejemplos de creación por caso de uso

En la creación de usuarios en MariaDB, puedes especificar dónde se permiten las conexiones en la parte hostname. Es importante realizar configuraciones adecuadas para cada caso de uso.

Caso

Ejemplo de comando

Descripción

Entorno local (para desarrollo)CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'password';

Usuario dedicado al entorno de desarrollo

Permitir conexiones externasCREATE USER 'remote_user'@'%' IDENTIFIED BY 'password';

Conexión posible desde cualquier host (precaución de seguridad)

Permitir conexiones desde una dirección IP específicaCREATE USER 'admin'@'192.168.1.100' IDENTIFIED BY 'password';

Restringir conexiones solo a una dirección IP específica

Permitir conexiones desde subred específicaCREATE USER 'network_user'@'192.168.1.%' IDENTIFIED BY 'password';

Solo las máquinas con direcciones IP 192.168.1.x pueden conectarse

De esta manera, al usar '%' se permiten conexiones desde todos los hosts, pero no se recomienda para entornos de producción debido a los altos riesgos de seguridad.

En entornos de producción, lo mejor es especificar direcciones IP o configurar conexiones a través de VPN.

2.3 Precauciones al crear usuarios

Al crear un usuario, existen varias precauciones.

(1) Verificar usuarios existentes

Antes de crear un usuario, es importante verificar si ya existe el mismo usuario.

Ejecutar el siguiente comando lista los usuarios registrados actualmente.

SELECT user, host FROM mysql.user;

Salida de ejemplo:

+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | localhost |
| yamada  | localhost |
+---------+-----------+

Si ya existe un usuario con el mismo username@hostname, ejecutar CREATE USER provocará un error.

(2) Manejo cuando el usuario ya existe

En MariaDB, no puedes crear un usuario sobrescribiendo uno existente.

Por lo tanto, si el mismo usuario ya existe, maneja la situación con uno de los siguientes métodos.

  • Cambiar la contraseña del usuario existente
ALTER USER 'yamada'@'localhost' IDENTIFIED BY 'newpassword123';
  • Eliminar el usuario innecesario y luego crear uno nuevo
DROP USER 'yamada'@'localhost';
CREATE USER 'yamada'@'localhost' IDENTIFIED BY 'password123';

Gestión de permisos en MariaDB

3.1 Otorgar permisos

Sintaxis básica

En MariaDB, puedes otorgar permisos a los usuarios usando el comando GRANT.

GRANT privileges ON database_name.table_name TO 'username'@'host_name';

Por ejemplo, para otorgar todos los permisos sobre test_db al usuario yamada, descríbelo de la siguiente manera.

GRANT ALL PRIVILEGES ON test_db.* TO 'yamada'@'localhost';

El significado de este comando:

  • ALL PRIVILEGES : Otorga todos los permisos
  • test_db.* : Se aplica a todas las tablas en la base de datos test_db
  • 'yamada'@'localhost' : Se aplica a yamada que inicia sesión desde el host local

3.2 Lista de Permisos Comúnmente Usados

En MariaDB, puedes otorgar permisos como los siguientes.

Permission

Descripción

ALL PRIVILEGES

Grants all permissions

SELECT

Lectura de datos

INSERT

Insertando datos

UPDATE

Actualizando datos

DELETE

Eliminando datos

CREATE

Creando nuevas tablas o bases de datos

DROP

Eliminación de bases de datos o tablas

GRANT OPTION

Otorgar permisos a otros usuarios

ALTER

Modificando estructuras de tabla

EXECUTE

Ejecutando procedimientos almacenados o funciones

RELOAD

Recargando la configuración del servidor

Si deseas permitir solo operaciones específicas, se recomienda otorgar permisos individualmente.

3.3 Configuración de Permisos por Caso de Uso

Es importante establecer permisos adecuados para cada situación.

Caso de uso

Permissions to Grant

Ejemplo de comando

Administrador de WordPressSELECT, INSERT, UPDATE, DELETEGRANT SELECT, INSERT, UPDATE, DELETE ON wp_db.* TO 'wp_user'@'localhost';
Desarrollador (Entorno de Desarrollo)ALL PRIVILEGESGRANT ALL PRIVILEGES ON dev_db.* TO 'dev_user'@'%';
Usuario de solo lectura

Solo SELECT

GRANT SELECT ON analytics_db.* TO 'readonly'@'localhost';
Administrador de Bases de Datos (Entorno de Producción)ALL PRIVILEGESGRANT ALL PRIVILEGES ON production_db.* TO 'admin'@'localhost';

En entornos de producción, ten cuidado de no otorgar ALL PRIVILEGES de manera indiscriminada.

3.4 Verificar Permisos Otorgados

Para verificar los permisos otorgados, utiliza el comando SHOW GRANTS.

SHOW GRANTS FOR 'username'@'host_name';

Por ejemplo, para verificar los permisos del usuario yamada:

SHOW GRANTS FOR 'yamada'@'localhost';

Ejemplo de salida:

+-----------------------------------------------------------------------------------+
| Grants for yamada@localhost                                                       |
+-----------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE ON test_db.* TO 'yamada'@'localhost'                |
+-----------------------------------------------------------------------------------+

De esta manera, puedes verificar los permisos otorgados actualmente.

3.5 Revocando (Eliminando) Permisos

Para revocar permisos específicos de un usuario, utiliza el comando REVOKE.

Sintaxis Básica

REVOKE privileges ON database_name.table_name FROM 'username'@'host_name';

Por ejemplo, para eliminar el permiso UPDATE del usuario yamada:

REVOKE UPDATE ON test_db.* FROM 'yamada'@'localhost';

Revocando Todos los Permisos

REVOKE ALL PRIVILEGES ON *.* FROM 'yamada'@'localhost';

3.6 Solución de Problemas Cuando los Cambios de Permiso No Se Reflejan

En MariaDB, los cambios de permiso pueden no aplicarse de inmediato. En ese caso, ejecuta el siguiente comando.

FLUSH PRIVILEGES;

Al ejecutar este comando, la configuración de permisos se reflejará inmediatamente.

Eliminando Usuarios en MariaDB

4.1 Eliminando un Usuario

Para eliminar un usuario en MariaDB, utiliza el comando DROP USER.

Sintaxis Básica

DROP USER 'username'@'hostname';

Por ejemplo, para eliminar el usuario yamada de localhost, ejecuta lo siguiente.

DROP USER 'yamada'@'localhost';

Al ejecutar este comando, se eliminará por completo al usuario yamada del sistema MariaDB.

4.2 Cosas a Verificar Antes de Eliminar un Usuario

Antes de eliminar un usuario, hay varias verificaciones importantes.Ten cuidado de no eliminar accidentalmente un usuario necesario.

(1) Confirmar si el Usuario a Eliminar Existe

En MariaDB, intentar eliminar un usuario que no existe resultará en un error.

Ejecuta el siguiente comando para verificar si el usuario a eliminar existe.

SELECT user, host FROM mysql.user;

Ejemplo de salida:

+---------+-----------+
| user    | host      |
+---------+-----------+
| root    | localhost |
| yamada  | localhost |
| admin   | 192.168.1.100 |
+---------+-----------+

Si el usuario yamada aparece en esta lista, puede ser eliminado.

(2) Confirmar si el Usuario a Eliminar Está Conectado Actualmente

En MariaDB, si el usuario que intentas eliminar tiene una sesión activa,

la eliminación puede resultar en que el impacto no se refleje inmediatamente.

Ejecuta el siguiente comando para verificar si el usuario a eliminar está activo actualmente.

SELECT user, host FROM information_schema.processlist;

Ejemplo de salida:

+---------+-----------+
| user    | host      |
+---------+-----------+
| yamada  | localhost |
| root    | localhost |
+---------+-----------+

En este caso, dado que el usuario yamada está activo actualmente, debes terminar esa sesión antes de eliminarlo.

(3) Terminar la sesión de forma forzada

Puedes terminar forzosamente una sesión específica usando el comando KILL.

  1. Primero, verifica el ID del proceso actual (columna ID):
SELECT id, user, host FROM information_schema.processlist;

Ejemplo de salida:

+----+---------+-----------+
| id | user    | host      |
+----+---------+-----------+
| 10 | yamada  | localhost |
| 11 | root    | localhost |
+----+---------+-----------+
  1. Si el ID del proceso de yamada es 10, termina forzosamente con el siguiente comando:
KILL 10;

4.3 Post-procesamiento después de la eliminación de un usuario

Después de eliminar un usuario, puede quedar información de permisos innecesaria en la base de datos. En ese caso, ejecuta FLUSH PRIVILEGES para actualizar la información de permisos.

FLUSH PRIVILEGES;

Al ejecutar esto, la información del usuario eliminado se eliminará por completo de la base de datos.

4.4 Precauciones al eliminar usuarios

Al eliminar un usuario, ten en cuenta los siguientes puntos.

  1. Los usuarios eliminados no pueden ser restaurados
  • Ejecutar DROP USER significa que el usuario eliminado no puede deshacerse.
  • Si se elimina por error, debes recrearlo usando CREATE USER nuevamente.
  1. Puede ser necesario transferir los permisos
  • Si el usuario eliminado era responsable de operaciones importantes de la base de datos, debes transferir los permisos a un usuario apropiado con antelación.
  1. Ten cuidado al eliminar en un entorno de producción
  • En un entorno de producción, la eliminación repentina puede causar tiempo de inactividad del sistema o errores.
  • Recomendamos analizar el impacto con antelación y realizar copias de seguridad.

Errores comunes y soluciones (FAQ)

La gestión de usuarios en MariaDB puede provocar diversos errores. Esta sección explica las causas de los errores frecuentes y sus soluciones.

5.1 Preguntas frecuentes para principiantes

Q1: ¿Cuáles son las diferencias en la gestión de usuarios entre MariaDB y MySQL?

MariaDB y MySQL básicamente utilizan la misma sintaxis, pero existen diferencias en algunas funciones.

Elementos de comparación

MySQL

MariaDB

CREATE USER

Sí

Sí

SHOW GRANTS FOR

Sí

Sí

DROP USER

Sí

Sí

CREATE ROLE

MySQL 8.0 and later

MariaDB 10.0 and later

En MariaDB, se admite CREATE ROLE, lo que permite la gestión de usuarios basada en roles.

Q2: ¿Cuál es la diferencia entre localhost y %?

Especificar el nombre del host es importante en la gestión de usuarios de MariaDB.

  • localhost : Conexiones solo desde la máquina local
  • % : Permite conexiones desde todos los hosts (riesgo de seguridad)
  • 192.168.1.100 : Solo se permite la dirección IP específica
  • 192.168.1.% : Solo se permite la subred específica (192.168.1.*)

5.2 Solución de problemas

Q3: ¿Cómo manejar el error «ERROR 1396 (HY000): Operation CREATE USER failed»?

Causa: El usuario especificado ya existe, por lo que no se puede crear. Solución: Verifica el usuario existente y elimínalo o modifícalo según sea necesario.

  1. Verificar usuarios existentes
SELECT user, host FROM mysql.user WHERE user='Yamada';
  1. Eliminar si es innecesario
DROP USER 'Yamada'@'localhost';
  1. Si cambias la contraseña
ALTER USER 'Yamada'@'localhost' IDENTIFIED BY 'newpassword123';

Q4: Los permisos otorgados con GRANT no se aplican

Causa: La caché de privilegios de MariaDB no se ha actualizado. Solución: Ejecuta FLUSH PRIVILEGES para actualizar los privilegios.

FLUSH PRIVILEGES;

Q5: No se puede conectar desde el exterior incluso usando %

Causa: Las conexiones remotas pueden estar restringidas en el archivo de configuración de MariaDB. Solución:

  1. Edita el archivo de configuración /etc/mysql/my.cnf (o /etc/mysql/mariadb.conf.d/50-server.cnf) y cambia el valor de bind-address a 0.0.0.0.
bind-address = 0.0.0.0
  1. Reinicia MariaDB
sudo systemctl restart mariadb
  1. Permite la conexión usando el comando GRANT con %
GRANT ALL PRIVILEGES ON test_db.* TO 'remote_user'@'%' IDENTIFIED BY 'password';

Q6: El error «Acceso denegado para el usuario ‘user’@’host’» ocurre

Cause: El usuario no tiene privilegios adecuados o la contraseña es incorrecta.
Solution:

  1. Verificar privilegios

«` SHOW GRANTS FOR ‘user’@’host’;

2. **Conceder privilegios correctos**

   ```
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'host';
  1. Confirmar inicio de sesión con la contraseña correcta

mysql -u user -p

Resumen

La gestión de usuarios de MariaDB es crucial para mejorar la seguridad y la eficiencia operativa de la base de datos.
En este artículo, detallamos creación de usuarios, gestión de permisos, métodos de eliminación, errores comunes y sus soluciones.

6.1 Revisión del artículo

① Creación de usuarios en MariaDB

  • Crear un nuevo usuario usando el comando CREATE USER
  • Puedes restringir el acceso especificando @'hostname'
  • Al crear un usuario, es importante verificar si existen usuarios

② Gestión de permisos en MariaDB

  • Conceder permisos mínimos usando el comando GRANT
  • Verificar permisos actuales usando el comando SHOW GRANTS
  • Eliminar permisos innecesarios usando el comando REVOKE
  • Aplicar cambios con FLUSH PRIVILEGES

③ Eliminación de usuarios en MariaDB

  • Eliminar usuarios con el comando DROP USER
  • Verificar sesiones conectadas actualmente antes de la eliminación
  • Limpiar información de permisos innecesarios con FLUSH PRIVILEGES

④ Errores comunes y soluciones

  • ERROR 1396 (HY000) → Verificar si existen usuarios y eliminar si es necesario
  • Los cambios de permisos con GRANT no se reflejan → Ejecutar FLUSH PRIVILEGES
  • Access denied for user → Verificar si se conceden los permisos adecuados
  • No se puede conectar externamente incluso con % → Cambiar la configuración de bind-address

6.2 Mejores prácticas para la gestión de usuarios en MariaDB

  • Conceder solo los permisos mínimos necesarios
  • No conceder ALL PRIVILEGES de forma indiscriminada
  • En entornos de producción, limitar a SELECT, INSERT, UPDATE, DELETE únicamente
  • Verificar siempre después de los cambios de permisos
  • Verificar si la configuración es la deseada usando el comando SHOW GRANTS
  • Evaluar los impactos con antelación al eliminar
  • Ser cauteloso con las eliminaciones en producción y realizar copias de seguridad
  • Implementar medidas de seguridad exhaustivas
  • Implementar restricciones de IP para conexiones remotas
  • Establecer contraseñas fuertes para el usuario root
  • Eliminar regularmente usuarios y permisos innecesarios

6.3 En conclusión

A través de este artículo, puedes aprender desde lo básico hasta la gestión avanzada de usuarios en MariaDB.
Utiliza este conocimiento para lograr una gestión de bases de datos segura y eficiente.

Continuaremos proporcionando información útil sobre MariaDB, ¡así que consulta! 🚀