Administración de Sistemas Gestores de Bases de Datos

Herramientas de usuario

Herramientas del sitio

Traza:
bloque2:vistas

Diferencias

Muestra las diferencias entre dos versiones de la página.

Enlace a la vista de comparación

Ambos lados, revisión anteriorRevisión previa
Próxima revisión		Revisión previa
bloque2:vistas [2021/10/02 13:57] – [Ventajas e inconvenientes] fernandobloque2:vistas [2024/09/16 15:37] (actual) – editor externo 127.0.0.1
Línea 1: Línea 1:
-====== Índices y Vistas======+====== Índices, Vistas y Usuarios======
 ===== Índices ===== ===== Índices =====
 Los índices se utilizan para obtener datos de una columna concreta de las tablas de una forma más rápida. Optimizan el rendimiento de las consultas ''SELECT'' cuando buscamos datos en columnas indexadas. Los índices se utilizan para obtener datos de una columna concreta de las tablas de una forma más rápida. Optimizan el rendimiento de las consultas ''SELECT'' cuando buscamos datos en columnas indexadas.
Línea 30: Línea 30:
 Los índices se aplican a una columna en concreto, pudiendo haber distintas columnas con distintos índices en la misma tabla. Los tipos habituales de índice son: Los índices se aplican a una columna en concreto, pudiendo haber distintas columnas con distintos índices en la misma tabla. Los tipos habituales de índice son:
  
 +  * ''PRIMARY'': para columnas con valores únicos, con la restricción de que solo puede existir un índice de este tipo por tabla. Se asigna automáticamente a toda columna/s definidas como ''PRIMARY KEY''.
   * ''INDEX'': índice normal, permite valores duplicados, usado para mejorar el tiempo de ejecución.   * ''INDEX'': índice normal, permite valores duplicados, usado para mejorar el tiempo de ejecución.
   * ''UNIQUE'': para columnas con valor único, restringiendo la existencia de datos duplicados.   * ''UNIQUE'': para columnas con valor único, restringiendo la existencia de datos duplicados.
-  * ''PRIMARY'': para columnas con valores únicos, con la restricción de que solo puede existir un índice de este tipo por tabla. 
   * ''FULLTEXT'': se emplean para realizar búsquedas sobre texto (CHAR, VARCHAR y TEXT). Su funcionamiento es más complejo que los anteriores por lo que se especializa únicamente en búsquedas sobre columnas de texto.   * ''FULLTEXT'': se emplean para realizar búsquedas sobre texto (CHAR, VARCHAR y TEXT). Su funcionamiento es más complejo que los anteriores por lo que se especializa únicamente en búsquedas sobre columnas de texto.
  
 +Además el motor ''InnoBD '' que usa //MySql/MariaDB// por defecto en las tablas, asigna también un índice a toda **clave ajena** que creemos.
 ==== Implementación ==== ==== Implementación ====
 Existen distintas formas de añadir índices a una tabla: Existen distintas formas de añadir índices a una tabla:
Línea 49: Línea 50:
 -- Después de crear la tabla -- Después de crear la tabla
 CREATE INDEX indice_autor ON libro(autor); CREATE INDEX indice_autor ON libro(autor);
 +-- ó
 ALTER TABLE libro ADD INDEX [nombre_indice] (autor); ALTER TABLE libro ADD INDEX [nombre_indice] (autor);
  
Línea 67: Línea 69:
 <code sql> <code sql>
 DROP INDEX nombre_indice ON nombre tabla; DROP INDEX nombre_indice ON nombre tabla;
 +-- ó
 ALTER TABLE nombre_tabla DROP INDEX nombre_indice; ALTER TABLE nombre_tabla DROP INDEX nombre_indice;
  
 -- Para índices PRIMARY -- Para índices PRIMARY
 DROP INDEX 'PRIMARY' on nombre_tabla; DROP INDEX 'PRIMARY' on nombre_tabla;
 +-- ó
 ALTER TABLE nombre_tabla DROP PRIMARY KEY; ALTER TABLE nombre_tabla DROP PRIMARY KEY;
 </code> </code>
Línea 82: Línea 86:
 Cuando creamos una vista a partir de una consulta sobre una o más tablas, las futuras modificaciones en las tablas no se propagarán a la vista; si se añaden columnas a las tablas no se mostrarán en la vista, y si se eliminan columnas de las tablas que aparecían en la vista, la ejecucón dará un error. Funciona del mismo modo que funciona al consulta con la que se ha creado. Cuando creamos una vista a partir de una consulta sobre una o más tablas, las futuras modificaciones en las tablas no se propagarán a la vista; si se añaden columnas a las tablas no se mostrarán en la vista, y si se eliminan columnas de las tablas que aparecían en la vista, la ejecucón dará un error. Funciona del mismo modo que funciona al consulta con la que se ha creado.
  
-Del midmo modo, ya que una vista depende de la estructura de las tablas de origen, no se pueden añadir índices a una vista. Sin embargo podemos crear vistas usando la clausula ''ORDER BY'' en su creación. Si posteriormente al consultar sobre una vista, usamos ''ORDER BY'' anularemos el órden inicial.+Del midmo modo, ya que una vista depende de la estructura de las tablas de origen, no se pueden añadir índices a una vista. Sin embargo podemos crear vistas usando la cláusula ''ORDER BY'' en su creación. Si posteriormente al consultar sobre una vista, usamos ''ORDER BY'' anularemos el órden inicial.
  
 ====Ventajas==== ====Ventajas====
Línea 90: Línea 94:
   * Seguridad: podemos limitar el acceso a ciertas columnas con datos no autorizados a usuarios que deben consultar y procesar información de esas tablas.   * Seguridad: podemos limitar el acceso a ciertas columnas con datos no autorizados a usuarios que deben consultar y procesar información de esas tablas.
   * Mantiene la apariencia original: podemos modificar la estructura de las tablas que almacenan los datos sin que la estructura de la vista se vea afectada.   * Mantiene la apariencia original: podemos modificar la estructura de las tablas que almacenan los datos sin que la estructura de la vista se vea afectada.
 +  * Sustituye a consultas sobre subconsultas en la cláusula ''FROM''.
  
 Como hemos indicado, las vistas son una solución para ciertos aspectos por lo que no generan importantes desventajas. Como hemos indicado, las vistas son una solución para ciertos aspectos por lo que no generan importantes desventajas.
  
 ====Implementación==== ====Implementación====
 +
 +===Crear una vista ===
  
 <code sql> <code sql>
Línea 99: Línea 106:
 </code> </code>
  
-===Crear una vista === 
 Veamos un ejemplo: Veamos un ejemplo:
  
Línea 112: Línea 118:
 GROUP BY PP.id GROUP BY PP.id
 </code> </code>
 +
 +=== Uso de Vistas para resolver consultas complejas===
  
 Si ahora suponemos que nos pidieran conocer el polideportivo o polideportivos que más pistas tiene, sólo tendríamos que realizar una consulta utilizando la vista creada anteriormente. Si ahora suponemos que nos pidieran conocer el polideportivo o polideportivos que más pistas tiene, sólo tendríamos que realizar una consulta utilizando la vista creada anteriormente.
Línea 122: Línea 130:
                   FROM pistas_por_polideportivos)                   FROM pistas_por_polideportivos)
 </code> </code>
 +
 ===Eliminar una vista=== ===Eliminar una vista===
 Nos permite eliminar una vista creada: Nos permite eliminar una vista creada:
Línea 130: Línea 139:
 {{ youtube>EoQdJkhrnZw }} {{ youtube>EoQdJkhrnZw }}
 \\ \\
 +
 +===== Administración de Usuarios =====
 +
 +Una de las funciones del SGBD es la de proporcionar seguridad en el acceso a los datos a través de mecanismos de control de acceso.
 +
 +En SQL, y así lo hacen todos los SGBD relacionales, se sigue un modelo Usuario-Privilegio para otorgar acceso a        los objetos de la Base de Datos. Existen una serie de privilegios predefinidos y es el administrador del SGBD el        encargado de asignar o no los privilegios ((https://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html)) a los usuarios sobre determinados objetos (tablas, procedimientos, vistas, etc).
 +
 +Supongamos que somos los administradores de un SGBD MySQL y tenemos que proporcionar acceso a una Base de Datos        para una aplicación //biblioteca// a un desarrollador de mi compañía:
 +
 +<code sql>
 +-- Si no hemos creado la base de datos, podemos hacerlo ahora
 +CREATE DATABASE biblioteca;
 +
 +-- Crea el usuario asignándole contraseña
 +CREATE USER 'desarrollador' IDENTIFIED BY 'mipassword';
 +
 +-- Indicando el host de conexion del usuario
 +-- Desde cualquier host (igual que si se omite)
 +CREATE USER 'desarrollador'@'%' IDENTIFIED BY 'mipassword';
 +
 +-- Usuario para conectarse localmente
 +CREATE USER 'desarrollador'@'localhost' IDENTIFIED BY 'mipassword';
 +
 +-- Usuario para conectarse remotamente
 +CREATE USER 'desarrollador'@'146.167.34.15' IDENTIFIED BY 'mipassword';
 +
 +-- Ver los usuarios 
 +SELECT 'desarrollador' FROM mysql.user;
 +SELECT * FROM mysql.user;
 +
 +</code>
 +
 +
 +==== Permisos y privilegios ====
 +=== Conceder privilegios sobre un objeto: GRANT ===
 +
 +Permite conceder privilegios sobre un objeto a un usuario de la Base de Datos.
 +<code sql>
 +    GRANT <privilegio>
 +    ON <objeto>
 +    TO <usuario>
 +    [WITH GRANT OPTIONS]
 +</code>
 +
 +=== Revocar privilegios sobre un objeto: REVOKE ===
 +
 +Permite eliminar el privilegio sobre un objeto a un usuario.
 +
 +<code sql>
 +    REVOKE <privilegio>
 +    ON <objeto>
 +    FROM <usuario>
 +</code>
 +
 +=== Tipos de permisos===
 +
 +^Tipo^Función^
 +|SELECT | ver las tablas|
 +|INDEX| crear o eliminar índices|
 +|CREATE| usar la sentencia create|
 +|DELETE| eliminar registros de una tabla|
 +|DROP|eliminar elementos de la base de datos|
 +|SHOW TABLES|permite ver las tablas de la base de datos|
 +|USAGE| permite la instrucción USE 'base de datos'|
 +
 +Ejemplos:
 +<code sql>
 +-- Uso sin permisos para acceder desde local
 +mysql> GRANT USAGE ON *.* TO 'user'@localhost IDENTIFIED BY 'pass';
 + 
 +-- Uso sin permisos para acceder desde remoto
 +mysql> GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY 'pass';
 +
 +-- Conceder todos los privilegios sobre una base de datos en local
 +GRANT ALL PRIVILEGES ON `mibd`.* TO 'user'@localhost;
 +
 +-- Privilegios desde remoto
 +GRANT ALL PRIVILEGES ON `mibd`.* TO 'user'@'%';
 + 
 +-- Mostrar privilegios
 +mysql> SHOW GRANTS FOR 'user'@localhost; 
 + 
 +-- Eliminar privilegios
 +REVOKE ALL PRIVILEGES on *.* from 'user'@localhost;
 +REVOKE USAGE on *.* from 'user'@localhost;
 + 
 +-- Aplicar los cambios realizados
 +FLUSH PRIVILEGES;
 + 
 +-- Verificar permisos
 +mysql> SHOW GRANTS FOR 'user'@localhost;
 +</code>
 +
 +{{ :bloque5:mysql_grantrevoke.jpg?700 |}}
 +
 ---- ----
  
 (c) {{date> %Y}} Santiago Faci y Fernando Valdeón (c) {{date> %Y}} Santiago Faci y Fernando Valdeón
bloque2/vistas.1633183054.txt.gz · Última modificación: 2024/09/16 15:40 (editor externo)