Mod BD UD8 ADMINIST

Introducción

• En esta unidad vamos a ver diferentes actividades orientadas a la administración y mejora de acceso a los datos en una base de datos relacional empleando Mysql.

Veremos:

• El uso de índices para mejorar la velocidad de acceso a datos.
• Creación de eventos: Los eventos van a permitir ejecutar código SQL y que este se ejecute cada cierto tiempo o en una fecha-hora determinada.
• Creación de usuarios y privilegios: Durante todo este curso hemos realizado todas las operaciones siendo root, un usuario creado por defecto en Mysql y que tiene todos los permisos. Veremos que esto no es lo adecuado y que debemos de limitar el acceso a los recursos en base a los privilegios que tenga cada usuario.
• Copias de seguridad: Aprenderemos cómo crear copias de seguridad de nuestra base de datos y como recuperarla en caso de catástrofe.
• Importación / Exportación: También relacionado con el punto anterior, veremos como importar y exportar nuestra base de datos a diferentes formatos.

Base de datos employees

• Para aprender todo lo anterior, vamos a abandonar la base de datos con la que trabajamos en la UD7, y haremos uso de una base de datos de prueba que suministra Mysql de nombre employees.

Dicha base de datos no es muy compleja (sólo tiene 6 tablas) pero incorpora millones de registros que nos va a permitir realizar pruebas de 'optimización' de acceso a la base de datos.

Vamos a instalar la base de datos employees.

Los pasos para instalarla los tenéis en este enlace.

Básicamente tenemos que:

• Descargar de github la base de datos. Desde Linux, en una consola podemos escribir la orden: wget https://github.com/datacharmer/test_db/archive/master.zip
• Descomprimimos el archivo descargado desde consola con la orden: unzip master.zip
• Dentro del directorio test-db-master/ se encuentra el archivo employees.sql que es el que crea todas las tablas y añade los datos a cada una de ellas. Para ejecutarlo, ejecutaremos desde consola la orden: sudo mysql -uroot -p < employees.sql (suponemos que root tiene password). Una vez hecho esto ya tenemos la base de datos de nombre employees creada.

Indices

Nota aclaratoria: Esta sección está pensada para alumnos que desarrollen aplicaciones y necesiten optimizar sus consultas.

En este enlace y este otro, que forman parte del curso Mysql 5.7 de esta Wiki introduce aspectos relacionados con la administración que no serán comentados en esta sección.

• La función principal que tiene un índice es la de acelerar las consultas SELECT que se hagan sobre una base de datos.

En Mysql disponemos del registro de consultas lentas que podemos activar para determinar cuales son las consultas que más ralentizan el Mysql, analizarlas y ver si podemos mejorarlas cambiándolas o creando índices.

• Un índice es una estructura de datos en la cual los valores guardados son los datos de una o más columnas de una tabla de forma ordenada. Cada entrada del índice apuntará a la ubicación del registro en la tabla.

De esta forma, la búsqueda de registros por un campo indexado será mucho más rápida que si no lo está.

• Como en todo, el uso de registros lleva aparejado ciertos problemas (sino fuera así podríamos crear índices para todas las columnas de una tabla):

• Es una estructura de datos que ocupa espacio a mayores del espacio de la propia tabla.
• En caso de operaciones de inserción, borrado o modificación el índice se debe rehacer con la consiguiente carga de trabajo para el gestor.

IMPORTANTE:

• Toda clave primaria debe tener un índice asociado (de hecho se crea automáticamente).

Toda clave foránea debe de tener asociado un índice (si lo hacemos gráficamente ya crea el índice, pero si somos nosotros los que ejecutamos la orden CREATE TABLE tendremos que crear un índice por cada columna que sea clave foránea).

• Un ejemplo obtenido de la wiki:

En este ejemplo, la clave primaria tiene asociado un índice de tipo BTREE, en el que las claves están ordenadas, estando al lado izquierdo las menores y al lado derecho las mayores con respecto al valor referenciado de un nodo dado.

• ¿ Cuando voy a hacer uso de índices ?

• En cláusulas WHERE
• En cláusulas GROUP BY
• En cláusulas ORDER BY

Si tengo una orden SQL como:

SELECT nombre FROM ALUMNOS

WHERE edad between 10 and 20

Tardará menos en ejecutarse si tengo un índice asociado a la edad que si no lo tengo ya que no tendrá que recorrer todas las filas comprobando la cláusula Where.

Crear índices

• Podemos crear un índice de tres formas:

• Con la orden SQL CREATE INDEX
• Con la orden SQL ALTER TABLE y la opción ADD INDEX.
• Con la orden SQL CREATE TABLE y la opción INDEX | KEY (se puede emplear una u otra).

• Veamos un ejemplo sobre una supuesta tabla ALUMNOS sobre la que queramos indexar su nombre:

CREATE INDEX idx_Alumnos_nombre ON ALUMNOS (nombre)

Siendo ALUMNOS el nombre de la tabla y nombre la columna a indexar.

ALTER TABLE ALUMNOS ADD INDEX idx_Alumnos_nombre (nombre)

Siendo Alumnos el nombre de la tabla y nombre la columna a indexar.

• Algunos de los modificadores que podemos añadir a la creación de un índice:

• UNIQUE: índice formado por campos cuyo valor no puede repetirse.
• PRIMARY: Es el índice que conforma la clave primaria de una tabla. Los valores no pueden repetirse. Es recomendable que sea numérica y en caso de no existir, se debe crear una columna de tipo 'autonumérico'.
• full-text: Es un tipo de índice 'especial' que se aplica a campos de texto de gran tamaño, como el tipo TEXT y sus variantes, aunque también puede realizarse sobre char y varchar. Al crear un índice de este tipo, cada una de las palabras del texto es 'indexada' para poder hacer búsquedas en las que se busca sólo alguna de las palabras del mismo. Esto se realiza haciendo uso de la orden SQL MATCH(). A diferencia de LIKE, este comando busca por palabras y permite buscar en varias columnas, indicar si deben aparecer ciertas palabras en la columna o que no aparezcan ciertas otras,...
• SPATIAL: Son índices usados para tipos de datos espaciales como LINE o CURVE.

• Indicar que cuando creamos una tabla, también podemos 'crear índices' de la forma:

CREATE TABLE `dept_emp` (
  `emp_no` int(11) NOT NULL,
  `dept_no` char(4) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`dept_no`),
  KEY `idx_dept_no` (`dept_no`),
  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
) ENGINE=InnoDB;

Se puede poner KEY o INDEX.

En el ejemplo crea un índice sobre la columna dept_no de nombre idx_dept_no.

Fijarse que no crea un índice para emp_no a pesar de ser clave foránea. Esto es así ya que al formar parte de la clave primaria y estar en primer lugar, Mysql ya crea un índice para dicha columna.

Modificar índices

• No es posible.

Podemos utilizar la orden SQL ALTER TABLE con la opción ALTER INDEX para hacer el índice visible o invisible.

Esto hará que el optimizador de consultas no lo use (invisible) o lo tenga en cuenta (visible).

Sólo se puede utilizar en NDB CLUSTER.

• Un ejemplo:

ALTER TABLE Alumnos ALTER INDEX idx_Alumnos_nombre INVISBLE

Siendo Alumnos el nombre de la tabla.

Borrar índices

• Podemos usar la orden SQL DROP INDEX.
• Un ejemplo:

DROP INDEX idx_Alumnos_nombre ON Alumnos

Siendo Alumnos el nombre de la tabla.

Tipos de índices

Parciales

• Como comentamos en la introducción, los índices van a ocupar espacio en disco.

Una columna de 200 caracteres de ancho (200 bytes) y con 5 millones de registros, si hacemos un índice sobre ella, ocupará 1.000.000.000 de bytes = 953MB

• Para evitarlo podemos crear un índice parcial, es decir, un índice sobre una parte del tamaño de la columna.

CREATE INDEX idx_Alumnos_direccion ON Alumnos (direccion(100))

En este caso estamos creando un índice de la mitad del tamaño de la columna (100 caracteres) por lo tanto ocupará 953/2 = 476MB

• Se pueden crear sobre columnas de tipo CHAR, VARCHAR y TEXT.

En el caso de TEXT es obligatorio indicarlo.

También con los tipos de datos BINARY, VARBINARY y BLOG, pero en este caso indicamos el número de bytes. También es obligatorio.

• Nota: Podéis consultar esta sección del curso Mysql 5.7 donde se explica como determinar el tamaño de un índice parcial.

Multicolumna

• Este tipo de índices abarca más de una columna.

Un caso típico podría ser el de columnas nombre y apellidos.

Si quiero crear un índice por apellidos y dentro de apellidos que ordene por nombre, crearía un único índice con las dos columnas, de la forma:

CREATE INDEX idx_Alumnos_nombre_apellidos ON Alumnos (apellidos(100),nombre)

Nota Fijarse que el orden de las columnas en el índice es importante. No es lo mismo crear un índice de 'nombre-apellidos' que de 'apellidos-nombre'.

• Si se realizan consultas en los que se busca por varios campos, se debe de crear un índice multicolumna mejor que varios índices parciales

Veremos en el siguiente punto de la Wiki cual es el mejor orden

Secundarios o clusters

• Cuando creamos una clave primaria, realmente estamos creando un índice sobre las columnas que conforman la clave primaria, un tipo de índice que impide que haya dos valores iguales.

Al crearlo, Mysql ordena físicamente los registros para que coincidan con el índice, de tal forma que índice y registro están 'unidos'. El registro se guarda junto a su clave primaria.

Así, si tengo creado como índice de clave primaria una columna id, físicamente los registros estarán:

ID-------Nombre
1--------Angel
2--------Maria
3--------Juan
4--------Ana

Este tipo de índice se denomina cluster.

• En una tabla, como vimos, podemos crear otros índices. Cuando usamos INNODB como motor de almacenamiento, éstos guardan el valor de la clave primaria para buscar el registro, a parte del valor del índice creado. Este tipo de índices se denominan índices secundarios o no clusterizados.

En el ejemplo anterior, si creamos un índice sobre la columna Nombre tendremos:

Nombre-------ID
Ana----------4
Angel--------1
Juan---------3
Maria--------2

Para recuperar un registro cuando usamos un índice secundario, necesitamos dos operaciones de disco, una para buscar el valor en la tabla del índice y otra para buscar el registro a partir de la clave primaria.

En el motor MyISAM pasa lo mismo, pero la diferencia es que los índices se guardan en archivos separados y el valor asociado al índice no es la clave primaria, sino el valor desplazamiento (offset) del registro en la tabla.

• Visto lo anterior es importante elegir bien la clave primaria:

• Debemos elegir un campo que no implique modificaciones, ya que será necesario una reordenación de los registro.
• Además es mejor que tenga un tamaño pequeño, ya que, si por ejemplo es una cadena de 100 caracteres, estamos guardando en todos los índices secundarios 100 caracteres en cada entrada del índice.

Optimizando los índices

• Los índices no pueden ser actualizados y es necesario borrarlos y volverlos a crear.

Además, los índices generan ciertas estadísticas que son utilizadas por el optimizador de consultas para determinar cual es la mejor forma de obtener los resultados de una consulta.

• Por estas dos razones, debemos de utilizar uno de estos dos comandos:

• El comando OPTIMIZE TABLE cuando trabajamos con tablas que han sufrido muchas operaciones modificando las columnas asociadas a sus índices (borrados, actualizaciones, inserciones).

Este comando va a desfragmentar la tabla y a actualizar y reordenar sus índices. Sólo se puede aplicar sobre motores INNODB, MYISAM y Archive.

Tendrá el efecto de recuperar el espacio de disco que una tabla ya no usa (después de hacer un borrado de muchas filas).

Recrear los índices y volver a generar la información necesaria para que el optimizador de consultas determine cuando hacer uso del mismo.

• El comando ANALYZE TABLE cuando trabajamos con tablas que han sufrido muchas operaciones modificando las columnas asociadas a sus índices (borrados, actualizaciones, inserciones).

A diferencia del anterior, este comando sólo recrea los índices de la tabla pero no recupera el espacio de disco que ya no utilice.

OPTIMIZE TABLE en el caso del motor INNODB, lo que hace 'realmente' es crear una tabla copia de la tabla a optimizar, borra la original y renombra la nueva, haciendo después un ANALYZE TABLE.

Por lo tanto este comando sirve también para 'reclamar' el espacio que ya no utiliza una tabla (al borrarse registros, el tamaño físico de la tabla se mantiene).

Hay que tener cuidado ya que va a necesitar el doble de tamaño en disco (de lo que ocupa la tabla) para realizar la operación (en el caso de recrear los índices).

La reducción de espacio sólo funcionará si la variable del sistema innodb_file_per_table está a ON (valor por defecto).

Nota:

• Cuando se ejecuta este comando con el motor InnoDB saldrá un aviso con el siguiente texto: Table does not support optimize, doing recreate + analyze instead.

No hacer caso al mismo ya que lo que indica es que el motor InnoDB no va a hacer lo que se hacía con el motor MyISAM para optimizar la tabla.

• Debemos de tener cuidado cuando ejecutamos el comando ya que la tabla queda 'bloqueada' hasta que finalice la optimización.

Por defecto. si está activo el registro binario de log, las operaciones realizadas por el comando anterior son guardadas en dicho fichero. Si queremos que esto no ocurra, tendremos que llamar al comando con la opción 'LOCAL' o 'NO_WRITE_TO_BINLOG'. Esto es así para que en caso de un entorno de maestro-esclavos, los cambios no sean replicados a los esclavos.

• Por tanto, tendremos que ejecutar operaciones de optimización sobre tablas que se modifiquen habitualmente (operaciones de borrado, inserción y modificación sobre los datos asociados al índice).

• Veamos un ejemplo:

OPTIMIZE TABLE ALUMNOS;

ANALYZE TABLE ALUMNOS;

• Según esta documentación, en el caso de tener índices secundarios es mejor borrarlos, optimizar la tabla y volver a crearlos.

Optimizando los tipos de datos

• Siempre es preferible valores numéricos para identificar las filas que valores de cadena.

• Si la información a guardar puede ser guardada en formato numérico o cadena, es preferible que sea numérica.

• Siempre que se comparan columnas es mejor que dichas columnas tengan la misma codificación (collation) y juego de caracteres (charset).

• Con columnas inferiores en tamaño a 8KB es mejor hacer uso del tipo de datos varchar que blob.

• Si habitualmente en una tabla se disponen de columnas a las cuales raramente se va a acceder, es mejor mover dichas columnas a otra tabla y relacionarlas con una clave primaria-foránea con la tabla original.

Optimizando las consultas

Caso 1: Parte Where con una única condición

• La sentencia SQL es del tipo:

1 SELECT columnas
2 FROM Tabla
3 WHERE col=valor

Para este tipo de sentencias es necesario crear un índice sobre la columna a la que aplicamos la condición.

Veamos un ejemplo:

1 SELECT *
2 FROM employees.employees
3 where hire_date='1986-06-26';

Si ejecutamos esta sentencia dos veces, una sin el índice creado y la siguiente vez con un índice creado sobre la columna hire_date nos dará este resultado:

Podemos comprobar como la velocidad (columna Duration) es mucho menor en el segundo caso gracias al uso del índice.

• Podemos ver en este enlace un ejemplo en el que el uso de un índice aplicado a una columna es peor que no tenerlo (de hecho Mysql tiene un optimizador y sabe que no debe hacer uso de él). Fijarse que la sentencia count es sobre la columna 'name' que no forma parte de la estructura del índice.

Caso 2: Parte Where con una varias condiciones de tipo AND

Nota: Partimos de un ejemplo de dos columnas, aplicándose los mismos conceptos a 3 o más columnas.

• La sentencia SQL es del tipo:

1 SELECT columnas
2 FROM Tabla
3 WHERE col1=valor1 and col2=valor2

En este caso se debe de 'cubrir' todas las columnas de la parte Where.

Aquí podemos tener dos posibilidades:

• índice sobre las columnas col1,col2
• índice sobre las columnas col2,col1

Fijarse que el orden es importante, no es lo mismo ordenar por apellidos y dentro de cada apellido por el nombre, que ordenar por el nombre y por cada nombre, ordenar todos los apellidos.

¿ Cual debemos elegir ?

Como siempre vamos a dar un consejo 'general' pero que dependiendo de los datos puede que no sea la mejor opción.

Si sobre la base de datos tenemos, a parte de la consulta anterior, consultas del tipo:

1 SELECT columnas
2 FROM Tabla
3 WHERE col1=valor1

Debemos crear el índice con las columnas col1,col2.

Este índice cubre las dos consultas anteriores y otras, siempre que se busque por col1:

 1 SELECT columnas
 2 FROM Tabla
 3 WHERE col1=valor1
 4 
 5 SELECT columnas
 6 FROM Tabla
 7 WHERE col1=valor1 and col2=valor2
 8 
 9 SELECT columnas
10 FROM Tabla
11 WHERE col2=valor2 and col1=valor1
12 
13 SELECT columnas
14 FROM Tabla
15 WHERE col1=valor1 and (col2=valor2 or col2=valor3)
16 
17 SELECT columnas
18 FROM Tabla
19 WHERE col1=valor1 and col2>=valor2 and col2<=valor3

Si por el contrario, tenemos consultas del tipo:

1 SELECT columnas
2 FROM Tabla
3 WHERE col2=valor2
4 
5 SELECT columnas
6 FROM Tabla
7 WHERE col1=valor1 and col2=valor2

Escogeremos el índice: col2,col1.

Nota::

• Siempre que tengamos una consulta SQL en la que la parte where afecte a más de una columna, será mejor hacer un único índice sobre todas las columnas del where que el realizar múltiples índices sobre cada columna individual.
• Como dije al principio, esta regla puede no ser correcta dependiendo de los datos. Si la condición col1='valor1' devuelve casi todos los registros de la tabla, será mejor hacer índice a 'col2,col1' no hacer índice sobre col1 (explicamos a continuación por qué)

• Debemos de tener en cuenta otro factor a la hora de elegir el orden, que es el de la cardinalidad.

Este factor indica cuantos valores diferentes guarda un índice.

La idea es que cuantos más valores diferentes haya, mejor, ya que el número de filas que devolverá el índice debería ser menor, al seleccionar filas en base a algún criterio.

Para saber la cardinalidad de una columna podemos ejecutar la siguiente orden SQL: SELECT COUNT(DISTINCT col).

Nota: Una desventaja que tiene esta opción es que en la recuperación de datos se moverá más por la estructura en árbol del índice si la condición devuelve muchos valores diferentes, mientras que si primero hacemos uso de la columna que tenga más duplicados, podrá devolver más filas con los mismos valores y por tanto no será necesario moverse tanto por la estructura de índice.

Por eso en varios sitios encontraréis que es mejor utilizar en primer lugar aquella columna que tenga más duplicados.

Son dos opciones contradictorias. Como consejo intentaremos escoger aquella opción que nos devuelve el menor número posible de resultados en base a los datos que tengamos de los datos empleados en la consulta.

Si no podemos saber este dato, escogeremos primero aquellas columnas que tengan la cardinalidad más alta.

• Veamos un ejemplo:

1 SELECT *
2 FROM employees.employees
3 where hire_date='1986-06-26' and first_name='Shuzo'

Comprobamos cual de las dos columnas tiene la cardinalidad más alta:

Como vemos la columna 'hire_date' tiene más valores diferentes (cardinalidad más alta).

Por lo tanto el índice que deberíamos crear sería: hire_date,first_name.

Atención: Al crear los índices compuestos desde el MysqlWorkBench (creamos un índice compuesto de (first_name, last_name) y otro de (last_name, first_name) hace invisible uno de ellos para el optimizador. Debemos marcar la opción visible en los dos:

Aún no vimos el comando 'explain' pero si creamos los dos tipos de índices sobre la tabla (hire_date,first_name y first_name,hire_date) podemos comprobar cual escoge Mysql:

Como vemos escoge el formado por las columnas 'hire_date,first_name'.

Pero como estoy indicando desde el principio esto son 'líneas generales' pero que puede que necesiten modificarse dependiendo de los datos.

Por ejemplo, si ejecutamos la sentencia anterior de forma separada por cada columna del where y contamos el número de filas obtenemos:

Como vemos hemos acertado en el sentido que devuelve menos filas el índice 'hire_date' que 'first_name',!!!!CON ESTOS DATOS!!!!'

Por eso Mysql escoge el índice formado por 'hire_date,first_date'

Pero si usamos datos que devuelvan más filas por la condición 'hire_date' que por la condición 'first_name', ¿ qué pasará ?, ¿ qué índice deberíamos escoger ?

Lo veremos más adelante con el comando explain.

Ejercicio Propuesto

En una base de datos se ejecutan este tipo de sentencias:

1 SELECT *
2 FROM employees.employees
3 where first_name='DATO1' and last_name='DATO2'

Analiza las columnas first_name, last_name y determina el orden de las columnas que conforman el índice

Nota: Si los resultados no son los esperados, probar a ejecutar la orden SQL 'Analyze table TABLA' o desde el Mysql WorkBench, seleccionando la tabla y pulsando el botón derecho, escoger la opción 'Table Inspector':

Solución Ejercicio Propuesto

• Si hacemos un count(distinct()) de cada columna podemos comprobar como la que tiene más valores diferentes es 'last_name'.

Por tanto el índice debería ser: last_name,first_name

Recordar: Esto es así porque presuponemos que el número de filas que devolverá el indice aplicando la primera columna será menor al tener menos valores repetidos, pero dependiendo de los datos, esto puede no ser cierto.

1 SELECT *
2 FROM employees.employees
3 where last_name='Rosch' and first_name='Shuzo'

Si averiguamos cuantas filas devuelve cada condición individualmente:

Podemos comprobar como first_name devuelve más filas, por lo tanto es mejor escoger el índice: last_name,first_name como hemos decidido.

Si 'forzamos' a ejecutar la consulta con cada uno de los índices da mejor tiempo el índice 'last_name,first_name):

En definitiva, Mysql intentará escoger el índice con el mayor número de valores diferentes, pero nosotros podemos analizar cuantas filas va a devolver si sabemos cuales van a ser los datos enviados a la consulta en la parte Where y sabiendo si los datos de la tabla van a cambiar mucho o no, podremos escoger otro índice diferente.

Caso 3: Parte Where con una varias condiciones de tipo OR

• Borramos todos los índices creados sobre la tabla 'employees' menos el de clave primaria.

• Con una condición OR no tiene efecto un índice compuesto y tendremos tres posibilidades:

1 SELECT *
2 FROM tabla
3 WHERE col1=dato1 or col2=dato2

• Crear un índice sobre col1
• Crear un índice sobre col2
• Crear un índice sobre col1 y crear otro índice sobre col2

Las dos primeras opciones no tienen sentido, ya que a pesar de tener un índice, Mysql tendría que recorrer toda la tabla para comprobar la condición que no tiene índice asociado.

La tercera opción podrá ser viable ya que Mysql a partir de la versión 5, puede aplicar varios índices haciendo una operación de combinación (en el comando explain aparece 'Using ....'.

Podéis consultar todo lo referente a combinación de índices en este enlace.

Pero al igual que en el Caso 2, tendremos que tener en cuenta la cardinalidad de cada columna.

Si esta es baja, no valdrá la pena crear un índice sobre la columna.

• Veamos un ejemplo:

1 SELECT *
2 FROM employees.employees
3 WHERE first_name='Georgi' or hire_date='1985-11-21'

Creamos un índice sobre la columna 'first_name' y otro sobre la columna 'hire_date'

Recordar que hacer un índice sobre una de las columnas no tiene sentido

Con el comando Explain nos indica que está combinando los dos índices uniendo los resultados.

Al igual que en todos los casos anteriores muchas veces vamos a depender de los datos guardados en las tablas.

En foros diversos establecen que que cuando una de las columnas (o ambas) tiene varios valores posibles es mejor cambiar la consulta y utilizar OR.

Podéis mirar el ejemplo propuesto en este enlace.

La idea es que toda instrucción OR se puede descomponer en dos órdenes SELECT unidas con el comando UNION de la forma:

1 SELECT *
2 FROM employees.employees
3 where first_name='Georgi' or hire_date='1985-11-21'

Es igual a:

1 SELECT *
2 FROM employees.employees
3 WHERE first_name='Georgi'
4 UNION
5 SELECT *
6 FROM employees.employees
7 WHERE hire_date='1985-11-21'

De esta segunda forma puede aplicar el índice individual a cada columna y hacer la unión de los dos resultados.

Pero por pruebas realizadas eso debería de ser así en versiones anterior de Mysql. En la actual y con la combinación de índices, siempre obtengo resultados más rápidos con el índice combinado.

De todas formas tener en cuenta esta posibilidad ya que puede ser que dependiendo de la consulta os convenga utilizar esta opción.

Ejercicio Propuesto

Borra todos los índices de la tabla employees menos el de clave Primaria.

Indica que índice crearías para optimizar este tipo de consultas:

1 SELECT *
2 FROM employees
3 WHERE (first_name like 'A%' and gender='M') or
4       (hire_date between '1993-01-01' and  '1995-01-01')

Solución Ejercicio Propuesto

La consulta está formada por dos partes separadas por el operador OR.

Con este tipo de operador intentaremos que haga uso de un índice combinado.

El primer índice será sobre la columna 'hire_date'

El segundo índice llega hacerlo sobre la columna 'first_name'. Tendríamos que escoger primero la columna que tuviera la cardinalidad más alta (first_name) y después la segunda columna. La columna 'gender' no va a compensar utilizarla ya que los datos están repartidos prácticamente al 50% entre los valores M y F, por lo que el costo de ir a recorrer la segunda parte del índice será mayor que el de ir seleccionando las filas devueltas por la primera columna. La cosa variaría si hubiese muy pocas filas con el valor 'M'. En ese caso podría hasta convenir escoger dicha columna en primer lugar.

• IMPORTANTE: Como comenté antes, esta sección está pensado para alumnos que van a desarrollar aplicaciones y que existen otras personas que se encargan de optimizar la base de datos (los administradores).

Si se quiere profundizar en esta parcela, puedes consultar este enlace de la Wiki.

Eventos

• Los eventos son tareas que se ejecutan en base a un horario definido previamente.

• Antes de la versión 5.7, los eventos eran 'simulados' haciendo uso de la función cron del sistema operativo LINUX o con el programador de tareas de Windows.

• Un evento se identifica por su nombre. Cada evento está asociado a un esquema (base de datos) y se ejecuta en un intervalo de tiempo determinado.

Un evento ejecuta un conjunto de instrucciones SQL formando un bloque dentro de un BEGIN - END.

• Los eventos se pueden programar para que se ejecuten una única vez o periódicamente.

• Para poder crear eventos programados y que estos se ejecuten es necesario hacer uso de la variable global event_scheduler.

• Si vale OFF: El thread que se encarga de ejecutar los eventos está desactivado y por lo tanto no se ejecutará ningún evento. La tarea 'event_sheduler' no se muestra con la orden SHOW PROCESSLIST. Es el valor por defecto. En este estado, para activar el planificador de eventos debemos darle el valor ON a esta variable.
• Si vale ON: El 'Event Scheduler' (planificador de eventos) es iniciado. En este caso aparecerá como un proceso más al ejecutar la orden SQL: SHOW PROCESSLIST. Para desactivarlo tendremos que cambiar su valor a OFF. Es el valor por defecto.
• Si vale DISABLED: En este caso el planificador de eventos no podría ser activado dinámicamente cambiando el valor de la variable global a ON.

• Una de las formas que disponemos de cambiar el valor de una variable global (siempre que esta sea dinámica, es decir, que se pueda cambiar estando el gestor en marcha):

SET GLOBAL event_scheduler = ON;

Nota: Podéis consultar en este enlace de la Wiki como diferentes formas de cambiar el valor de una variable global.

Creación de eventos

• La orden SQL para crear eventos es CREATE EVENT.
• Se necesita el privilegio EVENT sobre la base de datos.

Imagen obtenida de https://dev.mysql.com

• Entre las diferentes opciones tenemos:

• ON SCHEDULE: Indicamos el momento en que se va a ejecutar el evento y su frecuencia (una vez, una vez cada cierto tiempo, entre dos intervalos de tiempo,....)
• DEFINER: Al igual que en el caso de las vistas y procedimientos, indicamos el usuario con el que se va a ejecutar el evento y por lo tanto se ejecutará con los permisos que tenga asociados dicho usuario.
• event_body: Es el contenido del evento.
• ON COMPLETION PRESERVE indica que el evento, cuando se termina de ejecutar (y si ya no va a ejecutarse posteriormente) no se elimine (el comportamiento por defecto es el contrario y se indica con la opción ON COMPLETION NOT PRESERVE).
• DISABLE indica que el evento está creado pero no se va a ejecutar. Para activarlo (normalmente con la orden SQL ALTER EVENT) pondremos ENABLE. La opción DISABLE ON SLAVE se utiliza en entornos de replicación con Mysql instalado en varios equipos. Lo que indicaría esta opción es que el evento sólo debe ejecutarse en el MASTER y no debe replicarse a los SLAVES.

• Al igual que los procedimientos, podemos tener varias órdenes en el cuerpo del evento.

En ese caso deberemos poner después del DO BEGIN Código del evento END

Deberemos establecer un delimitador diferente antes del CREATE para separar el fin del CREATE con el fin de cada instrucción SQL que conforma el evento (al igual que pasaba en los procedimientos).

• Una función que nos puede ser útil

• CURRENT_TIMESTAMP que nos devuelve el día y hora actual del sistema y que se suele utilizar para establecer los intervalos de ejecución del evento de la forma: EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK (es un ejemplo)
• Funciones para el manejo de tiempos y fechas.

• Veamos un ejemplo sobre la base de datos employees (recordar cambiar el valor de la variable del sistema event_scheduler).

Vamos a crear un evento que se ejecute el día uno de cada mes y que obtenga los nombres de los empleados que hayan sido contratados durante el mes anterior.

Dichos nombres de guardarán en una tabla de nombre empleados_mes creada previamente.

delimiter \\

CREATE EVENT empleados_del_mes
    ON SCHEDULE 
        EVERY 1 MONTH
    COMMENT 'Guarda los empleados contratados en el mes anterior'
    DO
    BEGIN

        INSERT INTO empleados_mes (nombre)
        SELECT concat(first_name,' ',last_name) 
        FROM employees.employees
        WHERE hire_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH);
    END\\
    
delimiter ;

Veremos después consultando los datos del evento, que éste no comenzará hasta el mes que viene.

Modificación de eventos

• La orden SQL que modifica un evento es ALTER EVENT.
• Se necesita el privilegio EVENT sobre la base de datos.

• Veamos un ejemplo.

Sobre el evento creado en el paso anterior, haz una modificación para que comience en el mes actual:

ALTER EVENT empleados_del_mes
ON SCHEDULE 
EVERY 1 MONTH STARTS NOW();

Borrado de eventos

• La orden SQL que modifica un evento es DROP EVENT.
• Se necesita el privilegio EVENT sobre la base de datos.

• Si intentamos borrar un evento que no existe sin utilizar la opción DROP EVENT IF EXISTS el gestor Mysql lanzará una excepción con el mensaje: ERROR 1517 (HY000): Unknown event 'event_name'

• Veamos un ejemplo:

DROP EVENT [IF EXISTS] nombre_del_evento;

Consulta de eventos

• La orden SQL que permite obtener información de un evento es SHOW EVENTS.

Se necesita el privilegio EVENT sobre la base de datos.

Para ver los eventos asociados a una base de datos específica podemos hacer uso de la palabra FROM de la forma: SHOW EVENTS FROM nombre_bd;

También podemos 'buscar' como ya hicimos anteriormente:

• Si queremos buscar por los eventos por su nombre podemos hacer uso de la cláusula LIKE que busca por patrón (símbolo comodín el %) nombres de eventos.
• Si queremos buscar dentro de los resultados aquellas filas que cumplan alguna condición asociada al valor de alguna columna debemos hacer uso la cláusula WHERE. Por ejemplo: SHOW EVENTS WHERE Definer like 'root%'

Veamos un ejemplo:

SHOW EVENTS;

La orden anterior muestra información sobre quien creó el evento, cuando comienza a ejecutarse y su intervalo de ejecución, cuando finaliza, si está habilitado y sobre qué base de datos actúa.

• Si queremos obtener el código del evento, debemos de ejecutar la orden SQL SHOW CREATE EVENT.

También podemos consultar directamente el diccionario de datos, concretamente la tabla EVENTS de la base de datos INFORMATION_SCHEMA.

Veamos un ejemplo:

SHOW CREATE EVENT empleados_del_mes;

• IMPORTANTE: Para obtener información sobre cómo fue la ejecución de un evento debemos consultar al fichero de log (por defecto es /var/log/mysql/error.log):

cat /var/log/mysql/error.log | grep Event

Ejercicios propuestos

• Nota: Para crear una tabla podemos hacer uso de la orden SQL: CREATE TABLE nombre_table SELECT col1,col2,... FROM tabla (o de una tabla temporal con la orden CREATE TEMPORARY TABLE)

Lógicamente si la tabla existe dará un error, por lo que deberíamos borrarla previamente en caso de que exista: DROP TABLE IF EXISTS nombre_tabla;

Si quisiéramos añadir filas una vez creada, en vez de hacer el drop en caso de que exista, tendríamos que hacer un insert multifila:

• Comprobamos si existe: SELECT count(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = 'BASE_DE_DATOS') AND (TABLE_NAME = 'TABLA_A_BUSCAR');
• Insert multifila: INSERT INTO tabla_destino (col_tabla_destino) SELECT col FROM tabla_origen;

• Queremos registrar, cada 1 minuto en el día de hoy y durante unos 10 minutos desde el momento de creación del evento, qué usuarios están accediendo a la base de datos employees. Estos usuarios (sus nombres y equipos) deberán quedar registrados en una tabla de nombre 'historico_usuarios_hora' en la que se guardará el nombre del usuario así como el día-hora-minuto en el que se produjo el registro de actividad.

Crea dos usuarios y conéctate a la base de de datos. Después haz que uno de ellos se desconecte la base de datos.

Modifica el evento y haz que el evento no desaparezca cuando termine el intervalo de ejecución.

Modifica el evento para que se vuelva a ejecutar en el minuto siguiente al actual y comprueba que registra nuevamente a uno de los usuarios anteriores (que se vuelva a conectar)

Pista: La tabla del diccionario de datos donde se guarda esta información es information_schema.processlist y disponéis de un ejemplo de uso en este enlace.

• Haz que el día 30 de Abril del 2018 a las 23:15 se guarde una copia de los datos de la tabla departments en una tabla creada por ti previamente de nombre dept_copia_temp

• Crea una table de nombre empleados_numero_mensual que guarde el número de empleados que tiene la empresa. Haz que se ejecute una vez 4 días después de que se cree el evento.

Ejecuta la orden que muestre el momento en que se va a ejecutar.

Modifica el evento para que se ejecute desde el día de hoy hasta dentro de un año y que lo haga cada mes.

Ejecuta la orden que muestre el momento en que se va a ejecutar.

• Haz que el día 1 de Enero de cada año se actualicen los salarios de los manager de cada departamento un 10% (trabajan mucho :) que estén trabajando actualmente en la empresa). Esta actualización se debe producir durante 5 años a partir del actual.

Cambia la hora del sistema para comprobar que se ejecuta el evento.

• Muestra todos los eventos creados de la base de datos employees.

Muestra todos los eventos creados de la base de datos employees y que fueran creados por el usuario root.

• Borra uno de los eventos, asegurándote de que exista.

• Crea un evento que se ejecute cada día a las 22:00 y que pase a un archivo .csv el contenido de la tabla departments, con el nombre departments_datos.csv

Para hacerlo puedes hacer uso de una tabla con motor CSV o bien haciendo uso de la orden SQL SELECT INTO

En el enlace anterior podemos ver un ejemplo de uso:

SELECT a,b,a+b INTO OUTFILE ' /var/lib/mysql-files/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

Nota: En la versión actual el único directorio por defecto al que se puede escribir con la orden anterior es /var/lib/mysql-files/

Si se quiere escribir en otro lugar del disco habría que modificar la variable del sistema secure_file_priv.

Usuarios y privilegios

• Nota Informativa: Recordar que este curso está orientado a alumnos de programación. Si se quiere tener una idea más profunda sobre usuarios y permisos pero orientado a la administración, visitar esta sección del curso de Mysql 5.7 de esta Wiki.

Niveles de seguridad

• Parece lógico que si queremos acceder a información guardada en una base de datos, tengamos establecido algún sistema de permisos que especifique que operaciones van a poderse realizar con los datos guardados.

Además, el servidor Mysql también va a permitir gestionar ciertos permisos administrativos sobre el propio gestor.

• Las operaciones que vamos a poder realizar las podemos agrupar en las siguientes categorías:

• DATOS (conformaría el Lenguaje de Manipulación de Datos => DML (Data Manipulation Language), componente de un Sistema de Gestión de Base de Datos):

• Manipulación de datos: INSERT, UPDATE, DELETE aplicadas a tablas/vistas dentro de una base de datos.
• Consulta de datos: SELECT aplicada a tablas/vistas dentro de una base de datos.
• Vistas: CREATE VIEW, ALTER VIEW, DROP VIEW.
• Resto de órdenes DML.

• GESTIÓN (conformaría el Lenguaje de Definición de Datos => DDL (Data Definition Language), componente de un Sistema de Gestión de Base de Datos):

• Base de datos: CREATE DATABASE, ALTER DATABASE, DROP DATABASE
• Tablas: CREATE TABLE, ALTER TABLE, DROP TABLE
• Procedimientos almacenados: CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, EXECUTE
• Triggers: CREATE TRIGGER, ALTER TRIGGER, DROP TRIGGER
• Resto de órdenes DDL.

• SEGURIDAD (conformaría un sub-lenguaje del Lenguaje de Definición de Datos, denominado Lenguaje de Control de Datos (Data Control Language => DCL)):

• Usuarios: CREATE USER, ALTER USER, DROP USER
• Gestión de permisos: GRANT, REVOKE
• Resto de órdenes DCL.

• Mysql incorpora un sistema de seguridad en el que con las órdenes definidas en el DCL, especifica qué permisos DDL y DML va a tener un usuario.

• Para ello debemos pasar por diferentes niveles de seguridad.

Primer Nivel: Mysql

• El primer nivel lo compone el propio Mysql.

Debemos de determinar qué usuarios tienen permiso de conexión al servidor Mysql.

• La información que va a guardar Mysql para determinar si un usuario tiene permiso de conexión es:

• Nombre del usuario
• Password del usuario
• Dirección IP/Nombre del host desde el que el usuario está intentando conectarse al servidor.

• Toda esa información está guardada en la tabla user de la base de datos mysql.

• Si ejecutamos la orden SQL: desc mysql.user podemos observar lo siguiente:

• Host: Indica la dirección ip/nombre del host desde el cual el usuario va a poder conectarse.

Recordar que por defecto, Mysql no permite conexiones remotas utilizando TCP/IP, por lo que tendríais que comentar la bind-address variable del sistema bind-address] en su archivo de configuración.

Los valores más comunes para este campo suelen ser:

• localhost/127.0.0.1: esto implica que el usuario sólo puede conectarse vía ssh o estando físicamente en la misma máquina donde se encuentra el servidor Mysql.
•  %: Cualquier dirección IP.
• A.B.C.D: Una dirección ip concreta. Por ejemplo: 192.168.1.10
• Es posible utilizar el símbolo '%' como cualquier parte de una dirección ip de la forma: 192.168.1.%
• Es posible utilizar el símbolo '%' como cualquier parte de un nombre de host de la forma: '%.mysql.com'

Más información en este enlace.

• User: El nombre del usuario que tiene acceso al servidor.
• Password: El password del usuario. Está encriptada.

• Importante: Recordar que los usuarios del servidor Mysql no tienen nada que ver con los usuarios creados a nivel del sistema operativo.

• Una vez creado un usuario, éste puede conectarse al Mysql, pero no puede realizar ninguna acción ya que no tiene establecido ningún tipo de permiso.

Imagen obtenida de este enlace

• Podemos observar como hay permisos que se sitúan a este nivel (al nivel de Mysql) y no continúan por el resto de niveles de seguridad.

Son permisos que afectan al servidor Mysql y no a sus objetos (bases de datos, tablas,...).

Por ejemplo, el permiso para poder parar el servicio de Mysql está en la columna shutdown_priv. Si un usuario tiene este permiso el gestor Mysql ya no necesita consultar ninguna otra tabla.

Segundo Nivel: Bases de datos

• Una vez creado el usuario debemos otorgarle permisos.

Los más comunes son los que le van a permitir realizar operaciones sobre una base de datos.

• Cuando creamos un usuario podemos otorgarle permisos a nivel de 'todas las bases de datos' o a nivel de 'una base de datos en particular'.

• Los permisos globales (los que se aplican a todas las bases de datos) están definidos en la propia tabla 'user' de la base de datos 'mysql'.

En la imagen podemos observar las columnas de la tabla user: 'select_priv','update_priv','delete_priv','insert_priv'.

Estas columnas son de tipo ENUM y sólo pueden tener un valor 'Y' o 'N' y por defecto, el valor predeterminado es 'NO'. Un usuario que tenga puesto a 'YES' el valor de estas columnas, tendría como consecuencia que podría realizar operaciones SQL de SELECT, UPDATE, DELETE e INSERT sobre todas las tablas de todas las bases de datos de Mysql.

• Los permisos locales a nivel de una base de datos en particular se encuentran guardados en la tabla db de la base de datos mysql.

En el caso de que un usuario intentara realizar alguna operación en una base de datos, y no tuviera permiso 'global' para realizarla, iría a esta tabla a buscar si tiene otorgado el permiso.

Si vemos la imagen podemos comprobar como en esta tabla:

• Las columnas Host,Db, User conforman la clave primaria de la tabla y por tanto no puede haber dos filas con los mismos valores.

• Host: nombre del host desde está permitido acceder a la base de datos.
• Db: nombre de la base de datos.
• User: nombre del usuario que tiene permiso de acceso a la base de datos.

• Igual que en los permisos a nivel global, un permiso a 'YES' en esta tabla, implicaría que el usuario tendría ese permiso en todas las tablas creadas dentro de la base de datos.

Podemos comprobar en la imagen superior como el usuario 'user1' tiene permiso de selección (select_priv) desde 'localhost' en la base de datos 'employees' y por lo tanto va a poder ejecutar la orden SQL SELECT sobre cualquier tabla de la base de datos.

Imagen obtenida de este enlace

Tercer Nivel: Tablas y columnas

• A nivel de tabla, un usuario que tenga permiso a este nivel lo tendrá sobre toda la tabla.

Es decir, si tengo permiso de selección, podré seleccionar todas las columnas de la tabla.

Los permisos en este nivel se guardan en la tabla tables_priv de la base de datos mysql.

Como observamos, las columnas de la tabla tienen:

• Clave primaria formada por las columnas (User,Db,Host y Table_name).
• Grantor: Indica el usuario que otorgó este permiso.
• TimeStamp: El momento en que se otorgó el permiso.
• TablePriv: Tabla sobre la que se va a aplicar el permiso.

Debemos de tener en cuenta que si otorgamos permiso a nivel de tabla, aquí aparecerá el tipo de permiso (por ejemplo, delete,insert).

Es de tipo SET y a diferencia de los de tipo 'ENUM' pueden tener varios valores, dentro de las opciones posibles,

• Column_priv: En caso de que tengamos un permiso a nivel de tabla, esta columna estará en blanco. Si tenemos un permiso a nivel de columna, esta columna tendré el tipo de permiso (por ejemplo, update) y la columna anterior (TablePriv) estará en blanco (podría tener un permiso diferente al de update).

Es de tipo SET y a diferencia de los de tipo 'ENUM' pueden tener varios valores, dentro de las opciones posibles,

Imagen obtenida deeste enlace

• El listado de los posibles permisos que podemos otorgar a nivel de tabla/columna se muestran en la siguiente imagen (también están los permisos a nivel de proc. y funciones que se comenta en el siguiente punto).

Imagen obtenida de este enlace

Procedimientos y funciones

• Son permisos que afectan a los procedimientos y funciones que podamos tener creados en Mysql.

Uno de los permisos que vamos a tener es el de ejecución (EXECUTE) para que un usuario pueda llamar al procedimiento/función.

• Las columnas TimeStamp y Grantor no se utilizan.

Imagen obtenida de este enlace

• Los permisos que podemos otorgar a este nivel son los indicados en la siguiente figura en la última fila (proc_priv):

Como podemos ver, los permisos que podemos aplicar a nivel de un procedimiento/función concreto son los de poder ejecutarlo (EXECUTE), poder modificarlo o borrarlo (ALTER ROUTINE) y que tengamos el permiso para poder otorgar los dos permisos anteriores a otros usuarios (GRANT). Estos permisos se registran en la tabla mysql.provs_priv.

Disponemos de los mismos permisos en los otros niveles vistos anteriormente, a nivel general (mysql.user) y a nivel de base de datos concreta (mysql.db). En estos niveles, a mayores disponemos del permiso CREATE ROUTINE que permite a un usuario que tenga dicho permiso crear procedimientos/funciones.

Creación de Usuarios

SQL

Añadiendo usuarios

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

Imagen obtenida de este enlace

Su forma más simple:

CREATE USER nombre_usuario IDENTIFIED BY 'password';

nombre_usuario tiene el formato: 'usuario'@'host'

Por ejemplo:

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:

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:

mysql -u anonimo

• Si no indicamos el host, Mysql asignará el patrón '%' al mismo.

Por ejemplo:

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:

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

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

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

Modificando usuarios

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

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:

ALTER USER IF EXISTS 'angel'@'%' IDENTIFIED BY 'nueva_password';

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

• Un ejemplo:

RENAME USER 'angel'@'%' to 'angel2'@'localhost';

Borrando usuarios

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

Imagen obtenida de este enlace

• Un ejemplo:

DROP USER IF EXISTS 'angel'@'%';

MysqlWorkBench

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

• Dando de alta a un nuevo usuario en WorkBench
• 

  Escogiendo la opción Users and privileges podemos ver la lista de usuarios actualmente.

• 

  Pulsamos el botón Add Count para añadir un nuevo usuario.

• 

  Introducimos los datos para el nuevo usuario.

• 

  Si queremos establecer alguna de las limitaciones de los recursos vistas anteriormente, debemos pulsar la pestaña Acount limits. Las siguientes pestañas las veremos en el siguiente punto del manual. Una vez tengamos los datos introducidos debemos de presionar el botón Apply.

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

• Más información en este enlace.

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:

• Dando de alta a un nuevo usuario en PhpMyadmin
• 

  Debemos pulsar la opción superior que viene indicada por: Servidor: Localhost. Al hacerlo aparecerá la opción 'Cuentas de usuario' en la parte superior debajo de la opción pulsada.

• 

  Al pulsar la opción Cuentas de usuario aparecen la lista de usuarios existentes.

• 

  Pulsamos la opción Agregar cuenta de usuario.

• 

  En la sección Información de la cuenta podemos introducir los datos del nombre del usuario, host y password. Podemos generar un password aleatorio pulsando el botón Generar.

• 

  En la sección Base de datos para la cuenta del usuario disponemos de dos opciones. La primera indica si queremos crear (al mismo tiempo que creamos el usuario) una base de datos con el mismo nombre del usuario y que tenga permisos totales sobre la misma. La segunda opción indica que se le otorgarán permisos totales al usuario creado sobre las bases de datos que se llamen usuario_????????'

• 

  En la sección Límite de recursos podemos establecer alguna de las limitaciones de los recursos vistas y en la sección Require SSL podemos indicar si queremos que las conexiones del usuario con el servidor sean seguras utilizando un certificado SSL como vimos anteriormente. El resto de secciones las veremos en el siguiente punto del manual.

• 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....'

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

• Modificando un usuario en PhpMyadmin
• 

  Pulsamos en enlace Editar Privilegios.

• 

  Apareceremos en la sección de permisos Global. Aquí podemos establecer los permisos a nivel global (las veremos en el siguiente punto del manual). En la sección Límite de recursos podemos establecer alguna de las limitaciones de los recursos vistas y en la sección Require SSL podemos indicar si queremos que las conexiones del usuario con el servidor sean seguras utilizando un certificado SSL como vimos anteriormente.

• 

  En la sección Base de datos podemos darle permisos al usuario al nivel de una base de datos (las veremos en el siguiente punto del manual).

• 

  En la sección Cambio de contraseña podremos cambiar la contraseña del usuario elegido.

• 

  En la sección Información del usuario podremos crear un nuevo usuario y al mismo tiempo realizar una serie de operaciones con el usuario que estamos editando (podemos borrarlo, mantenerlo,...).IMPORTANTE: Después de cualquier modificación en cualquiera de las cuatro secciones es necesario pulsar el botón Continuar para que aplique los cambios.

• Más información en este enlace.

Ejercicios propuestos

SQL

Ejercicio 1

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

• Crea dos usuarios utilizando una única orden SQL, de tal forma que las dos cuentas estén inicialmente bloqueadas

• Desbloquea las cuentas anteriores.

• Modifica el password del usuario u3 y desbloquéalo en una única instrucción SQL

• Da de baja al usuario u1.

PhpMyadmin

Ejercicio 2

• Crea un usuario de nombre work1 y dale permiso total sobre cualquier base de datos de nombre work1_???????

• 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

Ejercicio 3

• Crea un usuario de nombre work2 que se pueda conectar desde cualquier red con ip 10.1.1.X.

• Modifica el usuario anterior y bloquea su cuenta.

• Borra el usuario anterior.

Gestión de Permisos

• Las dos órdenes SQL que nos van a permitir establecer y quitar permisos son: GRANT y REVOKE.

• En general podemos afirmar lo siguiente:

• Los cambios en los privilegios de tabla y columna tiene efecto inmediatamente en el usuario aunque ya se encuentre conectado.
• Los cambios en los privilegios a nivel de base de datos no tienen efecto hasta que se ejecute la sentencia SQL: USE nombre_db;

A nivel gráfico si estamos utilizando el MysqlWorkbench, se produce al escoger la base de datos presionando dos veces sobre ella, quedando en negrilla.

• Los cambios en los privilegios globales no tienen efecto hasta que el cliente vuelve a conectarse.

• Los permisos que vamos a otorgar estarán en uno de los niveles de seguridad que vimos en un punto anterior y que por lo tanto se traducirá en la inserción, modificación y borrado de filas en las tablas: user,db,tables_priv, columns_priv y procs_priv dentro de la base de datos mysql.

• Cuando se crea un usuario el único permiso que tiene es el de USAGE que le da derecho a conectarse a Mysql.

• La lista de todos los permisos que se pueden 'otorgar' o 'denegar' a un determinado usuario son los siguientes (están todos los niveles: general, db, table, column):

Información obtenida de este enlace.

GRANT

• Esta es la orden SQL que concede permisos.

• Podemos consultar su sintaxis completa en este enlace.

Imagen obtenida desde https://dev.mysql.com

• Para ver los permisos que tiene otorgado un usuario, debemos de ejecutar la orden SQL: SHOW GRANTS.

Todo usuario tiene permiso para ver sus privilegios.

Si queremos ver los privilegios de otro usuario (mirar la sintaxis de la orden SQL) debemos de tener permiso de SELECT sobre la base de datos mysql.

Permiso para crear usuarios

• Vimos en el punto anterior del manual como podíamos crear usuario con la orden SQL: CREATE USER

• Para que otros usuarios puedan crear usuarios, necesitan tener el permiso: CREATE USER (lo veremos a continuación)

Permisos a nivel global

• Para que otorgar un permiso a este nivel, hay que indicar: ON *.* en la cláusula grant.

• La sintaxis básica para dar permisos a nivel del gestor Mysql, incluyendo los permisos administrativos sería:

GRANT SELECT ON *.* TO 'user1'@'localhost'

Nota: Si el usuario ya está conectado cuando se realiza el cambio de permisos, no serán aplicados. El usuario necesita cerrar la conexión y volver a abrirla.

En este ejemplo, estaríamos dando permiso de selección sobre todas las tablas de todas las bases de datos, al usuario 'user1' conectado desde host 'localhost'.

• Podemos otorgar varios permisos en una única operación, separándolos con comas:

GRANT SELECT,UPDATE ON *.* TO 'user1'@'localhost'

Nota: Si el usuario ya está conectado cuando se realiza el cambio de permisos, no serán aplicados. El usuario necesita cerrar la conexión y volver a abrirla.

• Como ya comentamos en un punto anterior, existen ciertos permisos que permiten administrar el gestor Mysql y que no tienen 'otros niveles' como sucede con el permiso SELECT por ejemplo (podemos tener un select a nivel de base de datos, a nivel de tabla, a nivel de columna de una tabla).

Nota: Lógicamente para que un usuario pueda otorgar estos permisos, debe de tener permiso para poder otorgarlo. Esto lo veremos posteriormente, pero por ahora indicar que el usuario que ejecuta las órdenes que otorgan los permisos es 'root'.

Vamos a analizar alguno de estos permisos a nivel global.

Podéis consultar el resto y profundizar en su aplicación en el curso Mysql 5.7 de esta Wiki.

• EXECUTE: El usuario tiene permiso para ejecutar procedimientos y funciones.

• CREATE USER: Permite crear, modificar, borrar, renombrar y quitar los permisos de los usuarios. Esto último lo conseguimos ejecutando la orden SQL: REVOKE ALL PRIVILEGES, GRANT OPTION FROM user.

• SHOW DATABASES: La cuenta que tenga este privilegio puede ver las bases de datos del Mysql ejecutando la orden SQL SHOW DATABASES. En caso de no tener este permiso, un usuario va a poder ver las bases de datos sobre las que tenga algún permiso de acceso.

• SHUTDOWN: Quien tenga este permiso podrá ejecutar la orden SQL SHUTDOWN o ejecutar la orden de consola: mysqladmin shutdown

• SUPER: Disponemos de diferentes permisos, los cuales estén indicados en el enlace.

• USAGE: Este permiso sólo indica que el usuario puede conectarse al Mysql. No da ningún tipo de privilegio.

Permisos a nivel de bases de datos

• Recordar que a este nivel, los permisos son filas que se añaden, borran o se modifcan en la tabla mysql.db, y se aplican en una base de datos concreta.

• A este nivel, la sintaxis para otorgar un permiso es la siguiente:

GRANT ALL ON mydb.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

Con GRANT ALL estamos otorgando todos los privilegios a este nivel.

Fijarse como se indica el nombre de la base de datos: GRANT SELECT ON mydb.* TO 'someuser'@'somehost';

• Los privilegios que pueden ser especificados a nivel de base de datos:

• SELECT, UPDATE, INSERT, INSERT
• CREATE: Permite crear tablas.
• DROP: Elimina una tabla.
• ALTER: Modifica una tabla. Es necesario tener el privilegio 'CREATE'.
• EVENT: Permite crear, eliminar o modificar eventos que pueden ser programados en el servidor Mysql. Más información en este enlace.
• LOCK TABLES: Permite 'bloquear' una tabla impidiendo que ningún usuario pueda realizar operaciones sobre la misma e incluso impidiendo que puedan leerla. Es necesario tener el permiso SELECT sobre la misma tabla para poder bloquearla.
• REFERENCES: Para poder crear una regla de clave foránea al crear una tabla.
• Los privilegios relacionados con la gestión de procedimientos almacenados y funciones (routines) pueden ser aplicados a nivel general y a nivel de base de datos. En este último caso, sólo se permitiría crear/modificar (incluye borrado) procedimientos/funciones dentro de la base de datos indicada.

GRANT CREATE ROUTINE, ALTER ROUTINE ON mydb.* TO 'someuser'@'somehost';

Indicar que en el caso de las rutinas, no existe el permiso DROP ROUTINE. Si posee el permiso ALTER ROUTINE puede modificar o borrar una rutina.

• GRANT OPTION: Otorgamos privilegios a un usuario y dicho usuario puede otorgar esos mismos privilegios a otros usuarios.

GRANT CREATE ROUTINE, ALTER ROUTINE 
ON mydb.* 
TO 'someuser'@'somehost'
WITH GRANT OPTION;

Permisos a nivel de tabla

• Recordar que a este nivel, los permisos son filas que se añaden, borran o se modifican en la tabla mysql.tables_priv, y se aplican en una base de datos concreta y en una tabla concreta.

GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

• Los privilegios que pueden ser aplicados a este nivel:

• Privilegios que gestionan tablas:

• CREATE: Mysql permite dar permiso para crear una tabla concreta, incluso si esta no existe.
• ALTER: Mysql permite dar permiso para modificar una tabla concreta, incluso si esta no existe.
• DROP: Mysql permite dar permiso para eliminar una tabla concreta, incluso si esta no existe.

• Privilegios que manejan datos de la tabla:

• DELETE
• SELECT
• UPDATE
• INSERT

Estos privilegios se aplican a todas las columnas de una tabla determinada.

• Privilegios que manejan vistas (visiones externas de una base de datos):

• CREATE VIEW
• SHOW VIEW

Para borrar una vista o modificarla, se necesita el permiso DROP.

• Otros privilegios:

• GRANT OPTION: Otorgamos privilegios a un usuario y dicho usuario puede otorgar esos mismos privilegios a otros usuarios.
• INDEX: Permite crear o borrar índices sobre una tabla.
• REFERENCES: Para poder crear una regla de clave foránea al crear una tabla.
• TRIGGER: Permite crear, borrar, modificar y mostrar triggers.

Permisos a nivel de columnas

• Recordar que a este nivel, los permisos son filas que se añaden, borran o se modifican en la tabla mysql.columns_priv, y se aplican en una base de datos concreta, en una tabla concreta y una columna concreta de esa tabla. Recordar que también se modifica la tabla mysql.tables_priv.

GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';

• Los privilegios que pueden ser aplicados a este nivel:

• INSERT
• REFERENCES: Para poder crear una regla de clave foránea al crear una tabla.
• SELECT
• UPDATE

Permisos sobre rutinas (proc.almacenados/funciones)

• Recordar que a este nivel, los permisos son filas que se añaden, borran o se modifcan en la tabla mysql.procs_priv table (cuando otorgamos permiso sobre una rutina concreta),mysql.user (todas las rutinas de todas las bases de datos) y mysql.db (todas las rutinas de una base de datos concreta).

GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

• Los privilegios que pueden ser aplicados a este nivel:

• Aplicables a cualquier rutina:

• ALTER ROUTINE (incluye borrado)
• CREATE ROUTINE
• EXECUTE
• GRANT OPTION

• Aplicados a una rutina concreta:

• ALTER ROUTINE
• EXECUTE
• GRANT OPTION

Ejercicios propuestos

Ejercicio 1

• Crea un usuario 'tengopermisos' y otórgale permisos para que pueda crear usuarios.

• Conectado como 'tengopermisos' crea un nuevo usuario 'user1'.

• Conectado como 'root', otórgale permisos al usuario 'user1' para que pueda parar el servicio Mysql.

• Conectado como 'root', muestra los permisos que tiene el usuario 'user1'.

Conectado como 'user1' muestra los permisos que posee y comprueba que son los mismos a los de la orden anterior.

• Conectado como 'root' otorga permiso de creación y borrado de procedimientos, así como de ejecución al usuario 'user1' sobre una base de datos creada previamente.

• Conectado como 'root' crea un usuario de nombre 'creartablas' que tenga permisos para crear, borrar y modificar tablas de una base de datos creada previamente.

• Conectado como 'creartablas' crea un tabla sencilla de al menos dos columnas.

• Conectado como 'root' crea un usuario de nombre 'accesoglobal' que pueda realizar operaciones de selección y inserción sobre todas las tablas de todas las bases de datos.

• Conectado como 'accesoglobal' añade una fila a la tabla creada anteriormente. Intenta borrar la fila creada. ¿ Puedes ?

• Conectado como 'root' crea un usuario de nombre 'accesolocal' que pueda seleccionar todas las tablas de la base de datos anterior.

Conéctate como 'accesolocal' y comprueba que puedes selecciona la fila añadida anteriormente.

• Conectado como 'root' crea un usuario de nombre 'accesolimitado' que pueda realizar operaciones de inserción, actualización y selección sobre la primera columna de la tabla creada previamente.

Conéctate como 'accesolimitado' y comprueba que tienes los permisos ejecutando las órdenes SQL SELECT, UPDATE e INSERT.

Comprueba que permisos tienes.

REVOKE

• La orden SQL contraria a grant es REVOKE.
• Podéis consultar la sintaxis completa en este enlace.

Imagen obtenida desde https://dev.mysql.com

• Un ejemplo sería el siguiente:

REVOKE INSERT ON *.* FROM 'usuario'@'localhost';

En este caso estaríamos quitando el permiso de inserción a nivel global (tabla mysql.user) al usuario 'usuario'.

• Los permisos que podemos quitar son los que ya vimos con la orden GRANT.

• Para poder quitar un permiso a un usuario debes de poseer dicho permiso y haberlo obtenido con la opción 'with grant option'.

Otra opción para poder quitar permisos (todos ellos) es que un usuario tenga el permiso 'CREATE USER'.

En este caso, va a poder ejecutar la orden:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user'@'localhost'

• Aclarar que esta orden quita permisos a un usuario pero no lo elimina.

Opción WITH GRANT OPTION

• Si observamos la sintaxis de la orden GRANT, podemos ver como se puede utilizar una opción WITH GRANT OPTION al final de la misma.

Esta opción lo que conlleva es que el permiso que estamos concediendo a un usuario con la orden GRANT, lo va a poder gestionar. Es decir, que va a poder otorgar el mismo permiso a otros usuarios.

Si otorga el permiso a otros usuarios con la opción WITH GRANT OPTION esos otros usuarios van a poder otorgar el mismo permiso a terceros. Y así sucesivamente.

• Por ejemplo:

GRANT SELECT,INSERT ON *.* TO 'matador'@'%' WITH GRANT OPTION;

Ahora si nos conectamos como 'matador', dicho usuario va a poder otorgar el permiso SELECT o INSERT a otro usuario.

• Indicar que si el permiso se puede aplicar a diferentes niveles, el usuario que tengo el permiso en un nivel con opción 'with grant option' va a poder otorgar el mismo permiso en los niveles de seguridad igual o inferiores al que posee.

Es decir, si tengo el permiso SELECT a nivel global con la opción 'with grant option' voy a poder otorgar el permiso SELECT a nivel global (tabla mysql.user), a nivel de una base de datos en concreto (tabla mysql.db) o a nivel de una tabla de una base de datos en concreto (tabla mysql.tables_priv).

• Si eliminamos un usuario (DROP USER) que haya otorgado permisos a otros usuarios, los permisos otorgados por el usuario se mantienen.

• Si queremos eliminar el permiso 'with grant option' de un usuario que lo tenga otorgado, tendremos que ejecutar la siguiente orden REVOKE:

REVOKE GRANT OPTION ON *.* FROM 'user'@'host'

Esto eliminará la opción WITH GRANT OPTION de todos los permisos otorgados con esa opción en el NIVEL CORRESPONDIENTE (en el ejemplo a nivel global).

LOS PERMISOS SE MANTIENEN.

Veamos un ejemplo:

• Elimando with grant option
• 

  Otorgamos permisos SELECT e INSERT a nivel global con la opción de gestionarlos al usuario 'user3'.

• 

  Eliminamos la opción WITH GRANT OPTION a dicho usuario en el mismo nivel de seguridad. Podemos comprobar como mantiene los permisos otorgados.

Ejercicios propuestos (grant / revoke / with grant option)

Ejercicio 1

• Conectado como root crea un usuario de nombre 'creador' que tenga permisos para crear usuarios.

• Conectado como 'creador' crea un nuevo usuario de nombre 'prueba1'.

Conectado como root haz que tenga permisos de selección y borrado a nivel global y todos los permisos sobre una base de datos de ejemplo creada previamente. Dichos permisos podrán ser gestionados por el usuario.

• Conectado como 'root' crea un usario de nombre 'prueba2' que tenga permiso para actualizar una tabla de una base de datos creada previamente. Podrá gestionar dicho permiso.

• Conectado como 'creador' crea un usuario 'prueba3' y 'prueba4'

• Conectado como 'prueba1' otorga permiso de selección, actualización de una columna de una tabla (creada previamente en la base de datos de ejemplo) y ejecución de procedimientos al usuario 'prueba3' en la base de datos de ejemplo. ¿ Puedes hacerlo ? ¿ Por qué ?

• Conectado como 'root' haz que el usuario 'prueba4' pueda parar el servicio mysql.

• Impide que el usuario 'prueba1' pueda gestionar los permisos otorgados a nivel global.

Comprueba que aún puede gestionar los permisos de la base de datos.

¿ Con qué usuario has realizado la operación ? ¿ Lo puedes hacer conectado con el usuario 'creador' ? ¿ Por qué ?

• Quita todos los permisos al usuario 'prueba3'. ¿ Puedes hacerlo conectado como 'creador' ? ¿ Por qué ?

• Quita los permisos 'específicos' otorgados a cada uno de los usuarios anteriores, comprobando con la orden SQL SHOW GRANTS que realmente fueron eliminados.

ROLES

• Un rol es un identificar que engloba un conjunto de permisos.

• A partir del Mysql v8 ya disponemos de roles.

• En la versión actual (5.7) lo más parecido lo tenemos en el MysqlWorkBench, donde existen un conjunto de roles 'predefinidos' que al pulsar sobre ellos aparecen en la parte derecha los permisos asociados a cada rol.

Si un usuario tiene permisos específicos, aparecerá una entrada 'CUSTOM' en la lista de roles.

• DBA: Grants all privileges
• MaintenanceAdmin: Grants privileges to maintain the server
• ProcessAdmin: Grants privileges to monitor and kill user processes
• UserAdmin: Grants privileges to create users and reset passwords
• SecurityAdmin: Grants privileges to manage logins and grant and revoke server privileges
• MonitorAdmin: Grants privileges to monitor the server
• DBManager: Grants privileges to manage databases
• DBDesigner: Grants privileges to create and reverse engineer any database schema
• ReplicationAdmin: Grants privileges to set up and manage replication
• BackupAdmin: Grants privileges required to back up databases
• Custom: Lists other (custom) privileges that are assigned to the user account

Ejercicios ROLES

Ejercicio 1

• Sobre uno de los usuarios creados, otorga los permisos necesarios para que pueda realizar copias de seguridad de las bases de datos.

Ejecuta la orden 'SHOW GRANTS' para ver qué permisos le has otorgado.

Elimina dichos permisos utilizando la orden REVOKE.

ROLES EN MYSQL 8

• Cuando Sun Microsystems compró MySQL AB, ya había una versión de MySQL 6. El producto MySQL Cluster ha estado utilizando la serie 7 durante mucho tiempo, así que los desarrolladores tivieron que saltar directamente a la versión MySQL 8.

• A partir de Mysql 8 es posible definir nuestros propios roles con un conjunto de permisos y asignarlo a usuarios.

• Más información en este enlace.

Crear Roles

• Más información en este enlace.

• El proceso para trabajar con roles debería seguir estos pasos:

• Crear los roles.
• Asignar a cada ROL los permisos correspondientes.
• Asignar a cada usuario el/los rol/es correspondientes.
• Activar el/los rol/es asignados

• Veamos un ejemplo:

CREATE ROLE IF NOT EXISTS mantenimiento_basico,mantenimineto_total,consulta_basica;

Es posible emplear el mismo formato de nombre que para el caso de nombres de usuarios, en los que el nombre está conformado por dos partes: nombre@host

A diferencia de la creación de usuarios, si empleamos el carácter % como identificador de host, este forma parte del identificador del ROL como un literal y no como un comodín.

CREATE ROLE IF NOT EXISTS 'miRol'@'%';

Por defecto es el carácter que asigna a cualquier rol que no lleve la parte host indicada.

Indicar que cuando se crea un ROL, en el MysqlWorkBench aparece un usuario con dicho nombre y parte de host % si no se indica nada.

Dicho 'usuario' tiene la cuenta bloqueada y el password está expirado.

Desde la herramienta Model del MysqlWorkBench, podemos gestionar los roles desde la sección Schema Privileges en la parte inferior.

• Una vez tenemos el Rol creado ya podemos asignar los diferentes permisos sobre el mismo, tal cual vimos en los puntos anteriores (lo podemos hacer gráficamente a través del MysqlWorkBench => Usuarios):

GRANT EXECUTE ON *.* TO  mantenimineto_total;   -- Permiso a nivel global
GRANT SELECT ON employees.* TO mantenimiento_basico;   -- Permiso a nivel de base de datos
GRANT SELECT ON employees.departments TO consulta_basica;  -- Permiso a nivel de tabla

• Ahora debemos de asignar los roles a los usuarios:

GRANT mantenimiento_basico,consulta_basica TO 'user1'@'%';   --Ejemplo suponiendo que tenemos un usuario user1@% previamente creado
GRANT mantenimineto_total TO 'user2'@'%';    --Ejemplo suponiendo que tenemos un usuario user1@% previamente creado

Nota: Es posible definir roles que sean 'asignados' por defecto a todos los usuarios. Son los llamados 'mandatory roles'. Más información en este enlace.

• Fijarse que dicha asignación no lleva consigo que el usuario tenga los permisos asociados a los roles que ahora tiene.

Es necesario activar dichos roles.

Se puede comprobar como al acceder como el usuario 1, si ejecutamos la función CURRENT_ROLE() devuelve NONE:

SELECT current_role();

• La activación de un rol la podemos implementar de dos formas:

• Dejarla activada por defecto y cada vez que el usuario se conecta, los permisos del rol están activos para ese usuario.
• Que sea el usuario (o el administrador) el que active/desactive el rol.

Para activar por defecto un rol, necesitamos emplear la orden SET DEFAULT ROLE.

El mismo usuario puede activar o desactivar los roles por defecto o bien un administrador con permisos CREATE USER, o UPDATE en la tabla del sistema mysql.default_roles.

Por ejemplo, para activar todos los roles asignados a un usuario podemos poner:

SET DEFAULT ROLE ALL TO 'user1'@'%','user2'@'%';

Para activar un rol concreto:

SET DEFAULT ROLE mantenimineto_total TO 'user2'@'%';

Esto hará que cuando el usuario user2 se conecte tenga activados todos los roles asignados (mantenimiento_basico,consulta_basica) y que el usuario user2 tenga activado por defecto el rol mantenimineto_total.

Si queremos activar/desactivar un rol mientras estamos conectados, es el propio usuario que deberá de ejecutar la orden SET ROLE.

Por ejemplo:

SET ROLE mantenimineto_total;   -- Activar el role a user2 ESTANDO CONECTADO COMO user2

SET ROLE NONE;   -- Deactiviaría todos los roles a user2 ESTANDO CONECTADO COMO user2

Nota: Podéis comprobar en la sintaxis de SET ROL que existen diferentes formas de activar y desactivar múltiples roles.

Replicación

• Replicación con MariaDb

Enlace a la página principal de la UD8

Enlace a la página principal del curso

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