Mysql Creación de usuarios
Sumario
Introducción
- Vimos en el punto anterior como es posible manipular la tabla user directamente con las órdenes SQL INSERT, UPDATE, DELETE.
- Pero a la hora de crear nuevos usuarios, normalmente haremos uso de órdenes SQL pertenecientes al lenguaje de control de datos.
- Más información en este enlace.
SQL
Creando nuevos usuarios
- La orden SQL que permite añadir nuevos usuarios es: CREATE USER.
- Su forma más simple:
1 CREATE USER nombre_usuario IDENTIFIED BY 'password';
- nombre_usuario tiene el formato: 'usuario'@'host'
- Por ejemplo:
1 CREATE USER 'angel' IDENTIFIED BY '12345678';
- Estaríamos añadiendo una nueva fila a la tabla 'user' de la base de datos 'mysql', con nombre de usuario 'angel' y password '12345678'
- Si no podemos la palabra clave 'IDENTIFIED BY' estaríamos creando un usuario sin contraseña, lo cual no es recomendado.
- Por ejemplo:
1 CREATE USER 'anonimo';
- Al crear este usuario sin contraseña, podríamos conectarnos al Mysql sin enviar el parámetro -p o --password en herramientas clientes:
1 mysql -u anonimo
- Para ver las sentencia 'CREATE USER' utilizada para crear un determinado usuario, debemos ejecutar la orden SQL: SHOW CREATE USER usuario
- Por ejemplo, para ver la sentencia 'create user' del usuario actual podemos poner:
1 SHOW CREATE USER CURRENT_USER();
- Más información en este enlace.
Opciones para el password
- Más información en este enlace.
- Podemos 'obligar' a que un usuario cambie su contraseña cuando se conecta por primera vez:
1 CREATE USER 'angel' IDENTIFIED BY '12345678' PASSWORD EXPIRE;
- Poniendo la palabra 'PASSWORD EXPIRE'.
- También podemos 'obligar' al usuario a que cambie de password cada cierto número de días:
1 CREATE USER 'angel' IDENTIFIED BY '12345678' PASSWORD EXPIRE INTERVAL 60 DAY;
- En el ejemplo, deberá cambiar de password cada 60 días.
- El resto de opciones indicarían:
- NEVER: que el password nunca 'caduca'.
- DEFAULT: El password caducaría en el número de días indicado por la variable del sistema default_password_lifetime que es de 360 días.
Bloqueo / Desbloqueo de cuentas
- Más información en este enlace.
- Cuando creamos (o modificamos) una cuenta, podemos indicar si dicha cuenta está bloqueada.
- En caso de estarlo, no estaría permitida la conexión al servidor Mysql haciendo uso de dicha cuenta.
- Es una características que aparece a partir de la versión 5.7 de Mysql. Si queremos que la tabla 'user' disponga de dicha columna si venimos de una actualización de una versión anterior, deberemos de ejecutar la orden mysql-upgrade, ya comentada en el punto de instalación de Mysql.
- Por ejemplo:
1 CREATE USER 'angel' IDENTIFIED BY '12345678' ACCOUNT LOCK;
- Por defecto las cuentas, si no indicamos nada, son creadas con el estado desbloqueado, ser lo mismo que poner:
1 CREATE USER 'angel' IDENTIFIED BY '12345678' ACCOUNT UNLOCK;
- Para saber si una cuenta está bloqueada podemos hacer uso de la orden SQL: SHOW CREATE USER usuario.
- Cada vez que un usuario intenta conectarse haciendo uso de una cuenta bloqueada, la variable de estado statvar_Locked_connects se incrementa.
- Si un usuario intenta conectarse haciendo uso de una cuenta bloqueada, recibirá el siguiente aviso:
- Y se escribirá una entrada en el fichero de log error.log.
Conexiones seguras
- Más información en este enlace.
- Queda fuera de este manual explicar los tipos de certificados y como funcionan para garantizar una conexión segura.
- Decir que se basan en el uso de 'claves públicas' y 'claves privadas'.
- Podemos consultar la wikipedia para aclarar los conceptos.
- En MYSQL podemos hacer que el servidor acepte conexiones SSL, es decir, conexiones cifradas.
- Para ello debemos seguir los siguientes pasos:
Comprobamos si el servidor Mysql acepta conexiones seguras. Para ello debemos mirar las variables del sistema have_open_ssl y have_ssl. Como vemos en la imagen, están deshabilitados.
Ahora necesitamos generar los archivos que conformarán el certificado y que serán utilizados por los clientes y por el servidor. Para ello debemos hacer uso de la orden mysql-ssl-rsa-setup que viene con Mysql y que generá los archivos necesarios en el directorio indicado por la variable del sistema datadir y que por defecto es /var/lib/mysql/. Como a estos archivos tiene que acceder el servidor mysql vamos a indicar con la opción --uid=mysql que el propietario de los archivos generados será el usuario que utiliza mysql para acceder al sistema operativo y que por defecto es mysql.
En versiones anteriores de Mysql debíamos editar el archivo de configuración y añadir las líneas que le indicaban a Mysql donde buscar el certificado (más información en este enlace. Pero en las versiones actuales solamente necesitamos reiniciar el servicio.
Ahora los clientes que intenten conectarse al Mysql, si lo haces desde la propia máquina, intentarán utilizar la conexión SSL por defecto. Podemos comprobarlo conectándose con la orden: mysql -u root -p -h 127.0.0.1. Fijarse que debemos de poner el parámetro -h (de host) ya que de esta forma estamos conectándonos por TCTP/IP. Si no lo ponemos estaríamos conectándonos a través del socket.
Si hubiéramos generado los archivos SSL en otro directorio diferente al directorio de datos de Mysql, tendríamos que editar el archivo de configuración del servidor Mysql, indicar la ruta donde se encuentran los archivos (como muestra la imagen), editar el archivo de configuración de las herramientas clientes (en la sección [] correspondiente) y poner la misma información que en el servidor, pero sólo las entradas: ssl-cert y ssl-key. Recuerda que en las distribuciones basadas en Ubuntu tenemos que dar permiso de acceso a esos archivos si están en un directorio diferente como vimos en el punto de 'Casos especial: los directorios' durante la instalación.
- En este momento el servidor Mysql por defecto va a intentar siempre establecer una conexión segura cuando un cliente intente conectarse.
- Pero no impedimos que un cliente pueda conectarse de forma insegura.
- Veamos un ejemplo:
- Si ejecutamos la orden: mysql -u user1 -p -h 127.0.0.1 --ssl-mode=disabled
- Estaríamos conectando con un usuario creado previamente pero sin utilizar la conexión SSL.
- Podemos comprobarlo:
- Si quisiéramos que el servidor MYSQL sólo aceptara conexione seguras, debemos de escribir en su fichero de configuración: require_secure_transport = ON
- Reiniciar el servicio.
- Podemos comprobar ahora como las conexiones inseguras no está permitidas.
- Nota: En algunos sitios recomienda poner la opción de configuración en el archivo /etc/mysql/my.cnf para evitar conflictos con otras opciones puestas en los diferentes archivos de configuración.
- De esta forma siempre cargará, después de cargar las configuraciones indicadas en los include, la orden indicada.
1 !includedir /etc/mysql/conf.d/ 2 !includedir /etc/mysql/mysql.conf.d/ 3 4 [mysqld] 5 require_secure_transport = ON
- Una vez tenemos configurado el servidor Mysql para que acepte conexiones seguras, podemos obligar a un determinado usuario a que sólo se pueda conectar si utiliza una conexión cifrada mediante un certificado SSL:
- Nota: Para hacer esta práctica deberéis permitir que el servidor Mysql acepte conexiones inseguras, comentando la orden anterior y reiniciando el servicio...
1 CREATE USER 'usuario'@'localhost' IDENTIFIED BY 'password' REQUIRE SSL;
- Como vemos al final de la orden CREATE USER debemos escribir REQUIRE SSL.
- Nota: Un certificado SSL es el tipo de certificado más conocidos de los certificados X.509.
- Por ejemplo:
1 CREATE USER 'clientessl'@'192.168.1.103' IDENTIFIED BY 'clientessl' REQUIRE SSL;
- Estoy creando un usuario de nombre 'clientessl' que va a conectarse desde mi máquina real que tiene ip 192.168.1.103 hacia el servidor Mysql que está en una máquina virtual en modo bridge con la ip 192.168.1.109.
- Si intento conectarme deshabilitando las conexiones seguras me mostrará un mensaje de error, pero sí me dejará con la conexión SSL como podemos comprobar en la siguiente imagen:
- IMPORTANTE: Recordar que es necesario comentar la línea del archivo de configuración de Mysql, bind_address y que si nuestro equipo tiene un firewall deberemos habilitar una regla para permite el tráfico desde los ordenadores que intentan conectar al servidor Mysql y a su puerto 3306.
- Información obtenida de:
Opciones de limitación de recursos
Más información en este enlace.
- Cuando creamos una cuenta podemos establecer ciertos límites al uso que un usuario pueda hacer de los recursos del servidor Mysql.
- Un ejemplo:
1 CREATE USER 'usuario'@'localhost' IDENTIFIED BY 'password' WITH MAX_QUERIES_PER_HOUR=10,MAX_USER_CONNECTIONS 5;
- Los recursos que podemos limitar son:
- MAX_QUERIES_PER_HOUR count, MAX_UPDATES_PER_HOUR count, MAX_CONNECTIONS_PER_HOUR count: Indican cuantas consultas, actualizaciones y conexiones por hora son permitidas. Si no se indica o se pone el valor 0 no existen limitaciones.
- MAX_USER_CONNECTIONS count: Indica cuantas conexiones simultáneas están permitidas para el usuario. Si no se pone nada, se utilizará el valor de la variable del sistema max_user_connections que por defecto está sin limitación.
- Si un usuario intenta hacer uso de un número de recursos superior a los indicados, tendrá un mensaje de error al intentar conectarse como el de la siguiente imagen:
Opciones de autentificación
Más información en este enlace.
- Esta sección ya la vimos al principio para crear un usuario con password.
- En la creación de un usuario podemos especificar el plugin utilizado para guardas la contraseña, el password o ambos.
- Si no se indica el nombre del plugin, se utiliza por defecto el plugin con nombre 'mysql_native_password' a no ser que se indique otro diferente en la variable del sistema default_authentication_plugin.
- Podéis consultas los diferentes plugins en siguiente enlace.
- Recordar que la contraseña se guarda 'encriptada' haciendo uso del plugin indicado, en la columna authentication_string de la tabla mysql.user.
- Podemos enviar o bien:
- 'auth_string': Que sería la contraseña en texto sin encriptar.
- 'hash_string': Que sería la contraseña en formato 'hash' después de aplicarle la función de hash indicada por el plugin.
- Un ejemplo:
1 CREATE USER 'angel' IDENTIFIED BY '12345678';
Ejercicios propuestos
- Crea tres usuarios de nombre u1,u2,u3 utilizando una única instrucción SQL. Todos se van a conectar desde la red 170.10.0.0/16.
- Haz que tengan que cambiar la contraseña cuando se conecten, y que como máximo puedan realizar 50 operaciones de modificación por hora contra la base de datos.
- Sólo podrán conectarse desde un computador simultáneamente.
- Haz que cualquier usuario que se cree, pueda por defecto, realizar como máximo 3 conexiones simultaneas al servidor.
- Crea un usuario y comprueba que esto es cierto (no necesitas realizar tres conexiones, tendrás que comprobarlo de otra forma).
- Crea dos usuarios utilizando una única orden SQL, de tal forma que las dos cuentas estén inicialmente bloqueadas y que el máximo número de conexiones simultáneas sea de 1.
- Debes obligar a que dichos usuarios utilicen conexiones seguras.
- Desbloquea las cuentas anteriores.
- Modifica los usuarios u1 y u2 utilizando una única instrucción SQL y elimina la restricción de que el número máximo de operaciones sea de cincuenta, pero ponles la limitación de que el máximo número de consultas sea de cien a la hora.
- Modifica el password del usuario u3 y desbloquéalo en una única instrucción SQL
- Da de baja al usuario u1.
- Más información en este enlace.
-- Ángel D. Fernández González -- (2017).