Diseño e Implementación de Base de Datos y Gestión de Permisos SQL

Diseño de Esquema de Base de Datos y Gestión de Usuarios

1. Creación de Tablas

A continuación, se presenta el código SQL para la creación de las tablas que conforman el esquema de la base de datos, incluyendo comentarios explicativos sobre la estructura y las relaciones.

Tabla casas

Cada casa está identificada por un ID único. El campo p_disponibilidad es de tipo VARCHAR para permitir descripciones flexibles como "disponible del 10 de junio al 30 de agosto".

CREATE TABLE casas (
    id_casa INT PRIMARY KEY,
    dir VARCHAR(20),
    cp INT,
    ciudad VARCHAR(10),
    pais VARCHAR(20),
    p_disponibilidad VARCHAR(100),
    tiempo_min VARCHAR(20),
    tiempo_max VARCHAR(20)
) ENGINE=InnoDB;

Tabla familias

Cada familia está identificada por un ID único y contiene también el ID de la casa como clave ajena.

CREATE TABLE familias (
    id_familia INT PRIMARY KEY,
    nombre VARCHAR(10),
    edad_min_hijos INT,
    edad_max_hijos INT,
    num_hijos INT,
    correo VARCHAR(20),
    foto BLOB,
    id_casa INT REFERENCES casas(id_casa)
) ENGINE=InnoDB;

Tabla habitaciones

Las claves principales para habitaciones son num_habitacion e id_casa. Un número de habitación puede estar repetido, pero nunca para la misma casa. id_casa es clave ajena.

CREATE TABLE habitaciones (
    num_habitacion INT,
    precio INT,
    id_casa INT REFERENCES casas(id_casa),
    CONSTRAINT pk_habitaciones PRIMARY KEY (num_habitacion, id_casa)
) ENGINE=InnoDB;

Tabla clientes

Los clientes están identificados por una clave primaria (id_cliente) única para cada cliente.

CREATE TABLE clientes (
    id_cliente INT PRIMARY KEY,
    nombre VARCHAR(10),
    direccion VARCHAR(50),
    cp INT,
    pais VARCHAR(10),
    correo VARCHAR(30)
) ENGINE=InnoDB;

Tabla reservas

Todos los campos fecha_llegada, fecha_salida e id_cliente forman la clave primaria. id_cliente, id_casa y la combinación (id_casa, num_habitacion) son claves ajenas referenciando las tablas correspondientes.

CREATE TABLE reservas (
    fecha_llegada DATE,
    fecha_salida DATE,
    id_cliente INT,
    id_casa INT,
    num_habitacion INT,
    CONSTRAINT pk_reservas PRIMARY KEY (fecha_llegada, fecha_salida, id_cliente),
    CONSTRAINT fk_reservas_cliente FOREIGN KEY (id_cliente) REFERENCES clientes(id_cliente),
    CONSTRAINT fk_reservas_casa FOREIGN KEY (id_casa) REFERENCES casas(id_casa),
    CONSTRAINT fk_reservas_habitacion FOREIGN KEY (id_casa, num_habitacion) REFERENCES habitaciones(id_casa, num_habitacion)
) ENGINE=InnoDB;

Tabla comentarios

Todos los campos id_cliente, id_casa, num_habitacion y fecha forman la clave primaria. Todos, excepto fecha, son claves ajenas.

CREATE TABLE comentarios (
    id_cliente INT,
    id_casa INT,
    num_habitacion INT,
    fecha DATE,
    comentario VARCHAR(200) NOT NULL,
    CONSTRAINT pk_comentarios PRIMARY KEY (id_cliente, id_casa, num_habitacion, fecha),
    CONSTRAINT fk_comentarios_cliente FOREIGN KEY (id_cliente) REFERENCES clientes(id_cliente),
    CONSTRAINT fk_comentarios_casa FOREIGN KEY (id_casa) REFERENCES casas(id_casa),
    CONSTRAINT fk_comentarios_habitacion FOREIGN KEY (id_casa, num_habitacion) REFERENCES habitaciones(id_casa, num_habitacion)
) ENGINE=InnoDB;

2. Creación de Usuarios y Asignación de Privilegios

Se procede a la creación de usuarios para la base de datos y la asignación de sus respectivos privilegios.

Usuario bdadmin

Creación de un usuario bdadmin que requiere la contraseña 'password', conectándose desde el ordenador local ('localhost'), y que tenga todos los privilegios para trabajar sobre cualquier base de datos existente.

GRANT ALL PRIVILEGES ON *.* TO 'bdadmin'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Usuario remoto

Creación de un usuario 'remoto' que permita conectarse desde cualquier ordenador del aula 9 (red 192.168.9.%) con contraseña 'remoto', con privilegios para insertar, seleccionar y actualizar la base de datos "estancias" (supuestamente ya creada).

CREATE DATABASE estancias;
GRANT SELECT, INSERT, UPDATE ON estancias.* TO 'remoto'@'192.168.9.%' IDENTIFIED BY 'remoto';
FLUSH PRIVILEGES;

3. Comprobación de Usuarios y Permisos

Verificación del contenido de las tablas de permisos y usuarios creados en el sistema MySQL.

SELECT user, host FROM mysql.user WHERE user='bdadmin';
SELECT user, host FROM mysql.user WHERE user='remoto';

Consultas Adicionales y Gestión de Permisos

1. Visualización del Diccionario de Datos

Visualización del contenido (table_name, table_type, engine) del diccionario de datos para la base de datos estancias.

SELECT table_name, table_type, engine FROM information_schema.tables WHERE table_schema='estancias';

2. Permitir Conexión a Usuario Específico

Permitir a un usuario 'pepe' conectarse a la base de datos 'estancias' desde un cliente con la IP específica 192.168.9.1.

Nota: Como no se especifican permisos, se le otorgan todos. También se asume que el usuario 'pepe' existe; de no ser así, se debería crear y asignar una contraseña para evitar errores.

GRANT ALL ON estancias.* TO 'pepe'@'192.168.9.1';
FLUSH PRIVILEGES;

3. Permisos por Defecto al Crear Usuario

¿Qué permisos globales se otorgan por defecto cuando creamos un usuario nuevo con el comando CREATE USER? ¿Qué tablas se modifican cuando realizamos esta operación? Comprobación.

Respuesta:

• Si se crea con CREATE USER pepe, por defecto no tiene ningún privilegio global.
• Se modifican las tablas: mysql.user y information_schema.user_privileges.

4. Determinación del Usuario de Conexión

Suponiendo la siguiente tabla mysql.user:

HOST      USER
%         admin
%         pepe
localhost ''
localhost admin

¿Con qué usuario se conecta Pepe desde la máquina local? Comprobación con la función CURRENT_USER(). Explicación del motivo.

-- Conexión desde la línea de comandos como pepe
mysql -u pepe -p

-- Dentro del cliente MySQL, ejecutar:
SELECT CURRENT_USER();

Resultado esperado: 'pepe'@'localhost'.

Motivo: MySQL selecciona la entrada más específica que coincide con la combinación de usuario y host. En este caso, 'pepe'@'localhost' es más específico que 'pepe'@'%' cuando la conexión proviene de localhost.

5. Creación de Cuentas con Acceso Restringido por Host

Creación de dos cuentas con acceso a la base de datos "estancias". Ambas tienen nombre de usuario y contraseña, y permisos de consulta, inserción, modificación, borrado, creación y eliminación de tablas para la BD "estancias".

• La primera cuenta solo cuando se conecte desde el equipo "grupo9".
• La segunda cuando se conecte desde cualquier equipo.

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON estancias.* TO 'ejemplo1'@'grupo9' IDENTIFIED BY 'prueba';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON estancias.* TO 'ejemplo2'@'%' IDENTIFIED BY 'prueba';
FLUSH PRIVILEGES;

6. Eliminación de Privilegios y Cuenta

Eliminación de los privilegios de la primera cuenta creada en el ejercicio 5 y posterior eliminación de dicha cuenta.

REVOKE ALL ON estancias.* FROM 'ejemplo1'@'grupo9';
FLUSH PRIVILEGES;
DROP USER 'ejemplo1'@'grupo9';

7. Limitación de Recursos para una Cuenta

Limitación de los recursos, sobre la base de datos "estancias", para la segunda cuenta creada en el ejercicio 5.

• Número de consultas por hora: 2
• Número de actualizaciones por hora: 3
• Número máximo de conexiones por hora: 1

Comprobación de los límites establecidos y modificación posterior del número de consultas a 4.

Asignación de Límites

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON estancias.* TO 'ejemplo2'@'%' IDENTIFIED BY 'prueba'
WITH MAX_QUERIES_PER_HOUR 2
MAX_UPDATES_PER_HOUR 3
MAX_CONNECTIONS_PER_HOUR 1;

Comprobación Inicial de Límites

SELECT user, host, max_questions, max_updates, max_connections FROM mysql.user WHERE user='ejemplo2';

Modificación del Límite de Consultas

UPDATE mysql.user SET max_questions=4 WHERE user='ejemplo2';
FLUSH PRIVILEGES; -- Necesario para que los cambios en mysql.user surtan efecto

Comprobación del Resultado de la Modificación

SELECT user, host, max_questions, max_updates, max_connections FROM mysql.user WHERE user='ejemplo2';