while building a by now slightly complex database for a private project, we found an issue that may as well be a bug, but I wanted to ask for some advice here because maybe I am just wrong in my assumptions.
Preamble: All VIEWs and FUNCTIONs are created as DEFINER = `root`@`localhost` SQL SECURITY DEFINER. Just the final user that makes a connection is limited to what he can use.
For FUNCTIONs (this maybe is MySQL Workbench behaviour), the SQL SECURITY DEFINER never is transcribed to the DDL(?), but looking at SELECT SECURITY_TYPE FROM information_schema.routines, there is definitely written DEFINER.
Main issue: So the problem arises when I try to use a FUNCTION on a column of a VIEW, where the FUNCTION uses a VIEW itself and the user has just SELECT PRIVILEGES on the first mentioned VIEW.
If I do a straight SELECT, everything works as expected, but if I put the SELECT in a PREPAREd statement and EXECUTE this, I get an Error Code: 1356. View 'test.usableview' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them error.
MySQL Server used is 8.0.22 at the moment still on Windows 10, v8.0.27 (most recent release) has the same issue.
Minimal example:
- as a root user do the following:
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 TABLE IF NOT EXISTS `test`.`basetable_noRights2` (
`id` INT NOT NULL AUTO_INCREMENT,
`something` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
INSERT INTO `test`.`basetable_noRights2` VALUES (1, 'finally not visible');
CREATE OR REPLACE VIEW `test`.`firstlevelview` AS
SELECT 1 AS id, CONCAT(something, ' 1') AS foo FROM `test`.`basetable_noRights`;
DELIMITER $$
CREATE FUNCTION `user_has_no_rights_function`(i INT) RETURNS VARCHAR(99) CHARSET utf8
READS SQL DATA
begin
SELECT foo INTO @var FROM `test`.`firstlevelview` WHERE id = 1;
return @var;
end$$
DELIMITER ;
CREATE OR REPLACE VIEW `test`.`usableview` AS
SELECT user_has_no_rights_function(id) AS final FROM`test`.`basetable_noRights2` WHERE id = 1;
CREATE OR REPLACE VIEW `test`.`view_without_function` AS
SELECT snd.foo, fst.something FROM `test`.`firstlevelview` snd, `test`.`basetable_noRights` fst;
CREATE USER 'foo'@'%' IDENTIFIED BY 'bar';
GRANT SELECT ON TABLE `test`.`usableview` TO 'foo'@'%';
GRANT SELECT ON TABLE `test`.`view_without_function` TO 'foo'@'%';
FLUSH PRIVILEGES;
- login as newly created foo user. Compare:
SELECT * FROM view_without_function; -- works
PREPARE asdf FROM "SELECT * FROM view_without_function";
EXECUTE asdf; -- works
SELECT * FROM usableview; -- works
PREPARE asdf2 FROM "SELECT * FROM usableview";
EXECUTE asdf2; -- does not work
By the way, when changing the function to SELECT from a TABLE and not a VIEW, everything works as expected again. Maybe this is all by design, but in that case I don't understand the design. Maybe actually for prepared statements one needs to GRANT more privileges and we were just lucky that everything worked until here.
Maybe it is just some serverside setting that I don't know of?
I am especially looking forward for all "You need to grant privileges for the underlying tables", although we already saw in the example above that this ain't (completly) true because the straight SELECT works and the MySQL documentation also states that the privileges of (in our case) the root definer are used ;)