Mysql Creación de usuarios

De MediaWiki
Ir a la navegación Ir a la búsqueda

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.


SQL

Creando nuevos usuarios

  • La orden SQL que permite añadir nuevos usuarios es: CREATE USER.


Mysql usuarios 13.jpg
Imagen obtenida de este enlace


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'@'localhost' 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'@'localhost';
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


  • Si no indicamos el host, Mysql asignará el patrón '%' al mismo.
Por ejemplo:
1 CREATE USER 'anonimo';


  • Nota: Es importante tener en cuenta el formato del usuario de la forma: 'usuario'@'host', ya que si ponemos esto: 'usuario@host' estaríamos creando un usuario de nombre 'usuario@host' que puede conectarse desde cualquier PC.


  • 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();
Mysql usuarios 1.jpg




Opciones para el password

Mysql usuarios 14.jpg


  • Podemos 'obligar' a que un usuario cambie su contraseña cuando se conecta por primera vez:
1 CREATE USER 'angel'@'localhost' 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'@'localhost' 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 en versiones anteriores a la 5.7.11 y 0 (no caduca) a partir de la versión anterior.




Bloqueo / Desbloqueo de cuentas

Mysql usuarios 15.jpg


  • 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'@'localhost' IDENTIFIED BY '12345678' ACCOUNT LOCK;
Por defecto las cuentas, si no indicamos nada, son creadas con el estado desbloqueado, sería lo mismo que poner:
1 CREATE USER 'angel'@'localhost' IDENTIFIED BY '12345678' ACCOUNT UNLOCK;


  • Para saber si una cuenta está bloqueada podemos hacer uso de la orden SQL: SHOW CREATE USER usuario.
Mysql usuarios 16.jpg



  • Si un usuario intenta conectarse haciendo uso de una cuenta bloqueada, recibirá el siguiente aviso:
Mysql usuarios 17.jpg
Y se escribirá una entrada en el fichero de log error.log.



Conexiones seguras

Conexiones seguras en el servidor

Mysql usuarios 18.jpg


  • 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.
Deberíamos conocer los siguientes conceptos:
  • Certificado digital: identidad electrónica firmada por una autoridad certificadora o CA
  • Firma digital: es un sistema para demostrar la autenticidad de un mensaje electrónico.
  • Clave pública: es una clave usada para la autenticación en una comunicación segura TLS (es el sucesor de SSL) o cifrar mensajes destinados al dueño de la clave.


  • En MYSQL podemos hacer que el servidor acepte conexiones SSL, es decir, conexiones cifradas.
Para ello debemos seguir los siguientes pasos:


  • 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:
Mysql usuarios 10.jpg



  • 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.
Mysql usuarios 11.jpg
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:
Mysql usuarios 12.jpg


  • 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:



Conexiones seguras en MysqlWorkBench

Opción 1
  • Podemos hacer uso de una conexión SSH para realizar un 'puente' hacia el puerto 3306 del servidor remoto.
En este caso, no sería necesario modificar el archivo de configuración del servidor para aceptar conexiones remotas, ya que la conexión la haríamos realmente en el servidor, como si estuviéramos trabajando localmente, pero a través de una conexión ssh.
  • Por lo tanto, con esta opción no necesitamos comentar la línea 'bind_address' del archivo de configuración del servidor.
  • Los pasos a seguir son:
  • Que el servidor donde está instalado el Mysql acepte conexiones ssh, instalando el servicio con la orden de consola: sudo apt-get install ssh.
Podemos cambiar el puerto por defecto en el archivo de configuración: /etc/ssh/sshd_config
  • Debemos de crear una conexión ssh desde el equipo cliente al equipo donde está el servidor Mysql y creando un 'túnel' para que la herramienta MysqlWorkBench se conecte a través de la conexión ssh establecida. Para hacer eso seguiremos los pasos indicados en este enlace o de forma offline en este archivo: Media:Conectar_via_SSH_a_MYsql.pdf


  • Atención: En esta forma de conexión, los usuarios se están conectando al servidor Mysql desde localhost ya que estamos estableciendo una conexión ssh.
Opción 2
  • Esta opción es menos segura que la anterior ya que necesitamos que el servidor acepte conexiones desde fuera, dando lugar a posibles ataques.
  • En este caso, una vez instalado el certificado de seguridad, como vimos en el punto Conexiones seguras del servidor, solamente tenemos que ir a las propiedades de una conexión en MysqlWorkBench y escoger la opción SSL Required.




Conexiones seguras en phpmyadmin

  • Atención: Conectarnos haciendo uso de 'phpmyadmin',lleva consigo que los usuarios se están conectando al servidor Mysql desde localhost.


Opción 1
  • Modificar el servidor Web Apache para que acepte conexiones seguras (del tipo https).


  • Comentar que de esta forma estamos encriptando la comunicación entre Equipo cliente y el servidor WEB APACHE.
Los usuarios del Mysql pueden ser usuarios que no tengan conexión segura.




Opción 2
  • Hacer lo mismo que en el caso del MysqlWorkBench, crear un tunel ssh para las conexiones https de la forma:
  • Equipo Cliente: sudo ssh -L 443:localhost:443 USER_SO@IP_MYSQL (podríamos hacerlo con la herramienta putty)
  • Una vez conectado, escribir en la URL: https://localhost/phpmyadmin




Opciones de limitación de recursos

Mysql usuarios 19.jpg

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:
Mysql usuarios 20.jpg



Conexiones simultáneas

  • En lo referente al número de conexiones máximas de un usuario (simultáneas) debemos de tener en cuenta que cuando nos conectamos:
  • Al MySqlWorkBench se crean dos conexiones por cada usuario.
Para comprobarlo podemos ejecutar la orden SQL: SHOW PROCESS LIST
Esta orden realiza una consulta select en la tabla del sistema: information_schema.processlist y devuelve las conexiones activas al servidor Mysql de todos los usuarios.
El usuario tiene que tener el permiso PROCESS otorgado para ver las conexiones de todos los usuarios, sino sólo ve las suyas (columna Process_priv de la tabla mysql.users).
Mysql usuarios 50.jpg


  • A a través de PhpMyAdmin también se crean dos conexiones, pero una de ellas es la del usuario phpmyadmin. Además, en el entorno web, las conexiones se cierran una vez realizadas.
Mysql usuarios 51.jpg


  • A través de herramientas clientes, tipo mysql, mysqladmin,...se crea una única conexión al servidor y se mantiene hasta que salgamos.



  • Una orden SQL que podemos emplear para obtener el número de conexiones actuales por cada usuario conectado al servidor Mysql:
1 SELECT IFNULL(usr,'All Users') user,IFNULL(hst,'All Hosts') host,COUNT(1) Connections
2 FROM
3 (
4     SELECT user usr,LEFT(host,LOCATE(':',host) - 1) hst
5     FROM information_schema.processlist
6 ) A GROUP BY user,host WITH ROLLUP;



Opciones de autentificación

Mysql usuarios 21.jpg

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 guardar la contraseña.
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';




Modificando usuarios

  • La orden SQL que permite modificar usuarios es: ALTER USER.


Mysql usuarios 22.jpg
Imagen obtenida de este enlace


  • Como vemos la sintaxis es casi idéntica a la del CREATE USER, con las mismas opciones, lo que pasa es que estaríamos modificando los valores de dichas opciones para un usuario existente.
  • Lo único diferente es la opción IF EXISTS. Dicha opción sirve para que el gestor Mysql realice la orden SQL 'ALTER USER' sólo en el caso de que el usuario exista. Si no existe no hace nada.
Si no ponemos esta opción a la hora de modificar un usuario, tendríamos un mensaje de error.
Si estamos ejecutando un conjunto de instrucciones Mysql, pararía al llegar a esta instrucción.


  • Un ejemplo:
1 ALTER USER IF EXISTS 'angel'@'%' IDENTIFIED BY 'nueva_password';


  • En el caso del password también disponemos de la orden SET PASSWORD, pero que está deprecated desde la versión 5.7.6 de Mysql.
  • Un ejemplo:
1 SET PASSWORD FOR 'angel'@'%' = PASSWORD('nuevaPASSWORD');



  • Para modificar el nombre del usuario debemos hacer uso de la orden SQL: RENAME USER.
Mysql usuarios 46.jpg


  • Un ejemplo:
1 RENAME USER 'angel'@'%' to 'angel2'@'localhost';



Borrando usuarios

  • La orden SQL que permite dar de baja a usuarios es: DROP USER.


Mysql usuarios 23.jpg
Imagen obtenida de este enlace
  • Un ejemplo:
1 DROP USER IF EXISTS 'angel'@'%';




WorkBench

  • Podemos dar de alta, modificar o dar de baja a los usuarios desde la herramienta cliente MysqlWorkBench.


  • Las operaciones de baja y modificación son bastantes obvias de ver.
  • Para modificar sólo debemos de seleccionar el usuario, hacer los cambios que queramos, y pulsar el botón Apply.
  • Para borrar un usuario sólo debemos de seleccionar el usuario y pulsar el botón Delete.




PhpMyAdmin

Podemos dar de alta, modificar o dar de baja a los usuarios desde la herramienta cliente web PhpMyadmin.


  • Para dar de alta a un nuevo usuario:



  • Para eliminar uno o más usuarios debemos escogerlos pulsando en el checkbox al lado de su nombre y pulsar el botón Continuar.
En el caso de que tuvieran bases de datos con el mismo nombre que el nombre del usuario, podríamos eliminarlas si seleccionamos el checkbox 'Eliminar las bases de datos que tienen....'
Mysql usuarios 40.jpg



  • Para modificar un usuario debemos de escoger la opción Editar Privilegios:


Ejercicios propuestos

SQL

  • Modifica la variable del sistema para hacer que todos los usuarios creados tengan que cambiar el password cada 30 días.
Crea un nuevo usuario.
Muestra las opciones empleadas para crear dicho usuario.
Deja la variable del sistema a su valor original sin utilizar el número 360.


  • 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. Visualiza el valor de la variable del sistema que afecta al usuario (a nivel de sesión)
  • 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.



PhpMyadmin

  • Crea un usuario de nombre work1 y dale permiso total sobre cualquier base de datos de nombre work1_???????
  • Modifica el usuario creado en el paso anterior y limita el número máximo de conexiones por hora a 10 y que la conexión con el servidor sea segura.
  • Crea un usuario con una base de datos con su mismo nombre y que tenga permiso sobre ella para hacer cualquier operación.
  • Crea un nuevo usuario y elimina el anterior en una misma operación.



MysqlWorkBench

  • Crea un usuario de nombre work2 que se pueda conectar desde cualquier red con ip 10.1.1.X.
  • Modifica el usuario anterior e impide que pueda hacer más de 10 operaciones de modificación a la hora.
  • Modifica el usuario anterior y oblígalo a que se conecte con conexiones seguras. ¿ Como comprobarías desde workbench que la conexión es segura ?
  • Crea una conexión desde tu equipo real al virtualizado utilizando un túnel ssh, haciendo que en tu equipo (el real) una conexión al puerto 4000 se redirija al puerto 3306 del servidor mysql.
Crea un usuario en Mysql que tenga permiso de acceso. Desde qué ip ? (la pregunta tiene trampa)
Crea una conexión en MysqlWorkbench conectándote con este usuario.



-- Ángel D. Fernández González -- (2017).