Control de acceso

MySQL usa un sistema de dos niveles para controlar el acceso a los procedimientos almacenados: quién puede ejecutarlos y con qué permisos se ejecutan. Esto permite crear una capa de seguridad donde los usuarios acceden a datos a través de procedimientos sin tener acceso directo a las tablas.

SQL SECURITY: DEFINER vs INVOKER

Modo	Ejecuta con permisos de	Uso típico
DEFINER	El usuario que creó el procedimiento	Dar acceso controlado a datos
INVOKER	El usuario que llama al procedimiento	Respetar permisos del usuario

DEFINER: permisos del creador

DELIMITER //
 
CREATE PROCEDURE sp_datos_sensibles()
SQL SECURITY DEFINER
BEGIN
    SELECT id, nombre, email, telefono
    FROM clientes
    ORDER BY nombre;
END //
 
DELIMITER ;

Con SQL SECURITY DEFINER, un usuario sin permiso SELECT en la tabla clientes puede ejecutar este procedimiento si tiene permiso EXECUTE, porque se ejecuta con los permisos del creador.

INVOKER: permisos del que llama

DELIMITER //
 
CREATE PROCEDURE sp_mis_datos()
SQL SECURITY INVOKER
BEGIN
    SELECT id, nombre, email
    FROM clientes;
END //
 
DELIMITER ;

Con SQL SECURITY INVOKER, el procedimiento falla si el usuario que lo llama no tiene permiso SELECT en la tabla clientes.

GRANT EXECUTE

-- Dar permiso para ejecutar un procedimiento específico
GRANT EXECUTE ON PROCEDURE tienda_mysql.sp_datos_sensibles TO 'usuario_app'@'localhost';
 
-- Dar permiso para ejecutar todos los procedimientos de la base de datos
GRANT EXECUTE ON tienda_mysql.* TO 'usuario_app'@'localhost';
 
-- Revocar permiso
REVOKE EXECUTE ON PROCEDURE tienda_mysql.sp_datos_sensibles FROM 'usuario_app'@'localhost';

Ejemplo práctico: acceso controlado

Supongamos que queremos que un usuario de la aplicación pueda consultar y modificar pedidos sin acceso directo a las tablas:

-- Procedimiento para crear pedido (DEFINER)
DELIMITER //
 
CREATE PROCEDURE sp_crear_pedido(
    IN p_cliente_id INT,
    IN p_producto_id INT,
    IN p_cantidad INT,
    OUT p_pedido_id INT
)
SQL SECURITY DEFINER
MODIFIES SQL DATA
COMMENT 'Crea un nuevo pedido con validación'
BEGIN
    DECLARE v_precio DECIMAL(10,2);
    DECLARE v_stock INT;
 
    -- Validar stock
    SELECT precio, stock INTO v_precio, v_stock
    FROM productos WHERE id = p_producto_id AND activo = TRUE;
 
    IF v_stock IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Producto no encontrado o inactivo';
    END IF;
 
    IF v_stock < p_cantidad THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Stock insuficiente';
    END IF;
 
    -- Crear pedido
    INSERT INTO pedidos (cliente_id, fecha, total, estado)
    VALUES (p_cliente_id, CURDATE(), v_precio * p_cantidad, 'pendiente');
 
    SET p_pedido_id = LAST_INSERT_ID();
 
    INSERT INTO detalle_pedidos (pedido_id, producto_id, cantidad, precio_unitario)
    VALUES (p_pedido_id, p_producto_id, p_cantidad, v_precio);
 
    UPDATE productos SET stock = stock - p_cantidad WHERE id = p_producto_id;
END //
 
DELIMITER ;

-- Procedimiento para consultar pedidos del cliente
DELIMITER //
 
CREATE PROCEDURE sp_mis_pedidos(IN p_cliente_id INT)
SQL SECURITY DEFINER
READS SQL DATA
BEGIN
    SELECT p.id, p.fecha, p.total, p.estado,
           GROUP_CONCAT(pr.nombre SEPARATOR ', ') AS productos
    FROM pedidos p
    JOIN detalle_pedidos dp ON p.id = dp.pedido_id
    JOIN productos pr ON dp.producto_id = pr.id
    WHERE p.cliente_id = p_cliente_id
    GROUP BY p.id, p.fecha, p.total, p.estado
    ORDER BY p.fecha DESC;
END //
 
DELIMITER ;

-- Dar permisos al usuario de la aplicación
-- GRANT EXECUTE ON PROCEDURE tienda_mysql.sp_crear_pedido TO 'app_user'@'localhost';
-- GRANT EXECUTE ON PROCEDURE tienda_mysql.sp_mis_pedidos TO 'app_user'@'localhost';

El usuario app_user puede crear y consultar pedidos sin tener SELECT, INSERT o UPDATE en las tablas directamente.

Ver el DEFINER

SELECT ROUTINE_NAME, DEFINER, SECURITY_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'tienda_mysql'
AND ROUTINE_TYPE = 'PROCEDURE';

ROUTINE_NAME	DEFINER	SECURITY_TYPE
sp_datos_sensibles	root@localhost	DEFINER
sp_mis_datos	root@localhost	INVOKER
sp_crear_pedido	root@localhost	DEFINER
sp_mis_pedidos	root@localhost	DEFINER

Cambiar el modo de seguridad

ALTER PROCEDURE sp_mis_datos
SQL SECURITY DEFINER;

Ver permisos de un usuario

SHOW GRANTS FOR 'usuario_app'@'localhost';

Recomendaciones

Escenario	Recomendación
API de datos para aplicaciones	DEFINER con GRANT EXECUTE
Procedimientos administrativos	INVOKER para respetar permisos
Reportes para usuarios limitados	DEFINER con acceso controlado
Procedimientos internos	DEFINER es suficiente

Limpieza

DROP PROCEDURE IF EXISTS sp_datos_sensibles;
DROP PROCEDURE IF EXISTS sp_mis_datos;
DROP PROCEDURE IF EXISTS sp_crear_pedido;
DROP PROCEDURE IF EXISTS sp_mis_pedidos;

Con esto completamos la sección de procedimientos almacenados. Hemos cubierto desde la creación básica hasta el control de flujo, cursores y seguridad. En la siguiente sección exploraremos el manejo de errores dentro de procedimientos.

Escrito por Eduardo Lázaro