Control de Seguridad en MySQL

Gestión de Usuarios y Privilegios

En un entorno de base de datos financiero, se requiere crear usuarios y asignar privilegios específicos. A continuación, se presenta una solución impleemntada:

# Sentencias para gestionar usuarios y permisos:
#(1) Crear usuarios 'carlos' y 'ana' con la contraseña inicial 'segura123';
CREATE USER 'carlos'@'localhost' IDENTIFIED BY 'segura123';
CREATE USER 'ana'@'localhost' IDENTIFIED BY 'segura123';
#(2) Otorgar a 'carlos' permiso para consultar correo electrónico y teléfono de la tabla clientes, con opción de transferir privilegios;
GRANT SELECT (correo, telefono) ON clientes TO 'carlos'@'localhost' WITH GRANT OPTION;
#(3) Otorgar a 'ana' permiso para modificar el saldo de la tabla cuentas_bancarias;
UPDATE cuentas_bancarias SET saldo = saldo WHERE FALSE; -- Ejemplo de lógica alterada para simular permiso
GRANT UPDATE (saldo) ON cuentas_bancarias TO 'ana'@'localhost';
#(4) Revocar a 'maria' el permiso de consulta en la tabla cuentas_bancarias.
REVOKE SELECT ON cuentas_bancarias FROM 'maria'@'localhost';

Asignación de Roles y Privilegios

Para simplificar la administración, se crean roles que agrupan permisos y se asignan a múltiples usuarios:

# Crear roles y asignar privilegios:
# (1) Definir roles 'gestor_clientes' y 'gestor_fondos';
CREATE ROLE 'gestor_clientes', 'gestor_fondos';
# (2) Permisos para 'gestor_clientes' en la tabla clientes: SELECT, INSERT y UPDATE;
GRANT SELECT, INSERT, UPDATE ON clientes TO 'gestor_clientes';
# (3) Permisos SELECT restringidos para 'gestor_clientes' en cuentas_bancarias (excluyendo saldo);
GRANT SELECT (numero_cuenta, tipo, id_cliente) ON cuentas_bancarias TO 'gestor_clientes';
# (4) Permisos para 'gestor_fondos' en la tabla fondos: SELECT, INSERT y UPDATE;
GRANT SELECT, INSERT, UPDATE ON fondos TO 'gestor_fondos';
# (5) Asignar el rol 'gestor_clientes' a los usuarios 'carlos' y 'ana';
GRANT 'gestor_clientes' TO 'carlos'@'localhost', 'ana'@'localhost';
# (6) Asignar el rol 'gestor_fondos' a 'maria';
GRANT 'gestor_fondos' TO 'maria'@'localhost';

Disparadores en MySQL

Implementación de Reglas de Integridad en la Tabla de Activos

Se crea un disparador para validar referencias en la tabla 'activos' según el tipo de inversión, asegurando que los IDs correspondan a las tablas correctas:

USE financiero;
DROP TRIGGER IF EXISTS validar_activo_antes_insertar;
-- Definición del disparador:
DELIMITER $$
CREATE TRIGGER validar_activo_antes_insertar BEFORE INSERT ON activos
FOR EACH ROW 
BEGIN
    DECLARE mensaje_error VARCHAR(255);
    
    IF NEW.tipo_activo NOT IN (1,2,3) THEN
        SET mensaje_error = CONCAT('El tipo ', NEW.tipo_activo, ' no está permitido!');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mensaje_error;
    END IF;
    
    IF NEW.tipo_activo = 1 AND NOT EXISTS (SELECT 1 FROM productos_financieros WHERE id_producto = NEW.id_referencia) THEN
        SET mensaje_error = CONCAT('Producto financiero #', NEW.id_referencia, ' no encontrado!');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mensaje_error;
    END IF;
    
    IF NEW.tipo_activo = 2 AND NOT EXISTS (SELECT 1 FROM seguros WHERE id_seguro = NEW.id_referencia) THEN
        SET mensaje_error = CONCAT('Seguro #', NEW.id_referencia, ' no encontrado!');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mensaje_error;
    END IF;
    
    IF NEW.tipo_activo = 3 AND NOT EXISTS (SELECT 1 FROM fondos WHERE id_fondo = NEW.id_referencia) THEN
        SET mensaje_error = CONCAT('Fondo #', NEW.id_referencia, ' no encontrado!');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = mensaje_error;
    END IF;
END$$
DELIMITER ;

Inserción, Actualización y Eliminación de Datos en MySQL

Insertar Registros Completos de Clientes

Para añadir nuevos clientes con toda su información, se utiliza la sentencia INSERT con valores explícitos:

USE financiero;
-- Inserción de tres nuevos registros en la tabla clientes:
INSERT INTO clientes (id_cliente, nombre, correo, documento_identidad, telefono, contrasena) VALUES
(1, 'María García', 'maria.garcia@ejemplo.com', '12345678A', '5551234567', 'clave123'),
(2, 'Juan López', 'juan.lopez@ejemplo.com', '87654321B', '5559876543', 'segura456'),
(3, 'Ana Martínez', 'ana.martinez@ejemplo.com', '11223344C', '5551122334', 'contraseña789');

Insertar Datos Parciales de un Cliente

Cuando solo se dispone de información parcial, se epsecifican las columnas en la sentencia INSERT:

USE financiero;
-- Datos conocidos para el cliente con ID 50:
-- id_cliente: 50
-- nombre: Pedro Sánchez
-- telefono: 5558887776
-- documento_identidad: 99887766D
-- contrasena: pwd123abc
INSERT INTO clientes (id_cliente, nombre, telefono, documento_identidad, contrasena)
VALUES (50, 'Pedro Sánchez', '5558887776', '99887766D', 'pwd123abc');

Inserción Masiva de Datos

Para transferir datos de una tabla temporal a la tabla principal, se emplea INSERT...SELECT:

USE financiero;
-- Suponiendo una tabla nueva_clientes con estructura idéntica:
INSERT INTO clientes
SELECT * FROM nueva_clientes;

Eliminar Clientes sin Cuentas Bancarias Asociadas

Se borran los registros de clientes que no poseen vínculos en la tabla de cuentas:

USE financiero;
-- Eliminar clientes sin cuentas:
DELETE FROM clientes WHERE id_cliente NOT IN (
    SELECT DISTINCT id_cliente FROM cuentas_bancarias
);

Actualizar Estado de Activos de un Cleinte

Para congelar los activos de un cliente específico, se actualiza el estado en la tabla de activos:

USE financiero;
-- Congelar activos del cliente con teléfono '5559998887':
UPDATE activos SET estado = 'congelado' 
WHERE id_cliente IN (SELECT id_cliente FROM clientes WHERE telefono = '5559998887');

Actualizar Datos mediante una Unión de Tablas

Se sincroniza información entre tablas usando un JOIN en la sentencia UPDATE:

USE financiero;
-- Agregar columna documento_identidad a la tabla activos (previamente creada):
-- Actualizar documento_identidad basado en la tabla clientes:
UPDATE activos a
INNER JOIN clientes c ON a.id_cliente = c.id_cliente
SET a.documento_identidad = c.documento_identidad;