Uso de Vistas

Si queremos utilizar la Vista en una consulta, solo debemos usar su nombre como si fuera una tabla más de la base de datos.

1
SELECT * FROM promedios_filosofia;

Podemos usar solo los campos que nos interesen, y podemos agregar las restricciones que usaríamos con cualquier tabla base, con las cláusulas que ya conocemos.

1
SELECT apellido, promedio
2
FROM promedios_filosofia
3
WHERE promedio > 80
4
ORDER BY promedio DESC
5
LIMIT 3;

Este ejemplo muestra apellido y promedio de, a lo sumo, los 3 alumnos con promedio mayor a 80 puntos, ordenados de mayor a menor.

Podemos realizar JOINs si la Vista cuenta con algún campo clave (primaria o foránea).

Por ejemplo, si queremos los nombres de los alumnos que figuran en la Vista, que tengan un promedio mayor a 60 y, que también estén inscriptos en la materia “Álgebra”, podemos usar la siguiente consulta:

1
SELECT pf.nombre
2
FROM promedios_filosofia AS pf
3
INNER JOIN inscripciones AS i ON pf.id_alumno = i.id_alumno
4
INNER JOIN materias AS m ON m.id_materia = i.id_materia
5
WHERE pf.promedio > 60
6
AND m.nombre = "Álgebra";

El contar con el id de alumno en la Vista, nos posibilita realizar JOINs con otras tablas que tengan esa clave como foránea (en este caso inscripciones).

Restricciones de uso por usuarios

Como mencionamos anteriormente, podemos crear usuarios en el servidor que solo tengan acceso a ciertas secciones de la base de datos, incluyendo a las Vistas.

Normalmente, solo el/los DBA (o Database Administrators) deben tener permisos para administrar toda la base de datos (crear, modificar, eliminar). Para las aplicaciones que hacen uso de los datos, es común utilizar usuarios específicos con solo los permisos necesarios para su correcto funcionamiento.

MySQL permite conceder, y revocar, permisos de distintas formas:

Por roles.
Por base de datos (incluyendo todos los objetos que contengan).
Por cada objeto en particular (tablas, vistas, procedimientos almacenados, etc.).
Por tipo de objeto (por ejemplo, todas las tablas de una base de datos particular o todas las tablas de todas las bases de datos del servidor).

Para esto, definimos un usuario sobre la base de datos instituto y luego configuramos los permisos adecuados.

1
CREATE USER 'profe_filosofia'@'<host>' IDENTIFIED BY '<contraseña>';

También puede omitirse completamente el nombre del host, en cuyo caso se asume que el usuario puede conectarse desde cualquier host.

Concesión de permisos

Vamos a ver los permisos que podemos dar a un usuario sobre objetos de una base de datos, como tablas y Vistas.

La sentencia para conceder permisos, que ya vimos en la materia pasada, es GRANT. Recordemos su sintaxis:

1
GRANT <privilegio> [, <privilegio> ] ... ON <base de datos>.<objeto> TO '<usuario>'@'<host>';

Donde cada <privilegio> es un permiso que queremos conceder al <usuario>, <base de datos> es el nombre de la base de datos, <objeto> es el nombre del objeto (tabla, Vista, etc.), y <host> es la IP desde donde se está conectando el <usuario>.

Las comillas en el nombre de usuario y host pueden omitirse si son nombres sin espacios ni caracteres especiales. El host podrá ser alguno de los configurados para un usuario previamente, por ejemplo, localhost o % (que significa cualquier host).

Algunos de los privilegios que podemos conceder en MySQL son INSERT, UPDATE, DELETE, SELECT, CREATE, DROP, ALTER, CREATE VIEW, entre otros. O incluso podemos conceder todos los permisos con ALL PRIVILEGES. Podemos consultar todos los privilegios en la documentación oficial de MySQL.

Entonces, si queremos conceder permisos a un usuario llamado profe_filosofia, para que pueda realizar consultas sobre la Vista promedios_filosofia (que se encuentra en la base de datos instituto), debemos ejecutar el siguiente comando:

1
GRANT SELECT ON instituto.promedios_filosofia TO profe_filosofia;

Este comando concederá solo el permiso de ejecutar la sentencia SELECT sobre la Vista promedios_filosofia al usuario profe_filosofia, conectado desde cualquier host.

Recordemos que estas restricciones se pueden aplicar a otros objetos de una base de datos, como tablas base y procedimientos almacenados (que veremos más adelante).

Por otro lado, si podemos brindar permisos de manera más específica, respecto a las columnas de una tabla o Vista. Para ello empleamos la siguiente sintaxis:

1
GRANT <privilegio> [(<lista_columnas>)] [,<privilegio> [(<lista_columnas>)] ... ON <base de datos>.<objeto> TO '<usuario>'@'<host>';

Donde <lista_columnas> es una lista de nombres de columnas separados por comas.

Por ejemplo, si queremos conceder permisos al usuario profe_filosofia para la tabla examenes_rendidos de la base de datos instituto, de manera que pueda realizar:

SELECT para todas las columnas de la tabla.
INSERT para todas las columnas de la tabla.
UPDATE solo para la columna calificacion de la tabla examenes_rendidos.

Debemos ejecutar el siguiente comando:

1
GRANT
2
    SELECT, INSERT, UPDATE(calificacion)
3
ON instituto.examenes_rendidos
4
TO profe_filosofia;

Ahora, si el usuario profe_filosofia intenta realizar una actualización en la tabla examenes_rendidos que no sea sobre la columna calificacion, recibirá un error.

1
UPDATE examenes_rendidos
2
SET calificacion = 10
3
WHERE id_alumno = 1;

Revocación de permisos

Al igual que concedemos permisos, podemos revocarlos. Esto es útil si queremos restringir el acceso a ciertos usuarios o si queremos cambiar los permisos de un usuario.

Para revocar permisos, utilizamos la sentencia REVOKE.

1
REVOKE <privilegio> [, <privilegio> ] ... ON <base de datos>.<objeto> FROM '<usuario>'@'<host>';

Como vemos, la sintaxis es similar a la de GRANT. Por ejemplo, podríamos revocar el permiso de UPDATE sobre la tabla examenes_rendidos al usuario profe_filosofia con el siguiente comando:

1
REVOKE UPDATE ON instituto.examenes_rendidos FROM profe_filosofia;

Un ejemplo práctico

Digamos que tenemos una base de datos para una aplicación web de mensajería, similar a Discord. La base de datos, simplificada, a la que denominaremos teamhub consta de las siguientes tablas y DER:

Sobre esta base de datos se ejecutan consultas para reportes de datos de la aplicación. Una de estas consultas es la siguiente: obtener una lista con la cantidad de mensajes de cada canal (channel) de todos los servidores, ordenado por servidores.

1
SELECT srv.name, ch.name, count(ms.message_id) AS mensajes FROM servers AS srv
2
INNER JOIN channels AS ch ON srv.server_id = ch.server_id
3
INNER JOIN messages AS ms ON ms.channel_id = ch.channel_id
4
GROUP BY srv.name, ch.name
5
ORDER BY srv.name;

Ya que esta consulta es muy usada, se decide crear una Vista con ella. La creación de la Vista se haría de la siguiente manera:

1
CREATE VIEW v_mensajes_por_servidor AS
2
SELECT srv.server_id AS id_servidor, srv.name AS servidor, ch.channel_id AS id_canal, ch.name AS canal, count(ms.message_id) AS mensajes FROM servers AS srv
3
INNER JOIN channels AS ch ON srv.server_id = ch.server_id
4
INNER JOIN messages AS ms ON ms.channel_id = ch.channel_id
5
GROUP BY id_servidor, servidor, id_canal, canal
6
ORDER BY servidor;

En este caso decidimos agregar el prefijo v_ al nombre de la Vista para distinguirlo de los de las tablas base. Esta convención es usada en algunos entornos de trabajo, aunque existen otras.

Además agregamos los campos de id para poder utilizar la vista en conjunto con otras tablas si lo necesitasmos.

Una vez creada la Vista, podemos usarla, por ejemplo, para obtener el total de mensajes por cada servidor:

1
SELECT servidor, sum(mensajes)
2
FROM v_mensajes_por_servidor
3
GROUP BY servidor
4
ORDER BY servidor;

Vistas actualizables

Por último, podemos mencionar que MySQL permite que sus Vistas sean actualizables, es decir, que se puedan modificar sus tablas base subyacentes con sentencias INSERT, UPDATE y DELETE a través de la misma Vista.

Las Vistas de MySQL son actualizables por defecto, pero para poder hacer uso de esta característica una Vista debe cumplir con ciertas condiciones.

Para que una Vista sea actualizable, debe haber una relación uno a uno entre las filas de la Vista y las filas de la tabla subyacente. Además no debe contener ciertas cláusulas, entre ellas, DISTINCT, GROUP BY, HAVING, etc., o hacer uso de ciertas funciones de agregación (SUM(), MIN(), MAX(), etc.).

Por ejemplo, podríamos definir una vista v_enrolamiento que muestre los datos de los usuarios y servidores que están en proceso de incorporación a un servidor.

1
CREATE VIEW v_enrolamiento AS
2
SELECT
3
    so.id,
4
    so.user_id,
5
    u.username,
6
    so.server_id,
7
    s.name AS server_name,
8
    s.description AS server_description
9
FROM
10
    server_onboarding so
11
JOIN
12
    users u ON so.user_id = u.user_id
13
JOIN
14
    servers s ON so.server_id = s.server_id;

Esta vista en particular cumple con las condiciones para ser actualizable, por lo que podríamos realizar operaciones de inserción, actualización y eliminación sobre ella, lo cual se vería reflejado en las tablas subyacentes.

1
INSERT INTO v_enrolamiento (user_id, server_id)
2
VALUES (3, 7);

Además existen distintas condiciones adicionales dependiendo de cómo se intente actualizar la Vista.

Esto hace que el uso de esta característica sea muy limitado. Por lo tanto, no entraremos en detalles en este tema.