Description:
Pretext: I assume DEFINER=`root`@`localhost` for everything, so maximal privileges for definer.
The final db user has just privileges to execute the function, nothing else.
When selecting in a function from a table, both a direct query and a prepared statement work.
But when selecting in a function from a view, just the direct query works. A prepared statement fails with a privilege error.
"Error Code: 1356. View 'test.firstlevelview' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them"
But this errormessage is not right. Please refer to the example below. Tables and column and functions are valid, and the rights of the definer root has more than enough rights.
Either both should fail, but, by my understanding of the SQL SECURITY DEFINER statement, actually both should work.
When doing a stored procedure of a prepared statement which uses the function, it works again.
(I discovered the bug when using a user defined function in a view and the db user just had select right for the view. But by minimizing I found it to be a problem at the function level, not the view level above it.)
How to repeat:
-- login with 'root'@'localhost'
CREATE SCHEMA `test`;
USE `test`;
CREATE TABLE IF NOT EXISTS `test`.`basetable_noRights` (
`id` INT NOT NULL AUTO_INCREMENT,
`something` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
INSERT INTO `test`.`basetable_noRights` VALUES (1, 'test');
CREATE DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`firstlevelview` AS SELECT 1 AS id, CONCAT(something, ' 1') AS foo FROM `test`.`basetable_noRights`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `select_from_table`(i INT) RETURNS VARCHAR(99) CHARSET utf8 READS SQL DATA SQL SECURITY DEFINER
READS SQL DATA
begin
SELECT something INTO @var FROM `test`.`basetable_noRights` WHERE id = 1;
return @var;
end$$
CREATE DEFINER=`root`@`localhost` FUNCTION `select_from_view`(i INT) RETURNS VARCHAR(99) CHARSET utf8 READS SQL DATA SQL SECURITY DEFINER
READS SQL DATA
begin
SELECT foo INTO @var FROM `test`.`firstlevelview` WHERE id = 1;
return @var;
end$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `finaltest` () SQL SECURITY DEFINER
BEGIN
PREPARE asdf FROM "SELECT select_from_view(1)";
EXECUTE asdf;
DEALLOCATE PREPARE asdf;
END$$
DELIMITER ;
CREATE USER 'foo'@'%' IDENTIFIED BY 'bar';
GRANT EXECUTE ON FUNCTION `test`.`select_from_table` TO 'foo'@'%';
GRANT EXECUTE ON FUNCTION `test`.`select_from_view` TO 'foo'@'%';
GRANT EXECUTE ON PROCEDURE `test`.`finaltest` TO 'foo'@'%';
FLUSH PRIVILEGES; -- should actually not be necessary anymore, but well
-- change to user foo
USE `test`;
SELECT select_from_table(1); -- this works
PREPARE stmt FROM "select select_from_table(1)";
EXECUTE stmt; -- this works too
SELECT select_from_view(1); -- this works
PREPARE stmt2 FROM "select select_from_view(1)";
EXECUTE stmt2; this doenst work
CALL finaltest; -- this works even though it has a prepared statement of the function inside.
Suggested fix:
SQL SECURITY DEFINER should work in every instance, so also for selects to views and in prepared statements
Description: Pretext: I assume DEFINER=`root`@`localhost` for everything, so maximal privileges for definer. The final db user has just privileges to execute the function, nothing else. When selecting in a function from a table, both a direct query and a prepared statement work. But when selecting in a function from a view, just the direct query works. A prepared statement fails with a privilege error. "Error Code: 1356. View 'test.firstlevelview' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them" But this errormessage is not right. Please refer to the example below. Tables and column and functions are valid, and the rights of the definer root has more than enough rights. Either both should fail, but, by my understanding of the SQL SECURITY DEFINER statement, actually both should work. When doing a stored procedure of a prepared statement which uses the function, it works again. (I discovered the bug when using a user defined function in a view and the db user just had select right for the view. But by minimizing I found it to be a problem at the function level, not the view level above it.) How to repeat: -- login with 'root'@'localhost' CREATE SCHEMA `test`; USE `test`; CREATE TABLE IF NOT EXISTS `test`.`basetable_noRights` ( `id` INT NOT NULL AUTO_INCREMENT, `something` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB; INSERT INTO `test`.`basetable_noRights` VALUES (1, 'test'); CREATE DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`firstlevelview` AS SELECT 1 AS id, CONCAT(something, ' 1') AS foo FROM `test`.`basetable_noRights`; DELIMITER $$ CREATE DEFINER=`root`@`localhost` FUNCTION `select_from_table`(i INT) RETURNS VARCHAR(99) CHARSET utf8 READS SQL DATA SQL SECURITY DEFINER READS SQL DATA begin SELECT something INTO @var FROM `test`.`basetable_noRights` WHERE id = 1; return @var; end$$ CREATE DEFINER=`root`@`localhost` FUNCTION `select_from_view`(i INT) RETURNS VARCHAR(99) CHARSET utf8 READS SQL DATA SQL SECURITY DEFINER READS SQL DATA begin SELECT foo INTO @var FROM `test`.`firstlevelview` WHERE id = 1; return @var; end$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `finaltest` () SQL SECURITY DEFINER BEGIN PREPARE asdf FROM "SELECT select_from_view(1)"; EXECUTE asdf; DEALLOCATE PREPARE asdf; END$$ DELIMITER ; CREATE USER 'foo'@'%' IDENTIFIED BY 'bar'; GRANT EXECUTE ON FUNCTION `test`.`select_from_table` TO 'foo'@'%'; GRANT EXECUTE ON FUNCTION `test`.`select_from_view` TO 'foo'@'%'; GRANT EXECUTE ON PROCEDURE `test`.`finaltest` TO 'foo'@'%'; FLUSH PRIVILEGES; -- should actually not be necessary anymore, but well -- change to user foo USE `test`; SELECT select_from_table(1); -- this works PREPARE stmt FROM "select select_from_table(1)"; EXECUTE stmt; -- this works too SELECT select_from_view(1); -- this works PREPARE stmt2 FROM "select select_from_view(1)"; EXECUTE stmt2; this doenst work CALL finaltest; -- this works even though it has a prepared statement of the function inside. Suggested fix: SQL SECURITY DEFINER should work in every instance, so also for selects to views and in prepared statements